Distributed ID Generation Explained: Snowflake IDs vs UUIDv7 vs ULID vs Database Sequences (2026)
A primary key looks like the most boring column in a schema, yet it decides how your index grows, how much write-ahead log you ship, what a stranger can learn from a URL, and whether a clock step at 3 a.m. creates duplicate rows. The UUIDv7 vs Snowflake ID debate is usually framed as 128 bits against 64 bits. That framing misses the real difference. Each scheme gets its uniqueness from a different place: a shared counter, an assigned worker number, or raw randomness. Each one fails in a different way. The question matters more in 2026 because UUIDv7 is now standardised in RFC 9562, built into PostgreSQL 18 and Python 3.14, and has become the default suggestion in code reviews. Defaults deserve scrutiny.
What this covers: the three sources of uniqueness, exact bit layouts for Snowflake, Instagram, Discord, Sonyflake, UUIDv7, ULID and KSUID, B-tree locality and WAL cost, worked collision and throughput math, clock rollback, two runnable Python generators, database support, security leaks, and a decision matrix.
Context and Background
For decades the default answer to “how do I number rows” was a database sequence: AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, SERIAL or GENERATED ... AS IDENTITY in PostgreSQL. A single authority hands out the next integer. It is compact, ordered and cheap, and it works as long as one database owns the table.
Sharding broke that assumption. Once rows for one logical table live on many nodes, or are created on clients before they ever reach a database, a single counter becomes a bottleneck or a single point of failure. Twitter hit exactly this wall in 2010 while moving from MySQL to Cassandra. Its original Snowflake README states the motivation plainly: “There is no sequential id generation facility in Cassandra, nor should there be.” The resulting design packed a timestamp, a machine number and a counter into 64 bits. Instagram, Discord, Sony, Mastodon and many others published variants.
In parallel, the UUID world evolved. RFC 4122 (2005) gave us version 1, built from a timestamp and a MAC address, and version 4, built from 122 random bits. Version 4 became the default distributed ID because it needs no coordination at all. Its randomness, however, is hostile to B-tree indexes. Community formats such as ULID (128 bits, 48-bit millisecond time plus 80 random bits) and Segment’s KSUID put the timestamp first so IDs sort by creation time.
The IETF folded these lessons into RFC 9562, published in May 2024. It obsoletes RFC 4122 and defines versions 6, 7 and 8. Its guidance is direct: “Implementations SHOULD utilize UUIDv7 instead of UUIDv1 and UUIDv6 if possible.” PostgreSQL 18, released on 25 September 2025, added a native uuidv7() function. Python 3.14 added uuid.uuid6(), uuid.uuid7() and uuid.uuid8(). .NET exposes Guid.CreateVersion7().
If you already read our deep dive on B-tree and B+ tree index internals, page splits and fill factor, you know why insert order matters. This post builds on that foundation and asks a different question: which ID scheme should own your primary key, and what does each one cost?
Where Uniqueness Comes From: A Reference Model for Distributed Unique ID Generation
Every distributed unique ID generation scheme gets its guarantee from one of three sources. Coordination means a shared counter, such as a database sequence. Allocation means each generator gets a unique number in advance, as Snowflake does with worker IDs. Entropy means enough random bits that collisions are negligible, as UUIDv4 and UUIDv7 do. Pick the failure mode you can operate.

Figure 1: Three sources of uniqueness. Each one moves the operational cost to a different place.
Figure 1 sorts the common schemes by where they get their guarantee. Coordination-based schemes put the cost in a shared counter that must stay available. Allocation-based schemes put the cost in assigning worker or shard numbers so no two generators share one. Entropy-based schemes put the cost in key width, because you need enough random bits to make collisions vanishingly rare. Time-ordered schemes like UUIDv7, ULID and Snowflake also put a timestamp in the high bits. That makes them sortable, and it makes them depend on a clock.
Coordination: correct by construction, limited by availability
A database sequence guarantees uniqueness because one process increments one counter. PostgreSQL’s documentation is precise about what you do and do not get. With CACHE greater than one, “you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.” Also, “because nextval and setval calls are never rolled back, sequence objects cannot be used if ‘gapless’ assignment of sequence numbers is needed” (PostgreSQL 18 CREATE SEQUENCE).
The coordination cost can be amortised. A service can lease a block of 1,000 or 10,000 IDs from a central counter and hand them out locally, often called hi/lo or segment allocation. Two counters with different offsets and the same increment, for example odd and even, give a crude form of high availability. Flickr’s engineering blog described this “ticket server” pattern on MySQL years ago (details not re-verified for this post). The trade-off never goes away. The central counter must be reachable when a block runs out, and IDs from different leases interleave out of creation order.
Allocation: 64 bits, but someone must hand out worker numbers
Snowflake removes the hot counter by giving each generator a unique worker ID and letting it count locally. Two generators can never collide because their worker bits differ. Within one generator, the timestamp and sequence never repeat as long as the clock never goes backwards. That is a strong guarantee from only 64 bits, and it fits in a native BIGINT.
The price is operational. Something must assign worker IDs and guarantee that no two live processes share one, even during a deploy, an autoscaling event or a network partition. Typical approaches are a lease in a coordination service such as ZooKeeper or etcd, a Kubernetes StatefulSet ordinal, or a static map in configuration. Sonyflake’s default takes the lower 16 bits of the host’s private IP address, which is convenient until two hosts in different subnets share those bits. A duplicate worker ID silently produces duplicate primary keys. That is the worst failure in this whole topic, and the format cannot detect it.
Entropy: no coordination, but you pay in width
This is the axis where UUIDv7 vs Snowflake ID is most often misread. UUIDv4 needs no coordination at all. It relies on 122 random bits. UUIDv7 keeps 74 bits of randomness, or fewer if an implementation spends some on a counter, and puts a 48-bit millisecond timestamp in front. ULID uses 80 random bits behind the same 48-bit timestamp. KSUID uses a 32-bit seconds timestamp and a 128-bit random payload.
The cost is width. A 16-byte key is twice the size of a BIGINT. That cost repeats in every secondary index that carries the primary key, every foreign key column, and every join. On the other hand, entropy IDs can be minted anywhere: in a browser, on an IoT gateway that is offline for a week, or in a batch job, without any registry. For edge and offline-first systems that alone often settles the UUIDv7 vs Snowflake ID question.
Bit Layouts Compared: Snowflake, Instagram, Discord, Sonyflake, UUIDv7, ULID and KSUID
The layouts look like trivia. They are not. Every field width is a decision about lifetime, throughput and fleet size, and the numbers below come straight from the primary sources.

Figure 2: The 64-bit Snowflake layout next to the 128-bit RFC 9562 UUIDv7 layout.
Figure 2 puts the two layouts side by side. Snowflake spends 41 bits on time, 10 on the worker and 12 on a per-millisecond sequence. The top bit stays zero so the value is a positive signed 64-bit integer. UUIDv7 spends 48 bits on Unix milliseconds, 4 on the version, 12 on rand_a, 2 on the variant and 62 on rand_b. Both put time first, which is what makes them sort roughly by creation time.
Snowflake and its descendants
Twitter’s original README describes the layout as “time – 41 bits (millisecond precision w/ a custom epoch gives us 69 years)”, “configured machine id – 10 bits – gives us up to 1024 machines” and “sequence number – 12 bits – rolls over every 4096 per machine”. The requirements were “minimum 10k ids per second per process”, a 2 ms response time and IDs that are “k-sorted … within a reasonable bound (we’re promising 1s, but shooting for 10’s of ms)”. Twitter’s epoch is 1288834974657 ms, which is 4 November 2010 at 01:42:54.657 UTC.
The arithmetic is worth doing once. Forty-one bits of milliseconds is 2^41 ms, about 69.7 years. From Twitter’s epoch that runs out in July 2080. One worker can issue 4,096 IDs per millisecond, or about 4.1 million per second. Across 1,024 workers that is about 4.2 billion per second. You will run out of network long before you run out of sequence space.
Instagram adapted the idea for sharded PostgreSQL. Its “Sharding & IDs at Instagram” post uses 41 bits of milliseconds, 13 bits of logical shard ID and 10 bits of sequence. The ID is generated inside the database by a PL/pgSQL function. It takes nextval() of a per-table sequence modulo 1024, shifts the time left by 23 bits and ORs in the shard. The shard ID replaces the worker ID, so allocation is solved by the sharding scheme itself. The cost is 1,024 IDs per shard per millisecond, and a dependency on the database being reachable.
Discord’s API reference documents a timestamp in bits 63 to 22 counted from the Discord epoch of 1420070400000 ms (the first second of 2015), a 5-bit internal worker ID, a 5-bit internal process ID and a 12-bit increment. Discord also returns IDs “as strings in the HTTP API to prevent integer overflows in some languages.” That detail matters. JavaScript’s Number is a double and represents integers exactly only up to 2^53, about 9.0 × 10^15. A Snowflake ID from 2026 is far larger, around 2 × 10^18, so it silently loses precision in a browser unless you send it as a string.
Sony’s Sonyflake trades throughput for lifetime and fleet size. By default it uses 39 bits of time in 10 ms units, 8 bits of sequence and 16 bits of machine ID. That gives about 174 years of life and 65,536 machines, but only 256 IDs per 10 ms per instance, which works out to 25,600 per second. Version 2 makes the widths and the time unit configurable and moves the default start time to 1 January 2025.
UUIDv7, ULID and KSUID
RFC 9562 defines UUIDv7 as a 48-bit big-endian Unix millisecond timestamp, then the 4-bit version 0111, then 12 bits called rand_a, then the 2-bit variant 10, then 62 bits of rand_b. Forty-eight bits of milliseconds lasts until roughly the year 10889, which PostgreSQL’s documentation also states as the valid range for uuidv7().
Section 6.2 of the RFC lets implementations trade some randomness for strict ordering within a millisecond. Method 1 places “a fixed bit-length counter … immediately after the embedded timestamp.” Method 2 treats the random field as a counter that is “incremented in the least significant position for each UUID created on a given timestamp tick.” Method 3 uses “additional clock precision available on the system to substitute for up to 12 random bits.” Python 3.14’s uuid7() uses “a 42-bit counter to guarantee monotonicity within a millisecond” (Python docs). PostgreSQL 18 computes the value from “UNIX timestamp with millisecond precision + sub-millisecond timestamp + random”, which is Method 3.
The ULID specification uses the same 48-bit millisecond prefix, followed by 80 random bits. It is written as 26 characters of Crockford Base32, which omits I, L, O and U to avoid misreading. For monotonic generation within a millisecond, the spec increments the random component by one and fails generation if it overflows. The key point in the ULID vs UUID comparison is that a ULID is not a valid RFC 9562 UUID. It has no version or variant bits, so it cannot be validated as a UUID, though it fits in a 16-byte UUID column.
KSUID, from Segment, is 20 bytes. A 32-bit timestamp in seconds from a custom epoch (13 May 2014) is followed by 128 random bits, written as 27 base62 characters. Second-level resolution means IDs created in the same second are not ordered by time, but 128 random bits make same-second collisions negligible.
The table below condenses the UUIDv7 vs Snowflake ID comparison and its neighbours into one view.
| Scheme | Size | Time field | Uniqueness from | Per-generator rate | Lifetime | Standard |
|---|---|---|---|---|---|---|
| DB sequence / IDENTITY | 8 bytes | none | central counter | limited by DB | effectively unlimited | vendor SQL |
| Twitter Snowflake | 8 bytes | 41 bits ms | worker ID + sequence | 4,096 per ms | about 69.7 years | none, published design |
| 8 bytes | 41 bits ms | shard ID + sequence | 1,024 per ms per shard | about 69.7 years | none, blog post | |
| Sonyflake default | 8 bytes | 39 bits, 10 ms units | machine ID + sequence | 256 per 10 ms | about 174 years | none, library |
| UUIDv4 | 16 bytes | none | 122 random bits | unbounded | n/a | RFC 9562 |
| UUIDv7 | 16 bytes | 48 bits ms | up to 74 random bits | unbounded | to about year 10889 | RFC 9562 |
| ULID | 16 bytes | 48 bits ms | 80 random bits | unbounded | to year 10889 | community spec |
| KSUID | 20 bytes | 32 bits s | 128 random bits | unbounded | over 100 years per README | community spec |
Walk-through: What the Database Actually Sees
The ID format matters most where it meets the index. This section works through B-tree locality, write-ahead log cost and collision probability with real numbers, then shows two generators you can run.
Random keys scatter writes across the whole index
A B-tree keeps keys sorted in fixed-size pages, 8 KB in PostgreSQL and SQL Server and 16 KB by default in InnoDB. A new key must go into the one leaf page that covers its value. With a time-ordered key, that is almost always the rightmost leaf. The hot set is one page per index, and it stays in memory. With UUIDv4, the target leaf is effectively random, so the hot set is the whole index.

Figure 3: Random keys touch any leaf. Time-ordered keys touch the rightmost leaf.
Figure 3 shows three costs that follow from scattered inserts. First, once the index outgrows memory, most inserts need a disk read before they can write. Second, PostgreSQL writes a full-page image to WAL the first time a page is modified after each checkpoint. That is 8 KB of log for a 16-byte key if the page has not been touched since. Third, when a full leaf splits in the middle, both halves are left about half full. Under random inserts, B-tree leaves settle at around 69% full on average, a result that goes back to Andrew Yao’s 1978 analysis of random 2-3 trees. Time-ordered inserts split the rightmost page and leave the left page as full as the fill factor allows.
Tomas Vondra measured this in PostgreSQL in his “Sequential UUID Generators” benchmark (EDB, November 2018). He ran four hours of single-column inserts with a unique index on deliberately slow spinning disks. When the data exceeded shared buffers but fit in RAM, random UUIDs reached 260 transactions per second against 840 for the sequential generators. That is under 30% of the throughput. They also produced “more than 20GB of WAL, vast majority of that due to FPIs” (full-page images), against about 2.5 GB for the time-based generator. When the data exceeded RAM, random UUIDs fell to about 20% of the sequential throughput. That was a single-hardware test from 2018, so treat the ratios as directional, not as a prediction for NVMe.
You can estimate the WAL effect for your own workload. Take a 10 GB index, which is about 1.31 million 8 KB leaf pages. Insert 5,000 random keys per second with the default five-minute checkpoint interval. That is 1.5 million inserts per cycle, touching about 1.31M × (1 − e^(−1.5/1.31)) ≈ 890,000 distinct pages. Each first touch logs a full page, so you get roughly 6.8 GB of full-page images per cycle for that one index. With time-ordered keys, the same inserts fill a few thousand new rightmost pages, a few tens of megabytes. These are illustrative numbers from a simple model, not a benchmark. The model ignores full-page-image compression and internal pages, but the order of magnitude is why UUIDv4 primary keys hurt replication lag.
The comparator decides whether an ID is sortable
Here is the distinctive trap that most UUIDv7 vs Snowflake ID comparisons miss. Time ordering is not a property of the ID alone. It is a property of the ID plus the comparison function the database uses. PostgreSQL’s uuid type compares bytes left to right, so a UUIDv7’s leading timestamp sorts first. MySQL stores UUIDs in BINARY(16) and also compares bytes left to right, as long as you do not use UUID_TO_BIN(..., 1). That swap flag reorders the time fields of version 1 UUIDs, and the MySQL manual notes that it only helps version 1.
SQL Server is the exception. Its uniqueidentifier comparison treats the last six bytes as the most significant. Microsoft’s SqlGuid documentation says it “treats the last 6 bytes as the most significant ones in comparisons.” A UUIDv7 puts its timestamp in the first six bytes, which SQL Server compares last. A September 2026 dbi services test, “Why UUIDv7 does not reduce fragmentation in SQL Server”, inserted 25,000 single rows into SQL Server 2022 with fill factor 100. UUIDv7 gave 568 pages at 67.9% fullness, essentially the same as NEWID() at 560 pages and 68.9%. NEWSEQUENTIALID() gave 391 pages at 98.7%. Note how close the random case is to Yao’s 69%. On SQL Server, a UUIDv7 in a uniqueidentifier column behaves like a random key. You either store it as BINARY(16), cluster on a separate BIGINT IDENTITY, or use NEWSEQUENTIALID() and accept its guessability.
The same trap appears elsewhere. Little-endian GUID serialisers in some client libraries swap the first three fields. Base64 strings do not sort in byte order. Text columns with a case-insensitive collation can reorder ULIDs. Check the ordering end to end, from generator to stored bytes to comparator.
Collision math: the birthday bound, worked
Collision risk is the other half of the UUIDv7 vs Snowflake ID trade. Snowflake cannot collide by chance, only by misconfiguration. UUIDv7 can collide by chance, but almost never. For entropy-based IDs, the useful approximation is the birthday bound. If you draw n values uniformly from 2^b possibilities, the probability of at least one collision is about n² / 2^(b+1), while that number stays small. For UUIDv4 with 122 random bits, a 50% chance of any collision needs about √(2 · 2^122 · ln 2) ≈ 2.7 × 10^18 IDs. That is a billion IDs per second for about 86 years.
UUIDv7 is different because only IDs in the same millisecond can collide. The timestamp separates everything else. Assume 74 random bits and n IDs per millisecond across the whole fleet. The collision probability per millisecond is about n² / 2^75.
- At 1,000 IDs per ms, one million per second: about 2.6 × 10^-17 per ms. Over a year of 3.15 × 10^10 ms, that is about 8 × 10^-7 expected collisions.
- At 100,000 IDs per ms, 100 million per second: about 2.6 × 10^-13 per ms, or about 0.008 expected collisions per year.
- At one million IDs per ms, a billion per second: about 0.8 expected collisions per year.
Two points follow. First, at realistic rates UUIDv7 collisions are not a practical concern, but they are not as remote as UUIDv4’s, because the time prefix compresses all competition into a single millisecond. Second, a counter changes the maths. If an implementation spends 12 bits on a counter (Method 1), only 62 random bits remain, and IDs from different generators in the same millisecond compete in a smaller space. At 100,000 per ms that is about 34 expected collisions per year if every generator started its counter at the same value. Random counter seeds help. Method 3, using real sub-millisecond clock bits, does not lose entropy if the clock is genuinely precise, because it splits each millisecond into 4,096 smaller buckets. These figures are worked examples, not measurements. Your real defence is still a unique constraint on the column.
Throughput and lifetime per node
Throughput is where UUIDv7 vs Snowflake ID looks most lopsided on paper, because UUIDv7 has no sequence field to exhaust. The same style of arithmetic sizes Snowflake. A 12-bit sequence caps one worker at 4,096 IDs per millisecond. A generator that hits the cap must wait for the next millisecond, so a burst of 10,000 IDs from one worker takes at least three milliseconds. Instagram’s 10-bit sequence gives 1,024 per shard per ms. Sonyflake’s default gives 25,600 per second per instance, so a service issuing 100,000 IDs per second needs four or more instances, and four or more machine IDs.
Lifetime is the other limit. With 41 bits of milliseconds, a fresh epoch chosen today runs out in 2096. Twitter’s runs out in 2080. Instagram’s code uses an epoch constant of 1314220021721, which is August 2011, so its IDs run out around 2081. Discord counts its timestamp from bit 63 to bit 22, which is 42 bits. Read literally, that gives about 139 years, although IDs only stay positive in a signed 64-bit integer until about 2084. Nothing is broken today. But an ID scheme quietly sets an expiry date on your data model, and it is better to write it down.
A Snowflake generator with clock-rollback handling
Clock behaviour is where most home-made Snowflake generators go wrong. Twitter’s README says: “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.” It also recommends running NTP in a mode that never steps the clock backwards.

Figure 4: The control flow of a safe Snowflake generator, including the clock-rollback branch.
Figure 4 shows the logic, and the code below implements it. A small backwards step is waited out. A large one raises an error, because silently continuing could reissue IDs that already exist. When the 12-bit sequence wraps within a millisecond, the generator spins until the next millisecond.
import threading, time
class ClockMovedBackwards(Exception):
pass
class Snowflake:
"""64-bit Snowflake-style IDs: 1 sign | 41 ms | 10 worker | 12 seq."""
EPOCH_MS = 1288834974657 # Twitter's epoch; pick your own
WORKER_BITS, SEQ_BITS = 10, 12
MAX_WORKER = (1 << WORKER_BITS) - 1
MAX_SEQ = (1 << SEQ_BITS) - 1
MAX_BACKWARD_WAIT_MS = 5 # tolerate tiny NTP slews
def __init__(self, worker_id: int):
if not 0 <= worker_id <= self.MAX_WORKER:
raise ValueError("worker_id out of range")
self.worker_id = worker_id
self.last_ms = -1
self.seq = 0
self.lock = threading.Lock()
@staticmethod
def _now_ms() -> int:
return time.time_ns() // 1_000_000
def next_id(self) -> int:
with self.lock:
now = self._now_ms()
if now < self.last_ms:
drift = self.last_ms - now
if drift > self.MAX_BACKWARD_WAIT_MS:
raise ClockMovedBackwards(f"clock moved back {drift} ms")
while now < self.last_ms: # small slew: wait it out
time.sleep(drift / 1000)
now = self._now_ms()
if now == self.last_ms:
self.seq = (self.seq + 1) & self.MAX_SEQ
if self.seq == 0: # 4096 used: spin to next ms
while now <= self.last_ms:
now = self._now_ms()
else:
self.seq = 0
self.last_ms = now
return ((now - self.EPOCH_MS) << 22) | (self.worker_id << 12) | self.seq
def decode(sid: int, epoch_ms=Snowflake.EPOCH_MS):
return {"ms": (sid >> 22) + epoch_ms,
"worker": (sid >> 12) & 0x3FF,
"seq": sid & 0xFFF}
if __name__ == "__main__":
g = Snowflake(worker_id=7)
ids = [g.next_id() for _ in range(100_000)]
assert len(set(ids)) == len(ids) and ids == sorted(ids)
print(ids[0], decode(ids[0]))
Three details deserve attention. The lock makes the generator safe within a single process, but two processes with the same worker_id will still collide. Persisting last_ms across restarts closes the gap where a process restarts and the clock has stepped back during the restart. In a fleet, the error branch should page someone. A host that cannot issue IDs is better than a host that issues duplicates.
A UUIDv7 generator using RFC 9562 Method 1
On Python 3.14 or later, uuid.uuid7() is built in. For older runtimes, or to see the mechanism, here is a compact generator that uses a 12-bit counter in rand_a. The counter is seeded randomly each millisecond, which reduces the cross-generator collision issue described above. If the clock steps back, the generator keeps using the last timestamp, which preserves ordering.
import os, time, threading, uuid
class UUIDv7:
"""RFC 9562 UUIDv7 with Method 1: a 12-bit counter in rand_a,
seeded randomly each new millisecond; 62 random bits in rand_b."""
def __init__(self):
self.last_ms = -1
self.counter = 0
self.lock = threading.Lock()
def new(self) -> uuid.UUID:
with self.lock:
ms = time.time_ns() // 1_000_000
if ms > self.last_ms:
self.counter = int.from_bytes(os.urandom(2), "big") & 0x7FF # top bit 0 leaves headroom
self.last_ms = ms
else: # same ms or clock went back
self.counter += 1
if self.counter > 0xFFF: # counter exhausted:
self.last_ms += 1 # borrow the next ms
self.counter = 0
ms = self.last_ms # never emit a smaller timestamp
rand_b = int.from_bytes(os.urandom(8), "big") & ((1 << 62) - 1)
value = (ms & ((1 << 48) - 1)) << 80
value |= 0x7 << 76 # version 7
value |= self.counter << 64 # rand_a used as counter
value |= 0b10 << 62 # RFC variant
value |= rand_b
return uuid.UUID(int=value)
if __name__ == "__main__":
g = UUIDv7()
us = [g.new() for _ in range(100_000)]
assert all(u.version == 7 for u in us)
assert [u.int for u in us] == sorted(u.int for u in us)
print(us[0], us[0].int >> 80)
Both scripts were run on Python 3.13 while writing this post, and both assertions held for 100,000 IDs. Note the contrast in how they handle a clock step. The Snowflake generator refuses to continue. The UUIDv7 generator keeps going by borrowing future timestamps. Borrowing is safe for UUIDv7 because the 62 random bits still make IDs unique across generators. Snowflake has no random bits, so reusing a timestamp could reproduce an ID that already exists.
UUID v7 in Postgres and other databases
PostgreSQL 18’s UUID functions include uuidv7([shift interval]), uuidv4() as an alias for gen_random_uuid(), uuid_extract_timestamp() for versions 1 and 7, and uuid_extract_version(). A UUID v7 Postgres primary key is therefore one line:
CREATE TABLE events (
id uuid PRIMARY KEY DEFAULT uuidv7(),
device_id bigint NOT NULL,
payload jsonb
);
SELECT id, uuid_extract_timestamp(id) AS created_at
FROM events
ORDER BY id DESC
LIMIT 10;
According to Nile’s write-up of the feature, the 12-bit sub-millisecond fraction guarantees monotonicity “for all UUIDv7 values generated by the same Postgres session.” It does not guarantee monotonicity across sessions or across nodes. The optional shift argument moves the embedded timestamp, which is useful for backfills but means the embedded time is not always the insert time.
Elsewhere, MariaDB documents UUID_v7() from version 11.7. MySQL’s UUID() still returns version 1. The MySQL 9.7 manual lists no v7 function, and a verified 2026 feature request on bugs.mysql.com asks for one, so MySQL users generate UUIDv7 in the application and store it as BINARY(16). SQL Server has NEWID() (random) and NEWSEQUENTIALID(). Microsoft’s documentation says the latter can be used only in a DEFAULT constraint, can restart “from a lower range” after Windows restarts, and should be avoided if privacy matters because “it’s possible to guess the value of the next generated GUID.”
Trade-offs, Gotchas, and What Goes Wrong
Whichever side of UUIDv7 vs Snowflake ID you choose, most ID incidents are not collisions from randomness. They are operational mistakes that the format cannot detect.
Duplicate worker IDs. Two Snowflake processes with the same worker ID produce identical IDs whenever they generate in the same millisecond with the same sequence value. Typical causes are a blue-green deploy that briefly runs two copies of the same StatefulSet ordinal, a container image with a hard-coded ID, or IP-derived IDs after a network change. Use a lease with a TTL, and check it on startup.
Clock steps. A virtual machine resumed from a snapshot, a leap-second smear configured differently on different hosts, or an NTP correction can move wall-clock time backwards. A Snowflake generator that ignores this can reissue IDs. A UUIDv7 generator without monotonic handling may emit out-of-order IDs. That breaks no constraint, but it does break “newer IDs sort later” assumptions in cursor pagination. For a broader treatment of ordering without trusted clocks, see our post on hybrid logical clocks and vector clocks for event ordering.
Time-ordered is not causally ordered. Snowflake promised k-sorting “within a reasonable bound (we’re promising 1s…)”. Two IDs from different machines a few milliseconds apart can sort in either order. Do not use ID order to decide which write happened first across nodes.
Information leaks. RFC 9562 says “Implementations SHOULD NOT assume that UUIDs are hard to guess” and that UUIDs “MUST NOT be used as security capabilities.” A UUIDv7 or Snowflake ID reveals the creation time to the millisecond. A Snowflake ID also reveals the worker and how many IDs it issued in that millisecond. Sequential integers reveal your total volume, the classic “German tank problem.” Never let an ID be the only thing protecting a resource.
Hot right-hand pages. The locality that helps caching also concentrates every insert on one leaf page. On very high-concurrency single-table inserts this becomes a latch hotspot. SQL Server added OPTIMIZE_FOR_SEQUENTIAL_KEY for exactly this. In sharded systems, a time-ordered key used as a range-shard key sends all new writes to one shard. Hash the shard key, as discussed in our guide to database sharding with consistent hashing and resharding.
JSON precision. A 64-bit Snowflake ID loses precision in JavaScript above 2^53. Send it as a string, as Discord does.
Practical Recommendations
Start from the failure mode you can operate, not from the format you like. If a single PostgreSQL or MySQL primary owns the table and will for the foreseeable future, a BIGINT identity column is still the cheapest key. It is 8 bytes, ordered, and needs no new infrastructure. Add a separate random public identifier if you expose rows in URLs.
If IDs must be created on many nodes, on clients or offline, UUIDv7 is the sensible default in 2026. It is a standard, it needs no registry, and it indexes well in PostgreSQL and MySQL. In PostgreSQL 18, use the native function. On SQL Server, test before assuming it helps, because of the comparator issue described above.
Choose Snowflake when the 8-byte width really matters. Examples include trillions of rows, many secondary indexes, foreign keys everywhere, or a public API that already promises 64-bit integers. Only do this if you can run reliable worker-ID leasing and clock monitoring. The UUIDv7 vs Snowflake ID decision is really a decision about whether you would rather pay for 8 extra bytes per key or for a coordination service.
Choose ULID when you need a short, human-friendly, case-insensitive string and your stack already supports it. For new systems, UUIDv7 offers the same sort order with standard validation.
- Benchmark UUIDv7 vs Snowflake ID on your own engine and hardware before migrating a large table.
- Put a
UNIQUEorPRIMARY KEYconstraint on every generated ID, whatever the theory says. - Store UUIDs as native
uuidorBINARY(16), never as 36-character text. - Confirm sort order end to end: generator, serialiser, column type, collation, comparator.
- For Snowflake, lease worker IDs with a TTL and alert on any clock step backwards.
- Write down your epoch and the date your timestamp field runs out.
- Never use an ID as an access token. Use a separate random secret.
Frequently Asked Questions
Is UUIDv7 better than Snowflake ID?
Neither is better in general. UUIDv7 is a 128-bit IETF standard (RFC 9562) that needs no coordination, because uniqueness comes from up to 74 random bits behind a millisecond timestamp. A Snowflake ID is 64 bits and fits in a BIGINT, but every generator needs a unique worker ID and a clock that does not go backwards. Choose UUIDv7 for client-side, offline or multi-team generation. Choose Snowflake when halving key width saves real storage and you can operate worker-ID leasing reliably.
Does PostgreSQL support UUIDv7 natively?
Yes. PostgreSQL 18, released on 25 September 2025, added uuidv7(). It takes an optional interval argument that shifts the embedded timestamp. It also added uuidv4() as an alias for gen_random_uuid(), and uuid_extract_timestamp() now works for version 7. The implementation fills 12 bits after the millisecond timestamp with a sub-millisecond fraction, which keeps values monotonic within one session. On PostgreSQL 17 or earlier, generate UUIDv7 in the application or use an extension. The uuid column type is the same either way.
What is the difference between ULID and UUID?
A ULID is 128 bits: a 48-bit millisecond timestamp followed by 80 random bits, written as 26 Crockford Base32 characters. A UUIDv7 has the same timestamp width but spends 6 bits on version and variant markers, leaving up to 74 random bits. It is written in the standard 36-character hyphenated hex form. Both sort by creation time. The practical difference is standardisation. UUIDv7 is an RFC with native database and language support. A ULID is a community spec that is not a valid RFC UUID, though it fits a 16-byte column.
Can UUIDv7 values collide?
In theory yes, in practice almost never. Only IDs created in the same millisecond can collide, because the timestamp separates the rest. With 74 random bits and 100,000 IDs per millisecond across a fleet, the birthday bound gives about 0.008 expected collisions per year. At a billion IDs per second it approaches one per year. Implementations that spend random bits on a counter reduce the margin. Always keep a unique constraint on the column so a collision fails loudly and can be retried, instead of corrupting data.
Why do random UUIDs slow down database inserts?
A B-tree must place each key in the leaf page that covers its value. Random keys land on any page in the index, so once the index is larger than memory, most inserts trigger a disk read. In PostgreSQL, the first change to each page after a checkpoint also writes an 8 KB full-page image to WAL. Random splits leave pages about 69% full on average. In a 2018 EDB benchmark, random UUIDs fell to under 30% of sequential throughput once data exceeded shared buffers.
How does a Snowflake ID generator handle clock rollback?
The original Twitter design refuses to generate IDs until the clock passes the last timestamp it used. It recommends configuring NTP to slew rather than step backwards. A practical generator waits out small regressions of a few milliseconds and raises an error for larger ones, so an operator can investigate. Continuing silently is dangerous, because a reused timestamp with the same worker and sequence reproduces an ID that already exists. Persisting the last-used timestamp also protects against clock steps during a restart.
Further Reading
- B-tree and B+ tree index internals: page splits, fill factor and fan-out — the index mechanics that make time-ordered keys cheaper to insert.
- Database sharding with consistent hashing, rendezvous hashing and resharding — why a time-ordered ID should rarely be your range-shard key.
- Hybrid logical clocks and vector clocks for event ordering in distributed IoT — how to order events when wall clocks cannot be trusted.
- RFC 9562: Universally Unique IDentifiers (UUIDs), IETF, May 2024.
- Tomas Vondra, “Sequential UUID Generators”, EDB, November 2018.
By Riju — about
