Sequence

A sequence answers one number at a time under a lock, so caching turns many small locked calls into occasional large jumps. That single decision — correctness through a short lock, speed through batching — explains every sequence surprise: gaps after restarts, interleaved ids under concurrency, missing numbers in audit-sensitive tables. None of it is a bug; all of it follows from treating the sequence as a fast unique-number dispenser rather than an odometer. ...

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. ...