PostgreSQL 19: Online Table Reorganization with REPACK (Similar to Oracle DBMS_REDEFINITION)
For years, PostgreSQL DBAs have relied on VACUUM FULL to rewrite tables and reclaim unused space. However, when minimizing the impact of table reorganization is important, tools such as pg_repack provide an online-style alternative.
PostgreSQL 19 introduces the new REPACK command as a built-in solution for table reorganization, including REPACK CONCURRENTLY, which allows the table to remain available for normal database activity during most of the operation.
PostgreSQL 19 also extends this capability with support for reorganizing a table according to an index, providing functionality comparable to the physical ordering achieved with CLUSTER.
How REPACK Works
REPACK does not simply compact the existing table in place. Conceptually, it creates a new, compact copy of the table, copies the existing rows into it, tracks changes made to the original table while the operation is running, and applies those changes to the new table.
At the end, it performs a short final synchronization and swaps the old table with the reorganized one. This approach allows normal DML to continue during most of the operation.
This approach is conceptually similar to Oracle’s DBMS_REDEFINITION. In Oracle, an interim table is populated while the original table remains available, and a materialized view log can capture changes made to the original table during the reorganization. These changes are then synchronized to the interim table before the final DBMS_REDEFINITION.FINISH_REDEF_TABLE operation switches the objects.
Limitations of REPACK
Although REPACK provides online table reorganization with minimal downtime, it has some limitations:
- The table must have a primary key or a suitable NOT NULL unique index.
- DDL operations on the target table cannot be performed while REPACK is running.
- Long-running transactions can delay the final lock required to complete the reorganization.
In addition, the operation requires additional disk space because a new copy of the table and its indexes is created.
Testing REPACK on a Large Table
In this article, I demonstrate how REPACK can reorganize a large table with minimal disruption to concurrent DML. I also demonstrate the option to reorganize a table according to an index, similar to CLUSTER, and compare its behavior with VACUUM FULL.
The test uses a table containing 30 million rows.
1. Initial Table Size
First, I checked the number of rows in jadval1:
postgres=# select count(*) from jadval1;
count
----------
30000000
(1 row)I then checked the physical size of the table and its indexes:
postgres=# SELECT
pg_size_pretty(pg_table_size('jadval1')) AS table_size,
pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
table_size | index_size | total_size
------------+------------+------------
4824 MB | 1071 MB | 5895 MB
(1 row)Therefore, the table occupied approximately 4.8 GB, while the total relation size, including indexes, was approximately 5.9 GB.
To investigate how much space was actually occupied by live tuples, I enabled the pgstattuple extension:
postgres=# CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSIONThen I ran:
postgres=# SELECT
table_len,
tuple_count,
tuple_len,
dead_tuple_count,
dead_tuple_len,
free_space,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(tuple_len) AS live_tuple_size,
pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');
table_len | tuple_count | tuple_len | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
5056790528 | 30000000 | 2730000000 | 0 | 0 | 2040987872 | 4823 MB | 2604 MB | 0 bytes | 1946 MB
(1 row)This result is interesting.
There were:
- 30 million live tuples
- Approximately 2.6 GB of live tuple data
- Approximately 1.9 GB of free space
2. Running REPACK CONCURRENTLY
To demonstrate that REPACK (CONCURRENTLY) allows normal database activity during table reorganization, I started the operation from Session 1:
— session 1:
postgres=# REPACK (CONCURRENTLY) Jadval1;
executing...While REPACK was running, I performed concurrent updates from Session 2:
— session 2
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20000001 AND 20010000;
UPDATE 10000
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20010000 AND 20020000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20020000 AND 20030000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20030000 AND 20040000;
UPDATE 10001At the same time, Session 3 continuously queried the table:
session 3:
postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh'; \g
count
-------
0
(1 row)
postgres=# \g
count
-------
10000
(1 row)
postgres=# \g
count
-------
20000
(1 row)
postgres=# \g
count
-------
30000
(1 row)
postgres=# \g
count
-------
40000
(1 row)The increasing count confirms that the table remained accessible for both UPDATE and SELECT operations while REPACK (CONCURRENTLY) was reorganizing it.
Monitoring REPACK Progress
The pg_stat_progress_repack view can be used to monitor the operation:
postgres=# SELECT
pid,
phase,
heap_tuples_scanned,
heap_tuples_inserted,
heap_tuples_updated,
heap_tuples_deleted
FROM pg_stat_progress_repack;
pid | phase | heap_tuples_scanned | heap_tuples_inserted | heap_tuples_updated | heap_tuples_deleted
------+-------------------+---------------------+----------------------+---------------------+---------------------
1554 | seq scanning heap | 12727565 | 12727565 | 0 | 0
(1 row)Checking for Blocking
I also checked whether the concurrent activity was blocked:
postgres=# SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocked.wait_event_type,
blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
blocked_pid | blocked_query | blocking_pid | blocking_query | wait_event_type | wait_event
-------------+---------------+--------------+----------------+-----------------+------------
(0 rows)No blocking session was reported during the check. Therefore, in this test, REPACK (CONCURRENTLY) allowed the table to remain available for normal SELECT and UPDATE activity while the table was being reorganized.
REPACK Completion Time
After the concurrent activity and monitoring tests were completed, REPACK (CONCURRENTLY) finished successfully:
postgres=# REPACK (CONCURRENTLY) Jadval1;
REPACK
Time: 211323.981 ms (03:31.324)The table reorganization completed in approximately 3 minutes and 31 seconds, while the table remained available for concurrent database activity during the operation.
3. Verifying the Result of REPACK
After REPACK completed, I checked the table size again:
postgres=# SELECT
pg_size_pretty(pg_table_size('jadval1')) AS table_size,
pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
table_size | index_size | total_size
------------+------------+------------
2894 MB | 644 MB | 3539 MB
(1 row)This table shows the table and index sizes before and after REPACK:
So the table and its indexes together released approximately 2.3 GB of physical storage.
Then, I used pgstattuple again to verify the result:
postgres=# SELECT
table_len,
tuple_count,
tuple_len,
dead_tuple_count,
dead_tuple_len,
free_space,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(tuple_len) AS live_tuple_size,
pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');
table_len | tuple_count | tuple_len | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
3034136576 | 30000000 | 2728360000 | 0 | 0 | 26310320 | 2894 MB | 2602 MB | 0 bytes | 25 MBThis table shows the significant reduction in free space after REPACK:
4.Comparing REPACK with VACUUM FULL
After completing the REPACK test, I ran VACUUM FULL on the same table:
postgres=# vacuum full jadval1;
VACUUM
Time: 82397.189 ms (01:22.397)VACUUM FULL also rewrites the table and can significantly reduce its physical size. However, the main operational difference is table availability during the operation.
To demonstrate this, I executed a SELECT against jadval1 while VACUUM FULL was running:
postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh';
count
-------
40000
(1 row)
Time: 70548.856 ms (01:10.549)The blocking-session check showed:
postgres=# SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocked.wait_event_type,
blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
blocked_pid | blocked_query | blocking_pid | blocking_query | wait_event_type | wait_event
-------------+----------------------------------------------------------------+--------------+----------------------+-----------------+------------
1674 | SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh'; | 1554 | vacuum full jadval1; | Lock | relation
(1 row)VACUUM FULL and REPACK can both rewrite and compact a table, but their behavior in a production environment can be very different:
5.REPACK … USING INDEX: Clustering the Table According to an Index
REPACK provides another useful capability: reorganizing a table according to the ordering defined by an index.
First, I created an index on customer_code:
postgres=# CREATE INDEX idx_jadval1_customer_code ON jadval1(customer_code);
CREATE INDEXThen, I reorganized the table using this index:
postgres=# REPACK (CONCURRENTLY) jadval1 USING INDEX idx_jadval1_customer_code;
REPACKIn this case, REPACK does more than simply reclaim unused space. The table is physically reorganized according to the specified index, improving the physical locality of rows with similar customer_code values.
Written by Vahid Yousefzadeh
I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com
Comments
Post a Comment