Alright, let’s get our hands dirty with the two most straightforward, no-nonsense views in the PostgreSQL statistics arsenal: pg_stat_user_tables and pg_stat_user_indexes. These are your first port of call when you’re trying to figure out what your database is actually doing, as opposed to what you think it’s doing. They’re gloriously simple counters that track every access to your tables and indexes. No magic, no voodoo—just cold, hard numbers.

Think of them as the surveillance cameras on your data. They don’t judge, they just record. And like any good surveillance footage, the story is in how you interpret the sequence of events.

What’s Actually in These Views?

Let’s pull back the curtain. The columns in these views are mostly self-explanatory, but a few are deceptively simple. Here’s the cheat sheet.

For pg_stat_user_tables:

  • seq_scan: The number of times the table has been read via a sequential scan (a.k.a., a full table scan). High numbers here are a giant, flashing neon sign saying “INDEX ME, MAYBE?”.
  • seq_tup_read: The number of tuples (rows) read by those sequential scans. This number can get terrifyingly large, quickly.
  • idx_scan: The number of times an index was used to read the table. You want this to be high relative to seq_scan.
  • n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd: Counts of inserts, updates, deletes, and Hot Updates (a very good thing we’ll get to). These are your write volume metrics.

For pg_stat_user_indexes:

  • idx_scan: The number of times this specific index has been used. This is the big one. An index with a idx_scan of zero is a waste of disk space and write performance. It’s a ghost. Banish it.
  • idx_tup_read: The number of index entries returned.
  • idx_tup_fetch: The number of table rows fetched using this index.

The most important thing to remember? These counters are cumulative since the last statistics reset or the server start. A number by itself is meaningless; it’s the rate of change that tells the story.

Interpreting the Numbers: The Real-World Play-by-Play

Don’t just look at the numbers; ask them questions. Here’s how.

Is this table being scanned to death?

SELECT schemaname, relname, seq_scan, seq_tup_read,
       seq_tup_read / NULLIF(seq_scan, 0) AS avg_tuples_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

This query finds your most scanned tables and the average number of rows sucked in each time. If you see a table with a high seq_scan count and a massive avg_tuples_per_scan, you’ve found a prime candidate for adding an index or tuning a query. Your disk I/O is weeping.

Are my indexes even being used?

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

The indexes at the top of this list (with the lowest scan counts) are your suspects. An index with zero scans is a strong candidate for deletion. But be smart—check if it’s a unique constraint or supports a foreign key before you drop it. The optimizer might not use it for SELECTs, but it’s still enforcing your data integrity.

How HOT is my table? (No, really.) HOT (Heap-Only Tuples) is a fantastic PostgreSQL feature that allows a row update to be performed without needing to update every single index on the table, if the update doesn’t change any indexed columns. A high n_tup_hot_upd ratio is excellent.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       (n_tup_hot_upd::float / NULLIF(n_tup_upd, 0) * 100) AS hot_rate
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_rate ASC;

A low hot_rate means your updates are likely expensive because they’re forcing index updates. Consider your index design: do you really need an index on every column that gets updated?

The Gotchas and the Fine Print

  1. They’re Not Transaction-Aware. This is a big one. The counters are updated as the action happens, even if the transaction that performed the action later rolls back. Why? Because the cost of making these stats transactional would be immense. So, these views show you activity, not necessarily committed work. It’s a subtle but crucial distinction.
  2. Resetting the Counters. Sometimes you want a clean slate, like before running a test benchmark. You can do this with the pg_stat_reset() function. Be warned: this nukes all statistics for the entire database cluster. There’s no going back. Use it with the solemn respect of someone launching a nuke.
  3. The IO Timing Columns. You might see heap_blks_read and heap_blks_hit columns. These measure blocks read from disk vs. from cache. For them to be populated, you need to have track_io_timing enabled in postgresql.conf, which incurs a tiny overhead. It’s usually worth it for the insight.

In short, pg_stat_user_tables and pg_stat_user_indexes are your baseline vitals. They won’t give you the exact query text that’s causing the problem, but they’ll point you directly to the table or index that’s screaming in pain. It’s the first step in going from “my database is slow” to “this specific table is being abused by full scans.” And that, my friend, is half the battle.