0

I have 2 identical tables for 2 countries: ES and NL. In table_es I have 477k rows, in table_nl 1.600M rows, but after checking size of both tables I have 26 GB in ES and 24 GB in NL.

How is it possible that table_es is using that amount of space?

In both tables indexes are only on primary keys. Database encoding is UTF-8. I sum length of the text fields in both tables and for example description in table_es takes 0.5 GB (477k rows) and 3.99 GB for table_nl (1.600M rows) which is what I am expecting.

I checked the amount of dead_tuples in both tables, but for both is 0. Autovacuum is on.

Everyday during the night 50 - 500k rows are deleted from both tables and similar amount for rows are inserted.

Does somebody have an idea what is going on?

1

Either the rows in the table with fewer rows are bigger, or the table contains more empty space.

Use the pgstattuple function from the extension with the same name to find out for sure.

You can reclaim free space on a table with VACUUM (FULL), but that will block all concurrent access to the table while it is being rewritten.

6
  • Wow! I get 27333997224 (bytes?) of free space for table_es and 24885971088 for table_nl. It's some around 95% of total size. What it comes from? Reclaiming free space with VACUUM is safety? (skipping part which you mentioned about access)
    – Bob
    Nov 18 at 13:46
  • It is safe, but down time. The cause is lost in the past. Nov 18 at 13:55
  • How much time it's can be taken approximetely? And if cause is in the past so in future it's can be a similar problem?
    – Bob
    Nov 18 at 14:08
  • I cannot estimate the time. And yes, it may happen again unless you fix the problem. Monitor disk space! Nov 18 at 15:08
  • Thank you very much for help Laurenz! I did 'vacuum full' for all my tables with data and my whole db size was decrease from 230GB to 120GB... And queries working now faster and general perforamnce are better! I'm very glad to you. Which problem you have in mind? What it comes from generally?
    – Bob
    Nov 23 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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