1

I couldn't see any growth in table size down to a single byte when I added 1 million rows in the table during the last days. I'm using this query:

select
   table_name,
   pg_relation_size(quote_ident(table_name)) as data_size
from 
   information_schema.tables
where table_schema = SOME_SCHEMA
and table_name = SOME_TABLE;

And it returns the same result all these days.
What could cause this unexpected result?

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    May 22, 2023 at 20:36

1 Answer 1

1

If the table has been holding a sufficient amount of dead tuples (that have already been marked dead and added to the "free space map"), then newly added rows can reuse that physical space, without growing the result of pg_relation_size(). Note that this function ... (quoting the manual):

Computes the disk space used by one “fork” of the specified relation.

"main" data fork by default. That includes bloat which can be reused.

You'd never see the same immediately after a VACUUM FULL, which compacts the physical table, removing all bloat. Not saying you should run VACUUM FULL. Most of the time, you shouldn't. Like in your case: its cheaper overall to just reuse the space instead of shrinking and growing the physical file, which are expensive operations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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