Enabling pg_stat_statements

We use the pg_stat_statements extension (officially bundled with PostgreSQL) for tracking which queries get executed in your database.

The query information collected by the extension is cleaned and then sent to our servers using our collector script.

The extension's normalization code cleans the actual values from the queries,
turning SELECT * FROM t WHERE secret = '123' into SELECT * FROM t WHERE secret = ?.

We still recommend that you use prepared statements if you query for highly sensitive data.

Note: We never collect any stored data, or data returned by queries.

Install official PostgreSQL contrib package

On your database server, make sure that the extensions package is installed.

Debian/Ubuntu:

Debian/Ubuntu includes pg_stat_statements in the main Postgres package, therefore no additional install is required.

RedHat/CentOS:

sudo yum install postgresql-contrib

Configure PostgreSQL

There are a number of settings that define how pg_stat_statements will collect statistics. The right configuration will depend on your server, workload, and preferences, but the following is the example of what to modify. You can apply these changes by updating your postgresql.conf file.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

The pg_stat_statements_info view

Queries that have not been seen recently will be pruned from pg_stat_statements once pg_stat_statements.max is hit. The pruning happens in batches, so you may see the count lower than the max, even if the max has been reached. The dealloc column of the pg_stat_statements_info view tracks the number of pruned queries. This is useful to check if the pg_stat_statements.max should be increased.

The pg_stat_statements_info view, introduced in PostgreSQL 14, tracks the statistics of pg_stat_statements. In addition to deallocation information, this view also tracks when the pg_stat_statements_reset() function was last called to reset the statistics.

Restart the PostgreSQL daemon

If you change shared_preload_libraries or pg_stat_statements.max above, a restart is required:

sudo systemctl restart postgresql