Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep Dive: identity columns vs serial, and the sequence cache gap problem

PostgreSQL Data Types Sequences Identity Serial

You insert three rows into a table with a SERIAL primary key. You query SELECT max(id) and get 3. Then you insert three more rows and get 6. Then you restart PostgreSQL, insert one row, and get 12. Where did 7 through 11 go?

They were cached, allocated, and never used. This is not a bug. It’s the sequence cache doing exactly what it was designed to do.

SERIAL is not a type

The first thing to understand about SERIAL is that it’s not a data type. It’s a shorthand that PostgreSQL expands at table creation time into three objects:

-- What you write:
CREATE TABLE users (
    id serial PRIMARY KEY,
    name text
);

-- What PostgreSQL actually executes:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id integer PRIMARY KEY DEFAULT nextval('users_id_seq'),
    name text
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

SERIAL creates a sequence, an integer column with a default that calls nextval(), and an ownership link so the sequence is dropped when the table is dropped. The column type is integer — SERIAL never appears in the schema. You can verify this by checking pg_attribute:

SELECT attname, atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'users'::regclass AND attname = 'id';
-- id | integer

BIGSERIAL works identically but uses bigint and a 64-bit sequence. SMALLSERIAL uses smallint and a 16-bit sequence.

The sequence users_id_seq is a fully independent object. You can call nextval('users_id_seq') from anywhere, set it to any value with setval(), and modify its properties with ALTER SEQUENCE. The ownership link (OWNED BY) is the only connection to the table — it ensures the sequence is dropped when the column is dropped, and it resets the sequence when the column is dropped.

IDENTITY: the SQL standard alternative

PostgreSQL 10 introduced GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY, the SQL-standard mechanism for auto-generated values:

CREATE TABLE users (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text
);

Like SERIAL, this creates a sequence behind the scenes. Unlike SERIAL, the identity specification is a column property, not a shorthand expansion. The differences matter when you try to override the value.

GENERATED ALWAYS AS IDENTITY — the column always generates a value. You cannot insert a manual value unless you use OVERRIDING SYSTEM VALUE:

-- Normal insert: system generates the ID
INSERT INTO users (name) VALUES ('Alice');

-- This fails:
INSERT INTO users (id, name) VALUES (42, 'Bob');
-- ERROR: cannot insert into column "id"

-- This works:
INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (42, 'Bob');

GENERATED BY DEFAULT AS IDENTITY — the system generates a value only when no value is provided, similar to SERIAL:

-- System generates the ID
INSERT INTO users (name) VALUES ('Alice');

-- Manual value accepted
INSERT INTO users (id, name) VALUES (42, 'Bob');

-- But you can still force system generation
INSERT INTO users (id, name) OVERRIDING USER VALUE VALUES (DEFAULT, 'Carol');

The OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE clauses are the key difference from SERIAL. They give you explicit control over whether the system or the user provides the value, and the database enforces the policy.

SERIAL has no such enforcement. The default is nextval(), but you can always override it by supplying a value — there’s no way to prevent manual inserts:

-- SERIAL: this always works (no enforcement)
INSERT INTO users (id, name) VALUES (999, 'Mallory');
-- No error, no override needed

The sequence cache: where gaps come from

Sequences have a CACHE parameter (default 1 in older versions, but many tools and ORM migrations set it higher). When a backend calls nextval(), PostgreSQL fetches a batch of values from the sequence, stores them in backend-local memory, and serves subsequent nextval() calls from the local cache without touching the shared sequence. When the cache is exhausted, the backend fetches a new batch.

-- Create a sequence with cache of 20
CREATE SEQUENCE test_seq CACHE 20;

-- Each backend that calls nextval() gets 20 values at once
SELECT nextval('test_seq');  -- allocates 1-20 to this backend
SELECT nextval('test_seq');  -- returns 2 (from cache)
-- ...
SELECT nextval('test_seq');  -- returns 20 (last cached value)
SELECT nextval('test_seq');  -- allocates 21-40, returns 21

The cache has three consequences:

1. Gaps on crash or restart. If a backend has cached values 41-60 and the PostgreSQL server crashes, those cached values are lost forever. On restart, the next nextval() call allocates from 61 onward. Values 41-60 are gaps.

2. Gaps from unused values. If a backend caches 20 values but only uses 5 before its connection drops, values 6-20 are gaps.

3. Gaps from rolled-back transactions. Sequence values are not transactional. nextval() consumes the value immediately, even if the surrounding transaction rolls back:

BEGIN;
SELECT nextval('users_id_seq');  -- returns 7
ROLLBACK;
-- Value 7 is gone forever

SELECT nextval('users_id_seq');  -- returns 8

This is by design. If sequence values were transactional, concurrent transactions would need to lock the sequence for every nextval() call, eliminating the concurrency benefit entirely.

Why the cache exists

Without caching, every nextval() call acquires a lock on the sequence’s row in pg_sequence, increments the value, updates the row, and releases the lock. This is a single-row update — fast in isolation, but a bottleneck under concurrent load. Every INSERT into a table with a serial primary key must wait for this lock.

With a cache of 20, 19 out of 20 nextval() calls are served from backend-local memory with zero lock contention. Only every 20th call hits the shared sequence. This reduces sequence lock contention by a factor of 20.

On a table receiving 10,000 inserts per second across 100 connections, a cache of 1 means 10,000 lock acquisitions per second on one sequence row. A cache of 100 means 100 lock acquisitions per second — a 100x reduction in contention.

-- Check the cache size of a sequence
SELECT sequencename, cache_value
FROM pg_sequences
WHERE sequencename = 'users_id_seq';

The default cache size is 1, but many deployment tools (Django migrations, Rails ActiveRecord, Prisma) set it to 20 or higher. The trade-off: larger cache means fewer lock acquisitions but larger gaps on restart.

The sequence ownership trap

When you use SERIAL, PostgreSQL creates an OWNED BY relationship between the sequence and the column. This has a subtle behavior:

-- Dropping the column drops the sequence
ALTER TABLE users DROP COLUMN id;
-- The sequence users_id_seq is automatically dropped

-- Dropping the column resets the sequence (if it still exists)
-- This is less relevant since OWNED BY implies automatic drop

With IDENTITY, the sequence is even more tightly coupled. You cannot manually manipulate the sequence associated with an identity column:

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- This fails:
ALTER SEQUENCE orders_id_seq RESTART WITH 1000;
-- ERROR: cannot alter sequence "orders_id_seq": its relation is an identity column

-- Use the proper syntax:
ALTER TABLE orders ALTER COLUMN id RESTART WITH 1000;
-- Or to change behavior:
ALTER TABLE orders ALTER COLUMN id SET GENERATED BY DEFAULT;
ALTER TABLE orders ALTER COLUMN id SET GENERATED ALWAYS;
ALTER TABLE orders ALTER COLUMN id SET CACHE 100;

The ALTER TABLE ... ALTER COLUMN ... RESTART/SET GENERATED/SET CACHE syntax is the only way to modify an identity column’s sequence. You cannot DROP SEQUENCE on it either — the sequence is a dependency of the column, not an independent object.

Replication and sequences

Sequences are not replicated via logical replication. In a logical replication setup, the publisher’s sequences and the subscriber’s sequences advance independently. This is correct behavior — if both sides used the same sequence values, inserts on the subscriber would conflict with replicated inserts from the publisher.

However, this means you cannot rely on sequence values being gap-free or globally unique in a logical replication topology. Each node has its own sequence state. If a failover promotes a subscriber to a publisher, its sequences continue from where they were, which may overlap with values already used on the old publisher.

For physical replication (streaming replication), sequences replicate as part of the WAL stream, so standbys have identical sequence state. Failover preserves sequence continuity.

When gaps in IDs actually matter

Sequence gaps are benign in most applications. An auto-incrementing ID is a surrogate key — its value has no business meaning. Users never see it, and no business logic depends on continuity.

Gaps matter when:

  • IDs are exposed to users as order numbers or reference codes. Customers notice “your order was #3, then #7.” This is a UX problem, not a database problem — use a separate order_number column with a gapless sequence or a formatted reference.
  • ID continuity is used as a consistency check. “If we have 1000 rows, the max ID should be at least 1000.” This assumption is fragile and should not exist.
  • Legal or regulatory requirements demand no gaps. Financial transaction numbering in some jurisdictions requires gap-free sequences. This cannot be achieved with PostgreSQL sequences — you need a separate counter table with explicit locking:
-- Gapless sequence (not recommended unless legally required)
CREATE TABLE gapless_counter (
    name text PRIMARY KEY,
    next_val bigint NOT NULL
);
INSERT INTO gapless_counter VALUES ('invoice_number', 1);

-- Get the next gapless number (serializes all callers)
SELECT next_val, (SELECT next_val FROM gapless_counter WHERE name = 'invoice_number')
FROM gapless_counter
WHERE name = 'invoice_number'
FOR UPDATE;

UPDATE gapless_counter SET next_val = next_val + 1 WHERE name = 'invoice_number';

This approach serializes all inserts through a row lock, eliminating concurrency. It is appropriate only for low-volume tables where gaplessness is a hard requirement.

Practical SQL: sequence diagnostics

List all sequences in your schema and their properties:

SELECT schemaname, sequencename,
       data_type, start_value, minimum_value, maximum_value, increment,
       cache_value, cycle_option, last_value
FROM pg_sequences
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, sequencename;

Find sequences with small caches (potential contention under load):

SELECT schemaname, sequencename, cache_value
FROM pg_sequences
WHERE cache_value < 20
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY cache_value ASC;

Check the current value of a specific sequence:

SELECT last_value, is_called
FROM users_id_seq;

Note: last_value is the value most recently returned (or allocated to a cache). is_called indicates whether nextval() has been called at least once. If is_called is false, the next nextval() will return last_value; if true, it will return last_value + increment.

Find gaps in a sequence (for small tables only — this is a full scan):

WITH bounds AS (
    SELECT min(id) AS min_id, max(id) AS max_id FROM users
),
all_ids AS (
    SELECT generate_series(min_id, max_id) AS id FROM bounds
)
SELECT a.id AS missing_id
FROM all_ids a
LEFT JOIN users u ON u.id = a.id
WHERE u.id IS NULL
ORDER BY a.id
LIMIT 100;

This query generates every integer between the min and max IDs and finds which ones are missing. It’s only practical for small ranges — generating a series from 1 to 10 million is feasible, but 1 to 1 billion is not.

Key takeaways

  • SERIAL is not a type — it’s a shorthand that creates a sequence, an integer column, and an ownership link. The column type is integer.
  • GENERATED ALWAYS AS IDENTITY prevents manual value insertion unless OVERRIDING SYSTEM VALUE is specified. GENERATED BY DEFAULT AS IDENTITY allows manual values like SERIAL but adds OVERRIDING USER VALUE for explicit system generation.
  • Sequence values are not transactional. Rolled-back transactions consume values permanently. This is intentional — transactional sequences would require serializing every nextval() call.
  • The sequence cache (controlled by CACHE) allocates batches of values to backends to reduce lock contention. Larger cache = fewer lock acquisitions but larger gaps on crash or restart.
  • Identity column sequences cannot be directly altered with ALTER SEQUENCE. Use ALTER TABLE ... ALTER COLUMN ... RESTART/SET CACHE/SET GENERATED.
  • Sequences are not replicated by logical replication. Each node advances its sequences independently.
  • Gap-free sequences require explicit row-level locking and serialization. Use them only when legally required.

What’s next

Sequences, UUIDs, arrays, JSONB, timestamps — PostgreSQL’s data type system has a lot of surface area, and we’ve covered the major pitfalls. But there’s one more type that causes more production incidents than any other: text search. PostgreSQL’s full-text search with tsvector and tsquery is powerful, but the ranking algorithm, the GIN vs GiST index choice, the stop word problem, and the phrase search limitations catch people off guard constantly. Next time: how tsvector actually stores tokens, why your search results are ranked wrong, and the patterns that make full-text search fast.

Previously: UUID v4 vs v7: Why Your Random UUIDs Are Making Your Indexes Fat