4 ms·

it's "absurdly simple", but then you get presented with a bunch of weird things that look like abstraction ceilings like "oh you can't refer to the select claus

by rtpg 2y ago

it's "absurdly simple", but then you get presented with a bunch of weird things that look like abstraction ceilings like "oh you can't refer to the select clause alias you made in the filter because despite that showing up first lexicographically the ordering is different" and "oh you don't have to refer to the table name except when you do because of ambiguity issues". I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like (resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not"). I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes.

iTokio 2y ago
SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex. To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..). And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected). Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.
- rtpg 2y ago
  My honest experience in a skilled team has been that people more or less start off thinking "OK, what indices do we need to make this performant", work off of that, and then in the end try to have queries that hit those ones. I understand the value of full declarative planning with heuristics, but sometimes the query writers do in fact have a better understanding of the data that will go in. And beyond that, having consistent plans is actually better in some ideologies! Instead of a query suddenly changing tactics in a data- and time-dependent way, having consistent behavior at the planning phase means that you can apply general engineering maintenance tactics. Keep an eye on query perf, improve things that need to be improved... there are still the possibility of hitting absolutely nasty issues, but the fact that every SQL debugging session starts with "well we gotta run EXPLAIN first after the fact" is actually kind of odd!
sgarland 2y ago
> "oh you don't have to refer to the table name except when you do because of ambiguity issues" Maybe it's easier if you think of it like helpful syntactic sugar? > I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like That sounds like imperative programming, which is fine for most things, but [generally] not RDBMS (or IaC, but that's a completely separate topic). You can't possibly know the state of a given table or index – the cardinality of columns, the relative grouping of tuples to one another, etc. While you can hint at index usage (natively with MySQL, via extension with Postgres), that's as close as the planner will let you get, because it knows these things better than you do. > resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not" More frequently, I see "programmer writes query that is super non-performant because they haven't read the docs, and don't know the requirements for the planner to use the index." A few examples: * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway. * Indices on columns are unlikely to be used for aggregations like GROUP BY, except in very specific circumstances for MySQL [0] (I'm not sure what limitations Postgres has on this). * Not knowing that a leading wildcard on a predicate (e.g. `WHERE user_name LIKE '%ara'`) will, except under two circumstances [1], skip using an index. > despite people intuitively understanding B-Trees or indexes. You say that, but the sheer number of devs I've talked to who are unaware that UUIDv4 is an abysmally bad choice WRT performance for indices – primary or secondary – says otherwise. [0]: https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html https://dev.mysql.com/doc/refman/8.4/en/group-by-optimizatio... [1]: Postgres can create trigram indices, which can search with these, at the expense of the index being quite large. Both MySQL and Postgres can make use of the REVERSE() function to create a reverse index on the column, which can then be used in a query with the username also reversed.
- rtpg 2y ago
  In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs. The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)
golergka 2y ago
> I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes. That's one of the best things about SQL, it's declarative nature. I describe the end result, data I want to receive — not the instructions on how it should be done. There's no control flow, there's no program state, which means that my mental model of it is so much simpler.
- lucianbr 2y ago
  Yes but for performance you need to know how it is done, which defeats the declarative point. I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.
  - sgarland 2y ago
    Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge. I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.
  - paulmd 2y ago
    > I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way. While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work. SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.
    - sgarland 2y ago
mike_hearn 2y ago
There's an interesting experiment in this direction here: https://github.com/permazen/permazen https://github.com/permazen/permazen It maps Java objects to a scalable transactional K/V store of your choice, and handles things like indexing, schema migrations and the rest for you. You express query plans by hand using the Java collections and streams framework.
flyingsilverfin 2y ago
I wanted to jump in here and say that what we're working on at typedb.com, in our 3.0 version (coming soon in alpha!), is that we're taking our earlier database query language and making it much more Programming-like: functions, errors containing stack traces, more sophisticated type inference, queries as streams/pipelines... I think it's super exciting and has a huge horizon for where it could go by meshing more ideas from PL design :) Incidentally I think it also addresses what a lot of the comments here are talking about: not learning JOINs, indexing, build-in relation cardinality constraints, etc, but that's a separate point!
- sgarland 2y ago
  > TypeDB models are described by types, defined in a schema as templates for data instances, analogous to classes. Each user-defined type extends one of three root types: entity, relation, and attribute, or a previously user-defined type. This sounds like an EAV table, which is generally a bad idea. Re: types, Postgres allows you to define whatever kind of type you’d like. Also re: inheritance, again, Postgres tables can inherit a parent. Not just FK linking, but literally schema inheritance. It’s rare that you’d want to do this, but you can. In general, my view is that the supposed impedance mismatch is a good thing, and if anything, it forces the dev to think about less complicated ways to model their data. The relational model has stuck around because it’s incredibly good, not because nothing better has come around. EDIT: this came across as quite harsh, and I’m sorry for the tone. Making a new product is hard. I’m just very jaded about anything trying to replace SQL, because I love it, it’s near-universal, and it hasn’t (nor is likely to) gone anywhere for quite some time.

by rtpg 2y ago

iTokio 2y ago

SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex. To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..). And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected). Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.

rtpg 2y ago
My honest experience in a skilled team has been that people more or less start off thinking "OK, what indices do we need to make this performant", work off of that, and then in the end try to have queries that hit those ones. I understand the value of full declarative planning with heuristics, but sometimes the query writers do in fact have a better understanding of the data that will go in. And beyond that, having consistent plans is actually better in some ideologies! Instead of a query suddenly changing tactics in a data- and time-dependent way, having consistent behavior at the planning phase means that you can apply general engineering maintenance tactics. Keep an eye on query perf, improve things that need to be improved... there are still the possibility of hitting absolutely nasty issues, but the fact that every SQL debugging session starts with "well we gotta run EXPLAIN first after the fact" is actually kind of odd!

sgarland 2y ago

> "oh you don't have to refer to the table name except when you do because of ambiguity issues" Maybe it's easier if you think of it like helpful syntactic sugar? > I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like That sounds like imperative programming, which is fine for most things, but [generally] not RDBMS (or IaC, but that's a completely separate topic). You can't possibly know the state of a given table or index – the cardinality of columns, the relative grouping of tuples to one another, etc. While you can hint at index usage (natively with MySQL, via extension with Postgres), that's as close as the planner will let you get, because it knows these things better than you do. > resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not" More frequently, I see "programmer writes query that is super non-performant because they haven't read the docs, and don't know the requirements for the planner to use the index." A few examples: * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway. * Indices on columns are unlikely to be used for aggregations like GROUP BY, except in very specific circumstances for MySQL [0] (I'm not sure what limitations Postgres has on this). * Not knowing that a leading wildcard on a predicate (e.g. `WHERE user_name LIKE '%ara'`) will, except under two circumstances [1], skip using an index. > despite people intuitively understanding B-Trees or indexes. You say that, but the sheer number of devs I've talked to who are unaware that UUIDv4 is an abysmally bad choice WRT performance for indices – primary or secondary – says otherwise. [0]: https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html https://dev.mysql.com/doc/refman/8.4/en/group-by-optimizatio... [1]: Postgres can create trigram indices, which can search with these, at the expense of the index being quite large. Both MySQL and Postgres can make use of the REVERSE() function to create a reverse index on the column, which can then be used in a query with the username also reversed.

rtpg 2y ago
In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs. The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)

golergka 2y ago

> I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes. That's one of the best things about SQL, it's declarative nature. I describe the end result, data I want to receive — not the instructions on how it should be done. There's no control flow, there's no program state, which means that my mental model of it is so much simpler.

lucianbr 2y ago
Yes but for performance you need to know how it is done, which defeats the declarative point. I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.
- sgarland 2y ago
  Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge. I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.
- paulmd 2y ago
  > I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way. While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work. SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.
  - sgarland 2y ago

mike_hearn 2y ago

There's an interesting experiment in this direction here: https://github.com/permazen/permazen https://github.com/permazen/permazen It maps Java objects to a scalable transactional K/V store of your choice, and handles things like indexing, schema migrations and the rest for you. You express query plans by hand using the Java collections and streams framework.

flyingsilverfin 2y ago

I wanted to jump in here and say that what we're working on at typedb.com, in our 3.0 version (coming soon in alpha!), is that we're taking our earlier database query language and making it much more Programming-like: functions, errors containing stack traces, more sophisticated type inference, queries as streams/pipelines... I think it's super exciting and has a huge horizon for where it could go by meshing more ideas from PL design :) Incidentally I think it also addresses what a lot of the comments here are talking about: not learning JOINs, indexing, build-in relation cardinality constraints, etc, but that's a separate point!

sgarland 2y ago
> TypeDB models are described by types, defined in a schema as templates for data instances, analogous to classes. Each user-defined type extends one of three root types: entity, relation, and attribute, or a previously user-defined type. This sounds like an EAV table, which is generally a bad idea. Re: types, Postgres allows you to define whatever kind of type you’d like. Also re: inheritance, again, Postgres tables can inherit a parent. Not just FK linking, but literally schema inheritance. It’s rare that you’d want to do this, but you can. In general, my view is that the supposed impedance mismatch is a good thing, and if anything, it forces the dev to think about less complicated ways to model their data. The relational model has stuck around because it’s incredibly good, not because nothing better has come around. EDIT: this came across as quite harsh, and I’m sorry for the tone. Making a new product is hard. I’m just very jaded about anything trying to replace SQL, because I love it, it’s near-universal, and it hasn’t (nor is likely to) gone anywhere for quite some time.