Operational Health Auditing

Monitoring Query

SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    pg_size_pretty(pg_database_size(oid)) AS db_size,
    CASE
        WHEN age(datfrozenxid) > 1200000000 THEN 'CRITICAL'
        WHEN age(datfrozenxid) > 800000000 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM pg_database
WHERE datallowconn
ORDER BY age(datfrozenxid) DESC;

Per-Table XID Age

SELECT
    schemaname,
    relname,
    age(relfrozenxid) AS xid_age,
    last_autovacuum,
    last_vacuum,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Thresholds

Common Causes of High XID Age

• Long-running transactions preventing vacuum from freezing tuples
• Autovacuum not keeping up with write volume (needs tuning)
• Prepared transactions left open (pg_prepared_xacts)
• Replication slots preventing vacuum from advancing

Table and Index Bloat

Bloat occurs when dead tuples (from UPDATEs and DELETEs) accumulate faster than vacuum can reclaim them. The table and its indexes grow larger than the live data warrants.

Table Bloat Detection

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
         THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 1)
         ELSE 0 END AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Index Bloat Detection

-- Estimate index bloat by comparing actual size to estimated size
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Bloat Thresholds

Remediation

Autovacuum Tuning

Autovacuum is PostgreSQL's background process for reclaiming dead tuples and preventing XID wraparound. Default settings are conservative and often insufficient for high-write workloads.

Key Parameters

Per-Table Overrides

High-write tables often need more aggressive vacuum settings:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- Trigger at 1% dead tuples instead of 20%
    autovacuum_vacuum_threshold = 1000,       -- Minimum 1000 dead tuples
    autovacuum_analyze_scale_factor = 0.005   -- Analyze at 0.5% changes
);

Monitoring Autovacuum Activity

-- Currently running vacuum operations
SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    CASE WHEN heap_blks_total > 0
         THEN round(100.0 * heap_blks_scanned / heap_blks_total, 1)
         ELSE 0 END AS progress_pct
FROM pg_stat_progress_vacuum;

-- Last vacuum times per table
SELECT
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST
LIMIT 20;

Dead Tuple Tracking

Dead tuples are row versions left behind by UPDATEs and DELETEs. They consume space and I/O until vacuum reclaims them.

Monitoring Query

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_autovacuum,
    CASE WHEN n_live_tup > 0
         THEN round(100.0 * n_dead_tup / n_live_tup, 1)
         ELSE 0 END AS dead_to_live_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

What Blocks Vacuum From Reclaiming Dead Tuples

Cache Hit Ratios

PostgreSQL caches frequently accessed data in shared buffers. A low cache hit ratio means the database is reading from disk instead of memory.

Database-Level Cache Hit Ratio

SELECT
    datname,
    blks_hit,
    blks_read,
    CASE WHEN (blks_hit + blks_read) > 0
         THEN round(100.0 * blks_hit / (blks_hit + blks_read), 2)
         ELSE 0 END AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

Table-Level Cache Hit Ratio

SELECT
    schemaname,
    relname,
    heap_blks_hit,
    heap_blks_read,
    CASE WHEN (heap_blks_hit + heap_blks_read) > 0
         THEN round(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 2)
         ELSE 0 END AS cache_hit_pct
FROM pg_statio_user_tables
WHERE (heap_blks_hit + heap_blks_read) > 100
ORDER BY cache_hit_pct ASC
LIMIT 20;

Thresholds

Sequential Scan Ratios

High sequential scan ratios on large tables indicate missing or unused indexes.

Monitoring Query

SELECT
    schemaname,
    relname,
    seq_scan,
    idx_scan,
    CASE WHEN (seq_scan + idx_scan) > 0
         THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 1)
         ELSE 0 END AS seq_scan_pct,
    seq_tup_read,
    n_live_tup,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE (seq_scan + idx_scan) > 100
ORDER BY seq_scan_pct DESC;

Interpretation

Note: seq_tup_read / seq_scan gives the average rows read per sequential scan. If this is close to the total row count, the scan is reading the entire table each time.

Anti-Patterns

1. Ignoring Autovacuum Warnings

Treating autovacuum as "it just works" without monitoring. Default settings are insufficient for tables with more than a few thousand writes per hour.

2. VACUUM FULL as Routine Maintenance

Using VACUUM FULL regularly instead of tuning autovacuum. VACUUM FULL takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration of the rewrite.

3. Disabling Autovacuum

Setting autovacuum = off to "reduce overhead." This guarantees XID wraparound and eventual database shutdown.

Examples

Example 1: Investigating Slow Query Performance

Symptoms: Application queries on the events table have degraded from 5ms to 200ms over the past month.

Audit findings:

• n_dead_tup: 2.4M (45% of live tuples)
• last_autovacuum: 3 days ago
• cache_hit_pct: 87%
• seq_scan_pct: 62% on a 4GB table

Diagnosis: Dead tuple accumulation is causing bloat, reducing cache efficiency, and forcing sequential scans. Autovacuum is not keeping up with write volume.

Remediation:

1. Run VACUUM ANALYZE events immediately
2. Tune per-table autovacuum: ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02)
3. Add missing indexes based on query patterns
4. Monitor dead tuple ratio weekly

Example 2: XID Wraparound Alert

Monitoring alert: Database XID age is 900M.

Audit findings:

• Oldest table audit_logs: age(relfrozenxid) = 890M
• pg_prepared_xacts: one abandoned prepared transaction from 5 days ago
• Autovacuum running but blocked by the prepared transaction

Remediation:

1. ROLLBACK PREPARED 'txn_abc123'
2. Run VACUUM FREEZE audit_logs manually
3. Add monitoring for abandoned prepared transactions
4. Verify XID age drops below 500M within 24 hours

Example 3: Healthy Operational State

Audit findings:

• XID age: 120M (OK)
• Dead tuple ratio: < 2% on all tables
• Cache hit ratio: 99.7%
• Sequential scan ratio: < 5% on tables > 1MB
• Autovacuum: running regularly, no tables overdue

Assessment: Operational health is excellent. No action needed. Next audit in 30 days.