Postgres ops

postgres-ops

Production-grade PostgreSQL operations: diagnosis, performance, HA/DR, security, and observability. Assume the user is a senior engineer — skip introductory explanations of what Postgres is and go straight to evidence-driven SRE workflow.

When to use

Trigger on operational Postgres tasks:

• Incident diagnosis: slow queries, deadlocks, lock waits, runaway autovacuum, connection exhaustion, replication lag, disk pressure, OOMs.
• Performance review: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) interpretation, index strategy, partitioning, vacuum/autovacuum tuning, work_mem / shared_buffers sizing.
• HA/DR: streaming replication, logical replication, Patroni, pgBackRest, WAL-G, PITR planning, RTO/RPO target validation.
• Migrations & upgrades: minor and major version upgrades (pg_upgrade vs logical replication cutover), schema migration tooling (EF Core migrations, Alembic, Flyway, sqitch, raw SQL), zero-downtime patterns.
• Connection management: pgBouncer (transaction vs session pooling tradeoffs), RDS Proxy, PgCat, pool sizing math.
• Security & compliance: role design, RLS, pgaudit, TLS enforcement, secret rotation, STIG/SRG line items, CIS benchmark gaps.
• Observability: postgres_exporter, pg_stat_statements, auto_explain, slow query log shipping (Loki), Grafana dashboards, SLO definition.

Do NOT trigger for:

• New-feature CRUD scaffolding in Next.js (use nextjs-react-postgres-builder).
• Pure SQL-language questions ("what does LATERAL do") with no operational context.
• Other database engines (MySQL, SQL Server, Cosmos DB).

Instructions

1. Diagnose like an SRE

For any incident or performance complaint, follow hypothesis → evidence → fix → verification. Never propose a fix without naming the query you'd run to confirm the diagnosis first.

Default first-look queries:

-- Active sessions and what they're waiting on
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 200) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC NULLS LAST;

-- Blocking chains
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Top queries by total time (requires pg_stat_statements)
SELECT round(total_exec_time::numeric, 0) AS total_ms,
       calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 200) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- Bloat / vacuum status
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST;

-- Replication lag (on primary)
SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

2. Read query plans rigorously

When given an EXPLAIN ANALYZE output:

1. Identify row-estimate vs actual mismatch (>10x = stale stats or bad correlation).
2. Find the dominant cost node (Seq Scan on large table, nested loop with high outer rows, sort spilling to disk).
3. Check buffer numbers — heavy read= vs hit= indicates cold cache or undersized shared_buffers.
4. Recommend: index, query rewrite, statistics target bump, or partitioning — in that order of preference.
5. Always provide the verification command (re-run with same params and compare).

3. Migration & upgrade discipline

For schema migrations:

• Always show forward and rollback DDL.
• Flag any ALTER TABLE that rewrites the table (changing column type, adding NOT NULL without DEFAULT on PG <11, etc.).
• For zero-downtime: use the expand-contract pattern (add nullable → backfill → enforce → drop).
• For EF Core: use Add-Migration + Script-Migration to review SQL before apply; never Update-Database in prod.

For version upgrades:

• pg_upgrade with --link for in-place when downtime is acceptable.
• Logical replication cutover (pglogical or built-in) for near-zero downtime.
• Always confirm extension compatibility on the target version first.

4. Connection pooling math

For pgBouncer in transaction-pooling mode:

• default_pool_size per (user, database) ≈ max_connections / number_of_pools with headroom.
• App-side pool: keep small (5–20 per replica). The pooler is the real concurrency limit.
• Watch out for: prepared statements (need pgbouncer >= 1.21 with server_lifetime tuning), session-level features (SET LOCAL only, no LISTEN/NOTIFY, no temp tables across txns).

5. Federal/DoD posture (when relevant)

• Enforce TLS with ssl=on, ssl_min_protocol_version=TLSv1.2, restrict hostssl only in pg_hba.conf.
• pgaudit extension for STIG-required audit logging; ship logs to a tamper-resistant store.
• Separate roles: no shared accounts, no SUPERUSER for app roles, RLS for tenant isolation.
• FIPS-validated OpenSSL on the host; verify with SHOW ssl_library; and OS-level FIPS mode.
• Check the current PostgreSQL STIG (DISA) for line-item compliance — versions ship updates regularly.

6. Observability defaults

• Enable pg_stat_statements, auto_explain (with log_min_duration_statement reasonable for prod, e.g., 1000ms).
• Run postgres_exporter as a sidecar; scrape into Prometheus.
• Ship CSV logs to Loki via Promtail or Vector; Grafana dashboards keyed on pg_stat_statements and pg_stat_activity.
• SLO suggestion: P95 query latency for the top-N statements, plus replication lag <N seconds.

7. Output discipline

• Give complete, runnable SQL or shell — no placeholders.
• Call out destructive operations explicitly (DROP, TRUNCATE, pg_upgrade --link, vacuum full).
• For any tuning parameter recommendation, state the workload assumption (OLTP / analytics / mixed) and the math behind it.
• When uncertain about a version-specific behavior, say so and name the version where the behavior changed.

Anti-patterns

These look like reasonable Postgres moves but will either corrupt data, cause silent failures, or surprise you in production:

1. Running VACUUM FULL on a live high-traffic table — VACUUM FULL acquires an exclusive lock that blocks all reads and writes for the duration. On large tables this means minutes of downtime. Use regular VACUUM (autovacuum) for routine bloat; VACUUM FULL only on an offline table or during a maintenance window.
2. Using UPDATE-Database in EF Core directly against production — EF Core's migration runner will execute DDL without the ability to inspect SQL first, and there is no dry-run mode. Always generate a SQL script with Script-Migration, review it, then apply through a controlled change window.
3. Setting work_mem globally high — work_mem is per sort operation per query, and a single complex query can trigger many operations simultaneously. Setting work_mem = 1GB on a 64 GB server with 100 connections doing complex sorts will OOM the host. Set it low globally and override per session for known heavy queries.
4. Trusting pg_dump without a restore test — pg_dump completing successfully does not mean the backup is usable. Schema dumps with extension version mismatches or missing roles fail silently on restore. Test a full restore to a separate instance on a schedule — not right before you need it.
5. Using transaction pooling mode in pgBouncer with prepared statements — prepared statements are session-scoped; in transaction pooling the server-side connection changes between transactions, so PREPARE/EXECUTE will reference a statement that no longer exists. Either use session pooling or move to DEALLOCATE ALL patterns on every transaction.
6. Skipping the --link caveat with pg_upgrade — pg_upgrade --link creates hard links rather than copying data files, making the upgrade fast. But if the old cluster is accessed or the upgrade is rolled back after the new cluster has written to the linked files, data corruption results. Backup before --link and never start the old cluster again after the new one has written data.
7. Adding an index without CONCURRENTLY on a production table — standard CREATE INDEX holds a ShareLock that blocks writes for the index build duration. CREATE INDEX CONCURRENTLY avoids the lock but takes longer and cannot run inside a transaction block.

Error Handling

Domain-specific failure modes when running the diagnostic/fix workflows above:

1. Can't connect to diagnose max_connections exhaustion — the "check connections" query itself fails with FATAL: sorry, too many clients already. Detection: connection refused citing max_connections. Recovery: connect via the reserved superuser slot (superuser_reserved_connections, default 3) with a superuser role, or via a local Unix-socket psql on the host, which usually isn't gated the same way; from there, query pg_stat_activity and terminate the worst idle-in-transaction offenders with pg_terminate_backend(pid).
2. EXPLAIN ANALYZE on a write query actually executes it — running it against an UPDATE/DELETE/INSERT performs the write for real; there's no dry-run mode. Detection: none after the fact — the write is already committed if not caught first. Recovery: wrap it in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; so the plan runs but nothing commits, or run it against a replica/staging copy for anything destructive.
3. pg_stat_statements relation doesn't exist — the "top queries by total time" query fails with relation "pg_stat_statements" does not exist. Detection: that literal error. Recovery: it must be in shared_preload_libraries, which needs a full instance restart, not just CREATE EXTENSION — check SHOW shared_preload_libraries; first, and if it's missing, schedule a restart window before promising this data.
4. CREATE INDEX CONCURRENTLY fails partway through — leaves an INVALID index behind instead of rolling back cleanly (concurrent builds can't run in a transaction, so a failure or cancel doesn't undo it). Detection: pg_index.indisvalid = false for the new index, or \d <table> shows it present but marked invalid. Recovery: DROP INDEX CONCURRENTLY <name>; then retry — ideally after identifying what killed it (lock timeout, deadlock, disk full).
5. pg_upgrade --check fails on extension mismatch — reports incompatible extension versions or missing objects before anything is touched. Detection: check-mode output (always run --check first, never skip it). Recovery: update/reinstall the flagged extensions on the target version's cluster, re-run --check until clean, then perform the real upgrade.
6. pgBouncer transaction pooling clients see prepared statement "S_1" already exists / does not exist — surfaces once app code or an ORM uses server-side prepared statements. Detection: that literal error, only in transaction pooling mode. Recovery: switch the pool to session mode for that database, or disable server-side prepared statements at the driver (e.g., Npgsql Max Auto Prepare=0, psycopg prepare_threshold=None).
7. Replication lag query returns zero rows — pg_stat_replication is empty on what you thought was the primary. Detection: zero rows, not an error. Recovery: confirm you're actually on the primary (SELECT pg_is_in_recovery(); should return false); if it does and the view is still empty, no replicas are currently connected — check the replica's own logs for the connection failure (auth, pg_hba.conf, network reachability).

Example prompts

• "We have a query taking 30 seconds in prod. Here's the EXPLAIN ANALYZE — what's wrong?"
• "Our app hit max_connections. Walk me through diagnosing the cause and fixing it without downtime."
• "I need to add a NOT NULL column to a 200M-row table. What's the zero-downtime approach?"
• "We're upgrading from Postgres 14 to 16. What's the fastest path and what should I check first?"
• "Help me size pgBouncer pool for 50 app instances hitting a single primary."
• "Our DISA STIG audit is next week. What Postgres controls do I need in place?"
• "Autovacuum is running constantly on one table. How do I tune it?"

Related skills

• k8s-nextjs-deploy — Kubernetes deployment patterns if Postgres runs in-cluster
• ubuntu24-stig — OS-level STIG hardening for the host running Postgres