How to Use PostgreSQL Statistics Parameters for Powerful Database Monitoring

PostgreSQL provides a rich set of statistics tracking parameters that allow Database Administrators (DBAs) to monitor live activity, analyze query behavior, tune performance, and maintain database health. These parameters control how PostgreSQL collects runtime information and exposes it through system views such as pg_stat_activity, pg_stat_database, pg_stat_user_tables, pg_stat_user_indexes, and pg_stat_user_functions etc

In this blog, we will explore each important statistics-related configuration parameter, its purpose, how it is useful for DBAs, and real SQL query examples to show how these parameters affect PostgreSQL system views in practice.

How to Enable Statistics Parameters

All these parameters are set in postgresql.conf.

You can see the path of this postgres conf file by the below command.

show config_file;

You get a result like this

               config_file               
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

Open this file and go to the section of statistics

Example configuration:

#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
# - Cumulative Query and Index Statistics -
#track_activities = on
#track_activity_query_size = 1024       # (change requires restart)
#track_counts = on
#track_cost_delay_timing = off
#track_io_timing = off
#track_wal_io_timing = off
#track_functions = none                 # none, pl, all
#stats_fetch_consistency = cache        # cache, none, snapshot

Let’s change the default values of the statistics related parameters in the postgres.conf file

track_activities = on
track_activity_query_size = 2048
track_counts = on
track_cost_delay_timing = off
track_io_timing = on
track_wal_io_timing = on
track_functions = pl
stats_fetch_consistency = cache

After changing parameters that require restart, restart PostgreSQL:

sudo systemctl restart postgresql

Now let us explore each parameter in detail.

1. How to Use track_activities for Live Query Monitoring

Purpose

track_activities enables PostgreSQL to record currently executing SQL queries.

track_activities = on

Why It Is Useful for DBAs

• Helps identify long-running queries
• Detects blocking and lockedsessions
• Essential for real-time monitoring tools
• Required for debugging production issues

System View Affected

• pg_stat_activity

Practical Example

SELECT pid,
       usename,
       application_name,
       state,
       query,
       query_start
FROM pg_stat_activity
WHERE state = 'active';

This query shows:

• Which users are running queries
• What SQL is being executed
• For how long the query has been running

If track_activities is disabled, the query column becomes empty.

2. How to Control Query Storage with track_activity_query_size

Purpose

Defines how many bytes of a SQL query are stored in memory.

track_activity_query_size = 2048

Why It Is Useful for DBAs

• Allows full visibility of large queries
• Helps in debugging complex joins and subqueries
• Prevents query truncation in logs and monitoring tools

System View Affected

• pg_stat_activity.query

Practical Example

SELECT pid, query
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

If this value is too small, long queries appear truncated. Increasing this value helps DBAs see the complete SQL.

3. How track_counts Enables Table and Index Monitoring

Purpose

Enables collection of table-level and index-level usage statistics.

track_counts = on

Why It Is Useful for DBAs

• Drives the autovacuum system
• Tracks dead tuples and row activity
• Helps identify unused indexes
• Improves query planner accuracy

System Views Affected

• pg_stat_user_tables
• pg_stat_user_indexes
• pg_stat_all_tables
• pg_stat_all_indexes

Practical Example – Table Activity

SELECT relname,
       seq_scan,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del
FROM pg_stat_user_tables;

This shows:

• How many inserts, updates, and deletes happened
• Whether queries are using indexes or sequential scans

Practical Example – Identifying Unused Indexes

SELECT relname,
       indexrelname,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

This helps DBAs identify indexes that are never used and can be dropped.

If track_counts is turned off:

• Autovacuum becomes ineffective
• All the above system views show zero statistics

4. How track_cost_delay_timing Helps in Planner Research

Purpose

Tracks time spent in cost-based statement delays.

track_cost_delay_timing = off

Why It Is Useful for DBAs

• Mainly used for:
  • Internal PostgreSQL testing
  • Planner research and experimentation
  • Not required for day-to-day production use

This parameter does not directly affect common system views used by DBAs.

5. How to Analyze Disk Bottlenecks Using track_io_timing

Purpose

Measures exact time spent on disk read and write operations.

track_io_timing = on

Why It Is Useful for DBAs

• Identifies I/O bottlenecks
• Distinguishes CPU-bound queries from disk-bound queries
• Helps tune:
  • Storage
  • Caching
  • Query design

System Views Affected

• pg_stat_database
• pg_stat_statements (if extension is enabled)

Practical Example – Database-Level I/O Timing

SELECT datname,
       blks_read,
       blks_hit,
       blk_read_time,
       blk_write_time
FROM pg_stat_database;

• blk_read_time shows how long PostgreSQL waited for disk reads
• blk_write_time shows how long PostgreSQL waited for writes

If track_io_timing is disabled, both timing columns will always show 0.

6. How to Monitor WAL Performance Using track_wal_io_timing

Purpose

Tracks time spent on WAL read and write operations.

track_wal_io_timing = on

Why It Is Useful for DBAs

• Crucial for:
  • High-write workloads
  • Replication monitoring
  • Crash recovery analysis
• Helps detect slow disks affecting WAL writes

System View Affected

• pg_stat_wal

Practical Example

SELECT * FROM pg_stat_wal;

This query shows:

• How much WAL is generated
• How much bytes and the time defining the reset of statistics also

7. How to Track Stored Procedure Performance Using track_functions

Purpose

Tracks execution statistics of SQL and PL/pgSQL functions.

track_functions = pl

Why It Is Useful for DBAs

• Detects slow stored procedures
• Measures function call frequency
• Helps optimize business logic in the database

System View Affected

• pg_stat_user_functions

Practical Example

SELECT funcname,
       calls,
       total_time,
       self_time
FROM pg_stat_user_functions;

• calls shows how often the function is executed
• total_time shows total execution time
• self_time shows time spent inside the function itself

If track_functions is set to none, this view will always be empty.

8. How stats_fetch_consistency Controls Statistics Accuracy

Purpose

Defines how PostgreSQL fetches statistics when querying system views.

stats_fetch_consistency = cache

Modes Explained

• cache – fastest, slightly delayed statistics
• none – always fetches fresh statistics
• snapshot – fully transaction-consistent statistics

Why It Is Useful for DBAs

• cache is best for dashboards and real-time monitoring
• snapshot is useful for research and exact measurement
• none gives the most current values but adds overhead

This affects all statistics views such as:

• pg_stat_activity
• pg_stat_database
• pg_stat_user_tables

How DBAs Use These Parameters in Real Operations

DBAs rely on these parameters for:

1. Live production troubleshooting using pg_stat_activity
2. Autovacuum health monitoring using pg_stat_user_tables
3. Disk performance analysis using pg_stat_database
4. WAL and replication tuning using pg_stat_wal
5. Stored procedure optimization using pg_stat_user_functions
6. Index usage audits using pg_stat_user_indexes

These parameters together form the foundation of PostgreSQL monitoring and tuning.

Recommended Production Configuration

track_activities = on
track_activity_query_size = 2048
track_counts = on
track_cost_delay_timing = off
track_io_timing = on
track_wal_io_timing = on
track_functions = pl
stats_fetch_consistency = cache

This setup provides:

• Maximum visibility
• Accurate performance metrics
• Safe production-level overhead
• Full support for monitoring tools

Conclusion

PostgreSQL statistics parameters are the eyes and ears of a DBA. They reveal what is happening inside the database at every level — from live queries and table activity to disk I/O, WAL behavior, and stored procedure performance. By correctly configuring and querying these parameters, DBAs gain complete control over performance tuning, troubleshooting, capacity planning, and system reliability.

When used properly, these statistics views become a powerful real-time monitoring system built directly into PostgreSQL without relying on any third-party tools.