beginnerintermediateadvanced

PostgreSQL · Sequences and Identity

Sequences and Identity Columns

A sequence as its own standalone object, IDENTITY vs legacy SERIAL, why gaps are normal, non-transactional advancement, and the race condition manual max(id)+1 logic actually has

A sequence is its own objectSERIAL/IDENTITY just wire a DEFAULT to one
GENERATED ALWAYS rejects explicit valuesunless OVERRIDING SYSTEM VALUE is used
Gaps are normalrollback never reclaims a consumed value
nextval() is atomicmax(id)+1 has a real concurrency race

1.Sequences and Generated Identifiers

A sequence is its own database object that generates a new, incrementing integer every time it is asked, via nextval(). It exists independently of any table — a column with a DEFAULT of nextval('some_sequence') is simply a column that asks the sequence for the next value on every INSERT that omits it.

Key features

A sequence is created with CREATE SEQUENCE and generates values via nextval('sequence_name')
A sequence has its own current value, increment, min/max, and can be queried directly
SERIAL and GENERATED ... AS IDENTITY both create a sequence automatically and wire a column's DEFAULT to it — they are convenience syntax, not a different underlying mechanism
nextval() is guaranteed to return a unique value across concurrent callers, without any locking that would block other inserters
A sequence advances even if the transaction that called nextval() is rolled back — sequence advancement is not transactional

A sequence vs a column relying on it

A sequence vs a column relying on it
Object	What it is
CREATE SEQUENCE orders_id_seq	a standalone object generating incrementing integers
id INT DEFAULT nextval('orders_id_seq')	a column that asks the sequence for its default value

A sequence as an independent object a column defaults to

orders_id_seq

a standalone sequence object

nextval(...)

requests the next value, atomically

id column DEFAULT

used automatically on INSERT if omitted

Command

sql

CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (id INT DEFAULT nextval('orders_id_seq') PRIMARY KEY, total NUMERIC);

SELECT nextval('orders_id_seq');   -- can be called independently of any table

A sequence is its own standalone object with persistent state, independent of any table — SERIAL/IDENTITY are convenience syntax that create one automatically and wire a column's DEFAULT to it, not a different underlying mechanism.

2.GENERATED AS IDENTITY

GENERATED ALWAYS AS IDENTITY creates a column backed by a sequence, the same underlying mechanism as SERIAL, but as standard SQL syntax rather than a PostgreSQL-specific shorthand — and GENERATED ALWAYS specifically rejects an explicit value on INSERT unless overridden, while GENERATED BY DEFAULT allows one.

Key features

GENERATED ALWAYS AS IDENTITY rejects an explicit value on INSERT unless OVERRIDING SYSTEM VALUE is specified
GENERATED BY DEFAULT AS IDENTITY allows an explicit value on INSERT, falling back to the sequence only when omitted — matching SERIAL's behavior
IDENTITY is standard SQL; SERIAL is PostgreSQL-specific shorthand — both create and wire up a sequence underneath
PostgreSQL's own documentation recommends IDENTITY over SERIAL for new schemas
An IDENTITY column's backing sequence can still be inspected and manipulated directly, exactly like a SERIAL column's

ALWAYS vs BY DEFAULT

ALWAYS vs BY DEFAULT
Declaration	Explicit value on INSERT?
GENERATED ALWAYS AS IDENTITY	rejected, unless OVERRIDING SYSTEM VALUE is used
GENERATED BY DEFAULT AS IDENTITY	allowed — falls back to the sequence only if omitted
SERIAL (legacy shorthand)	always allowed — behaves like BY DEFAULT

Command

sql

CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    total NUMERIC
);

INSERT INTO orders (total) VALUES (100);              -- id auto-assigned
INSERT INTO orders (id, total) OVERRIDING SYSTEM VALUE VALUES (999, 50);  -- explicit, opted in

IDENTITY is the standard-SQL equivalent of SERIAL, backed by the same sequence mechanism — GENERATED ALWAYS rejects an explicit value by default (requiring OVERRIDING SYSTEM VALUE), while GENERATED BY DEFAULT permits one silently, matching SERIAL's historical behavior.

3.Sequence Gaps and Why They Are Normally Acceptable

A sequence can produce gaps — id 5 followed by id 8, with 6 and 7 never appearing in the table — because a rolled-back transaction, a failed insert, or simply calling nextval() without using it all permanently consume that value. This is expected, normal behavior, not corruption, and application logic should never assume sequence values are gap-free.

Key features

A rolled-back transaction that called nextval() (directly or via a DEFAULT) does not give the consumed value back — it is gone
Sequence advancement is not transactional specifically so that concurrent transactions never block each other waiting for a sequence value
A gap in id values is not evidence of data loss or corruption — it just means some value was consumed and not ultimately used
Application logic must never assume ids are contiguous — "count expected rows by subtracting min(id) from max(id)" is unsafe
A sequence can be queried (SELECT * FROM seq_name) to inspect its current state, but this does not reveal whether gaps exist in the actual table

Common causes of a sequence gap

Common causes of a sequence gap
Cause	Result
A transaction that inserts a row then rolls back	the consumed id is never reused
A failed INSERT (e.g. a constraint violation) after the DEFAULT already called nextval()	the id is consumed even though no row was stored
Calling nextval() directly, outside any INSERT	the value is consumed with no corresponding row at all

Command

sql

SELECT max(id) - min(id) + 1 AS id_span, count(*) AS actual_rows FROM orders;
-- if id_span > actual_rows, gaps exist -- this is normal, not a bug

A sequence guarantees uniqueness and increasing order, never contiguity — gaps from rollbacks, failed inserts, or direct nextval() calls are normal and expected, not corruption. Never infer a row count or a gap-free numbering from sequence/id values.

4.How Sequences Behave With Rollback

A sequence's nextval() call is never rolled back, even if the surrounding transaction is — this is a deliberate exception to PostgreSQL's normal rule that a rolled-back transaction undoes everything it did. Sequences are specifically designed this way to avoid blocking concurrent transactions.

Key features

nextval() advances the sequence immediately and permanently, regardless of whether the calling transaction later commits or rolls back
This is intentional, documented PostgreSQL behavior, not a bug or edge case
The alternative (transactional sequences) would require serializing concurrent nextval() calls, defeating the sequence's performance purpose
setval() can manually reset a sequence's value if truly needed, but this is an explicit administrative action, not automatic rollback behavior
This same non-transactional behavior applies whether nextval() is called directly or implicitly via a SERIAL/IDENTITY column's DEFAULT

What rolls back vs what does not

What rolls back vs what does not
Action inside a rolled-back transaction	Rolled back?
INSERT/UPDATE/DELETE	yes — fully undone
CREATE TABLE / DDL	yes — fully undone
nextval() (directly or via DEFAULT)	no — the consumed value stays consumed

Command

sql

BEGIN;
SELECT nextval('orders_id_seq');
ROLLBACK;
SELECT nextval('orders_id_seq');  -- continues from where it left off, unaffected by the rollback

Sequence advancement is deliberately exempt from transactional rollback — a consumed value stays consumed even if the transaction that requested it rolls back, because making sequences transactional would require serializing concurrent nextval() calls and defeat their entire performance purpose.

5.Why Application-Level max(id) + 1 Is Unsafe

SELECT max(id) + 1 FROM t, then inserting with that computed value, has a race condition: two concurrent transactions can both read the same max(id), both compute the same "next" value, and both attempt to insert it — one succeeds, one fails (or worse, both succeed if there is no unique constraint). A sequence's nextval() has no such window, because it is atomic by design.

Key features

max(id) + 1 involves two logically separate steps: reading the current max, then inserting a new row with the computed value
Two concurrent transactions can both read the same max(id) before either has inserted its new row
nextval() is atomic — the read-and-increment happens as one indivisible operation, with no window for another caller to see the pre-increment state
A UNIQUE or PRIMARY KEY constraint on id turns the race into a visible error (one transaction fails) rather than silent data corruption (two rows sharing an id) — but the underlying design is still fragile and unnecessary
This race gets more likely, not less, under higher concurrency — exactly the condition sequences are specifically designed to handle safely

max(id) + 1 vs nextval() under concurrency

max(id) + 1 vs nextval() under concurrency
Approach	Atomic?	Safe under concurrent inserts?
SELECT max(id) + 1, then INSERT	no — two separate steps	no — race condition
nextval('seq'), or a SERIAL/IDENTITY column	yes — one indivisible operation	yes — by design

The max(id)+1 race condition, two concurrent transactions

Transaction A

orders table

Transaction B

1SELECT max(id) → 5
2SELECT max(id) → 5 (same value)
3INSERT id=6
4INSERT id=6 → conflict or duplicate

Command

sql

-- the correct, safe alternative
CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
INSERT INTO orders (total) VALUES (100);  -- id assigned atomically

max(id) + 1 is a read-then-write race: two concurrent transactions can read the same max(id) and compute the same "next" value before either inserts. A sequence's nextval() is atomic — no window exists for that race, which is exactly why sequences (accepting gaps) exist instead of manual max+1 logic (which does not actually guarantee uniqueness).

Spotted a mistake on this page? Tell us.

Read Sheet