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
Expanded display is on.
test=# SELECT * FROM pgstattuple('pg_class'),
-[ RECORD 1 ]------+------
table_len          | 65536
tuple_count        | 305
tuple_len          | 56032
tuple_percent      | 85.5
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 5508
free_percent       | 8.4

What we see here is that pgstattuple returns the total size of the table, the number of rows inside the table, as well as their size. Then come two blocks; the dead_* columns provide us with information about the number and size of dead rows in the table. Dead rows can easily be turned into free space by running VACUUM.

The main challenge when using pgstattuple is to run it for many tables at the same time. It is always possible to run things for each table at a time, but this might be too time consuming. Running it for all tables at once is definitely more convenient.

To achieve that, we can turn to composite types. The pgstattuple is actually overloaded and not only can it be called with the name of a table, but it also works with object IDs. The main challenge now is to fetch the list of tables. A system table called pg_class can be used here. All tables are identified as r (relations) in the relkind column. However, the most important part is the decomposition of the data type returned by pgstattuple. Simple use of parentheses and an asterisk will work here:

test=# SELECT relname, (pgstattuple(oid)).* 
  FROM   pg_class 
  WHERE   relkind = 'r' 
  ORDER BY table_len DESC;
-[ RECORD 1 ]------+--------
relname            | pg_proc
table_len          | 557056
tuple_count        | 2697
tuple_len          | 525319
tuple_percent      | 94.3
dead_tuple_count   | 7
dead_tuple_len     | 1553
dead_tuple_percent | 0.28
free_space         | 8540
free_percent       | 1.53
...

A long list for each table in the system will be displayed.

The pgstattuple module is the perfect tool for detecting table bloat. However, use it with care. It has to read tables entirely. Therefore, using it too often is not a good idea because it causes heavy I/O (similar to a backup).

In addition to looking inside a table, it is also possible to inspect the PostgreSQL I/O cache (shared buffers area) to see what is cached and what is not. In many cases, knowing about the content of the I/O cache can reveal problems related to poor performance caused by too much disk activity.

The pg_buffercache view can be activated to inspect the cache:

test=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

Just as was done before, the module has to be activated in order to provide us with information. Again, a system view is provided. It contains all of the information needed:

test=# d pg_buffercache
     View "public.pg_buffercache"
     Column     |   Type   | Modifiers 
----------------+----------+-----------
 bufferid       | integer  | 
 relfilenode    | oid      | 
 reltablespace  | oid      | 
 reldatabase    | oid      | 
 relforknumber  | smallint | 
 relblocknumber | bigint   | 
 isdirty        | boolean  | 
 usagecount     | smallint | 

The main challenge with this special view is that it is a bit hard to read. Most fields are represented in a way that makes it pretty hard for normal users to comprehend what is actually shown. The following list outlines how those fields can be translated into something more readable:

With these SELECT statements, it will be possible to turn those numbers into readable names.

The next column is relforknumber (it is a cached block for the data file, Visibility Map, or Free Space Map), and finally, there is the number of the block in the cached table. This field allows you to see which areas of the table are in RAM and which are not.

The last two columns tell us whether the block is dirty and how often it is pinned. Given all of these fields, you can yourself create an analysis returning exactly the data that you need.

There are many more useful modules floating around on the web. However, the modules in this chapter are, according to my judgment, the most important modules. They are also shipped along with the PostgreSQL core distribution.