pg_total_relation_size accuracy guarantee
Hello, I'am currently trying to implement some simple disc space limitation
mechanism for my database. Database is partition based and insert-only - I
insert new rows to the current partition and drop oldest partition when
disc space limit exceeded.
Algorithm is quite simple and it seems it works but I must ensure two
assumptions to avoid extra VACUUM/ANALYZE calls:
1. pg_total_relation_size return predictable approximately correct table
size (apart from some preallocation) without subsequent
VACUUM/ANALYZE/anything call as long as I don't DELETE/UPDATE rows;
2. DROP/TRUNCATE table reclaim disc space without subsequent VACUUM;
These assumptions are correct? Is it fixed in doc if so?
Nikita wrote:
1. pg_total_relation_size return predictable approximately correct table size (apart from some preallocation) without subsequent VACUUM/ANALYZE/anything call as long as I don't DELETE/UPDATE rows;
2. DROP/TRUNCATE table reclaim disc space without subsequent VACUUM;
These assumptions are correct? Is it fixed in doc if so?
Sort of, if you consider the source as documentation.
Ad pg_total_relation_size:
See the function definition in backend/utils/adt/dbsize.c and
the functions that it calls, especially "calculate_relation_size":
PostgreSQL uses the stat(2) system call to determine the actual
size of the files on disk.
Ad TRUNCATE (from backend/commands/tablecmds.c):
/*
* Need the full transaction-safe pushups.
*
* Create a new empty storage file for the relation, and assign it
* as the relfilenode value. The old storage file is scheduled for
* deletion at commit.
*/
Yours,
Laurenz Albe
Source is good enough in my case and I planned to grep it anyway, so thanks
for saving my time!
2017-11-24 10:15 GMT+03:00 Laurenz Albe <laurenz.albe@cybertec.at>:
Show quoted text
Nikita wrote:
1. pg_total_relation_size return predictable approximately correct table
size (apart from some preallocation) without subsequent
VACUUM/ANALYZE/anything call as long as I don't DELETE/UPDATE rows;2. DROP/TRUNCATE table reclaim disc space without subsequent VACUUM;
These assumptions are correct? Is it fixed in doc if so?
Sort of, if you consider the source as documentation.
Ad pg_total_relation_size:
See the function definition in backend/utils/adt/dbsize.c and
the functions that it calls, especially "calculate_relation_size":PostgreSQL uses the stat(2) system call to determine the actual
size of the files on disk.Ad TRUNCATE (from backend/commands/tablecmds.c):
/*
* Need the full transaction-safe pushups.
*
* Create a new empty storage file for the relation, and assign it
* as the relfilenode value. The old storage file is scheduled for
* deletion at commit.
*/Yours,
Laurenz Albe