23 — Sequences & Identifiers

CREATE SEQUENCE my_seq START 1 INCREMENT 1;

SELECT nextval('my_seq');   -- 1 (advances the counter, returns the new value)
SELECT nextval('my_seq');   -- 2
SELECT currval('my_seq');   -- 2 (THIS SESSION's last nextval — not the global value)
SELECT lastval();           -- 2 (last sequence used by nextval in this session)
SELECT setval('my_seq', 100);   -- manually set the value (for migrations)
-- nextval is NEVER rolled back. If a transaction calls nextval, gets 5,
-- then rolls back — the sequence is STILL at 5. The gap (5) is permanent.
-- This is by design: if nextval were transactional, two concurrent
-- transactions would block on the sequence, serializing all inserts.
-- Non-transactional nextval = lock-free concurrency = high throughput.
-- The cost: gaps are normal and expected. A sequence guarantees
-- UNIQUENESS, not CONSECUTIVENESS.

-- currval('seq') returns the value that THIS SESSION last obtained from
-- nextval('seq'). It does NOT return the sequence's global current value
-- (another session might have advanced it further). Call nextval first
-- in the session, or currval errors:
-- ERROR: session does not have a current value for sequence "my_seq"

SELECT currval('my_seq');   -- ERROR if no nextval in this session yet
SELECT nextval('my_seq');   -- 3
SELECT currval('my_seq');   -- 3 (this session's last nextval)

-- lastval() returns the last value obtained by nextval for ANY sequence
-- in this session — no argument. Convenient but fragile (depends on
-- call order). Prefer explicit currval('seq_name') in production code.

-- This:
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

-- Is sugar for ALL of this:
CREATE SEQUENCE users_id_seq;                          -- 1. a sequence named <table>_<col>_seq
CREATE TABLE users (
  id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('users_id_seq')  -- 2. DEFAULT + 3. NOT NULL
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;         -- 4. sequence "owned by" the column
-- OWNED BY means: DROP COLUMN id → sequence is dropped too.

-- The column type is INTEGER, not "serial". SERIAL is not a type.
-- pg_typeof: SELECT pg_typeof(id) FROM users → "integer"
-- You CANNOT do: ALTER COLUMN id TYPE serial (not valid).

-- SERIAL is INTEGER → max 2,147,483,647 (~2.1B). High-write tables
-- hit this in months. BIGSERIAL is BIGINT → max 9.2 × 10^18.
-- Always use BIGSERIAL (or BIGINT IDENTITY) for new tables.
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,    -- bigint + sequence + default + not null
  event_data JSONB
);
-- The cost of bigint vs int: 8 bytes vs 4 bytes per column. For a PK
-- that appears in every index and every FK, the 4-byte difference
-- adds up — but overflow is worse. Use bigint.

-- GENERATED ALWAYS: the DB always generates the value. Manual inserts
-- specifying id are REJECTED (prevents accidental collisions).
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- GENERATED BY DEFAULT: the DB generates a value if id is omitted,
-- but ALLOWS manual inserts (with collision risk if the manual value
-- is below the sequence's current position).
CREATE TABLE users_mutable (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- GENERATED ALWAYS: manual id insert is rejected
INSERT INTO users (id, name) VALUES (100, 'Alice');
-- ERROR: cannot insert a non-DEFAULT value into identity column "id"
-- Override (for migrations/data loads):
INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (100, 'Alice');
-- OVERRIDING SYSTEM VALUE is an explicit "I know what I'm doing" escape hatch.

-- GENERATED BY DEFAULT: manual id insert is allowed
INSERT INTO users_mutable (id, name) VALUES (100, 'Alice');  -- works
-- But: if 100 is below the sequence's current position, a later
-- auto-generated insert might collide (duplicate key). BY DEFAULT
-- is more flexible for migrations but riskier for production.

-- 1. Standard SQL: portable to Oracle, DB2, SQL Server (with syntax tweaks)
-- 2. Proper dependency tracking: identity is tied to the column
--    DROP COLUMN id → identity is cleaned up automatically
--    SERIAL: the sequence can be orphaned if you DROP DEFAULT first
-- 3. Easier alteration: no manual sequence juggling
ALTER TABLE users ALTER COLUMN id RESTART WITH 1000;          -- reset the identity
ALTER TABLE users ALTER COLUMN id SET GENERATED BY DEFAULT;   -- change generation mode
ALTER TABLE users ALTER COLUMN id INCREMENT BY 10;            -- change increment
-- With SERIAL, you'd ALTER SEQUENCE users_id_seq RESTART WITH 1000 —
-- and you have to know the sequence name (use pg_get_serial_sequence).
-- 4. Cleaner pg_dump/restore: identities restore correctly; SERIAL
--    sequences can get out of sync if the restore order is wrong.

-- Gaps come from:
-- 1. Rolled-back inserts: nextval advances, the insert rolls back → gap
-- 2. Failed inserts (constraint violation): nextval advanced, insert failed → gap
-- 3. Session crashes: CACHE values reserved but unused → gap
-- 4. Failover: replica's sequence is behind the primary's → gap on failover

-- This is BY DESIGN. nextval is non-transactional to avoid lock contention.
-- If nextval were transactional, two concurrent transactions would need
-- to lock the sequence, serializing all inserts → throughput killed.

-- ❌ WRONG: trying to "fill gaps" by finding missing IDs and reusing them
-- This requires serialization (find the gap, claim it, insert) → kills
-- concurrency. And it breaks FK references (a "reused" ID might point
-- to a deleted row's old data in a foreign table).

-- ✅ RIGHT: accept gaps. Sequences guarantee uniqueness, not consecutiveness.
-- If you need consecutive numbers (invoice numbers, check numbers),
-- use a separate counter table with row-level locking:
CREATE TABLE invoice_counters (
  tenant_id BIGINT PRIMARY KEY,
  next_number BIGINT NOT NULL DEFAULT 1
);
-- Atomically get and increment:
UPDATE invoice_counters
SET next_number = next_number + 1
WHERE tenant_id = $1
RETURNING next_number - 1 AS invoice_number;
-- This is serialized (one transaction at a time per tenant) but guarantees
-- consecutive numbers. Use only when consecutiveness is a business requirement.

-- CACHE N: each session reserves N values at once. Reduces the
-- shared-counter lock contention (the sequence's internal lock is
-- acquired once per N values, not once per value).
CREATE SEQUENCE high_throughput_seq CACHE 1000;

-- Default CACHE is 1 (no caching — every nextval hits the shared counter).
-- For high-throughput insert tables:
ALTER SEQUENCE users_id_seq CACHE 100;
-- Now each session grabs 100 values at once → 100x fewer lock acquisitions.
-- Cost: on session crash, the unused cached values are LOST (gaps).
-- For a table with 10K inserts/sec, CACHE 1000 is reasonable.
-- For a table with 10 inserts/day, CACHE 1 is fine (no gap waste).

-- OWNED BY ties a sequence to a column → DROP COLUMN drops the sequence
ALTER SEQUENCE my_seq OWNED BY users.id;

-- pg_get_serial_sequence: the RELIABLE way to find a column's sequence
-- Don't assume the name is users_id_seq (it is for SERIAL, but IDENTITY
-- and manually-created sequences differ).
SELECT pg_get_serial_sequence('users', 'id');
-- → 'public.users_id_seq' (or the identity's internal sequence)

-- Use it in setval calls (don't hardcode the sequence name):
SELECT setval(pg_get_serial_sequence('users', 'id'),
              (SELECT max(id) FROM users));

-- After a COPY or bulk INSERT with explicit IDs, the sequence is
-- still at its old position. The next auto-insert calls nextval,
-- gets a value that already exists → duplicate key error.
-- Fix: advance the sequence past max(id).

-- For SERIAL / manual sequences:
SELECT setval(pg_get_serial_sequence('users', 'id'),
              (SELECT max(id) FROM users));
-- setval(seq, N) → next nextval returns N+1
-- setval(seq, N, false) → next nextval returns N (the is_called flag)

-- For IDENTITY columns:
ALTER TABLE users ALTER COLUMN id RESTART WITH 10001;
-- Or compute max first, then RESTART:
SELECT max(id) + 1 FROM users;  -- get the value
-- ALTER TABLE users ALTER COLUMN id RESTART WITH <value>;  -- use the literal
-- Note: RESTART doesn't accept a subquery — compute the value first.

-- Bulk reset for ALL tables after a full data load:
-- (Generate and execute the setval statements)
SELECT format(
  'SELECT setval(%L, (SELECT max(%I) FROM %I.%I));',
  pg_get_serial_sequence(schemaname || '.' || tablename, column_name),
  column_name, schemaname, tablename
)
FROM information_schema.columns
WHERE column_default LIKE 'nextval%'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');
-- Copy the output and run it. Or use psql's \gexec:
-- SELECT ... \gexec

-- ── Strategy 1: BIGINT IDENTITY (the default for most tables) ───
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1 CACHE 1000),  -- cache for throughput
  customer_id BIGINT NOT NULL,
  amount NUMERIC NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (id)
);
-- BIGINT: no overflow risk (9.2 × 10^18 max)
-- GENERATED ALWAYS: prevents manual id injection (collision-safe)
-- CACHE 1000: each session reserves 1000 IDs → low lock contention

-- ── Strategy 2: UUID for distributed/client-side generation ──────
-- Use when: client generates IDs before insert (idempotent retries),
-- multi-master writes (no shared counter), or IDs span databases.
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- for gen_random_uuid() in PG < 13
-- PG 13+: gen_random_uuid() is built-in (no extension needed)

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- 128-bit, globally unique
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- UUID v4 (gen_random_uuid): random → B-tree index fragmentation
-- (random insert positions cause page splits). Larger indexes, worse
-- cache locality. For large-scale UUID tables, consider UUIDv7
-- (time-ordered, monotonic → append-only index behavior).

-- ── Post-migration reset ────────────────────────────────────────
-- After loading a dump with explicit IDs into the orders table:
-- The identity sequence is still at 1. Next auto-insert collides.
-- Reset it:
SELECT setval(pg_get_serial_sequence('orders', 'id'),
              (SELECT max(id) FROM users));
-- Next INSERT INTO orders (customer_id, amount) VALUES (...) →
-- nextval returns max(id)+1 → no collision.

-- ── Size comparison: bigint vs UUID ─────────────────────────────
-- BIGINT: 8 bytes. UUID: 16 bytes (2x).
-- In a B-tree index with 10M rows:
--   bigint PK index: ~80MB (8 bytes × 10M + overhead)
--   UUID PK index:   ~160MB (16 bytes × 10M + overhead)
-- Every FK that references the PK also doubles in size.
-- Every composite index that includes the PK column grows.
-- For large-scale systems, the 2x size difference is significant.

-- ── UUIDv7: time-ordered UUID for indexable distributed IDs ─────
-- UUIDv7 combines a Unix timestamp (first 48 bits) with random bits.
-- Monotonic-ish → append-only index behavior (no fragmentation).
-- PostgreSQL doesn't have built-in UUIDv7 (as of PG 16), but you can
-- generate it in the application or via a function:
-- (simplified — use a library in production)
CREATE OR REPLACE FUNCTION uuid_v7() RETURNS UUID AS $$
DECLARE
  unixts_ms BIGINT;
  uuid_bytes BYTEA;
BEGIN
  unixts_ms := (extract(epoch from now()) * 1000)::bigint;
  -- 48-bit timestamp + 12 random bits + version + 62 random bits + variant
  uuid_bytes := substring(int8send(unixts_ms) FROM 3 FOR 6)  -- 6 bytes timestamp
                || gen_random_bytes(10);                      -- 10 random bytes
  -- Set version (7) and variant (10xx) bits (omitted for brevity)
  RETURN encode(uuid_bytes, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- ❌ WRONG: using SERIAL for new tables (IDENTITY available since PG 10)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,           -- non-standard, harder to migrate, orphanable sequence
  name TEXT
);
-- Problems:
-- 1. Not standard SQL → not portable to other databases
-- 2. The sequence can be orphaned (DROP DEFAULT → sequence survives, junk)
-- 3. Resetting requires knowing the sequence name (pg_get_serial_sequence)
-- 4. pg_dump/restore can desync the sequence from the data

-- ✅ RIGHT: use IDENTITY for all new tables
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);
-- Standard SQL, proper dependency tracking, easy ALTER/RESTART,
-- clean dump/restore. Use SERIAL only for legacy compatibility.

-- ❌ WRONG: "fixing" gaps by reusing skipped IDs
-- A developer notices IDs go 1, 2, 3, 5, 6, 8 (gaps at 4, 7) and
-- writes code to "fill" them:
SELECT generate_series(1, max_id) EXCEPT SELECT id FROM users;  -- find gaps
-- Then inserts into the gaps. This is WRONG:
-- 1. Requires serialization (find gap → claim → insert) → kills throughput
-- 2. Breaks FK references: if id=4 was referenced by an order, then deleted,
--    reusing id=4 makes the order point to the NEW row (wrong data)
-- 3. Sequences are designed to be gap-tolerant — gaps are not errors

-- ✅ RIGHT: accept gaps. A sequence guarantees uniqueness, not consecutiveness.
-- If consecutive numbers are a legal requirement (invoices, checks), use a
-- counter table with explicit locking (see the "Sequence Gaps" section above).
-- For entity IDs, gaps are meaningless — nobody cares that user 4 doesn't exist.

-- Sequences are per-server state. Logical replication does NOT replicate
-- sequence values. In a primary + replica setup:
-- - Replicas have their own sequence state (may lag the primary)
-- - On failover, the new primary's sequence might be BEHIND the max(id)
--   already in the table → immediate duplicate-key errors on insert

-- Post-failover fix: advance ALL sequences past max(id)
-- Run this on the new primary after failover:
SELECT format(
  'SELECT setval(%L, (SELECT max(%I) FROM %I.%I), true);',
  pg_get_serial_sequence(n.nspname || '.' || c.relname, a.attname),
  a.attname, n.nspname, c.relname
)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE d.adbin LIKE '%nextval%'                          -- columns with sequence defaults
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');
-- Copy and run the output. Or use \gexec in psql.

-- Prevention: allocate IDs in blocks per node (no shared counter)
-- Node 1: CREATE SEQUENCE ... START 1 INCREMENT 1
-- Node 2: CREATE SEQUENCE ... START 1000000001 INCREMENT 1
-- No replication contention, no failover gap. Or use UUIDs (no shared counter).

ALTER SEQUENCE my_seq RESTART WITH 1000;      -- reset (next nextval → 1000)
ALTER SEQUENCE my_seq INCREMENT BY 10;        -- change the increment
ALTER SEQUENCE my_seq CACHE 1000;             -- change the cache size
ALTER SEQUENCE my_seq MINVALUE 1 MAXVALUE 1000000;  -- bounds
ALTER SEQUENCE my_seq CYCLE;                  -- wrap around at MAXVALUE (rare, dangerous)
ALTER SEQUENCE my_seq NO CYCLE;               -- error at MAXVALUE (default, safer)
-- WARNING: ALTER SEQUENCE ... RESTART affects ALL sessions immediately.
-- Concurrent sessions with cached values may still use their cache.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,           -- sequence users_id_seq starts at 1
  name TEXT
);

-- Migration: loaded dump with explicit IDs
COPY users (id, name) FROM '/tmp/users.csv' WITH CSV HEADER;
-- The dump has IDs 1..10000. COPY inserts them directly, bypassing
-- the DEFAULT nextval('users_id_seq'). The sequence is still at 1.

-- After migration, the app inserts a new user:
INSERT INTO users (name) VALUES ('new_user');
-- → nextval('users_id_seq') returns 1
-- → id=1 already exists (from the dump)
-- → ERROR: duplicate key value violates unique constraint "users_pkey"