A side project I have stewing in my head is to try to hack something into a Postgres plugin to provide a nice AST format for producing specific query plans. I think this can be done, given that there are some old plugins that allow stuff like saving query plans, but I haven't looked much into the details yet. My thinking is that once you have that, you open the door for a lot of experimentation into query language alternatives that still maintain the power of SQL. And since you're cutting the query planner out of the equation (and moving it instead into a compilation step), you can hopefully take some of the frustrating black magic out of it.
Key assumptions I'm working from here are:
1. Actual programmers (in contrast with SQL's original target audience) generally know exactly what data access patterns are appropriate for a particular task; they just don't want to write them from scratch and have to worry about stuff like locking and transactions at a fine grained level.
2. Being able to switch plans on the fly based on query input and data statistics is not a huge win in most real world scenarios, and isn't worth the unpredictability that it entails.
3. Most of the shallow pain of working with SQL comes from the fact that it was a strange paradigm to start with, and then had a bunch of features hacked on top of it.
4. Most of the deep pain of working with SQL comes from the fact that it abstracts too much, forcing you to reverse engineer the desired query plan through those abstraction layers. And like a lot of reverse engineering, the result is fragile and might change to something far less efficient in the future for inscrutable reasons like database upgrades or subtle changes in how the data is shaped.
Pretty much any replacement can address 3. Ideas that really excite me address 1 and 4, and it seems to me that those are more likely to look procedural than purely functional. But like I said, the main thing is that having a low level target to compile to would allow us to try out different ideas and see what works.
Speaking as someone who has worked as a PostgreSQL DBA for over a decade, in environments ranging from hundreds of queries per second to hundreds of thousands, I can say with confidence that your assumptions do not map to my lived reality, or that of any other DBA with whom I've spoken about the challenges of working with developers who think the database is a black-box dump-truck they neither have to understand how to use well, nor care about the implications of using poorly.
1. "Actual programmers" tend to have net negative clue about which data access patterns are appropriate. It is, for example, staggeringly common for me to have to explain that a "table scan" is often more efficient than random IO ("index scan") — even on NAND media — if you're reading more than some threshold of the data in a table.
If you (the general "you") understand data access patterns that poorly, no, you absolutely should not be dictating query plans. If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview. I will hard pass.
2. See above.
3. The relational algebra is not a "strange paradigm". It's very, very simple. "Strange features" like what? Ordering? Aggregation? Set intersection and exclusion?
I'm confused by how severely you've misrepresented my points. For example:
> If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview.
Why did you remove "at a fine grained level" in quoting me? I'm saying that people want the facilities that a database painlessly provides with respect to these things. What I'm saying is that programmers don't want to implement MVCC themselves, for example, or invent their own system for managing locks. These are things that are great about RDBMSes, but my point is that they could be done without SQL.
For another example:
>"Strange features"
That's not a thing I said at all. I said "a bunch of features". "with recursive" is an example of this. It's a great feature, but as the commenter at the top of this thread pointed out, using it is clunky and hard to read. I believe that this is in large part because how it had to be worked into an existing, weirdly designed language in a backward compatible way.
Edit to add: I don't understand why discussions about software engineering so often quickly turn into these attacks on people's competence. I might be wrong, and if so, you can convince me of that without raising your hackles with these aggressive statements about what you would or wouldn't do if this were a job interview. That kind of rhetoric is toxic to productive discussions.
I apologize if I've misrepresented your concerns. I'm operating from a perspective of a decade and change of doing this dance over and over:
Engineer: "SQL is dumb and hard!"
DBA: "What part?"
Eng: describes problem
DBA: describes misunderstanding
Eng: "Oh! Oh, that's actually really simple! Thanks!"
It pretty much never goes the other way. So I'm probably a little over what read like dismissive, mis-premised, or under-informed criticisms. (And, yes, that probably colored the tone of my response. Again, apologies.)
> I believe that this is in large part because how it had to be worked into an existing, weirdly designed language in a backward compatible way.
Is sloppy shoe-horning the fault of the shoe, or the fault of the horn (assuming, for sake of discussion, that it's even sloppily done)? Recursively traversing parent-child (among other) relationships is not a wild, unforeseeable extension of set theory — and that's really all SQL is: a practical expression of set theory with a syntax that (admittedly) sometimes obscures that fact.
EDIT: Re: your edit. As one of my employer's DBAs, it's part of my job to reduce risk, including by passing on candidates I feel inadequately understand databases, or whose attitude evinces a lack of interest in improving that understanding. It's not about "attacking" a lack of competence, so much as avoiding the kind of incompetence that refuses to recognize itself.
If you've ever sat on the interviewer side of that table, you can't even pretend not to have seen entirely too much of that, and you'd pass on someone who didn't think they needed to understand the costs of various forms of, e.g., list traversal, just as quickly.
Key assumptions I'm working from here are:
1. Actual programmers (in contrast with SQL's original target audience) generally know exactly what data access patterns are appropriate for a particular task; they just don't want to write them from scratch and have to worry about stuff like locking and transactions at a fine grained level.
2. Being able to switch plans on the fly based on query input and data statistics is not a huge win in most real world scenarios, and isn't worth the unpredictability that it entails.
3. Most of the shallow pain of working with SQL comes from the fact that it was a strange paradigm to start with, and then had a bunch of features hacked on top of it.
4. Most of the deep pain of working with SQL comes from the fact that it abstracts too much, forcing you to reverse engineer the desired query plan through those abstraction layers. And like a lot of reverse engineering, the result is fragile and might change to something far less efficient in the future for inscrutable reasons like database upgrades or subtle changes in how the data is shaped.
Pretty much any replacement can address 3. Ideas that really excite me address 1 and 4, and it seems to me that those are more likely to look procedural than purely functional. But like I said, the main thing is that having a low level target to compile to would allow us to try out different ideas and see what works.