PostgreSQL Health Score, PostgreSQL

​

At a glance

A single 0 to 100 composite that answers “is my PostgreSQL instance healthy right now?” without making a platform lead read five dashboards. It folds the five things that actually take a production database down: query latency, query errors, replication health, autovacuum currency, and connection-pool headroom. Each factor scores 0 to 1; the score is their product, scaled to 100. Because it is multiplicative, any single factor collapsing drags the whole score down hard, which is the point: one broken pillar (a stalled standby, a wraparound-risk autovacuum) is an emergency even if everything else is green. Read it as a traffic light: green is steady-state, amber means one pillar is wobbling, red means act now.

​

Calculation

health = p95_latency_factor
       × error_free_factor
       × replication_factor
       × autovacuum_factor
       × pool_headroom_factor

score  = round(health × 100)

• p95 latency factor reads p95 query execution time from pg_stat_statements. At or below the healthy threshold it scores 1.0; it decays towards 0 as latency climbs past the alert level (200ms by default).
• error-free factor reads the rolling query error rate from pg_stat_database (rollbacks and failed statements over total). Zero errors scores 1.0; it decays as the rate rises past the 1% alert level.
• replication factor reads pg_stat_replication on the primary. A reachable standby with sub-second lag scores 1.0; lag growth decays it, and an unreachable / BROKEN standby collapses it towards 0.
• autovacuum factor reads the maximum time since last (auto)vacuum across user tables. Recently vacuumed scores 1.0; it decays as the oldest table ages past 24 hours, reflecting accumulating bloat and transaction-ID wraparound risk.
• pool-headroom factor reads active plus idle-in-transaction backends against max_connections. Comfortable headroom scores 1.0; it decays towards 0 as saturation approaches 100%.

​

Worked example

1. Replication is the dominant drag (0.55). A 14-second standby lag means failover would lose up to 14 seconds of writes and that read replicas are serving stale data. Open Replication Lag (seconds) and confirm whether the standby is apply-bound (catching up slowly) or network-bound.
2. Autovacuum is falling behind (0.78). The batch created bloat faster than autovacuum reclaimed it. Check Top Tables by Dead Tuples and consider a manual VACUUM on the hot table or tuning autovacuum_vacuum_cost_limit.
3. Latency over threshold is a symptom, not a cause (0.82). It will self-correct once the batch finishes and the standby catches up. Do not chase it independently.

Why the score is 32 and not "averaged" to 85:
  Multiplicative:  0.82 × 0.99 × 0.55 × 0.78 × 0.92 = 0.32  → 32  (honest)
  If it were a mean: (0.82+0.99+0.55+0.78+0.92)/5    = 0.81  → 81  (misleading)
  The 0.55 replication factor would have been hidden by averaging.

1. A low score names the broken pillar; the drill-down tells you which one. Never act on the composite alone. Open the factor breakdown, find the factor nearest 0, and start there. The lowest factor is your highest-leverage fix.
2. The score is multiplicative on purpose. One collapsed pillar should cap the whole score, because in production one collapsed pillar (a dead standby, a wraparound-risk table) is an emergency regardless of how healthy everything else is.
3. Two cards live outside the composite. Disk usage and backup currency are deliberately excluded so they cannot be masked. A 99 health score with a disk at 94% full is still an imminent outage; always read this hero card next to Database Disk Usage % and the backup card.

​

Sibling cards to reference together

​

Reconciling against the source

There is no single native command that produces this score, it is a Vortex IQ composite. To reconcile it, verify each factor against its own source: p95 latency: SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC; (requires the pg_stat_statements extension). error rate: SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; for the rollback ratio. replication: SELECT client_addr, state, replay_lag FROM pg_stat_replication; on the primary. autovacuum age: SELECT relname, last_autovacuum, n_dead_tup FROM pg_stat_user_tables ORDER BY last_autovacuum NULLS FIRST; pool headroom: SELECT count(*) FROM pg_stat_activity; against SHOW max_connections; Managed-service console: on Amazon RDS / Aurora, Performance Insights plus the CloudWatch metrics for ReadLatency, ReplicaLag, DatabaseConnections and MaximumUsedTransactionIDs. On Cloud SQL, the system insights and the replication/replica_lag, database/postgresql/num_backends, and transaction-ID metrics. Each maps to one factor.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre