Every row a system creates gets a name, and the naming scheme is picked on day one, when none of its pressures are visible. This guide reads the repositories of GitLab, Rails, PostgreSQL, Twitter, Mastodon, Nextcloud, CockroachDB and the IETF's own UUID revision to reconstruct how identifiers are minted in production, and the four distinct ways the choice fails: the numbers run out, two writers issue the same one, the ordered ones pile onto one page or one shard, and the ID itself tells strangers things you never meant to publish.
A problem that predates every database it lives in: give each new thing a name that is unique across concurrent writers, cheap to mint at write rate, orderable enough for the storage engine, and safe to show to strangers.
State the problem without naming a technology: a system that accepts writes must give each new record a name that no other record carries, without making writers wait for each other, and the name will then live in indexes, URLs, caches, foreign keys and other people's client code for the lifetime of the data. That is the whole problem. It looks like a one-line schema decision, and it is one of the few schema decisions that cannot be changed cheaply later: Nextcloud's admin manual puts the cost of widening the column afterwards at "several hours or even days" of maintenance, and GitLab's online version of the same change is a multi-year programme of triggers and backfills.
The public record on this problem is unusually good, because so much of it was argued in the open. Twitter published its generator's requirements in the Snowflake repository in 2010. Mastodon's switch away from sequential IDs is a merged pull request with the dissent still attached. GitLab's near-miss with a 32-bit key is a public issue with the exhaustion forecast in it. And the IETF's 2024 revision of the UUID standard, RFC 9562, was drafted in a public GitHub repository whose background section catalogues roughly sixteen non-standard schemes (ULID, KSUID, Snowflake, Sonyflake, MongoDB's ObjectID and others) that industry built while the standard stood still. The standards body did not lead this field; it ratified, fourteen years after Snowflake, what production systems had already converged on.
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 identifiers more ordered, because random UUIDs touch every index leaf page and evict the cache: Tomas Vondra's sequential-uuids extension (2018), then UUIDv7 itself, whose RFC claims an order-of-magnitude locality gain. The distributed-SQL world builds machinery to make ordered keys less ordered: CockroachDB's hash-sharded indexes exist because, in its own words, "workloads involving sequential keys can have major performance problems." Time-ordering is not good or bad; it is good exactly when the storage engine appends and bad exactly when the storage engine range-partitions. The ID must be chosen with the store, not before it.
Scope. This guide covers surrogate identifiers for rows and objects: how they are minted, sized, ordered and exposed. It deliberately does not cover natural and business keys, transaction-ID wraparound (a database-internal counter with its own incident history, indexed in the same postmortem collection cited here), URL-shortener keyspaces, trace and span IDs, or content-addressed hashes. One honest limit of the evidence base: this session's network policy reaches code hosts but not engineering-blog, paper or talk hosts, so the wall below is built from issues, commits, specs and runbooks, and three well-known blog-hosted postmortems (Basecamp, GitHub, Strava) enter via the danluu/post-mortems collection entry that indexes them rather than as direct fetches.
The node-local generator is the same machine everywhere it appears: a timestamp, a node number, a counter, packed into a fixed budget of bits. Every design choice is a reallocation of that budget, and every field fails differently.
Start with the archetype. Twitter needed IDs for tweets as it moved off MySQL, and the Snowflake README states the constraint that created the whole family: "There is no sequential id generation facility in Cassandra, nor should there be." The requirements were a minimum of 10,000 IDs per second per process, under 2 ms, with no coordination between machines, and IDs that are "k-sorted" (roughly time-ordered, within a bound the README puts at about one second). The layout that satisfies this is 41 bits of milliseconds since a custom epoch, 10 bits of machine ID and 12 bits of per-millisecond sequence, in one 64-bit integer. Everything since is a re-cut of those three fields.
Read the three fields as three liabilities, because that is how they fail in production.
The timestamp field is a bet on the clock. The Snowflake README is blunt about the posture: "If your clock is running fast and NTP tells it to repeat a few milliseconds, snowflake will refuse to generate ids until a time that is after the last time we generated an id." Refusal, not reuse: the generator chooses unavailability over duplicates, which means a clock step backwards is a write outage on that node. PostgreSQL's 2024 implementation of UUIDv7 takes the other branch: the commit stores a 12-bit sub-millisecond fraction in the space RFC 9562 calls rand_a, and the commit message states that generation stays monotonic "within the same backend even when the system clock goes backward." Same problem, opposite remedies, and the difference matters on the day NTP misbehaves.
The node field is a small distributed-systems problem you now operate. Ten bits of worker ID means something must hand out 1,024 distinct numbers and never give the same one to two live processes; the k-sortedness and the uniqueness both depend on it. None of the READMEs in this corpus describe their assignment mechanism in detail, which is itself a finding: the hard operational part of the snowflake family is the part the repositories are quietest about. The 128-bit family exists to delete this field entirely. Segment's KSUID README makes the trade explicit: 128 bits of randomness, "64 times larger than the 122 bits" of a UUIDv4, so that no worker registry is needed at all; the price is 160-bit IDs (20 bytes binary, 27 characters as text) instead of 64.
The sequence field is the rate limiter. 4,096 IDs per millisecond per worker for Snowflake, 256 per 10 ms for Sonyflake, 65,536 per millisecond per Postgres instance in Mastodon's layout. The ULID spec shows what happens at the boundary of the equivalent field: within one millisecond the random component is incremented "by 1 bit in the least significant bit position (with carrying)", and if the counter would overflow, "the generation will fail." Every generator in this family has a ceiling per tick; the only question is whether it blocks, fails or silently reuses when it hits it. RFC 9562 standardised the three respectable answers as its three monotonicity methods: a dedicated counter after the timestamp, a monotonic random increment, or extra clock precision in the leading random bits.
There is a fourth component that belongs in the reference architecture, although nobody
plans it: the widening machinery. GitLab's merge request !49778 (December 2020) is the
canonical shape, built to convert int4 primary keys to bigint on tables that cannot stop:
add a shadow bigint column, install a trigger that mirrors every write into it, backfill
the history in batches from background jobs, then swap the columns. It is a dual-write
system bolted onto your own primary key, it explicitly targets "DOWNTIME = false", and it
is not fast: the programme that started with that MR was still producing cleanup-migration
failures in self-managed upgrades in June 2024. Nextcloud, facing the same conversion with
less operational leverage over its installations, ships it as an offline command
(occ db:convert-filecache-bigint) and warns that it "can take several hours
or even days." The transfer for an architect: the day-one width decision is the only cheap
one you will ever get on this column, which is exactly why Rails changed the framework
default to bigint in PR #26266 with the one-line justification "Friends don't let friends
use INT as a primary key."
Four forks, each with the condition that flips it. The recurring pattern: every desirable property of an identifier is paid for in a different currency, and the right choice depends on which currency you happen to be rich in.
| Decision | Chosen | Rejected | Because | Evidence |
|---|---|---|---|---|
| Twitter, 2010 | Node-local time+worker+sequence | Store-issued sequence | The new store could not issue IDs; 10k IDs/s/process required | Snowflake README |
| Rails, 2016 | bigint default primary key | int default | Exhaustion is a default-setting problem, not an app problem | PR #26266 |
| Rails, 2016 (first attempt) | Closed unmerged | bigserial for Postgres only | "Running the same migration on Rails 4 and Rails 5 will produce a different database schema" (tenderlove); superseded by #26266 with a compatibility layer | PR #24962 |
| Mastodon, 2017 | Timestamp IDs, in-database, statuses only | Sequential IDs; also rejected: converting every table | Hide table sizes; scope cut after maintainer pushback | PR #4801 |
| Sony, 2015 | 39/16/8 bit split | Snowflake's 41/10/12 | Lifetime and machine count over mint rate | Sonyflake README |
| Segment, 2017 | 160-bit KSUID | UUIDv4 (unordered), 64-bit flake (coordination) | Time-ordering without a worker registry | KSUID README |
| IETF, 2024 | Standardise time-ordered UUIDv7 | Leaving RFC 4122 as-is | ~16 non-standard schemes had already filled the gap | RFC 9562 background |
| PostgreSQL, 2024 | uuidv7() with 12-bit sub-ms counter (method 3) | Refusing on backwards clocks | Monotonic within a backend even when the clock steps back | commit 78c5e141 |
| CockroachDB, open question | Hash-sharded indexes exist, off by default | Making them the default (proposed 2022, still open) | Scan cost "proportional to the number of buckets" vs users not discovering the fix | #78049 |
Four failure classes cover every identifier incident in this corpus: exhaustion, duplicate issuance, hot placement, and leakage. The first two have public incident records; the second two surface as design changes made under pressure.
Exhaustion deserves its arithmetic stated once, plainly. A signed 32-bit key dies at 2,147,483,647. GitLab opened its tracking issue in April 2020; by April 2021 the ci_builds sequence stood at 1.168 billion, 54.42% of capacity, and the issue forecast overflow "between September 2021 (at a worst case scenario using an exponential forecast) and May 2022 (following a polynomial model)". Twelve to twenty-five months of runway, against a conversion machinery (MR !49778, December 2020) whose long tail was still breaking self-managed upgrades in June 2024. The runway and the remediation were the same order of magnitude, and that is the general lesson: by the time a busy table is at half capacity you are not early, you are on schedule.
max(id) / type_max per table at 25% and 50%, not on failure. The metric is one query and it never pages you twice.hook.update!(recent_failures: hook.recent_failures + 1) with no bound, and a persistently failing hook walked the smallint to its ceiling.The duplicate-issuance class has a second, quieter member that operators meet after a
failover rather than in code review. GitLab's Patroni runbook documents the signature: a
storm of "duplicate key value violates unique constraint" errors that "can [be] because of
a sequence ... has been modified (i.e. has been RESET)", with the remediation being
setval() above the table's true maximum. The runbook states the symptom and
the cause; the sequence diagram below reconstructs the path from those two facts, and is
marked as our reconstruction, not the runbook's narrative.
Two classes remain, and their absence from incident trackers is itself information. Hot placement never appears in this corpus as a postmortem; it appears as engineering effort, twice, in opposite directions: CockroachDB building hash-sharded indexes because an index on a timestamp column "would have a hot spot" (the 2019 design issue that produced the USING HASH syntax), and the Postgres ecosystem building sequential and time-ordered UUIDs because unordered keys touch every leaf page. Our reading: placement failures degrade rather than break, so they get filed as performance work, not incidents, and you will not find them by searching outage reports. Leakage likewise: Mastodon's PR is a privacy fix shipped before any documented harm, RFC 9562 writes the rule ("Implementations SHOULD NOT assume that UUIDs are hard to guess ... they MUST NOT be used as security capabilities"), and uuidv47 exists because UUIDv7's own timestamp is a small leak. No public postmortem in reach of this session attributes a breach to enumerable IDs, and that is an open state, not evidence of safety: the well-known enumeration incidents live in reporting this session cannot fetch, so we name the gap rather than citing from memory.
Ceilings, rates and lifetimes, each with its source and date. These are spec and repository figures (measured or stated by the implementer), not vendor benchmarks.
| Metric | Value | At | Context | As of | Source |
|---|---|---|---|---|---|
| Signed int4 key ceiling | 2,147,483,647 | any SQL store | the number every unwidened key dies at | timeless | GitLab #215017 |
| smallint ceiling | 32,767 | GitLab.com | reached by an unbounded failure counter in production | 2021-05 | #330817 |
| ci_builds capacity consumed | 54.42% | GitLab.com | 1.168bn of 2.147bn, on the measurement in the tracker | 2021-04-10 | #215017 |
| Forecast runway at that point | 5–13 months | GitLab.com | exhaustion between Sept 2021 (exponential) and May 2022 (polynomial) | 2021-04 | #215017 |
| Snowflake mint requirement | 10,000/s/process, <2 ms | uncoordinated across data centres; k-sorted within ~1 s | 2010 | README | |
| Snowflake per-tick ceiling | 4,096/ms/worker | 12 sequence bits; 1,024 workers max (10 bits) | 2010 | README | |
| Sonyflake per-tick ceiling | 256/10 ms/instance | Sony | the price of 174 years of epoch and 65,536 machines in 64 bits | 2015 | README |
| Mastodon per-tick ceiling | 65,536/ms | Mastodon | 2 sequence bytes under a 6-byte ms timestamp, minted inside Postgres | 2017-10 | PR #4801 |
| KSUID size and lifetime | 20 bytes; ~100+ years | Segment | 32-bit timestamp from a 2014-05-13 epoch, 128 random bits, 27-char base62 text | 2017 | README |
| ULID timestamp horizon | year 10889 | ULID spec | 48-bit millisecond timestamp; 80 random bits; generation fails on same-ms overflow | 2016 | spec |
| UUIDv7 locality claim | ≥10× | RFC 9562 | "index locality vs random data inserts can be one order of magnitude or more"; a spec claim citing implementer experience, not a benchmark in the RFC itself | 2024-05 | RFC text |
| Postgres uuidv7 sub-ms counter | 12 bits | PostgreSQL | RFC method 3; monotonic within a backend even on backwards clocks | 2024-12-11 | commit 78c5e141 |
| Widening cost, offline path | hours to days | Nextcloud | "can take several hours or even days" for the filecache conversion | checked 2026-09 | admin manual |
| Widening cost, online path | ~3.5+ years | GitLab | helpers merged 2020-12; conversion cleanup still failing upgrades 2024-06 (calendar span of the programme, derived from the two artefacts' dates) | 2024-06 | #468671 |
Everything above is a stated spec limit, a repository measurement or a dated forecast; none of it is an independent benchmark. The RFC's "order of magnitude" is the spec summarising implementer experience, and the strongest quantified account of the mechanism (whole-index cache pressure from random keys) is the sequential-uuids README; the benchmark numbers behind it live on a blog host outside this session's reach. The "~3.5+ years" figure is derived by us from the dates of two GitLab artefacts and marks the programme's calendar span, not continuous effort on one table.
Every source behind this page, graded. Built from repository artefacts: this session's network reaches code hosts but not blog, paper or talk hosts, so those tiers are absent by constraint, not by judgement; the ledger in sources.md records the policy.
A production incident on GitLab.com: an unbounded increment walked a smallint to 32,768 and WebHookWorkers started throwing. Links the internal incident (gl-infra/production#4589) and shows the exact offending line.
Recurring production exceptions: imports and live issue creation both computed the next per-project iid and collided. The thread walks through routing all allocation through one InternalId mechanism or reserving blocks up front.
An operator's production report: oc_filecache.fileid outgrew an app table's int(10) unsigned copy of it, and file processing failed with SQLSTATE 22003. The core widened years earlier; the plugin's column lagged.
The curated postmortem index records three ID-exhaustion outages: Basecamp November 2018 ("a database hit the integer limit, leaving the service in read-only mode"), GitHub May 2021 (scoped-tokens foreign key at max INT32, "9h48m"), Strava July 2014 (signed-int primary key, uploads failed). The primary write-ups sit on hosts this session's network cannot reach; claims here are the collection's entries.
The near-miss, recorded in public: the largest CI table measured at 1.168bn IDs (54.42% of int4) in April 2021, with exhaustion forecast between September 2021 and May 2022 depending on the growth model.
The online widening machinery: shadow bigint column, trigger keeping both in sync, batched background backfill, then the swap; explicitly "DOWNTIME = false", covering the primary key and every referencing foreign key.
The long tail: a cleanup migration from the bigint programme raising PG::DependentObjectsStillExist in an upgrade test environment, three and a half years after the helpers merged.
The operator's view of duplicate issuance: unique-violation storms explained as a sequence that "has been modified (i.e. has been RESET)", remediated with setval() above the true maximum or absorbed with ON CONFLICT.
Why widening ships as an opt-in command: "changing column types on big tables could
take some time", so upgrades skip it and admins run
occ db:convert-filecache-bigint offline.
The official cost statement: conversion "can take several hours or even days, depending on the number of files", with the web server down or maintenance mode on.
The founding document of the node-local family: 10k IDs/s/process under 2 ms, uncoordinated, k-sorted within about a second, 64 bits; and the clock posture, refusing to mint until time passes the last issued ID.
The same 64 bits, re-budgeted: 39 bits of 10 ms ticks (174 years), 16 bits of machine (65,536 nodes), 8 bits of sequence (256 per 10 ms). A worked example of the bit-budget trade.
Time-ordered without coordination: 32-bit timestamp plus 128 random bits, "64 times larger than the 122 bits" of UUIDv4; 20 bytes binary, 27 characters of base62 that sort correctly as text.
The spec that names UUIDv4's problem ("can cause fragmentation in many data structures") and defines same-millisecond monotonicity by incrementing the random component, with generation failing on overflow.
A complete recorded argument: motivation ("purely to hide the total number of entries in each table"), dissent ("no perceivable benefit to >90% of instances"), scope cut to statuses, IDs returned as strings to protect JavaScript's 53-bit integer precision.
The UUID revision drafted in public on GitHub and published as RFC 9562 in May 2024, obsoleting RFC 4122 after 19 years.
Four load-bearing statements: the order-of-magnitude locality claim for time-ordered keys; three standardised monotonicity methods; "MUST NOT be used as security capabilities"; and a background cataloguing ~16 non-standard schemes the industry built first.
uuidv7() lands with RFC method 3: a 12-bit sub-millisecond fraction in rand_a, keeping generation monotonic within a backend "even when the system clock goes backward."
The clearest statement of the random-key problem: uniform distribution means "all index leaf pages are equally likely to be hit, forcing the whole index into memory"; plus two wrap-around generator designs that bound the damage without going fully sequential.
The systemic fix, merged: new Rails tables get 64-bit keys, with a migration compatibility layer so old migrations keep producing int and old schemas stay loadable.
Closed without merge after core maintainers showed the same migration would produce different schemas on Rails 4 and 5 and schema dumps would drift; superseded by #26266, which added the versioned compatibility layer.
The range-sharded world's view of ordered keys, argued by a founder: sequential keys cause "major performance problems", the fix exists but is not the default, and the cost of the fix is scan performance "proportional to the number of buckets". Still open.
The design argument that produced the USING HASH syntax: an index on a timestamp column "would have a hot spot", the manual workaround (computed hash column plus check constraint) "is starting to get heavy", so the database should "shoulder the burden of hashing" behind new syntax.
Both halves of the tension at once: "store sortable UUIDv7 in your database while emitting a UUIDv4-looking façade at your API boundary", by XOR-masking only the timestamp field with a keyed SipHash-2-4 stream.
Six rungs from an afternoon's query to the drill that would have caught every incident in section 4.
One query over your schema catalogue: for every int2/int4 key or counter, compute current max over type max and, from 30 days of growth, the projected exhaustion date. This is GitLab #215017 as a report you run monthly.
Done when: every table has a percentage and a date, and the worst one is on a dashboard. Teaches: exhaustion is a forecastable capacity problem, not a surprise.
Implement the 41/10/12 layout in ~50 lines. Then step the clock backwards under load and watch what your implementation does: duplicate, block, or refuse like Twitter's. Add the refusal, then add Postgres-style counter absorption, and measure the availability difference.
Done when: a 5-second clock step produces zero duplicates and you can state the write-outage cost. Teaches: the timestamp field is a distributed-systems dependency, not a convenience.
One table, one unique index, three key generators (bigserial, UUIDv4, UUIDv7), three sizes: index fits in shared buffers, fits in RAM, exceeds RAM. Chart insert throughput and WAL bytes per row.
Done when: your chart shows where random keys fall off, and by how much on your hardware. Teaches: why the RFC claims an order of magnitude, and why you saw nothing at small scale.
Load 100M rows with an int4 key, then convert to bigint using GitLab's recipe: shadow column, sync trigger, batched backfill, swap. Keep a write load running the whole time and measure its p99.
Done when: the swap completes with writes uninterrupted and you have a per-100M-rows duration to extrapolate from. Teaches: what "the fix takes years at scale" is made of.
Take every identifier your API exposes and answer three questions: can a stranger infer volume (sequential), infer creation time (timestamped), or walk the keyspace (dense)? Then put a masked or random public identifier in front of any answer you did not like, uuidv47-style.
Done when: the answers are written down per endpoint, and no authorisation decision anywhere rests on an ID being unguessable. Teaches: the ID is part of your public API's information surface.
In a replicated Postgres pair, kill the primary mid-load, promote, and immediately compare every sequence against its table's max(id). Automate the comparison as a post-failover check, then make the drill routine.
Done when: the check runs unprompted after promotion and a rigged lagging sequence is caught before the first duplicate-key error. Teaches: uniqueness is state, and state has to survive failover like everything else.
The queries that found this material, copyable. The productive move was searching error strings and tracker vocabulary rather than concept names.
site:gitlab.com gl-infra production "integer overflow" OR "out of range""out of range for type integer" production issue"convert-filecache-bigint" OR "bigint conversion" issuerepo:rails/rails is:pr bigint primary key default"duplicate key value violates unique constraint" sequence RESET runbooksite:gitlab.com "PG::UniqueViolation" iid import racesnowflake id "clock" "went backwards" refuse generate"hash-sharded indexes" sequential keys hotspot defaultrandom UUID "leaf pages" "shared buffers" index localityuuidv7 "index locality" "order of magnitude" rfc 9562repo:mastodon/mastodon is:pr snowflake non-serial idsietf-wg-uuidrev rfc4122bis monotonicity counter privacyis:pr is:closed is:unmerged primary key uuid defaultksuid OR ulid OR sonyflake README tradeoff epoch bits