Why does pg_stat_statement need to be included in shared_preload_libraries?

The question:

I am working on AWS RDS Postgres v9.6, and also v14. I noticed that in our shared_preload_libraries parameter, we have pg_stat_statement included. I don’t understand why that needs to be there.

In the Postgres docs, it says this:

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.

However, several other extensions are already built in – for example we use dblink and hstore. These do not need to be in shared_preload_libraries. Why does pg_stat_statement behave differently?

I think shedding light on this would help me (and hopefully others) better understand how built in libraries work, especially as regards the shared_preload_libraries parameter. The docs don’t seem to say enough on this topic, or else I don’t know where to find it.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

pg_stat_statements stores its data in a shared memory segment, and PostgreSQL only allows you to create shared memory segments when the server is started. So the extension has to be loaded at the time the server is started.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment