Generated and identity columns

IDENTITY: the standard way to auto-number

For a surrogate primary key, the modern, SQL-standard choice is GENERATED ALWAYS AS IDENTITY (or BY DEFAULT). It replaces the old serial/bigserial pseudo-types:

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (user_id) VALUES (42);   -- id auto-assigned

• GENERATED ALWAYS AS IDENTITY — the column is engine-owned; a plain INSERT that tries to supply id is rejected (you must use OVERRIDING SYSTEM VALUE to force it). This is the safer default — it prevents the application from accidentally inserting an id that collides with the sequence.
• GENERATED BY DEFAULT AS IDENTITY — auto-assigns when you omit the value but allows an explicit one. Use this only when you must occasionally supply ids (e.g. data imports).

Why identity beats serial

serial was never a real type — it was sugar that quietly created a sequence, set it as the column default, and made the column own the sequence. That indirection caused real operational pain:

• Ownership/permission quirks. The sequence is a separate object with its own privileges; granting INSERT on the table didn’t grant usage on the sequence, so locked-down roles hit “permission denied for sequence”. With IDENTITY, the sequence is internal and the permission model is on the column.
• Fragile dumps and renames. serial’s default is a literal nextval('orders_id_seq') baked into the column; renaming the table or restoring into a different schema could leave the default pointing at the wrong sequence name.
• No real protection. serial doesn’t stop the app from inserting an explicit id and desyncing the sequence; GENERATED ALWAYS does.

IDENTITY is the SQL standard, cleaner to introspect, and the recommended choice for new tables. serial survives mainly in legacy schemas.

STORED generated columns: compute from other columns

A GENERATED ALWAYS AS (expr) STORED column is computed from other columns of the same row and physically stored — you never write it, and it stays consistent automatically. Perfect for a denormalized value you want to index or filter on without a trigger:

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  price_cents bigint NOT NULL,
  tax_cents   bigint NOT NULL,
  total_cents bigint GENERATED ALWAYS AS (price_cents + tax_cents) STORED
);
-- total_cents updates itself whenever price or tax changes; you can index it.

The expression must be deterministic and reference only the current row (no subqueries, no now(), no other tables). STORED writes the value to disk (costing storage and write time) but reads are free and it’s indexable. Postgres currently supports only STORED; there is no VIRTUAL (compute-on-read) generated column yet.

Sequences are non-transactional — gaps are normal

This is the part the Hook turns on. A sequence is a shared counter that lives outside transactional rules so that thousands of concurrent inserts don’t block each other waiting for the next number. The consequence: nextval is not rolled back. If a transaction grabs id 1003 and then aborts, 1003 is gone forever — the next insert gets 1004. Sequences also pre-allocate blocks (CACHE n) per backend, so a crash or a multi-connection workload scatters ids further.

So: an identity/serial id is a unique, monotonically increasing surrogate — never a count and never gapless. If you genuinely need gapless, human-facing sequential numbers (invoice numbers, legal documents), you build that separately with a serializing table row you UPDATE ... RETURNING under a lock, accepting the contention. Don’t ask the surrogate key to be something it structurally isn’t.

Identity vs uuid

Both give you a surrogate key without the application choosing it; the tradeoff:

• bigint identity — 8 bytes, monotonic, perfect index locality (appends to the B-tree’s right edge), and human-readable. But it leaks information (id 5000 tells a competitor your row count and growth rate) and it requires the central database to mint it, which is awkward across shards or for ids generated client-side before insert.
• uuid — 16 bytes, globally unique with no coordination (generate it anywhere, even offline), and unguessable (v4). But random v4 fragments the index (from lesson 01), and it’s twice the width — bigger keys mean bigger indexes and fatter foreign keys everywhere they’re referenced.

Together these two points mean: when you’re designing a key column, you’re making a decision about physical index structure, coordination requirements, and information leakage all at once — not just picking “a unique id.” The pragmatic default: bigint identity for internal keys; uuid (preferably v7 for locality, v4 when unguessability matters) when you need distributed generation or must not leak counts.