Implementing and utilizing pg_repack

pg_repack is an extension for your IBM Cloud® Databases for PostgreSQL that provides functions to reorganize tables and indexes online without requiring table-locking. Its primary function is to reduce table size and improve performance by eliminating bloat.

Bloat typically occurs in PostgreSQL when rows in a table are updated or deleted, leaving space that isn't reused. Over time, this can cause tables to take up more disk space than necessary, leading to decreased performance due to increased disk I/O and memory usage.

pg_repack is supported by PostgreSQL version >9.5.

Run the following query to enable the extension:

CREATE EXTENSION pg_repack;

Requirements

The presence of a PRIMARY KEY or a NOT NULL UNIQUE constraint on the table is mandatory.
An additional amount of disk space equal to twice the current size is required.

Use the following query to check the table size:

SELECT schemaname as schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables where relname='<tablename>';

The pg_repack on the client needs to be compatible with the pg_repack on the server.

Use the following query to view the installed extension and its version:

SELECT extname AS extension_name,
       extversion AS version,
       nspname AS schema
FROM pg_extension
JOIN pg_namespace ON pg_namespace.oid = pg_extension.extnamespace
ORDER BY extname;

How-to-use-pgrepack

To run pg_repack with your desired options, use the following syntax:

```sh {: pre}

pg_repack -k [OPTION]... [DBNAME]

- The -k option tells pg_repack to keep the original table if a problem occurs, allowing for safer operation in critical environments.
- Replace [DBNAME] with the name of your PostgreSQL database.
- Common options include:
    
 - `-t` to target a specific table
 - `-U` to specify a user
 - `-h` and `-p` for host and port

After installing the pg_repack extension and verifying that the table has a primary key or a not-null unique constraint, you can repack a table as shown below:

```sh {: .codeblock}
eg:
exampledb=> create table test(id int);
CREATE TABLE
exampledb=> create table test1(id int PRIMARY KEY, name text);
CREATE TABLE
exampledb=>
exampledb=> CREATE EXTENSION pg_repack;
CREATE EXTENSION
exampledb=>
exampledb=>
exampledb=> SELECT extname AS extension_name,
    extversion AS version,
    nspname AS schema
FROM pg_extension
JOIN pg_namespace ON pg_namespace.oid = pg_extension.extnamespace
ORDER BY extname;
extension_name | version |    schema
----------------+---------+---------------
pg_repack      | 1.5.1   | ibm_extension
plpgsql        | 1.0     | pg_catalog
(2 rows)

----
pg_repack -k --host=localhost --dbname=exampledb --port=31273 --username=test_admin 
INFO: repacking table "public.test1"