Infrastructure at your Service

Daniel Westermann

PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?

In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short summary: When you reach the maximum number of allowed statements to track, pg_stat_statements will de-allocate entries to free space for other statements. As de-allocations use resources as well and frequent de-allocations might also mean that pg_stat_statement is not configured the right way for your workload, this patch provides the following information: how many de-allocations happened.

In the default configuration pg_stat_statements tracks a maximum of 5000 statements:

postgres=# show pg_stat_statements.max;
 pg_stat_statements.max 
------------------------
 5000
(1 row)

The lowest value allowed for pg_stat_statements.max is 100:

postgres=# alter system set pg_stat_statements.max=5;
ERROR:  5 is outside the valid range for parameter "pg_stat_statements.max" (100 .. 2147483647)
postgres=# alter system set pg_stat_statements.max=100;
ALTER SYSTEM

As this change requires a restart it should be set carefully:

postgres=# select context from pg_settings where name = 'pg_stat_statements.max';
  context   
------------
 postmaster
(1 row)

postgres=# \! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-20 01:00:54.656 CET - 1 - 102610 -  - @ LOG:  redirecting log output to logging collector process
2020-11-20 01:00:54.656 CET - 2 - 102610 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# show pg_stat_statements.max;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# show pg_stat_statements.max;
 pg_stat_statements.max 
------------------------
 100
(1 row)

Lets generate some random statements and the check what additional information we get with this patch:

postgres=# select 'create table t'||i||'( a int, b int, c int)' from generate_series(1,101) i; \gexec
                ?column?                 
-----------------------------------------
 create table t1( a int, b int, c int)
 create table t2( a int, b int, c int)
 create table t3( a int, b int, c int)
 create table t4( a int, b int, c int)
 create table t5( a int, b int, c int)
 create table t6( a int, b int, c int)
 create table t7( a int, b int, c int)
 create table t8( a int, b int, c int)
 create table t9( a int, b int, c int)
...
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=# 

This created 101 tables and should have forced pg_stat_statements to de-allocate some older statements. There is a new, very simple view which provides this information:

postgres=# select * from pg_stat_statements_info;
 dealloc 
---------
       3
(1 row)

Nice. This will give you an idea if pg_stat_statements.max is not properly configured for your environment. If you manually re-set pg_statements the de-allocation counter will also be reset:

postgres=# select * from pg_stat_statements_info;
 dealloc 
---------
       3
(1 row)

postgres=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)

postgres=# select * from pg_stat_statements_info;
 dealloc 
---------
       0
(1 row)

Update 2020-Nov-17: This was finally committed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure