pg_repack — full vacuum without table lock
--
In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. If you have application that performs a lot of UPDATE/DELETE operations your database can grow in size pretty quickly.
At IS Engineering we develop plant performance monitoring applications which collect hundreds (sometimes thousands) of data points ever minute. This “minute-by-minute” data is later compressed into hourly slots. This process involves quite a few DELETE operations every hour.
To reclaim disk space used by deleted records you need FULL VACUUM which locks tables, huge “no no” in 24–7–365 industry monitoring applications.
pg_repack is a PostgreSQL extension tool that can do pretty much what FULL VACUUM does without locking (minimum locking to be precise).
Installation
You can install from source or through pgxn.
apt-get install pgxnclient postgresql-server-dev-all pgxn install pg_repack psql -c "CREATE EXTENSION pg_repack" -d YOUR_DB_NAME -U postgresUsage
/usr/lib/postgresql/9.x/bin/pg_repack -d YOUR_DB_NAME -U postgresNotes
You will need about the same amount of space available as the table being repacked. The reason is that pg_repack is actually creating a fresh copy of table without “dead” space and replacing old one with new (just as FULL VACUUM does)
P.S.
Of course it does not mean that you should abbandon your regulary scheduled vacuumimg and reindexing!
Sources:
Originally published at pasierb.github.io on July 5, 2017.
