PostgreSQL – How to Reclaim Disk Space After Delete Without Rebuilding Table

disk-spacepostgresqlpostgresql-9.0vacuumwindows

Our PostgreSQL 9.0 Windows production server is running low on space.

In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the space to the O/S.

We do not have enough space to do a full rewrite of the table, and all my reading of CLUSTER, VACUUM FULL and pg_repack is that they need to rewrite the table. My google-fu has been insufficient so far to find anything else.

Some downtime is acceptable (~2 hours), however the backup/restoration is too slow for our purposes, nor am I keen on dropping the database in between steps.

QUESTION: How can I return the disk space to the O/S without doing a full table rewrite?

(This is a production server so any solutions must be reputable/recommended/supported etc).

(P.S. A separate much larger disk is available which could be used, if the table could be rebuilt on it somehow. Tablespaces?)

Best Answer

If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes.

TABLESPACE

Yes, your last idea could work. Create a new tablespace on the other disk.

CREATE TABLESPACE ts1 LOCATION '/data/disk2';

Then create an optimized copy of the table in the new tablespace:

CREATE TABLE new_tbl 
TABLESPACE ts1 AS
SELECT * FROM tbl
ORDER BY ....  -- ORDER BY is optional

The copy will be packed tightly without dead rows.

Then you might just drop the old table and rename the new one to keep using the new disk.

Or, if you have to keep it on the old disk for some reason, delete the old table, rename the new one and move it back to the default tablespace. This step is much faster now with:

ALTER TABLE tbl SET TABLESPACE pg_default

Per documentation:

This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. See also CREATE TABLESPACE.

Either way, don't forget to (re)create all depending objects. Indexes, foreign keys, views, ...

Well, you could use ALTER TABLE tbl SET TABLESPACE ... to begin with, but then the table would not be optimized as requested, just moved as is. But you would have enough wiggle room then to run pg_repack etc.

COPY

A complete backup / restore might take too long, but you could do that for only the table in question.

COPY tbl TO '/path/to/other/disk/tbl.pgsql';
TRUNCATE tbl;
COPY tbl FROM '/path/to/other/disk/tbl.pgsql';

The table is now packed tightly.

Temporary table

If you should happen to have enough RAM, you could do something similar with a temporary table in RAM. Would be much faster. Detailed instructions:

Related Question