When you add a SERIAL column to a table, the assumption feels natural. Rows go in one at a time, each gets the next number; the numbers should be consecutive. First row gets 1, second gets 2, third gets 3. Clean, predictable, ordered.
Then one day you query your table and find 1, 2, 5, 6, 11. The missing numbers are nowhere to be found — no deleted rows, no failed imports. The gaps just appeared. Understanding why they appear, and why PostgreSQL allows it, reveals something genuinely interesting about how the database handles concurrency and performance.
A Sequence Is Its Own Small Object
When you create a SERIAL column, PostgreSQL quietly creates a separate sequence object behind the scenes. A sequence is not part of your table — it is its own independent database object that keeps a counter and hands out the next number when asked. Every call to nextval() goes to this object, gets a number, and advances the counter.
The sequence stores three things on disk: the last value it handed out, a count of how many future values it has already pre-claimed, and a flag for whether it has been called at all. That is it. Every time a number is needed, PostgreSQL reads this small record, picks the next value, writes the updated state back, and returns the number to whoever asked.
The Cache Lives in Your Session
Here is the first thing that surprises people. The sequence maintains a cache of pre-fetched values, and that cache lives inside your database session — not in shared memory, not on disk, but in a small private hash table that belongs to your connection alone.
By default, the cache size is 1, meaning each call to nextval() touches the sequence object on disk. But if you create a sequence with CACHE 10, your session grabs 10 values in a single disk operation and stores them privately. The next 9 calls to nextval() never touch the disk or any other session at all — they just pull the next number from your private stash.
This is where the first source of gaps comes in. Two sessions both have CACHE 10 on the same sequence. Session A grabs values 1 through 10. Session B grabs values 11 through 20. Session A inserts rows using 1, 2, 3, then disconnects without using 4 through 10. Those six numbers are gone. No row was ever inserted with those values, no transaction failed, and no rollback happened. The session just ended and took its cache with it.
Rollback Does Not Undo a Sequence
The second source of gaps is transactions. Most developers assume that if a transaction rolls back, everything it did is undone. For table rows, foreign keys, index entries — yes, all of that is rolled back cleanly. But nextval() is explicitly not transactional.
The reason is concurrency. If sequence values were rolled back with transactions, two sessions could try to use the same number. Session A calls nextval() and gets 42. Session B also calls nextval() and gets 43. Session A rolls back. Now 42 is available again. But 43 is already out in the world. If 42 gets reused, you now have two rows that were alive at the same time with different data but the same ID, just at different moments. That creates the exact kind of ambiguity the sequence is supposed to prevent.
So PostgreSQL made a deliberate choice. Once a sequence value is handed out, it is gone regardless of what happens to the transaction that asked for it. Roll back all you want — the number stays consumed. This means every failed transaction, every INSERT that gets rolled back, every application error that triggers a retry — each one silently burns a sequence value.
Crashes Eat Values Too
The third source of gaps is server crashes. Writing to the WAL log for every single nextval() call would be expensive on busy systems. PostgreSQL avoids this by pre-logging sequence values in batches. When it writes to the WAL, it does not log the current state — it logs the state as it would look after 32 more calls. This means the next 32 calls can skip WAL entirely, which is a meaningful performance gain.
The cost of this shortcut is that if the server crashes before those 32 pre-logged values are used, the sequence on disk already claims they were issued. When the server restarts and replays the WAL, the sequence picks up after those 32 values even though none of them were ever returned to any client. Up to 32 values disappear on every unclean shutdown.
None of This Is a Bug
The comment in the source code is direct about the trade-off: PostgreSQL does not want to log every sequence fetch, so it pre-logs a batch and accepts that those values can be lost in a crash. This is a conscious design decision made in favour of speed.
The practical advice that follows from all of this is simple: never write application logic that depends on sequence values being consecutive. Do not use them to count rows, do not check for gaps to detect missing data, and do not assume that the highest ID tells you how many records exist. A sequence ID is a unique identifier, nothing more. The gaps are not data loss — they are evidence that the system is working correctly under real conditions of concurrency, retries, and occasional crashes.
If you genuinely need gap-free numbering for an audit log or an invoice sequence, a sequence is not the right tool. That problem requires a transactional counter with a different set of trade-offs. For everything else, the gaps are harmless, and the speed is worth it.