How to use pg_stat_statements to monitor the performance of VACUUM and ANALYZE operations?

pg_stat_statements is a PostgreSQL extension that provides a detailed view of the SQL statements executed by the database server. It can be used to monitor the performance of VACUUM and ANALYZE operations by tracking the execution time and resource usage of these operations.

Here are the steps to use pg_stat_statements to monitor VACUUM and ANALYZE operations:

  1. Install and enable pg_stat_statements if it is not already installed. This can be done by running the following commands:

2. Configure pg_stat_statements to track VACUUM and ANALYZE operations by adding the following lines to your postgresql.conf file:

These settings will track the execution time, resource usage of VACUUM and ANALYZE operations, and other utility statements.

  1. Restart your PostgreSQL server to apply the configuration changes.
  2. Use the following SQL query to retrieve statistics on VACUUM and ANALYZE operations:

SELECT query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written FROM pg_stat_statements WHERE query LIKE ‘VACUUM%’ OR query LIKE ‘ANALYZE%’;

This query will return a list of all VACUUM and ANALYZE operations executed on the server, along with information on the number of times each operation was executed, the total execution time, the number of rows affected, and the amount of shared and local memory used.

By monitoring VACUUM and ANALYZE operations in this way, you can identify any performance issues or inefficiencies in these operations and take steps to optimize them. This can help to improve the overall performance and reliability of your PostgreSQL database.

About Shiv Iyer 157 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.