If inserts into a Postgres table got slower as it grew and the primary key is a random UUID (v4), the key is a real suspect: random keys scatter B-tree writes across the whole index instead of concentrating them at one end. UUIDv7 and ULID fix that by putting a millisecond timestamp in the high bits, so new rows land next to each other. bigint identity is still the smallest and fastest option — it just leaks row counts and makes multi-writer ID generation someone else's problem.
This is the part of schema design that's cheap to get right on day one and genuinely expensive to change at row 400 million.
Why do random UUIDs slow down inserts as the table grows?
The symptom is unglamorous: insert latency that used to be flat starts creeping up, correlating with table size rather than traffic. Nothing shows up as a slow query, and EXPLAIN on the insert looks fine.
The mechanism is index locality. A B-tree index on a random value means each new row's key sorts into an essentially random leaf page. Once the index is bigger than the memory Postgres can keep it in, most inserts touch a leaf page that isn't in shared_buffers, so the write turns into a read first. That read is the actual cost.
There's a second-order effect that surprises people more. With full_page_writes on (the default), the first modification of a page after a checkpoint writes the entire page into the WAL, not just the row. Random inserts touch many distinct pages per checkpoint interval; sequential inserts keep hammering the same few. Same rows, meaningfully more WAL — which surfaces as replication lag and backup size, not query latency, so it gets diagnosed as a storage problem.
Random keys also age the index badly. Sequential inserts fill leaf pages to the fillfactor and move on. Random inserts land in already-full pages, splitting them, and the halves stay half-empty. The index ends up physically larger than its contents justify.
A random primary key doesn't make any single query slow; it makes the whole index stop fitting in cache sooner, which is much harder to spot.
How do you confirm it's the key and not something else?
Don't take my word for it — the diagnosis is three queries against your own database.
Start with how much of the index actually fits in memory, and how much dead space it's carrying:
-- index size vs table size
SELECT
relname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan
FROM pg_stat_user_indexes
JOIN pg_class ON pg_class.oid = indexrelid
WHERE relname LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
-- density of the primary key index (needs the pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, leaf_pages, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('events_pkey');
avg_leaf_density well below ~85% on an append-only table is the fingerprint of random-key page splits; a freshly built index on sequential keys sits near the B-tree fillfactor of 90%.
Then check whether the buffer cache is losing:
SELECT
sum(heap_blks_read) AS heap_read,
sum(heap_blks_hit) AS heap_hit,
round(100.0 * sum(idx_blks_hit) /
nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) AS idx_hit_pct
FROM pg_statio_user_tables;
If index hit percentage on a write-heavy table is drifting down over weeks while the working set hasn't changed shape, you're watching an index outgrow RAM.
Measure
avg_leaf_densityand index cache hit rate on your own table before rewriting a schema — key choice is worth changing when those two numbers say so, not on principle.
UUIDv7 vs ULID vs bigint: what actually differs?
bigint identity |
UUIDv4 | UUIDv7 | ULID | |
|---|---|---|---|---|
| Storage in Postgres | 8 bytes | 16 bytes (uuid) |
16 bytes (uuid) |
16 bytes as uuid, ~27 as text
|
| Insert locality | Sequential | Random | Sequential (ms granularity) | Sequential (ms granularity) |
| Generated by client? | No (needs the DB) | Yes | Yes | Yes |
| Sortable by creation time | Yes | No | Yes | Yes, also as a string |
| Standardized | SQL standard | RFC 9562 | RFC 9562 | Community spec, no RFC |
| Native Postgres type | Yes | Yes | Yes (uuid) |
No |
| Leaks | Row count, insert order | Nothing | Creation time (ms) | Creation time (ms) |
Two rows in that table decide most arguments.
The storage row is why bigint still wins on pure efficiency: 8 bytes versus 16 sounds trivial until you count every foreign key, every composite index that includes the key, and every index tuple's share of a page. Doubling key width on a schema with several FK columns per table is a real, permanent tax on how much of your database fits in cache.
The "generated by client" row is why people pay that tax anyway. If the ID has to exist before the row does — because the client creates it offline, because you're writing to several shards, because you want to build an object graph in memory and insert it in one round trip — then a database sequence is the wrong tool, and you're choosing among the UUID-shaped options.
Pick
bigintunless something in your architecture genuinely needs an ID before the insert; if it does, the choice narrows to UUIDv7 in almost every case.
Is ULID worth giving up the native uuid type?
ULID's distinguishing feature is its canonical text form: 26 characters of Crockford base32 that sort lexicographically in the same order as the underlying bytes. If IDs travel through systems that only handle strings — log lines, sorted keys in an object store, a URL path you want to eyeball chronologically — that's a genuine convenience UUID hex doesn't give you.
The cost in Postgres is that there is no ULID type. Store it as text and you pay ~27 bytes plus varlena overhead per index entry, and collation-aware comparisons instead of a fixed 16-byte memcmp. Store the same 128 bits in a uuid column and convert at the application boundary — the approach I'd take — and you've kept only the string formatting.
ULID also has no RFC behind it. UUIDv7 was standardized in RFC 9562 (2024) and now has first-class support arriving across ecosystems: PostgreSQL 18 ships a built-in uuidv7() function, and Python's standard library gained uuid.uuid7() in 3.14. As of mid-2026, choosing ULID means choosing the option with less platform support for a formatting benefit.
ULID is a reasonable choice when the ID's string form is part of your product surface; if it isn't, UUIDv7 gets you the same insert locality with a native type.
How do you generate UUIDv7 on the Postgres version you actually have?
On PostgreSQL 18 or newer it's built in:
CREATE TABLE events (
id uuid PRIMARY KEY DEFAULT uuidv7(),
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
On 13–17, generate it in the application (the uuid npm package and Python's uuid_utils both do v7), or add this function, which builds a valid v7 by overwriting the first 6 bytes of a v4 with a millisecond timestamp and flipping the version nibble to 0111:
CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
BEGIN
RETURN encode(
set_bit(
set_bit(
overlay(
uuid_send(gen_random_uuid())
PLACING substring(
int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint)
FROM 3
)
FROM 1 FOR 6
),
52, 1
),
53, 1
),
'hex'
)::uuid;
END
$$ LANGUAGE plpgsql VOLATILE;
gen_random_uuid() has been built in since PostgreSQL 13, so this needs no extensions. Verify it before trusting it — SELECT uuid_generate_v7(); twice a second apart should give you two values whose first hex characters are ascending.
What's the honest downside of sequential keys?
Everything monotonic concentrates writes on the rightmost leaf page of the index, and under high insert concurrency that page becomes a lock hotspot. This affects bigint sequences and UUIDv7 equally. In practice it's a problem at a scale where you're already thinking hard about write throughput, and it's the flip side of the cache benefit — you can't have locality without contention. If you hit it, that's when the random-key layout stops being a bug and starts being the design.
The other real cost is the timestamp: a UUIDv7 or ULID exposes its creation time to millisecond precision to anyone holding it. Fine for an internal events table. For a password reset token, or any ID where creation time is sensitive, use a random v4 — that's the case v4 is for, and it's why "always use v7" is bad advice.
FAQ
Should I migrate an existing UUIDv4 primary key to UUIDv7?
Usually not on its own. Changing a primary key type means rewriting the table and every foreign key referencing it, which is a lock-and-backfill project. Do it when the index-density and cache-hit numbers above show real degradation, or fold it into a migration you were already doing.
Is UUIDv7 slower to generate than UUIDv4?
No meaningfully. Both are dominated by the random-bytes call; v7 replaces 48 bits of randomness with a timestamp read. Generation cost is not the reason to choose between them.
Can I sort by primary key instead of by created_at with UUIDv7?
Within millisecond resolution, yes — UUIDv7 sorts by creation time. Rows created in the same millisecond have no defined order between them, so if you need a strict total order (cursor pagination, for instance), keep an explicit tiebreaker.
Bottom line
If your IDs can come from the database, use bigint identity — it's half the width and there's no clever alternative that beats it. If they can't, default to UUIDv7 stored in a native uuid column, using PostgreSQL 18's uuidv7() or the function above on older versions. Reach for ULID only when the 26-character sortable string is genuinely part of how your system works, and keep UUIDv4 for IDs where leaking a creation timestamp would be a problem. And before you migrate anything, run pgstatindex on the primary key you already have — the number either justifies the work or it doesn't.
Top comments (0)