Automatic Index Recommendations in PostgreSQL using pg_qualstats and hypopg

percona.com

29 pointsDowwie3 comments

There was an interesting related talk on SIGMOD this year[1], at which Microsoft presented how they take db-level index suggestions from many MS SQL databases in Azure, apply most of them to the databases on hidden replicas, do A/B testing on them (replaying recorded workloads on replicas, etc), and push only the proven suggestions to production.

Seems like tools described in the article could allow doing something similar with PostgreSQL at scale.

[1] https://www.microsoft.com/en-us/research/publication/automat...

I'd like to see more tooling applied to small-scale postgres operation as well. There are a lot of novice postgres users who find themselves managing postgres in production who could use this kind of helping hand. I also believe a lot of start-ups shy away from relational databases because they do a prototype with inefficient queries and poor indexing and the "slowness" pushes them to mongodb or the like for no good reason.

I was recently looking into this exact topic and found Dexter[1], which also uses HypoPG.

[1] https://github.com/ankane/dexter

Reply on news.ycombinator.com