Your Primary Key Is a Bottleneck (And Scaling Won't Fix It)

Here’s a question I like asking backend engineers:

“Your PostgreSQL instance on RDS is hitting a write throughput ceiling at peak traffic. Walk me through how you’d approach scaling it horizontally.”

The answer I usually get is the textbook one. Read replicas for read-heavy queries. PgBouncer for connection pooling. Maybe Aurora if the budget allows. Citus for sharding. Bigger instance class as a stopgap.

All reasonable. All missing a question that comes first: is your primary key strategy creating a contention pattern that no amount of hardware will fix?

This post is about that pattern — rightmost-leaf contention on monotonic keys. And because this topic is drowning in folklore, it holds itself to the same standard as the rest of this blog: every number below comes from real runs — PostgreSQL 18.4 in a podman container (6 CPUs, shared_buffers=512MB, synchronous_commit=off — more on that choice later), pgbench with 40 concurrent clients doing single-row inserts into tables pre-seeded with 3 million rows. The harness is at the bottom; the results are less tidy than the folklore, which is exactly why they’re worth publishing.

The Problem Everybody Describes and Nobody Measures

#

Every tutorial, every ORM, and every “getting started with PostgreSQL” guide tells you to do this:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

Looks innocent. BIGSERIAL gives you a nice, clean, auto-incrementing integer. Small (8 bytes), fast to compare, natural to paginate with. Every INSERT gets the next number in the sequence.

But a B-tree stores keys in sorted order across leaf pages, and when keys are monotonically increasing — 1, 2, 3, 4, … — every new key is larger than every existing key. Every insert targets the rightmost leaf page of the index.

PostgreSQL is actually smart about this. The B-tree code has a “fastpath” optimisation (nbtinsert.c, _bt_search_insert): each backend caches the block number of the rightmost leaf so it doesn’t walk the tree from the root on every insert. Efficient — and under concurrency, that efficiency concentrates the fight. To insert a tuple, a backend needs an exclusive content lock on that buffer page, and only one backend can hold it at a time. Forty backends inserting means thirty-nine waiting, in a queue, for one 8KB page.

That’s the theory. Here’s what it looks like on a real box.

Watching It Happen

#

During a 20-second pgbench run against the BIGSERIAL table (40 clients, ~183,000 inserts/sec), I sampled pg_stat_activity twice a second and counted what the active backends were waiting on:

    321 LWLock:BufferContent
     62 Client:ClientRead
     20 LWLock:XidGen
      8 IPC:ProcarrayGroupUpdate

LWLock:BufferContent — “waiting to access a data page in memory” — dominates. At any sampled moment, roughly a third of the 40 backends were queued on a page lock.

Which page? pg_buffercache can tell you, if you catch it mid-run. Snapshot taken 5 seconds into the run, filtered to the PK index:

 hot_block | pinning_backends | max_block
-----------+------------------+-----------
     31863 |                9 |     31875
     31864 |                6 |     31875

The two hottest pages in a 31,875-block index are blocks 31,863 and 31,864 — the extreme right edge. That’s the rightmost leaf (and its freshly-split successor), photographed with nine backends pinning it simultaneously. Not a theory: a block number.

Two notes on those diagnostics, because the versions of these queries floating around the internet (including an earlier version of this post) have gaps. pg_buffercache needs a reldatabase filter — relfilenode values collide across databases — and pinning_backends is a proxy (pins, not lock waits) that changes every microsecond, so both queries must be run in a loop during load. A single idle snapshot will show you nothing and tell you you’re fine.

The Part the Folklore Skips: Throughput

#

So BIGSERIAL spends a third of its time queued on one page. The folklore says this serialises your writes and caps your throughput — that’s the whole premise of the “never use sequential keys” advice. Here’s the same 20-second run, all five table variants, same box:

* two runs; ~10% run-to-run variance on this box, so treat single-digit-percent differences as noise.

Read that table carefully, because it says something the folklore doesn’t:

• The contention is real. The wait-event fingerprint tracks the key strategy exactly as the theory predicts: fully-random v4 makes BufferContent vanish, hash partitioning (8 independent rightmost leaves) cuts it to a third, v7 sits near BIGSERIAL.
• The throughput collapse isn’t — at this scale. Every variant lands within noise of 200k inserts/sec, except UUID v7, which is the slowest — the contention it removed was worth less than the 16-byte keys and WAL volume it added.
• At 40 clients on 6 cores, the rightmost-leaf queue is a visible fingerprint, not yet a ceiling. The queue costs you when concurrency rises far beyond cores — hundreds of backends, bigger boxes — because lock queues degrade nonlinearly while page spreads don’t.

One honesty note on the harness: synchronous_commit=off. With it on, every one of these runs bottlenecks on WAL flush (IO:WALSync) and the index pattern barely matters — which is itself the single most important diagnostic fact in this post, so let’s make it step zero.

Diagnosing: Rule Out the Boring Bottleneck First

#

Before you blame your primary key, check what your backends are actually waiting on during peak writes:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;

Run it in a loop during load (\watch 0.5 in psql). Then triage:

• IO:WALSync, LWLock:WALWrite, IO:DataFileWrite on top? You’re I/O-bound — commit latency, WAL flush, disk. On RDS this is the overwhelmingly common ceiling, it’s what Performance Insights will show you, and no key strategy will move it. Stop here; this post isn’t your problem.
• LWLock:extend? Relation extension — the heap growing, a frequent co-symptom of heavy append that also has nothing to do with key choice.
• LWLock:BufferContent on top, sustained? Now find the page. CREATE EXTENSION pg_buffercache; and, again in a loop during load:

SELECT c.relname, b.relblocknumber, b.pinning_backends,
       (pg_relation_size(c.oid) / 8192) - 1 AS max_block
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND b.pinning_backends > 1
ORDER BY b.pinning_backends DESC
LIMIT 10;

If your PK index’s hottest block sits at the right edge (relblocknumber ≈ max_block), you’ve photographed the rightmost leaf, same as above.

If you don’t see this fingerprint, stop reading and go ship features. Your BIGSERIAL keys are fine. This pathology needs high concurrent insert rates, an index that’s resident in memory (so I/O isn’t the dominant wait), and enough backends per millisecond to form a real queue on one page.

What’s Actually Happening Inside the B-tree

#

The insert flow for a monotonic key, simplified (nbtree internals):

1. Find the target leaf. The fastpath remembers the rightmost leaf’s block per backend and goes straight there.
2. Acquire the exclusive content lock on that buffer page. This is the serialisation point.
3. Insert the index tuple.
4. If the page is full, split it — allocate a new page, move tuples, update the parent’s downlink, WAL-log all of it while holding exclusive locks on both pages and the parent.
5. Release and return.

With monotonic keys, step 4 happens metronomically: the rightmost leaf fills, splits, and the new rightmost leaf inherits the queue. (You may have read that lock partitioning helps here — it doesn’t. BufMappingLock partitioning, which has existed since PostgreSQL 8.2 and went from 16 to 128 partitions in 9.5, spreads the buffer lookup table, a different lock family entirely. When every backend wants the same page’s content lock, there is nothing to partition.)

A side note on the sequence itself, because someone always asks: nextval() is a short critical section on the sequence’s single page — it can show up as buffer waits on that one-page relation under extreme rates, and raising CACHE on the sequence (default 1) pre-allocates values per backend if it does. On this box it never cracked the top waits; the index page is the fight worth having.

And this is why the scaling menu from the interview question doesn’t touch it:

• Scaling up buys CPU; the queue is on a lock, not a core. Same shape, higher ceiling.
• Read replicas don’t touch write throughput at all.
• Sharding by the sequential ID as the shard key is the cruellest joke — every shard gets its own B-tree, and all current writes land on one shard. (Sharding by customer_id or a hash is a different story.)

The Case FOR Sequential IDs

#

Now here’s the part the internet skips, and the part my own benchmark just reinforced.

Cache locality is excellent. The hot working set is the rightmost leaf and its parent path — a handful of pages. Compare random-key inserts, where decent write performance wants the entire index cached. My 3M-row seed made this concrete in a different way: the BIGSERIAL PK index was 64 MB; the UUID one was 90 MB, +40% before a single foreign key compounds it.

Append-only patterns love them. Time-series, event logs, anything where reads chase recent data: sequential keys give you physical clustering for free.

JOINs are faster — not mainly from comparison cost, but cache density: more entries per page, fewer pages per lookup, and the effect multiplies across every FK index that references the table.

Polling CDC is trivial. WHERE id > @last_processed is unbeatable for the polling pattern.

And per the table above: at ~200k inserts/sec on 6 cores, BIGSERIAL beat UUID v7 outright. The vast majority of PostgreSQL deployments never reach the concurrency where the rightmost-leaf queue becomes their ceiling. I won’t invent a threshold number for you — my earlier draft said “500 inserts/sec” and I’ve deleted it, because the honest answer is the wait-event loop above, run on your box, under your load.

When You DO Have the Problem: Options, Measured

#

Option 1: FILLFACTOR — the folklore fix that makes it worse

#

The advice you’ll find on Stack Overflow: lower the index’s fillfactor (default 90 for B-trees) to “leave headroom and delay splits.” An earlier version of this post repeated that advice. Then I measured it, and it’s backwards for exactly this workload.

For monotonic inserts, nbtree packs each finished page to fillfactor and moves right (nbtsplitloc.c: rightmost splits leave the left page fillfactor% full). The reserved space sits on pages behind the insert point — where strictly ascending keys never return:

    setting    | avg_leaf_density | bytes per row |  BufferContent samples
---------------+------------------+---------------+------------------------
 default (90)  |            90.68 |            22 |  321
 fillfactor 70 |            70.24 |            28 |  276

Leaf density lands exactly at the fillfactor and stays there forever. The index is ~27% bigger per row, each page absorbs fewer tuples before the next split (more splits, not fewer), and the contention fingerprint is unchanged. Fillfactor headroom is for scattered inserts and HOT-adjacent update patterns — the opposite of this workload. Don’t.

Option 2: Hash partitioning — distribution without changing your key

#

If you must keep BIGSERIAL, this is the structural fix that actually addresses the mechanism, and it’s the one variant in my table that beat everything else while keeping 8-byte keys:

CREATE TABLE orders (
    id BIGSERIAL,
    customer_id BIGINT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... p1 through p7

Eight partitions → eight independent B-trees → eight rightmost leaves, and consecutive ids hash to different partitions, so live traffic spreads. BufferContent samples dropped from 321 to 108 and tps was the best of the bunch. Note the PK works here precisely because the partition key is id — PostgreSQL requires the partition key to be part of the primary key.

That constraint is also why the commonly-suggested alternative — partition by time range — does not fix this problem, on two counts. First, with PARTITION BY RANGE (created_at), a PRIMARY KEY (id) is illegal; you either widen it to (id, created_at) (goodbye simple FKs to orders(id), goodbye global uniqueness on id alone) or silently drop it. Second, all current inserts land in the current partition, on its one rightmost leaf — you’ve moved the hotspot behind a partition boundary and called it fixed. Time partitioning earns its complexity for retention and backfills, not for live-insert contention.

Option 3: UUID v7 — right reasons, honest mechanics

#

On PostgreSQL 18+ this is now zero-dependency (release notes):

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id BIGINT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
-- SELECT uuidv7();  →  019f32b1-fe4e-7607-8e62-481b03d48dfe

(On ≤17, the pg_uuidv7 extension or app-side generation — uuid.NewV7() in google/uuid ≥1.6.0 — fills the gap.)

UUID v7 (RFC 9562) puts a 48-bit millisecond timestamp in the high bits — so range scans and sorting still work — and fills the remaining 74 bits (12-bit rand_a + 62-bit rand_b) with random or sub-millisecond data.

Now the mechanics, stated more carefully than most posts (including, again, an earlier version of this one) state them: every new v7 value still sorts after everything from previous milliseconds. The write spread exists only within a same-millisecond cohort, so it scales with your insert rate per millisecond — not with index size. At my run’s ~180 inserts/ms, a whole cohort of 16-byte keys fits on roughly one 8KB leaf — which is why v7’s contention fingerprint (248) stayed near BIGSERIAL’s (321) rather than near v4’s (0). Two extra wrinkles on PG 18: the native uuidv7() fills rand_a with a sub-millisecond timestamp fraction (docs), making values more ordered, not less; and you need on the order of 10⁵+ inserts/sec before same-ms cohorts span enough pages to genuinely spread the load.

So choose v7 for its real wins — decentralised generation (no sequence round-trip, IDs mintable in the app or at the edge), shard-friendliness, time-ordering, non-guessability — and treat leaf-contention relief as a bonus that arrives only at extreme rates. The costs stay: 16-byte keys (+40% index size, measured), compounding across every FK, and in my runs a ~15% throughput deficit against BIGSERIAL.

Option 4: UUID v4 — great distribution, terrible idea

#

v4 is the one variant that erased BufferContent from the top waits entirely. It’s still the wrong primary key for almost everyone: no time component, no ordering, no locality — every insert lands on a random page, so the entire index is your working set, and range scans degrade to noise. If you need unpredictable public IDs, put a v4 in a secondary unique column and keep a sane PK. Don’t make your B-tree suffer for your API design.

The Comparison

#

Snowflake deserves its footnote: with multiple app instances, each worker’s ID bits give it a distinct range, spreading burst contention across regions of the tree — but the values are still globally increasing, so writes still trend to the right edge. It reduces the burst, not the structure.

The Answer I’m Actually Looking For

#

Back to the interview question. The strong answer orders the diagnosis correctly:

“Before scaling infrastructure, I’d check what the backends are waiting on during peak writes. If it’s WALSync or IOPS, that’s a storage ceiling — Performance Insights will show it, and faster commit paths or better storage fix it. If it’s sustained LWLock:BufferContent concentrated on the PK index’s rightmost block with the index fully cached, that’s a data-structure bottleneck — and no amount of horizontal scaling fixes a queue on one 8KB page; we’d change the key distribution instead.”

Infrastructure scaling is what you can buy with a credit card, so it’s what everyone reaches for first. Data-structure pathologies don’t show up on the cloud provider’s dashboard — but as the runs above show, they do show up in two queries anyone can run. The engineer who checks the wait events before the pricing calculator is the one I want on my team.

Opinionated Takeaway

#

• Default to BIGSERIAL (or GENERATED ALWAYS AS IDENTITY, its modern spelling). Smaller, faster, simpler — and in my runs it out-inserted UUID v7 while “suffering” the very contention this post is about.
• Diagnose in this order: wait-event loop → I/O waits mean storage, stop → sustained BufferContent → hot-block query → rightmost leaf confirmed. Both queries need loops under live load; idle snapshots lie.
• Skip fillfactor for monotonic keys — measured: same contention, 27% fatter index, more splits. It’s a fix for a different workload.
• Hash-partition if you must keep the key — the only mitigation that beat baseline on both contention and throughput. Time-range partitioning does not fix live-insert contention and quietly costs you your simple PK.
• Pick UUID v7 for architecture, not for leaf contention — decentralised minting and shard-friendliness are real; the write spread only materialises at ~10⁵+ inserts/sec, and it’s zero-dependency on PG 18 (uuidv7()).
• Never v4 as the PK. Secondary column, always.
• Distrust every threshold number in posts like this one — including my old “500/sec”, now deleted. The wait-event loop on your box is the threshold.

Reproduce It Yourself

#

podman run -d --name pg18 -p 5432:5432 -e POSTGRES_PASSWORD=x \
  docker.io/library/postgres:18-alpine \
  -c shared_buffers=512MB -c synchronous_commit=off -c max_connections=200

# seed 3M rows into each variant table, then per table:
pgbench -U postgres -n -f insert.sql -c 40 -j 6 -T 20 postgres
# insert.sql:  \set cid random(1, 1000000)
#              INSERT INTO orders_serial (customer_id, total) VALUES (:cid, 9.99);

# and in a second session during the run, sample twice a second:
psql -c "SELECT wait_event_type||':'||wait_event, count(*) FROM pg_stat_activity
         WHERE state='active' AND pid <> pg_backend_pid() GROUP BY 1 ORDER BY 2 DESC" \
     -c "\watch 0.5"

synchronous_commit=off is doing real work in that harness: it removes the WAL-flush wait so the index path is visible. With it on — which is production reality for most — WAL dominates and your primary key is even less likely to be your problem. That asymmetry is the takeaway: measure before you migrate, diagnose before you prescribe, and stop blaming PostgreSQL for a contention pattern your workload may not even have.