Monitoring and using pg_repack in Azure Database for PostgreSQL flexible server

In this post: we will walk through how to configure and use the pg_repack extension in Azure Database for PostgreSQL flexible server. We will also cover how to run pg_repack on a table, monitor the progress during execution, and validate the results after the repack operation is completed.

Why Monitor pg_repack During Execution?

While running pg_repack is straightforward, administrators often need visibility into what is happening behind the scenes, especially when working with large tables in production environments.

Monitoring the operation provides several benefits:

• Verify that pg_repack is actively running and has not stalled.
• Identify the current phase of the operation, such as table copying, index rebuilding, or final table swap.
• Understand resource usage and the impact on the database.

Before you start:

Before performing this lab, ensure the following prerequisites are met:
Azure Resources:

• An active Azure subscription
• An Azure Database for PostgreSQL flexible server instance

Database Requirements:

• A table with a PRIMARY KEY or UNIQUE NOT NULL index (required by pg_repack)

Linux Machine:

• I have used an Ubuntu Linux Virtual Machine
• SSH connectivity to the VM using PuTTY

Configuring and using pg_repack in Azure Database for PostgreSQL

Step 1: Allow list and create the pg_repack Extension

Before using pg_repack, the extension must be allowlisted and created in the target database. Navigate to your Azure Database for PostgreSQL flexible server and add pg_repack to the allow list of extensions. Once the server configuration is updated, connect to the database and create the extension.

Step 2: Create and connect to a Linux Virtual Machine

Since pg_repack is a client-side utility, a Linux virtual machine was created to install and run the pg_repack client against Azure Database for PostgreSQL flexible server.

Step 3: Connect to the Linux Virtual Machine

Before running pg_repack, I connected to the Linux virtual machine that would be used to install and execute the pg_repack client.

1. In the Azure portal, navigate to the Linux Virtual Machine.
2. Open PuTTY and enter the VM's IP Address.
3. Select SSH (Port 22) as the connection type and click Open.
4. Enter the VM username and password when prompted.
5. After successful authentication, a terminal session is established

The following output confirms that the connection was successful and that the Ubuntu operating system is ready for further configuration.

Step 4: Update Package Repositories on the Linux Virtual Machine

Before installing the pg_repack client, update the package repositories on the Ubuntu virtual machine to ensure the latest package information is available. Please run the following command and provide the password for the linux virtual machine when prompted.

sudo apt update

Step 5: Download the pg_repack

Important: If you face any version mismatch issue or errors then you can use the below command to resolve

After preparing the test environment and generating table bloat, the next step was to download the pg_repack source code to the Linux virtual machine.
The git clone command downloads the pg_repack source code from the official GitHub repository to the Linux virtual machine. This source code is later used to build and install the pg_repack client utility required to perform table reorganization operation. After downloading the repository, the cd pg_repack command changes the current directory to the downloaded project folder.

git clone https://github.com/reorg/pg_repack.git
cd pg_repack

Step 6: Install PostgreSQL Client Packages

After updating the package repositories, the next step was to install the PostgreSQL client packages on the Linux virtual machine. The PostgreSQL package installs the PostgreSQL client tools, including psql, which is used to connect to Azure Database for PostgreSQL flexible server.

sudo apt install postgresql postgresql-contrib

When the command is executed, Ubuntu displays a summary of the packages that will be installed along with their dependencies. To proceed with the installation, type Y and press Enter.

Step 7: Connect to Azure Database for PostgreSQL flexible server

After installing the PostgreSQL client packages on the Linux virtual machine, the next step is to establish a connection to the Azure Database for PostgreSQL flexible server using the psql client.  Please update the following command with your server details and execute it and enter your PostgreSQL server user password to establish the connection. Note: Make sure your Linux machine network is allowed to connect on your Azure Database for PostgreSQL flexible server.

psql -h <Hostname> -p 5432 -U <username> postgres

Step 8: Create a Test Database

After successfully connecting to Azure Database for PostgreSQL flexible server, a dedicated database was created to perform the pg_repack lab activities as shown below:

Step 9: Connect to the Newly Created Database

After creating the repack_lab database, connect to it before proceeding with the pg_repack activities.

\c repack_lab

Step 10: Create a Sample Table for pg_repack Testing

After connecting to the repack_lab database, I created a sample table that would be used throughout the lab to test the functionality of pg_repack.

CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
name TEXT,
created_at TIMESTAMP DEFAULT NOW()
);

Step 11: Insert Sample Data into the Test Table

After creating the test_table, the next step was to populate it with sample data. This helps simulate a realistic workload and provides enough records to demonstrate how pg_repack works. The following command was used to insert 100,000 rows into the table:

INSERT INTO test_table(name)
SELECT md5(random()::text)
FROM generate_series(1,100000);

Step 12: Create an Index on the Test Table

After populating the test_table with 100,000 records, an index was created as shown below:

CREATE INDEX idx_test_name
ON test_table(name);

Step 13: Check the Initial Table Size

Before generating table, bloat and running pg_repack, it is useful to capture the current size of the table. This serves as a baseline for comparing storage consumption before and after the repack operation.

SELECT pg_size_pretty(pg_total_relation_size('test_table'));

Step 14: Generate Table Bloat Using UPDATE Operations

To demonstrate how pg_repack reorganizes a table and reclaims unused space, the next step was to generate table bloat by repeatedly updating all rows in the table. The following command was executed multiple times:

UPDATE test_table
SET name = md5(random()::text);

Step 15: Disable Autovacuum on the Test Table

To clearly observe table bloat and demonstrate the effectiveness of pg_repack, autovacuum was temporarily disabled on the test table. This prevents Azure Database for PostgreSQL flexible server from automatically cleaning up dead tuples generated by the previous UPDATE and DELETE operations.

ALTER TABLE test_table
SET (autovacuum_enabled = false);

Step 16: Analyze Live and Dead Tuples Before Running pg_repack

After generating table bloat through multiple UPDATE and DELETE operations and disabling autovacuum, the next step was to measure the number of live and dead tuples in the table.

Step 17: Execute pg_repack to Reorganize the Table

The pg_repack utility was executed against the test table to reclaim unused space and reorganize the table structure. The pg_repack utility reorganizes tables and indexes online while minimizing locking and application downtime. Unlike VACUUM FULL, pg_repack performs the reorganization in the background and requires only a brief lock during the final table swap operation.

Command executed:

pg_repack \
--host=myflexibleserver.postgres.database.azure.com \
--port=5432 \
--username=dbadmin \
--dbname=repack_lab \
--table=test_table \
--jobs=2 \
--no-kill-backend \
--no-superuser-check

Monitoring pg_repack Execution

Once the pg_repack operation was initiated, the next step was to monitor its execution and identify the activities being performed by the utility in the background:

To track active pg_repack sessions, the following query was executed:

SELECT
pid,
usename,
application_name,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
query
FROM pg_stat_activity
WHERE application_name ILIKE '%repack%'
OR query ILIKE '%repack%'
ORDER BY query_start;

After starting the pg_repack operation, I monitored the active sessions by querying the pg_stat_activity system view. This helped me understand the current stage of the operation and verify that the process was executing successfully. The query returned multiple sessions created by pg_repack, indicating that the utility was actively processing the table.

Session 1 - Lock Acquisition

LOCK TABLE public.test_table IN SHARE UPDATE EXCLUSIVE MODE

This session acquired a SHARE UPDATE EXCLUSIVE lock on the target table. This lock prevents conflicting schema changes while still allowing normal read and write operations during most of the repack process.

Session 2 - Temporary Repack Table Creation

SELECT 'repack.table_24861'::regclass::oid

At this stage, pg_repack was working with an internal temporary table created to hold the reorganized data. This table acts as a replacement for the original table during the repack operation.

Session 3 - Creating Primary Key Index

CREATE UNIQUE INDEX index_24869
ON repack.table_24861 USING btree(id)

This session shows pg_repack rebuilding the primary key index on the new table structure.

Session 4 - Creating Secondary Index

CREATE INDEX index_24873
ON repack.table_24861 USING btree(name)

This indicates that additional indexes are being recreated on the temporary table to match the original table definition.

Based on the output, the operation had successfully moved past the initialization phase and was actively rebuilding indexes on the temporary table. This is one of the final stages before pg_repack performs the table swap and completes the reorganization process.

Conclusion

In summary, monitoring pg_repack execution is essential for ensuring a smooth and efficient table reorganization process. Proper visibility into progress and resource consumption helps administrator complete maintenance tasks confidently while maintaining optimal database performance and availability.

References

• Optimize by using pg_repack - Azure Database for PostgreSQL | Microsoft Learn
• PostgreSQL: Documentation: 18: 27.4. Progress Reporting
• pg_repack 1.5.3 -- Reorganize tables in PostgreSQL databases with minimal locks