5 ms·

> People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actua

> People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. That's because, inexplicably, devs by and large don't know SQL, and don't want to learn it. It's an absurdly simple language that should take a competent person a day to get a baseline level of knowledge, and perhaps a week to be extremely comfortable with. As an aside, something you can do is create views (non-materialized) for whatever queries are desired. The counter-arguments to this are that is slows development velocity, but then, so does devs who don't know how to do joins.

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.
pif 2y ago
> devs by and large don't know SQL, and don't want to learn it One huge problem is that learning SQL will take you nowhere, unless your schema consists of a few tables with a handful of rows at most. There is no standard SQL, and there is no serious application development without having to delve into the sanctum sanctorum of your database vendor.
- sgarland 2y ago
  I have worked in massive DBs (tables with billions of rows) in both Postgres and MySQL. I occasionally have to look at docs to figure out if what I want to do is supported by the particular version, but that’s about it. There is a standard SQL, and vendors are free to add to it. Generally speaking, though, you can get quite far with the standard. > there is no serious application development without having to delve into the sanctum sanctorum of your database vendor. OK? I could say the same thing about needing to intimately know the differences in C++ versions. Yes, you should know your tools well.

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.

pif 2y ago

> devs by and large don't know SQL, and don't want to learn it One huge problem is that learning SQL will take you nowhere, unless your schema consists of a few tables with a handful of rows at most. There is no standard SQL, and there is no serious application development without having to delve into the sanctum sanctorum of your database vendor.

sgarland 2y ago
I have worked in massive DBs (tables with billions of rows) in both Postgres and MySQL. I occasionally have to look at docs to figure out if what I want to do is supported by the particular version, but that’s about it. There is a standard SQL, and vendors are free to add to it. Generally speaking, though, you can get quite far with the standard. > there is no serious application development without having to delve into the sanctum sanctorum of your database vendor. OK? I could say the same thing about needing to intimately know the differences in C++ versions. Yes, you should know your tools well.