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. It only takes a minute to sign up.
Sign up to join this community
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?
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.
