Sequence

A five-value reservation marks returned one and three, rolled-back two, and never-returned four and five, with a clean-exit abandonment bracket and next backend starting at six.

Postgres sequences hand out numbers with a cache

A PostgreSQL sequence cache reserves values for a database backend, not for an application object or one transaction. The distinction explains why the sequence’s stored state can run ahead of values actually returned, why reconnecting can create holes, and why making CACHE smaller cannot make identifiers gapless. Start by separating allocation state from the rows the application successfully committed. ...

Allocated one, two and three sit above stored one and three; a red cross replaces the stored-two position after rollback.

A sequence allocates numbers independently of rollback

A PostgreSQL sequence allocates numbers independently of whether the caller’s transaction eventually commits. That makes ordinary identifier generation practical under concurrency, but it also makes gaps normal. A sequence number describes an allocation; it does not prove that a row exists, that a transaction committed, or that every smaller number has been used. ...