The Complete PostgreSQL Morning Health Check Guide — From Native Views to a Formatted Report

---

This guide builds a complete morning health check for PostgreSQL: the queries that gather the data, the categories a report needs to cover, the format that data should be presented in, and the delivery options once it is built. It is written for anyone coming from a SQL Server background who already runs a DMV-driven morning report and needs the PostgreSQL equivalent, and equally for anyone building a PostgreSQL health check from nothing.

Three things make this different from a query reference. First, PostgreSQL does not expose several things SQL Server does as a single number, and this guide says so plainly wherever that is true rather than inventing a substitute metric. Second, a health check that reports everything is not a health check, it is a data dump; this guide builds toward a report format that surfaces issues only, ranks them by priority, and states what changes if each one gets fixed. Third, every query and every claim of “no equivalent” in this guide was checked against official PostgreSQL documentation, official AWS migration documentation, or Ryan Booz and Grant Fritchey’s Introduction to PostgreSQL for the Data Professional (Redgate Books, 2024) rather than assumed from SQL Server habit.

1Why This Is Not Just Translated DMVs (SQL Server’s Diagnostic Views)

A natural first instinct, for anyone coming from SQL Server, is to treat this as a translation exercise: find the PostgreSQL view that corresponds to each SQL Server DMV (Dynamic Management View, SQL Server’s built-in set of diagnostic views) and swap the query. That works for a meaningful share of a typical health check and produces a wrong or overconfident report for the rest. Some concepts have a clean PostgreSQL equivalent. Some have a partial one that requires combining two different signals. A few have no equivalent at all, and the honest thing to do with those is say so, not approximate them into a false sense of parity.

The report format matters just as much as the queries. A report that lists every metric, healthy or not, trains readers to skim past it. Section 12 of this guide builds the format that avoids that: issues only, ranked by what needs attention first, with the consequence of fixing (or not fixing) each one stated plainly.

2Prerequisites: Settings, Permissions, and Version Compatibility

Three categories of prerequisite determine whether the queries in this guide return data at all, and a fourth determines whether the exact column names below will work on a given server. None of these are optional footnotes; a health check built without checking them first will either return empty results or fail outright on an older server.

Settings that must be on

Setting	Default	What breaks without it
track_counts	On by default	Confirmed directly in PostgreSQL’s own documentation: this parameter is on by default specifically because the autovacuum daemon depends on it. Every query in Sections 5–7 of this guide (activity/table stats, cache, vacuum, missing-index signals) requires it. Effectively always on in practice, since disabling it also disables autovacuum’s ability to function, but confirm with SHOW track_counts; rather than assuming.
track_activities	Needs verification per environment	Controls whether pg_stat_activity.query shows the actual command text. Without it, the activity and blocking queries in Section 4 return session metadata but no query text to diagnose. Check with SHOW track_activities; rather than assuming a default.
track_io_timing	Needs verification per environment	Controls whether I/O timing (not just counts) is available in EXPLAIN (ANALYZE, BUFFERS) output and certain pg_stat_statements timing columns. Some managed PostgreSQL platforms enable it by default, others do not, and it carries a measurable overhead on some platforms, which is why it is not universally on. Check with SHOW track_io_timing; before assuming timing data will be present.

-- Run this first, on every server, before anything else in this guide
SHOW track_counts;
SHOW track_activities;
SHOW track_io_timing;

Permissions

Running these queries as a superuser works everywhere but is not the recommended path for a scheduled health check job. The pg_monitor predefined role, confirmed as available starting in PostgreSQL 10, bundles read access to the pg_stat_* views and settings without requiring superuser privileges.

-- Create a dedicated, non-superuser monitoring role
CREATE ROLE health_check_reader WITH LOGIN PASSWORD '...';
GRANT pg_monitor TO health_check_reader;

Extension setup

Section 9 of this guide (Query Performance) depends entirely on the pg_stat_statements extension, which is not enabled by default and requires a server restart to add, since it needs additional shared memory reserved at startup. This is covered in full in Section 9; it is listed here because it is the one prerequisite in this guide that cannot be satisfied with a runtime setting alone.

Version compatibility at a glance

Guide section	Minimum version	Reason
Sections 4–5, 7–8 (activity, cache, replication, configuration)	PostgreSQL 10+	Core views used are stable well before PostgreSQL 12; the pg_monitor role itself requires 10+
Section 6 core query (dead tuples, statistics staleness)	PostgreSQL 10+	n_mod_since_analyze, n_dead_tup, n_live_tup are long-standing columns
Section 7 (missing-index signal), last_seq_scan / last_idx_scan columns specifically	PostgreSQL 16+	Confirmed added in PostgreSQL 16; querying these columns on an older server raises a column-does-not-exist error, not an empty result
Section 9 (query performance), total_exec_time / mean_exec_time / max_exec_time column names	PostgreSQL 13+	Confirmed renamed from total_time / mean_time / max_time starting in pg_stat_statements as shipped with PostgreSQL 13; the pre-13 names must be used on PostgreSQL 12
pg_amcheck (referenced in the gaps table)	PostgreSQL 14+	Confirmed added in PostgreSQL 14

3The View Mapping Reference

SQL Server Source	PostgreSQL Equivalent	Fidelity
sys.dm_os_sys_info	pg_postmaster_start_time(), pg_settings	Direct: uptime, configuration
sys.dm_exec_requests	pg_stat_activity	Direct: active queries, state, wait events
dm_exec_requests.blocking_session_id	pg_blocking_pids()	Direct equivalent
sys.dm_os_wait_stats	pg_stat_activity (current), pg_wait_sampling (extension, historical)	Partial: no cumulative wait totals in core PostgreSQL
sys.dm_io_virtual_file_stats	pg_statio_user_tables, pg_statio_user_indexes	Partial: cache hit/miss at table level, no per-file latency in ms
Page Life Expectancy (PLE)	pg_statio_user_tables cache hit ratio	Partial: no single equivalent number; hit ratio is the signal
sys.dm_db_missing_index_*	pg_stat_user_tables.seq_scan	Partial: no direct equivalent; sequential scan count on large tables is the proxy signal
Index fragmentation	pg_stat_user_tables.n_dead_tup	Conceptual: dead rows are the closer PostgreSQL equivalent; VACUUM is the corrective action, not REBUILD
sys.dm_db_stats_properties (modification_counter)	pg_stat_user_tables.n_mod_since_analyze	Direct equivalent
DBCC CHECKDB (last good time)	No built-in equivalent	Gap: pg_amcheck (PostgreSQL 14+) checks integrity but has no last-run catalog entry
sys.dm_hadr_database_replica_states	pg_stat_replication	Partial: primary-side only
AG Redo Queue / Redo Rate	pg_wal_lsn_diff(sent_lsn, replay_lsn)	Partial: bytes only; rate requires two snapshots
sys.dm_exec_query_stats	pg_stat_statements (extension)	Requires extension; not available if not loaded
MAXDOP / Cost Threshold / TempDB files	max_parallel_workers_per_gather / work_mem / temp_tablespaces	Conceptual equivalents only; tuning values do not translate directly
msdb.dbo.sysjobhistory	pg_cron.job_run_details (if pg_cron installed)	Gap: no native job history without pg_cron or pgAgent
msdb.dbo.backupset	No built-in equivalent	Gap: PostgreSQL has no internal backup catalog; depends entirely on the backup tool in use (pg_dump, pg_basebackup, pgBackRest, Barman)
xp_readerrorlog	PostgreSQL log files, or log_fdw extension	Partial: requires a shell step or extension to query logs as data

4Activity, Blocking, and Waits

pg_stat_activity is the direct equivalent of sys.dm_exec_requests, and pg_blocking_pids() is a direct equivalent for finding what is blocking what, no join gymnastics required.

-- Active sessions with blocking chains
SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    query_start,
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Idle-in-transaction sessions deserve a dedicated check. They hold open transactions without doing work, which blocks vacuum’s ability to clean up dead tuples behind them.

-- Idle-in-transaction sessions older than 5 minutes
SELECT pid, usename, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - query_start > interval '5 minutes';

5Cache Hit Ratio and I/O Health

PostgreSQL has no direct equivalent to Page Life Expectancy as a single number. The documented alternative, confirmed in the Redgate PostgreSQL book, is tracking the cache hit ratio from pg_statio_user_tables, where a sustained ratio below roughly 80–85% indicates the buffer pool is undersized for the workload.

-- Cache hit ratio (buffer pool health signal)
SELECT
    sum(heap_blks_hit) AS heap_hit,
    sum(heap_blks_read) AS heap_read,
    round(
        sum(heap_blks_hit)::numeric /
        NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2
    ) AS cache_hit_ratio_pct
FROM pg_statio_user_tables;

Per-table I/O breakdown identifies which specific tables are driving physical reads, closer to what sys.dm_io_virtual_file_stats shows per file, though PostgreSQL exposes this per table rather than per physical file, and does not report latency in milliseconds natively.

-- Per-table read activity, worst cache performers first
SELECT
    schemaname, relname,
    heap_blks_read, heap_blks_hit,
    round(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) AS table_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 15;

6Vacuum, Bloat, and Statistics Freshness

This is the category with the most direct SQL Server equivalent. n_mod_since_analyze maps cleanly to modification_counter, and the same tiered percentage-threshold thinking applies.

-- Statistics staleness by tiered threshold (matches the SQL Server pattern)
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup, n_mod_since_analyze,
    last_analyze, last_autoanalyze,
    round(n_mod_since_analyze::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS pct_modified
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY pct_modified DESC;

Dead tuple percentage is the direct PostgreSQL parallel to fragmentation: a table isn’t “fragmented” the way a SQL Server B-tree is, but a high dead-tuple percentage produces the same practical symptom (more pages read for the same amount of live data), and VACUUM is the corrective action, not REBUILD.

-- Tables with high dead tuple percentage, and whether autovacuum has run
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS pct_dead,
    last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 0
  AND n_dead_tup::numeric / NULLIF(n_live_tup, 0) > 0.10
ORDER BY pct_dead DESC;

7Missing Index and Bloat Signals

PostgreSQL has no equivalent to sys.dm_db_missing_index_details with its weighted impact score. The honest substitute is sequential scan frequency on tables large enough that a scan is expensive, combined with row count as a rough proxy for how costly each scan actually is.

-- Sequential scan candidates on tables large enough to matter
-- Works on PostgreSQL 10 and later
SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    idx_scan,
    n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
  AND seq_scan > 0
ORDER BY seq_scan * n_live_tup DESC
LIMIT 15;

8Replication Health

Streaming replication status comes from two different sources depending on what is being asked. pg_stat_replication exists as a view on every server, but it only returns rows on a server that currently has replicas streaming from it, normally the primary. pg_is_in_recovery() is universal: it works on every server regardless of whether replication is configured at all, and simply reports whether the server it is run on is currently a standby.

-- Run on the primary: replica lag and sync state
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_lag_bytes
FROM pg_stat_replication;

-- Run on each node: confirm recovery/replica status
SELECT pg_is_in_recovery();

The lag figures above are reported in bytes, not seconds. Converting to a rate requires two snapshots and the time elapsed between them; a single reading only shows the current gap, not whether it is growing or shrinking.

9Query Performance

Unlike sys.dm_exec_query_stats, query-level performance history in PostgreSQL is not available by default. It requires the pg_stat_statements extension, added to shared_preload_libraries, followed by a restart and CREATE EXTENSION.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- After restart:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total execution time
-- PostgreSQL 13 and later
SELECT
    calls, total_exec_time, mean_exec_time, max_exec_time,
    rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Same query, PostgreSQL 12 and earlier
SELECT
    calls, total_time, mean_time, max_time,
    rows, query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 15;

For plan-level detail on a specific slow query, PostgreSQL’s equivalent to capturing an actual execution plan is EXPLAIN ANALYZE, and for proactively logging plans for queries that exceed a duration threshold without manually running EXPLAIN each time, the auto_explain module (also loaded via shared_preload_libraries) serves the role SQL Server’s Query Store plays for automatic capture.

10Configuration Review

A configuration check should confirm the handful of settings most likely to be left at defaults that don’t fit production: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, and random_page_cost.

-- Current values for the settings worth checking against workload
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'work_mem', 'maintenance_work_mem',
    'effective_cache_size', 'random_page_cost', 'max_connections'
);

The source column is worth watching specifically: a value of default on a production instance for something like shared_buffers is itself a finding, since the PostgreSQL default is deliberately conservative and assumes nothing about available server memory.

11What PostgreSQL Cannot Natively Report

SQL Server Capability	Gap Status	Workaround
Page Life Expectancy (single value)	Partial	Cache hit ratio is the combined signal; no single equivalent number
Historical cumulative wait stats	Extension needed	pg_wait_sampling provides sampled historical waits; not in core PostgreSQL
Per-file I/O latency in milliseconds	Partial	Not exposed natively; cache hit ratio is the proxy, OS-level tools (iostat) needed for actual ms-level data
DBCC CHECKDB last run time	No equivalent	pg_amcheck (PostgreSQL 14+) checks integrity but keeps no last-run catalog entry; a custom tracking table is required to log runs
Backup history (msdb.backupset)	No equivalent	PostgreSQL has no internal backup catalog; depends entirely on whichever backup tool is in use
SQL error log query access (xp_readerrorlog)	Partial	PostgreSQL writes plain-text or CSV logs to disk; the log_fdw extension or a shell pre-step can load them into a queryable table
Native Database Mail	No equivalent	Confirmed directly in AWS’s own SQL Server-to-PostgreSQL migration documentation; report delivery must happen outside the database (see Section 12)

12The Report Format: Issues Only, Prioritized, With Impact

This is the part that actually makes a health check useful instead of just accurate. A report that lists every metric trains its readers to stop reading it. The format below has three rules: report issues, not passing checks; rank every issue by priority; and state what changes if the issue is fixed.

Rule 1: Issues Only

A category that passed does not need a paragraph. One line is enough: “Cache hit ratio: 96.2%, no action needed.” Every line of detail in the report should belong to something that actually needs a decision.

Rule 2: Priority Tiers, Not a Flat List

Tier	Meaning	Example trigger
P1 — Act Today	Actively causing or about to cause a production problem	Idle-in-transaction session blocking vacuum for hours; replication lag climbing with no ceiling in sight
P2 — This Week	Degrading performance or heading toward a problem, not yet urgent	Cache hit ratio trending down over several days; a table’s dead-tuple percentage climbing past 15–20%
P3 — Monitor / Backlog	Worth tracking, not worth interrupting other work for	A configuration value still at default that should eventually be reviewed; a missing-index candidate with low query frequency

Rule 3: State the Impact of the Fix

Every finding gets a fourth line beyond what’s wrong and what tier it sits in: what changes if it gets fixed, and anything that could be affected by making the change. This is the same discipline as a “Risk If Not Done” column, applied in both directions.

Finding: [what the data shows, in one sentence]
Evidence: [the specific query result: table name, percentage, count, timestamp]
Priority: [P1 / P2 / P3]
Solution: [the specific action, e.g., "run VACUUM on table_name" or
           "lower autovacuum_vacuum_scale_factor to 0.05 for this table"]
Impact if resolved: [what improves, stated concretely, e.g., "removes ~X
           dead rows currently read on every sequential scan of this table"]
Impact if left unresolved: [what continues to degrade or what risk remains,
           e.g., "dead tuple percentage will keep climbing until the next
           autovacuum cycle, currently N days out at the current write rate"]

Filled in, one real finding looks like this:

Finding: orders table dead tuple percentage exceeds the 10% review threshold.
Evidence: n_dead_tup = 84,200, n_live_tup = 612,000 (13.8%), last_autovacuum
          3 days ago.
Priority: P2 -- This Week
Solution: Manually VACUUM the table now; lower autovacuum_vacuum_scale_factor
          on this table from the 20% default to 5-10% given its write volume.
Impact if resolved: Sequential scans on this table stop reading dead rows
          alongside live ones, reducing pages read per scan.
Impact if left unresolved: Dead tuple percentage keeps climbing until the
          next autovacuum cycle fires under the current 20% threshold,
          which at current write volume is still several days out.

13Delivering the Report: Email or Manual Export

With the queries and the format decided, delivery is a mechanical choice between two options, not the central design decision of the health check itself.

Option A: Manual Export

psql has a genuine built-in HTML table output mode, documented back to PostgreSQL 8.4 and unchanged in current releases. This is the simplest path when a scheduled email isn’t needed yet.

psql -h localhost -U postgres -d appdb \
  -H \
  -c "SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables;" \
  -o /tmp/health_check_report.html

The -H flag (equivalent to \pset format html) produces a properly formed HTML table. Open the resulting file directly, or paste its content into whatever the review process already uses.

Option B: Scheduled Email

PostgreSQL has no built-in equivalent to SQL Server’s Database Mail; this is confirmed directly in AWS’s own migration documentation, not a version-specific gap. The standard, lowest-risk path is generating the HTML with psql -H as above, then sending it with a mail utility that supports an explicit HTML content type.

mailx -a 'Content-Type: text/html' \
      -s "PostgreSQL Morning Health Check" \
      dba-team@example.com < /tmp/health_check_report.html

Third-party options exist for in-database email (pgMail, pgsmtp), but both require enabling an untrusted procedural language inside PostgreSQL itself, which is a real security trade-off, not a minor one. Keeping the mail step outside the database avoids that trade-off entirely.

14Scheduling It

Either OS-level cron or the pg_cron extension can trigger this. pg_cron schedules SQL and function calls, not shell commands, so a common pattern is pg_cron triggering a function that writes results to a table, with OS cron still handling the file assembly and mail send.

# Standard crontab entry, 6:00 AM daily
0 6 * * * /opt/scripts/pg_health_check.sh

15Key Takeaways

• A large share of a SQL Server health check translates directly to PostgreSQL views; the rest is either a partial proxy or has no equivalent at all. This guide states which is which rather than papering over the difference.
• Cache hit ratio from pg_statio_user_tables is the documented substitute for Page Life Expectancy; there is no single-number equivalent.
• n_mod_since_analyze and n_dead_tup in pg_stat_user_tables are the direct equivalents of SQL Server's stale-statistics and fragmentation signals, with VACUUM as the corrective action rather than REBUILD.
• PostgreSQL has no missing-index DMV; sequential scan frequency weighted by table size is the honest proxy, not a replacement.
• PostgreSQL has no native Database Mail, confirmed directly in AWS's own documentation. Delivery has to happen outside the database, either as a manual export using psql -H or a scheduled shell script sending HTML mail.
• A health check report is only as useful as its format. Issues only, ranked by priority tier, with the impact of each fix stated explicitly, is what turns a data dump into something worth reading every morning.

The technical information in this article was verified against official PostgreSQL documentation, official AWS migration documentation, and Ryan Booz and Grant Fritchey's Introduction to PostgreSQL for the Data Professional (Redgate Books, 2024) at the time of publication. Extension availability and default configuration values can vary by PostgreSQL version and hosting environment. Always validate implementation details against current official documentation before deploying to production.

References

• Official Docs: The Cumulative Statistics System (PostgreSQL Documentation)
• Official Docs: psql (PostgreSQL Documentation)
• Official Docs: Predefined Roles (PostgreSQL Documentation)
• Official Docs: pg_amcheck (PostgreSQL Documentation)
• Official Docs: Write Ahead Log Settings, wal_level (PostgreSQL Documentation)
• Official Docs: pg_stat_statements (PostgreSQL 13 Documentation, confirming the total_exec_time column rename)
• Official Docs: Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL
• Community and Industry Sources: EnterpriseDB, "Effective PostgreSQL Monitoring: last_seq_scan and last_idx_scan in PostgreSQL 16"
• Book: Ryan Booz & Grant Fritchey, Introduction to PostgreSQL for the Data Professional, First Edition (Redgate Books, 2024) — Chapters 5, 11, 13, and 15
• SQLYARD: 12 Things SQL Server DBAs Need to Know Before Supporting PostgreSQL
• SQLYARD: The PostgreSQL Migration Trap: Heaps, 3.22 Billion Forwarded Fetches, and How to Fix It in SQL Server