5 ms·

Could the same queries be 'planned', compiled down to pipelined KV operations, by the requester? I don't see that this is inherently less capable. You could eve

Could the same queries be 'planned', compiled down to pipelined KV operations, by the requester? I don't see that this is inherently less capable. You could even use an existing ORM – though I think you can do better when not compiling to something declarative, maybe more like polars. I feel like databases effectively (/literally) add a JIT, which can mostly figure out what to do, even has accurate heuristics on the distribution of the data, but in exchange you get a less deterministic system, and less intuition for how to query or structure things. It's like, you know when to use a list/map/queue, but you want to focus on the business logic, so just use a smart collections which guess at runtime. I think you can get this with FoundationDB, I should experiment rather than hypothesizing, but it feels like it would be nicer

zbentley 24d ago
It’s theoretically possible to do that kind of planning on the client, but difficult and not worth it compared to letting the database do it. Especially for reporting, there’s another disadvantage: many query planners use runtime statistics from the database to build the plan; synchronizing those onto the client would be difficult and error-prone. But why bother? If I have a thousand clients that all want to run a query, why compile the plan a thousand times (and build/distribute the local planner to all of the different clients’ platforms) when I could send a query and have the database plan and cache the query once?
- speedstyle 24d ago
  For most cases I mean compiled into the client, not planned at runtime. And yes, explicitly trading away access to the live latencies and data distribution (except maybe for interactive analysts) – if you want a better query you profile and edit the client, like with any other service. Or in the JIT analogy, writing Go/Rust over JS/Java, using experience and profile-guided optimization but not realtime heuristics. Certainly this would make it easier to improve on bad plans, but that's survivorship bias, maybe I don't understand how many currently-good executions a more naive abstraction wouldn't produce.
  - zbentley 23d ago
    Fair, but I still think it’s not worth it. Distribution alone would be a pain. Also, what about views? Let’s say I expose my tables in a convenient non-materialized view. You query that view, bake the query plan into an executable, and ship it. Later, I change the backing schema a bunch, adding/removing/changing tables. I update the view so that it behaves the same way it did before. Your pre-compiled plans are going to be invalid now, right? Same deal for efficiency: if I make an unindexed table and you ship a plan that copes with that by compiling in a hyper-efficient vectorized full table scan, then later I add an index to the table, do I have to rebuild all my client deployments to start using that index? If the answer to those is “make the client code aware of the schema, indexes included, at build time”, I think that excludes a lot of cases where multiple codebases (some of which don’t contain the ORM or schema info beyond queries) talk to the same database, and reactive database-side schema changes to e.g. add an index by hand during an outage. I don’t particularly like it, but it’s true that a lot of shops don’t use a database migrator at all, or don’t use one that’s integrated with their client application SDLC in any way, and that’s likely to remain the case in a lot of situations. Both views-as-query-snippets and reactively adding indices are pretty common, so I’m reluctant to consider SQL alternatives that don’t support those patterns.
    - speedstyle 23d ago
      Yes, it would get rid of these things, maybe I overstated the similarity to existing SQL deployments. Non-materialized views wouldn't be part of the schema, but you could still have stored procedures which change with migrations. A new index would not be used until clients were updated to use it, just like a new API method wouldn't. For better and worse this is the point – changes and improvements are made in the place you write the query, rather than in a dynamic general query runner. It would probably also increase the places you use an 'application layer' which is tightly coupled to the database and provides a more stable, less general view to various clients. So, the place you write queries can itself be centralized towards what owns the data, but either way there's less happening in between the query and the data.

zbentley 24d ago

It’s theoretically possible to do that kind of planning on the client, but difficult and not worth it compared to letting the database do it. Especially for reporting, there’s another disadvantage: many query planners use runtime statistics from the database to build the plan; synchronizing those onto the client would be difficult and error-prone. But why bother? If I have a thousand clients that all want to run a query, why compile the plan a thousand times (and build/distribute the local planner to all of the different clients’ platforms) when I could send a query and have the database plan and cache the query once?

speedstyle 24d ago
For most cases I mean compiled into the client, not planned at runtime. And yes, explicitly trading away access to the live latencies and data distribution (except maybe for interactive analysts) – if you want a better query you profile and edit the client, like with any other service. Or in the JIT analogy, writing Go/Rust over JS/Java, using experience and profile-guided optimization but not realtime heuristics. Certainly this would make it easier to improve on bad plans, but that's survivorship bias, maybe I don't understand how many currently-good executions a more naive abstraction wouldn't produce.
- zbentley 23d ago
  Fair, but I still think it’s not worth it. Distribution alone would be a pain. Also, what about views? Let’s say I expose my tables in a convenient non-materialized view. You query that view, bake the query plan into an executable, and ship it. Later, I change the backing schema a bunch, adding/removing/changing tables. I update the view so that it behaves the same way it did before. Your pre-compiled plans are going to be invalid now, right? Same deal for efficiency: if I make an unindexed table and you ship a plan that copes with that by compiling in a hyper-efficient vectorized full table scan, then later I add an index to the table, do I have to rebuild all my client deployments to start using that index? If the answer to those is “make the client code aware of the schema, indexes included, at build time”, I think that excludes a lot of cases where multiple codebases (some of which don’t contain the ORM or schema info beyond queries) talk to the same database, and reactive database-side schema changes to e.g. add an index by hand during an outage. I don’t particularly like it, but it’s true that a lot of shops don’t use a database migrator at all, or don’t use one that’s integrated with their client application SDLC in any way, and that’s likely to remain the case in a lot of situations. Both views-as-query-snippets and reactively adding indices are pretty common, so I’m reluctant to consider SQL alternatives that don’t support those patterns.
  - speedstyle 23d ago
    Yes, it would get rid of these things, maybe I overstated the similarity to existing SQL deployments. Non-materialized views wouldn't be part of the schema, but you could still have stored procedures which change with migrations. A new index would not be used until clients were updated to use it, just like a new API method wouldn't. For better and worse this is the point – changes and improvements are made in the place you write the query, rather than in a dynamic general query runner. It would probably also increase the places you use an 'application layer' which is tightly coupled to the database and provides a more stable, less general view to various clients. So, the place you write queries can itself be centralized towards what owns the data, but either way there's less happening in between the query and the data.