I would like to think that pg_hint_plan is designed in such a way that any hint it accepts must be a valid plan for the query. I’m quite confident that schemes with this property that can also express high quality plans are possible and not even excessively complicated.
This is not to say that it’s possible to genetically verify that a proposed algorithm does what you want it to — that would be undecidable or NP-hard or co-NP-hard depending on how you formulate the question.
I wouldn't be very excited about adding a 4B param model to my database deployment, but using this kind of approach while testing your app to identify query plans where Postgres is leaving performance on the table seems pretty valuable without much risk.
Because P!=NP (very probably IMHO) there's a huge class of problem for which LLMs are useful on the expensive and heuristic-y generation side because the verification is relatively inexpensive.
You're misunderstanding the setup here. The LLM doesn't modify the query, just some details about how to choose between different ways to break the query into basic operations on the tables. The SQL doesn't change. It's still up to postgres to guarantee that the results match the query. If the proposed plan were nonsense that didn't amount to carrying out the query, postgres would ignore it.
I'd like to contribute my amateur hour entry into this thread, although I did administer and develop mssql stuff for awhile.
sure optimizations based on stats, but the stats are the wildcard, in my experience query plans can change suddenly.
Queries are translated into plans according to statistics. However the transforms will be deterministic and should only change one valid plan to another. I could very easily see a neural network manipulate transforms the same way the current programming does, its just that the neural networks are by nature really nicely suitable because the "decisions" are based on training, and this training can be closed world type things like the ai assists that chess engines are now getting. Obviously ai still can't play chess but apparently its very good at ranking board positions just by developing that much statistical info because its training comes not from reading the web, but playing a gazzilian games against itself in a "closed" chess world of its own.
I'm thinking that the ai does "this legal transform of the query plan should be applied to this pattern of data (statistics, cardinality, etc)" simply because the ai encountered it in closed world training, much like the chess thing.
I don't believe that a pure neural network can currently abide by the rules of chess, even with unreasonable amounts of training. Do you have a counter example? I never mind having beliefs challenged with facts lol
There's quite a big difference between "AI" and "pure neural networks." No, frontier chess (Alpha zero etc) is not "pure," because there's Monte Carlo tree search and a hard-coded game rules implementation.
> I paid ~$800 to rent a 2x H100 SXM node from Lambda for ~95 hours, and ~$400 in OpenAI API fees to generate the Astra trajectory demonstrations.
> a tiny 4B model went from not being able to understand the harness it was wrapped in, to achieving a 1.81x geometric mean speedup and a summed latency decrease of 44.7% across a workload of join-heavy SQL queries
I can’t find it in the article (may have skimmed it too much), but I suspect they didn’t include those ~95 hours in the benchmark numbers.
I think all database vendors know their query optimizers could do much better if they could afford to spend lots of time to derive query plans.
⇒ this may be useful for some workloads, but even then, can you afford to spend hours every now and then to update your 4B model to ensure it still picks a good query plan?
> ⇒ this may be useful for some workloads, but even then, can you afford to spend hours every now and then to update your 4B model to ensure it still picks a good query plan?
I think this would be likely comparable to a scheduled backup, so I think it would be an acceptable maintenance window. However, deterministic algorithms would likely beat re-training (or re-fine-tuning) the model. For example, one could analyze actual distributions or whatever (instead of assuming uniform), and then some plans would automatically be eliminated.
Imo a good thought experiment is to look at places that are hyper-optimized, like compilers. Would LLMs bring anything to the table (architecturally or performance-wise) to a piece of software that has been carefully crafted for decades? (Methinks no.)
The Postgres query planner has had to operate, for those same decades, in a much more realtime-sensitive and restricted environment than compilers. It can only draw its conclusions from summary statistics on tables in isolation, not on their relationships with each other (and even less so when filters are involved). For many cases this is fine! For many others it isn't.
There's a good number of heuristic choices in compilation where, maybe, you could get more optimal outcomes with machine learning - but at the cost of compilation resources, both time and space, and possibly determinism too.
As an example, register allocation is graph colouring, and thus NP complete; a model for producing an allocation plan is learning heuristics that might look at more features in combination than the ones hand-crafted into the compiler. An LLM for the job might do better than a more focused model like a GNN, due to sheer size, the effectiveness of transformers, or magic. But it probably won't do an overall better job than the handcrafted heuristics, because those handcrafted heuristics also tend to compile very, very fast with a small memory footprint, and can be debugged (more) easily when they go wrong.
> For example, one could analyze _actual_ distributions or whatever (instead of assuming uniform)
Postgres keeps histograms (including N most common values) for all columns; it does not blindly assume uniform distributions. (Presumably an LLM would have access to the same histograms.)
I don't think anybody's denying that small models are doing distillation, the issue at hand is frontier lab vs frontier lab when it comes to big models.
You'd be shocked at some of the punishments handed out from various empires over history for offences as minor as stealing an apple. Whether they can claim the moral high ground is not question of ethics but a question of power.
On the flip side, its important to know just how much we would be sacrificing if big frontier gets their way in convincing the courts that distillation is a bad thing
Like this scene from Pirates of Silicon Valley between Bill Gates and Steve Jobs: https://www.youtube.com/watch?v=CBri-xgYvHQ Bill was saying that Steve was stealing from Xerox so it's okay if Bill stole from Xerox first.
I’ve played with it already. I don’t think this is the use case. I think Jev’s use case is fast, cheap and somewhat easy classification. It’s not trainable in the way you would want here. Even though it’s fast it wont be faster than pgs query optimizer.
At least as I understand things.
How did you plan to use Jev for query optimization?
I am still struggling to understand a use-case for Jev. Isn't what was explained in this article a classification problem? I.e. find and aggregate data?
The number of options has to be small and bounded. The query planning is more of a search/optimization problem than a classification problem since the number of options increases wildly based on query size.
It’s classifying faster and cheaper. A lot of immediate ideas are better solved by pre-classifying + embedding, but their doom example or the wikipedia runs are one where you can’t preclassify.
“81% faster query plans than Postgres”…on an 8 GB dataset that fits entirely in memory, with shared_buffers constrained to a fraction of that, queries warmed before measuring, and read-only SELECTs.
I would be cautious about over fitting, it’s tough to say if those query plans would really be more optimal than Postgres heuristics at scale and with a bit more realistic OLTP workloads.
In any case, such is life with profile guided optimization. Many of us appreciate how database workloads can drift over time and with scale.
Kudos to the author for getting their hands dirty and writing up their experiments.
Which is why a good query plan is so important, so that as much disk I/O can be avoided as possible (predicate push down, index elimination, join ordering, partition/scan pruning). Like the old CTE optimization fence problem.
8GB isn't even CPU RAM these days. That's GPU super-mega-awesome ram. Ordinary Server CPUs are regularly pushing 2TB capacities.
GPUs are in the 8GB to 32GB typically, at least for smaller and more regular GPUs. This GPU RAM is also well known to be at least 10x the bandwidth of CPU RAM.
Yeah, I have a GPU from almost six years ago in my desktop that has twice that much VRAM. Less than a year ago my wife got a 5070 Ti with the same for around $750 without needing to wait for it to be in stock or anything. I'm inclined to think that for a server that needs a GPU, even 32 GB would probably be considered small.
not necessarily, no.
With SSDs you get much better IOPS for cold data, and many datasets fit in RAM. So a lot of (OLTP/HTAP) workloads can become CPU-bound due to sorting/hashing - bread and butter of joins.
You know that GPUs are more flexible than just linear algebra, right?
GPUs are simply faster at fundamental algorithms like sorting (which has huge parallelism), and hashing. This is because both sorting and hashing are in Nick's class, offering enough "work" for these 10,000 SIMD-core systems to crunch work upon.
And because of modern algorithms/libraries with 'Mergepath sort' (a GPU-SIMD parallel sorting algorithm), its not even that difficult to implement anymore.
Naturally, this then leads to parallel Sort Merge Join, as well as parallel Hash-Join (two ways to implement left or right joins in a GPU that benefit from significant parallelism).
Even if GPUs weren't fundamentally faster at these kinds of operations... the RAM is simply 10x higher bandwidth and we all know its a RAM-constrained problem.
I think in principle you could clone your database in prod and at least test to see if your most difficult + common queries are indeed faster after running through the LLM optimizer?
Frankly, just a general extension to feed a query log to a batch job to do offline optimisation of common actual reoccurring query shapes based on a query log might well be worth it.
> I think in principle you could clone your database in prod and at least test to see if your most difficult + common queries are indeed faster after running through the LLM optimizer?
That is the responsibility of whoever thought it would be a good idea to write this article. It's their responsibility to show that their idea has merit, and that their results are significant. I mean, don't they have a vested interest in manipulating and cherry-picking their results to inflate their relevance?
I had the similar feelings, the setup is biased for certain outcomes it feels. At times I feel like that I am in an eternal questioning mode but then again I find it to be a better choice to be critical and skeptical for technology related things.
Couple things that I found interesting
1. Inefficiencies/limitations of the query planner in certain cases are known for a long time, its a trade off. This is the reason why hints exists and one can provide their own plan too. DBAs have been doing that for a while now.
2. A SQL database by design is a resilient unit in itself just dependent on CPU and memory/disk. The availability for the DB is heavily dependent on this factor. Everything built on the top derives their availability and reliability from this. Adding an LLM in between is more cost for sure, question is if its really brining the benefits which are worth the trade off
Optimal plan construction is math-heavy, algorithm-heavy and vary even by workload. There are options like creating just-in-time indexes, so solution space grows even faster than article presents. Sometimes it is the query planner which is the slow part of total execution time.
LLM is kind of blunt weapon to use here. I am waiting rather for alphago style neural net heuristic.
GEQO is not to get a better plan than the traditional optimizer, it is to be able to get a plan at all when the query is large. And it's widely known for creating poor plans.
Yes, but it's exactly the kind of hybrid between a regular planner and something generative (writ broadly) that they were asking about. Practically speaking if you're hitting the GEQO you've already failed as a query writer unless it's a purely OLAP on a dedicated beefy machine.
I think calling GEQO generative is a bit of a stretch; it's just a different way of searching through the same space with the same cost model. More or less devolving to “let's take a bunch of randomized join orders and see which one is best” :-)
And yes, large joins is definitely for OLAP use. If you have 20-way joins for OLTP, you're either crazy or you're using an ORM.
I also wondered why an LLM would be the right starting point. Why would Balzac or billions of lines of rwir code or reddit be relevant to mapping this smallish, well-defined language (SQL) to this other tiny constrained specification language (the query plan suggestions)? You could make a (relatively) tiny network and then actually pass it some relevant features of the actual data, like as numbers, not just as text returned from a tool call.
It’s ultimately based on a lot of hand-written heuristics. Google has some non-LLM based machine learning technique to guide optimization heuristics in LLVM; that would be closer to what you are looking for.
It was a good read and I liked the authors methodology, but I he did so many things write, but why did he train and test on the same dataset, even though it was cleaned up. This seems like an approach an LLM might take or a human that hasn’t taken the walk to get into system 2 thinking. He would’ve saved himself the query topology part.
Engineer: "HELP, our production DB is frozen on this query that worked fine before!"
Infra: "Hmm, let's check... Well would you look at that, it seems like your LLM query planner usually works and produces fast queries, but this time it happened to hallucinate and miss an index, would you mind re-running the LLM a few times until you get a faster query?"
By people with a specific skill set. LLMs generation can also be fixed and verified by people with a certain skill set, and non-deterministic computing doesn't automatically mean unpredictable. When people say that the LLMs are a black box, it means unpredictability in unknown situations.
You do structured output, input validation, output validation, lower temperature, limit decisions, RL, etc. to increase predictability to near certainty. It's just statistics after all. Or you can as well generate the code to do the job.
It's just that the required skill set is a different one to do those things, and unusual in the context of DB administration.
Only if someone is planning on running a pinned self hosted version of an LLM alongside the DB to fix the problem. The developer can change the binary easy enough and test it but the LLM approach just seems either theoretical or bending ourselves in knots to justify using an LLM.
I didn't argue that it'd make sense, I just said that it could be reasonably fixable when problems occur and verifyable that the fix works. Even if shipping and RLing an LLM were easy tasks in terms of software distribution (they are not) we'd still hit the skill mismatch, as I said in my previous comment.
I just find the "all llms are non dererministic and therefore unreliable" narrative a bit backwards. All software that has more than 0 users needs to deal with non-determinism anyway :)
None of the things you mention are guaranteed to increase the probability of correctness. You can run the LLM output through as many deterministic programs as you like, but "the query plan runs in acceptable time" is not something you can verify with such a tool. Nobody knows how the LLM does it, so they cannot know how to make the LLM do it better.
Even if the query plan was not generated by an llm, you can't verify it will run in an acceptable time. This is one of the biggest unsolved problems in databases
From a completely technical perspective (outside the LLMs are blackboxes scare-mongering), we know how the LLMs work. While we always have an idea, we can't know what they would generate with unknown input, but that's the same if you imagine putting a pumpkin in a rockets fuel container.
Also, yes, you're right, we still didn't solve the halting problem - neither the LLMs.
This isn't a conversation about the guts of query planners but postgres is known for what can only be described as gremlins in the query planner. But that is not the same thing as being non deterministic.
A bad query plan is not your typical kind of bug. I would definitely not call it fixable. Query planners are inherently dealing with estimations and approximations. If the query planners estimation is off, you're screwed.
Unless you come up with a way to cheaply determine exactly how many rows a query will return, bad query plans will still exist.
Indeed. I'm honestly shocked that we're still having to evict bad query plans in 2026. And "you changed a variable", ha, that sounds like an actual reason. How about "data statistics were automatically refreshed and you hit some magical undocumented heuristic threshold, an the query that ran in 35ms yesterday now takes 45 minutes. And we can actually tell you this because we have the data, but decided to let you find out manually, instead."
I've literally been saying "I can't believe the date is X and we still have to put up with this" for around 25 years now.
Our database 12.34 was working great, but 12.35 deployment had some optimizer changed that had regression on exact scenario that you have in your statistics. Shit happens, sorry. Use this hint.
The answer to every problem is to rebuild statistics. (And never use stored procedures. It's just a shitty API layer in the worst language imaginable, sitting outside of source control. If you need an API layer, write it in a real language, ideally the one you're already using.)
Rebuilding statistics will not help if the cause of the bad plan is something that the cost based optimizer is not even trying to model.
Stored procedures in this context are just a clumsy workaround to control the planner so your comment about API layer is irrelevant. But if you like, you can write stored procedures in a ton of different languages. And if you do not have source control for your database artifacts, you are doing it wrong. A common reason for stored procedures is to not have a bunch network roundtrips in the middle of your transaction logic while you are holding onto locks / have an open conflict window.
SQL Server stored procedures don’t provide a separate tier of T-SQL functionality. Anything they do to the data can generally be expressed and executed as a T-SQL batch. If you think you need stored procedures to avoid network roundtrips in the middle of your transaction logic, you’d be wrong.
Stored procedures are essentially a crude, database-bound API layer. For serious application development, a proper service layer provides stronger contracts, authentication, testing, versioning, observability and source control in a sane general-purpose language, ideally the same one you’re already manipulating the data with elsewhere.
Meh, a T-SQL batch is just an anonymous transient stored procedure.
I guess we agree on them being an API that gets deployed on the database, but I disagree that it needs to be crude. It's exactly as crude as you make it. If you don't have authentication, testing, versioning, observability and source control for your database you are doing it wrong.
The issue is that a query plan that works when the data is in a certain state can fail when the data has changed. And the pesky business insists on constantly changing the data for some reason
This was a thoroughly enjoyable read, both the writing and presentation. I really liked the level of writing as it's basically introducing a whole lot of advanced topics but at just the right level for a non-AI researcher type of engineer like myself to be able to understand what's going on, and I felt it made some elements of LLMs actually something I could understand rather than wizardry done by maths PhDs. Probably because it's more like applied engineering rather than hard mathematics here. Thank you for a delightful post.
81%is nothing. It is not hard to be more than 3x better than Postgres [1]. And you don’t need a model to do that, let alone a 4B model. How much additional compute is needed to just run that model?
Nice article about how to train/fine-tune a language model.
However, it misses the whole point of database query planning. You can't just ignore the planning time itself, as if the database query were a static entity to be optimized once at a leisurely pace.
The real constraint on live query planners is quite different: they must improve the combined time - planning + query - based on live database statistics. You can amortize the planning with prepared statements, but that too is fraught since optimal plans can change quite frequently and based on input parameters. "Live" and "faster than the queries themselves" are the hard requirements to be considered a viable database query planner. This project does neither.
Unless they’ve been elided, there were no indices other than the PK on any table, and no additional statistics. There are correlated columns here: a given country may have produced more movies in a given range of years, as its movie industry built up; a given country may produce more TV series than movies, etc.
Nearly every time I’ve seen someone resorting to hints for a query, it’s because their statistics are incorrect. Adding hints is papering over the problem, and can backfire later if the data shape changes.
>Nearly every time I’ve seen someone resorting to hints for a query, it’s because their statistics are incorrect. Adding hints is papering over the problem, and can backfire later if the data shape changes.
YES. And hints ossify and at some point become useless or even wrong - at least that is my experience analyzing old Oracle queries.
But many people don't want to hear it because they just want a quick fix. Not having hints in PG has been a godsend for longterm quality at $dayjob, because people are forced to think.
Suppose you run a platform, and you run a couple thousand different queries of different types throughout the day. It would make sense to have an auto-optimizer that would read long queries, ponder over them with an LLM, come up with some good plans, and store them as hints. This seems like quite a good idea? Is there a product for this?
140 comments
[ 3.4 ms ] story [ 102 ms ] threadThis is not to say that it’s possible to genetically verify that a proposed algorithm does what you want it to — that would be undecidable or NP-hard or co-NP-hard depending on how you formulate the question.
sure optimizations based on stats, but the stats are the wildcard, in my experience query plans can change suddenly.
Queries are translated into plans according to statistics. However the transforms will be deterministic and should only change one valid plan to another. I could very easily see a neural network manipulate transforms the same way the current programming does, its just that the neural networks are by nature really nicely suitable because the "decisions" are based on training, and this training can be closed world type things like the ai assists that chess engines are now getting. Obviously ai still can't play chess but apparently its very good at ranking board positions just by developing that much statistical info because its training comes not from reading the web, but playing a gazzilian games against itself in a "closed" chess world of its own.
I'm thinking that the ai does "this legal transform of the query plan should be applied to this pattern of data (statistics, cardinality, etc)" simply because the ai encountered it in closed world training, much like the chess thing.
Just a theory tho feel free to correct!
I believe you are wrong on that. Do you mean large language models can’t play chess?
> a tiny 4B model went from not being able to understand the harness it was wrapped in, to achieving a 1.81x geometric mean speedup and a summed latency decrease of 44.7% across a workload of join-heavy SQL queries
I can’t find it in the article (may have skimmed it too much), but I suspect they didn’t include those ~95 hours in the benchmark numbers.
I think all database vendors know their query optimizers could do much better if they could afford to spend lots of time to derive query plans.
⇒ this may be useful for some workloads, but even then, can you afford to spend hours every now and then to update your 4B model to ensure it still picks a good query plan?
I think this would be likely comparable to a scheduled backup, so I think it would be an acceptable maintenance window. However, deterministic algorithms would likely beat re-training (or re-fine-tuning) the model. For example, one could analyze actual distributions or whatever (instead of assuming uniform), and then some plans would automatically be eliminated.
Imo a good thought experiment is to look at places that are hyper-optimized, like compilers. Would LLMs bring anything to the table (architecturally or performance-wise) to a piece of software that has been carefully crafted for decades? (Methinks no.)
As an example, register allocation is graph colouring, and thus NP complete; a model for producing an allocation plan is learning heuristics that might look at more features in combination than the ones hand-crafted into the compiler. An LLM for the job might do better than a more focused model like a GNN, due to sheer size, the effectiveness of transformers, or magic. But it probably won't do an overall better job than the handcrafted heuristics, because those handcrafted heuristics also tend to compile very, very fast with a small memory footprint, and can be debugged (more) easily when they go wrong.
Postgres keeps histograms (including N most common values) for all columns; it does not blindly assume uniform distributions. (Presumably an LLM would have access to the same histograms.)
‘create plan llm_optimized …’
‘create view foo (select x, y, z from table bar) with plan llm_optimized’
Wouldn't admitting this invite trouble due to accusations of distillation flying around between closed and open models.
It's very hard for them to claim the moral high ground here.
It's like stealing an apple from the British Colonial Empire.
Need 5 days just to go through it.
Also you can now ask AI to summarise it for you and even probe with questions pertaining to your specific interests.
I've asked myself that question a whole bunch of times
At least as I understand things.
How did you plan to use Jev for query optimization?
I would be cautious about over fitting, it’s tough to say if those query plans would really be more optimal than Postgres heuristics at scale and with a bit more realistic OLTP workloads.
In any case, such is life with profile guided optimization. Many of us appreciate how database workloads can drift over time and with scale.
Kudos to the author for getting their hands dirty and writing up their experiments.
At a certain point we should seriously talk about CUDA accelerating Postgres instead.
8GB isn't even CPU RAM these days. That's GPU super-mega-awesome ram. Ordinary Server CPUs are regularly pushing 2TB capacities.
GPUs are in the 8GB to 32GB typically, at least for smaller and more regular GPUs. This GPU RAM is also well known to be at least 10x the bandwidth of CPU RAM.
GPUs are simply faster at fundamental algorithms like sorting (which has huge parallelism), and hashing. This is because both sorting and hashing are in Nick's class, offering enough "work" for these 10,000 SIMD-core systems to crunch work upon.
And because of modern algorithms/libraries with 'Mergepath sort' (a GPU-SIMD parallel sorting algorithm), its not even that difficult to implement anymore.
Naturally, this then leads to parallel Sort Merge Join, as well as parallel Hash-Join (two ways to implement left or right joins in a GPU that benefit from significant parallelism).
So yeah, Joins. https://www.kenchoi.dev/papers/gpu-joins.pdf (This paper also has a description of "Mergepath sort", a GPU parallel way of sorting)
---------
Even if GPUs weren't fundamentally faster at these kinds of operations... the RAM is simply 10x higher bandwidth and we all know its a RAM-constrained problem.
It can be. That was the big new innovation in video game load times
That is the responsibility of whoever thought it would be a good idea to write this article. It's their responsibility to show that their idea has merit, and that their results are significant. I mean, don't they have a vested interest in manipulating and cherry-picking their results to inflate their relevance?
This is why academic papers are peer reviewed.
Couple things that I found interesting
1. Inefficiencies/limitations of the query planner in certain cases are known for a long time, its a trade off. This is the reason why hints exists and one can provide their own plan too. DBAs have been doing that for a while now.
2. A SQL database by design is a resilient unit in itself just dependent on CPU and memory/disk. The availability for the DB is heavily dependent on this factor. Everything built on the top derives their availability and reliability from this. Adding an LLM in between is more cost for sure, question is if its really brining the benefits which are worth the trade off
LLM is kind of blunt weapon to use here. I am waiting rather for alphago style neural net heuristic.
- a question from someone with lack of DB depth, me.
https://www.postgresql.org/docs/current/geqo-pg-intro.html
And yes, large joins is definitely for OLAP use. If you have 20-way joins for OLTP, you're either crazy or you're using an ORM.
Infra: "Hmm, let's check... Well would you look at that, it seems like your LLM query planner usually works and produces fast queries, but this time it happened to hallucinate and miss an index, would you mind re-running the LLM a few times until you get a faster query?"
By people with a specific skill set. LLMs generation can also be fixed and verified by people with a certain skill set, and non-deterministic computing doesn't automatically mean unpredictable. When people say that the LLMs are a black box, it means unpredictability in unknown situations.
You do structured output, input validation, output validation, lower temperature, limit decisions, RL, etc. to increase predictability to near certainty. It's just statistics after all. Or you can as well generate the code to do the job.
It's just that the required skill set is a different one to do those things, and unusual in the context of DB administration.
I just find the "all llms are non dererministic and therefore unreliable" narrative a bit backwards. All software that has more than 0 users needs to deal with non-determinism anyway :)
From a completely technical perspective (outside the LLMs are blackboxes scare-mongering), we know how the LLMs work. While we always have an idea, we can't know what they would generate with unknown input, but that's the same if you imagine putting a pumpkin in a rockets fuel container.
Also, yes, you're right, we still didn't solve the halting problem - neither the LLMs.
Query planner feels pretty LLM-esque already
A bad query plan is not your typical kind of bug. I would definitely not call it fixable. Query planners are inherently dealing with estimations and approximations. If the query planners estimation is off, you're screwed.
Unless you come up with a way to cheaply determine exactly how many rows a query will return, bad query plans will still exist.
I've literally been saying "I can't believe the date is X and we still have to put up with this" for around 25 years now.
Stored procedures in this context are just a clumsy workaround to control the planner so your comment about API layer is irrelevant. But if you like, you can write stored procedures in a ton of different languages. And if you do not have source control for your database artifacts, you are doing it wrong. A common reason for stored procedures is to not have a bunch network roundtrips in the middle of your transaction logic while you are holding onto locks / have an open conflict window.
Stored procedures are essentially a crude, database-bound API layer. For serious application development, a proper service layer provides stronger contracts, authentication, testing, versioning, observability and source control in a sane general-purpose language, ideally the same one you’re already manipulating the data with elsewhere.
I guess we agree on them being an API that gets deployed on the database, but I disagree that it needs to be crude. It's exactly as crude as you make it. If you don't have authentication, testing, versioning, observability and source control for your database you are doing it wrong.
(Indexes would be nice though)
[1] https://github.com/datalevin/datalevin/tree/master/benchmark...
> As it turns out: enormously hard.
This exactly tracks me learning everything
However, it misses the whole point of database query planning. You can't just ignore the planning time itself, as if the database query were a static entity to be optimized once at a leisurely pace.
The real constraint on live query planners is quite different: they must improve the combined time - planning + query - based on live database statistics. You can amortize the planning with prepared statements, but that too is fraught since optimal plans can change quite frequently and based on input parameters. "Live" and "faster than the queries themselves" are the hard requirements to be considered a viable database query planner. This project does neither.
Nearly every time I’ve seen someone resorting to hints for a query, it’s because their statistics are incorrect. Adding hints is papering over the problem, and can backfire later if the data shape changes.
YES. And hints ossify and at some point become useless or even wrong - at least that is my experience analyzing old Oracle queries.
But many people don't want to hear it because they just want a quick fix. Not having hints in PG has been a godsend for longterm quality at $dayjob, because people are forced to think.
Let’s talk when you are looking at double digit TB at a minimum.