How to maintain Indexes and Statistics in a PostgreSQL database

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How to maintain Indexes and Statistics in a PostgreSQL database - 18 September 2026
• How to enable Data Masking in PostgreSQL - 16 September 2026
• How to do Bulk Export Import in PostgreSQL (pg_dump) - 14 September 2026

As we have seen in previous articles, the smooth operation of a database is not only ensured by backup and recovery mechanisms, but also requires constant maintenance. Over time and the execution of daily transactions, tables and indexes accumulate fragmentation, while statistics become outdated, resulting in a drop in performance. In this article we will see how we can keep the database in good condition by performing manual and automated maintenance procedures.

Statistics maintenance (analyze)

The PostgreSQL query optimizer relies on table statistics to decide the execution plan to follow. If the statistics are not up-to-date, the database may incorrectly choose sequential scans instead of index scans.

To update statistics across the database, we use the tool vacuumdb:

sudo -u postgres vacuumdb --all --analyze-in-stages

Rebuild indexes (reindex)

When many modifications are made to tables, indexes lose their efficiency due to fragmentation. To restore them to their original state, we can rebuild them.

To reindex the entire database:

sudo -u postgres vacuumdb --all --full

Or alternatively, if we want to rebuild a specific index without blocking users, we use the corresponding command CONCURRENTLY:

REINDEX INDEX CONCURRENTLY my_index_name;

*The choice CONCURRENTLY is not supported by VACUUM FULL, as the latter locks the table.

Maintenance Process Automation with Cronjob

Because these tasks should not be performed manually during peak hours, we can automate them by adding the relevant actions to the user's crontab. postgres.

To process cronjobs, we run:

sudo -u postgres crontab -e

And we add the tasks we want to be executed automatically:

# daily update statistics 02:00
0 2 * * * /usr/pgsql-14/bin/vacuumdb --all --analyze >> /var/log/postgres/maintenance-analyze.log 2>&1

# weekly full vacuum / maintenance Sunday 03:00
0 3 * * 0 /usr/pgsql-14/bin/vacuumdb --all --full >> /var/log/postgres/maintenance-full.log 2>&1

In this way, we ensure that the database remains efficient without requiring human intervention.