I have some large sets of tables that I partition by date.
Every day I run a query similar to this to merge the two tables
WITH moved_rows AS ( DELETE FROM tableA_20230531 RETURNING * ) INSERT INTO tableA_202305 SELECT * FROM moved_rows
The table called
tableA_20230531 contains all my data for 1 day - averages about 1Gbyte. The table
tableA_202305 contains the data for the month so far, obviously this gets bigger as the month progresses.
Sometimes the query falls due to lack of space (ERROR: 53100: could not extend file.... No space left on device ).
I know how large the two tables are, and I know how much free space I have on the disk.
Is there any way of working out (before running) if I have enough space for the query to execute successfully?
Somedays it will happily work even if the available freespace is less than the size of the daily table. Otherdays it will fail even with freespace more than twice the size of the daily table.
(no other users connected when this is running, whole database on same disk, one index, about 10% of the table size - an additional 100Mbyte per day.
Archiving, replication and checkpointing all left to original, as-installed default settings - that's my of saying I don't know!)