Checking WAL size generated for a particular query

This article explains how to monitor WAL size generated per SQL query using pg_stat_statements, providing an example.

Excessive WAL generation can lead to increased I/O overhead, storage increase, and replication lag, affecting overall system performance. By leveraging pg_stat_statements, which tracks execution statistics of SQL statements, database administrators can gain insights into which queries are contributing most to WAL generation. This enables proactive optimization, ensuring that the database operates smoothly and efficiently, minimizing downtime and maintaining high availability.

If you are using PostgreSQL/EPAS version 13 or higher, you can enable pg_stat_statements to monitor amount of WALs generated by the statement in bytes.

You can follow the below steps to enable this extension:

1. Add pg_stat_statements to shared_preload_libraries parameter in postgresql.conf file.

shared_preload_libraries = 'pg_stat_statements'

Note that this parameter accepts comma separated values.

2. Restart the database after the parameter change.

3. Create pg_stat_statements extension in the required database using the below statements:

CREATE EXTENSION pg_stat_statements;

Here is an example:

edb=# CREATE TABLE test (a int, b char(20));
CREATE TABLE
edb=# INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
INSERT 0 10
edb=# UPDATE test SET b = 'bbb' WHERE a > 7;
UPDATE 3
edb=# DELETE FROM test WHERE a > 9;
DELETE 1

edb=# SELECT pg_get_userbyid(userid), query, calls, rows, wal_bytes, wal_records FROM pg_stat_statements WHERE wal_bytes > 0 AND pg_get_userbyid(userid)='enterprisedb' ORDER BY query;
 pg_get_userbyid |                        query                         | calls | rows | wal_bytes | wal_records
-----------------+------------------------------------------------------+-------+------+-----------+-------------
 enterprisedb    | CREATE TABLE test (a int, b char(20))                |     1 |    0 |      4565 |          45
 enterprisedb    | DELETE FROM test WHERE a > $1                        |     1 |    1 |        54 |           1
 enterprisedb    | INSERT INTO test VALUES(generate_series($1, $2), $3) |     1 |   10 |       800 |          10
 enterprisedb    | UPDATE test SET b = $1 WHERE a > $2                  |     1 |    3 |       219 |           3
(4 rows)

From the above output, you can see wal_bytes shows the amount of WAL data generated for each query by particular database user.

Monitoring WAL generation using pg_stat_statements is essential for maintaining optimal PostgreSQL performance. By identifying and optimizing high-impact queries, administrators can reduce I/O overhead, prevent storage issues, and ensure the database remains efficient and reliable. This proactive approach enhances overall system stability and supports sustained high availability.