Identity columns, sequences and why generated IDs have gaps

What it is

id bigint GENERATED ALWAYS AS IDENTITY (or BY DEFAULT) declares an identity column. The documentation describes it as backed by an implicit sequence: with ALWAYS, a user-supplied value is accepted only if the INSERT says OVERRIDING SYSTEM VALUE; with BY DEFAULT, a supplied value takes precedence. nextval advances the sequence, and the sequence-functions documentation says a value obtained by nextval is not reclaimed if the calling transaction later aborts, that an INSERT with ON CONFLICT computes the row including nextval calls before detecting the conflict, and that sequences therefore cannot provide gapless numbering. The CREATE SEQUENCE page adds that a CACHE setting above one preallocates values per session, so unused values are lost when the session ends and values across sessions may be out of order.

Why it matters

Developers and auditors notice gaps and suspect lost rows. Code that assumes max(id) + 1 is the next value, or that count(*) equals max(id), is wrong. Numbering rules that demand contiguous numbers cannot be met by the primary key.

How to apply

• Prefer bigint GENERATED ALWAYS AS IDENTITY; an integer column tops out at 2^31 - 1 (2,147,483,647), and changing the type later rewrites the table.
• Treat IDs as opaque; never derive meaning from gaps or magnitude, and never expose row volume through them if that matters, using UUIDs or random tokens externally instead.
• When a contiguous number is required (document numbering), assign it at the moment the document becomes final from a counter row updated in the same transaction (UPDATE counters SET last = last + 1 WHERE name = 'invoice' RETURNING last); the CREATE SEQUENCE documentation describes such a locked counter as much more expensive than a sequence, which is the price of the guarantee.
• After bulk loads with explicit IDs into a BY DEFAULT identity column, reset the sequence (SELECT setval(pg_get_serial_sequence('t', 'id'), max(id)) FROM t), otherwise the next generated value collides.
• Leave CACHE at its default of 1 unless the sequence is a measured bottleneck.

Pitfalls

setval changes are visible to other sessions immediately and are not undone by rollback. Serial columns (serial, bigserial) are a sequence plus a column default rather than an identity column, so explicit values are never rejected. The CREATE SEQUENCE documentation states that NO CYCLE is the default: once the maximum is reached, nextval returns an error rather than wrapping.