PostgreSQL

User preferences and memory live in ~/Clawic/data/pg/ (see setup.md on first use, memory-template.md for the file format). If you have data at an old location (~/pg/ or ~/clawic/pg/), move it to ~/Clawic/data/pg/.

When To Use

• A query is slow, a plan needs interpretation, or an index is being ignored
• Designing a schema: types, keys, constraints, partitions, JSONB vs columns
• Running DDL, a migration, or a backfill against a live production table
• Operating Postgres: connections, timeouts, vacuum, bloat, locks, replication, backups, upgrades
• A production incident: disk full, wraparound warnings, connection storm, runaway query, suspected corruption
• Building queue, upsert, search, or vector patterns that Postgres solves natively
• Not for cross-engine SQL portability or ORM-level modeling (see Related Skills)

Quick Reference

Depth on demand, by phase:

• Diagnose — slow-queries.md plan reading and misestimates · locks.md blocked queries and deadlocks · errors.md SQLSTATE to cause · monitoring.md what to watch and alert on · incidents.md disk full, wraparound, runaway query, corruption
• Design — schema-design.md types, keys, constraints · indexing.md index types and when each wins · partitioning.md tables too big for one heap · json.md JSONB modeling and indexing · search.md text, fuzzy, and vector search · queues.md job queues, retries, transactional outbox
• Change — migrations.md online DDL, backfills, expand/contract · bulk-load.md loading millions of rows · upgrades.md major versions, OS/glibc, extensions · functions-triggers.md plpgsql, triggers, LISTEN/NOTIFY · test-databases.md fast test isolation, anonymized copies, CI, rehearsals
• Operate — connections.md pooling and PgBouncer · vacuum-bloat.md autovacuum tuning and reclaim · tuning.md postgresql.conf memory, WAL, planner · backup-restore.md dumps, PITR, restore drills · replication.md replicas, lag, failover · security.md roles, grants, RLS, pg_hba · managed-postgres.md RDS, Aurora, Cloud SQL, Neon, Supabase · psql.md client workflow and scripting · extensions.md which extension solves this

Core Rules

1. Tune from a plan, never from query text. EXPLAIN (ANALYZE, BUFFERS); plain EXPLAIN prints estimates and the estimate is usually the bug. Check: estimated vs actual rows per node — off by more than 10x means a statistics problem, not a missing index.
2. Index every foreign key column you join or cascade on. Postgres indexes primary keys and unique constraints automatically, never FK columns. A parent DELETE of 10k rows against an unindexed 50M-row child is 10k sequential scans.
3. Composite index order: equality columns first, then one range or sort column. (tenant_id, created_at) answers WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 20 from the first 20 index entries; (created_at, tenant_id) walks every row in date order filtering as it goes, and gets slower as the tenant gets smaller. A column is usable only while every column to its left is pinned by equality.
4. Set the three timeouts per role before you need them, and start every live DDL with lock_timeout. Defaults to apply: lock_timeout = '2s' (variable lock_timeout_default), statement_timeout = '30s', idle_in_transaction_session_timeout = '5min'. A blocked ALTER TABLE waiting for ACCESS EXCLUSIVE queues every later query on that table behind it — the outage is the queue, not the lock. An idle transaction with no timeout freezes vacuum's horizon for the whole database, not just its own table.
5. work_mem is per sort/hash node, not per query. Worst case = connections × nodes per query × work_mem: 100 × 4 × 64MB = 25.6GB, the classic out-of-memory. Keep it small globally (4-16MB) and raise it per session for a known heavy query.
6. Pick the type that deletes a class of bugs. TIMESTAMPTZ (stores a UTC instant, not a zone) · money as NUMERIC(12,2) or integer cents, never float (0.1 + 0.2 ≠ 0.3) and never money · TEXT over VARCHAR(n) (identical performance; add length as a CHECK only if it is a business rule) · GENERATED ALWAYS AS IDENTITY over SERIAL (PostgreSQL >=10).
7. A schema change is expand → backfill in batches → contract, never one statement. One UPDATE over 50M rows writes 50M new row versions in a single transaction: the table doubles on disk, vacuum cannot reclaim anything until commit, and a failure at minute 50 discards everything. Batch 1k-50k rows with a commit and a resume key per batch.
8. A backup you have never restored is a hypothesis. Schedule the restore, not just the dump: restore into a scratch instance on a fixed cadence, time it, and write the measured RTO down. pg_dump also omits roles and tablespaces — only pg_dumpall --globals-only has them.

Slow-Query Triage

Run in this order; skipping to step 4 spends the fix on the wrong query.

1. pg_stat_statements ordered by total_exec_time DESC (needs shared_preload_libraries). High mean_exec_time = slow query; high calls = hot path — a 5ms query called 10k/min beats a 2s report as a target.
2. EXPLAIN (ANALYZE, BUFFERS) on the worst offender. Estimates are the thing that lies.
3. Compare estimated vs actual rows per node. Off by >10x → stale or insufficient statistics: run ANALYZE; still off → ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 (default 100), or CREATE STATISTICS for correlated columns (city+country style).
4. Read Buffers: read dominant → I/O-bound (missing index, cold cache); hit dominant → plan or CPU bound (wrong join order, overwide scan).
5. A seq scan is not automatically the bug: it beats an index once a query touches roughly 5-15% of the table (correlation-dependent). On SSD set random_page_cost = 1.1 (default 4.0 assumes spinning disk) or the planner will refuse good indexes.
6. Re-run step 2 after the fix and compare buffers and actual time — a prettier plan shape with the same buffer count fixed nothing.

Indexing Essentials

• Partial index size is proportional to matching rows: WHERE active over a 5%-active table is ~95% smaller and stays resident in cache.
• Expression index must match the query text exactly: ON lower(email) serves WHERE lower(email) = ?, not WHERE email ILIKE ?.
• Covering index INCLUDE (name) enables index-only scans — verify "Heap Fetches" near 0 in EXPLAIN; vacuum lag leaves a stale visibility map and silently degrades them back to heap fetches.
• Updating any indexed column defeats HOT updates and writes to every index on the table. Keep counters and churning timestamps out of indexes; set fillfactor = 90 on update-heavy tables so HOT has page room.
• Drop unused with care: pg_stat_user_indexes where idx_scan = 0, but only after a full business cycle (month-end reports) and checked on every replica — index statistics are per node.
• Do not index a low-cardinality column alone (boolean, three-value enum): the planner ignores it. Fold it into a composite or make the index partial.

Postgres-Native Query Patterns

• FOR UPDATE SKIP LOCKED: concurrent workers each claim unlocked rows; the canonical Postgres job queue, no broker required.
• pg_advisory_xact_lock(key): application mutex with no table, released automatically at commit. The session variant (pg_advisory_lock) survives commit and leaks when a pooled connection is recycled.
• IS NOT DISTINCT FROM: NULL-safe equality, replaces (a = b OR (a IS NULL AND b IS NULL)).
• x NOT IN (subquery) returns zero rows if the subquery yields a single NULL — use NOT EXISTS.
• count(*) > 0 scans every match; EXISTS (SELECT 1 ...) stops at the first.
• Aggregates with FILTER (WHERE ...) replace CASE-inside-SUM pivots, readably.
• now() is frozen at transaction start, so every row written in one long transaction shares a timestamp; clock_timestamp() gives wall time.
• CTEs: PostgreSQL >=12 inlines them like subqueries; below that every CTE is an optimization fence that blocks index pushdown. MATERIALIZED restores the fence deliberately.

Safe DDL on Busy Tables

Every recipe starts with SET lock_timeout = '2s' and a retry loop (rule 4). Full procedures, backfill loops and expand/contract sequencing in migrations.md.

• ADD COLUMN ... DEFAULT is metadata-only (PostgreSQL >=11); a volatile default (now(), gen_random_uuid()) rewrites the whole table. Add nullable, backfill in batches, then set the default.
• New constraint on a big table: ADD CONSTRAINT ... NOT VALID (instant), then VALIDATE CONSTRAINT (weak lock, full scan that does not block writes).
• SET NOT NULL: PostgreSQL >=12 skips the full-table scan when a validated CHECK (col IS NOT NULL) already proves it — add the check NOT VALID, validate, set not null, drop the check.
• CREATE INDEX CONCURRENTLY: no write lock, but it cannot run inside a transaction block and a failure leaves an INVALID index behind — check pg_index.indisvalid, drop, retry. Same for REINDEX CONCURRENTLY (PostgreSQL >=12).
• Type changes: widening varchar(n) or varchar → text is metadata-only; int → bigint rewrites the table. On a hot table that means new column, dual-write, backfill, swap.

Error Codes

SQLSTATE is stable across versions; the message text is not. Match on the code. Full catalog with fixes in errors.md.

Configuration

User-dependent variables. Defaults apply until the user states a preference; store them in ~/Clawic/data/pg/config.yaml.

Preference areas — customizable dimensions; a stated preference is recorded in config.yaml and applied from then on:

• Tooling — migration framework (plain SQL files, Flyway, Alembic, Prisma Migrate), diff tooling, bloat/repack tooling — affects the shape of emitted migrations
• Thresholds — per-role statement_timeout, backfill batch size, slow-query threshold worth reporting, index-bloat percentage that triggers a rebuild
• Conventions — schema layout (public vs per-domain schemas), enum vs lookup table, soft-delete policy, timestamp column names, index naming
• Platform — major version, OS and glibc/ICU provider, storage class (NVMe vs network disk), instance memory — affects tuning.md numbers
• Risk posture — whether to run DDL directly or hand back reviewed SQL, whether replicas may be read, how aggressive autovacuum tuning may be
• Output format — SQL only vs SQL plus a plan walkthrough, how much EXPLAIN detail to narrate, whether to include rollback SQL by default
• Integrations — monitoring stack (pg_stat_statements alone, Prometheus exporter, provider console), backup tooling (pgBackRest, WAL-G, provider snapshots)
• Restrictions — extensions the platform forbids, compliance regimes that mandate RLS or column encryption, tables that must never be touched online
• Cadence — restore-drill frequency, index-usage review cycle, statistics/vacuum maintenance windows

Output Gates

Before emitting DDL, a migration, or a schema:

• Does every new foreign key column get an index in the same migration?
• Does every statement touching a live table set lock_timeout first?
• Is the destructive step (drop column, drop table, drop index) split into its own later migration, after the code that stopped using it shipped?
• Is the backfill batched, resumable, and committed per batch?
• Are new indexes CONCURRENTLY and outside any transaction block?
• New tables: TIMESTAMPTZ timestamps, key per id_style, NOT NULL wherever it is true, uniqueness stated as a constraint?
• Did you run EXPLAIN (ANALYZE, BUFFERS) before and after, and compare buffers rather than wall time alone?

Traps

Where Experts Disagree

• bigint vs UUID primary keys. Random UUIDv4 fragments the B-tree and bloats every secondary index; bigint leaks row counts and needs coordination across writers. Boundary: external-facing or multi-writer IDs → UUID, time-ordered (v7) where available; internal high-write tables → bigint IDENTITY.
• Normalize vs denormalize. Postgres joins are cheaper than most engineers assume; denormalize only after a plan shows the join as the measured bottleneck, and prefer a materialized view over duplicated columns — it has a single refresh point.
• App-side pool vs PgBouncer. One or two services → the app pool suffices; many services or serverless connection churn → PgBouncer, accepting the session-state limits.
• Scale up vs scale out. Read replicas push lag handling into the application; a bigger box stays defensible far longer than fashion suggests. Reach for replicas when reads are heavy AND tolerant of staleness, not at the first slow query.
• JSONB as a schema escape hatch. One camp treats a jsonb column as pragmatic velocity; the other as a schema you failed to write. The testable boundary: any key you filter, join, or sort on regularly belongs in a real column, because jsonb keys carry no per-key statistics.

Related Skills

More Clawic skills, get them at https://clawic.com/skills/pg (install if the user confirms):

• sql — cross-engine SQL and portability; jump there when the target isn't Postgres-specific
• database-indexing — deeper index theory: write-cost budgets, bitmap/hash structures
• prisma — ORM-level schema modeling and query pitfalls on top of Postgres
• timescaledb — time-series workloads on Postgres: hypertables, compression, continuous aggregates
• db — general database operations, reliability, and scaling patterns

Part of Clawic, the verified skill library. Get this skill: https://clawic.com/skills/pg.