pg_stat_database / table / index Views and Interpreting Cache/Scan/Write Counters

Learning outcomes

The immediate blocker is fixed, but ServiceHub still feels slower than last week. Cumulative statistics answer a different question from pg_stat_activity: “what has this database/table/index accumulated since its statistics baseline?” Counters become useful only when their reset time, collection lag, workload interval, and denominator are known.

1. Build a read/write workload with one useful index

DROP TABLE IF EXISTS app.ch21_workload;SET ROLE servicehub_owner;CREATE TABLE app.ch21_workload (  event_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_id integer NOT NULL,  status text NOT NULL,  created_at timestamptz NOT NULL,  payload text NOT NULL);INSERT INTO app.ch21_workload(tenant_id,status,created_at,payload)SELECT (g % 50) + 1,       (ARRAY['open','closed','queued'])[(g % 3)+1],       clock_timestamp() - (g || ' seconds')::interval,       repeat(chr(65 + (g % 26)), 120)FROM generate_series(1,50000) AS g;CREATE INDEX ch21_workload_tenant_status_idxON app.ch21_workload (tenant_id, status);RESET ROLE;ANALYZE app.ch21_workload;

The table is intentionally large enough to make counters move but small enough for a local lab. Exact plan choices and timings depend on your machine and cache state; the lesson never assumes one particular millisecond result.

2. Database-wide counters are cumulative, not instantaneous

SELECT datname,       numbackends,       xact_commit,       xact_rollback,       blks_read,       blks_hit,       tup_returned,       tup_fetched,       tup_inserted,       tup_updated,       tup_deleted,       temp_files,       pg_size_pretty(temp_bytes) AS temp_bytes,       deadlocks,       blk_read_time,       blk_write_time,       active_time,       idle_in_transaction_time,       stats_resetFROM pg_stat_databaseWHERE datname = current_database();

numbackends reflects current connections; most other columns accumulate since stats_reset. A database that has been running for months naturally has larger totals than one restarted/reset yesterday. Compare rates over matched intervals, not raw magnitude across unequal baselines.

3. A “cache hit ratio” is specifically PostgreSQL shared buffers

SELECT datname,       blks_hit,       blks_read,       round(         100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0),         2       ) AS pg_shared_buffer_hit_pctFROM pg_stat_databaseWHERE datname = current_database();

blks_hit means PostgreSQL found the requested block in its shared buffer cache. A blks_read event means PostgreSQL had to ask the operating system for the block; the kernel may still satisfy that read from its own page cache rather than physical storage. Therefore this ratio is not “percent of reads served from RAM anywhere in the stack,” and it is not a standalone storage-health score.

4. Table statistics describe access and tuple churn

SELECT relname,       seq_scan,       last_seq_scan,       seq_tup_read,       idx_scan,       last_idx_scan,       idx_tup_fetch,       n_tup_ins,       n_tup_upd,       n_tup_del,       n_tup_hot_upd,       n_live_tup,       n_dead_tup,       last_vacuum,       last_autovacuum,       last_analyze,       last_autoanalyzeFROM pg_stat_user_tablesWHERE relid = 'app.ch21_workload'::regclass;

n_live_tup and n_dead_tup are estimates used for operational decisions, not exact row counts. seq_scan is not “bad scans”: reading a large fraction of a table can make a sequential scan optimal. idx_scan shows index-scan initiations through indexes of the table, not how much business value the index provides.

5. Index counters need both index and table context

SELECT indexrelname,       idx_scan,       last_idx_scan,       idx_tup_read,       idx_tup_fetchFROM pg_stat_user_indexesWHERE relid = 'app.ch21_workload'::regclassORDER BY indexrelname;

An index with idx_scan=0 since the current baseline is a candidate for investigation, not an automatic DROP target. It may enforce a uniqueness constraint, serve a rare incident query, be new, or have had its statistics reset recently. Check constraints/dependencies and observe a representative business interval before removing it.

6. pg_statio shows buffer interactions, not physical-disk truth

SELECT relname,       heap_blks_read,       heap_blks_hit,       idx_blks_read,       idx_blks_hit,       toast_blks_read,       toast_blks_hitFROM pg_statio_user_tablesWHERE relid = 'app.ch21_workload'::regclass;SELECT indexrelname,       idx_blks_read,       idx_blks_hitFROM pg_statio_user_indexesWHERE relid = 'app.ch21_workload'::regclassORDER BY indexrelname;

These counts explain which PostgreSQL buffers were missed/hit for heap/index/TOAST objects. They still do not reveal whether a kernel read came from SSD, SAN, network storage, or the OS page cache. Correlate with pg_stat_io and OS metrics.

7. Generate two distinct access patterns

SELECT count(*)FROM app.ch21_workloadWHERE tenant_id = 17  AND status = 'open';UPDATE app.ch21_workloadSET payload = payload || 'x'WHERE tenant_id = 17  AND status = 'queued';

SELECT status, count(*), avg(length(payload))FROM app.ch21_workloadWHERE created_at >= now() - interval '1 day'GROUP BY statusORDER BY status;

After these statements finish, cumulative statistics may take a short interval to flush from the backend to shared statistics. An observer session is the cleanest place to collect before/after samples.

8. Build a baseline table for explicit deltas

DROP TABLE IF EXISTS app.ch21_table_stat_sample;CREATE TABLE app.ch21_table_stat_sample (  observed_at timestamptz NOT NULL,  seq_scan bigint NOT NULL,  seq_tup_read bigint NOT NULL,  idx_scan bigint NOT NULL,  idx_tup_fetch bigint NOT NULL,  n_tup_ins bigint NOT NULL,  n_tup_upd bigint NOT NULL,  n_tup_del bigint NOT NULL,  n_dead_tup bigint NOT NULL);INSERT INTO app.ch21_table_stat_sampleSELECT clock_timestamp(),       seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,       n_tup_ins, n_tup_upd, n_tup_del, n_dead_tupFROM pg_stat_user_tablesWHERE relid = 'app.ch21_workload'::regclass;

Run a known workload from a different session, wait for it to go idle, then insert another sample. The difference between adjacent samples is the observed interval workload.

WITH s AS (  SELECT *,         lag(observed_at) OVER (ORDER BY observed_at) AS prev_at,         lag(seq_scan) OVER (ORDER BY observed_at) AS prev_seq_scan,         lag(idx_scan) OVER (ORDER BY observed_at) AS prev_idx_scan,         lag(n_tup_upd) OVER (ORDER BY observed_at) AS prev_upd,         lag(n_dead_tup) OVER (ORDER BY observed_at) AS prev_dead  FROM app.ch21_table_stat_sample)SELECT observed_at,       observed_at - prev_at AS interval,       seq_scan - prev_seq_scan AS seq_scans_delta,       idx_scan - prev_idx_scan AS idx_scans_delta,       n_tup_upd - prev_upd AS updates_delta,       n_dead_tup - prev_dead AS dead_estimate_deltaFROM sWHERE prev_at IS NOT NULLORDER BY observed_at DESCLIMIT 1;

9. Understand statistics snapshot caching

SHOW stats_fetch_consistency;BEGIN;SELECT pg_stat_get_snapshot_timestamp() AS before_read;SELECT seq_scan, idx_scanFROM pg_stat_user_tablesWHERE relid = 'app.ch21_workload'::regclass;SELECT pg_stat_get_snapshot_timestamp() AS after_read;SELECT pg_stat_clear_snapshot();COMMIT;

With the default cache mode, statistics for an object can be cached within the transaction. snapshot makes a consistent all-statistics snapshot on first access; none re-fetches each access and is often suitable for monitoring collectors that read values once. pg_stat_clear_snapshot() discards cached/snapshot values; it does not force another backend to publish unfinished statistics.

10. Resetting counters is an operational action

Do not reset production statistics just to make graphs easy. Reset times are themselves important context; some reset functions also affect counters used by autovacuum decisions. Prefer storing external baselines and subtracting samples.

Authoritative references

Statistics and logging fields evolve across PostgreSQL majors. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Cumulative Statistics System
• PostgreSQL 18 — Run-time Statistics Configuration
• PostgreSQL 18 — ANALYZE
• PostgreSQL 18 — Monitoring Database Activity