PostgreSQL relation size does not sum up

database-sizepostgresqlsize;

I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:

=> SELECT pg_size_pretty(pg_total_relation_size('data'));
157 GB

This seemed a bit large, so I tried to sum up the data:

=> SELECT pg_size_pretty(pg_relation_size('data'));
19 GB

With the indices:

SELECT pg_size_pretty(pg_relation_size('data_pkey'));
757 MB   
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB   
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_merged_idx'));
854 MB
SELECT pg_size_pretty(pg_relation_size('data_owner_idx'));
794 MB
SELECT pg_size_pretty(pg_relation_size('data_session_format_idx'));
779 MB

The summation of the data and indices size is around 26 GB, but the total relation size is near 160 GB. The table was restored from dump just a while ago, and no writes were made since.

  • What's the explanation for this difference?
  • Any way to reduce wasted disk space? Will clustering help?

Best Answer

Does table have any variable-width columns? If the answer is yes, you are probably missed toast tables (pg_toast_xxx). You need to find their names (not sure how to do it, need to google it) and calculate their size as well.

To reduce space (for example after deleting bunch of rows) execlute VACUUM FULL and reindex table after it (to prevent index fragmentation).

Related Question