After inspecting active queries, checking for I/O problems, and locking, it might be interesting to see which queries are actually causing most of the trouble. Without knowing the actual time-consuming queries, it is pretty hard to improve things in the long run. Fortunately, PostgreSQL provides a module called pg_stat_statements that is capable of tracking queries and providing administrators with statistical information about those queries.

To use this module, it has to be enabled:

test=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Note that the module has to be enabled inside a database. The system views created by this module will only live in this database for now.

However, we are not done yet:

test=# SELECT * FROM pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via
    shared_preload_libraries

The module is actually loaded when the postmaster starts up. It cannot be loaded on the fly because the information created must survive the disconnection...