Postgres Auto-Increment ID Gap Jumping After Rolled Back Transaction

A gap in auto-incrementing IDs after a rolled-back transaction is expected PostgreSQL behavior, not a bug — sequence value generation and transaction rollback are deliberately independent of each other, since making them dependent would require locking behavior that would severely hurt concurrent insert performance for every application using serial or identity columns.

The Problem

After a transaction that inserted a row and was subsequently rolled back (due to an error, or an explicit ROLLBACK), the next successfully inserted row has a noticeably higher ID than expected, with a gap corresponding to the rolled-back attempt:

BEGIN;
INSERT INTO orders (customer_id) VALUES (123); -- gets id 501
ROLLBACK;

INSERT INTO orders (customer_id) VALUES (456); -- gets id 502, not 501

The rolled-back insert clearly never actually persisted (correctly, since the transaction was rolled back), yet its sequence value was still consumed and is now permanently skipped.

Why It Happens

PostgreSQL's sequence generator (used internally by SERIAL and GENERATED ... AS IDENTITY columns) is deliberately non-transactional — meaning a sequence's nextval() call takes effect immediately and permanently the moment it's called, regardless of whether the surrounding transaction that used that value ultimately commits or rolls back. This is a deliberate design decision: making sequence generation transactional (so a rollback would also "return" the unused value) would require significant locking coordination between concurrent transactions all trying to get the next sequence value, which would seriously limit how many transactions could insert rows concurrently. By accepting that gaps can occur, PostgreSQL allows many transactions to request sequence values simultaneously without blocking each other at all.

The Fix

In the vast majority of cases, there genuinely isn't a "fix" needed here, because gaps in an ID column are not actually a problem for what an ID column is meant to do — uniquely and reliably identify a row. If your application or any downstream logic assumes IDs are perfectly sequential with no gaps, that assumption itself is the actual issue to address, not the gaps themselves:

-- This kind of assumption is fragile and shouldn't be relied upon:
-- "if max(id) - count(*) == 0, there are no gaps" — don't build logic depending on this

If you need an accurate count of rows, query the count directly rather than inferring it from ID values:

SELECT COUNT(*) FROM orders;

If your specific business requirement genuinely needs a gapless, sequential number — a legally required sequential invoice number is the most common legitimate case — don't use the table's primary key SERIAL/IDENTITY column for this purpose at all. Instead, implement a separate, explicitly transactional sequence mechanism using row-level locking, generating the number only within the same transaction as the row that will actually use it, and only once you're confident that transaction will commit:

BEGIN;
SELECT next_invoice_number FROM invoice_sequence FOR UPDATE;
-- generate and use the number here, within this same transaction
UPDATE invoice_sequence SET next_invoice_number = next_invoice_number + 1;
INSERT INTO invoices (invoice_number, ...) VALUES (retrieved_number, ...);
COMMIT;

This pattern trades some concurrency (since the row-level lock on the sequence table serializes concurrent invoice-generating transactions specifically) for a genuine, strict gaplessness guarantee that a standard SERIAL/IDENTITY column fundamentally cannot provide, precisely because of the non-transactional design discussed above.

If gaps are occurring far more frequently than you'd expect from normal, occasional transaction failures, investigate whether something in your application is causing an unusually high rate of rolled-back transactions in the first place — frequent rollbacks might indicate a separate underlying issue (retry logic that's too aggressive, a validation check happening too late in the transaction) worth addressing on its own merits, even though the resulting ID gaps themselves aren't inherently a problem to fix.

Still Not Working?

If you're seeing gaps that seem unusually large or frequent, and want to confirm whether rolled-back transactions are genuinely the explanation versus some other cause (like a bulk import process that's calling nextval() more than expected for each row, or a caching layer pre-fetching sequence values in batches), check the sequence's own current value against actual inserted row counts to get a clearer picture of the actual gap pattern:

SELECT last_value FROM orders_id_seq;
SELECT COUNT(*) FROM orders;

A consistently proportional gap correlating with your application's actual observed transaction failure/retry rate confirms the explanation covered here; a gap pattern that doesn't match your application's failure rate at all suggests investigating a different, unrelated cause specific to your particular setup.