5 ms·

This this this a thousand times this I've had buliding a system such as this for postgres on my bucket list for almost a decade now. Why is this not a thing

code_biologist 4y ago
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 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-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://www.percona.com/blog/2019/07/22/automatic-index-reco... https://github.com/ankane/dexter 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.
nikita 4y ago
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.
- eurasiantiger 4y ago
  So we’re back to Kubernetes! ;)
  - ElectricalUnion 4y ago
    Except that Kubernetes (at least last time I checked it did) more or less ignores exactly this hard part - keeping state sane and manageable - because it's hard and implementation/vendor dependent. The only reasonably ready "Kubernetes" project I saw attempting to solve this was SUSE/Rancher longhorn, and that was still somewhat incubating last time I checked.
lfittl 4y ago
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-postgres-pganalyze-indexing-engine https://pganalyze.com/blog/automatic-indexing-system-postgre...

code_biologist 4y ago

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 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-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://www.percona.com/blog/2019/07/22/automatic-index-reco... https://github.com/ankane/dexter 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.

nikita 4y ago

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.

eurasiantiger 4y ago
So we’re back to Kubernetes! ;)
- ElectricalUnion 4y ago
  Except that Kubernetes (at least last time I checked it did) more or less ignores exactly this hard part - keeping state sane and manageable - because it's hard and implementation/vendor dependent. The only reasonably ready "Kubernetes" project I saw attempting to solve this was SUSE/Rancher longhorn, and that was still somewhat incubating last time I checked.

lfittl 4y ago

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-postgres-pganalyze-indexing-engine https://pganalyze.com/blog/automatic-indexing-system-postgre...