There's a lot of good ecosystem stuff around this, though I personally can't use most of it as the HypoPG extension isn't on AWS RDS. :(

https://github.com/HypoPG/hypopg

HypoPG is a PostgreSQL extension adding support for hypothetical indexes.

An hypothetical -- or virtual -- index is an index that doesn't really exists, and thus doesn't cost CPU, disk or any resource to create. They're useful to know if specific indexes can increase performance for problematic queries, since you can know if PostgreSQL will use these indexes or not without having to spend resources to create them.

With one approach to actually implementing it here: https://www.percona.com/blog/2019/07/22/automatic-index-reco...

https://github.com/ankane/dexter is an autoindexer project that builds on hypopg, and I believe it can be configured to autocreate indices — it will only suggest them by default.

Doing this well for a production system is a hard problem. The sibling comment pointed out HypoPG and Dexter, but I'm not familiar with folks using a simple approach like the one Dexter implements on a production system. The human element is valuable when indexing, to correctly model the workload understanding and assess the trade-offs (index write overhead, etc).

For context, I've personally been working on an automatic indexing system for Postgres for more than a year now [1], and whilst I think what we have today is pretty useful, there is still work to be done. We've intentionally not yet enabled full automation (i.e. actual automatic creation of the indexes on the production database), because most people I've talked with prefer to review a recommendation and then apply it through their regular migration tooling, after making an assessment.

If you ever want to talk more about this, feel free to send me an email - I've spent a lot of time thinking about this topic :)

[1] https://pganalyze.com/blog/automatic-indexing-system-postgre...

One of the reasons is "safety". It's hard to make sure that adding an index won't have adverse effects on your app. Adding an index invalidates query plans and new plans may or may not be as good as the old ones.

I think the enabling technology for this is branching and the ability to "fork" a database AND the workload to prove that adding an index doesn't break things.