Postgres pg_stat_user_tables: The Per-Table Statistics Every Operator Should Watch

The Postgres operational dashboard most teams need but never quite build lives inside the database itself, in a system view called pg_stat_user_tables. The view has one row per user table and roughly fifteen columns of per-table operational statistics: insert/update/delete counts, sequential vs index scan counts, dead-tuple accumulation, last vacuum and analyze timestamps, autovacuum work history. Reading it well is one of the higher-leverage Postgres operational skills, because most operational problems show up in the view before they show up as user-visible incidents.

The shape of the view

The view is in the pg_catalog schema and is automatically available to any user with read access to the catalog. The columns can be grouped into four conceptual categories. The first is the write-activity counters: n_tup_ins, n_tup_upd, n_tup_del, and the breakdown of HOT updates (n_tup_hot_upd) and newpage updates (n_tup_newpage_upd in Postgres 16+). The second is the read-activity counters: seq_scan and seq_tup_read for sequential scans, idx_scan and idx_tup_fetch for index scans. The third is the tuple-state counters: n_live_tup for approximate live row count and n_dead_tup for approximate dead tuple count. The fourth is the autovacuum history: last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, and the per-table counts of how many times each has run.

The counters are cumulative since the last pg_stat_reset() call (or since database startup if no reset has occurred). The accumulation is helpful for spotting trends and unhelpful for spotting recent anomalies, because the absolute numbers can be very large after long uptime. Most operational use of the view involves either taking periodic snapshots and computing deltas, or comparing the absolute numbers across tables to identify outliers.

The diagnostic question: which tables are doing all the work

The first diagnostic that pg_stat_user_tables answers is which tables in the database are seeing the most activity. The naive expectation is that the application's main tables dominate the activity, but the actual distribution is often skewed in surprising ways. Audit log tables, session tables, queue tables, and event log tables often dominate the write activity even though they are not the application's main feature surface.

The query for the top write-activity tables is:

SELECT schemaname, relname,
  n_tup_ins, n_tup_upd, n_tup_del,
  n_tup_ins + n_tup_upd + n_tup_del AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

The result is usually informative on first run. The tables at the top of the list are the ones consuming the most disk write bandwidth, the most WAL volume, the most autovacuum work, and the most replication lag budget. Optimization effort should be concentrated there if any is justified.

The HOT update ratio

The HOT (Heap-Only Tuple) update mechanism is one of Postgres's most underappreciated optimizations. When an update changes a row's values but does not change any indexed column, Postgres can do the update in place without updating the indexes, which is dramatically cheaper than a non-HOT update. The n_tup_hot_upd column counts the HOT updates, and the ratio of HOT updates to total updates is a diagnostic for whether the table is laid out efficiently.

The query for the HOT ratio is:

SELECT relname,
  n_tup_upd,
  n_tup_hot_upd,
  ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC;

A HOT ratio above 80% is healthy. A ratio below 50% on a high-update table is worth investigating. The usual culprits are indexes on columns that get updated frequently (consider whether the index is necessary, or whether the column updates are necessary), and fillfactor settings that leave too little room per page for in-place updates (consider lowering fillfactor on high-update tables to 70-80, which leaves room for HOT updates at the cost of larger storage).

The sequential-vs-index scan ratio

The seq_scan vs idx_scan ratio is the most-commonly-cited diagnostic in pg_stat_user_tables and the easiest to misuse. The naive interpretation is that high seq_scan counts indicate missing indexes. The actual interpretation requires knowing the table size: a sequential scan on a 10-row table is the right plan, and a sequential scan on a 10-million-row table is usually wrong. The query that combines the scan counts with the row count is:

SELECT relname,
  n_live_tup,
  seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch,
  ROUND(1.0 * seq_tup_read / NULLIF(seq_scan, 0), 1) AS avg_seq_tuples
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 100000
ORDER BY seq_tup_read DESC;

The diagnostic is the average sequential tuples per scan (seq_tup_read / seq_scan). If the average is close to the live tuple count, the sequential scans are reading the whole table, which is expensive. If the average is much smaller, the scans are probably hitting early exit conditions and are not as expensive as the raw count suggests.

The follow-up step is to look at the actual queries that are running sequential scans, which lives in pg_stat_statements (a related extension that needs to be installed). The combination of pg_stat_user_tables showing which tables get sequentially scanned and pg_stat_statements showing which queries do the scanning is the diagnostic chain that identifies missing-index candidates.

The dead tuple accumulation diagnostic

The n_dead_tup column is the count of tuples that have been deleted or updated but not yet vacuumed. Dead tuples accumulate naturally between vacuum cycles and are cleaned up by autovacuum. When the cleanup falls behind, the dead tuple count grows, and the consequences are several: query plans get worse because the planner sees inflated row counts, sequential scans get slower because they have to skip the dead tuples, and index queries get slower for the same reason.

The diagnostic query is the dead-tuple ratio:

SELECT relname,
  n_live_tup, n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead-tuple ratio under 5% is healthy. A ratio between 5% and 20% is normal during high-write periods between autovacuum runs. A ratio above 20% indicates that autovacuum is falling behind, and the operator should investigate. The usual causes are autovacuum throttling that is too conservative (the default autovacuum_vacuum_cost_limit of 200 was set for the spinning-disk era and is usually too low on modern NVMe storage), long-running transactions that hold back the xmin horizon and prevent vacuum from cleaning up tuples, or autovacuum scale factors that are too conservative for the table size.

The remediation for any of these is per-table autovacuum tuning via ALTER TABLE ... SET:

ALTER TABLE hot_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 1000
);

The aggressive settings tell autovacuum to start cleaning the table when 5% of rows are dead (rather than the default 20%) and to do so at five times the default throttling rate. The trade-off is more I/O during vacuum runs and more frequent autovacuum activity, but in practice the trade-off is favorable for any high-write table on modern hardware.

The autovacuum history columns

The last_autovacuum and last_autoanalyze columns give the timestamps of the most recent autovacuum and autoanalyze on each table. The corresponding autovacuum_count and autoanalyze_count columns give the cumulative counts since the statistics reset.

The first diagnostic is which tables have not been autovacuumed recently:

SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE last_autovacuum < now() - interval '7 days'
  OR last_autovacuum IS NULL
ORDER BY n_dead_tup DESC;

A table that has not been autovacuumed in a week is unusual and worth investigating. The usual causes are: the table is small and infrequently changed (which is normal and not a problem), the table is large and infrequently changed (the autovacuum threshold has not been reached, which is also normal), or the table has been continuously blocked by a long-running transaction (which is a problem). The diagnostic for the third case is to cross-reference with pg_stat_activity to look for long-running transactions.

The second diagnostic is autovacuum frequency. A table with very high autovacuum_count relative to the period of observation might be getting autovacuumed too frequently, which is operationally expensive even when each run is short. The fix is the opposite of the dead-tuple-accumulation fix: raise the scale factor so that autovacuum runs less often but does more work per run. The trade-off depends on the table; high-frequency tables benefit from frequent small vacuums, but tables with bulk-load patterns benefit from less-frequent larger vacuums.

The reset story

The cumulative statistics can be reset via SELECT pg_stat_reset(); for the whole database or per-table via specific reset functions. The reset is useful when you want to measure activity over a defined interval rather than since database start. The typical pattern is to reset at the beginning of a load test or before a behavioral observation period, then read the view after the period to see the per-table activity during that interval.

The downside is that resetting also resets the autovacuum count, which is occasionally relevant for diagnosing whether autovacuum has been keeping up. For most diagnostic purposes the reset is harmless, but the operator should be aware that the cumulative counters are not preserved.

What pg_stat_user_tables does not show

The view is per-table activity statistics; it is not query history (which is in pg_stat_statements), it is not connection or lock state (which is in pg_stat_activity and pg_locks), and it is not I/O patterns (which are in pg_stat_io on Postgres 16 or in pg_statio_user_tables on earlier versions). The view is a starting point for many diagnostics, but it is not a complete operational picture. The operational picture requires combining it with the related views, each of which exposes a different slice of the database's state.

The view also does not show schema-level or database-level aggregates directly; the equivalent views pg_stat_user_indexes for per-index statistics and pg_stat_database for per-database aggregates exist and complement pg_stat_user_tables for the corresponding analyses. The full operator's toolkit includes all of these views, but pg_stat_user_tables is the one operators reach for most often, and learning to read it well is the highest-leverage starting point.

What this looks like across our four products

Our four products (DocuMint, CronPing, FlagBit, WebhookVault) are currently SQLite-based, where the equivalent of pg_stat_user_tables is the application-level activity logs we instrument ourselves. The Postgres migration plan includes pg_stat_user_tables-driven monitoring as a launch requirement, because the operational visibility that the view provides is genuinely important and we want it from day one rather than after the first operational incident reveals its absence.

The deeper observation is that Postgres exposes substantial operational visibility through its system views, and most teams use only a small fraction of what is available. Reading pg_stat_user_tables regularly is one of the lower-effort operational disciplines that compounds: every diagnostic you can answer from the view is one you do not need to instrument separately, and the view captures most of the questions an operator asks about a Postgres database in production.