0

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!)

Improve this question
4
  • You have elided the file name from the message, which might carry useful information.
    – jjanes
    Jun 6 at 12:59
  • filename was "pg_tblspc/59625/PG_13_202007201/59627/635175.15"
    – ConanTheGerbil
    Jun 6 at 13:57
  • When you mention the 1GB size of the table, are you including indexes? Do the tables have the same indexes?
    – jjanes
    Jun 6 at 17:28
  • 1
    Is pg_wal on the same partition as the tablespace data is? If so, that can make it hard to predict how much space you will need, and it would depend on things like your archiving, replication, and checkpointing configurations.
    – jjanes
    Jun 6 at 17:32

0

Reset to default

Your Answer

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