Thread: 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
subsequentVACUUM/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  *
asthe 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 : > 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 >