Postgres Health Check

PostgreSQL Instance Health Check

Target version: postgres:16-alpine — this repository's gc-platform-postgres container (db gcplatform, owner role gcplatform, runtime role gc_kourou_app_login).

Contract: every check below is a read-only diagnostic (SELECT against pg_stat_*/pg_catalog, pg_isready, docker read commands). Each check gives: query → interpretation → severity → remediation. Remediations that mutate state are emitted ONLY as ⚠️ HUMAN CONFIRMATION REQUIRED blocks — the postgres-dba agent never executes them.

Connection:

docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"

Progressive disclosure: for the OPS side of applying EF Core migrations (lock windows at apply time, CREATE INDEX CONCURRENTLY vs the migration transaction, post-migration bloat/vacuum watch, live model-drift check), load reference-migration-ops.md in this skill's directory; code-side migration/schema correctness stays with database-reviewer.

1. Connectivity & uptime

SELECT version();
SELECT now() - pg_postmaster_start_time() AS uptime;
SELECT pg_is_in_recovery();

docker exec gc-platform-postgres pg_isready -U gcplatform -d gcplatform

Interpretation: pg_isready non-zero exit = server not accepting connections (CRITICAL). Very short uptime + no deploy = crash-loop (check docker logs --tail 200 gc-platform-postgres). pg_is_in_recovery() = true on the primary = unexpected (this stack has no replicas).

Severity: CRITICAL if unreachable; LOW note if uptime < 1h (statistics views were reset — treat other checks' counters cautiously).

2. Cache hit ratio

SELECT
  datname,
  blks_hit,
  blks_read,
  ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

Interpretation: ratio = shared-buffer hits / (hits + disk reads). Healthy OLTP ≥ 99%. < 95% on a warm instance means working set exceeds shared_buffers (default 128MB here — no command: override in compose) or queries do large sequential scans. Stats accumulate since last reset — judge the trend, not a snapshot.

Severity: MEDIUM (< 95%), HIGH (< 90% with user-visible latency).

Remediation: size shared_buffers per the postgres-performance-tuning skill; first confirm with EXPLAIN that low ratio is not just batch analytics scans.

3. Dead tuples & autovacuum hotspots

SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Interpretation: n_dead_tup grows with UPDATE/DELETE; autovacuum reclaims them. Hotspots: high dead_pct (> 20%) AND (last_autovacuum NULL or old) = autovacuum not keeping up (long transactions hold back the xmin horizon; check check 5). n_dead_tup is an estimate from the stats collector, not an exact count. Bloat also lives in indexes — compare pg_total_relation_size vs pg_relation_size.

Severity: HIGH (dead_pct > 50% on a hot table, or never autovacuumed); MEDIUM (20–50%).

Remediation:

⚠️ HUMAN CONFIRMATION REQUIRED

-- Reclaim space without the exclusive lock of VACUUM FULL (safe online):
VACUUM (ANALYZE, VERBOSE) <schema>.<table>;

-- For tables that bloat repeatedly, tighten per-table autovacuum:
ALTER TABLE <schema>.<table> SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

VACUUM FULL rewrites the whole table under an ACCESS EXCLUSIVE lock — only as last resort, off-hours, with a fresh backup. Source: https://www.postgresql.org/docs/16/routine-vacuuming.html

4. XID wraparound distance

-- Per-database age:
SELECT datname, age(datfrozenxid) AS xid_age,
       ROUND(age(datfrozenxid)::numeric / 2147483647 * 100, 2) AS pct_to_wraparound
FROM pg_database WHERE datname = current_database();

-- Oldest per-table age (finds the table holding the horizon back):
SELECT schemaname, relname, age(relfrozenxid) AS rel_xid_age
FROM pg_stat_user_tables
ORDER BY age(relfrozenxid) DESC LIMIT 10;

Interpretation: transaction IDs are 32-bit; at age ≈ 2^31 (≈ 2.147 billion) the database stops accepting writes to prevent wraparound data loss. PostgreSQL warns when a database comes within 40 million XIDs of wraparound and refuses new transactions when only 3 million remain. Autovacuum normally prevents this (autovacuum_freeze_max_age, default 200M, triggers aggressive freeze vacuums) — except when autovacuum is blocked (long-lived transactions, stuck replication slots, idle in transaction sessions). Source: https://www.postgresql.org/docs/16/routine-vacuuming.html

Severity: CRITICAL (age > 2.1B, i.e. within 40M of wraparound); HIGH (> 1B); MEDIUM (> 500M — investigate why freeze vacuums lag).

Remediation:

⚠️ HUMAN CONFIRMATION REQUIRED

-- First remove whatever holds the xmin horizon back (check 5): kill the stuck session/slot.
-- Then database-wide freeze vacuum (I/O-heavy; run off-hours):
VACUUM (FREEZE, VERBOSE) <schema>.<table>;   -- per worst table
-- or, full database (long):
-- VACUUMFREEZE via: VACUUM (FREEZE); on each database

5. Long-running & idle-in-transaction sessions

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - xact_start AS tx_duration,
       now() - query_start AS query_duration,
       LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND (state = 'idle in transaction'
       OR now() - xact_start > interval '5 minutes')
ORDER BY xact_start NULLS LAST;

Interpretation: idle in transaction holds locks AND holds back the xmin horizon → blocks autovacuum → feeds checks 3 and 4. A 10-minute idle in transaction from the backend (gc_kourou_app_login) usually means an undisposed EF Core transaction or a debugger paused mid-transaction. wait_event explains blocked sessions (e.g. relation, transactionid = lock waits).

Severity: HIGH (> 30 min, or blocking autovacuum on hot tables); MEDIUM (5–30 min).

Remediation:

⚠️ HUMAN CONFIRMATION REQUIRED

-- Prefer cancel (graceful) before terminate (force):
SELECT pg_cancel_backend(<pid>);
SELECT pg_terminate_backend(<pid>);

App-side fix (uncommitted transactions, missing await using on transactions) → dispatch coder via conductor.

6. Unused indexes

SELECT s.schemaname, s.relname, s.indexrelname,
       s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique      -- unique indexes enforce constraints: never drop on scan count alone
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Interpretation: idx_scan = 0 since the last statistics reset (check uptime / pg_stat_reset history) — a rarely-used but load-bearing index (month-end job) can look unused. Write-heavy tables pay INSERT/UPDATE cost for every unused index. Statistics are per-index-scan, not per-query.

Severity: MEDIUM (large unused indexes on write-heavy tables); LOW otherwise.

Remediation: dropping an index defined in an EF Core migration is a code change → recommend database-reviewer (review) + coder (migration). Ad-hoc drop, if ever:

⚠️ HUMAN CONFIRMATION REQUIRED

DROP INDEX CONCURRENTLY <schema>.<index_name>;