New PostgreSQL Extension pg_repack Optimizes Tables Without Downtime

PostgreSQL users frequently encounter the issue of database bloat, which can severely degrade performance. Traditional methods such as VACUUM FULL and CLUSTER can be quite disruptive, but pg_repack offers an online solution that minimizes downtime. Here, we delve into the installation and configuration of pg_repack on a PostgreSQL 14 setup running on Red Hat Enterprise Linux 9 (RHEL 9).

What is pg_repack?

pg_repack is a PostgreSQL extension designed to eliminate bloat from tables and indexes. Unlike CLUSTER and VACUUM FULL, pg_repack works online without requiring an exclusive lock on the tables being processed, making it a more efficient and less intrusive option.

Installation

Download the RPM Package
First, download the pg_repack RPM package for PostgreSQL 14 on RHEL 9:

wget https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-9-x86_64/pg_repack_14-1.5.0-1PGDG.rhel9.x86_64.rpm

Install the RPM Package
Next, install the downloaded RPM package:
```
rpm -iv pg_repack_14-1.5.0-1PGDG.rhel9.x86_64.rpm
```
Verify Installation
Verify the installation by listing the files included in the package:
```
rpm -ql pg_repack_14-1.5.0-1PGDG.rhel9.x86_64.rpm | grep bin
```
Alternatively, use the find command to locate the pg_repack binary:
```
find / -name pg_repack
```
Update PATH Environment Variable
Add the PostgreSQL bin directory to your PATH:
```
export PATH=$PATH:/usr/pgsql-14/bin
```
Make this change permanent by adding it to your ~/.bashrc file:
```
echo 'export PATH=$PATH:/usr/pgsql-14/bin' >> ~/.bashrc
source ~/.bashrc
```

Configuration

Create the Extension
Connect to your PostgreSQL database and create the pg_repack extension:
```
psql -c "CREATE EXTENSION pg_repack" -d employee
```
Verify pg_repack Availability
To ensure pg_repack is available, list the PostgreSQL binaries:
```
pg_re
```
You should see pg_repack listed among the other PostgreSQL binaries:
```
pg_receivewal   pg_recvlogical  pg_repack       pg_resetwal     pg_restore      pg_rewind
```
If you don't see it, you may have forgotten to source your bashrc (source ~/.bashrc)

Usage

Basic Usage
To repack a specific database (e.g., employee), use the following command:

pg_repack -d employee

Advanced Options
pg_repack offers several options to customize its behavior. Here are some useful ones:

Repack all databases:
```
pg_repack -a
```
Repack a specific table:
```
pg_repack -t table_name -d employee
```
Repack tables in a specific schema:
```
pg_repack -s schema_name -d employee
```

Move repacked tables to a new tablespace:

pg_repack --tablespace=new_tablespace -d employee

Order by specific columns:

pg_repack --order-by=column_name -d employee

For a complete list of options, refer to the pg_repack help:

pg_repack --help