I've come to believe that relational is the wrong choice for most apps.
Most people choose a relational db without even realizing what relational means and what the tradeoffs are.
Almost every app dev would prefer that their app reacts to changes in the entities, and updates it UI accordingly. Incrementally computing new query results. This is non-trivial in the relational model.
The relational model is a bunch of constraints such that relational algebra can be used by the system to re-arrange the query plan and get the same results.
Your queries are optimized, yes, but if you want to stream changes, then you probably want a different query plan altogether. And its not a matter of just making SQL cacheable and streamable like with incremental view maintenance - you probably want different queries to run altogether. Most people aren't building analytical reporting apps with SQL. The database size for a single user or team in most apps is such a small size that aggregations can just be done client-side even.
I think a better way would be to have a database that knows about all queries in your app, and optimizes collectively. And instead of relying on the database to do it, give users the tools to build query plans themselves. And also, throw away the idea of relations and just make all query results reference the underlying entities. Instead of complaining about the "ORM mismatch" with relations, get rid of the relations, and embrace objects.
The relational model (and generally working at the level of sets/collections, instead of the level of individual values/objects) actually makes it easier to have this kind of incremental computation in a consistent way, I think.
There's a bunch of work being done on making relational systems work this way. Some interesting reading:
Man, this is such a great list of links. I have had these ideas floating around my head for nearly a decade now and have somewhat of a radical goal in mind. This has given me some much needed food for thought. Thank you, truly -- and add my name to the list of people who would love to see your other links
Thanks great links. I've been following some of those projects for a while.
I haven't dived into the differential dataflow stuff yet. I think a lot of these projects may be handicapped though by trying to work with existing SQL queries and user SQL knowledge which makes things more complex than they need to be.
> I think a lot of these projects may be handicapped though by trying to work with existing SQL queries and user SQL knowledge which makes things more complex than they need to be.
I could not agree more!
I'm sorry to report I actually peeked through your HN submissions and I think you and I are seeing the exact same problem and trying to come up with a solution... from markup languages to visual programming to database internals to issues with electron, these are all things I've been spending time on! It's uncanny how much our interests overlap
Personally I'm debating dedicating my time to this exclusively as I think it's (a) incredibly exciting and (b) a massive opportunity. It will probably be 6-9 months before I pull the trigger and start a company (depending on how things develop between now and then), but while I have your attention, if you would be interested in reconnecting at that point, I'd love to get your contact info--my HN email alias is on my profile
> have a database that knows about all queries in your app, and optimizes collectively
I’ve had this idea floating around in my head for a while: if you could take all the queries and the schema, you should be able to compile that to a specialized binary that only does those queries. I have no idea how to accomplish that but I bet you could do a lot of nifty optimization. Migrations get hard but I wonder how good it would be
This has been prototyped several times over the decades. Migration is one significant limitation as you note, but the other is that the metaprogramming required to code gen a highly optimized database for a specific data model and workload is insanely complex, and someone has to write and maintain that with the very low defect rate people expect of databases.
The performance is excellent but modern databases tend to be bandwidth-bound for many workloads anyway, so the benefit will be significantly limited.
Another limitation is debugability. I frequently run all kinds of queries against databases to figure out what's going on and compiling in the set of queries in advance could significantly limit your visibility into the data.
its only a performance problem. there wouldn't be any reason to forbid arbitrary queries - they just might not have pre-built indices that make them go as fast as they possibly could
> there wouldn't be any reason to forbid arbitrary queries
There is an argument for this, it's just probably not worth the tradeoff. The security benefit seems somewhat reasonable. Feels like the kind of tradeoff as a unikernel.
> Almost every app dev would prefer that their app reacts to changes in the entities, and updates it UI accordingly.
Absolutely. I’ve been going through this tech research process now as I need near-real-time or at least reactivity and I just scratch my head and think “why is this so hard? don’t everyone need this stuff these days?”
> This is non-trivial in the relational model.
Yes, but I don’t believe that it has almost anything to do with the model, and almost everything to do with choices in RDBMSs like Postgres, which are slow moving tankers.
All the abstractions and procedures for materializing views (ie store and run the queries in a push instead of pull fashion) are both theoretically and practically well understood, and half of the support is already there.
WAL + CDC is virtually already the standard, it’s just clunky to use. The missing pieces seem to be more about multiplexing subscriptions and it’s implications on downstream protocols (probably needs wire protocol changes?).
In either case, I very much think the relational model should not be considered mutually exclusive from reactivity, and the implications of assuming that may be throwing out millions of holy babies with a little bit of bath water.
The problem is that I think a lot of people will do these big joins and serve them via graphql or a deep json object...they get all the data they need...but then to update this query may be inefficient depending on the query plan. The query plan won't optimize for fine-grained reactivity of certain parts of the result, nor does it know about other queries that will be run which prevents intelligent caching of sub-queries.
> Almost every app dev would prefer that their app reacts to changes in the entities, and updates it UI accordingly. Incrementally computing new query results. This is non-trivial in the relational model.
I assume you mean detecting data changes and not so much broadcasting (messaging). Views are designed to capture what entity changes you care about. Not all RDBMS' support indexed views and Postgres unfortunately falls into this category, but views are a best practice.
Perhaps the missing piece is that based on the views and frequency of view calls, RDBMs automatically curate and maintain table indexes.
> I've come to believe that relational is the wrong choice for most apps.
The benefit of relational Model is that every operation can return a table. Right? That makes it easy to sequence all operations together, and easy to understand what they are doing, and thus avoid errors.
Most people choose a relational db without even realizing what relational means and what the tradeoffs are.
Almost every app dev would prefer that their app reacts to changes in the entities, and updates it UI accordingly. Incrementally computing new query results. This is non-trivial in the relational model.
The relational model is a bunch of constraints such that relational algebra can be used by the system to re-arrange the query plan and get the same results.
Your queries are optimized, yes, but if you want to stream changes, then you probably want a different query plan altogether. And its not a matter of just making SQL cacheable and streamable like with incremental view maintenance - you probably want different queries to run altogether. Most people aren't building analytical reporting apps with SQL. The database size for a single user or team in most apps is such a small size that aggregations can just be done client-side even.
I think a better way would be to have a database that knows about all queries in your app, and optimizes collectively. And instead of relying on the database to do it, give users the tools to build query plans themselves. And also, throw away the idea of relations and just make all query results reference the underlying entities. Instead of complaining about the "ORM mismatch" with relations, get rid of the relations, and embrace objects.