Inspecting internal information

Once the basics (locking, slow queries, and so on) are done, our attention can be turned to more sophisticated inspections.

Looking inside a table

As already described in this book, PostgreSQL uses a mechanism called Multi-Version Concurrency Control (MVCC). The beauty of this system is that more than one version of a row can exist at the same time, which is the perfect way to scale up the system. However, this also means that space on the disk might be wasted if too many versions of too many rows exist. This problem is generally called table bloat. If tables grow too large and inefficient, performance problems are likely to arise.

The pgstattuple module has been created to detect those problems. Before the module can be used, it has to be loaded:

test=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION

Basically, pgstattuple is easy to use. All you have to do is call the pgstattuple function and pass a table name as a parameter. No columns are returned:

test=# \x...