Short answers to what readers ask most about this topic.
How to generate unique IDs across many servers: auto-increment limits, UUIDv4 index damage, UUIDv7 under RFC 9562, Snowflake's 41/10/12 layout, clock rollback and block allocation.
Matthews Wong
Software Engineer
Key Takeaway
Use UUIDv7 for uncoordinated, time-ordered 128-bit IDs, or a Snowflake ID when you need compact 64-bit keys. A Snowflake packs 41 bits of timestamp, 10 of worker ID and 12 of sequence, so one worker issues 4,096 IDs per millisecond. UUIDv4 fragments B-tree indexes and auto-increment needs a single coordinating writer.
A schema that starts on bigserial works perfectly right up to the day a second writer appears. Picture an ERP or POS system where a mobile field app must create records while offline, or a second database node takes writes: now two machines both want to hand out the next number, and a counter that lives in one place stops being an answer.
This post answers the question people actually search: how do you generate unique IDs in a distributed system? It compares auto-increment, UUIDv4, UUIDv7, ULID and Snowflake, derives the capacity of each layout with the arithmetic shown, and includes a TypeScript Snowflake generator you can run. Every specification number comes from RFC 9562, the PostgreSQL documentation, the ULID spec or Wikipedia, linked at the end.
Auto-increment is the right default for a single database, and it is the smallest and fastest key you can have. It stops being enough for three reasons. A sequence is one counter in one place, so two independent writers must either share it or be given separate ranges. A 32-bit counter runs out sooner than people expect. And sequential values leak how many rows you have: an invoice numbered 48,213 tells a competitor roughly how many you have issued.
The PostgreSQL documentation gives the integer range as 2147483647 at the top, and bigint reaches 9223372036854775807. The worked example below shows why the difference matters on a busy table. Bigint solves the exhaustion problem but not the single-counter problem, which is the one that distribution actually creates.
# How long does a 32-bit auto-increment last?
integer max = 2,147,483,647
insert rate = 1,000 rows/s
seconds until overflow = 2,147,483,647 / 1,000 = 2,147,483 s
days until overflow = 2,147,483 / 86,400 = about 24.9 days
# bigint max = 9,223,372,036,854,775,807
# At the same rate that is about 9.2e15 s, so exhaustion stops being the problem.
# The single shared counter does not go away, though.UUIDv4 solves coordination completely: any machine can generate one with no contact with any other. The cost is paid at the index. RFC 9562 explains that non-time-ordered versions such as UUIDv4 cause random inserts and poor B-tree locality, so each new row lands on an arbitrary leaf page instead of the rightmost one. The working set of the index becomes the whole index, not its hot edge.
The key is also twice the width. As a raw value, 100 million rows cost 100,000,000 x 8 bytes = 800 MB of key data for a bigint and 100,000,000 x 16 bytes = 1.6 GB for a UUID, before any page overhead, and every secondary index repeats the primary key. That is arithmetic on key width, not a benchmark; the RFC says the speed difference between random and time-ordered inserts can be an order of magnitude or more, which is the figure to cite.
Store UUIDs in a native uuid column, not as a 36-character string. RFC 9562 recommends the 128-bit binary form where feasible, and the string form more than doubles the key size.
RFC 9562, published in May 2024, obsoletes RFC 4122 and defines UUIDv7 in section 5.7. The first 48 bits are a big-endian Unix timestamp in milliseconds, followed by the 4-bit version, 12 bits of rand_a, the 2-bit variant and 62 bits of rand_b. That leaves 12 + 62 = 74 bits that can be random, or can hold a sub-millisecond fraction or a counter. Because the timestamp leads, UUIDv7 values sort in creation order as plain bytes, which is what restores B-tree locality.
The RFC says that a single node only needs to make sure the timestamp advances before each new UUID, and describes counters for batch generation. Strict monotonicity inside one millisecond is therefore an implementation choice, not something the format promises. PostgreSQL 18 ships a built-in uuidv7() function: it appears in the version 18 documentation and not on the version 17 page. Its documentation describes the result as time-ordered and says nothing about monotonicity, so do not build on a stronger guarantee than that.
-- UUIDv7 layout, RFC 9562 section 5.7 (128 bits)
-- 48 bits unix_ts_ms milliseconds since 1970, big-endian
-- 4 bits ver 0111
-- 12 bits rand_a random, or sub-ms fraction, or counter
-- 2 bits var 10
-- 62 bits rand_b random
-- random-capable bits = 12 + 62 = 74
-- PostgreSQL 18 or later: built in
CREATE TABLE invoices (
id uuid PRIMARY KEY DEFAULT uuidv7(),
total_idr bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
SELECT uuidv7(); -- time-ordered uuid
SELECT uuidv7(interval '-1 hour'); -- optional shift argument
-- PostgreSQL 17 and earlier: no uuidv7(); generate it in the application
-- and pass the value in the INSERT, keeping the native uuid column type.ULID is the older cousin. It uses a 48-bit millisecond timestamp and 80 bits of randomness, rendered as a 26-character Crockford base32 string, and its spec describes a monotonic mode that increments the random part within the same millisecond and fails if that part overflows. Pick it when you want a short sortable string in a URL; pick UUIDv7 when you want a native uuid column and standard tooling.
A Snowflake ID is a 64-bit integer: one sign bit that is always 0, 41 bits of milliseconds since a chosen epoch, 10 bits of machine ID and 12 bits of per-machine sequence. Twitter's original library split the 10 bits into a 5-bit data centre and a 5-bit worker, but the layout is a convention you can adjust as long as every generator agrees. The Wikipedia article lists the original epoch as 1288834974657, and nothing stops you picking your own.
The capacity follows from the field widths. The code block shows the three numbers worth knowing: how long the timestamp lasts, how fast one worker can go, and how fast the whole fleet can go. Choosing a recent custom epoch is free and buys back the years you would otherwise spend counting from 1970.
# Snowflake: 1 + 41 + 10 + 12 = 64 bits
sign 1 bit always 0, so the value fits a signed bigint
timestamp 41 bits ms since your epoch
worker 10 bits 2^10 = 1,024 generators
sequence 12 bits 2^12 = 4,096 IDs per ms per generator
# Lifetime of the timestamp field
2^41 ms = 2,199,023,255,552 ms
/ 31,557,600,000 ms per year (365.25 days)
= about 69.7 years from the epoch
# Throughput
per worker : 4,096 per ms x 1,000 = 4,096,000 IDs/s
whole fleet: 4,096,000 x 1,024 = 4,194,304,000 IDs/s
# Largest possible value is 2^63 - 1 = 9,223,372,036,854,775,807.
# A JavaScript Number is exact only to 2^53 - 1 = 9,007,199,254,740,991.The hard part is not the bit shifting but the worker ID. Two generators sharing one worker ID will eventually collide. On a single VPS running several Docker containers, a WORKER_ID environment variable per container is enough; across a fleet you need a registry such as a database row, or a lease that expires, that hands each process a number nobody else holds.
A Snowflake generator trusts the clock, so the clock is its failure mode. If NTP steps the time backwards, the generator can re-issue a millisecond it has already used, with the same worker and sequence, and that is a duplicate key. RFC 9562 is candid that implementations must decide what to do when the system clock moves backward, and lists reusing the previous timestamp and incrementing the counter, or reporting an error, as the sensible options.
const EPOCH = 1767225600000n; // 2026-01-01T00:00:00Z, our own epoch
const WORKER_BITS = 10n;
const SEQ_BITS = 12n;
const MAX_WORKER = (1n << WORKER_BITS) - 1n; // 1023
const SEQ_MASK = (1n << SEQ_BITS) - 1n; // 4095
const MAX_BACKWARD_MS = 5n; // tolerate small NTP steps, refuse anything bigger
export class Snowflake {
private lastMs = -1n;
private seq = 0n;
private readonly workerId: bigint;
private readonly now: () => bigint;
constructor(workerId: bigint, now: () => bigint = () => BigInt(Date.now())) {
if (workerId < 0n || workerId > MAX_WORKER) {
throw new RangeError("workerId must be 0..1023");
}
this.workerId = workerId;
this.now = now; // injectable so the rollback path is testable
}
next(): bigint {
let ms = this.now();
if (ms < this.lastMs) {
const behind = this.lastMs - ms;
// Wrong: issue IDs anyway - they can collide with ones already handed out.
// Right: wait out a small step, fail loudly on a large one.
if (behind > MAX_BACKWARD_MS) throw new Error("clock moved back " + behind + " ms");
while (ms < this.lastMs) ms = this.now();
}
if (ms === this.lastMs) {
this.seq = (this.seq + 1n) & SEQ_MASK;
if (this.seq === 0n) {
// 4096 IDs used in this millisecond: spin until the next one.
while (ms <= this.lastMs) ms = this.now();
}
} else {
this.seq = 0n;
}
this.lastMs = ms;
return ((ms - EPOCH) << (WORKER_BITS + SEQ_BITS)) | (this.workerId << SEQ_BITS) | this.seq;
}
}
// Trace with a fake clock: worker 7, one second after the epoch.
// first ID = (1000 << 22) | (7 << 12) | 0 = 4194304000 + 28672 = 4194332672
// second ID = 4194332673 (same ms, sequence 1)
// Send these to browsers as strings: JSON.stringify cannot serialise a BigInt.
const gen = new Snowflake(7n, () => EPOCH + 1000n);
console.log(gen.next().toString(), gen.next().toString());The generator below makes that decision explicit. A small step of up to 5 ms is waited out; anything larger throws, because silently issuing IDs from a clock you cannot trust is worse than a failed request. When the 4,096-value sequence is used up inside one millisecond it spins until the next one, which caps one worker at 4,096 IDs per millisecond rather than ever repeating one.
Also persist the last issued timestamp somewhere if a restart can land in the past. A process that restarts after a clock correction starts with lastMs at -1 and has no memory of the IDs it issued a moment earlier.
If you want small, strictly increasing integers without Snowflake's worker-ID bookkeeping, centralise the counter but amortise the cost. A ticket server is a database row that hands out numbers; block allocation improves on it by handing out a whole range at once, so each application instance reserves, say, 1000 IDs in one round trip and then issues them from memory.
-- One row per ID space
CREATE TABLE id_blocks (
name text PRIMARY KEY,
next_id bigint NOT NULL
);
INSERT INTO id_blocks VALUES ('invoice', 1);
-- Reserve 1000 IDs in ONE atomic statement; concurrent callers serialise on the row.
UPDATE id_blocks
SET next_id = next_id + 1000
WHERE name = 'invoice'
RETURNING next_id - 1000 AS block_start; -- caller owns block_start .. block_start + 999
-- First call returns 1 (owns 1..1000), second returns 1001 (owns 1001..2000).
-- The application hands these out from memory and reserves a new block when it runs dry.The arithmetic is the argument. At 1,000,000 inserts a day, asking for one ID each time costs 1,000,000 round trips; reserving blocks of 1000 costs 1,000,000 / 1000 = 1,000. The price is paid in two places: a crashed instance wastes up to 999 unused IDs, which is harmless because gaps are fine, and IDs from different instances interleave rather than strictly ascend, so you get roughly sorted rather than perfectly sorted keys.
The remaining weakness is the allocator itself, a single point of failure on the write path. Run it on the same Postgres you already operate and keep the reservation a single atomic statement, and it is a simple, boring and defensible design for a system that is still one database.
Decide on three things: whether you need a 64-bit key, whether you can tolerate any coordination, and how much sort order matters. The table summarises the schemes discussed above using only the properties derived or cited in this post.
| Scheme | Key size | Sorts by creation time | Coordination needed | Main risk |
|---|---|---|---|---|
| Auto-increment bigint | 8 bytes | Yes, strictly | One shared counter | Single writer, leaks row counts |
| UUIDv4 | 16 bytes | No | None | Random inserts, poor B-tree locality |
| UUIDv7 | 16 bytes | Yes, by millisecond | None | Order inside one millisecond is up to the implementation |
| ULID | 16 bytes, 26 characters as text | Yes, monotonic mode available | None | Not a native uuid type, so less database tooling |
| Snowflake | 8 bytes | Yes, by millisecond | Unique worker ID per generator | Clock rollback, duplicate worker IDs |
For most Postgres-backed applications on a single VPS, a bigint sequence is still correct and UUIDv7 is the upgrade path when IDs must exist before the row reaches the database. Reach for Snowflake only when 64 bits matter, for example because the ID travels through a JavaScript client, where a plain Number is exact only up to 9007199254740991 and a 63-bit ID must be sent as a string.
The rule I carry from this: coordination is the cost you are choosing between. A sequence pays it on every insert, block allocation pays it once per block, Snowflake pays it once per process in worker IDs, and UUIDv7 pays none of it but gives up 8 bytes of key width. Pick the scheme whose cost you can actually afford, and write the clock-rollback policy down before the first duplicate teaches it to you.
Sources and references