mpasierbski's 🗒️🌱

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 does this massive batch UPDATEs or DELETEs your database can grow in size pretty quickly.

At ISE we develop plant performance monitoring application which collects hundreds (somtimes > 1000) of data counters ever minute. This "minute-by-minute" data is later compressed into hourly slots. This process involves massive DELETE operations every hour.

To reclaim disk space you need FULL VACUUM, but it locks tables which is a huge "no no" in 24-7-365 industry monitoring application.

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 postgres

Usage

/usr/lib/postgresql/9.x/bin/pg_repack -d YOUR_DB_NAME -U postgres

Notes

You will need around 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:

Michal Pasierbski © 2020, Built with Gatsby