Support #7387
openEvaluate pg_repack as a way to shorten maintenance downtime on upgrade
50%
Description
According to the production admins, the time taken by VACUUM FREEZE, CLUSTER and ANALYZE, which does require downtime, is too long during the upgrade procedure. There are ways to shorten it, by parallelizing the work by pg_vacuum's -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 [1], which can operate online with much less locking.
Characterized in [1]:
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š almost 3 years ago
- Status changed from New to In Progress
- % Done changed from 0 to 50
- To be discussed changed from No to Yes
The 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).
The pg_repack tool looks like the ultimate PostgreSQL DB compaction solution.
Updated by Pavel Kácha almost 3 years ago
Ok, let's consider testing it on next upgrade, and based on the results we can add it as alternative (or sole solution?) into doc.
(Disadvantage: not packaged, compiling needed.)
Updated by Radko Krkoš almost 3 years ago
- To be discussed changed from Yes to No
Updated by Radko Krkoš almost 3 years ago
- Status changed from In Progress to Deferred
Updated by Radko Krkoš over 2 years ago
Pavel Kácha wrote in #note-2:
(Disadvantage: not packaged, compiling needed.)
Update: There is a package actually: postgresql-<version>-repack, e.g. postgresql-14-repack.