PostgreSQL 12 High Availability Cookbook - Third Edition

Using pgstattuple

The pgstattuple extension is also useful for analysis by performing a deep scan of any supplied database object. It's best to use the extension to retrieve storage-related data regarding indexes or tables matched with the preceding queries. Since it interacts with the server filesystem, only superusers are allowed to install or invoke the function provided by this extension.

Execute this SQL query to install pgstattuple:

CREATE EXTENSION pgstattuple; 

To use the extension, select from it as if it were a normal table or view. The only difference is that we use it as a function with the name of the table we want to analyze. For example, to obtain storage statistics on the pg_class table, we could execute this:

SELECT * FROM ...