Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain.
If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.
My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).
Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.
I'll typically serialize the data fetched to send out over HTTP again
That's a whole other can of worms, so let's just ignore it for a second.
I've found that in a sense, the converse is the case, where back-end protocols significantly pre-date HTTP and "haven't learned the hard lessons" around things like cookies, caching, load-balancing, etc...
Ask yourself this: How many SQL database platforms can provide a redirect to clients? Or handle "client steering" at the load-balancer layer when hosted as a cluster with a partition key? Can any database platforms work with an in-line cache equivalent to a HTTP proxy? Digitally sign responses so that even un-trusted caches can be safely used? Etc...
Sure, some database platforms have solutions for some of those issues, but it's hit & miss at best.
And then, you have the problem that fundamentally all of them return tabular data, or go through some legacy thing like ODBC that expects row-oriented tabular data. If the source data isn't really tabular, it'll get expanded into a tabular form anyway.
At risk of sounding like the Dropbox-guy: it's not that hard to implement these yourself. I've had to write a service mimicking a PostgreSQL database for... reasons, and expanding the Postgres connection protocol to have functionalities for load balancing/caching/cookies would definitely be possible and not even crazy hard, especially if you at some point in your connection can just start proxying to an actual PostgreSQL server so you only have to intercept the handshake.
The bigger question is: what would that solve? Caching is mostly relevant if you are repeatedly executing the same, relatively heavy, query. Load balancing is mostly interesting if you have read-only queries that you can easily execute against any of X replicas, in which case you might as well do that application side instead of having some transparent layer in between. And I don't get why you'd need cookies for something that is a persistent connection instead of individual requests?
The tabular data thing is tricky though, you're right. The impedance mismatch between tables and nested object structures isn't easy to solve.
The reality of the situation is that ODBC isn't going away any time soon, and proposing a breaking change on top of protocols like that is not realistically going to see lots of adoption unless you come up with a really great alternative AND some good marketing around that.
I've come to the conclusion that the best way to spend my efforts is to work with the ecosystem and build tools that try and fix the impedance mismatch is easier than hoping for something better to come along. Making it easier to write performant code is yielding more results for me than trying to adapt SQL-protocols to horizontally scale. A single box with some well placed indexes and well written queries is ridiculously fast.
A lot of ORMs default eager loading to select-in loading with multiple queries because it performs best. First A is loaded and then the B query has a `WHERE B.foreign_key in (...all A id's loaded in the first step..)`.
Still has overhead, but usually less than duplicating data or using another serialization format like returning hierarchical json objects
Comments
Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain.
If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.
My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).
Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.
That's a whole other can of worms, so let's just ignore it for a second.
I've found that in a sense, the converse is the case, where back-end protocols significantly pre-date HTTP and "haven't learned the hard lessons" around things like cookies, caching, load-balancing, etc...
Ask yourself this: How many SQL database platforms can provide a redirect to clients? Or handle "client steering" at the load-balancer layer when hosted as a cluster with a partition key? Can any database platforms work with an in-line cache equivalent to a HTTP proxy? Digitally sign responses so that even un-trusted caches can be safely used? Etc...
Sure, some database platforms have solutions for some of those issues, but it's hit & miss at best.
And then, you have the problem that fundamentally all of them return tabular data, or go through some legacy thing like ODBC that expects row-oriented tabular data. If the source data isn't really tabular, it'll get expanded into a tabular form anyway.
At risk of sounding like the Dropbox-guy: it's not that hard to implement these yourself. I've had to write a service mimicking a PostgreSQL database for... reasons, and expanding the Postgres connection protocol to have functionalities for load balancing/caching/cookies would definitely be possible and not even crazy hard, especially if you at some point in your connection can just start proxying to an actual PostgreSQL server so you only have to intercept the handshake.
The bigger question is: what would that solve? Caching is mostly relevant if you are repeatedly executing the same, relatively heavy, query. Load balancing is mostly interesting if you have read-only queries that you can easily execute against any of X replicas, in which case you might as well do that application side instead of having some transparent layer in between. And I don't get why you'd need cookies for something that is a persistent connection instead of individual requests?
The tabular data thing is tricky though, you're right. The impedance mismatch between tables and nested object structures isn't easy to solve.
The reality of the situation is that ODBC isn't going away any time soon, and proposing a breaking change on top of protocols like that is not realistically going to see lots of adoption unless you come up with a really great alternative AND some good marketing around that.
I've come to the conclusion that the best way to spend my efforts is to work with the ecosystem and build tools that try and fix the impedance mismatch is easier than hoping for something better to come along. Making it easier to write performant code is yielding more results for me than trying to adapt SQL-protocols to horizontally scale. A single box with some well placed indexes and well written queries is ridiculously fast.
Ideally a query could return multiple cte tables.
A lot of ORMs default eager loading to select-in loading with multiple queries because it performs best. First A is loaded and then the B query has a `WHERE B.foreign_key in (...all A id's loaded in the first step..)`.
Still has overhead, but usually less than duplicating data or using another serialization format like returning hierarchical json objects