Compare a database sequence or auto-increment integer with a randomly generated UUID (version 4) as the primary key of a large, insert-heavy table. What are the real costs, and where does a time-ordered UUID (version 7) fit?

## Why key ordering dominates insert cost Primary keys are backed by a B-tree. Where a new entry lands is decided by the key's value, so the distribution of generated keys decides the physical write pattern. **Monotonic keys.** With an increasing sequence, every insert goes to the rightmost leaf page. That page is almost certainly already in the buffer cache, so no read is needed. When it fills, the split is a specialised right-edge split that leaves the old page full and starts a new one - so pages stay dense and the index stays compact. The set of pages touched by inserts is tiny regardless of how large the table is. **Random keys.** With UUIDv4 the target leaf is uniformly random across the whole index. While the index fits in memory this costs little. Once it does not, each insert must read a page from storage, modify it, and eventually write it back: one random read and one random write per insert. Splits occur in the middle of the key space, typically leaving both halves around half full, so the index inflates - commonly to well over the size an ordered build would produce - and needs more memory, which worsens the cache miss rate in a feedback loop. Checkpointing costs more too, because dirty pages are scattered rather than clustered, and in engines that log full pages after a checkpoint, log volume rises with the number of distinct pages touched. This is why teams report an insert-throughput cliff: performance is fine until the index no longer fits in RAM, then it falls off sharply. ## Width, and where the key gets copied A `bigint` is 8 bytes; a UUID is 16 as a native type, 36 if stored as text (and worse still with a collation applied to comparisons). The multiplier matters because the key is copied. In an index-organised or clustered-primary-key engine such as InnoDB, every secondary index entry stores the primary key as its row pointer, so an extra 8 bytes per row is paid in every secondary index. In heap-organised engines such as PostgreSQL, secondary indexes store a physical tuple pointer instead, so the multiplication is smaller - but every foreign key column referencing the table, and every index over those columns, still doubles in width. Wider keys mean fewer entries per page, deeper trees, and more pages to cache. **Never store UUIDs as text** if you use them. Native `uuid` or `binary(16)` halves the storage and compares as bytes. ## What random keys buy - **Generation without the database.** Clients, offline apps and separate services can mint an id before any write, which makes the id usable as an idempotency key and lets an aggregate be assembled entirely in memory before insertion. - **Merge safety.** Rows created independently in different shards, regions or environments can be combined without collisions. - **No shared allocator.** No sequence to be a bottleneck or a single point of coordination. - **Non-enumerable.** They do not reveal row counts or allow guessing neighbouring ids - though that is a defence-in-depth property, never a substitute for authorization. ## UUIDv7 and friends UUIDv7 (standardised alongside the other UUID versions in the current IETF specification) places a Unix millisecond timestamp in the most significant bits, then random bits. Sorting by the value approximately sorts by creation time, so inserts cluster at the right edge like a sequence while retaining decentralised generation. ULID and KSUID are earlier designs with the same idea. Practical consequences: - Insert locality is restored; index density and cache behaviour approach the sequence case. - Range scans and partition pruning by time become possible on the key itself. - **It leaks creation time** to anyone who sees the id - usually harmless, occasionally not. - **Right-edge contention returns.** Many concurrent inserters hammering the same rightmost page can contend on latches, which is precisely the problem some teams use random keys to avoid. Within a millisecond the random suffix spreads inserts slightly, which helps. - Ids from different nodes interleave only as well as their clocks agree; clock skew reorders but does not break uniqueness. ## Sequences: the sharp edges Sequences are not transactional. A rolled-back transaction consumes the value, and caching or preallocation blocks means gaps and, across restarts or failover, sometimes non-monotonic ordering. So **never treat a sequence value as a count, an audit ordering, or a gapless document number** - invoice numbering that must be gapless needs its own serialised allocation. Sequences also require a round trip or a RETURNING clause to learn the id, and they collide when independently generated datasets are merged unless you use per-node offsets or ranges. ## A defensible default For a single-database OLTP system: `bigint` identity, and expose something else publicly if enumeration matters. When ids must be generated outside the database, or rows are created across shards or regions, use UUIDv7 stored natively. Reserve UUIDv4 for cases where unpredictability of the value itself is a requirement, and accept the index cost knowingly. And measure at your real data size, because every one of these effects is invisible while the index still fits in memory.