postgresql,

PostgreSQL 101: pg_table_size vs pg_relation_size vs pg_indexes_size vs pg_total_relation_size

Dec 08, 2022 · 2 mins read · Post a comment

Another good nugget to share as I was trying to find out which production DB table was bloated and why. The size was in GB, and it was ridiculous, something around 50GB. You can find the queryies here → Show database and table size in PostgreSQL.

The reason I wanted to write this post is that I made a great mistake not knowing the differences between pg_table_size, pg_relation_size and pg_total_relation_size as I was getting false results because of it. So, here are the differences.

Size and on-disk size are used interchangeably.

Prerequisites

  • PostgreSQL

Solution

pg_table_size: determines a table on-disk size, an index or a view size. The key point is you’ll get the table size excluding any indexes.

pg_relation_size: the size of the table or index data forks. Fork is a table or index storing the relation’s data. So, you got the main fork (main), freespace map (fsm), visibility map (vm) and initialization fork (init). Unfortunately, not the topic for this post to get deep into fork details, so feel free to search the internet.

pg_indexes_size: self-explanatory. The size of a table’s indexes.

pg_total_relation_size: the total size of a table including the data and indexes if any.

To sum up, you need to find the pg_total_relation_size in order to get the total table size. That being said:

pg_total_relation_size = pg_table_size + pg_indexes_size

the big picture

|-------------------------------------|
|             pg_table_size           |
|------------------------|------------|-----------------|
|    pg_relation_size    |            |                 |
|------------------------| toast_size | pg_indexes_size |
| main | fsm | vm | init |            |                 |
|------------------------|------------|-----------------|
                         |        external_size         |
                         |------------------------------|
|-------------------------------------------------------|
|                 pg_total_relation_size                |
|-------------------------------------------------------|

external_size: size of external elements such as: toast_size + pg_indexes_size.

toast_size: size of a TOAST table. TOAST is a feature, technique that compress and/or break large field values into smaller multiple chunks (physical rows). Keeps the physical data rows outgrowing the data block size with the default value of 8kB. First, compress the data to fit in the 8kB, if not, compress it into smaller blocks.

bonus

Here’s a SQL query to list all table sizes including indexes:

SELECT
   relname as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_relation_size(relid)) as "Relation Size (main fork only)",
   pg_size_pretty(pg_table_size(relid)) as "Table Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size"
   FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Note: The main fork is the default so, if you wish to list the rest of the data forks as mentioned above, either replace the following line: pg_size_pretty(pg_relation_size(relid)) as "Relation Size (main fork only)" with pg_size_pretty(pg_relation_size(relid, 'fsm')) as "Relation Size (fsm fork only)", or even add the rest of them. However, IMO the rest of the forks won’t bring any significant value.

Conclusion

Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.

postgresql