Health Score for PostgreSQL: health monitoring

Health Score in PostgreSQL: A Composite Health Indicator for Your Database

Traditional monitoring with dozens of Grafana dashboards often leads to dashboard fatigue—operators ignore charts, alerts either spam with false positives or trigger too late. The Health Score solves this by aggregating 150+ metrics from pg_stat_* into a single score from 0 to 100. The formula accounts for category weights and dangerous combinations, enabling quick assessment: 95+ is normal, below 70 requires action.

The base formula: Health Score = 100 - Σ(penalty_i × weight_i). Penalties are calculated non-linearly, reacting to trends and anomalies. This isn’t a threshold alert—it’s a composite metric, similar to Apdex in APM or EWS in medicine.

Category Structure and Calculations

The Health Score is built on five categories with fixed weights. Each evaluates key metrics via SQL queries against PostgreSQL’s system views.

Connections (weight 0.20)

Measures connection pool saturation and idle states:

SELECT 
    count(*) FILTER (WHERE state = 'active') AS active_connections,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    now() - min(xact_start) AS longest_transaction_age
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

Penalty grows exponentially after 80% utilization. Idle-in-transaction sessions older than 30 seconds incur an additional penalty, as they block autovacuum and contribute to bloat.

Performance (weight 0.25)

Focuses on user experience: cache hit ratio and slow queries.

-- Cache hit ratio
SELECT 
    round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Slow queries
SELECT count(*), round(avg(mean_exec_time)::numeric, 2)
FROM pg_stat_statements
WHERE mean_exec_time > 1000 AND calls > 10;

Below 95% hit ratio triggers a penalty; below 90% is critical. Execution time is compared to historical baselines to detect anomalies.

Storage (weight 0.20)

Monitors dead tuples and bloat:

SELECT 
    schemaname, relname, n_dead_tup, n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_ratio DESC LIMIT 20;

Bloat is assessed using pgstattuple or heuristics based on relpages. Disk usage is compared against filesystem limits.

Replication (weight 0.15)

Checks replica lag:

SELECT application_name, state, (sent_lsn - replay_lsn) AS lag_bytes,
    write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Lag exceeding 1MB or 5 seconds incurs a penalty. Absence of replicas doesn’t penalize this category.

Maintenance (weight 0.20)

Tracks vacuum progress and XID age:

-- XID age
SELECT datname, age(datfrozenxid), 2147483648 - age(datfrozenxid)
FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- Vacuum age
SELECT schemaname, relname, now() - greatest(last_vacuum, last_autovacuum) AS vacuum_age
FROM pg_stat_user_tables WHERE n_live_tup > 10000
ORDER BY vacuum_age DESC NULLS FIRST LIMIT 20;

XID age above 1.5 billion triggers a high penalty due to wraparound risk.

Weight Logic and Interpretation

Weights were chosen empirically: Performance leads at 0.25 because it directly impacts UX. Connections and Maintenance (0.20) are fast-acting failure points. Replication (0.15) is optional.

• 95+: Stable condition.
• 70–94: Monitor, plan fixes.
• 40–69: Immediate action required.
• <40: Incident status.

Automated Diagnostics with Recommendations

The Health Score includes a prioritized list of issues:

• HIGH: Table with >40% dead tuples and vacuum older than 4 days — run VACUUM ANALYZE, adjust autovacuum_vacuum_scale_factor.
• MEDIUM: Replication lag >500ms — check replica load, wal_receiver_timeout.
• LOW: Cache hit <99% — increase shared_buffers to 25% of RAM.

Recommendations are generated via rules, including checks against autovacuum thresholds.

SELECT relname, n_dead_tup > (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float + current_setting('autovacuum_vacuum_threshold')::float) AS vacuum_due
FROM pg_stat_user_tables;

Key Takeaways

• Health Score aggregates weighted metrics, uncovering hidden combinations like idle-in-transaction + bloat that threshold alerts miss.
• Non-linear formula provides early warnings before critical thresholds.
• Integrates with Prometheus/Grafana via postgres_exporter.
• Automated diagnostics deliver actionable recommendations with real SQL examples.
• Scalable: compares trends over history to detect anomalies.

— Editorial Team