Sequence Gaps and Why They're Normal

1. The typical observation: missing IDs in an auto-increment column

A look at a table with an auto-increment primary key occasionally shows an ID sequence like 1001, 1002, 1005, 1006, even though nobody actively deleted any rows. The obvious suspicion is a bug in the application or an error in the database. In reality this is completely normal, well-documented behavior that follows directly from how sequences and transactions work, not from a defect.

The central misconception is assuming a sequence is a transactional resource that gets rolled back along with the transaction. The exact opposite is true, and that difference explains practically every observed gap phenomenon.

2. Why sequences deliberately work outside transactions

A sequence assigns values outside the normal transaction context so multiple concurrent transactions can request new, guaranteed-unique values at the same time without blocking each other. If a sequence value only became final at the commit of the requesting transaction, every other transaction also needing a new value would have to wait until the first transaction commits or rolls back. Under high concurrency, that would turn the sequence into a hard bottleneck.

Because assignment instead happens immediately and independently of the transaction's eventual fate, a once-assigned value stays consumed forever, even if the associated transaction later rolls back. This deliberate design decision trades guaranteed gaplessness for high concurrency, and for the overwhelming majority of use cases that is a very good trade.

BEGIN;
INSERT INTO orders (customer_id) VALUES (42); -- gets e.g. id=1005
ROLLBACK; -- transaction is discarded

BEGIN;
INSERT INTO orders (customer_id) VALUES (43); -- gets id=1006, NOT 1005
COMMIT;

3. Rollbacks as the most common everyday cause of gaps

Every transaction that requests a new sequence value and then rolls back for business or technical reasons, for example because a downstream validation fails, a constraint is violated, or the application throws an exception, leaves a permanent gap behind. In production systems with validation logic, optimistic locking, or external dependencies within a transaction, a certain rate of failing transactions is entirely normal.

The higher the rate of failing or deliberately rolled-back transactions in a system, the more gaps appear. That is not a quality signal about the database, it is a direct, expectable consequence of the volume of transactions that, for good reason, do not go through.

4. Sequence caching as a second, often underestimated cause

Many database systems cache entire blocks of sequence values per database connection or session for performance reasons, instead of assigning a single value per request with corresponding synchronization overhead. If a connection ends or the database restarts before a cached block is fully consumed, the remaining, already reserved values of that block are lost permanently.

This behavior is independent of transaction rollbacks and occurs even in systems with a hundred percent success rate, as soon as connections are regularly re-established, which is the norm in modern applications with connection pooling and autoscaling. Knowing cache size and restart frequency lets you roughly estimate the magnitude of resulting gaps, but not prevent them without giving up the performance benefit of caching.

-- Sequence caching 20 values per session
CREATE SEQUENCE orders_id_seq
    START WITH 1000
    INCREMENT BY 1
    CACHE 20;
-- On a connection drop after only 3 consumed values,
-- the remaining 17 reserved values are lost

5. Concurrent transactions and commit order

Even without a single rollback, concurrent transactions produce another, more subtle phenomenon: the order in which sequence values are assigned does not have to match the order in which the associated transactions actually commit. A transaction that requests a sequence value first can, due to longer processing time, commit later than a second transaction that received a higher value but finished faster.

For application code this means an ID column must never be used as a reliable indicator of the actual chronological order of commits, even when no gap exists at all. For robust chronological ordering, a dedicated timestamp with sufficient resolution is the correct solution, not inference from the ID.

6. Telling a genuine bug apart from a harmless, normal gap

Not every gap is automatically harmless, so it is worth having a way to tell a normal gap apart from an actual bug. A normal gap correlates with a plausible cause: a logged validation failure, a known rollback, a restarted connection pool, or a burst of concurrent writes around the affected ID range. If application logs or transaction logs show a matching event at roughly the same time, the gap is expected behavior, not a defect.

A gap becomes suspicious when it appears without any corresponding rollback, error log entry, or connection event, especially if it recurs in a fixed, implausible pattern. In that case the more likely explanation is an application bug generating and discarding IDs outside the database, for example by pre-fetching a batch of sequence values in the application layer and only using some of them, which reintroduces exactly the caching problem described above under different control.

7. Why fighting gaps is usually unnecessary effort

Trying to avoid gaps, for example by explicitly locking the entire table when assigning a new ID, serializes every write to that one table. What could previously run in parallel becomes a strictly sequential queue, with noticeable effects on throughput and latency as soon as write load rises above a trivial level.

In the overwhelming majority of use cases, a gap in the ID column carries no business meaning whatsoever. The primary key serves as a technical, unique identifier, not as a continuous, gapless count of business events. Once that distinction is made cleanly, the supposed problem disappears without any additional logic being necessary.

8. Rare, legitimate exceptions: when gaplessness is a business requirement

There are cases where gaplessness is not a technical preference but a business or regulatory requirement. The most prominent example is invoice numbers, for which many jurisdictions legally mandate continuous, gapless numbering without jumps, to make manipulation of the books demonstrably impossible. Similar requirements sometimes apply to certain contract numbers or official document numbers.

In these cases the answer is not to manipulate the primary auto-increment sequence or disable its caching, but to treat the gapless number as a separate, business-level attribute managed independently of the technical primary key.

9. How to solve gapless numbers cleanly, separate from the primary key

The robust solution is a dedicated counter table with exactly one row per business context, whose value is read and incremented via SELECT ... FOR UPDATE within the same transaction that creates the invoice. Because this increment happens inside the transaction, it gets rolled back along with it too, unlike with a real sequence, which is what keeps gaplessness guaranteed.

The deliberate downside of this solution is strict serialization of every operation needing a new number for the same context, because the row stays locked until commit. That is almost always acceptable for invoice numbers with comparatively low volume per unit of time, a consciously accepted, clearly bounded trade-off, very different from blanket locking of a heavily used main table.

CREATE TABLE invoice_number_counter (
    context TEXT PRIMARY KEY,
    next_value BIGINT NOT NULL
);

BEGIN;
SELECT next_value FROM invoice_number_counter
    WHERE context = 'DE-2026' FOR UPDATE;
UPDATE invoice_number_counter
    SET next_value = next_value + 1
    WHERE context = 'DE-2026';
-- create the invoice with the read value within the same transaction
COMMIT;

10. Summary

11. FAQ: Understanding Sequence Gaps