Rows must be created on several database shards, in more than one region, and by offline clients that assign an identifier before the row ever reaches a database. How would you choose an identifier strategy for that, and what are the trade-offs?

## Start with requirements, not schemes Six questions decide almost everything: 1. **Where is the id minted?** Database, application server, or an offline client? Anything minted outside the database rules out sequences and identity columns. 2. **Is coordination acceptable?** A central allocator is a dependency and a failure domain; some systems can afford it, region-partitioned or offline-first ones cannot. 3. **Does ordering matter?** For insert locality, for time-range pruning, or for the semantics of "most recent"? Note that no distributed scheme gives true global ordering; the best you get is approximate ordering under clock skew. 4. **What width can you afford?** 64 bits keeps `bigint` columns and every referencing index narrow. 128 bits doubles keys everywhere and, in clustered-primary-key engines, in every secondary index too. 5. **What may the id reveal?** Time prefixes leak creation time; node ids leak topology; counters leak throughput. 6. **What happens on retry?** If an offline client resends, the id must let the server recognise a duplicate. ## The options **Random UUIDv4.** Uniqueness with no coordination whatsoever, and no information leaked. Worst insert locality: random placement across the index means random reads, mid-page splits and index bloat once the index exceeds memory. Choose it when unpredictability of the value itself is the requirement. **Time-ordered UUIDv7 (or ULID/KSUID).** Millisecond timestamp in the high bits, randomness below. Still mintable anywhere with no coordination, but inserts cluster at the right edge, so index density and cache behaviour approach a sequence. Costs: 128 bits, visible creation time, and right-edge latch contention under heavy concurrent insert. Ordering across nodes is only as good as clock synchronisation - though uniqueness never depends on the clock, because the random bits carry it. **Snowflake-style 64-bit ids.** Partition the bits: timestamp since a custom epoch, a node identifier, and a per-millisecond sequence. Fits `bigint`, is roughly time-sortable, and needs no allocator per id. Costs are operational: node ids must be assigned uniquely and durably (config, coordination service, or a stable ordinal from the orchestrator), and you must decide what to do when the clock goes backwards - wait it out, or refuse to issue. Exhausting the per-millisecond counter means blocking until the next millisecond. The epoch and bit split also cap the scheme's lifetime and node count, which must be chosen with decades in mind. **Range allocation (hi-lo).** A central sequence or service hands each writer a block of, say, 1,000 values; the writer consumes them locally. Excellent locality per writer, small keys, no per-id coordination. Costs: an allocator to run and make highly available, gaps whenever a writer restarts with a partly used block, and ids that are only ordered within a writer. **Composite keys.** `(shard_id, local_sequence)` or `(tenant_id, local_sequence)` makes the routing information part of identity, which suits sharded systems and makes the shard obvious from the key. Costs: composite keys propagate into every foreign key, resharding is painful because the shard component is baked into identity, and per-tenant sequential numbers are visible to that tenant - occasionally desirable, occasionally a leak. **Per-node offset sequences.** Each node's sequence starts at a distinct offset and increments by the node count. Cheap, but adding nodes later is awkward and the scheme quietly assumes a fixed node count. ## Offline clients When a client creates rows without connectivity, the client-minted id is not merely an identifier - it is the deduplication key for retried submissions. Store it with a unique constraint so the second delivery of the same creation is rejected by the database rather than by hopeful application logic. This is a strong argument for UUIDv7 in mobile and field-service systems: the client can mint it, the server can insert it, and the unique constraint provides idempotency for free. ## Collision reasoning With 122 random bits, UUIDv4 collision probability is negligible at any realistic volume. UUIDv7's randomness is smaller because the timestamp consumes bits, so collisions are governed by how many ids a single generator can produce within one millisecond - real implementations add a monotonic counter within the millisecond precisely for this. Snowflake ids have no randomness at all: uniqueness rests entirely on node ids being unique and the clock never repeating a millisecond. That is a very different risk profile - a duplicated node id, from a bad deployment or a cloned VM, silently produces duplicate keys. ## How I would decide If ids can be minted by the database and there is one writer, use `bigint` identity and stop. Once minting must happen elsewhere, default to UUIDv7 stored natively, because it buys decentralised generation, acceptable insert locality and idempotency with no infrastructure. Move to Snowflake-style ids when 128-bit keys are measurably too wide for the data volume and you have the operational maturity to assign node ids and manage clocks. Use range allocation when a central allocator is already acceptable and you want small ordered keys. Reach for composite shard-scoped keys only when the shard is a genuinely permanent property of the row. And say the meta-point: this decision is very expensive to reverse, because ids propagate into every foreign key, every export, every partner integration and every URL. Decide it deliberately and early, then leave it alone.