git:20260615.5ea6bc3 to git:20260729.3ecb845

50 added, 70 removed. Audit A to A.

---

name: postgresdb

- description: Use when designing or reviewing a PostgreSQL schema (types, constraints, normalization), writing or optimizing SQL, choosing or adding indexes, reading EXPLAIN/ANALYZE, planning a migration (expand-contract, concurrent index, batched backfill), or operating/securing Postgres (roles, RLS, PgBouncer pooling, VACUUM/autovacuum, pg_stat_statements, declarative partitioning, backups/PITR). Triggers - "slow query", "add an index", "EXPLAIN", "design this table", "zero-downtime migration", "row level security", "connection pool", "N+1", "JSONB", "full-text search", "pgvector". PostgreSQL 16; ORM-agnostic (SQLAlchemy/Alembic, Prisma, golang-migrate, raw SQL).

+ description: Use when PostgreSQL engine behaviour decides the answer — schema and type design, index choice, reading EXPLAIN on a slow query, zero-downtime DDL and backfills, or ops (roles, RLS, pooling, vacuum, partitioning, PITR). PG16, ORM-agnostic. NOT portable query logic (that is `sql`), NOT a managed provider's platform surface (that is `neon`).

tags: [postgres, sql, database, migrations]

recommends: [secure-coding]

origin: risco

---

# PostgreSQL — schema, indexing, queries, ops

Engine-level PostgreSQL 16 guidance: design correct schemas, pick the right index, read EXPLAIN and

fix slow SQL, run zero-downtime migrations, and operate/secure the database. Tooling-agnostic; every

example is runnable.

- ## When to use / When NOT to use

- > **⚠️ SDD new-feature gate — read this first.** If this skill fired on a **new, non-trivial feature or behaviour change** and there is **no approved spec + plan** under `02-DOCS/wiki/sdd/`, STOP — do **not** write feature code yet. Hand off to `../specify/SKILL.md` first: it runs brainstorm → spec → plan → tasks before any code, then routes back here once the plan is approved. Build here directly only for a genuinely one-line / low-risk change. Method: `../sdd/SKILL.md`.

- **When to use:**

- - Schema/DDL decisions: types, keys, constraints, generated/identity columns, enum-vs-lookup, jsonb-vs-column.

- - Any query that is slow, scans too much, or returns wrong cardinality; reading a plan.

- - Index decisions: which kind, column order, partial/covering, and when NOT to add one.

- - Migrations against tables with real data or live traffic.

- - Concurrency: isolation, locking, deadlocks, queues.

- - Ops: pooling, vacuum, monitoring, partitioning, backups, RLS, least-privilege roles.

- **When NOT to use:**

- - ORM-API ergonomics (Prisma `updateMany` count trap, SQLAlchemy session lifecycle) → your ORM's own docs. This skill owns the **SQL the ORM emits and the engine behavior underneath**.

- - Non-Postgres engines (MySQL/SQLite/ClickHouse) — different MVCC, locking, planner.

- - App-layer caching / Redis / Kafka as products; only Postgres-as-queue via `SKIP LOCKED` is in scope.

- - Cloud-vendor console clicks — we give the SQL/params, not the RDS/Cloud SQL UI path.

+ New, non-trivial feature with no approved spec + plan under `02-DOCS/wiki/sdd/`? Hand off to

+ [`specify`](../specify/SKILL.md) before writing feature code (method: [`sdd`](../sdd/SKILL.md)); build

+ straight from here only for a genuinely one-line, low-risk change.

Deep dives: [schema-and-indexing](references/schema-and-indexing.md) (types, constraints, every index

kind, bloat) · [query-optimization](references/query-optimization.md) (EXPLAIN, joins, concurrency,

JSONB/FTS/pgvector) · [migrations](references/migrations.md) (zero-downtime DDL, per-ORM) ·

[operations-and-security](references/operations-and-security.md) (roles, RLS, pooling, vacuum,

partitioning, backups).

- ## Non-negotiables

- 1. `timestamptz` always for events; store UTC. Never naive `timestamp`.

- 2. Money is `numeric(19,4)`, never `float`/`double precision`.

- 3. Every FK gets a covering index — Postgres does **not** auto-index FK columns.

- 4. `text` + `CHECK (length(...) <= n)`, not `varchar(n)` as a length hack.

- 5. Index creation on a live table is **always** `CONCURRENTLY` (so it cannot run inside a txn).

- 6. Never `ADD COLUMN ... NOT NULL` without a default/backfill plan; never add a volatile default on a large table without a batched backfill.

- 7. Read the plan before adding an index: `EXPLAIN (ANALYZE, BUFFERS)` or it didn't happen.

- 8. Migrations are forward-only in prod; never edit an applied migration.

- 9. `SET lock_timeout` + `SET statement_timeout` around DDL on hot tables.

- 10. RLS is opt-in per table and the **table owner bypasses it** — verify with a non-owner role (use `FORCE ROW LEVEL SECURITY` for the owner too).

+ **Not this skill.** ORM-API ergonomics (Prisma `updateMany` count trap, SQLAlchemy session lifecycle)

+ and per-runner migration wiring → that tool's own docs; this skill owns the **SQL the ORM emits and

+ the engine behavior underneath**. Other engines → [`mysql`](../mysql/SKILL.md),

+ [`sqlite-turso`](../sqlite-turso/SKILL.md), [`clickhouse-analytics`](../clickhouse-analytics/SKILL.md)

+ (different MVCC, locking, planner). App-layer caching / Redis / Kafka as products are out — only

+ Postgres-as-queue via `SKIP LOCKED` is in scope. Cloud-vendor console clicks →

+ [`deployment`](../deployment/SKILL.md); we give the SQL and params, not the RDS/Cloud SQL UI path.

## Decision rules

Fast lookups; runnable DDL lives in the references.

### Pick the column type

| --- | --- | --- | --- |

### Pick the index

| --- | --- | --- | --- |

Hash indexes: almost never — equality-only, no multicolumn, rarely beats btree even though WAL-logged

since PG10.

+ Read the plan **before** adding an index — `EXPLAIN (ANALYZE, BUFFERS)` or it didn't happen. An index

+ the planner never picks is pure write tax on every insert and update, forever.

### When NOT to add an index

- Low-selectivity boolean / `status` with few distinct values (planner ignores it; seq scan wins).

- Tiny tables (a seq scan reads one page; the index adds maintenance for nothing).

- Write-heavy columns rarely filtered — every index is a write tax.

- A column already the **left prefix** of an existing composite index.

- Redundant with a `UNIQUE` constraint — the constraint already created an index.

## Copy-paste patterns

### Canonical table (types + constraints + identity)

```sql

-- GOOD: identity PK, public uuid, FK with action, numeric money, timestamptz,

-- status via lookup FK, generated tsvector, CHECK constraints.

CREATE TABLE orders (

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

public_id uuid NOT NULL DEFAULT gen_random_uuid(), -- v4; see schema ref for v7

user_id bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT,

status text NOT NULL REFERENCES order_statuses (code) ON UPDATE CASCADE,

amount numeric(19,4) NOT NULL CHECK (amount >= 0),

currency text NOT NULL CHECK (length(currency) = 3),

note text,

search tsvector GENERATED ALWAYS AS (to_tsvector('simple', coalesce(note, ''))) STORED,

created_at timestamptz NOT NULL DEFAULT now(),

updated_at timestamptz NOT NULL DEFAULT now(),

UNIQUE (public_id)

);

CREATE INDEX ix_orders_user_id ON orders (user_id); -- FKs are NOT auto-indexed

CREATE INDEX ix_orders_status ON orders (status);

```

```sql

-- BAD: every column is a future migration or a bug.

CREATE TABLE orders (

id serial PRIMARY KEY, -- sequence-ownership gotchas; use IDENTITY

user_id int REFERENCES users(id), -- int overflows at 2.1B; no FK index

status varchar(20), -- length hack; no constraint on values

amount float, -- money drift

created_at timestamp DEFAULT now() -- naive: loses the zone

);

```

`updated_at` is not auto-maintained — add a `BEFORE UPDATE` trigger (see schema ref) or set it in the

app; Postgres has no `ON UPDATE` clause.

### Composite index column order

Equality columns first, then the range/sort column.

```sql

-- Query: WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at DESC

-- GOOD: equality (user_id) then range (created_at)

CREATE INDEX ix_orders_user_created ON orders (user_id, created_at DESC);

-- BAD: range-first index cannot satisfy the equality efficiently for this query

CREATE INDEX ix_orders_created_user ON orders (created_at, user_id);

```

Confirm with `EXPLAIN` that the plan shows `Index Cond: (user_id = ... AND created_at >= ...)`, not a

`Filter:`.

### Partial + covering (INCLUDE) index

```sql

-- Partial: index only the rows you query (smaller, hotter)

CREATE INDEX ix_orders_active ON orders (user_id, created_at DESC)

WHERE status <> 'cancelled';

-- Covering: INCLUDE non-key columns to enable an index-only scan

CREATE INDEX ix_orders_user_cover ON orders (user_id) INCLUDE (amount, created_at);

```

Index-only scan requires a recently-`VACUUM`ed table; confirm `Heap Fetches: 0` in `EXPLAIN (ANALYZE)`.

### Keyset (cursor) pagination — not OFFSET

```sql

-- GOOD: keyset on a stable composite sort; uses ix_orders_user_created

SELECT id, amount, created_at

FROM orders

WHERE user_id = $1

AND (created_at, id) < ($2, $3) -- row-value comparator = last row of prev page

ORDER BY created_at DESC, id DESC

LIMIT 20;

-- BAD: OFFSET scans and discards 100000 rows every page (O(n))

SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

```

The index must match the `ORDER BY` direction exactly; include the tiebreaker (`id`).

### UPSERT done right

```sql

-- GOOD: insert-or-update; EXCLUDED is the row that failed to insert

INSERT INTO inventory (sku, qty)

VALUES ($1, $2)

ON CONFLICT (sku)

DO UPDATE SET qty = inventory.qty + EXCLUDED.qty

WHERE inventory.qty + EXCLUDED.qty >= 0 -- guard

RETURNING id, qty;

-- DO NOTHING returns no row on conflict; wrap to always get the row:

WITH ins AS (

INSERT INTO tags (name) VALUES ($1)

ON CONFLICT (name) DO NOTHING

RETURNING id

)

SELECT id FROM ins

UNION ALL

SELECT id FROM tags WHERE name = $1 LIMIT 1;

```

### Queue with SKIP LOCKED

```sql

-- GOOD: contention-free job claim; concurrent workers never block each other

UPDATE jobs

SET status = 'processing', locked_at = now()

WHERE id = (

SELECT id FROM jobs

WHERE status = 'pending'

ORDER BY created_at

FOR UPDATE SKIP LOCKED

LIMIT 1

)

RETURNING id, payload;

```

`SKIP LOCKED` skips rows another txn holds; `FOR UPDATE` alone would serialize all workers.

### Kill the N+1

```sql

-- BAD: application loops, one query per order (N+1)

-- for o in orders: SELECT * FROM order_items WHERE order_id = o.id

-- GOOD: one set-based query

SELECT o.id, json_agg(i.*) AS items

FROM orders o

JOIN order_items i ON i.order_id = o.id

WHERE o.user_id = $1

GROUP BY o.id;

-- Top-N-per-group: JOIN LATERAL, not a window-filter scan

SELECT u.id, recent.*

FROM users u

JOIN LATERAL (

SELECT id, amount, created_at FROM orders

WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3

) recent ON true;

```

An ORM emitting N queries is the same bug — fix it at the SQL boundary, not with a cache.

### EXPLAIN, the right way

```sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)

SELECT * FROM orders WHERE user_id = $1 AND created_at >= $2;

```

Read these four first:

1. **Estimated vs actual rows** — a large gap means stale stats; run `ANALYZE`.

2. **The most expensive node** — highest `actual time` × `loops`.

3. **`Seq Scan` on a big table** where you expected an index.

4. **`Rows Removed by Filter`** — the predicate was not pushed to an index.

Full method in [query-optimization](references/query-optimization.md).

- ## Anti-patterns / rationalizations → STOP

+ ## DDL on a live table

- | Rationalization | Reality → STOP |

+ Full sequences (zero-downtime expand-contract, batched backfills, per-ORM runners) in

+ [migrations](references/migrations.md). These four are absolute because each one is a lock you cannot

+ take back once traffic is on the table:

+ 1. Index creation on a live table is **always** `CONCURRENTLY` — plain `CREATE INDEX` holds ACCESS

+ EXCLUSIVE for the entire build and blocks every writer. It therefore cannot run inside a txn.

+ 2. Never `ADD COLUMN ... NOT NULL` without a default/backfill plan, and never add a **volatile**

+ default (`now()`, `gen_random_uuid()`) on a large table without a batched backfill — a volatile

+ default rewrites the whole table under ACCESS EXCLUSIVE. A non-volatile constant is instant (PG11+).

+ 3. `SET lock_timeout` + `SET statement_timeout` around DDL on hot tables, so a blocked statement fails

+ fast instead of parking an ACCESS EXCLUSIVE request that every reader behind it then queues on.

+ 4. Migrations are forward-only in prod; never edit an applied migration — it has already run

+ somewhere, so the next environment replays a history that no longer matches the one in production.

+ Lock modes: `CREATE INDEX CONCURRENTLY` takes SHARE UPDATE EXCLUSIVE (allows writes); plain

+ `CREATE INDEX`, `ALTER TABLE ... TYPE`, `ADD COLUMN` with a volatile default, and `VACUUM FULL` take

+ ACCESS EXCLUSIVE (blocks everything). Full table in

+ [migrations](references/migrations.md#lock-impact-reference).

+ ## Anti-patterns

+ | Claim | Reality |

| --- | --- |

| "I'll add the FK index later, the query works now" | Unindexed FK = seq scan + heavy lock cascade on parent `DELETE`/`UPDATE`. Index it now. |

| "UUID PK is fine everywhere" | Random v4 fragments the B-tree and bloats WAL. Use `IDENTITY` internally or uuid **v7**. |

| "`SELECT count(*)` to check existence" | Counts the whole match. Use `EXISTS (SELECT 1 ...)`. |

| "CTEs are just for readability" | Pre-12 they were optimization fences; PG12+ inlines unless `MATERIALIZED`. Know which you want. |

| "`NOT IN (subquery)`" | NULL-unsafe (one NULL → empty result) and slow. Use `NOT EXISTS`. |

| "Store money as float, round on display" | Silent drift across arithmetic. `numeric(19,4)`. |

| "`ADD COLUMN ... NOT NULL DEFAULT now()`" | Volatile default rewrites the table under ACCESS EXCLUSIVE. Non-volatile constant is instant (PG11+). |

| "One big `jsonb` blob beats columns" | No constraints, no per-key stats, GIN bloat. Promote hot keys to typed columns. |

+ | "RLS is on, so the table is protected" | RLS is opt-in per table and the **table owner bypasses it**. Verify with a non-owner role; add `FORCE ROW LEVEL SECURITY` to cover the owner too. |

| "RLS policy calling `auth.uid()` per row" | Re-evaluated per row. Wrap: `(SELECT auth.uid())` so it runs once. |

| "`CREATE INDEX` in the migration is fine" | Blocks writes for the whole build. `CREATE INDEX CONCURRENTLY` (outside a txn). |

| "`VACUUM FULL` will fix bloat" | Takes ACCESS EXCLUSIVE, rewrites the table. Use autovacuum tuning / `REINDEX CONCURRENTLY`. |

## Quick reference

### Isolation levels

| --- | --- | --- | --- |

Retry the txn on SQLSTATE `40001` (serialization_failure) and `40P01` (deadlock_detected).

- ### Lock modes (DDL)

- `CREATE INDEX CONCURRENTLY` takes SHARE UPDATE EXCLUSIVE (allows writes); plain `CREATE INDEX`,

- `ALTER TABLE ... TYPE`, `ADD COLUMN` with a volatile default, and `VACUUM FULL` take ACCESS EXCLUSIVE

- (blocks everything). Full table in [migrations](references/migrations.md#lock-impact-reference).

### Diagnostic one-liners

```sql

-- Unindexed foreign keys

SELECT c.conrelid::regclass AS tbl, a.attname AS col

FROM pg_constraint c

JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)

WHERE c.contype = 'f'

AND NOT EXISTS (

SELECT 1 FROM pg_index i

WHERE i.indrelid = c.conrelid AND (i.indkey::int2[])[0] = a.attnum

);

-- Top slow queries (needs pg_stat_statements)

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,

round(total_exec_time::numeric, 2) AS total_ms, query

FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Dead tuples / bloat candidates

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum

FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;

-- Blocking locks

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query

FROM pg_stat_activity blocked

JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted

JOIN pg_locks gl ON gl.locktype = bl.locktype AND gl.database IS NOT DISTINCT FROM bl.database

AND gl.relation IS NOT DISTINCT FROM bl.relation AND gl.granted

JOIN pg_stat_activity blocking ON blocking.pid = gl.pid;

-- Cache hit ratio (aim > 0.99)

SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS ratio

FROM pg_statio_user_tables;

-- Unused indexes

SELECT relname, indexrelname, idx_scan

FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

```

## Verify

- Run `scripts/verify.sh` from your project root. It lints discovered SQL with `sqlfluff` (if

+ Run `scripts/verify.sh` from your project root: it lints discovered SQL with `sqlfluff` (if

configured), syntax-sanity-checks migration files (the quote/paren balance check is dollar-quote and

- block-comment aware) and flags foot-guns (`CREATE INDEX` without `CONCURRENTLY` in a migration,

+ block-comment aware), flags foot-guns (`CREATE INDEX` without `CONCURRENTLY` in a migration,

`ADD COLUMN ... NOT NULL` without `DEFAULT`, `VACUUM FULL`), and — only if `DATABASE_URL` and `psql`

are present — checks that `pg_stat_statements` is enabled. It exits non-zero **only** on a real

- `sqlfluff` lint error; everything else (missing tools, heuristic quote/paren warnings, DB unreachable)

- is advisory `[skip]`/`[warn]`, never a failure. Runs on stock macOS bash 3.2; it never writes and

- never connects without `DATABASE_URL`.

- ## Project grounding (02-DOCS + CLAUDE.md)

- When this skill runs in a project with a `02-DOCS/` layer (the

- [`harness`](../harness/SKILL.md) Karpathy wiki), record this

- project's database decisions there and index them from the root `CLAUDE.md`, so the next

- agent inherits the conventions instead of re-deriving them.

- 1. **Find the article** `02-DOCS/wiki/stack/postgresdb.md`, indexed in `02-DOCS/wiki/index.md` (the

- Knowledge map index; root `CLAUDE.md` points to it).

- 2. **If missing or stale**, create/update it with the project's real choices — schema and naming conventions, the migration tool, indexing/partitioning decisions, the pooling setup, and any RLS policies —

- then index it in `02-DOCS/wiki/index.md` (the Knowledge map; root `CLAUDE.md` keeps only a

- short pointer to it).

- 3. **Read it first on every use** and stay consistent; when a convention changes, update the

- article (bump its `Updated` date) in the same change.

- No `02-DOCS/` layer? Skip silently (optionally suggest `harness`). Unlike the

- brand study, technical conventions are *recorded, not gated* — never block the task on this.

+ `sqlfluff` lint error; everything else (missing tools, heuristic warnings, DB unreachable) is advisory

+ `[skip]`/`[warn]`. Runs on stock macOS bash 3.2; never writes, never connects without `DATABASE_URL`.

- ## See Also

+ ## Project grounding

- - [references/schema-and-indexing.md](references/schema-and-indexing.md) — types, constraints, all index kinds, bloat.

- - [references/query-optimization.md](references/query-optimization.md) — EXPLAIN, joins, concurrency, JSONB/FTS/pgvector.

- - [references/migrations.md](references/migrations.md) — zero-downtime DDL, lock-impact table, per-ORM.

- - [references/operations-and-security.md](references/operations-and-security.md) — roles, RLS, pooling, vacuum, partitioning, backups.

- - Sibling skills: [`harness`](../harness/SKILL.md) (scaffolds the `01-TOOLS/POSTGRES` operational tool). Stack siblings: [`fastapi`](../fastapi/SKILL.md), [`nextjs`](../nextjs/SKILL.md), [`go`](../go/SKILL.md), [`flutter`](../flutter/SKILL.md), [`secure-coding`](../secure-coding/SKILL.md), [`deployment`](../deployment/SKILL.md).

- - Out of scope here — these are external tools with their own upstream documentation, not skills in this collection: ORM-surface traps (Prisma's `updateMany` count, serverless connection exhaustion) and per-runner migration wiring (Prisma, Alembic, golang-migrate, Drizzle, …). This skill owns the engine-level SQL and migration mechanics those tools sit on top of; for the tool's own API/CLI, read that tool's docs.

+ In a project with a `02-DOCS/` layer (the [`harness`](../harness/SKILL.md) Karpathy wiki), read

+ `02-DOCS/wiki/stack/postgresdb.md` first and stay consistent with it. Missing or stale? Write the

+ project's real choices there — schema and naming conventions, migration tool, indexing/partitioning

+ decisions, pooling setup, RLS policies — index it in `02-DOCS/wiki/index.md` (the Knowledge map; root

+ `CLAUDE.md` keeps only a pointer), and bump its `Updated` date in the same change, so the next agent

+ inherits the conventions instead of re-deriving them. No `02-DOCS/` layer? Skip silently (optionally

+ suggest `harness`) — technical conventions are *recorded, not gated*; never block the task on this.