Project

General

Profile

Actions

Support #7387

open

Evaluate pg_repack as a way to shorten maintenance downtime on upgrade

Added by Radko Krkoš 2 months ago. Updated about 2 months ago.

Status:
Deferred
Priority:
Normal
Assignee:
Category:
Research and analysis
Target version:
Start date:
06/01/2021
Due date:
% Done:

50%

Estimated time:
To be discussed:
No

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.

[1] https://reorg.github.io/pg_repack/

Actions #1

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

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.

Actions #2

Updated by Pavel Kácha about 2 months 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.)

Actions #3

Updated by Radko Krkoš about 2 months ago

  • To be discussed changed from Yes to No
Actions #4

Updated by Radko Krkoš about 2 months ago

  • Status changed from In Progress to Deferred
Actions

Also available in: Atom PDF