Improve pg_stat_statements by making jumble handle savepoint names better
Greg Sabino Mullane <htamfids@gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-07-24T20:09:23Z
Lists: pgsql-hackers
Attachments
- savepoint_jumble.v1.patch (application/octet-stream) patch v1
Please find attached a patch to jumble savepoint name, to prevent certain
transactional commands from filling up pg_stat_statements.
This has been a problem with some busy systems that use django, which likes
to wrap everything in uniquely named savepoints. Soon, over 50% of
your pg_stat_statements buffer is filled with savepoint stuff, pushing out
the more useful queries. As each query is unique, it looks like this:
postgres=# select calls, query, queryid from pg_stat_statements where query
~ 'save|release|rollback' order by 2;
calls | query | queryid
-------+----------------------------------------------+----------------------
1 | release b900150983cd24fb0d6963f7d28e17f7 | 8797482500264589878
1 | release ed9407630eb1000c0f6b63842defa7de | -9206510099095862114
1 | rollback | -2049453941623996126
1 | rollback to c900150983cd24fb0d6963f7d28e17f7 | -5335832667999552746
1 | savepoint b900150983cd24fb0d6963f7d28e17f7 | -1888817254996647181
1 | savepoint c47bce5c74f589f4867dbd57e9ca9f80 | 355123032993044571
1 | savepoint c900150983cd24fb0d6963f7d28e17f7 | -5921314469994822125
1 | savepoint d8f8e0260c64418510cefb2b06eee5cd | -981090856656063578
1 | savepoint ed9407630eb1000c0f6b63842defa7de | -25952890433218603
As the actual name of the savepoint is not particularly useful, the patch
will basically ignore the savepoint name and allow things to be collapsed:
calls | query | queryid
-------+----------------+----------------------
2 | release $1 | -7998168840889089775
1 | rollback | 3749380189022910195
1 | rollback to $1 | -1816677871228308673
5 | savepoint $1 | 6160699978368237767
Without the patch, the only solution is to keep raising
pg_stat_statements.max to larger and larger values to compensate for the
pollution of the
statement pool.
Cheers,
Greg
Commits
-
Show savepoint names as constants in pg_stat_statements
- 31de7e60da34 17.0 landed