pg_rewrite

Overview

PIGSTY RPM and PGDG DEB are aligned at 2.2 for PostgreSQL 14-18; shared_preload_libraries=pg_rewrite and wal_level=logical are required.

Version

Build

You can build the RPM packages for pg_rewrite using pig build:

pig build pkg pg_rewrite         # build RPM packages

Install

You can install pg_rewrite directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_rewrite;          # Install for current active PG version

pig ext install -y pg_rewrite -v 18  # PG 18
pig ext install -y pg_rewrite -v 17  # PG 17
pig ext install -y pg_rewrite -v 16  # PG 16
pig ext install -y pg_rewrite -v 15  # PG 15
pig ext install -y pg_rewrite -v 14  # PG 14

dnf install -y pg_rewrite_18       # PG 18
dnf install -y pg_rewrite_17       # PG 17
dnf install -y pg_rewrite_16       # PG 16
dnf install -y pg_rewrite_15       # PG 15
dnf install -y pg_rewrite_14       # PG 14

apt install -y postgresql-18-pg-rewrite   # PG 18
apt install -y postgresql-17-pg-rewrite   # PG 17
apt install -y postgresql-16-pg-rewrite   # PG 16
apt install -y postgresql-15-pg-rewrite   # PG 15
apt install -y postgresql-14-pg-rewrite   # PG 14

Preload:

shared_preload_libraries = 'pg_rewrite';

Create Extension:

CREATE EXTENSION pg_rewrite;

Usage

pg_rewrite: Tool allows read write to the table during the rewriting

pg_rewrite requires wal_level = logical and must be added to shared_preload_libraries. Common use cases include changing column data types, partitioning tables, reordering columns, and moving tables to different tablespaces – all while allowing concurrent reads and writes.

Rewrite a Table

Create the target table with the desired schema, then call rewrite_table():

-- Source table
CREATE TABLE measurement (id int, city_id int NOT NULL, logdate date NOT NULL, peaktemp int, PRIMARY KEY(id, logdate));

-- Target table with new schema (e.g., bigint id + partitioning)
CREATE TABLE measurement_aux (id bigint, city_id int NOT NULL, logdate date NOT NULL, peaktemp int, PRIMARY KEY(id, logdate))
  PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement_aux FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

-- Perform the rewrite (copies data, applies concurrent changes, then renames)
SELECT rewrite_table('measurement', 'measurement_aux', 'measurement_old');

Both source and target tables must have an identity index (typically a primary key). The function copies all rows, replays concurrent changes via logical decoding, then atomically renames the tables.

Progress Monitoring

SELECT * FROM pg_rewrite_progress;

Shows ins_initial (initial rows copied), ins, upd, del (concurrent changes applied).

Configuration

• rewrite.max_xlock_time – Maximum time (ms) the exclusive lock is held during the final rename stage. Default 0 (unlimited). Set to e.g. 100 to limit lock duration to 0.1s; the function will retry if exceeded.

SET rewrite.max_xlock_time TO 100;

Constraints Handling

• PRIMARY KEY, UNIQUE, EXCLUDE: add to target table before calling rewrite_table()
• CHECK, NOT NULL (PG18+), FOREIGN KEY: created automatically as NOT VALID; validate with ALTER TABLE ... VALIDATE CONSTRAINT ...