What is the Best Way to Shrink Your Tables in PostgreSQL?

In PostgreSQL (PG), there are two issues when it comes to table bloat. One is a consequence of the MVCC (Multi-Version Concurrency Control), where there are possibly several versions of a tuple (table row) being used by various queries, at the same time. Only one is active. The other tuples are old versions of the tuples that need to be cleaned up via the vacuum process (manual or autovacuum).

However, there is another problem that PostgreSQL heap tables face, which is free space in a table.  When tuples are deleted or updated, they are not cleaned straight away, but marked, so that the vacuum process can remove them later on (as it is more efficient to vacuum in bulk transactions).  Over time, though, regular vacuuming and inserting tuple data can cause fragmentation in the table (and any indexes on the table), as new tuples are not always the same size as old tuples.  Thus, a table with lots of data changes, will have lots of fragmented free space.

You might be thinking at this point, what is the issue here?  There are two main reasons lots of fragmented free space in a heap table is a problem.

1. There is only so much space for a PostgreSQL instance to use.  Disk space is not free on a database system, so managing space is good for cost management.
2. Fragmentation can cause performance issues.  While there have been improvements on space management in later versions of PG like HOT (Heap Only Tuples), having tuples in random places slows up queries and degrades overall performance.

So, to fix that PostgreSQL uses the VACUUM FULL command.  This command is not actually vacuuming the table but rather (since PG 9) runs the CLUSTER command in the background.  The VACUUM FULL command is excellent at reclaiming space but it does have a few drawbacks, namely the table is completely locked while the table rebuild is taking place and it needs the same amount of space available to rebuild the table (as it clones the table in available space).

Therefore, VACUUM FULL tends to need a full outage to be able to run (or decreased functionality in the application layer).  Therefore, for production systems, alternatives are needed.

The two most common approaches are pg_repack and pg_squeeze.

Note:  Both pg_repack and pg_squeeze need primary or unique constraints, when rebuilding the table or an error will occur:

$: pg_repack -d db01 -t public.test_heap_tab01
WARNING: relation "public.test_heap_tab01" must have a primary key or not-null unique keys

db02=# SELECT squeeze.squeeze_table('public', 'test_heap_tab02');

ERROR:  Table "public"."test_heap_tab02" has no identity index

pg_repack

pg_repack is an extension that can be installed using either the PostgreSQL apt repo (for Debian and Ubuntu) or download the RPM from the pgxn.org website.  It is simple to install and run.

How does pg_repack work?

pg_repack has an external component and an internal extension, which includes some functions, triggers and views.  pg_repack is run from the command line, against an object (usually a table) or list of objects and rebuilds the object table by:

1. Creates a “log” table to record any DML changes
2. Creates a trigger on the original table to record DML that would be made while the COPY process is being run and records them in the “log” table.
3. Creates a copy of the targeted table being rebuilt in the designated tablespace.
4. Duplicates the existing indexes on the new copied table.
5. Applies the changes accumulated in the “log” table (if any) to the copied table.
6. Exchange the copied tables, indexes and associated objects with the original objects.
7. Drop the original table
8. Analyse the table.

More detailed information can be located here.  The details can be found, when running pg_repack with the –echo command on.

Installing pg_repack

To install pg_repack is simple.  On Debian/Ubuntu, check if pg_repack exists in one of the repositories (the PostgreSQL apt repo should be enough).

sudo apt search pg_repack

Output:

Sorting... Done
Full Text Search... Done
postgresql-10-repack/jammy-pgdg 1.5.1-1.pgdg22.04+1 amd64
  reorganize tables in PostgreSQL databases with minimal locks

postgresql-11-repack/jammy-pgdg 1.5.1-1.pgdg22.04+1 amd64
  reorganize tables in PostgreSQL databases with minimal locks

...
postgresql-17-repack/jammy-pgdg,now 1.5.1-1.pgdg22.04+1 amd64 [installed]
  reorganize tables in PostgreSQL databases with minimal locks

To install, choose the pg_repack that is the same as the PG version being used (in this case postgresql-17-repack).

To install use:

sudo apt install postgresql-17-repack

For RHEL-based distros, the procedure is similar.
Check for the pg_repack binary in the installed repos:

sudo dnf search pg-repack

then install the necessary version:

sudo dnf install pg_repack_16.x86_64

Running pg_repack

To run pg_repack is simple.  There are a myriad of options that one can choose from but the simplest is to repack a single table:

pg_repack -d db01 -t public.test_heap_tab01 

Another way would be to do an entire database:

pg_repack -d [database]

Also, the entire instance can be repacked by using the “-a” option.

Note: If the pg_repack extension is not installed, an exception for that database will be thrown.

Therefore, database maintenance schedules can be planned by using the crontab (or using a client for Cloud instances).

Potential issues with using pg_repack

pg_repack does have several issues.  The first and most obvious is that pg_repack uses a trigger to capture all DML changes.  Therefore, in a very heavily used system, that trigger can cause issues.  Therefore, it is recommended that the pg_repack operation is done during quiet times.  On busy systems, this is not always possible, so using pg_repack may not be possible.

Another issue is that the tool does lock the table in ACCESSEXCLUSIVE mode, so no DDL (Dynamic Data Language) is allowed while this lock is in place.  Termination of sessions is another possibility, so pg_repack can affect application behaviour.

pg_squeeze

pg_squeeze was created to address some of the shortcomings of pg_repack, namely the locking and trigger-based performance issues.

pg_squeeze is a completely server-side solution, so there is no client program to run.  All the code runs internally, inside the database (after the extension has been installed correctly) and uses logical decoding to apply all the CDC (Change Data Capture) made, while the table is being rebuilt.

How does pg_squeeze work?

pg_squeeze works similarly to pg_repack, except the CDC log is done by logical replication.  pg_squeeze still has the same limitations as pg_repack, as it needs twice the space to be able to rebuild the table, it needs some locks to move (rename) the table, DDL commands are blocked and some sessions can be blocked.

Installing pg_squeeze

Recommend to change the parameters to the following in postgresql.conf, although max_replication_slots needs to be more than one and pg_squeeze needs to be loaded into the shared_preload_libraries parameter.

Installing the binary 

To install pg_squeeze, the binary has to be installed separately from the PostgreSQL package.

For debian/Ubuntu systems, the apt command is:

sudo apt install postgresql-[PG version]-squeeze

and for RHEL (Red Hat Enterprise Linux) based distro’s, it can be built from source:

https://github.com/cybertec-postgresql/pg_squeeze

Some PG parameters have to be changed before the extension can be installed:

wal_level = logical
max_replication_slots = 10
shared_preload_libraries = 'pg_squeeze' 

or via psql:

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET shared_preload_libraries = 'pg_squeeze' ;

Note: The database cluster needs to be restated, if the wal_level was not already at the ‘logical’ level.

After the parameters have been setup correctly, the extension can be installed via a PG user with the correct privileges:

CREATE EXTENSION IF NOT EXISTS pg_squeeze;

Running pg_squeeze

There are a couple of ways to run pg_squeeze.  One is to schedule a target table inside the squeeze.squeeze_table function or to run it manually using the same function.

Manual:

SELECT * FROM squeeze.tables ;

There is also a scheduled option that is more detailed.  A tuple (a named table) is added to squeeze.tables table, with a series of parameters like when the table should be checked and if it needs to be changed i.e. “squeezed”.  More information about pg_squeeze can be found here.

Scheduled example:

INSERT INTO squeeze.tables VALUES ('public', 'test_heap_tab02 ('{30}', '{22}', NULL, NULL, '{3, 5}'));

To start the scheduled squeeze process, it can be run started manually via:

SELECT squeeze.start_worker();

and stopped via:

SELECT squeeze.stop_worker();

There is also a way to automate the processing of pg_squeeze on a cluster level by enabling these parameters in the postgresql.conf file:

squeeze.worker_autostart = '[dbname]'
squeeze.worker_role = postgres

A background worker will be started, when the database cluster initialises.

For monitoring tables have been “squeezed” and to check performance, querying the “squeeze.log” table, will give the needed information:

SELECT * FROM squeeze.log;

output:

tabschema   | public
tabname     | test_heap_tab02
started     | 2024-10-13 18:49:09.456857+01
finished    | 2024-10-13 18:49:36.785023+01
ins_initial | 5000000
ins         | 0
upd         | 0
del         | 0

and any subsequent errors will be recorded in the “squeeze.error” table.

Problems to be aware of with using pg_squeeze

The most common issues found, when using pg_squeeze are:

• Blocking access to other processes.  To control the exclusive access needed to rename the table, the squeeze.max_xlock_time parameter can be set, so the table can’t be locked for more than the specified time (in milliseconds).
• Table squeezing is not fast enough.  If the squeeze process is too slow, the number of background control workers can be increased by the squeeze.workers_per_database parameter.

Comparing the different approaches

There are several key differences between the different packages but the table below summarises them.

pg_repack is a good tool that offers the ability to rebuild any table that needs it.  However, it does not have any control mechanisms to say if the table needs to be rebuilt.  It’ll just rebuild the table.  On top of that, the performance hit via the trigger CDC mechanism can really affect performance.  However, it is easier to setup and works well on tables without high churn (lots of DML).

pg_squeeze is the more mature product.  It requires more time to setup and will require an instance restart, if the wal_level is not already set to “logical".  It also can have performance issues, namely, if the logical replication can’t keep up with the demand.  But it should be better under higher loads, due to the mechanism and the fact that that trigger-based CDC can have performance implications.

Conclusion

All these methods have similar problems, namely that they need enough free space to be able to rebuild (copy) the objects in the first place.  So, which tool to use, comes down to preference and performance.

The key outcome comes down to whether or not the tables in the target database need to be rebuilt often, or if it can be scheduled, like a change.

If the table rebuild can be scheduled, and there is no user or application interaction with tables dueing this time, VACUUM FULL is an excellent candidate.  It does not need additional code to be installed or setup and is already there.  The only issue is that exclusive table lock while the rebuild is taking place and the additional space needed (diskspace for the copy and the additional WAL).  Also, it is the only option, when there is no primary key or unique key on the target table.

If the table or database only needs to be rebuilt occasionally and there is too many DML changes, pg_repack is a good option.

However, if regular rebuilding of the tables is needed, then having the ability to rebuild the tables on schedule and to have vacuum-like parameters to decide, if a rebuild is needed, then pg-squeeze is the optimal tool. 

All these tools have the same issue, which is that on extremely busy OLTP (OnLine Transactional Processing) databases, none of these tools will work without having an impact on performance or functionality.

Therefore, it goes without saying that any solution has to be tested vigorously and under load conditions.  

These repack/squeeze tools can go a long way to helping prevent freespace bloating and fragmentation and they are a welcome addition to combating table bloat.

Appendix A - pg_repack echo 

[postgres@Pearson-PF38TSR2: pg_scripts ] $: pg_repack -d db01 -t public.test_heap_tab01 --echo
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) select repack.version(), repack.version_sql()
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT r FROM (VALUES ($1, 'r')) AS given_t(r,kind) WHERE NOT EXISTS(  SELECT FROM repack.tables WHERE relid=to_regclass(given_t.r)) AND NOT EXISTS(  SELECT FROM pg_catalog.pg_class c WHERE c.oid=to_regclass(given_t.r) AND c.relkind = given_t.kind AND given_t.kind = 'p')
LOG:    (param:0) = public.test_heap_tab01
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES ($1::text)) as v (tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemaname
LOG:    (param:0) = (null)
LOG:    (param:1) = public.test_heap_tab01
INFO: repacking table "public.test_heap_tab01"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 33150
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL lock_timeout = 100
LOG: (query) LOCK TABLE public.test_heap_tab01 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET lock_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG:    (param:0) = 33150
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG:    (param:0) = 33150
LOG:    (param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG:    (param:0) = 33150
LOG: (query) SELECT repack.create_index_type(33156,33150)
LOG: (query) SELECT repack.create_log_table(33150)
LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON public.test_heap_tab01 FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('id')
LOG: (query) ALTER TABLE public.test_heap_tab01 ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_33150')
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT pg_backend_pid()
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 33150 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SELECT coalesce(array_agg(l.virtualtransaction), '{}')   FROM pg_locks AS l   LEFT JOIN pg_stat_activity AS a     ON l.pid = a.pid   LEFT JOIN pg_database AS d     ON a.datid = d.oid   WHERE l.locktype = 'virtualxid'   AND l.pid NOT IN (pg_backend_pid(), $1)   AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0')   AND (a.application_name IS NULL OR a.application_name <> $2)  AND a.query !~* E'^\\s*vacuum\\s+'   AND a.query !~ E'^autovacuum: '   AND ((d.datname IS NULL OR d.datname = current_database()) OR l.database = 0)
LOG:    (param:0) = 215384
LOG:    (param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_33150
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 33150 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) SET LOCAL lock_timeout = 100
LOG: (query) LOCK TABLE public.test_heap_tab01 IN ACCESS SHARE MODE
LOG: (query) RESET lock_timeout
LOG: (query) SELECT repack.create_table($1, $2)
LOG:    (param:0) = 33150
LOG:    (param:1) = pg_default
LOG: (query) INSERT INTO repack.table_33150 SELECT id,create_dt,cust_name,country,data FROM ONLY public.test_heap_tab01
LOG: (query) SELECT repack.disable_autovacuum('repack.table_33150')
LOG: (query) COMMIT
LOG: (query) SELECT 'repack.table_33150'::regclass::oid
LOG: (query) CREATE UNIQUE INDEX index_33156 ON repack.table_33150 USING btree (id)
LOG: (query) CREATE INDEX index_33158 ON repack.table_33150 USING btree (country)
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_33150 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_33150 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_33150 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_33150 SET (id, create_dt, cust_name, country, data) = ($2.id, $2.create_dt, $2.cust_name, $2.country, $2.data) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_33150 WHERE id IN (
LOG:    (param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
LOG:    (param:0) = {}
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL lock_timeout = 100
LOG: (query) LOCK TABLE public.test_heap_tab01 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET lock_timeout
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL lock_timeout = 100
LOG: (query) LOCK TABLE repack.table_33150 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET lock_timeout
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_33150 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_33150 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_33150 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_33150 SET (id, create_dt, cust_name, country, data) = ($2.id, $2.create_dt, $2.cust_name, $2.country, $2.data) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_33150 WHERE id IN (
LOG:    (param:5) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG:    (param:0) = 33150
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL lock_timeout = 100
LOG: (query) LOCK TABLE public.test_heap_tab01 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET lock_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG:    (param:0) = 33150
LOG:    (param:1) = 4
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE public.test_heap_tab01
LOG: (query) COMMIT
LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 33150