PostgreSQL - why does moving a partition require so much space?

Question

I have a postgres database with tablespaces spread over two disks. (one SSD disc for current data, one magnetic drive for older data)

Occasionally I move table partitions that contain 'old' data to the tablespace on the backup drive using....

ALTER TABLE..... SET TABLESPACE....

Sometimes the command will fail after a few minutes with a space warning. The source table may be (say) 30Gbytes, the disk the table is on may be down to it's last 5Gbytes of free space (according to windows) and the destination tablespace might have several hundred Gbytes free.

Questions

1. Why is freespace required on the source disk - i.e. the disk the data is already on?
2. Is there anyway of reducing (or removing) the requirement for freespace?
3. Is there anyway of calculating how much free space is going to be required to allow the SET TABLESPACE command to work?

• Laurenz Albe: Please add the exact error message. Perhaps you are running out of WAL space.

Answer 1

Unless your wal_level is set to 'minimal', the entire table has to be written to the WAL stream when you move it to a new tablespace. This could easily run you out of space if pg_wal is on the same disk/partition as the old tablespace is.

• ConanTheGerbil: thanks for that, so I guess if the table partition is 30GByte I need 30Gbyte free space before attempting the move?
• jjanes: Not necessarily. The WAL could get recycled fast enough so not all of it would need to be stored simultaneously, depending on your settings about checkpoints and WAL management. Unfortunately there is no way to throttle the WAL generation, so it can be hard to make sure of this.