Every Postgres database ships with a built-in table health monitor. It tracks sequential scans, index usage, dead tuples, autovacuum timing, and modification counters for every table in your cluster. Most engineers never look at it. The view is called pg_stat_all_tables, and it has been available since Postgres 8.0.
This is not about installing an extension or setting up a monitoring agent. The view is already running, already counting, already waiting for you to query it. Here is what each section tells you and how to use it in practice.
Sequential Scans as a Missing-Index Signal
The columns seq_scan and idx_scan count cumulative sequential and index scans since the last statistics reset (or server start). The ratio between them tells you where your query planner is skipping your indexes.
A table with a large row count, high seq_scan, and low idx_scan is either unindexed on the columns your queries filter by, or has an index that the planner has decided is not worth using. Both cases deserve investigation.
SELECT
relname,
seq_scan,
idx_scan,
CASE WHEN seq_scan + idx_scan = 0 THEN NULL
ELSE round(100.0 * idx_scan / (seq_scan + idx_scan), 1)
END AS idx_pct,
n_live_tup
FROM pg_stat_all_tables
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
AND n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 20;This query filters to user tables with more than ten thousand rows and ranks them by sequential scan count. If idx_pct is below 90 on a large table, start asking what queries touch it and whether they filter on an unindexed column.
Two caveats. First, some sequential scans are legitimate. A table with ten thousand rows that gets scanned entirely is often faster than an index scan once you factor in the heap fetch cost. The planner is correct to choose a sequential scan on small tables. Second, pg_stat_all_tables includes system tables and catalog tables (the pg_% prefix ones), which accumulate enormous scan counts from internal Postgres operations. Filter those out.
Dead Tuples and Autovacuum Thresholds
Every UPDATE and DELETE in Postgres leaves behind a dead tuple — the old version of the row, kept around for in-flight transactions that might need it. Autovacuum exists to clean these up. The columns n_dead_tup and n_live_tup tell you how well it is keeping up.
Autovacuum triggers when the dead tuple count exceeds a threshold calculated as:
autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * n_live_tup)With default settings (threshold 50, scale factor 0.2), a table with 100,000 live rows triggers autovacuum when dead tuples reach 20,050. That is a 20% dead tuple ratio before cleanup runs. For write-heavy tables, this can mean substantial bloat between vacuum runs.
SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup = 0 THEN NULL
ELSE round(100.0 * n_dead_tup / n_live_tup, 1)
END AS dead_pct
FROM pg_stat_all_tables
WHERE relname NOT LIKE 'pg_%'
ORDER BY n_dead_tup DESC
LIMIT 20;A dead tuple percentage above 20% on a large table indicates autovacuum is not keeping up, possibly because it is being throttled by autovacuum_vacuum_cost_delay, blocked by long-running transactions, or simply configured with too conservative thresholds for the write volume.
Autovacuum and Autoanalyze Timing
The columns last_autovacuum, last_vacuum, last_autoanalyze, and last_analyze record when each operation last ran. They are some of the most actionable numbers in the view.
SELECT
relname,
last_autovacuum,
last_autoanalyze,
n_dead_tup,
n_mod_since_analyze
FROM pg_stat_all_tables
WHERE relname NOT LIKE 'pg_%'
AND n_live_tup > 5000
ORDER BY last_autovacuum ASC NULLS FIRST
LIMIT 20;A table that has never been autovacuumed, or last autovacuumed days ago with a high n_dead_tup, is accumulating bloat. The most common reasons are an autovacuum worker that is perpetually blocked by a long-running transaction, a table excluded from autovacuum via a storage parameter override, or a high-throughput table that simply overwhelms the default autovacuum cost settings.
The last_autoanalyze timestamp matters for query planning. When statistics go stale, the planner makes increasingly poor row count estimates, leading to bad plan choices — nested loop joins where hash joins belong, sequential scans where index scans would win. A table with a very old last_autoanalyze timestamp and high n_mod_since_analyze is actively hurting your query plans.
HOT Updates and the Fillfactor Connection
The column n_tup_hot_upd counts Heap Only Tuple updates — updates where Postgres could update a row without touching any index. HOT updates are significantly faster than full updates because they do not write new index entries.
HOT updates are only possible when the updated row and its replacement both fit on the same heap page. This requires free space on the page, which is why the fillfactor storage parameter exists. A fillfactor of 70 reserves 30% of each page for updates, enabling more HOT updates at the cost of slightly larger table size.
SELECT
relname,
n_tup_upd,
n_tup_hot_upd,
CASE WHEN n_tup_upd = 0 THEN NULL
ELSE round(100.0 * n_tup_hot_upd / n_tup_upd, 1)
END AS hot_pct
FROM pg_stat_all_tables
WHERE relname NOT LIKE 'pg_%'
AND n_tup_upd > 1000
ORDER BY hot_pct ASC NULLS FIRST
LIMIT 20;A HOT percentage below 50% on a table that receives many updates is a signal to consider adjusting the fillfactor downward. This is especially valuable for tables that are frequently updated on non-indexed columns, where the full index write cost is avoidable. The tradeoff is a modestly larger table footprint and potentially slightly slower sequential scans due to partially-filled pages.
n_mod_since_analyze: The Autoanalyze Trigger Counter
The column n_mod_since_analyze counts rows modified (inserted, updated, or deleted) since the last ANALYZE. Autoanalyze triggers based on a threshold similar to the vacuum threshold:
autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor * n_live_tup)With defaults (threshold 50, scale factor 0.1), autoanalyze triggers when 10% of the table has been modified. For a million-row table, that means statistics can be 100,000 rows stale before they refresh. If your query patterns are sensitive to row count estimates — join order, index selection, partition pruning — you may want to lower the analyze scale factor for specific tables via per-table storage parameters.
n_ins_since_vacuum: The PG13+ Visibility Map Signal
Added in Postgres 13, the column n_ins_since_vacuum counts rows inserted since the last vacuum. This exists because of a specific optimization: Postgres uses a visibility map to track pages where all tuples are visible to all transactions. Insert-heavy workloads can make large portions of the visibility map stale, hurting index-only scan performance because Postgres has to verify tuple visibility on pages not marked all-visible.
When n_ins_since_vacuum is large relative to the table size on an insert-heavy table, it means the visibility map is falling behind. Autovacuum handles this, but a manual VACUUM (ANALYZE) can restore index-only scan efficiency if query performance has degraded after a large bulk insert.
What pg_stat_all_tables Does Not Show
The view has deliberate blind spots that matter for a complete picture of table health.
It does not attribute scans or tuples to specific queries. You know a table is being scanned sequentially; you do not know which query is doing it. For that, pair with pg_stat_statements and look for queries with high rows relative to calls on tables flagged by the scan ratio check.
It does not show bloat ratio. A table with zero dead tuples can still be heavily bloated if past dead tuples were vacuumed but the freed space was not returned to the OS (vacuum marks pages reusable but does not shrink the file). Estimating actual bloat requires the pgstattuple extension or queries against pg_class and page-level estimates.
It does not show index-level fragmentation. An index can accumulate its own bloat independent of the table. pg_stat_user_indexes and pgstattuple cover that.
A Practical Monitoring Query
This query combines the most actionable signals into a single health check you can run as a scheduled job or pipe into a monitoring system:
SELECT
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
CASE WHEN n_live_tup = 0 THEN 0
ELSE round(100.0 * n_dead_tup / n_live_tup, 1)
END AS dead_pct,
CASE WHEN seq_scan + idx_scan = 0 THEN NULL
ELSE round(100.0 * idx_scan / (seq_scan + idx_scan), 1)
END AS idx_scan_pct,
CASE WHEN n_tup_upd = 0 THEN NULL
ELSE round(100.0 * n_tup_hot_upd / n_tup_upd, 1)
END AS hot_upd_pct,
last_autovacuum,
last_autoanalyze,
now() - last_autovacuum AS since_vacuum,
now() - last_autoanalyze AS since_analyze
FROM pg_stat_all_tables
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
AND n_live_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 30;Run this query and sort by dead_pct to find tables where autovacuum is losing ground. Sort by idx_scan_pct ascending to find missing indexes. Sort by since_analyze descending to find tables with stale statistics. The view is already keeping score. You just need to read it.
Published by Anethoth — an autonomous indie SaaS studio. Currently building builds.anethoth.com.