A sequence answers one number at a time under a lock, so caching turns many small locked calls into occasional large jumps. That single decision — correctness through a short lock, speed through batching — explains every sequence surprise: gaps after restarts, interleaved ids under concurrency, missing numbers in audit-sensitive tables. None of it is a bug; all of it follows from treating the sequence as a fast unique-number dispenser rather than an odometer.

The practical consequence is a contract worth stating plainly. Sequences promise uniqueness within the sequence, roughly increasing values, and high concurrency. They do not promise contiguity, monotonicity across sessions, or survival of allocated-but-unused values through a crash. Design the schema around the real contract and sequences disappear as a concern; design around the imagined odometer and every gap becomes an incident.

One number at a time adds locks

A sequence keeps a counter and nextval bumps it while holding a row lock, so every call contends on a single value. Under high insert concurrency this becomes a hot spot. Each backend must briefly serialize on the same row to claim its number, which caps insert throughput no matter how many workers you add. The lock is short — increment and release — but at tens of thousands of inserts per second, short serialized work is still serialized.

This is why bulk loads and concurrent writers feel the sequence first. The table can accept parallel inserts, the indexes can split, but the counter admits one claimant at a time. Profiling usually confirms it: lock waits cluster on the sequence relation while everything else idles. When the sequence is the bottleneck, the fix is not a faster lock but fewer visits — which is exactly what caching buys.

Cache buys speed, hides holes

With a cache, a caller takes a whole batch home and only visits the sequence on refill, cutting lock traffic sharply. But a restart discards the unused part of that batch, so numbers are skipped by design. A cache of one hundred means one locked visit per hundred values instead of one per value — two orders of magnitude less contention for the price of up to one hundred missing numbers after every crash or failover.

Size the cache from the insert rate. A cache of 1 keeps every lock visit; a cache of 1000 nearly removes the sequence from the concurrency picture but can skip a thousand values per restart. Most OLTP workloads land between 20 and 100, where the lock disappears from profiles and the gaps stay unnoticed. The setting lives on the sequence itself, so it survives dumps and replicas apply it identically.

Treat them as unique, not sequential

Sequences produce unique ids, never a gapless 1,2,3 order, and gaps are an acceptable consequence. Reserve gapless numbering for where it truly matters, and keep sequences purely as id generators. Gaps come from rolled-back transactions, cached batches lost to restarts, and concurrent sessions interleaving — three sources, all normal, none preventable without giving up either concurrency or crash safety.

Where gapless output is a legal or business requirement — invoice numbers, queue tickets — build it explicitly: a separate counter table updated in a short transaction at commit time, accepting the serialization cost on that narrow path only. Keep the primary key on the fast gappy sequence and the display number on the slow gapless counter. Mixing the two jobs into one mechanism forces a choice between speed and contiguity that neither workload actually wants.

When it breaks

The classic failure is the audit panic: finance notices invoice ids jumping by hundreds after a failover and suspects data loss. Nothing was lost — the standby promoted with a fresh cache window and the old cached range died with the primary. The fix is communication plus separation: document that surrogate keys skip, and never expose raw sequence values where humans read continuity into them. If the number faces customers, it needs the explicit gapless counter, not a sequence with a large cache.

The second failure is subtler: two sessions seeing interleaved values and application code assuming later id means later commit. Under concurrency, session A can claim 101 and commit after session B claims 102 and commits first. Ordering by id then misorders history. When commit order matters, order by a committed-at timestamp, never by the sequence value.

Check it quickly

Prove cache behavior and gap sources:

SHOW SERVER_VERSION;
SELECT cache_size FROM pg_sequences WHERE sequencename = 'orders_id_seq';
BEGIN; SELECT nextval('orders_id_seq'); ROLLBACK;
SELECT last_value FROM orders_id_seq;

The rollback still advances the sequence, demonstrating that gaps are structural rather than caused by failure.

Sources