Let’s delve into exploring pg_index_watch. In this instalment, I will guide you through the rationale behind its creation and explain its operational nuances.
Meet pg_index_watch – a utility for automagical rebuild of bloated indexes, an absolutely handly tool designed to tackle index bloat on PostgreSQL tables with high velocity of UPDATEs.
Index bloat on frequently updated tables is a common problem in PostgreSQL. Even with autovacuum, which is built-in, index bloat often persists despite different settings. pg_index_watch steps in to address this by automatically rebuilding indexes when necessary, offering a solution where autovacuum falls short.
PostgreSQL 12 brought about the introduction of REINDEX CONCURRENTLY, offering a safer method to rebuild indexes and consequently reduce bloat. However, a lingering question remained:
How do we determine when an index is bloated and if it needs to be rebuilt?
We require a straightforward statistical model to help us gauge the level of index bloat without the necessity of conducting a thorough review of the entire index.
pg_index_watch tackles this challenge with a unique strategy:
Utilizing PostgreSQL’s capabilities, pg_index_watch leverages two key metrics:
- The count of rows within the index (accessible via
pg_class.reltuples) - Size of the index itself
By monitoring this ratio, pg_index_watch can detect when an index is bloated – indicated by a doubling of the ratio from its regular state. This signals that the index is nearing a 100% bloat rate, prompting pg_index_watch to spring into action, initiating a reindexing process without requiring manual intervention from database administrators.
For installing and using pg_index_watch, you’ll need:
- PostgreSQL version 12.0 or higher
- Superuser access and Passwordless access to the database as a superuser
Recommendations:
If your server can handle it, consider setting up multiple (i.e., non-zero) max_parallel_maintenance_workers to speed up index rebuilds.
Installation (as a PostgreSQL user):
Obtain the pg_index_watch code by cloning the repository from GitHub using the command
git clone https://github.com/dataegret/pg_index_watch
Navigate into the pg_index_watch directory.
Then, execute the SQL script:
index_watch_tables.sql
to create the necessary table structure, followed by importing the code, including stored procedures, using index_watch_functions.sql via:psql -1 -d osdb_index_bloat -f index_watch_functions.sql
During the initial launch of pg_index_watch, it’s imperative to prepare for a significant transformation. At this crucial stage, all indexes exceeding 10MB (default setting) undergo a comprehensive rebuilding process. This meticulous approach guarantees that your indexes are finely tuned for peak performance.
However, be mindful that this process may require several hours or even days to complete, particularly for larger databases. We recommend initiating this process manually to ensure a seamless transition.
nohup psql -d osdb_index_bloat -qt -c "CALL index_watch.periodic(TRUE);" >> index_watch.log
Continuous Monitoring:
Following the initial launch, pg_index_watch seamlessly transitions into continuous monitoring mode. From this point forward, only newly created or bloated indexes trigger alerts, ensuring ongoing database optimisation. Furthermore, pg_index_watch offers the flexibility to utilise current index sizes as a baseline, further enhancing its adaptability to your database’s evolving needs.
Setting Up Cron:
To uphold consistent performance, establishing a cron schedule for regular monitoring is essential. Whether scheduling daily checks at midnight or hourly scans during periods of high activity, cron ensures that pg_index_watch remains vigilant without impeding critical maintenance tasks.
00 * * * * psql -d osdb_index_bloat -AtqXc "select not pg_is_in_recovery();" | grep -qx t || exit; psql -d osdb_index_bloat -qt -c "CALL index_watch.periodic(TRUE);"
In Conclusion
With pg_index_watch by your side, attaining peak PostgreSQL performance becomes a straightforward endeavour. As you embark on this path, have confidence that your database’s well-being and effectiveness are being diligently monitored. Embrace the benefits of continuous monitoring, and witness the remarkable enhancements that pg_index_watch brings to your PostgreSQL databases.
Leave a Reply