{"repo":"dataegret/pg_index_watch","free":true,"listed":false,"github":"https://github.com/dataegret/pg_index_watch","clone":"git clone https://github.com/dataegret/pg_index_watch.git","description":"Utility for automatical rebuild bloated indexes (a-la smart autovacuum to deal with index bloat) in PostgreSQL.","language":"PLpgSQL","stars":62,"topics":["postgresql"],"license":"BSD-3-Clause","category":"databases-storage","readme_excerpt":"pg index watch Utility for automatic rebuild of bloated indexes in PostgreSQL (a-la smart autovacuum, but for index bloat). Code version: 1.14 ( SELECT index watch.version(); ) Table structure version: 14 ( SELECT version FROM index watch.tables version; ) License: BSD 3-Clause --- Table of contents 1. Purpose 2. How it works 3. Requirements 4. Installation 5. First run 6. Scheduled runs 7. Dry-run and force modes 8. Reindex decision flow 9. Configuration 10. Monitoring and history 11. API reference 12. Limitations 13. Upgrade 14. Support 15. Todo --- Purpose Uncontrolled index bloat on frequently updated tables is a known issue in PostgreSQL. Built-in autovacuum does not reliably shrink indexes, regardless of its settings. pg index watch watches index bloat across local databases and rebuilds indexes with REINDEX INDEX CONCURRENTLY when estimated bloat exceeds a configurable threshold. --- How it works PostgreSQL 12+ provides REINDEX CONCURRENTLY — a mostly lock-free way to rebuild indexes. The remaining problem is deciding when an index is bloated enough to rebuild. pg index watch uses a simple and cheap signal that does not require pgstattuple : 1. Index size ( pg relation size ) 2. Estimated number of index tuples ( pg class.reltuples , kept up to date by autovacuum/autoanalyze) For a healthy index the ratio size / tuples is roughly stable. After a clean rebuild the tool stores this ratio as best ratio . Later it estimates bloat as: If estimated bloat is at least index re","default_branch":null,"files":null,"tree":[],"storefront":"/r/dataegret","claimed":false,"request_supported":{"post":"https://gitbuyer.com/r/dataegret/pg_index_watch/request-supported","requests":0},"note":"indexed from public GitHub; nothing is for sale on this page. Clone it from GitHub. Paid listings live at /search."}