It sounds like Malloy might be able to find a niche amongst some casual users, but it will never gain traction amongst DBAs. My reasoning is as follows.
Being a DBA is more than just writing queries, you have to be able to maintain the database, maintain the security settings, and (most importantly for this discussion) do performance tuning.
Performance tuning is incredibly important; I have, personally, seen a query that was running in only a few seconds have an absolutely catastrophic performance drop off when a few extra records were added. I had to get help from the DBA to find where it was going wrong and rewrite it to get the performance back to a reasonable state again. (I am talking about query run time going from seconds to minutes.)
You can't do performance tuning in Malloy I doubt; You'll be needing to run the analyser and rewriting the produced SQL. Since you'll have to know SQL really well to do this, why bother learning Malloy as well?
You could make the same argument that we all should learn assembly language, because when our C compilers produce bad assembly we'll have to rewrite it by hand. But I haven't written assembly in 30 years, and even then it was just for fun.
As it turns out, having high-level abstractions means that in many ways it's easier to do automatic optimization. I look forward to automatic data-driven database tuning and query optimization.
Postgres has done an incredible job optimizing both the overall performance of queries and the query planner - but these things make mistakes still and being able to fix those mistakes can make the difference between a two minute query and a twenty milisecond query - this comes with the fact that complex database operations can usually make or break overall response times, and these optimizations usually depend on statistic accuracy which can be hard to ensure.
If SQL optimization was as good as compilers I'd be all on this train, but I just don't think we're there yet.
Your argument seems to assume that SQL is assembly language; it is not! The query plan or internal bytecode is the 'assembly language' of an RDMS, therefore SQL is more akin to the RDMS version of C, rather than assembly language.
So Malloy is akin to a language that compiles to C, which is then compiled to assembly. Does this seem like a worthwhile pursuit to you?
Personally, I don't think any SQL replacement will really take off unless someone can get the major RDMS makers to support it natively.
Just to be clear, I'm not saying it's not a worthwhile pursuit: SQL does have many problems and could do with a replacement that supports more modern approaches to problem solving. What I am saying is that unless someone comes up with something that can automatically do performance tuning for you, or that is supported natively by the RDMS, then SQL replacements will continue to fail to gain traction.
People do this, e.g. building a cost-based optimizer for queries, but it can take over 2 years for it to really get pretty good. And even then it can only go so far and you'll still want to manually tune certain queries.
I still don’t understand why so many people seem to talk about replacing SQL. It’s not complicated, can be learned in a few hours, it’s relatively easy to read and it fairly portable across databases and stacks.
It’s been the most important detail of my job for the last 20 years.
SQL has a couple legitimate limitations as a query language, largely because we expect databases to do so much more today than when it was designed. I've been both a heavy user of SQL and implementor of SQL databases focused on data models like graph, spatial, et al, so I feel the pain from both directions.
Some useful things are difficult to effectively express in SQL because it was not designed to make them expressible. Adding support after the fact introduced a lot of compromises and complexity; you can tell which parts of SQL are second-class citizens. SQL would have been designed differently if its original scope had been broader and it is an inelegant mess for some types of data models. You can make it work but it isn't pleasant. Attempts to address these gaps explain much of why SQL databases all have their own non-standard SQL dialect, and it has made SQL effectively non-portable across databases.
In principle, a more modern query language could elegantly address the much broader scope of how we use databases today in a standardized way, instead of the zoo of non-standard extensions and mismatched parts that are grafted onto specific SQL implementations.
because as a language it's quite complicated, except in simple cases, the syntax is completely arbitrary and randomly injects keywords willy-nilly in an absurd attempt at being "english-readable"
because it lacks composability, making dynamic query generation a massive pain in the ass, as well as manual edits of queries.
because watching DBA's flailing strings about, smashing them together like apes with stones, just so they can struggle to feed it into an EXEC SQL call is perhaps one of the greatest tragedies of our programmatic times.
because it's not at all portable, with every database arbitrarily extending the SQL standard left-and-right, and even basic conveniences and functions end up breaking your portability goals; the ANSI SQL standard defines less a standard and more of an aesthetic mold for each dialect to try to match against.
because it lacks any portable ability to extend the language, with nearly every function added not as a stdlib function but instead as part of the language itself, often with custom keywords to go with it; there's a reason you find imports for anything in python, and next to nothing for rdbms's. Nearly any extension/package implemented is hooked into the DB internals, and not portable.
because the editor tooling is basically stuck in the 80's, with universally useless error messages, absurd limitations like select-clause ordering requirements (e.g. you can't use a select alias in the where clause, because the where clause gets evaluated first; this is exactly equivalent to C requiring forward declarations), select-before-from breaking autocomplete, and only advanced editors bother to even forward data-type mapping from schema definitions to your autocomplete because of the open-world assumption (the schema could change under your feet); hell, even SQL formatters have largely given up on trying to format anything but the most rudimentary constructs, because of the ridiculous amount of language they need to support.
because the language stuffs a trinary logic (true,false,null) into a binary logic system and grants you access to all of the benefits of both false positive results and false negatives.
because the relational algebra is elegant, in all the ways the SQL language is not.
But we'll stay with it, because at the end of the day, no one gives a shit about SQL. The SQL language is just the interface to the Glory of Codd, the relational algebra, the RDBMS, and in this we find all true value. But we can still dream of a better world. A world where RDBMS's support more than one query language.
SQL is uniquely challenging for many to learn because you cannot poke around in the bushes procedurally and still arrive at a good outcome. The declarative nature of SQL usually means all or nothing.
I do recall how frustrating it was to learn all of the join shapes. It's so trivial to me now, but it was a nightmare at the beginning of my journey. The popularity of ORMs is absolutely not a surprise to me.
Performance tuning is important. Things like `random_page_cost`, or `effective_cache_size` are important to understand – especially with nvme drives, or databases that partition data across multiple disks (eg. hot data on nvme, stale data on spinning disks).
But...
Being a DBA is more than just writing queries
I had to get help from the DBA to find where it was going wrong and rewrite it
Just wanted to chuckle at this. I agree with the point — you want to write as close to the metal as possible in SQL. It's the same with ORMs. That means... writing SQL, not an intermediate language. Although https://prql-lang.org/ looks great!
Comments
It sounds like Malloy might be able to find a niche amongst some casual users, but it will never gain traction amongst DBAs. My reasoning is as follows.
Being a DBA is more than just writing queries, you have to be able to maintain the database, maintain the security settings, and (most importantly for this discussion) do performance tuning.
Performance tuning is incredibly important; I have, personally, seen a query that was running in only a few seconds have an absolutely catastrophic performance drop off when a few extra records were added. I had to get help from the DBA to find where it was going wrong and rewrite it to get the performance back to a reasonable state again. (I am talking about query run time going from seconds to minutes.)
You can't do performance tuning in Malloy I doubt; You'll be needing to run the analyser and rewriting the produced SQL. Since you'll have to know SQL really well to do this, why bother learning Malloy as well?
You could make the same argument that we all should learn assembly language, because when our C compilers produce bad assembly we'll have to rewrite it by hand. But I haven't written assembly in 30 years, and even then it was just for fun.
As it turns out, having high-level abstractions means that in many ways it's easier to do automatic optimization. I look forward to automatic data-driven database tuning and query optimization.
Postgres has done an incredible job optimizing both the overall performance of queries and the query planner - but these things make mistakes still and being able to fix those mistakes can make the difference between a two minute query and a twenty milisecond query - this comes with the fact that complex database operations can usually make or break overall response times, and these optimizations usually depend on statistic accuracy which can be hard to ensure.
If SQL optimization was as good as compilers I'd be all on this train, but I just don't think we're there yet.
Your argument seems to assume that SQL is assembly language; it is not! The query plan or internal bytecode is the 'assembly language' of an RDMS, therefore SQL is more akin to the RDMS version of C, rather than assembly language.
So Malloy is akin to a language that compiles to C, which is then compiled to assembly. Does this seem like a worthwhile pursuit to you?
Personally, I don't think any SQL replacement will really take off unless someone can get the major RDMS makers to support it natively.
Just to be clear, I'm not saying it's not a worthwhile pursuit: SQL does have many problems and could do with a replacement that supports more modern approaches to problem solving. What I am saying is that unless someone comes up with something that can automatically do performance tuning for you, or that is supported natively by the RDMS, then SQL replacements will continue to fail to gain traction.
People do this, e.g. building a cost-based optimizer for queries, but it can take over 2 years for it to really get pretty good. And even then it can only go so far and you'll still want to manually tune certain queries.
I still don’t understand why so many people seem to talk about replacing SQL. It’s not complicated, can be learned in a few hours, it’s relatively easy to read and it fairly portable across databases and stacks.
It’s been the most important detail of my job for the last 20 years.
SQL has a couple legitimate limitations as a query language, largely because we expect databases to do so much more today than when it was designed. I've been both a heavy user of SQL and implementor of SQL databases focused on data models like graph, spatial, et al, so I feel the pain from both directions.
Some useful things are difficult to effectively express in SQL because it was not designed to make them expressible. Adding support after the fact introduced a lot of compromises and complexity; you can tell which parts of SQL are second-class citizens. SQL would have been designed differently if its original scope had been broader and it is an inelegant mess for some types of data models. You can make it work but it isn't pleasant. Attempts to address these gaps explain much of why SQL databases all have their own non-standard SQL dialect, and it has made SQL effectively non-portable across databases.
In principle, a more modern query language could elegantly address the much broader scope of how we use databases today in a standardized way, instead of the zoo of non-standard extensions and mismatched parts that are grafted onto specific SQL implementations.
because as a language it's quite complicated, except in simple cases, the syntax is completely arbitrary and randomly injects keywords willy-nilly in an absurd attempt at being "english-readable"
because it lacks composability, making dynamic query generation a massive pain in the ass, as well as manual edits of queries.
because watching DBA's flailing strings about, smashing them together like apes with stones, just so they can struggle to feed it into an EXEC SQL call is perhaps one of the greatest tragedies of our programmatic times.
because it's not at all portable, with every database arbitrarily extending the SQL standard left-and-right, and even basic conveniences and functions end up breaking your portability goals; the ANSI SQL standard defines less a standard and more of an aesthetic mold for each dialect to try to match against.
because it lacks any portable ability to extend the language, with nearly every function added not as a stdlib function but instead as part of the language itself, often with custom keywords to go with it; there's a reason you find imports for anything in python, and next to nothing for rdbms's. Nearly any extension/package implemented is hooked into the DB internals, and not portable.
because the editor tooling is basically stuck in the 80's, with universally useless error messages, absurd limitations like select-clause ordering requirements (e.g. you can't use a select alias in the where clause, because the where clause gets evaluated first; this is exactly equivalent to C requiring forward declarations), select-before-from breaking autocomplete, and only advanced editors bother to even forward data-type mapping from schema definitions to your autocomplete because of the open-world assumption (the schema could change under your feet); hell, even SQL formatters have largely given up on trying to format anything but the most rudimentary constructs, because of the ridiculous amount of language they need to support.
because the language stuffs a trinary logic (true,false,null) into a binary logic system and grants you access to all of the benefits of both false positive results and false negatives.
because the relational algebra is elegant, in all the ways the SQL language is not.
But we'll stay with it, because at the end of the day, no one gives a shit about SQL. The SQL language is just the interface to the Glory of Codd, the relational algebra, the RDBMS, and in this we find all true value. But we can still dream of a better world. A world where RDBMS's support more than one query language.
SQL is uniquely challenging for many to learn because you cannot poke around in the bushes procedurally and still arrive at a good outcome. The declarative nature of SQL usually means all or nothing.
I do recall how frustrating it was to learn all of the join shapes. It's so trivial to me now, but it was a nightmare at the beginning of my journey. The popularity of ORMs is absolutely not a surprise to me.
And all frameworks migrate over time towards being more declarative with this exact failure mode.
Performance tuning is important. Things like `random_page_cost`, or `effective_cache_size` are important to understand – especially with nvme drives, or databases that partition data across multiple disks (eg. hot data on nvme, stale data on spinning disks).
But...
Just wanted to chuckle at this. I agree with the point — you want to write as close to the metal as possible in SQL. It's the same with ORMs. That means... writing SQL, not an intermediate language. Although https://prql-lang.org/ looks great!