PostgreSQL sequences and identity columns: gaps, rollbacks and crashes, tested

Short answer: I ran kill -9 on the postgres processes: a PostgreSQL identity column went from id 9 to 37, because PostgreSQL sequences log 32 values ahead in the WAL. Rollbacks and failed inserts burn values too, while a BY DEFAULT column ignores explicit ids. Use GENERATED ... AS IDENTITY rather than serial, and never expect contiguous ids.

PostgreSQL 18.6, throw-away cluster on Rocky Linux 10.2. Part 1 of 5 (PostgreSQL, MariaDB, Oracle, SQL Server, recap). Every output comes from my own test runs.

Three ways to get a generated value

CREATE TABLE t_ident  (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v text);
CREATE TABLE t_serial (id serial PRIMARY KEY, v text);
CREATE SEQUENCE s1;
CREATE TABLE t_seq    (id int PRIMARY KEY DEFAULT nextval('s1'), v text);

Each INSERT ... RETURNING id returns 1 on a fresh table. The catalog shows the difference:

 column_name |            column_default            | is_identity | identity_generation
-------------+--------------------------------------+-------------+---------------------
 id          |                                      | YES         | ALWAYS
 id          | nextval('s1'::regclass)              | NO          |
 id          | nextval('t_serial_id_seq'::regclass) | NO          |

An identity column has no default expression; the sequence is attached to the column (pg_get_serial_sequence('t_ident','id') returns public.t_ident_id_seq). serial is a shorthand for an integer column plus a default calling nextval.

ALWAYS or BY DEFAULT

With GENERATED BY DEFAULT, an explicit value is accepted but does not move the counter:

INSERT INTO t_by(id, v) VALUES (2, 'explicit 2');         -- ok
INSERT INTO t_by(v) VALUES ('default 1') RETURNING id;    -- 1
INSERT INTO t_by(v) VALUES ('default 2') RETURNING id;    -- ERROR: duplicate key ... (id)=(2) already exists
INSERT INTO t_by(v) VALUES ('default 3') RETURNING id;    -- 3

The failed insert also burned the value 2 of the counter. With GENERATED ALWAYS, an explicit value is refused unless you say so:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

After OVERRIDING SYSTEM VALUE VALUES (50, ...), the next default insert returned 1, because the counter is independent from the data.

Rollback and failed inserts consume values

BEGIN; INSERT INTO t_ident(v) VALUES ('rolled back') RETURNING id;   -- 2
ROLLBACK;
INSERT INTO t_ident(v) VALUES ('after rollback') RETURNING id;       -- 3

Same with a unique violation: ids 1, then an error on the duplicate, then 3. A gapless counter needs a separate design (a counter table with a lock), but that serialises writers.

Concurrency: order of ids is not order of commits

Session A inserts first and commits 3 seconds later, session B inserts second and commits at once:

A got id 1
B got id 2
B: committed 08:18:48.652
A: committed 08:18:51.154

B’s row is visible before A’s row, although it has the higher id. Do not use “max(id) seen so far” as a replication or polling cursor, because commit order differs from id order.

CACHE is per session

CREATE SEQUENCE s_cache CACHE 10;
same session twice : 1|2
new session        : 11
another new session: 21

With PostgreSQL sequences, each session reserves a block of 10. Values from different sessions interleave and gaps appear when a session ends.

Limits and CYCLE

ERROR:  nextval: reached maximum value of sequence "s_small" (3)

After ALTER SEQUENCE s_small CYCLE, the next value is 1. When an identity sits on a smallint column, it stops at 32767 with the same error on t_small_id_seq. integer identities stop at 2147483647: choose bigint for tables that live long.

Restart and crash

Three rows inserted, the last deleted, clean restart:

sequence before the restart: 3|30|t     (last_value | log_cnt | is_called)
sequence after the restart:  3|30|t
new id = 4

Then five more rows (last id 9), kill -9 of every postgres process, restart:

sequence before the crash: 9|27|t
sequence after the crash:  36|0|t
new id = 37
ids now: 1,2,4,5,6,7,8,9,37

log_cnt is the number of values still covered by the last WAL record. PostgreSQL writes to the WAL one record per 32 values; after crash recovery it restarts from the end of the reserved range (9 + 27 = 36), so the next id is 37. A clean shutdown writes the exact state, so there is no gap.

Where to read the state of PostgreSQL sequences

SELECT * FROM pg_sequences;                -- start, increment, max, cache_size, cycle, last_value
SELECT last_value, log_cnt, is_called FROM t_ident_id_seq;
SELECT attrelid::regclass, attname, attidentity FROM pg_attribute WHERE attidentity <> '';  -- 'a' ALWAYS, 'd' BY DEFAULT

Official documentation

PostgreSQL, project site

CREATE SEQUENCE, PostgreSQL 18 documentation

Sequence manipulation functions, nextval, currval, setval

CREATE TABLE, GENERATED ... AS IDENTITY and serial

pg_sequences, the system view used above

*Not tested here: logical replication of sequences, pg_upgrade, partitioned tables, ALTER SEQUENCE ... RESTART while in use.*

All parts of the series

1/5 PostgreSQL

2/5 MariaDB (coming 17 Nov)

3/5 Oracle (coming 24 Nov)

4/5 SQL Server (coming 1 Dec)

5/5 TL;DR: the four engines compared (coming 8 Dec)

Related articles

Inside database data files (1/3): InnoDB, from MySQL to MariaDB

Inside database data files (2/3): PostgreSQL

Inside database data files (3/3): Oracle, and all three side by side