How to Use pg_stat_user_tables in PostgreSQL for Table Statistics

Use the pg_stat_user_tables view in PostgreSQL to get statistics about user tables such as number of sequential scans, index scans, and tuples inserted or deleted. Query it like a regular table with SELECT * FROM pg_stat_user_tables; to see current usage stats for all user tables in your database.

Syntax

The pg_stat_user_tables is a system view in PostgreSQL that shows statistics about user tables. You query it like a normal table using SELECT. It has columns such as relid (table OID), schemaname, relname (table name), and counters like seq_scan (number of sequential scans), idx_scan (number of index scans), n_tup_ins (tuples inserted), n_tup_upd (tuples updated), and n_tup_del (tuples deleted).

You can filter or order results to focus on specific tables or statistics.

sql

SELECT * FROM pg_stat_user_tables;

Example

This example shows how to get the table name, number of sequential scans, and number of index scans for all user tables, ordered by the number of sequential scans descending. It helps identify which tables are scanned most often without using indexes.

sql

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Output

 relname  | seq_scan | idx_scan 
----------+----------+----------
 customers|      150 |      300 
 orders   |      120 |      450 
 products |       80 |      200 
(3 rows)

Common Pitfalls

• Forgetting that pg_stat_user_tables only tracks user tables, not system tables.
• Expecting real-time data: statistics are updated by the autovacuum process and may lag behind actual activity.
• Not having the right permissions: you need SELECT rights on the view, usually granted to all users.
• Confusing pg_stat_user_tables with pg_stat_all_tables which includes system tables.

sql

/* Wrong: expecting system tables stats here */
SELECT * FROM pg_stat_user_tables WHERE schemaname = 'pg_catalog';

/* Right: use pg_stat_all_tables for system tables */
SELECT * FROM pg_stat_all_tables WHERE schemaname = 'pg_catalog';

Quick Reference

Column	Description
relid	Object ID of the table
schemaname	Name of the schema containing the table
relname	Name of the table
seq_scan	Number of sequential scans initiated on this table
seq_tup_read	Number of live rows fetched by sequential scans
idx_scan	Number of index scans initiated on this table
idx_tup_fetch	Number of live rows fetched by index scans
n_tup_ins	Number of rows inserted
n_tup_upd	Number of rows updated
n_tup_del	Number of rows deleted
n_tup_hot_upd	Number of rows HOT updated (Heap-Only Tuple)
n_live_tup	Estimated number of live rows
n_dead_tup	Estimated number of dead rows
vacuum_count	Number of times this table has been manually vacuumed
autovacuum_count	Number of times this table has been vacuumed by autovacuum
analyze_count	Number of times this table has been manually analyzed
autoanalyze_count	Number of times this table has been analyzed by autovacuum

Key Takeaways

Query pg_stat_user_tables like a normal table to get user table statistics.

Use columns like seq_scan and idx_scan to understand table scan patterns.

Statistics are updated periodically, not in real-time.

pg_stat_user_tables only includes user tables, not system tables.

Check permissions if you cannot access the view.