I realise I'm straying a bit from core OLTP stuff but also I think removing the historical need for a separate OLAP database is something modern systems should address. Off the top of my head:
1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions).
2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB.
3) Fine tuned control over query plans if I want it.
3) Fine tuned control over query plans if I want it.
I feel like when most people say this, what they really want is a better query planner.
The optimum query plan depends on a lot of dynamically changing factors: system load, free RAM, and of course the data in the tables themselves. Any hints we give the query planner are going to help at certain times and be somewhere between "suboptimial" and "disasterous" at most other times.
It's certainly true that the query planners in major RDBMS could be better or, at least, give insight into why they made the choices they did.
It would be cool if EXPLAIN ANALYZE also perhaps showed other query plans the planner considered but discarded, and why. Imagine if the planner tried multiple plans and adjusted itself accordingly.
For Postgres in particular, I think the user-specified costs in pg.conf like `seq_page_cost` and `random_page_cost` feel like one obvious area for improvement: why am I guessing at these values? Postgres should be determining and adjusting these costs on the fly. But, I could be wrong.
> The optimum query plan depends on a lot of dynamically changing factors
Except … I’m part of a large organization with a very thorough incident post-mortem process and I’ve read a lot of analyses that end up blamed on the dreaded “query plan flip.” This ends up being an unplanned change that has an unexpected perf cost (and no real “rollback”) and causes an outage. The lesson I’ve seen learned over and over is to have very good understanding (and constant re-evaluation) of your hot queries’ perf , and to lock the query plan so it can only change when you intend it to.
Yeah we've had to add some weekly forced statistics recalculation on certain key tables just to prevent the large customers from going down due to indexer suddenly not wanting to use an index.
Hasn't happened often, but the few occasions have of course been at the worst possible time.
> It would be cool if EXPLAIN ANALYZE also perhaps showed other query plans the planner considered but discarded, and why. Imagine if the planner tried multiple plans and adjusted itself accordingly.
The interesting point is in that regard MySQL is really better than PostgreSQL. MySQL can give you more execution plans it evaluated and tell you why it didn't use them (cost value is higher) and even tell you why it didn't use a specific index it. Combined with the enforcing specific indexes you can sometimes trick it into a more efficient query plan even if it believed to be worse.
But to be honest, PostgreSQL query planner is a lot more intelligent and does stupid things very seldom. And the ability to instruct PostgreSQL collecting more statistics on some attributes (https://www.postgresql.org/docs/13/sql-createstatistics.html) is a huge improvement to getting PostgreSQL make more intelligent plans.
What i really miss in PostgreSQL is:
* Seeing other query plans it discarded as you described to get a feeling how to hint PostgreSQL into a direction if the query planner is doing stupid things
* The ability to enforce specific query plans. The PostgreSQL devs stated they are against this but pg_hint_plan is really usefull and i was able to drastically improve some very complex queries.
It's true a better query planner is what I want, i.e. one that always does what I want it to do. That not being the case, I will settle for being in control, footguns and all.
I agree the config is hard to wrangle though, you effectively find yourself doing grid search with a bunch of common workloads, it does feel like something the machine should be doing for me (the most common config variable we end up tweaking is "how much money we give Amazon").
Another interesting approach is HyPer[1]. HyPer uses many new techniques to combine OLTP and OLAP in one database. For example, to achieve good OLAP performance, a columnar storage layout is used, but the columns are chunked for locality to achieve good OLTP performance at the same time. OLTP queries are executed in memory, but cold data that is not used for OLTP is automatically compressed and moved to secondary storage for OLAP.
Not much one can do with Hyper as it sold the commercial license to Tableau and I can’t find any mention of an OSS version to play with anywhere on the site.
1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions).
2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB.
3) Fine tuned control over query plans if I want it.
4) Probably very deep Apache Arrow integration.