The Statistics Collector
Find hot tables with pg_stat_user_tables, measure a real cache hit ratio, and catch a scoping gotcha in counter resets
Every table and index in a running PostgreSQL cluster has a live entry in the statistics collector: how many times it was scanned sequentially, how many times through an index, how many rows are dead but not yet reclaimed, how many blocks came from the OS cache versus were already sitting in shared_buffers. None of this requires an external monitoring tool — it is a SQL query away, in pg_stat_user_tables and pg_statio_user_tables.\n\nThis lab builds the habit of checking these views before touching anything else. You will generate a real workload against a 50,000-row table, watch seq_scan and idx_scan diverge depending on how a query filters, measure an actual cache hit ratio instead of assuming one, and then hit a genuine gotcha when you try to reset the counters for a clean baseline: pg_stat_reset_single_table_counters() takes exactly one object ID, and a table's indexes are separate objects from the table itself.
The Two Views That Answer Most First Questions
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables
WHERE relname = 'orders';
pg_stat_user_tables answers "how is this table actually being accessed?" — pg_statio_user_tables answers "is that access coming from RAM or disk?".
Cache Hit Ratio, By Hand
SELECT
heap_blks_hit,
heap_blks_read,
round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 1) AS hit_ratio_pct
FROM pg_statio_user_tables
WHERE relname = 'orders';
A ratio near 100% means this table's working set comfortably fits in shared_buffers (or the OS page cache underneath it). A ratio that drops under real production load is the first sign a table has outgrown its cache — Lab 2.4.6 goes further into exactly what shared_buffers is holding.
Resetting for a Clean Baseline
SELECT pg_stat_reset_single_table_counters('monitoring.orders'::regclass::oid);
This resets seq_scan, n_live_tup, n_dead_tup, and the other table-level counters to zero for exactly the object ID you pass in — nothing more. An index on that table is its own catalog object with its own statistics entry, so resetting the table does not reset the table's idx_scan count, which is tracked against the index's OID:
SELECT pg_stat_reset_single_table_counters('monitoring.idx_orders_customer_id'::regclass::oid);
Both calls are needed for a fully clean baseline on a table that has any indexes at all.
seq_scan vs idx_scan
seq_scan counts full-table reads — every row examined regardless of whether it matches. idx_scan counts reads that went through an index to find only the matching rows. A table with a high seq_scan count relative to its row count and query volume is a strong hint that a query is filtering on a column with no useful index.
n_live_tup vs n_dead_tup
PostgreSQL never overwrites a row in place — an UPDATE writes a new row version and marks the old one dead; a DELETE just marks the row dead. n_live_tup is the count of currently-visible rows; n_dead_tup is rows still occupying space that VACUUM has not yet reclaimed. A live row count that never moves while dead tuples climb is exactly what heavy UPDATE traffic looks like from the statistics collector.
pg_stat_reset_single_table_counters(oid)
Resets the statistics for exactly one catalog object — a table OR an index, never both from a single call. Because an index is its own object with its own row in pg_stat_user_indexes, a table's idx_scan figure (which is sourced from its indexes' own counters) survives a reset of the table's OID untouched.
🔎 Orient: Check the Baseline
Before running any workload, check what the statistics collector already shows for the freshly-created orders table.
psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 2 | 0 | 50000 | 0 (1 row)
🐌 Generate a Seq-Scan Workload
Run 3,000 lookups filtered on item, a column with no index, then check how seq_scan responded.
psql -U postgres -d beer_db -c "DO \$\$ DECLARE i int; r monitoring.orders%rowtype; BEGIN FOR i IN 1..3000 LOOP SELECT * INTO r FROM monitoring.orders WHERE item = 'item-7' LIMIT 1; END LOOP; END \$\$;"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "DO \$\$ DECLARE i int; r monitoring.orders%rowtype; BEGIN FOR i IN 1..3000 LOOP SELECT * INTO r FROM monitoring.orders WHERE item = 'item-7' LIMIT 1; END LOOP; END \$\$;" SET DO student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 3002 | 0 | 50000 | 0 (1 row)
🎯 Generate an Idx-Scan Workload
Run 3,000 lookups filtered on customer_id, which is indexed, and compare the new idx_scan count against the unchanged seq_scan.
psql -U postgres -d beer_db -c "DO \$\$ DECLARE i int; r monitoring.orders%rowtype; BEGIN FOR i IN 1..3000 LOOP SELECT * INTO r FROM monitoring.orders WHERE customer_id = (i % 500) + 1 LIMIT 1; END LOOP; END \$\$;"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "DO \$\$ DECLARE i int; r monitoring.orders%rowtype; BEGIN FOR i IN 1..3000 LOOP SELECT * INTO r FROM monitoring.orders WHERE customer_id = (i % 500) + 1 LIMIT 1; END LOOP; END \$\$;" SET DO student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 3002 | 3000 | 50000 | 0 (1 row)
📈 Measure the Cache Hit Ratio
Check pg_statio_user_tables to see whether all those scans came from shared_buffers or from disk.
psql -U postgres -d beer_db -c "SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = 'orders';" SET heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit ----------------+---------------+---------------+-------------- 0 | 59830 | 0 | 205204 (1 row)
💀 Generate Dead Tuples
UPDATE 10,000 rows and confirm n_dead_tup rises while n_live_tup stays exactly where it was.
psql -U postgres -d beer_db -c "UPDATE monitoring.orders SET status = 'shipped' WHERE id % 5 = 0;"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "UPDATE monitoring.orders SET status = 'shipped' WHERE id % 5 = 0;" SET UPDATE 10000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 3003 | 3000 | 50000 | 10000 (1 row)
🔄 Reset the Table — and Meet the Gotcha
Reset the table's own counters and see which numbers actually went back to zero.
psql -U postgres -d beer_db -c "SELECT pg_stat_reset_single_table_counters('monitoring.orders'::regclass::oid);"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_stat_reset_single_table_counters('monitoring.orders'::regclass::oid);" SET pg_stat_reset_single_table_counters -------------------------------------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 3000 | 0 | 0 (1 row)
🕵️ Prove idx_scan Is Still Live
Run one more indexed lookup and confirm idx_scan keeps climbing from where it was, not from zero.
psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.orders WHERE customer_id = 42;"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.orders WHERE customer_id = 42;" SET count ------- 100 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 3001 | 0 | 0 (1 row)
✅ Reset the Index Too
Reset idx_orders_customer_id by its own OID, and confirm every counter on the table is now genuinely at zero.
psql -U postgres -d beer_db -c "SELECT pg_stat_reset_single_table_counters('monitoring.idx_orders_customer_id'::regclass::oid);"psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_stat_reset_single_table_counters('monitoring.idx_orders_customer_id'::regclass::oid);" SET pg_stat_reset_single_table_counters -------------------------------------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';" SET relname | seq_scan | idx_scan | n_live_tup | n_dead_tup ---------+----------+----------+------------+------------ orders | 0 | 0 | 0 | 0 (1 row)
Lab 2.4.1 complete. You can now read the statistics collector directly, without any external tool:\n\n\n seq_scan vs idx_scan : ✅ measured on a real workload\n Cache hit ratio : ✅ heap_blks_read vs heap_blks_hit\n n_live_tup / n_dead_tup : ✅ watched dead tuples accumulate\n pg_stat_reset_single_table_counters : ✅ scope understood — table and index are separate resets\n
Enable JavaScript to run the live terminal and track your progress.