PostgreSQL Health Check
Last reviewed by Tyler Eastridge
A PostgreSQL health check is an engineer-led review of a running production database: autovacuum and transaction ID age, bloat, index use, the slowest statements, replication and slots, connection handling, and whether a backup actually restores. You get a written report, a ranked fix list and the evidence behind each finding. It runs from read-only statistics and configuration first, so nothing changes until you decide.
Is This You?
Queries that were fine last quarter are slow and nobody can say whether it is data growth, bloat or a plan change
Autovacuum warnings, a table that keeps growing or a transaction ID age that someone has started watching
A replica lags at peak, or a replication slot is holding WAL and the disk is filling
The last backup restore test was never done, or was done once, years ago
The people who tuned the database have left, and its settings are a mix of defaults and folklore
An upgrade, a cloud move or a growth step is coming and you want the current state on paper first
You are choosing between support, managed PostgreSQL and tuning work and want facts before the contract
Outcomes
Concrete state changes, not deliverable counts. This is what actually differs about your PostgreSQL estate when the engagement closes.
Before
Slowness is blamed on hardware, the application or the cloud, depending on who is in the meeting.
After
The slowest statements ranked by total time from pg_stat_statements, each tied to a cause: a missing index, a bad plan, bloat, locking or a client pattern.
Before
Autovacuum is left on defaults that were set for a database one tenth the size.
After
Per-table settings and a monitored transaction ID age, with the tables that are furthest from a freeze listed by name.
Before
Backups run every night and nobody knows how long a restore takes or whether it works.
After
A restore performed on a separate machine and timed, with the recovery point and recovery time compared with what the business expects.
Before
The team knows the database is fragile but cannot rank the risks or size the work.
After
Findings in three buckets: fix now, fix before the next growth step or upgrade, and fine as it is.
Scope
The statements that cost the most total time, called most often, or move the most buffers, plus plan changes, missing and unused indexes, blocked and blocking queries, and ORM patterns such as N+1 queries and unbounded result sets.
Dead tuples and last autovacuum per table, per-table thresholds on the large ones, long transactions that pin the xmin horizon, and the age of the oldest unfrozen transaction ID in every database.
Table and index size against growth, bloat estimates, duplicate and unused indexes, partitioning where tables are large, and TOAST-heavy columns.
Streaming and logical replication lag, standbys that hold back cleanup, inactive replication slots retaining WAL, and what failover tooling such as Patroni or repmgr will do when the primary goes.
Base backup and WAL archiving health, the archiver's failure count, retention, and a restore to a separate machine with the time recorded. A backup that has not been restored is a hope.
Connection counts against max_connections, idle-in-transaction sessions, pgBouncer or application pool settings, and the timeouts that stop one client holding the database hostage.
shared_buffers and the buffer cache hit ratio from pg_stat_database, work_mem, maintenance_work_mem, WAL and checkpoint settings, and the parameters that differ from the version's defaults, each explained or flagged.
Roles and their privileges, pg_hba.conf rules, TLS, password hashing method, superuser use and extensions installed, read against the access the application needs.
Version and minor release against the community and cloud support calendar, extension compatibility and the pg_upgrade --check result when a major version move is on the horizon.
The Engagement
Every phase has a defined duration and a concrete artifact handed over at the end of it. You always know what stage you're in and what you've received.
We agree the databases in scope and the access model: a role holding only the predefined pg_monitor privileges, or a screen-share where your engineers run the queries. The first reading captures the statistics views, settings and logs, and the time each counter was last reset.
You receive
Cumulative counters show a rate only when they are read twice. A second snapshot after a period you pick, such as a month-end batch, turns totals into rates and separates a problem that is happening now from one that happened once.
You receive
The latest backup and archived WAL are restored somewhere that is not production, and the time is recorded. Findings are checked against the workload, ranked by risk and effort, and each fix is written so that it can be run and rolled back.
You receive
A walkthrough of the report and the order of fixes, with your DBAs in the room. If you want the fixes made, the same engineers can do them under a separate scope, or the database can move onto proactive monitoring so the checks keep running.
You receive
A health check is only as good as its evidence. Every finding in the report names the view, log or test it came from, so your DBAs can reproduce it. This is the map from a question to the source.
| Question | Read from | What it flags |
|---|---|---|
| Which statements cost the most? | pg_stat_statements (extension), ordered by total execution time, calls and shared buffers read | Missing indexes, regressed plans, chatty clients, queries that need rewriting rather than tuning. |
| Is autovacuum keeping up? | pg_stat_user_tables: n_dead_tup, last_autovacuum, autovacuum_count; pg_stat_activity for long transactions | Tables with default thresholds that are too high, blockers of cleanup, a stalled worker. |
| How close is wraparound? | age(datfrozenxid) in pg_database and age(relfrozenxid) per table | Tables approaching a forced freeze, and the sessions or slots holding the horizon back. |
| Is the replica current? | pg_stat_replication: sent, write, flush and replay lag; pg_stat_wal_receiver on the standby | Lag at peak, a standby that cannot keep up, or hot standby feedback holding vacuum back on the primary. |
| What is retaining WAL? | pg_replication_slots: active, restart_lsn, retained bytes; pg_stat_archiver failed_count | Abandoned logical or physical slots, a failing archive command, a disk that will fill. |
| Are connections used well? | pg_stat_activity: state, wait events, idle in transaction age; pg_settings for max_connections | Connection storms, missing pooling, sessions that hold locks while idle. |
| Is the configuration sound? | pg_settings joined to defaults (source, boot_val), plus the host's memory and storage | Memory and WAL settings that no longer match the data, parameters that were copied between environments. |
| Do backups restore? | Backup catalog and WAL archive, then a timed restore on a separate machine | Gaps in WAL, missing configuration, and a restore time the business would not accept. |
The monitoring views are readable by the predefined pg_monitor role, so the review does not need a superuser. Where an extension such as pg_stat_statements is not installed, we say so in the report and agree whether to enable it, since it needs a restart on most setups.
The first pass is read-only. We ask for a role with monitoring privileges, or we join a screen-share and read the output of queries you run. Configuration files and logs are read, not edited. Production services keep running and the application does not notice.
What a health check is not
It is a point-in-time review with evidence, not continuous monitoring and not a guarantee. Findings describe the database on the days the evidence was read. Continuous checks are what Tier 2 proactive monitoring adds.
Autovacuum defaults are set for a general case, and the largest tables in a production database are rarely the general case. The table lists the settings the review reads, with their defaults from the PostgreSQL 18 documentation, and what each one tells us about your database.
| Setting | Default (PostgreSQL 18) | What the health check looks at |
|---|---|---|
| autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor | 50 rows plus 20% of the table | On a large table, 20% is a lot of dead rows before cleanup starts. We list the tables near that line and whether they have their own settings. |
| autovacuum_vacuum_max_threshold | 100,000,000 rows, new in 18 | Caps the trigger on very large tables. Versions before 18 do not have it, so per-table settings do that job there. |
| autovacuum_vacuum_insert_threshold and its scale factor | 1,000 rows plus 20% of the table's unfrozen pages | Insert-mostly tables, such as events and logs, and whether they are vacuumed before a freeze is forced. |
| autovacuum_max_workers | 3 | Workers against the number of large tables, and whether one long vacuum holds up the rest. |
| autovacuum_freeze_max_age | 200 million transactions | A table older than this gets an anti-wraparound vacuum even when autovacuum is off. We rank tables by age(relfrozenxid). |
| vacuum_failsafe_age | 1.6 billion transactions | The age at which VACUUM drops cost delays and non-essential work such as index vacuuming to avoid wraparound. A table near it is a fix-now finding. |
| Wraparound warnings and refusal | Warnings from 40 million transactions before wraparound; new transaction IDs refused below 3 million | Below 3 million, only read-only transactions can start. A production database should never get close, and the report says how far each one is. |
None of these defaults is wrong. The finding is the gap between a default and the table it is applied to, and the fix is usually a per-table setting rather than a global change. After the report, the work goes one of three ways: your team works through the list, we make the changes under a separate scope, or the database moves onto 24/7 support with Tier 2 proactive monitoring, or to managed operation (Tier 3 or 4).
A major version upgrade carries the state of the old cluster with it: bloat, long-lived replication slots, extensions that pin the server version, and indexes built under a collation you may be about to change. A health check before the move turns those into a short list instead of surprises in the maintenance window.
Typical engagements
Typical engagement scenarios: what the work covers and what it delivers. They describe the service, not past results.
Telecommunications Operator
Remediation of a Grafana deployment that became unusable during incidents, with dashboards timing out exactly when engineers needed them most.
Grafana
Prometheus
Kubernetes
PostgreSQL
Digital Media Platform
Ongoing support for a Memcached tier prone to thundering-herd database load after node changes and cache expiry cliffs.
Memcached
LinuxPostgreSQLKubernetesInsurance Services Provider
Emergency intervention on a database approaching transaction ID wraparound because autovacuum could not keep pace with the largest tables.
PostgreSQLLinuxPrometheuspgBackRestDigital Banking Platform
Ongoing support for a cluster where an abandoned logical replication slot repeatedly filled the WAL volume and threatened to halt the primary.
PostgreSQL
Debezium
KafkaPrometheusTelecommunications Operator
Designing an automated failover architecture with quorum-based leader election, synchronous replication policy, and tested recovery procedures.
PostgreSQLPatroniKubernetesetcdHAProxyRetail Technology Provider
Independent assessment of query performance, index health, table and index bloat, and connection management for a cluster with degrading response times.
PostgreSQLPgBouncerPrometheusLinuxRegional Insurer
A typical engagement for a team weighing an exit from Oracle: classify every object by conversion effort, then convert and test one representative database before committing to the rest.
PostgreSQLOracle DatabaseOra2PgAWS DMSSaaS Platform Team
A typical engagement for a team on SQL Server 2016 whose applications are written in T-SQL: assess Babelfish compatibility, choose between a bridge and full conversion, and plan the cutover.
PostgreSQLSQL ServerBabelfishAWS DMSFAQ
Nine areas: statements and plans, autovacuum and transaction ID age, storage and indexes, replication and slots, backups and a timed restore, connections and pooling, configuration and memory, the security baseline, and upgrade readiness. Each finding names the view, log or test it came from so your team can reproduce it.
Yes. The first pass is read-only: a role with the predefined pg_monitor privileges, or a screen-share where your engineers run the queries. The restore test uses a separate machine. Fixes are listed with their risk and rollback, and made only if you agree to a separate scope.
No. Monitoring watches continuously, and a health check is a review at a point in time with evidence. Tuning answers why a given query or workload is slow and changes things to fix it, while a health check reviews the whole database, including vacuum, replication, backups and security, and returns a ranked list of what to do. Teams usually run one before a major upgrade or cloud move and after a large growth step, then on a cycle that suits how fast their data changes.
Yes. The database-level checks are the same. On managed cloud services we also read the parameter groups, backup settings and maintenance windows through the provider's console or API. On Kubernetes we read the operator's configuration and the storage class, and run the restore test from the operator's own backup mechanism.
You choose. Your team can work through the fix list alone, we can make the fixes under a separate scope, or the database can move to 24/7 support with proactive monitoring, or to managed operation on your infrastructure or ours. A quote reply comes within 1 hour of getting your versions, platform and database sizes.
Let's talk
Tell us the PostgreSQL version, the platform (self-managed, RDS, Aurora, Kubernetes) and what hurts today. We will reply within 1 hour with a scope and a written quote.
Send us a message and we'll follow up within 1 hour — or book a free 30-min consultation directly.
305-204-2607info@acemq.comPrefer to talk now? Call us directly or use the consultation tab to find a time that works.