Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?
share|improve this question
up vote 4 down vote accepted

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).

share|improve this answer
I'm also pretty sure you'll find some of the missing MB in the matching TOAST-tables. – DrColossos Apr 5 '11 at 17:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.