5 ms·

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 (exc

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 24d 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

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.