• Must be Super User 
• The table must have a Primary Key OR a Unique Index on a NOT NULL column 
• PostgreSQL development package installed (PostgreSQL-Devel)

Download

https://pgxn.org/dist/pg_repack/ is available for download. 

Installation

$ cd pg_repack
$ make
$ make install
$ psql -c "create extension pg_repack" -d [Database_name]

Usage

pg_repack [options] ... [DB_Name]

Options

The following options can be specified.

Common Options

Connection Options

Generic Options

Examples

Perform online CLUSTER on all clustered tables and online VACUUM FULL on all non-clustered tables in the database. 

$ pg_repack test

Perform online VACUUM FULL on tables foo and bar in the database.

$ pg_repack --no-order --table foo --table bar test

Move all indexes of table foo to tablespace tbs.

$ pg_repack -d test --table foo --only-indexes --tablespace tbs

Move specified index to tablespace tbs.

$ pg_repack -d test --index idx --tablespace tbs

Limitations

• Temporary tables cannot be processed by pg_repack.
• Tables using GiST indexes cannot be processed by pg_repack.
• While pg_repack is working, DDL commands on the target table cannot be executed except VACUUM or ANALYZE. During full table repacking, ACCESS SHARE lock is held on the target table to enforce this restriction, and for versions prior to 1.1.8, no DDL commands should be attempted on the target table while pg_repack is running. In most cases, pg_repack will fail correctly and roll back, but earlier versions may cause data corruption in some cases. 

Operation Details

Code level (1170 lines):

https://github.com/reorg/pg_repack/blob/ebc8a425fcabc06d62a1c9b51fbc3a5f460b2baa/bin/pg_repack.c#L1170C2-L1170C2

To perform full table repacking, pg_repack follows these steps:

1. Create a log table to record changes made to the original table.
2. Add triggers to the original table to log INSERT, UPDATE, and DELETE operations to the log table.
3. Create a new table containing all rows from the original table.
4. Build indexes on this new table.
5. Apply all changes recorded in the log table to the new table.
6. Use system catalogs to swap the tables, including indexes and toast tables.
7. Drop the original table.

pg_repack holds ACCESS EXCLUSIVE locks only briefly during the initial setup phase (steps 1 and 2) and the final swap and drop phase (steps 6 and 7). For the rest of the time, pg_repack holds only ACCESS SHARE locks on the original table, allowing INSERT, UPDATE, and DELETE operations to continue.

Testing

Vacuum Full

1. Vacuum Full
   • After performing vacuum full in photo 2, running a select statement in photo 1 shows the select statement is waiting for a response

2. Check
   • Lock, dead_tuple

-- Check Lock 
SELECT
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,
substr(query,1,25) AS query
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.
relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid() AND
pg_locks.pid=pg_stat_activity.pid ;

-- Check dead_tuple
SELECT
  schemaname || '.' || relname AS table_name,
  n_dead_tup,
  n_live_tup,
  CASE
    WHEN (n_dead_tup + n_live_tup) = 0 THEN -1
    ELSE ROUND((n_dead_tup::decimal / (n_dead_tup + n_live_tup)) * 100, 2)
  END AS dead_tuple_ratio
FROM
  pg_stat_all_tables
WHERE
  schemaname NOT LIKE 'pg_%' AND
  schemaname != 'information_schema'
ORDER BY
  dead_tuple_ratio DESC;

pg_repack

1. Select statement during pg_repack
   • Since pg_repack does not take accessExclusive lock, Select statements can be executed. 
   • Select statements executed after pg_repack

2. Update during pg_repack
   • Update statements must finish before pg_repack finishes. 
     Otherwise, pg will execute a cancel command on the update statement. 

• Update statements on the table after pg_repack

• If the update statement does not finish before pg_repack finishes, pg cancels the update transaction. 

• Perform analyze after cancel

   

3. No problem if UPDATE finishes before pg_repack finishes. 

Failover

Test environment preparation

• pg_repack configured

• autovacuum = off setting

• Generate dead tuples

1. DB down during pg_repack

-- Primary (192.168.1.222)
nohup pg_repack -t bmsql_stock -d benchmarksql -U benchmarksql &

• Before pg_ctl stop

• After pg_ctl stop and failover

• After failover, statistics need to be updated when querying on Primary (192.168.1.223)

-- Primary (192.168.1.222)
psql benchmarksql                  # To check dead_tuple status, perform analyze
analyze public.bmsql_stock     ;
analyze public.bmsql_item      ; 
analyze public.bmsql_order_line; 
analyze public.bmsql_new_order ; 
analyze public.bmsql_config    ; 
analyze public.bmsql_warehouse ; 
analyze public.bmsql_history   ; 
analyze public.bmsql_district  ; 
analyze public.bmsql_oorder    ; 
analyze public.bmsql_customer  ;

• Compare tuples after updating statistics

• Confirmed that tables created during pg_repack remain intact

• Errors occur on Primary (192.168.1.223) after the issue occurs
• Because failover occurred during pg_repack, objects created for pg_repack remain, so all related objects must be dropped.
• List of objects to drop
  • tables
  • triggers
  • types

• It is safe to drop the extension and recreate it.

• Running pg_repack with the -e option allows you to check detailed operations.