What is the best utility to use to reorganize PostgreSQL tables (for example: pg_repack vs pgcompactor vs autovacuum tweak)?

Question

PostgreSQL

The pg_repack module is a Postgres Pro Enterprise extension that allows you to eliminate voids in tables and indexes and can optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM_FULL, it performs these operations on the fly, avoiding table exclusive locks while processing them. In addition, pg_repack is efficient, showing performance comparable to using CLUSTER directly.
pgcompacttable is a tool to reduce the size of bloated tables and indexes without heavy locks. It is designed to reorganize data in tables and rebuild indexes to reclaim disk space without impacting database performance.
autovacuum adjustment: increase the number of workers. reduce idle autovacuum.
pg_repack vs pgcompactor vs autovacuum setup. What is the best utility to use to reorganize PostgreSQL tables (or is it better to adjust autovacuum)?

Answer 1

pg_repack is a community extension, a fork of the earlier pg_reorg. And without denying the contribution of pgpro contributors to postgresql development in general - I don't see them among pg_repack contributors.
We actively use both. The main points regarding pg_repack vs pgcompacttable are , in general, exactly what I stated in the README of the compactor. Speaking in Russian:
- pg_repack makes a copy of the table, i.e. two times the disk supply is required. The compactor works within a single table and requires space only for a copy of the thickest index, and then, thick indexes are processed last. Those. calculation for work when there is not enough space.
- the compactor is deliberately executed slowly so as not to hurt the running project
- the compactor is notcan handle toast tables. Repak - maybe.
- pg_repack has very noticeable spikes in io and wal. The whole table goes in one transaction and this is not regulated in any way. Therefore, it can affect production on unimportant disks, including tearing off replicas with a lack of network bandwidth.
- pg_repack can move between tablespaces and can do a cluster command
Of course, it is necessary to set up an autovacuum and not interfere with its work - i.e. eliminate long transactions. But still, there are tricky moments when a repack or compactor is needed.