Project

General

Profile

Actions

Support #7387

open

Evaluate pg_repack as a way to shorten maintenance downtime on upgrade

Added by Radko Krkoš about 3 years ago. Updated 6 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

Also available in: Atom PDF