How to Free PostgreSQL Space After DELETE Without VACUUM

postgresql

Is there anyway of freeing up disk space after a major delete without using VACUUM?

When I was learning about postgres I created a very large (unpartitioned) table that has since swollen to nearly 1 TByte.

To rectify this I decided I needed to partition the table by month.

I created a table for one months data and populated it with this….

WITH archived_rows AS
( DELETE FROM tabArc WHERE fixTime >= DATE '2018-02-01' AND fixTime < 
DATE '2018-03-01' RETURNING * )
INSERT INTO tabArc_201802 SELECT * FROM archived_rows;

This worked fine, the new table has a months worth of data, the old table has a months less. The problem is that the disk it was on has now swollen to about 90% full, so there no room to do a similar process with a second months worth of data.

The reason isn't complicated – the big table hasn't freed up any space.

The catch is that I've never managed to do a VACUUM on this table because I simply run out of time, I've let the process run for over 24 hours and it hasn't completed.

Is there any other way to free up space? or to speed up the VACCUM process? I can't continue to partition the data until I have some more space!

Best Answer

You are in a tough spot. You will probably need to schedule some down time and get some more disk space to act as scratch space. VACUUM might not free up any space anyway because it can only free up contiguous chunks of completely empty pages at the physical end of the table. With downtime, the fastest solution may be to copy the data out of the table with \copy, perhaps compressing as it goes so that it all fits on your existing storage. Then drop the table and re-import it into the individual partitions. But note that partitioning the data won't make it smaller, unless there is currently lots of bloat. So it doesn't solve the fundamental problem. If you want to keep a 1TB of data around, you need to have enough storage to do that.

If you have the luxury of patience, you can try to use pg_repack to squeeze the space out of the table while it is still live.

or to speed up the VACCUM process?

The default settings for manual VACUUM are for it not to be IO throttled. That is because the default setting for vacuum_cost_delay is zero. Make sure you haven't changed that. Make maintenance_work_mem be 1GB if you have enough RAM to support that. Drop any indexes on the table you can get away with dropping. Use system tools (e.g. top, vmstat, perf, strace) to figure out where the bottlenecks in your VACUUM are.

Related Question