At some point, someone opens a ticket: "Our order IDs are not consecutive. We went from 10047 to 10051. What happened to 10048 through 10050?"
Nothing happened to them. They never existed. The gaps are normal. The question is why.
How sequences work
A database sequence is an independent counter object. When you call nextval('orders_id_seq'), the database increments the counter and returns the new value. This happens outside of your transaction.
That last part is what causes gaps. Sequence values are consumed at call time, not at commit time. If your transaction calls nextval() to get ID 10048, then does some work, then rolls back — the rollback does not return 10048 to the sequence. The sequence counter has already moved. The next transaction gets 10049.
-- Transaction A: gets 10048, does work, rolls back
BEGIN;
INSERT INTO orders (id, ...) VALUES (nextval('orders_id_seq'), ...);
-- Some constraint fails
ROLLBACK;
-- Transaction B: gets 10049
BEGIN;
INSERT INTO orders (id, ...) VALUES (nextval('orders_id_seq'), ...);
COMMIT;
-- 10048 is gone. This is correct.
This design is intentional. If sequences were transactional, every rollback would require coordinating with every other transaction that might have consumed later values. That coordination would serialize all sequence operations and eliminate the performance benefit of sequences.
Caching amplifies gaps
Sequences have a CACHE parameter (default 1 in Postgres) that controls how many values a session pre-allocates. With CACHE 1, each session fetches one value at a time. With CACHE 100, a session fetches 100 values and hands them out locally without consulting the sequence object on disk.
Caching dramatically reduces contention on high-insert tables. It also dramatically increases gaps. If a session pre-allocates IDs 10048 through 10147 and processes 3 inserts before the connection closes, IDs 10051 through 10147 are gone. On a server restart, every cached allocation in every connection vanishes.
This is why gaps tend to cluster after deployments, restarts, and failovers: all cached allocations are discarded simultaneously.
Replication and failover
On primary/replica setups, sequence state can diverge during a failover. If the primary's sequence is at 10100 and the replica's sequence is at 9900 (because WAL for sequence updates has not yet replicated), a failover to the replica will start issuing IDs from 9901. Depending on the failover tool and the application, you may get duplicate IDs or large gaps.
The standard mitigation is to advance the sequence on the new primary after failover:
SELECT setval('orders_id_seq', max(id) + 1000) FROM orders;
The 1000 is a buffer for any IDs that existed on the old primary but have not yet been confirmed in the new primary's data. The exact value depends on your insert rate and your failover detection time.
GENERATED ALWAYS AS IDENTITY
Modern Postgres (10+) recommends GENERATED ALWAYS AS IDENTITY over SERIAL. The underlying mechanism is the same — both use a sequence — but identity columns are more explicit about their behavior and avoid some SERIAL quirks around permission inheritance.
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-- ...
);
You can still configure the underlying sequence with CACHE, INCREMENT, and MINVALUE/MAXVALUE via the SEQUENCE NAME clause. The gaps will appear in the same circumstances.
What this means in practice
If consecutive IDs matter for your application — audit trails, invoice numbering, regulatory compliance — do not use a database sequence for that purpose. Use a separate counter table inside a transaction, or generate the sequential number at display time after the fact.
If you are using IDs only as primary keys and foreign key references, gaps are irrelevant. The uniqueness guarantee is intact. The referential integrity is intact. The gap between 10047 and 10051 means a rollback happened or a session was killed. That is operational information, not data loss.
The instinct to fill gaps or audit them comes from treating IDs as a business-meaningful sequence. Most IDs are not business-meaningful. They are opaque handles. When an ID is only ever referenced, never inspected, the consecutive gap concern dissolves.
If someone reopens the ticket, close it with: sequences are designed to skip numbers, and the design is correct.
Building something? builds.anethoth.com is a public build ledger — prove your product is really being built with a free dossier.