Evaluate pg_repack as a way to shorten maintenance downtime on upgrade
According to the production admins, the time taken by
ANALYZE, which does require downtime, is too long during the upgrade procedure. There are ways to shorten it, by parallelizing the work by
-j option, but that still requires locking and therefore downtime and could expose the disk subsystem as a bottleneck, so would require testing.
There is a non-standard tool
pg_repack , which can operate online with much less locking.
Characterized in :
pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. pg_repack is efficient to boot, with performance comparable to using CLUSTER directly.
it seems to be a viable way for Mentat's post-upgrade DB maintenance, minimizing the downtime.
Updated by Radko Krkoš about 2 months ago
- Status changed from New to In Progress
- % Done changed from 0 to 50
- To be discussed changed from No to Yes
pg_repack extension was tested and seems to be a good tool for the task. It performs the compaction by several methods, based on table properties, similar to
CLUSTER on tables with designated clustering index and similar to
VACUUM FULL on tables without one. The compaction seems somewhat faster while reclaiming a bit more space (up to ~2%, equal to 1GB on test data). Also, the compaction can be run in the background with minimal locking, as advertised. While modification to the compacted tables is supported, I advise to run the
Mentat system read-only for compaction. As for disk space requirements, those match the original methods. A separate
ANALYZE run is required afterwards, as the tables are rewritten (but keeping details such as index usage statistics).
pg_repack tool looks like the ultimate PostgreSQL DB compaction solution.