5 ms·

The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQ

The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQL as written. That is a very powerful property for an ad-hoc query language because it means that the user describes what they want, and the database itself takes care of the best way to get it to them. For programmers though, who usually want something predictable and repeatable to happen, it is not always ideal. ORMs try to make SQL seem more programmer-oriented by adding an extra layer of indirection over SQL. Unfortunately, what they actually tend to do is to add an additional black box enclosing SQL's black box, and that usually makes everything worse because it is now almost impossible to reason about how the database is actually going to execute a particular query. There doesn't seem to be an ideal solution to this problem. I think it is why so many databases tend to tweak SQL into something that better fits their implementation detail, and we end up with the explosion of almost-SQL languages that the author is complaining about. Personally, the least-bad solution I have found is the one you mention in your post: write your own SQL abstraction layer every time. At least that way, you can poke your screwdriver into the box and reason about what is going on in there.

akra 6y ago
There's been times where I've had to spend quite a bit of time trying to change the SQL in subtle ways to get an index to be used and do the join algorithm type I wanted; when it didn't want to do so. Usually improved performance by a massive factor for the datasets I was working with (e.g. 10x). The predictable performance as well was a big factor - I would prefer predictable and adequate performance over peak performance but high variability every time. In the end after all this frustration I wished I could have written the query plan directly, especially when I used Postgres with no query hints. And yes I'm aware of Postgres and all the tricks that you can do to make it do certain types of joins and such and I employed many of them (adding statistics, loose index scans, all the index types and others). IMO the potential this could open up is quite large given many databases all have the indexes/algorithms and many data structure types these days. Gluing them together in a performant way where you use the appropriate algorithm/data structure index for the data on tables/JSON blobs/etc seems to be the hard part right now that requires a lot of trial and error and learnings of the SQL optimizer to get right.

akra 6y ago

There's been times where I've had to spend quite a bit of time trying to change the SQL in subtle ways to get an index to be used and do the join algorithm type I wanted; when it didn't want to do so. Usually improved performance by a massive factor for the datasets I was working with (e.g. 10x). The predictable performance as well was a big factor - I would prefer predictable and adequate performance over peak performance but high variability every time. In the end after all this frustration I wished I could have written the query plan directly, especially when I used Postgres with no query hints. And yes I'm aware of Postgres and all the tricks that you can do to make it do certain types of joins and such and I employed many of them (adding statistics, loose index scans, all the index types and others). IMO the potential this could open up is quite large given many databases all have the indexes/algorithms and many data structure types these days. Gluing them together in a performant way where you use the appropriate algorithm/data structure index for the data on tables/JSON blobs/etc seems to be the hard part right now that requires a lot of trial and error and learnings of the SQL optimizer to get right.