Because a SQL query encompasses an arbitrary combination of MANY different sub-programs that are expected to be auto-solved.
Attempt to implement them, manually, and you see how hard is it.
PLUS, not only you need to account for the general solution, but what could be the best considering the current data set.
And, you can't compile statically (dynamically sure).
And, should work interactively, so hopefully be solved faster than run the actual query.
P.D: Joins are normally the focus, but other constructs are also challenging. For example, just solving if and which indexes to pick can be challenging when you have dozens of predicates.
And yes, your optimizer should survive(eventually!) to solve when you get feed hundreds of joins, predicates, aggregates, sorting and arbitrary expressions.
* I worked in the optimizer of a database. EVERYTHING is tricky!
I even dream of build tools for business to make apps (like Air table, but better) and even if you can do anything that do, perfectly, the software they need not means they want to babysit it all the time.
Is like the person that knows how cook, amazingly, yet hire a chef for take care of it most days.
Yeah, this is a hard problem, in special because Standard SQL databases only partially implement the relational model, have not good recurse for deal with relations-in-relations and lack of ways to (in user space) build your own storage (all stuff that I dream to tackle).
I think the possible answer is to try to "compress" columns with custom datatypes, it could require to touch part of the innards of sql (like in postgreSQL you need to solve it with c) but is a viable option in many cases where you noted that what you could express in json, for example, is in fact a custom type that could be stored efficiently if there is a way to translate it to more primitive types, then solved that the indexes will work.
The second option is to hide part of the join complexity with views.
I work independently as consultor/solo dev (https://www.elmalabarista.com) for years now, so with that much money (even 1 million) I just be set for life!.
But I actually love my job (programming) and do it in my spare time (after program for work!) and like to join others to learn (as I have done for https://spacetimedb.com/).
Will probably dedicate the time for my dream of build a FoxPro-alternative, and pretty certain will love it for more than a decade, so if anyone wanna help get funds I all hears!
And if a major problem, `jj op log` + `jj op log restore` fix it. This is the major super power of jj: Before I need to nuke the git repo on bad rebases (not chance in hell I can find how undo the massive 20+ steps bad rebase)
My dream is to build such tool, I slowly doing in the side (https://tablam.org). Probably need to ask for funding too.
In concrete, what I want is a real alternative to Fox/Access that work, unrestricted, locally (including inside a phone/iPad). I don't mind have support for cloud stuff (that is what pay the bills mostly) but that is secondary IMHO.
Current computers, even mobile, have far much power than most need, and is a shame not much tools actually exploit it.
Senior Rust · Databases · compilers · business applications engineer
Location: Medellín, Colombia
Remote: Yes
Willing to relocate: No
Technologies: Rust, RDBMs (PostgreSQL · SQL Server · SQLite), Swift, Python
Résumé/CV: https://github.com/mamcx
Email: mamcx@elmalabarista.com
Senior software engineer with 30+ years building production systems and 3+ years working as core developer on database internals and high-performance RDBMs. Recent work includes building the internals of a production-ready real-time database used in a large multiplayer game.
Founder and principal engineer with end-to-end ownership from architecture to production.
Looking for senior / staff / principal roles in Rust, databases, or backend systems.
Attempt to implement them, manually, and you see how hard is it.
PLUS, not only you need to account for the general solution, but what could be the best considering the current data set.
And, you can't compile statically (dynamically sure).
And, should work interactively, so hopefully be solved faster than run the actual query.
P.D: Joins are normally the focus, but other constructs are also challenging. For example, just solving if and which indexes to pick can be challenging when you have dozens of predicates.
And yes, your optimizer should survive(eventually!) to solve when you get feed hundreds of joins, predicates, aggregates, sorting and arbitrary expressions.
* I worked in the optimizer of a database. EVERYTHING is tricky!
reply