While the PostgreSQL statistics collector is rich and mature, having to monitor connection activity can be a little tricky since the pg_stat_activity catalog does not provide historic information. For example, as we explained previously, there will be a single tuple with the last executed statement, so no history nor extended details will be provided.
The pg_stat_statements extension solves this problem by providing a single view that gives you a full history of executed statements, timing, and other little details that can come in very handy when doing introspection. Moreover, pg_stat_statements provides a count of how many times the same statement has been executed, resulting in important information that queries might need to pay attention to for optimization purposes.
In the following subsections, you will learn how to install this extension and use it.
Installing the pg_stat_statements extension
This extension is shipped with PostgreSQL...
