Postgresql

PostgreSQL

Data Type Defaults

Schema Rules

• Every FK column gets an index (PG does NOT auto-create these)
• NOT NULL on every column unless NULL has business meaning
• CHECK constraints for domain rules at DB level
• EXCLUDE constraints for range overlaps: EXCLUDE USING gist (room WITH =, during WITH &&)
• Default created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• Separate updated_at with trigger, never trust app layer alone
• Use BIGINT PKs -- cheaper JOINs than UUID, better index locality
• Safe migrations: CREATE INDEX CONCURRENTLY, add columns with DEFAULT (instant add). Never ALTER TYPE on large tables in-place.
• NULLS NOT DISTINCT on unique indexes (PG15+) -- treats NULLs as equal for uniqueness
• Under NULLS NOT DISTINCT, a pre-flight duplicate check written with SQL = misses NULL/NULL collisions -- the index rejects the second row, but NULL = NULL evaluates to NULL (not true), so a self-join or WHERE a.col = b.col probe silently skips exactly the pairs the index will reject. Write the probe with IS NOT DISTINCT FROM so NULL/NULL compares as equal.
• Revoke default public schema access: REVOKE ALL ON SCHEMA public FROM public

Migration Safety

Core rules:

• Every schema change is a migration. No ad-hoc DDL in production.
• Migrations are immutable once deployed -- never edit a migration that has run in any shared environment.
• Schema migrations and data migrations are separate files. Schema changes are fast and transactional; data backfills are slow and may need batching.
• Forward-only in production. Rollback = a new forward migration that reverses the change.

Expand-contract pattern for zero-downtime renames and removals:

1. Expand: add the new column/table, backfill data, update writes to populate both old and new 2. Migrate: switch reads to the new column/table, verify in production 3. Contract: remove the old column/table in a later deploy

Never rename or remove a column in a single migration -- callers reading the old name will break between deploy and code rollout.

Dangerous operations:

• NOT NULL without a DEFAULT on an existing table locks and rewrites every row. Add the column nullable first, backfill, then add the constraint.
• CREATE INDEX (without CONCURRENTLY) locks writes for the duration. Always use CONCURRENTLY, which cannot run inside a transaction block -- keep it in its own migration.
• Large data backfills: batch with FOR UPDATE SKIP LOCKED to avoid locking the entire table:

UPDATE target SET new_col = compute(old_col)
WHERE id IN (
  SELECT id FROM target
  WHERE new_col IS NULL
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
);

Run in a loop until zero rows affected.

Full-replace clobber on read-modify-write loops. A migration that loops SELECT col → mutate in app → UPDATE SET col = new_full_value WHERE id = ? silently drops concurrent writes that landed between SELECT and UPDATE. Any column written by live traffic is exposed: jsonb documents, comma-separated tag fields, denormalized counters, JSON-encoded attribute blobs. Mitigations, in order of preference:

• In-place atomic update when the edit is expressible as SQL: UPDATE t SET col = jsonb_set(col, '{path}', :value) WHERE ..., or UPDATE t SET tags = array_append(tags, :tag) WHERE ... — no read-modify-write window.
• Row-level lock during the loop: wrap each iteration in a transaction, SELECT ... WHERE id = ? FOR UPDATE, then mutate and write. Cheaper to author, accepts more lock contention.
• Compare-and-swap retry: include the original snapshot in WHERE col = :original_value, check the affected-row count; on 0, re-read and retry. Robust under contention, requires explicit retry-loop handling.

Default chunked decode-encode loops are only safe during a maintenance window with writes blocked. ORM "chunkById + load + mutate + save" patterns hit this same trap.

Index Strategy

Index rules:

• Composite: most selective column first, max 3-4 columns
• Partial: WHERE status = 'active' -- smaller, faster
• Covering: INCLUDE (col) -- avoids heap lookup
• Expression: ON (lower(email)) -- for function-based WHERE
• fillfactor = 70-90 on write-heavy tables -- reserves space for HOT updates, reducing index bloat
• Drop unused indexes (only after one full business cycle since last restart -- check pg_stat_database.stats_reset first, otherwise you may drop a primary key on a freshly restarted DB or read replica): SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0

Detect unindexed foreign keys:

SELECT conrelid::regclass, a.attname
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 a.attnum = ANY(i.indkey)
  );

JSONB Patterns

-- GIN index for containment queries
CREATE INDEX ON items USING gin (metadata);
SELECT * FROM items WHERE metadata @> '{"status": "active"}';

-- Expression index for specific key access
CREATE INDEX ON items ((metadata->>'category'));
SELECT * FROM items WHERE metadata->>'category' = 'electronics';

Prefer typed columns over JSONB for frequently queried, well-structured data. Use JSONB for truly dynamic/variable attributes.

Use jsonb_path_ops operator class for containment-only (@>) queries -- 2-3x smaller index. Use default jsonb_ops when key-existence (?, ?|) is needed.

Delete operators:

Common mistakes:

• col - 'a,b' treats 'a,b' as a single key name (no-op against a normally-structured document — the comma isn't a path separator).
• col - 'a' - 'b' first removes the entire a subtree before attempting - 'b' on the result (data loss of a.*, then a no-op).
• jsonb_set(col, '{a,b}', 'null'::jsonb) sets the value to JSON null rather than removing the key — strict "key absent" checks downstream then fail. Worse: jsonb_set(col, '{a,b}', NULL) with a bare SQL NULL makes the STRICT function return SQL NULL, clobbering the entire column on update. To delete the key, use #-; to set it explicitly to JSON null, use 'null'::jsonb (and know that's distinct from absence).

For nested deletes, use #- with a text-array path. Verify with one round-tripped row of the worst-case shape before committing the migration: SELECT col #- '{a,b}' FROM t WHERE id = ? LIMIT 1, then confirm the key is gone (not present-as-null, no sibling data loss).

Row-Level Security (RLS)

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- applies to table owner too

-- Set session context (generic, no extensions needed)
SET app.current_user_id = '123';

CREATE POLICY orders_user_policy ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::bigint);

Performance: Policy expressions evaluate per row. Wrap function calls in a scalar subquery so PG evaluates once and caches:

-- BAD: called per row
USING (get_current_user() = user_id)
-- GOOD: evaluated once, cached
USING ((SELECT get_current_user()) = user_id)

Always index columns referenced in RLS policies. For complex multi-table checks, use SECURITY DEFINER helper functions.

Query Optimization

• Always EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) before optimizing
• Use pg_stat_statements for slow-query detection and pg_stat_user_tables for bloat (see Detection queries below for the full SQL)
• Sequential scan on large table -> add index or check WHERE for function wrapping
• High rows removed by filter -> index doesn't match predicate
• CTEs are inlined by default; use MATERIALIZED/NOT MATERIALIZED hints to control optimization
• Prefer EXISTS over IN for correlated subqueries
• Use LATERAL JOIN when subquery needs outer row reference
• Cursor pagination (WHERE id > $last ORDER BY id LIMIT $n) over OFFSET
• Approximate row counts: SELECT reltuples FROM pg_class WHERE relname = 'table' -- avoids full count(*) on large tables
• Materialized views for expensive aggregations: REFRESH MATERIALIZED VIEW CONCURRENTLY (needs unique index). Schedule refresh, not per-query.

Concurrency Patterns

See concurrency-patterns.md for UPSERT, deadlock prevention, N+1 elimination, batch inserts, and queue processing with SKIP LOCKED.

Partitioning

Use when table exceeds ~100M rows or needs TTL purge:

• RANGE -- time-series (by month/year), most common
• LIST -- categorical (by region, tenant)
• HASH -- even distribution when no natural key

Partition key must be in every unique/PK constraint. Create indexes on partitions, not parent.

Transactions & Locking

• Keep transactions short -- long txns block vacuum and bloat tables
• Advisory locks for application-level mutual exclusion: pg_advisory_xact_lock(key)
• Non-blocking alternative: pg_try_advisory_lock(key) -- returns false instead of waiting
• Check blocked queries: SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'
• Monitor deadlocks: SELECT deadlocks FROM pg_stat_database WHERE datname = current_database()
• `SELECT ... FOR UPDATE` only locks rows that already exist -- it does not prevent a phantom insert of a missing row. Two transactions can both query a key, both see no row, both proceed to insert; the second fails the unique constraint (or both succeed if none existed). For a get-or-create / insert-if-missing race, FOR UPDATE is the wrong tool -- use a partial unique index + INSERT ... ON CONFLICT DO NOTHING/UPDATE, or serialize the key with pg_advisory_xact_lock(hashtext(:key)) before the existence check.
• A unique-violation (SQLSTATE 23505) caught inside an open transaction can't continue in that same transaction -- once any statement raises, the transaction enters the aborted state and every later statement fails with current transaction is aborted, commands ignored until end of transaction block. Wrap the risky statement in a SAVEPOINT and ROLLBACK TO SAVEPOINT on error, or push the insert-or-update into a single ON CONFLICT statement that never raises. A bare try/catch around the failing statement is not enough on PostgreSQL.
• A nested `BEGIN` (or framework `transaction()` wrapper) becomes a `SAVEPOINT`, not an independent transaction -- only the outermost BEGIN is a real transaction. A per-iteration "transaction" inside an outer one does not commit independently and does not release row locks between iterations (held until the outer COMMIT); an unhandled inner error aborts the whole outer transaction. For a long backfill that needs per-row commit and lock release, run each unit as its own top-level transaction -- don't nest it under an outer one.

Full-Text Search

See full-text-search.md for weighted tsvector setup, query syntax, highlighting, and when to use PG full-text vs external search.

Connection Pooling

Always pool in production. Direct connections cost ~10MB each.

• PgBouncer in transaction mode for most workloads
• statement mode if no session-level features (prepared statements, temp tables, advisory locks)

Prepared statement caveat: Named prepared statements are bound to a specific connection. In transaction-mode pooling, the next request may hit a different connection. Use unnamed/extended-query-protocol statements (most ORMs default to this), or deallocate immediately after use.

Operations

See operations.md for performance tuning, maintenance/monitoring, WAL, replication, and backup/recovery.

Vector Search (pgvector)

CREATE EXTENSION vector;
ALTER TABLE items ADD COLUMN embedding vector(1536);  -- match your model's output dimensions

-- HNSW: better recall, higher memory. Default choice.
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- IVFFlat: lower memory for large datasets. Set lists = sqrt(row_count).
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 1000);

Always filter BEFORE vector search (use partial indexes or CTEs with pre-filtered rows). Distance operators: <=> cosine, <-> L2, <#> inner product.

Anti-Patterns

Detection queries:

-- Slow queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC LIMIT 20;

-- Table bloat (dead tuples awaiting vacuum)
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Unused indexes (candidates for removal)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Verify

Run EXPLAIN (ANALYZE, BUFFERS) on changed queries. Confirm no sequential scans on large tables and no unindexed FK columns before declaring done.

Concurrency Patterns

UPSERT -- atomic insert-or-update, avoids race conditions:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now()
RETURNING *;

Deadlock prevention -- acquire locks in deterministic order:

SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Or collapse into single atomic statement:
UPDATE accounts SET balance = balance + CASE id
  WHEN 1 THEN -100 WHEN 2 THEN 100 END
WHERE id IN (1, 2);

N+1 elimination -- batch with array parameter instead of per-row queries:

SELECT * FROM orders WHERE user_id = ANY($1::bigint[]);

Batch inserts -- multi-row VALUES (up to ~1000 per batch), or COPY for bulk loading:

INSERT INTO events (user_id, action) VALUES
  (1, 'click'), (1, 'view'), (2, 'click');

Queue processing:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

PostgreSQL Full-Text Search

Weighted tsvector with generated column

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')), 'B')
  ) STORED;

CREATE INDEX ON articles USING gin (search_vector);

SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', $1)) DESC;

Weight priority: A > B > C > D. Use A for title/heading, B for body, C for metadata, D for ancillary.

Query syntax

-- Simple search
WHERE search_vector @@ to_tsquery('english', 'postgres & replication');

-- Web-style (handles phrases, OR, negation automatically)
WHERE search_vector @@ websearch_to_tsquery('english', '"full text" search -spam');

-- Prefix matching
WHERE search_vector @@ to_tsquery('english', 'post:*');

Highlighting

SELECT ts_headline('english', body,
  websearch_to_tsquery('english', $1),
  'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
) AS snippet
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', $1);

When to use PG full-text vs external

Use PG full-text search when:

• Data is already in PostgreSQL
• Search needs are straightforward (keyword, phrase, prefix)
• Consistency matters (no sync lag between DB and search index)

Consider Elasticsearch/Typesense/Meilisearch when:

• Fuzzy matching, typo tolerance, or faceted search needed
• Search corpus exceeds ~10M documents with complex ranking
• Real-time autocomplete with sub-50ms latency required

PostgreSQL Operations

Performance Tuning

Key postgresql.conf parameters (adjust for available RAM):

• shared_buffers = 25% of RAM
• effective_cache_size = 75% of RAM
• work_mem = RAM / max_connections / 4 (start 4-16MB)
• maintenance_work_mem = 256MB-1GB
• random_page_cost = 1.1 for SSD (default 4.0 is for HDD)

Maintenance & Monitoring

• pg_stat_statements extension -- find slow queries by total time, not just duration
• pg_stat_user_tables -- check n_dead_tup for vacuum needs, last_autovacuum timestamps
• Cache hit ratio (should be > 99%): SELECT sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) FROM pg_statio_user_tables

Autovacuum tuning for hot tables:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- default 0.2
  autovacuum_analyze_scale_factor = 0.02
);

XID wraparound prevention -- monitor transaction ID age (emergency shutdown at 2B):

SELECT datname, age(datfrozenxid),
  round(100.0 * age(datfrozenxid) / 2147483648, 2) AS pct_to_wraparound
FROM pg_database ORDER BY age DESC;

Set idle_in_transaction_session_timeout = '30s' and statement_timeout = '30s' to prevent long-running transactions from blocking vacuum.

WAL (Write-Ahead Logging)

Changes write to pg_wal/ before data files. Checkpoints flush dirty pages to disk. If "checkpoints occurring too frequently" appears in logs, increase max_wal_size. Never disable fsync.

Key config:

• checkpoint_timeout = 5min (default, usually fine)
• checkpoint_completion_target = 0.9 (spread I/O)
• max_wal_size -- increase if checkpoint warnings appear

Monitor WAL disk usage:

SELECT count(*) AS files, pg_size_pretty(sum(size)) AS total
FROM pg_ls_waldir();

Replication

Streaming replication sends WAL to hot standbys (read-only). Replication slots guarantee WAL retention but can exhaust disk if standby goes offline -- use max_slot_wal_keep_size to cap.

Monitor lag:

SELECT application_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)/1024/1024 AS lag_mb
FROM pg_stat_replication;

Monitor slot lag (prevent disk exhaustion):

SELECT slot_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)/1024/1024 AS mb_behind
FROM pg_replication_slots;

Synchronous commit levels: off (lose ~600ms on crash) to remote_apply (read-your-writes guarantee). Provision N+1 standbys for N required confirmations.

Failover: SELECT pg_promote() (PG12+). Use pg_rewind to resync old primary as new standby without full rebuild (requires wal_log_hints=on or data checksums).

Backup & Recovery

Without PITR, RPO = backup interval (often 24h). With continuous WAL archiving, RPO drops to minutes.

Enable WAL archiving:

archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Verify archiving health:

SELECT last_archived_wal, last_archived_time, failed_count
FROM pg_stat_archiver;

For production, use pgBackRest, Barman, or WAL-G over raw pg_basebackup. Test recovery regularly -- backups are useless until you've successfully restored from one.

ia-postgresql Specification

Intent

ia-postgresql is a language-class skill (stack-specific patterns and idioms). PostgreSQL schema design, query optimization, indexing, and administration. Use when working with PostgreSQL, JSONB, partitioning, RLS, CTEs, window functions, or EXPLAIN ANALYZE.

Scope

In scope:

• Behaviors described in SKILL.md and routed via the should_trigger phrasings in distillery/tests/fixtures/triggers/ia-postgresql.jsonl.
• Updates to runtime behavior, structure, trigger precision, references, and validation.

Out of scope:

• Acting as the runtime instructions themselves (those live in SKILL.md).
• Trigger phrasings already covered by adjacent ia-* skills (validate-plugin flags >70% description overlap as DUPLICATE_TRIGGER).
• <!-- to fill in: domain-specific exclusions when the skill drifts -->

Trigger Context

• Class: language
• Hook regex: plugins/whetstone/hooks/skill-patterns.sh -> SKILL_PATTERNS[ia-postgresql]
• Common requests (from fixture should_trigger):
• "optimize the PostgreSQL query for the users table"
• "add a JSONB column to the events table"
• "set up row level security for the tenant table"
• Should not trigger for (from fixture should_not_trigger):
• "write a React component for the form"
• "add a Laravel queue job"
• "write a bash script for backups"

Source And Evidence Model

Authoritative sources:

• SKILL.md -- runtime instructions and reference routing.
• references/*.md -- bundled supplementary content (3 file(s)).
• distillery/tests/fixtures/triggers/ia-postgresql.jsonl -- positive and negative trigger phrasings under regression test.
• plugins/whetstone/hooks/skill-patterns.sh -- regex pattern that fires this skill.
• distillery/.eval-data/ia-postgresql/ -- harvested session examples (when present).

Data that must not be stored in this skill or its references:

• Secrets, credentials, tokens.
• Machine-specific filesystem paths (/home/..., /Users/..., ~/ai/...). The validator (MACHINE_PATH_LEAK) flags these as HIGH.
• Private URLs, customer data, or unredacted personal information.

Coverage matrix

Evaluation

Lightweight (run on every change):

python3 distillery/scripts/distiller.py validate-plugin --component ia-postgresql
python3 distillery/scripts/distiller.py test-triggers --skill ia-postgresql

Deeper (when behavior risk warrants):

python3 distillery/scripts/distiller.py dspy-eval ia-postgresql
python3 distillery/scripts/distiller.py diagnose-negatives ia-postgresql

Acceptance gates:

• validate-plugin --component ia-postgresql returns 0 HIGH findings.
• test-triggers --skill ia-postgresql returns F1 = 1.0 with floors of 5 should_trigger and 5 should_not_trigger.
• For dspy-eval, the composite score does not regress against the most recent saved baseline (see distillery/.eval-data/ia-postgresql/history.json).

Known Limitations

<!-- to fill in over time as drift surfaces. Default rule: any time diagnose-negatives surfaces a recurring failure pattern, document it here so future maintainers understand the trade-off the current implementation accepts. -->

Maintenance Notes

• Update SKILL.md when the runtime workflow, branch conditions, or output contract changes.
• Update this SPEC.md when intent, scope, evidence model, evaluation gates, or maintenance expectations change.
• Update the trigger fixture when adding new positive phrasings, removing stale ones, or expanding scope (the 5/5 floor is a hard validator gate).
• Update the hook regex in skill-patterns.sh whenever fixture positives expose a missed phrasing; verify F1 = 1.0 with eval-triggers before committing.
• Run the full release pipeline via /release -- never bump versions or update CHANGELOG.md from a per-skill edit.