Identity Columns, Sequences, Sequence Caching, Gaps, and Distributed-ID Considerations

Learning outcomes

Identifiers need to be unique under concurrency, but “unique” is not the same requirement as “consecutive.” PostgreSQL sequence objects deliberately optimize for fast multi-session allocation. Values can be skipped because of rollback, conflicts, caching, crashes, or administrative changes. If an invoice number must be legally gapless, an identity/sequence column is not that ledger requirement.

This lesson makes identity and sequence semantics visible using separate transactions and sessions. You will inspect the implicit sequence behind an identity column, compare GENERATED ALWAYS with BY DEFAULT, use nextval/currval/setval safely, and compare sequence keys with UUID identifiers.

1. Identity columns: SQL-facing intent, sequence-backed mechanism

GENERATED ... AS IDENTITY declares that PostgreSQL should generate values for a column through an implicit sequence. ALWAYS rejects ordinary explicit values unless the statement deliberately uses OVERRIDING SYSTEM VALUE; BY DEFAULT allows an explicit caller value to override generation.

CREATE TABLE app.ch04_identity_always (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    note text NOT NULL);CREATE TABLE app.ch04_identity_default (    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,    note text NOT NULL);INSERT INTO app.ch04_identity_always(note) VALUES ('generated') RETURNING id;INSERT INTO app.ch04_identity_default(note) VALUES ('generated') RETURNING id;-- Ordinary explicit value should fail for ALWAYS:INSERT INTO app.ch04_identity_always(id,note) VALUES (100,'explicit');-- BY DEFAULT accepts an explicit value:INSERT INTO app.ch04_identity_default(id,note) VALUES (100,'explicit');

ALWAYS is useful when callers should not choose identifiers accidentally. BY DEFAULT can simplify imports/migrations that preserve source IDs, but then you must reconcile the sequence state before relying on generated values again.

2. Find the sequence instead of guessing its name

Identity implementation details should be inspected through supported interfaces. pg_get_serial_sequence() also works for identity columns despite its historical name.

SELECT column_name, is_identity, identity_generation,       identity_start, identity_incrementFROM information_schema.columnsWHERE table_schema='app'  AND table_name IN ('ch04_identity_always','ch04_identity_default')ORDER BY table_name, ordinal_position;SELECT pg_get_serial_sequence('app.ch04_identity_always','id') AS sequence_name;SELECT schemaname, sequencename, sequenceowner,       start_value, min_value, max_value, increment_by, cache_size, last_valueFROM pg_catalog.pg_sequencesWHERE schemaname='app' AND sequencename LIKE 'ch04_identity_%';

3. Standalone sequences and session semantics

A sequence is an independent database object. nextval() atomically advances it and returns a distinct value under concurrency. currval() returns the value most recently obtained from that sequence in the current session and errors if this session has never called nextval(). lastval() refers to whichever sequence this session used most recently.

CREATE SEQUENCE app.ch04_ticket_seq START WITH 1000 INCREMENT BY 1 CACHE 1;SELECT nextval('app.ch04_ticket_seq') AS allocated;SELECT currval('app.ch04_ticket_seq') AS this_sessions_last_value;SELECT lastval() AS this_sessions_last_sequence_value;

Open a second psql session and call currval('app.ch04_ticket_seq') before nextval(). The expected error is evidence that currval is session-local, not a global “show me the current number” operation.

4. The rollback gap: allocation is intentionally non-transactional

PostgreSQL does not reclaim a value from nextval() when the transaction rolls back. This prevents allocators from blocking each other in order to recycle numbers and is fundamental to efficient concurrency.

BEGIN;SELECT nextval('app.ch04_ticket_seq') AS allocated_then_aborted;ROLLBACK;SELECT nextval('app.ch04_ticket_seq') AS next_after_rollback;

The second value advances past the aborted one. Similar holes can appear when INSERT ... ON CONFLICT computes a default sequence value before choosing the conflict path. Therefore sequence values are unique allocators, not evidence that N committed rows/events occurred.

5. setval and ALTER SEQUENCE RESTART are not the same operation

setval() changes sequence state immediately and the change is not rolled back. The optional Boolean controls whether the next nextval returns exactly the specified value or advances first. In contrast, ALTER SEQUENCE ... RESTART is transactional and blocks concurrent sequence operations while it changes the restart position.

CREATE SEQUENCE app.ch04_setval_probe START 1 CACHE 1;SELECT setval('app.ch04_setval_probe', 42, false);SELECT nextval('app.ch04_setval_probe');  -- 42SELECT nextval('app.ch04_setval_probe');  -- 43BEGIN;ALTER SEQUENCE app.ch04_setval_probe RESTART WITH 100;ROLLBACK;SELECT nextval('app.ch04_setval_probe');

Never run sequence-reset commands against a valuable production sequence because a tutorial says “sync it.” First identify duplicates/collision risk, active writers, ownership, and the highest committed ID. Sequence repair belongs in a migration/runbook with verification.

6. Sequence caching: throughput versus visible gaps/order

CACHE n allows a backend to reserve multiple sequence values at once. This can reduce sequence contention, but multiple sessions may hold separate chunks. Values observed by commit time may appear out of numerical order, and unused cached values can become gaps when sessions exit.

ALTER SEQUENCE app.ch04_ticket_seq CACHE 10;SELECT sequencename, cache_size, last_valueFROM pg_catalog.pg_sequencesWHERE schemaname='app' AND sequencename='ch04_ticket_seq';

Do not pick a universal cache value. Benchmark the real concurrency pattern, understand failover/replication and external-ID expectations, and treat the sequence number as an identifier—not a reliable event-order clock.

7. Ownership associations and privileges

A sequence can be associated with a table column via OWNED BY, which ties lifecycle: dropping the owned column/table can drop the sequence. Identity columns manage this relationship automatically. Standalone sequences default to no such ownership association.

Calling standalone sequence functions also requires privileges. nextval requires USAGE or UPDATE; currval requires USAGE or SELECT; setval requires UPDATE. For explicit sequence-backed defaults (including legacy SERIAL-style designs), table privileges do not automatically imply sequence privileges. Identity columns package generation as part of the column definition, so test the exact runtime role against the identity/table design instead of blindly copying legacy serial grants.

SELECT has_sequence_privilege('servicehub_app','app.ch04_ticket_seq','USAGE') AS app_usage,       has_sequence_privilege('servicehub_app','app.ch04_ticket_seq','SELECT') AS app_select,       has_sequence_privilege('servicehub_app','app.ch04_ticket_seq','UPDATE') AS app_update;

Grant only what the runtime workflow needs. Do not grant UPDATE merely to make an error disappear if the app only needs nextval.

8. Identity/sequence integer versus UUID

ServiceHub can reasonably use an internal bigint primary key plus a public UUID when both compact joins and externally safe identifier generation are useful. Avoid duplicating identifiers without an architectural reason.

9. Allocation order is not commit order

Sequence values are assigned when nextval() runs, not when a transaction commits. Session A can receive 2001 and then perform slow work while Session B receives 2002 and commits immediately. A report ordered by the identifier may therefore look like an event timeline even when it is not the transaction-commit timeline. If business ordering matters, store an explicit timestamp or domain sequence whose semantics are designed for that ordering requirement.

Session A: BEGIN -> nextval = 2001 -> long work -----------------> COMMITSession B:        BEGIN -> nextval = 2002 -> COMMITObserved commit order: 2002, then 2001Observed allocation order: 2001, then 2002

This distinction becomes even more important with cached values and distributed application workers. Treat an identity as a key unless you have separately proved that its allocation properties satisfy the ordering contract.

10. Identity versus SERIAL: prefer the clearer modern declaration

PostgreSQL still supports the historical smallserial, serial, and bigserial pseudo-types. They expand into an integer column, a sequence, a nextval default, and ownership linkage. Identity columns express the generation relationship as a first-class column property in SQL metadata and support ALWAYS/BY DEFAULT override semantics. For new schemas, identity usually makes the intent easier for migrations and introspection to understand.

Existing production schemas using SERIAL are not automatically wrong and do not need cosmetic rewrites. Migration risk, sequence names, defaults, grants, dependencies, ORM assumptions, and replication/backup tooling matter more than syntax fashion. Teach the mechanism so you can operate both forms safely.

SELECT table_name, column_name, column_default, is_identity, identity_generationFROM information_schema.columnsWHERE table_schema='app'  AND table_name IN ('ch04_identity_always','ch04_identity_default')ORDER BY table_name, ordinal_position;

11. Deliberately wrong approach: SELECT max(id)+1

Two concurrent transactions can both read the same maximum and compute the same next value. Adding a unique constraint merely converts the race into errors. Sequences exist specifically to provide atomic multi-session allocation.

-- Race-prone pseudo-code:SELECT COALESCE(max(id),0)+1 FROM app.some_table;-- Another session can compute the same value before either INSERT commits.

12. Hands-on lab: allocation timeline

1. In Session A create/use app.ch04_ticket_seq and record nextval/currval.
2. In Session B call currval before nextval and record the error.
3. In Session A allocate inside a transaction and roll it back; prove the value is not reused.
4. Change CACHE on the disposable sequence and inspect pg_sequences.
5. Compare GENERATED ALWAYS and BY DEFAULT with explicit inserts.
6. Write a design decision: which ServiceHub identifiers are internal sequence-backed keys and which, if any, need UUIDs?

13. Cleanup

DROP TABLE IF EXISTS app.ch04_identity_always;DROP TABLE IF EXISTS app.ch04_identity_default;DROP SEQUENCE IF EXISTS app.ch04_ticket_seq;DROP SEQUENCE IF EXISTS app.ch04_setval_probe;

14. Summary and bridge

Identity columns make sequence-backed generation part of the table definition; sequence functions expose the allocator directly. PostgreSQL guarantees safe distinct allocation, not gaplessness, commit ordering, or secrecy. Next, generated columns and defaults will show a different kind of generation—computed data whose value depends on row expressions—then connect it to encoding and collation choices that can change comparison/index semantics across upgrades.

Authoritative references

• PostgreSQL 18 — Identity Columns
• PostgreSQL 18 — CREATE SEQUENCE
• PostgreSQL 18 — Sequence Manipulation Functions
• PostgreSQL 18 — ALTER SEQUENCE
• PostgreSQL 18 — pg_sequences
• PostgreSQL 18 — Privilege Inquiry Functions