Getting an overview of the problem

Before getting started, the most important thing is to get an overview of the problem. Assuming that there is no corruption around, the first thing to do is to take a detailed look at the content of pg_stat_activity. As already outlined in this book, pg_stat_activity contains a list of all open database connections. There are a couple of things to look out for here:

• Is the number of open database connections reasonable?

• Are there many idling transactions? Is there a certain query showing up again and again?

• Are there queries that are obviously running too long?

Many middleware components open an insane number of database connections. Remember that each useless connection takes away a bit of memory, which could have been used for something more productive, such as caching. However, the number of database connections alone does not usually impose a dramatic overhead. The number of active snapshots is way more dangerous because active snapshots directly translate...