PostgreSQL is not just a powerful open-source database — it’s a deeply introspective system. Beneath the surface, PostgreSQL constantly collects and stores internal performance metrics through its system catalog tables, most of which begin with the prefix pg_stat.
These views serve as a window into PostgreSQL’s core operations — query execution, I/O, replication, function usage, and even real-time progress tracking.
In this blog, we’ll explore 10 essential pg_stat system catalog views, detailing what they do, when to use them, and how to extract actionable insights from each.
1. pg_stat_activity
Purpose:
Monitors real-time activity for each active session or connection to the database.
Best Use Case:
* Detect long-running or idle queries.
* Monitor query states during heavy load or debugging.
Example Query:
SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
2. pg_stat_user_tables
Purpose:
Tracks access patterns and write operations on user-defined tables.
Best Use Case:
Identify the most accessed or updated tables.
Determine whether sequential scans are hurting performance.
Example Query:
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
3. pg_stat_user_indexes
Purpose:
Provides statistics about user-defined indexes — how often they're used or scanned.
Best Use Case:
Detect unused or overused indexes.
Tune index usage or identify the need for new indexes.
Example Query:
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
4. pg_stat_statements (Extension Required)
Purpose:
Collects query-level performance metrics: execution time, call count, I/O, etc.
Best Use Case:
Identify slow or most frequent queries.
Optimize expensive query patterns.
Example Query:
SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
5. pg_stat_user_functions
Purpose:
Tracks usage and execution time of user-defined functions.
Best Use Case:
Spot inefficient or overused PL/pgSQL functions.
Optimize function bottlenecks.
Example Query:
SELECT funcname, calls, total_time, self_time
FROM pg_stat_user_functions
ORDER BY total_time DESC
LIMIT 5;
6. pg_stat_progress_vacuum
Purpose:
Shows real-time progress of ongoing VACUUM operations.
Best Use Case:
Monitor long-running vacuum tasks on large tables.
Useful during maintenance or heavy loads.
Example Query:
SELECT pid, relid::regclass AS table_name, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;
7. pg_stat_progress_create_index
Purpose:
Displays live stats about CREATE INDEX operations in progress.
Best Use Case:
Track the duration of large index builds.
Troubleshoot slow migrations or schema changes.
Example Query:
SELECT pid, datname, relid::regclass AS table, command, phase, lockers_total
FROM pg_stat_progress_create_index;
8. pg_stat_wal
Purpose:
Provides statistics about the Write-Ahead Logging (WAL) mechanism.
Best Use Case:
Monitor WAL generation rates and checkpoint activity.
Tune WAL settings for replication and bulk inserts.
Example Query:
SELECT wal_records, wal_fpi, wal_bytes
FROM pg_stat_wal;
9. pg_stat_replication
Purpose:
Gives insights into the status of connected standby replicas.
Best Use Case:
Monitor replication lag.
Troubleshoot issues with streaming replication.
Example Query:
SELECT pid, usename, client_addr, state, sync_state, write_lag, flush_lag
FROM pg_stat_replication;
10. pg_statistic & pg_stats
Purpose:
Stores statistics gathered by ANALYZE, used by the planner for cost estimation.
Best Use Case:
Investigate poor query plans due to inaccurate statistics.
Understand column distribution, distinct values, and histograms.
Example Queries:
pg_stats (Readable view):
SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'your_table_name';
pg_statistic (Raw internal view):
SELECT * FROM pg_statistic
WHERE starelid = 'your_table_name'::regclass;
Conclusion
PostgreSQL’s pg_stat catalog views offer a powerful window into the internal workings of your database. Far beyond simple metadata, these views provide real-time insights into query execution, table access patterns, function usage, replication status, and background operations like vacuum and indexing. Whether you're managing a production server, debugging performance bottlenecks, or planning infrastructure changes, these system views act as essential diagnostics tools for database administrators and developers alike.
By tapping into these statistics, you gain the ability to make data-driven performance decisions. You can detect slow-running queries, understand index utilization, measure WAL traffic, track replication lag, and monitor vacuum progress—all from within SQL itself. This visibility not only helps you tune and optimize your database efficiently but also minimizes guesswork during troubleshooting and reduces downtime risks.
Incorporating these views into your regular monitoring and audit routines ensures that your PostgreSQL environment stays healthy, responsive, and scalable. As your application grows in complexity and volume, mastering these internal catalogs will empower you to build a more reliable, performance-conscious system architecture. Master concurrency in PostgreSQL. Learn how to use advisory locks to prevent data corruption and safely manage transactions for complex application logic.