懒人功能之| PostgreSQL索引自动维护

自驱动数据库功能之一 | PostgreSQL索引自动维护

随着AI Agent的兴起, AI4DB领域之一的self-driving数据库概念也被重新提及.

虽然这么多年过去了, 似乎自驱动数据库只有Oracle做得比较好, 不需要消耗用户太多的管理精力. 还有就是云数据库, 少量人员管理大规模的数据库实例, 也算self-driving数据库范畴吧.

但是self-driving肯定远不止于此. 关于self-driving的讨论可参考:

• https://postgres.fm/episodes/self-driving-postgres
• https://postgres.ai/blog/20250725-self-driving-postgres

本文仅介绍一个有趣的项目 : pg_index_pilot

项目目标概括起来分3类:

• AR , 自动reindex膨胀的索引
• AIR , 自动发现无用的索引并删除
• AIC&O , 自动创建索引以优化数据库SQL性能

通过pg_cron或外部的定时任务, 定期扫描数据库系统表, 发现膨胀超过阈值的索引( https://github.com/ioguix/pgsql-bloat-estimation ), 通过dblink插件异步执行reindex concurrently任务.

项目处于初期阶段, 目前仅实现了部分AR能力, AIR和AIC&O都还没有开始.

详见roadmap:

The Roadmap covers three big areas:

1. [ ] "AR": Automated Reindexing

• [x] RDS and Aurora (see RDS Setup below)
• [ ] CloudSQL
• [ ] Supabase
• [ ] Crunchy Bridge
• [ ] Azure

1. [x] original implementation (pg_index_pilot) – requires initial full reindex
2. [x] non-superuser mode for cloud databases (AWS RDS, Google Cloud SQL, Azure)
3. [x] flexible connection management for dblink
4. [ ] API for stats obtained on a clone (to avoid full reindex on prod primary)

1. [x] Maxim Boguk's bloat estimation formula – works with any type of index, not only btree
2. [ ] Traditional bloat estimatation (ioguix; btree only)
3. [ ] Exact bloat analysis (pgstattuple; analysis on clones)
4. [x] Tested on managed services
5. [ ] Integration with postgres_ai monitoring
6. [ ] Resource-aware scheduling, predictive maintenance windows (when will load be lowest?)
7. [ ] Coordination with other ops (backups, vacuums, upgrades)
8. [ ] Parallelization and throttling (adaptive)
9. [ ] Predictive bloat modeling
10. [ ] Learning & Feedback Loops: learning from past actions, A/B testing and "what-if" simulation (DBLab)
11. [ ] Impact estimation before scheduling
12. [ ] RCA of fast degraded index health (why it gets bloated fast?) and mitigation (tune autovacuum, avoid xmin horizon getting stuck)
13. [ ] Self-adjusting thresholds

1. [ ] Unused indexes
2. [ ] Redundant indexes
3. [ ] Invalid indexes (or, per configuration, rebuilding them)
4. [ ] Advanced scoring; suboptimal / rarely used indexes cleanup; self-adjusting thresholds
5. [ ] Forecasting of index usage; seasonal pattern recognition
6. [ ] Impact estimation before removal; "what-if" simulation (DBLab)

1. [ ] Index recommendations (including multi-column, expression, partial, hybrid, and covering indexes)
2. [ ] Index optimization according to configured goals (latency, size, WAL, write/HOT overhead, read overhead)
3. [ ] Experimentation (hypothetical with HypoPG, real with DBLab)
4. [ ] Query pattern classification
5. [ ] Advanced scoring; cost/benefit analysis
6. [ ] Impact estimation before operations; "what-if" simulation (DBLab)

参考

https://gitlab.com/postgres-ai/pg_index_pilot/-/blob/main/README.md

https://gitlab.com/postgres-ai/pg_index_pilot/-/blob/main/TODO.md

https://gitlab.com/postgres-ai/pg_index_pilot

https://github.com/xataio/agent

https://postgres.fm/episodes/self-driving-postgres

https://postgres.ai/blog/20250725-self-driving-postgres

《这才靠谱数据库MCP Server的样子》

《PostgreSQL 索引推荐 - HypoPG , pg_qualstats》

《解读用户最常问的PostgreSQL垃圾回收、膨胀、多版本管理、存储引擎等疑惑 - 经典》

《PostgreSQL 收缩膨胀表或索引 - pg_squeeze or pg_repack》

《PostgreSQL 如何精确计算表膨胀(fsm,数据块layout讲解) - PostgreSQL table exactly bloat monitor use freespace map data》

《PostgreSQL pgstattuple - 检查表的膨胀情况、dead tuples、live tuples、freespace》