A PostgreSQL sequence cache reserves values for a database backend, not for an application object or one transaction. The distinction explains why the sequence’s stored state can run ahead of values actually returned, why reconnecting can create holes, and why making CACHE smaller cannot make identifiers gapless. Start by separating allocation state from the rows the application successfully committed.

Three different numbers

For a new ascending sequence with CACHE 5, its first nextval can return one while last_value already records five. The latter is the reservation boundary, not a count of inserted rows. Currval reports the value most recently established for that sequence in this session; another connection’s allocations do not replace it. Comparing these fields without naming their scope produces misleading monitoring.

The following calls in that backend can use its reserved values. PostgreSQL’s implementation has a backend-local fast path and separate buffer locking when refilling; it is inaccurate to describe every nextval as taking an ordinary table row lock. Caching reduces repeated sequence-state access, but that is not evidence that sequence contention dominates a particular application’s insert latency. Measure before changing the cache.

Session lifetime matters

Unused reservations are abandoned when the session ends, even after a clean exit. A service that frequently opens new database sessions can therefore waste more reservations than a long-lived pool using the same allocation rate. The cache belongs to the server session, so an application-level connection abstraction does not tell you how long that cache survives.

Different sessions can hold different ranges. One can return one, another six, and the first then two. This is allocation interleaving, not a promise about commit order or evidence that those values were lost. Even CACHE 1 cannot impose commit order: transactions can obtain numbers and finish in the opposite order. A timestamp recorded before commit does not repair that ordering guarantee either.

When it breaks

Do not use CACHE as a maximum crash-gap formula. Multiple sessions can hold reservations, and the implementation also logs sequence progress ahead of individual returned values. Rollbacks, conflict-handled inserts, and unused allocations produce additional holes. A gap alone proves neither data loss nor the absence of data loss; reconcile committed business records and recovery evidence before dismissing an incident.

During normal operation, noncycling allocation provides distinct values when administrators do not reset the allocator into an already used range. CYCLE, manual resets, and explicit imported identifiers change that premise. A table still needs its primary key or uniqueness constraint. Reducing the cache cannot replace that integrity rule, recover deleted rows, or make a business numbering process auditable. Before exposing an allocation durably outside the database, commit its transaction; a crash can leave uncommitted sequence changes uncertain.

Changing sequence settings also requires attention to live sessions that already hold values. Do not reset an active sequence to a table maximum in an attempt to close holes. If a business requires contiguous issued numbers, define issuance, rollback, cancellation, and external publication separately, then evaluate a transactional counter. There is no universal cache size or performance gain to prescribe without a measured workload.

A numbered five-row reservation ledger separates returned, rolled-back and never-returned values; a clean-exit band leads to a second five-cell range from six through ten, with currval and reservation-boundary scopes stated separately.
Returned two and never-returned four/five are distinct gap sources. Exact historical shell evidence covers sequential standalone backends only, not crash gaps or concurrency.

Check it quickly

This socket-free exercise uses two successive single-user backends against one newly initialized, disposable cluster. Run the shell script as a non-root account with PostgreSQL 18 initdb and postgres in PATH. Never point single-user mode at a live cluster. It grants implicit superuser powers and cannot test concurrent clients, realistic interprocess locking, or failover.

#!/bin/sh
set -eu
umask 077
export LC_ALL=C
lab=$(mktemp -d "${TMPDIR:-/tmp}/xbcatm-sequence-cache.XXXXXX")
initdb -D "$lab/data" --no-locale --encoding=UTF8 \
  --auth-local=trust --auth-host=reject >"$lab/init.log" 2>&1
postgres --single -j -D "$lab/data" -c io_method=sync -c log_min_messages=notice postgres \
  >"$lab/session-a.log" 2>&1 <<'SQL'
CREATE SEQUENCE cache_probe CACHE 5;

SELECT nextval('cache_probe') AS first_value;

SELECT last_value AS reserved_through FROM cache_probe;

BEGIN;

SELECT nextval('cache_probe') AS rolled_back_value;

ROLLBACK;

SELECT nextval('cache_probe') AS after_rollback;

DO $$ BEGIN
  IF currval('cache_probe') <> 3 OR
     (SELECT last_value FROM cache_probe) <> 5 THEN
    RAISE EXCEPTION 'cache reservation or rollback result wrong';
  END IF;
  RAISE NOTICE 'cache_probe_a_ok';
END $$;

SQL
if grep -E '(ERROR|FATAL|PANIC):' "$lab/session-a.log"; then exit 1; fi
grep -q 'cache_probe_a_ok' "$lab/session-a.log"
postgres --single -j -D "$lab/data" -c io_method=sync -c log_min_messages=notice postgres \
  >"$lab/session-b.log" 2>&1 <<'SQL'
SELECT nextval('cache_probe') AS after_clean_session_end;

DO $$ BEGIN
  IF currval('cache_probe') <> 6 OR
     (SELECT last_value FROM cache_probe) <> 10 THEN
    RAISE EXCEPTION 'unused cache values were unexpectedly reused';
  END IF;
  RAISE NOTICE 'cache_probe_b_ok';
END $$;

DROP SEQUENCE cache_probe;

SQL
if grep -E '(ERROR|FATAL|PANIC):' "$lab/session-b.log"; then exit 1; fi
grep -q 'cache_probe_b_ok' "$lab/session-b.log"
cat "$lab/session-a.log" "$lab/session-b.log"
printf 'retained_lab %s\n' "$lab"

Retained exact-script records and raw logs report a PostgreSQL 18.6 pass; this source review did not rerun the script. The first backend returned one, recorded reservation five, allocated two inside a transaction that rolled back, and then returned three. Its assertion checks that the cache boundary remains five. Blank lines delimit commands in single-user -j mode, preserving the intended transaction boundaries rather than sending the entire example as one command.

After that backend exited cleanly, the second returned six and recorded reservation ten. Four and five were never returned; two was returned but belonged to a rolled-back transaction. These are distinct gap sources demonstrated without a crash. Both success notices appeared, and the sequence was dropped. The printed directory retains the disposable cluster and logs for inspection; it is not automatic filesystem cleanup. Failures can leave the same artifacts.

The script checks error text as well as process status because a standalone backend can report SQL errors without a failing exit code. This is a reservation-lifecycle test, not a throughput benchmark or durability drill. It supplies no evidence for a fixed crash-gap bound or a recommended production cache size.

Sources