There is no neutral identifier
How production systems mint row and object identifiers, and the four ways the choice fails: exhaustion, duplicate issuance, hot placement, and leakage.
Reads the repositories of GitLab, Rails, PostgreSQL, Twitter, Mastodon, Nextcloud, CockroachDB and the IETF's UUID revision to reconstruct how identifiers are chosen, sized, ordered and exposed. A reader leaves able to forecast their own exhaustion dates, pick the ID family that matches their store's physical placement, decide a backwards-clock posture, and audit what their public IDs disclose.
The two halves of the database industry give opposite advice about the same property and both are right: the PostgreSQL world spent a decade making keys more time-ordered for B-tree locality, while the range-sharded world (CockroachDB's hash-sharded indexes) builds machinery to make ordered keys less ordered, so the ID must be chosen with the storage engine, not before it.
What you get out of it
- GitLab noticed int4 exhaustion on ci_builds at 54.42% capacity with a 5 to 13 month forecast, and the online widening programme it started (Dec 2020) was still breaking upgrades in June 2024: the remediation spans more calendar than the runway did.
- Exhaustion propagates along the ID's whole reference graph: GitHub's May 2021 outage was a foreign key, not a primary key, and Nextcloud operators hit the same shape when a plugin column lagged the core's widening.
- Uniqueness never comes free: a sequence is a single issuing point, a snowflake needs worker-id assignment plus a clock posture (Twitter refuses to mint when time goes backwards; Postgres absorbs it with a 12-bit counter), and randomness costs 128 bits and index locality.
- Time-ordering fixes B-trees and breaks range-sharded stores; CockroachDB's own founder proposed hash-sharding indexes by default because users do not discover the fix.
- IDs leak by design: sequential IDs publish table sizes (Mastodon's stated reason for switching in 2017), UUIDv7 publishes creation time, and RFC 9562 rules that UUIDs MUST NOT be used as security capabilities.
Scope
Why this, now. RFC 9562 (May 2024) and PostgreSQL's native uuidv7() (committed December 2024) just ended a fourteen-year period in which every large system invented its own identifier scheme, so the choice is being re-litigated in design reviews now.
What it does not cover. Natural and business keys, transaction-ID wraparound, URL-shortener keyspaces, trace and span IDs, and content-addressed hashes; engineering-blog, paper and talk sources are absent because this session's network reaches only code hosts, and the guide says so.
Other field guides
Every backend you add is a six-year promise: ten years of Grafana Labs in git
A decade of Grafana Labs reconstructed entirely from its own git history across ten repositories: the convergence of Mimir, Loki, Tempo and Pyroscope…
26 sources · 4 organisations · 4 postmortemsThe lock is brief. The queue is the outage.
Reconstructs live schema change from the places fourteen organisations wrote their lessons down: GitLab's production incident tracker, gh-ost's desig…
24 sources · 14 organisations · 4 postmortemsWhen the database cannot be split: ten years of GitLab.com, read from its own incidents and design documents
A single-company archaeology of the problem every growing platform eventually hits: two components that cannot be sharded without rewriting the appli…
30 sources · 1 organisations · 9 postmortems