4 ms·
Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. Th
by derefr 5y ago
Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query.
I've heart the rebuttal to this idea before, that even minor DB schema/configuration changes that should be "invisible" to the application (e.g. an index being created — or even refreshed — on the table) would result in the resulting bytecode being different; and so you'd never be able to ship such bytecode as a static artifact in your client.
But: who said anything about a static artifact? I'm more imagining a workflow like this:
1. user submits a query with leading command "PLAN" (e.g. "PLAN SELECT ...")
2. Postgres returns planned bytecode (as a regular single-column bytea rowset)
3. if desired, the user modifies the received bytecode arbitrarily (leaving alone anything the client doesn't understand — much like how optimization passes leave alone generated intrinsics in LLVM IR)
4. user later submits a low-level query-execution command over the binary wire protocol, supplying the (potentially modified) bytecode
5. if the bytecode's assumptions are still valid, the query executes. Otherwise, the query gives a "must re-plan" error. The client is expected to hold onto the original SQL and re-supply it in this case.
In other words, do it similarly to how Redis's EVAL + EVALSHA works; but instead of opaque handles (SHA hashes), the client receives a white-box internal representation.
Or: do it similarly to how compiling a shader on a game-console with unikernel games + unified RAM/VRAM works. The driver's shader compiler spits you back a buffer full of some GPU object code — which, running in kernel-mode, you're free to modify before running.
- SigmundA 5y agoPostgreSQL doesn't even cache its own byte code, it replans every time!
- mst 5y agoDepending on the placeholder values the optimal plan may be very different. You -can- use a server side prepared statement to force it to -ahem- plan ahead but that's -usually- not actually worth it.
- SigmundA 5y agoThats why other databases have parameter sniffing. And using prepared statements only works on the same connection so of limited use. Luckily PostgreSQL's optimizer is very primitive and so planning doesn't take too much time, as it gets more advanced the lack of plan reuse will become more of an issue. Its already an issue with the LLVM JIT compilation time.
- tshaddox 5y agoYou can't ship SQL queries and then do arbitrary schema changes either, right?
- lazide 5y agoNot arbitrary changes, but nearly so - if you add new columns (and your existing queries name the columns they use explicitly), then nothing changes as far as the client is concerned. If you remove or rename one, only those clients who refer to it need to change, and then only if you don’t do a interposing view/schema/etc. The server is supposed to take the logical intent specified by the queries and do the work to mapping that into concrete retrievals in a stable and performant way, so a query explicitly telling the server to do something in a specific way breaks the model. The more specific, the more broken. DDL changes for instance ideally would not have to tell the server how to structure things on disk. The server should know how to do it efficiently. That said, real life intrudes and sometimes (but not usually) it’s important to do this for stability or performance reasons. The more magic involved, the more unpredictable a system can be, and the closer a system runs to redline, the more chaos/downtime that can cause.
- mst 5y agoI suspect the extra compatibility/dependency surface would make this a non starter in practice but it's an absolutely fascinating thought experiment and I'm glad you wrote it up.
- jhgb 5y ago> Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query. And now you've invented Firebird, or even 1980's Interbase. ;)