Compared with a plain non-unique index on the same column, what extra work does a unique index perform on every INSERT, and why can engines not defer or buffer that work the way they can for non-unique index maintenance?

## Baseline: what a non-unique index insert costs Inserting a row into a table with a secondary B+Tree index costs, per index: 1. A descent from root to leaf to find the insertion point — typically 3–4 page accesses, usually cached for the upper levels. 2. A latch on the target leaf and the write of an entry (key + row locator). 3. A write-ahead log record for that change. 4. Occasionally a page split, which touches at least two leaves plus the parent, and can cascade upward. Crucially, correctness here does not depend on the current contents of the leaf. The entry is valid regardless of what neighbours exist. That property is what makes deferral possible: engines with a change/insert buffer can record "add this entry later" without reading the leaf page at all, avoiding a random read on a cold index; LSM-based engines can accept the write into an in-memory table and merge it down later; some engines batch and sort secondary index changes for a whole statement. ## What uniqueness adds A unique index must guarantee that at most one *live* row has a given key. That turns a blind write into a read-then-write: **1. The duplicate probe.** Before inserting, the engine scans the entries at and around the key position. In a non-MVCC storage engine that is cheap — an existing entry means a duplicate. Under MVCC it is not: an index entry may point at a row that was deleted, or that was inserted by a transaction that later aborted, or that is simply not visible to anyone anymore. The index alone often cannot tell. So the engine may need to fetch the referenced row (or a visibility summary structure) to decide whether the entry represents a live conflict. Each such fetch is a random access to the table, on the write path. **2. The conflict wait.** Suppose transaction T1 inserted key `k` and has not committed. T2 now inserts `k`. Is this a duplicate? Nobody knows yet — it depends on whether T1 commits. The engine cannot fail T2 (T1 might abort) and cannot let T2 through (T1 might commit). So T2 **waits** on T1's transaction. This is the single most important operational consequence: a unique index couples otherwise independent writers. Under a hot key it produces convoys; combined with other locks it produces deadlocks. Plain indexes never do this. **3. No buffering.** Because step 1 requires reading the leaf, the optimizations that make non-unique index maintenance cheap on cold indexes are disabled for unique indexes in most engines. On a large index that does not fit in memory, this converts an avoidable random read into a mandatory one for every inserted row. In LSM-style engines the effect is even sharper: the whole point of an LSM write is that it is a blind append, and a uniqueness requirement forces a read across all levels (or at least a bloom-filter-guided lookup) per insert, eroding the write advantage. ## Where the cost actually shows up - **Number of unique indexes.** Each one adds a full probe-plus-insert cycle per row. A table with a primary key and three unique constraints pays four times. - **Key distribution.** Random keys (UUIDv4) scatter the probes across the whole index, so each insert is likely a cache miss on both the probe and the write, and page splits are random and leave pages half full. Monotonic keys concentrate everything on the rightmost leaf: excellent cache behaviour, but a latch hotspot at very high concurrency. - **Updates.** If an UPDATE does not touch the indexed columns, engines generally skip index maintenance entirely (and may keep the update inside the same table page). If it does touch them, it is a delete-plus-insert in the index, and the insert half pays the uniqueness probe again. - **Rollbacks and churn.** Aborted inserts leave dead entries that later probes must still examine and judge, so a workload with high abort rates or heavy delete/re-insert of the same keys makes the probe progressively more expensive until cleanup runs. ## Deferral does not make it cheap A constraint declared deferrable and checked at commit still requires the index maintenance immediately (the entry must exist for others to see); deferral moves *when the violation is reported*, and costs memory to remember the pending checks. It can help with ordering problems inside a transaction; it does not reduce per-row cost. ## Practical guidance - Enforce a given uniqueness rule exactly once. Duplicated unique indexes on the same column set (one from a constraint, one hand-made) double the write cost for zero benefit. - Prefer narrow keys; a unique index on a wide composite or a long text column makes every probe and every leaf entry more expensive and reduces fanout. - For bulk loads, load first and build the unique index afterwards: a sort-based build detects duplicates in one pass over sorted data, instead of N random probes, and produces densely packed pages. - If a natural key must be unique but is never queried, ask whether the uniqueness is worth a whole index on the hot write path, or whether it can be validated asynchronously — an explicit tradeoff, not a default. ## The one-line answer "A unique index turns a blind append into a read-modify-write with a possible wait on another transaction, so it costs an extra probe, defeats insert buffering, and serializes writers that collide on a key."