Index bloat is a common performance issue in long-running databases. Frequent UPDATE, and DELETE operations gradually inflate B-tree indexes, increasing disk usage and slowing queries. While PostgreSQL provides manual REINDEX, running it regularly and safely in production is not straightforward.
That’s where pg_auto_reindexer helps. It automates index bloat detection and performs reindexing only when required.
This article explains how to install pg_auto_reindexer, set up password-free authentication, and work with each reindexing option through hands-on examples you can run on your own system.
What is pg_auto_reindexer?
pg_auto_reindexer is a standalone utility that:
- Detects bloated B-tree indexes
- Applies configurable thresholds (bloat %, size limits, disk safety)
- Reindexes qualifying indexes
- Supports parallel execution
- Logs activity to systemd journal
Instead of rebuilding everything blindly, it focuses only on indexes that actually need attention.
Installation
Download the script:
wget https://raw.githubusercontent.com/vitabaks/pg_auto_reindexer/refs/heads/main/pg_auto_reindexer
Move it to your executable path:
sudo mv pg_auto_reindexer /usr/local/bin/
sudo chmod +x /usr/local/bin/pg_auto_reindexer
Verify installation:
pg_auto_reindexer --version
Example output:
pg_auto_reindexer v1.7
View available options:
pg_auto_reindexer -?
You can these options:
cybrosys@cybrosys:~$ pg_auto_reindexer -?
pg_auto_reindexer - Automatic reindexing of B-tree indexes
Usage:
pg_auto_reindexer [OPTIONS]
Connection options:
-h, --host=HOSTNAME PostgreSQL host (default: /var/run/postgresql)
-p, --port=PORT PostgreSQL port (default: 5432)
-U, --username=USERNAME PostgreSQL user (default: postgres)
-d, --dbname=DBNAME PostgreSQL database for reindexing (default: all databases)
Reindexing options:
-b, --index-bloat=PERCENT Index bloat threshold in percent (default: 30)
-m, --index-minsize=MB Minimum index size in MB (default: 1)
-M, --index-maxsize=MB Maximum index size in MB (default: 1000000)
-s, --maintenance-start=HHMM Maintenance window start (24h format)
-S, --maintenance-stop=HHMM Maintenance window stop (24h format)
-t, --bloat-search-method=METHOD Bloat detection method: estimate | pgstattuple (default: estimate)
-l, --failed-reindex-limit=N Max reindex failures before skipping DB (default: 0)
-j, --jobs=N Number of parallel workers for reindex (default: 1)
-o, --sorting-order=(asc|desc) Defines the sorting order of indexes by size (default: asc)
-r, --disk-free-reserve=MB Disk free space reserve in MB (default: 5120)
Other options:
-v, --version Show version information and exit
-?, --help Show this help message and exit
Example:
pg_auto_reindexer --index-bloat=40 --maintenance-start=0100 --maintenance-stop=0600
Avoiding Repeated Password Prompts
Because pg_auto_reindexer opens multiple database connections (especially with parallel jobs), you’ll otherwise be prompted for the password repeatedly.
Create a .pgpass file for the postgres OS user:
sudo su postgres
nano ~/.pgpass
Add:
localhost:5432:odoo_production:postgres:your_password
The format for the pgpass file is like this :
host:port:database:user:password
Secure the file:
chmod 600 ~/.pgpass
Test:
psql -h localhost -U postgres -d odoo_production
Once this works without prompting, pg_auto_reindexer will also authenticate silently.
Basic Run
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production
Sample output:
Started index maintenance for database: odoo_production
1 indexes have been found for reindexing
[1/1] reindex index public.ir_model_data_module_name_uniq_index
Completed index maintenance (released: 1 MB)
This performs reindexing using default thresholds.
Understanding Each Reindexing Flag
Below are the flags you tested, explained individually.
-b - Index Bloat Threshold
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -b 10
Purpose:
Defines the minimum bloat percentage required before an index is selected.
Your result:
2026-02-03 18:20:12.096 INFO: Started index maintenance for database: odoo_production
2026-02-03 18:20:12.186 INFO: 4 indexes have been found for reindexing
2026-02-03 18:20:12.301 INFO: [1/4] reindex index public.idx_model2_value (current size: 42 MB)
2026-02-03 18:20:13.174 INFO: [1/4] completed reindex public.idx_model2_value (new size: 21 MB, reduced: 50%)
2026-02-03 18:20:13.290 INFO: [2/4] reindex index public.model2_pkey (current size: 42 MB)
2026-02-03 18:20:14.193 INFO: [2/4] completed reindex public.model2_pkey (new size: 21 MB, reduced: 50%)
2026-02-03 18:20:14.313 INFO: [3/4] reindex index public.idx_model3_value (current size: 42 MB)
2026-02-03 18:20:15.231 INFO: [3/4] completed reindex public.idx_model3_value (new size: 21 MB, reduced: 50%)
2026-02-03 18:20:15.342 INFO: [4/4] reindex index public.model3_pkey (current size: 42 MB)
2026-02-03 18:20:16.241 INFO: [4/4] completed reindex public.model3_pkey (new size: 21 MB, reduced: 50%)
2026-02-03 18:20:16.245 INFO: Completed index maintenance for database: odoo_production (released: 84 MB)
Lower values make the tool more aggressive. This is useful in lab environments or when cleaning heavily churned tables.
-m - Minimum Index Size (MB)
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -m 10
Purpose:
Skips small indexes. Only indexes larger than 10 MB are considered.
Your run reindexed two large indexes and released 42 MB. This option prevents wasting effort on tiny structures.
-M - Maximum Index Size (MB)
Limits the upper size boundary. This protects very large indexes from being rebuilt automatically.
Example:
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -M 5120
Indexes larger than 5 GB would be ignored.
-s and -S — Maintenance Window
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_demo_test -s 0100
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_demo_test -S 0600
Purpose:
Restricts execution to a defined time range.
Observed behavior:
Current time: 18:05 IST. This is outside of the maintenance window: 01:00-06:00. Exit.
This is essential for production systems where reindexing must happen only during off-peak hours.
-t - Bloat Detection Method
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -t pgstattuple
Purpose:
Switches from the default estimate-based approach to exact measurement using pgstattuple.
This requires:
CREATE EXTENSION pgstattuple;
Use this when accuracy matters more than speed.
-l - Failed Reindex Limit
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -l 2
Purpose:
If two reindex operations fail, the tool skips the database. This avoids endless retries in automated setups.
-j - Parallel Workers
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -j 2
Purpose:
Runs multiple reindex jobs concurrently.
This increases throughput but also increases load and connection count. Use cautiously on busy systems.
-o - Sorting Order
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_production -o desc
Purpose:
Processes indexes by size in descending order.
Largest indexes are rebuilt first, which can free substantial disk space early in the run.
-r - Disk Free Reserve
Example:
pg_auto_reindexer -h localhost -p 5432 -U postgres -d odoo_demo_test -r 20480
Purpose:
Ensures at least 20 GB remains free. Reindexing duplicates index data temporarily, so this guard prevents disk exhaustion.
Viewing Execution History via systemd
pg_auto_reindexer logs directly to journald.
To inspect recent activity:
sudo journalctl -t pg_auto_reindexer -n 100
Example output:
cybrosys@cybrosys:~$ sudo journalctl -t pg_auto_reindexer -n 100
Feb 03 18:02:41 cybrosys pg_auto_reindexer[285329]: Started index maintenance for database: odoo_demo_test
Feb 03 18:02:48 cybrosys pg_auto_reindexer[285471]: 1 indexes have been found for reindexing
Feb 03 18:03:45 cybrosys pg_auto_reindexer[286411]: Started index maintenance for database: odoo_demo_test
Feb 03 18:03:47 cybrosys pg_auto_reindexer[286445]: no bloat indexes were found
Feb 03 18:03:47 cybrosys pg_auto_reindexer[286448]: Completed index maintenance for database: odoo_demo_test (released: 0 MB)
Feb 03 18:05:22 cybrosys pg_auto_reindexer[287965]: Current time: 18:05 IST. This is outside of the maintenance window: 01:00-06:00. Exit.
Feb 03 18:05:28 cybrosys pg_auto_reindexer[288071]: Current time: 18:05 IST. This is outside of the maintenance window: 01:00-06:00. Exit.
Feb 03 18:06:37 cybrosys pg_auto_reindexer[289187]: Started index maintenance for database: odoo_demo_test
Feb 03 18:06:37 cybrosys pg_auto_reindexer[289195]: 1 indexes have been found for reindexing
Feb 03 18:06:37 cybrosys pg_auto_reindexer[289211]: [1/1] reindex index public.ir_model_data_module_name_uniq_index (current size: 1 MB)
Feb 03 18:06:37 cybrosys pg_auto_reindexer[289248]: [1/1] completed reindex public.ir_model_data_module_name_uniq_index (new size: 0 MB, reduced: 100%)
Feb 03 18:06:37 cybrosys pg_auto_reindexer[289251]: Completed index maintenance for database: odoo_demo_test (released: 1 MB)
Feb 03 18:06:59 cybrosys pg_auto_reindexer[289623]: Started index maintenance for database: odoo_demo_test
Feb 03 18:06:59 cybrosys pg_auto_reindexer[289629]: no bloat indexes were found
Feb 03 18:06:59 cybrosys pg_auto_reindexer[289633]: Completed index maintenance for database: odoo_demo_test (released: 0 MB)
This is valuable for auditing automated runs or cron jobs.
- pg_auto_reindexer performs selective, automated index maintenance
- Each flag controls candidate selection, execution order, or operational safety
- .pgpass is essential for smooth unattended runs
- Maintenance windows protect production workloads
- Journald integration provides clear operational visibility
Used carefully, this tool replaces manual REINDEX workflows with a controlled and repeatable process.