4 ms·

A lower-hanging fruit may be a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning

A lower-hanging fruit may be a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning, and query planning based on the actual database activity. This could even be something that operates as a layer on top of Postgres, although it would probably need more precise data than what is currently available through stats tables, and since Postgres doesn't have query hints there's no way to modify query plans. It would also need the look at logs to see what the most frequent queries are, and possibly require a dedicated slave to "test run" queries on to gauge whether it has found an optimal solution (or maybe do it on the actual database during quiet hours). AI would definitely be interesting, though.

derefr 9y ago
I've always wondered why there doesn't exist something like this, but architected like Apache's mod_security: something you run for a while in "training" mode to let it learn your usage patterns, which then spits out a hints file (in mod_security's case, a behaviour whitelist; in this case, a migration to add a set of indexes + views). As a bonus, it could (like mod-security) also have a "production mode" where the DB consumes that hints file and applies the instructions in it as an overlay, rather than as a permanent patch to your schema; that way, you'd be able to re-train after changes and then swap out the hints in production by just swapping out the file and HUPping the DB server (which would hopefully do the least-time migration to turn the old indexes into the new indexes), rather than needing to scrub out all the old indexes before building new ones.
- Drdrdrq 9y ago
  It exists for MongoDB (Dex).
- tatersolid 9y ago
  This is also exactly what Microsoft SQL Server has done _since 2005_ with dynamic managements views. It shows on a running system what indexes would be optimal for an actual workload.
dantiberian 9y ago
> a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning, and query planning based on the actual database activity. This is the core idea behind Peloton, a research database from CMU: http://pelotondb.io http://pelotondb.io. Andy Pavlo has a really interesting, and entertaining talk about it https://www.youtube.com/watch?v=mzMnyYdO8jk https://www.youtube.com/watch?v=mzMnyYdO8jk.
anonetal 9y ago
There is lot of somewhat-researchy work on this topic, the primary one being the effort at Microsoft Research, called AutoAdmin, that started about 20 years ago. They looked at automatically creating indexes, views, histograms, and stuff like that. Peloton@CMU is a more recent incarnation of the same idea with newer tradeoffs. Although it might sound easy, this turns out to be a very challenging problem for many technical reasons. A primary reason for it not getting traction in practice is also that, database administrators don't like an automated tool messing with their setup and potentially nullifying all the tricks they might have played to improve the performance. This was especially true in big DB2/Oracle deployments, but is increasingly less true, which has opened it up for innovations in the last few years.
mwpmaybe 9y ago
It only handles indexes, so it's just a start, but have you seen HypoPG[0] and Dexter[1]? 0. https://github.com/dalibo/hypopg https://github.com/dalibo/hypopg 1. https://github.com/ankane/dexter https://github.com/ankane/dexter
- thom 9y ago
  Kinda wish someone would package up capabilities like these as nicely as the SQL Server Profiler and Index Tuning Wizard. But even so, real fixes almost always lie a level above - you can write semantically identical SQL (just like any other language) that has wildly different performance characteristics. That's annoying for a supposedly declarative language. And nothing will save you if you have just messed up your schema - something that tells you do denormalise stuff to get it on the same page of data because of such and such a query load would also help.
thom 9y ago
Yes. I have views in my database, and CPU cycles (and disk) to spare. Please, please use them.

derefr 9y ago

I've always wondered why there doesn't exist something like this, but architected like Apache's mod_security: something you run for a while in "training" mode to let it learn your usage patterns, which then spits out a hints file (in mod_security's case, a behaviour whitelist; in this case, a migration to add a set of indexes + views). As a bonus, it could (like mod-security) also have a "production mode" where the DB consumes that hints file and applies the instructions in it as an overlay, rather than as a permanent patch to your schema; that way, you'd be able to re-train after changes and then swap out the hints in production by just swapping out the file and HUPping the DB server (which would hopefully do the least-time migration to turn the old indexes into the new indexes), rather than needing to scrub out all the old indexes before building new ones.

Drdrdrq 9y ago
It exists for MongoDB (Dex).
tatersolid 9y ago
This is also exactly what Microsoft SQL Server has done _since 2005_ with dynamic managements views. It shows on a running system what indexes would be optimal for an actual workload.

dantiberian 9y ago

> a dynamic profiler that continuously analyzes query patterns, and automatically reorganizes indexes, table clustering, partitioning, and query planning based on the actual database activity. This is the core idea behind Peloton, a research database from CMU: http://pelotondb.io http://pelotondb.io. Andy Pavlo has a really interesting, and entertaining talk about it https://www.youtube.com/watch?v=mzMnyYdO8jk https://www.youtube.com/watch?v=mzMnyYdO8jk.

anonetal 9y ago

There is lot of somewhat-researchy work on this topic, the primary one being the effort at Microsoft Research, called AutoAdmin, that started about 20 years ago. They looked at automatically creating indexes, views, histograms, and stuff like that. Peloton@CMU is a more recent incarnation of the same idea with newer tradeoffs. Although it might sound easy, this turns out to be a very challenging problem for many technical reasons. A primary reason for it not getting traction in practice is also that, database administrators don't like an automated tool messing with their setup and potentially nullifying all the tricks they might have played to improve the performance. This was especially true in big DB2/Oracle deployments, but is increasingly less true, which has opened it up for innovations in the last few years.

mwpmaybe 9y ago

It only handles indexes, so it's just a start, but have you seen HypoPG[0] and Dexter[1]? 0. https://github.com/dalibo/hypopg https://github.com/dalibo/hypopg 1. https://github.com/ankane/dexter https://github.com/ankane/dexter

thom 9y ago
Kinda wish someone would package up capabilities like these as nicely as the SQL Server Profiler and Index Tuning Wizard. But even so, real fixes almost always lie a level above - you can write semantically identical SQL (just like any other language) that has wildly different performance characteristics. That's annoying for a supposedly declarative language. And nothing will save you if you have just messed up your schema - something that tells you do denormalise stuff to get it on the same page of data because of such and such a query load would also help.

thom 9y ago

Yes. I have views in my database, and CPU cycles (and disk) to spare. Please, please use them.