4 ms·

>One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructi

>One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. The problem here is these low level optimizations that the database is doing is freaking complicated. There's a lot of intelligence going on in the background and it's different depending on the database. We can solve it with a low level common IR but that IR will be extremely complex. And that IR will likely give different performance profiles for the same code on different databases. I can see this being solved for one database. But not across all databases and a common api. We sort of do have a common api and we paid for it with the trade off of it being a easier high level language on top of the part of the web that needs to be most optimized.

bpicolo 2y ago
> can solve it with a low level common IR Wouldn't that basically just be a query plan? The language is the easy part. Determining the optimal query plan based on rough statistics about the data and the DBs ability to go about that is the hard part. There are more general purpose query optimization libs out there, like Calcite
josephg 2y ago
> I can see this being solved for one database. But not across all databases and a common api. Database client libraries are already database-specific. Why boil the ocean? We could start with one database with this very easily until we figured out a good IR for queries. It could even be database-version specific if you wanted, through the use of wasm. Make the database client library embed a wasm VM. Then on startup, the database sends any connected client application the latest query optimizer / whatever logic in a compiled wasm bundle. This would allow the database to make improvements to the IR with successive versions without needing to update all the client libraries out there.

bpicolo 2y ago

> can solve it with a low level common IR Wouldn't that basically just be a query plan? The language is the easy part. Determining the optimal query plan based on rough statistics about the data and the DBs ability to go about that is the hard part. There are more general purpose query optimization libs out there, like Calcite

josephg 2y ago

> I can see this being solved for one database. But not across all databases and a common api. Database client libraries are already database-specific. Why boil the ocean? We could start with one database with this very easily until we figured out a good IR for queries. It could even be database-version specific if you wanted, through the use of wasm. Make the database client library embed a wasm VM. Then on startup, the database sends any connected client application the latest query optimizer / whatever logic in a compiled wasm bundle. This would allow the database to make improvements to the IR with successive versions without needing to update all the client libraries out there.