Code for Concinnity

Conditionally free up PostgreSQL table space without locking or extensions

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


-- 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;


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.

Published by kizzx2, on September 6th, 2021 at 3:33 pm. Filled under: UncategorizedNo Comments

No comments yet.

Leave a Reply