4 ms·

> This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries. You can already do this. They’re cal

> This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries. You can already do this. They’re called prepared queries. The application sends the query once, and asks the DB to create a prepared query, and every time the application wants to reuse it, it just needs to pass the query parameters. The DB reuses the already existing prepared query, and often the query plan is reused as well. But that depends on the exact DB implementation. You also can’t just reuse the same query plan over and over again. The whole point of the query planning process is to optimise the query plan to current state of the data on disk. As that data is appended to and updated, the optimal query plan may change. It’s also possible for applications to know exactly what types a query is going to return. That information is already returned by the database as part of the query response, it has to be for the client to correctly deserialise the returned data. Most DB uses a binary protocol for most of their clients (but also support a pure text based protocol), and deserialisation the binary response requires information about the response types. I think you’re drastically underestimating just how complex and magical the inside of a real SQL database is. They represent literally decades of engineering and research into the best ways of storing and querying databases. If anything close to what you’re suggesting was a good idea, someone would have tried it.

josephg 2y ago
> I think you’re drastically underestimating just how complex and magical the inside of a real SQL database is. They represent literally decades of engineering and research into the best ways of storing and querying databases. If anything close to what you’re suggesting was a good idea, someone would have tried it. I was with you up until the last sentence. I almost certainly am underestimating how magic the internals of databases are, and how much work has gone into them. But there is a mountain of good ideas in CS that simply haven't been tried because of convenience and momentum. For example, SeL4's microkernel architecture is brilliant - but it'll probably never make it into Linux because of simple inertia. (Its too hard to rearchitect linux like that, and nobody really wants to port all their working software to L4.) One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations. Moving any logic into the DB client would require all of that logic to be reimplemented a lot of times, and thats a lot of inconvenient work. But webassembly changes that calculus - since the database itself can hand a wasm blob to the client to run. Thats a very new capacity that was really hard to pull off a few years ago. (Foundationdb does something similar - the client library is a native library that needs bindings on every language / platform to work - but its incredibly inconvenient to use as a result. And a native apple silicon version of the library took years to come out, even though its maintained by Apple.)
- avianlyric 2y ago
  > One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations. Or it could be that the added complexity simply doesn’t provide any benefits. What benefits would splitting up the query planner between the DB and its clients provide? Computing a query plan isn’t the expensive part of executing a query. You’re suggesting that it’s a good idea for databases to expose their core internal APIs, which are tightly coupled to their underlying storage subsystems, to some kind of magic query planner that runs in the client. Basically ensuring that you can’t ever modify those APIs without breaking every existing client out there. Effectively you would force every DB to require their clients to be updated in perfect lock-step with the DB itself. All to achieve what? A saving of a few milliseconds on un-prepared queries? Simply put, there are much more sensible ways to reducing the cost of query parsing and planning. As it happens every DB worth talking about already implements those optimisation in the form of prepared queries. What benefit would moving where the query planner is executed provide? I would also highlight that if you go an look at DBs built from the ground up for massive scale and parallelisation, they still run their query planners on a single node to figure out how best to break up a query for distributed execution. Nobody is attempting to distribute the actual query planning part of a DB.

josephg 2y ago

> I think you’re drastically underestimating just how complex and magical the inside of a real SQL database is. They represent literally decades of engineering and research into the best ways of storing and querying databases. If anything close to what you’re suggesting was a good idea, someone would have tried it. I was with you up until the last sentence. I almost certainly am underestimating how magic the internals of databases are, and how much work has gone into them. But there is a mountain of good ideas in CS that simply haven't been tried because of convenience and momentum. For example, SeL4's microkernel architecture is brilliant - but it'll probably never make it into Linux because of simple inertia. (Its too hard to rearchitect linux like that, and nobody really wants to port all their working software to L4.) One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations. Moving any logic into the DB client would require all of that logic to be reimplemented a lot of times, and thats a lot of inconvenient work. But webassembly changes that calculus - since the database itself can hand a wasm blob to the client to run. Thats a very new capacity that was really hard to pull off a few years ago. (Foundationdb does something similar - the client library is a native library that needs bindings on every language / platform to work - but its incredibly inconvenient to use as a result. And a native apple silicon version of the library took years to come out, even though its maintained by Apple.)

avianlyric 2y ago
> One reason that this approach to database design probably hasn't been tried before is that there's dozens of database client driver implementations. Or it could be that the added complexity simply doesn’t provide any benefits. What benefits would splitting up the query planner between the DB and its clients provide? Computing a query plan isn’t the expensive part of executing a query. You’re suggesting that it’s a good idea for databases to expose their core internal APIs, which are tightly coupled to their underlying storage subsystems, to some kind of magic query planner that runs in the client. Basically ensuring that you can’t ever modify those APIs without breaking every existing client out there. Effectively you would force every DB to require their clients to be updated in perfect lock-step with the DB itself. All to achieve what? A saving of a few milliseconds on un-prepared queries? Simply put, there are much more sensible ways to reducing the cost of query parsing and planning. As it happens every DB worth talking about already implements those optimisation in the form of prepared queries. What benefit would moving where the query planner is executed provide? I would also highlight that if you go an look at DBs built from the ground up for massive scale and parallelisation, they still run their query planners on a single node to figure out how best to break up a query for distributed execution. Nobody is attempting to distribute the actual query planning part of a DB.