PostgreSQL databases naturally accumulate unused space over time. This typically happens due to frequent UPDATE, DELETE operations. Although PostgreSQL uses MVCC to maintain data consistency, old tuple versions remain on disk until vacuumed. In high-write systems such as ERP platforms (for example, Odoo), this behavior can lead to significant table and index bloat.
Bloat increases disk usage, reduces cache efficiency, and can negatively affect query performance.
While PostgreSQL provides VACUUM and VACUUM FULL, these approaches are often either insufficient for reclaiming disk space or too disruptive for production environments.
This is where pgcompacttable becomes useful.
pgcompacttable is a PostgreSQL bloat-reduction tool designed to reorganize tables and rebuild indexes in order to reclaim disk space while avoiding heavy locks and minimizing performance impact.
It selectively processes only bloated objects, making it a practical maintenance tool for large databases.
What is pgcompacttable?
pgcompacttable is an open-source utility that:
- Detects table and index bloat using pgstattuple
- Rewrites bloated tables
- Rebuilds bloated indexes
- Reclaims unused disk space
- Skips objects that do not meet the minimum bloat threshold
Rather than blindly rewriting every table, pgcompacttable evaluates each relation and acts only when meaningful space savings are possible.
Compatibility
pgcompacttable supports PostgreSQL starting from version 9.2 and works with:
- Partial indexes
- Functional indexes
- Unique indexes
- Foreign key indexes
- Indexes stored on separate tablespaces
Installing pgcompacttable
Clone the repository:
git clone https://github.com/dataegret/pgcompacttable.git --depth=1
Go to the folder
cd pgcompacttable/bin
chmod +x pgcompacttable
Explore more about it’s flag
./pgcompacttable --help
You should see the result like this
cybrosys@cybrosys:~/pgcompacttable/bin$ ./pgcompacttable --help
Name:
pgcompacttable - PostgreSQL bloat reducing tool.
Usage:
pgcompacttable [OPTION...]
General options:
[-?mV] [(-q | -v LEVEL)]
Connection options:
[-h HOST] [-p PORT] [-U USER] [-W PASSWD] [-P PATH]
Targeting options:
(-a | -d DBNAME...) [-n SCHEMA...] [-t TABLE...] [-N SCHEMA...] [-T
TABLE...]
Examples:
Shows usage manual.
pgcompacttable --man
Compacts all the bloated tables in all the database in the cluster plus their bloated indexes. Prints additional progress information.
pgcompacttable --all --verbose info
Compacts all the bloated tables in the billing database and their
bloated indexes excepts ones that are in the pgq schema.
pgcompacttable --dbname billing --exclude-schema pgq
cybrosys@cybrosys:~/pgcompacttable/bin$
Also, check the installed version with this command
./pgcompacttable --version
Result :
./pgcompacttable v1.0.14
Basic Usage
General syntax:
pgcompacttable [OPTION...]
Common options:
- -d Database name
- -U PostgreSQL user
- -p Port
- --verbose info Detailed logging
- --all Process all databases
Example:
pgcompacttable --all --verbose info
Required Extension: pgstattuple
pgcompacttable relies on the pgstattuple extension to calculate real bloat statistics.
Switch to the PostgreSQL system user:
sudo su postgres
Connect to your target database (example):
psql
Create the extension:
CREATE EXTENSION pgstattuple;
Verify:
\dx
You should see:
pgstattuple | 1.5 | public | show tuple-level statistics
Note: Extensions are database-specific, so this must be done inside every database you plan to compact.
Running pgcompacttable
Execute:
./pgcompacttable -d postgres -u postgres -p 5433 --verbose info
This starts analyzing each table and index.
Understanding the Output
During execution, pgcompacttable prints detailed statistics.
Example:
[Thu Jan 29 19:37:04 2026] (postgres:public.mail_followers) Start handling table public.mail_followers
[Thu Jan 29 19:37:04 2026] (postgres:public.mail_followers) Vacuum initial: 445317 pages left, duration 0.020 seconds.
[Thu Jan 29 19:37:08 2026] (postgres:public.mail_followers) Bloat statistics with pgstattuple: duration 3.842 seconds.
[Thu Jan 29 19:37:08 2026] (postgres:public.mail_followers) Statistics: 445317 pages (1110582 pages including toasts and indexes), it is expected that ~0.590% (2608 pages) can be compacted with the estimated space saving being 20.375MB.
[Thu Jan 29 19:37:08 2026] (postgres:public.mail_followers) Skipping processing: 0.59% space to compact from 20% minimum required.
[Thu Jan 29 19:37:08 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers__partner_id_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:08 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers_partner_id_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:09 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers_res_model_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:09 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers__res_model_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:09 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers_res_id_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:10 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers__res_id_index, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:37:10 2026] (postgres:public.mail_followers) Skipping reindex: public.mail_followers_pkey, 0% space to compact from 20% minimum required.
[Thu Jan 29 19:38:23 2026] (postgres:public.mail_followers) Reindex: public.mail_followers_mail_followers_res_partner_res_model_id_uniq, initial size 239909 pages(1.830GB), has been reduced by 0% (0.000B), duration 71 seconds.
[Thu Jan 29 19:38:23 2026] (postgres:public.mail_followers) Processing complete.
[Thu Jan 29 19:38:23 2026] (postgres:public.mail_followers) Processing results: 445317 pages left (1110582 pages including toasts and indexes), size reduced by 152.000KB (152.000KB including toasts and indexes) in total.
[Thu Jan 29 19:38:23 2026] (postgres:public.mail_followers) Finish handling table public.mail_followers
(public.mail_followers)
Statistics: 445317 pages, expected ~0.590% can be compacted.
Skipping processing: 0.59% space to compact from 20% minimum required.
Here again, bloat is minimal, so pgcompacttable avoids unnecessary rewriting.
Index handling is also selective:
Reindex: public.mail_followers_mail_followers_res_partner_res_model_id_uniq,
initial size 1.830GB, reduced by 0%, duration 71 seconds.
If rebuilding an index does not provide meaningful savings, the tool reports it and continues.
You can also set the threshold based on your choices
Open the file named pgcompacttable and you can set your own threshold
nano pgcompacttable
Production Safety Guidelines
Before running pgcompacttable on Odoo or any production environment:
Stop application services:
sudo systemctl stop odoo
Reason: pgcompacttable internally rewrites tables. Running it while the application is active can cause lock conflicts or data inconsistency.
After completion:
sudo systemctl start odoo
Always schedule pgcompacttable during maintenance windows.
pgcompacttable offers better control by compacting only objects that truly need it.
pgcompacttable is a powerful PostgreSQL maintenance utility for environments suffering from table and index bloat.
It:
- Uses pgstattuple for accurate measurements
- Reclaims disk space efficiently
- Avoids unnecessary rewrites
- Provides detailed operational visibility
For high-write databases such as Odoo, pgcompacttable serves as a practical alternative to VACUUM FULL, offering safer and more targeted compaction.
Adding pgcompacttable to your database maintenance strategy can significantly improve storage utilization and long-term performance.