Code for Concinnity

The problem

Simply DELETEing rows often does not reclaim disk space in PostgreSQL without doing a VACUUM FULL. Unfortunately that locks up the table which often means the whole app being offline for unforeseeable duration.

The quick and dirty solution

begin;

-- Instead of deleting rows you don't need, keep the rows you need
-- This is often quite fast if you keep only a small percentage of data
select * into request_logs_backup from request_logs where id > 76667000;
-- Truncate the table. This is usually instantaneous
truncate request_logs;
-- Copy the data back
insert into request_logs select * from request_logs_backup;
drop table request_logs_backup;
commit;

The “usual” solutions

There are dedicated extensions like pg_repack, pg_squeeze. They require installation on the server, that’s why a quick and dirty solution is proposed here.

Why copy the data instead of renaming the table?

Apparently preserving the table structure in a new table can be tricky. The above solution is defensively safe.