Nik and Michael discuss query plan flips in Postgres - what they are, some causes, mitigations, longer term solutions, and the recent outage at Clerk. Here are some links to things they mentioned: Recent postmortem from Clerk https://clerk.com/blog/2026-02-19-system-outage-postmortem The real cost of random I/O (blog post by Tomas Vondra) https://vondra.me/posts/the-real-cost-of-random-io autovacuum_analyze_scale_factor https://www.postgresql.org/docs/current/runtime-config-vacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET pg_hint_plan https://github.com/ossc-db/pg_hint_plan Aurora PostgreSQL query plan management https://docs.aws.amazon.comAmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Start.html pg_stat_plans https://github.com/pganalyze/pg_stat_plans pg_plan_alternatives https://jnidzwetzki.github.io/2026/03/04/pg-plan-alternatives.html Waiting for Postgres 19: Better Planner Hints with Path Generation Strategies https://pganalyze.com/blog/5mins-postgres-19-better-planner-hints ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
Michael: Hello and welcome to PostgresFM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard, and I'm joined as always by Nik, founder of Postgres.AI. Hey Nik! Nikolay: Hi Michael, how are you? Michael: I am good, how are you? Nikolay: Very good. Fantastic. Oh Michael: great. What are we talking about this week? Nikolay: Not about AI. Michael: Yes, for a few. We have a detox. Nikolay: Everyone is talking about AI, so we are not going to
talk about AI. Michael: Great, so what are we talking about? Nikolay: Let's talk about plan flips and it's quite a complex topic because I feel Postgres lacks a lot of stuff and I'm not sure what it needs to have in future to be better. And there is a lot of criticism, including the recent one. A recent wave of criticism against Postgres in this area. After some incidents from one company, the company name is Clerk,
right? Yeah. Clerk. Clerk. How do you pronounce it? Michael: I say Clerk. Nikolay: Clerk. Okay. That's interesting. Michael: So it's E, Nikolay: but you say I. I don't know. Yeah. Okay. Yeah. And there was an outage or series of outages. I don't know. Michael: Yeah. Nikolay: Two weeks ago. And I'm as usual kudos to all companies who share experience. This is great. They shared experience. They had a blog post with timeline and root cause analysis to
some extent and then I saw on Twitter, I saw discussions which looked like Postgres is not good enough, let's choose better systems because look what you might have with Postgres. Michael: Yeah, look what can happen and... Nikolay: Yeah, might happen, can happen. Michael: Exactly. Something quite innocuous. So it was, they've said the plan flip was the, maybe not the root cause, but the thing that took them down and due to insufficient
statistics but yeah they're an infrastructure provider right so they they do authentication and if auth is down that means all of their customers are down yeah exactly so this is a huge issue for them and for all of their customers. They are very wary of things that can change unexpectedly, understandably. But equally, lots of services that need extreme high availability run on Postgres, so how can they do it? I think that would be a good discussion.
Nikolay: So in my opinion, their root cause analysis is halfway done. And naming plan flip A root cause is not all right. Why? Why 5 why's? You know this approach. Michael: Yeah, I think to a... Nikolay: Yeah, basic approach for root cause analysis is ask why until you cannot answer at all. So their analysis was saying like, the problem happened because Postgres triggered Analyze, obviously, automatic Analyze by autovacuum
process, which caused plan flip. So they did only 2 steps in this 5-y approach. They said okay what happened? Why we are down? Because statistics changed. Why is this? Because plan flip. Why? Because it was triggered by autovacuum. 2 of 3 steps. But you can go deeper, right? Yeah. You can go deeper. I think there was 1 Michael: more, I think they did increase statistics target for example to make it less likely to happen again but there still
are more steps for sure Nikolay: yeah they increased default_statistics_target? Michael: Maybe not default but for the table I think. Or for the column, I forget which. Nikolay: Yeah, it's either Column or global. Anyway, that's interesting because in my opinion there are different plan flips, different kinds of plan flips. And I'm still trying to figure out for myself, but if you have plan flip when suddenly like it flipped after recalculation of
statistics, that's interesting because that might happen. This is actually a difficult type, this is a hard type of plan flip. But the question is why it happened only now, not 1 hour ago, for example. Michael: And what's to stop it happening again? Nikolay: Yeah, yeah. And this is interesting, right? Michael: So I have some thoughts on this specific case but I think it might actually be useful for us to go back to basics a little bit and define what do we mean by a plan flip and what the normal causes of them and I think this is a little bit of a special case that should be interesting and might be completely
avoidable I think, but a lot of cases aren't avoidable and I think that's an interesting discussion. But yeah, in terms of plan flips, I wanted to ask you, do you define them only when they're bad because we have a cost-based optimizer, right? The reason we can flip query plans is because the planner has choices and it makes those choices based on estimated costs of doing different join algorithms or different scan types. But flipping is part of its design, right?
The same query running on different data as the data changes, perhaps should use a different execution plan. It would be faster to use a different execution plan. So flipping is designed, it's deliberate, but the idea is it flips to a better plan when the data suits that. Nikolay: So sudden plan change is normal. Michael: Yes. So, yes. When people say plan flip, it feels like they're only ever Nikolay: talking about that. Michael: Yes, exactly.
Nikolay: Yeah, because this is when it changes to wrong state. Because the planner sometimes have mistakes. Michael: Yes, or misestimations. Nikolay: For example, if you have random page cost 4, which is default, I saw many situations when we have 2 candidate plans which have very similar cost under these circumstances.
So for this data and what we have in pg_statistic, and for these settings, including random page cost 4, which is default, I saw the cases when there are 2 plans. 1 is obviously wrong. For example, sequential scan for huge table. And another is obviously right, which is index scan. Like I'm remembering some Simple case, like simple in terms of it's obvious to a human or even AI that this plan for this table shouldn't be chosen because it's sequential scan on whole table,
but since for example Postgres thinks that random I/O is so expensive suddenly chooses sequential scan plan instead of index And you don't notice until some point when these costs, they are very close to each other. And then suddenly when a little bit more data comes to the table and ANALYZE happens. Sequential bad 1 wins and that's it. And this we usually solve by adjusting random_page_cost.
Telling the planner that please don't choose sequential plans. I know we had, like, Tomas Vondra, Tomas Vondra, has great proof that maybe random_page_cost 4 is not bad. Maybe even it should be bigger. But I have brain split here because I see what his reasoning is makes sense theoretically, but what I saw in production proves opposite. I saw these plan flips happening when just like suddenly, and
it was like some ticking bomb it was like yeah it was sitting there sitting and then boom like suddenly the planner chooses the sequential scan and we are down because the time's out right Michael: yeah I have a theory about this I've been thinking a little bit about it and I think it's like, Tomasz's work is very good in theory because he cares about the optimal time to flip, he cares about how much data do we need to be reading before
sequential scan is faster? But in practice, especially with large tables, the flipping point is not that important. The main point is we almost never want to request that much data. Often, especially in OLTP, we want very small SELECTs. We're only selecting a time, 1 record or only a very few number of records. So the flipping point, we want to put so far out of sight that even if our row estimate is really bad, because often it's a combination of 2 things right it's not just the the cost of doing
random access it's also how many rows do we expect to be reading and if that's out by a lot and the cost is high those multiply together and suddenly Postgres is thinking you know what I might as well scan the whole table here because I'm expecting to read so much. But if we can, if we decrease the random_page_cost, we get away with the bad row estimate. So I think you can fix either and get towards it. But if you do both, then you massively decrease your chance Nikolay: of Michael: this kind of issue. Nikolay: I hear you and this in my mind connects very well to
the topic of OLTP versus analytical versus hybrid because there are good advancements in the area of analytical queries processing for Postgres in various ways. And there is also, I'm strongly believe that there is a very simple case called pure OLTP. Pure OLTP means that We need to serve users who are humans mostly, maybe, and they have human perception. Our very first episode was about this. 200 milliseconds, this is when they recognize something is already
happening. And If it's significantly more than 200 milliseconds, it feels slow, which means we need to have our queries below 20 milliseconds because 1 web page consists of multiple queries, usually roughly 10, say 10, like some guess, right? It means we need to be below 20 milliseconds, in this case, by no means I want any sequential scan be applied. Never, like, I don't want it, like, just forget about it. I want to turn it off completely.
I Michael: would encounter that Sequential scans on tiny tables are fine, right? If it's only got 100 rows in it, but also an index scan on that table would be fine as well because it's still only got 100 rows. So the times where sequential scan is best don't matter anymore because an index scan, whilst being less efficient, or even a bitmap scan would be absolutely fine, even if it's twice as slow, or even 5 times as slow because it's such a tiny table. Nikolay: But we also have different other, there is also bitmap
scan, index and heap scan, So it also, it's like in the middle and how the planner thinks about the cost. It's interesting. So I think how we tune some service for OLTP, and this is obviously OLTP. People didn't want this service to, Michael: of course, Nikolay: Yeah. We definitely want very strict timeouts. I think 15 milliseconds, it's old. We should go down to 10 seconds. So statement timeout should be 10 seconds.
Even transaction timeout should be 10 seconds for radical. Let's introduce concept of radical OLTP. Yes, you have some workloads that needs to be analytical and some people can wait on or some background jobs. Yes, the create index taking a couple of hours. It's also a different story, but if it's pure OLTP mobile app, which many people depend on, put transaction timeout and statement timeout, very low value, be very strict, and just cut those queries which start running longer.
In this case, if you, for example, have a bunch of them happening, there's a chance that on powerful machine, you will survive. If you don't have it, it will be a snowball effect of many sessions being stuck and running in sequential scan or something like a not optimal and it will be consuming resources. Michael: The other thing it might do is it might more quickly
point you in the right direction as to what's causing the issues that you start... Nikolay: Yeah, how to diagnose. Let's maybe, I think I'm pushing us to some like problems and resolutions already but let's go back. You raised a very good point about what plan flip is so in my head, it's also like this is negative term plan flip is Sudden plan change in the wrong direction when like basically the planner Chooses a plan we don't want like sequential scan or maybe bitmap scan which is timing out for example.
Michael: Yeah I think that it might even be worse than that I think it might even be like it's noticeably worse. Nikolay: If Michael: it flips and it's 1.1 times slower I think most people don't really care. But if it's double or triple or it's like it takes you from being 90% CPU to 100% CPU It crosses that threshold. Maybe again. It's the Tomas episode we did on performance cliffs It's a cliff for you then. Nikolay: Yeah in my opinion performance cliff term is different and it's macro Yeah, while plan shape is micro term.
It's like it's just 1 session and we see suddenly execution of this query became much worse. It was 10 milliseconds, suddenly became like 1 minute. Boom. And obvious example is like we had an index scan, but suddenly the planner... And usually it happens in complex queries, right? So joins, subqueries, CTE, multi-stage, like it's something complex. And then boom. While performance cliffs for me, like it's macro level, like
some lightweight lock contention happens and didn't happen and then suddenly it happens. There is no official terminology here. So I'm just reading some blog posts and trying to adjust my own mind to them mostly, right? This is how I feel. So plan flip is negative term, right? So we don't want it to happen. And we started with difficult example. This company obviously experienced this difficult example. And my question to them is like, what random page cost you had?
Did you have statement timeout, item transaction timeout, maybe not related in this case, and transaction timeout? So safeguards, safeguards which would protect from a huge wave of sessions which experience this bad plan to be executed and also what were your autovacuum settings. I think Michael: that's relevant. Nikolay: Before we go to an increased number of buckets with
statistics target, these are macro settings we need to keep in mind. Michael: I had another thought. This for me felt like a state of skew issue. And so they mentioned the column in question was 99.9996% NULL values. And this query was looking for the not null values which of which there are still 17,000 so we're talking about billions of records
but only 17,000 were not NULL. In cases like that, where you've got such a data skew, you could be looking at, like for those columns you could be looking at increased statistics or often you want a partial index on a column like that right because you don't care about indexing the billions of NULL values you only care about indexing the not null values and once you've got the perfect index on that I know it could be a more complex query where you want different columns but if you've got those columns in the index at where the value is
not null then actually the planners almost certainly going to choose that because it's such the perfect index for that query and you don't have to worry about misestimations it's never going to pick a sex scan in that case. Nikolay: So we definitely lack information here. Yeah. We didn't see the plans. We don't know their settings. We even don't know the version. Right. So we can only guess. And again, there are difficult situations with plan flips. I saw quite difficult ones and where obviously like everything
was normal. But what I'm trying to say, you can get a plan flip because you didn't do a good job configuring Postgres well enough and putting these safeguards for OLTP workloads. And this is what we do, number 1, with all the clients who are doing consulting and which also the clients who install our monitoring and checkup service. And this is like number 1 thing we need to be, we recognize we are OLTP. Priority is to have the super fast queries and we need to prepare
basics, right? Unfortunately, they cannot protect us in 100% of cases. But if you have your autovacuum not tuned, for example, you have this relatively new setting. I remember Darafei raised this topic. We didn't have autovacuum insert, autovacuum vacuum insert scale factor. In autovacuum analyzing, there are 2, right? So which triggers on insert?
And by default, it's 20%. And also we have all the regular vacuum scale factor, which triggers on updates and deletes, also 20%. For analyze, it's 10%, 0.10, which means 10% of your table needs to be changed. If it's a huge table, it's a lot of data. Again, this data skew is a good point. You had 1 distribution, but suddenly all the new records, they are very different. And you don't have properly tuned autovacuum, you accumulate
that data a lot. And then suddenly after 10 or 20% of data changed or inserted, autovacuum finally ran analyze. Suddenly we have a flick. If it happened earlier, maybe it also would be bad. It's actually a good point. Maybe still we need the statistics target, but we, like in my opinion, it's better to have more frequent process running on large tables. So statistics will represent actual data better, more often,
right? To allocate more resources, to have more workers. This is what we do all the time with larger services. And unfortunately, we see, like I would say 2 out of 3 clients coming, most of our clients are startups, which like have say database exceeding 100 gigabytes up to 10 or 20 terabytes. This is like sweet spot for us where they experienced some problems already and 2 out of 3 roughly have poorly tuned autovacuum.
Poorly tuned. Michael: Only 2 out of 3 is quite surprising to me. Nikolay: Yeah this is my rough. I can check statistics you Michael: need to update your statistics Nikolay: my internal yeah I need to ask a guy to maintain statistics Michael: anyway but I had a question though yeah default_statistics_target is like 100 and I quite often see people increasing it to 1000 when an issue like this happens or at least for like
specific columns other than increased analyze time which is like important but not that important for yeah and it doesn't increase that much and also it's not that important for OLTP systems right like it's a background thing it can be like it's I'm wondering... Nikolay: I'm not really sure during upgrades though. Michael: Only some types of upgrades, right? Because if you've got such an important infrastructure OLTP system, you're probably not going to be doing pg_upgrade. Like, you're probably not going to be doing those types of upgrades that care about analyze.
You're probably going to be doing logical failover. Nikolay: You still need to analyze. Still not. You create this logical replica, you need to analyze this replica. Michael: Not while you're, you don't have to be down during that. That could be done while the other system's serving queries. Nikolay: You are right. You are right. Michael: And the statistics will have been getting updated while it's being populated because vacuum will be running and analyze will be running. So it's not in this. So I was wondering for really important OLTP systems, why not
just boost the default_statistics_target everywhere and just pay that extra cost? Nikolay: Did we raise this question with Tomas Vondra or no? Michael: I don't think so. Nikolay: Maybe we raised it when he was on PostgresTV YouTube channel. I remember the opinion that 100 buckets is a lot. Right? This topic connects to partitioning for me. Because when I see a table with 10 billion records, like a table like multi-terabyte in size, It's, oh, this is like already quite
late. We should have it partitioned. In this case, we would have many tables and at least we would have localized problem. Only for this partition we have a problem. Michael: And we would have, let's say it had 30 partitions, we would have 30 times more data in the samples Nikolay: because it... Different statistics, yeah. Yeah. Yeah. So I don't know. I see people raise it. I like, okay, does it affect or analyze? Yeah, it does. How much we can afford it?
Okay. Set it to thousand globally. Okay. How to prove it? It helps or not. We need holistic analysis of plans. So we need, This is actually, we tried to do many years ago before like current state of art of like things with branching and so on. Actually I disagree. I've Michael: just realized I disagree. I don't think this is about on average improving things. I don't think this is a case of on average I want a slightly better execution plan. I think this is a case of I never want to completely flip to an awful plan.
So it's about risk mitigating, it's like mitigating the outliers not about improving. I would actually accept slightly worse on average latencies. Nikolay: I don't talk about average. I don't talk about average. I'm talking about complete analysis. When you have, for example, thousands of plans based on production, you have a clone of production and using branching you can create replicas and compare plans before and after the change. This is what if framework which we have with Database Lab. And you can compare plans.
There's a question how to properly compare plans at the ground scale. We talked about this before. Our approach is just compare structure and cost and the buffers and also timing. But timing, it's tricky because it's volatile and so on. If cost changes seriously, this is already a signal to investigate what happened. If structure changes significantly, also a signal. Structure, by the way, maybe like you don't need the whole structure, you probably just need to access methods like you had 3 index scans, suddenly you have 2 index scans and 1 heap scan, sequential
scan, right? So it's bad, right? So what happened? Anyway, this holistic approach is great. So you can have a huge amount of plans, and then you have the ability to do the change and compare before and after. Again, I haven't visited the topic of default_statistics_target particularly in this context for quite a while, but it would be great to do it. And again, I have a bunch of unfinished thoughts. This topic, plan flips, is unfinished in my head.
I'm still thinking what's better. So once again, you don't need average or something. You want, it's a classic optimization problem. You want to optimize something and you don't want everything else to degrade significantly. You say, I'm okay if something degrades like maximum 10% on some metric There's a question which metric to choose also, but I want this to be improved. This is 1 thing. So we have framework We don't have all the answers yet Second
thing is how to understand we are on the edge of flip. Or cliff if you like, doesn't matter. How to understand. Yeah, I think, Michael: I actually don't know if this is important as much as how do we stick with what we've got. I think Nikolay: I want to predict the flip. Michael: Yeah, but I think it's really difficult to predict bad plan flips without also predicting good ones. There would be so many false positives. If you think about how you would do it. Nikolay: I forgot to mention that in our case, We execute on
real database, full size, full clone, using database branching provisioned very quickly. And we see not only planner, but executor in action, like realistic testing, right? Full-fledged realistic testing. We execute hundreds or thousands of queries, samples properly collected. So it's not we have 100 samples and 90 of them is the same query ID. We take query ID, we collect multiple samples for each query ID, and then we execute before and after the change.
We developed this framework specifically to predict plan flips for major upgrades. Michael: Yeah, which is, it's slightly different, right? I think Nikolay: it's different. Michael: I think it's different. I think why it's different is often people talk about plan flips in the context of I didn't change anything and this thing just happened yeah whereas with a major version upgrade you know in advance you're doing Nikolay: it you Michael: can look it yeah you you have this reference point if
we change you Nikolay: have new angel big Michael: yeah yeah So I think psychologically at least it's different. I understand that in practice there's a lot of the same harnesses. Nikolay: So that's why I'm saying there are easy plan flips and there are difficult plan flips. Easy is when you did some action, you upgraded Postgres. You jumped between 13 to 18, 5 versions for example. There are new nodes and plans, right? Never existed before. So definitely Michael: new settings, new configuration parameter defaults. Nikolay: In my opinion, plan flip analysis should be a part of
major Postgres upgrade. And we have it for our clients. Yeah, we have it for our clients. It still requires some, like It's semi-automated right now. I hope at some point we will fully automate it, and it should And it shines when you have Database branching properly implemented. But there are difficult plan flips. So to explain how to properly categorize it, let's a little bit stop and think about what the planner, what does it
take into account when choosing plans? It's only 3 things. It's version, which defines the version of the planner, basically, which engine we are using. And second, it's settings, not all settings. My current vision is like the planner settings plus work, plus work_mem. Why I'm saying plus work_mem? Because the planner settings, there is a category. If you check pg_settings, 1 of the columns will be category, right?
And there is a category called planner settings, but work_mem is beyond. So plus work_mem. And finally, the number 3 is pg_statistic content. And that's it. It doesn't care about hardware or actual data. Michael: Yeah, true. It's not actually the data volume, it's how much data it thinks it has, which is the statistics. Nikolay: Yeah and if the statistics is lagging, it might live in different reality. That's why I say I would prefer, it might still be wrong, but
I would prefer it be wrong sooner and be closer to reality, right? Yeah. So when we change engine, it's obvious we know when it happens. And before that, we can perform this plan flip analysis holistically according to our methodology. This is great. We can predict this plan flip and prepare a resolution, solution for it in advance. This is what we do with clients. Second, if we change settings, basically it's the same. We also know when we do it. For example, okay, we're going to raise default_statistics_target globally.
This will help to this plan. Question, how will other plans behave? We don't want them to degrade significantly, at least now. Same approach can happen, it can be applied. The third thing is pg_statistic content is changing. Again I would prefer to be changing more often, like continuously changing so more often an autoanalyze should happen more often to reflect the current reality. For this we need autovacuum tuning. And Then how to predict?
Michael: Also, you missed something which is we could gather more statistics, like it's a sample and we could increase that. Nikolay: Yeah, this is mitigation activity already. I agree that moving from 100 to 1,000 sometimes might make sense. And honestly, like, I don't have strong opinion here. I have clients with 100, with 1000. In the past, I had clients who thought that going down to 10 is a good idea. I don't have them anymore somehow. Somehow right now, all the thoughts are to direction of raising
this, not to reducing this. Yeah, but I remember we had such discussions. Maybe we should raise it. Counterintuitive ideas, sometimes we know, and we don't have it anymore. Anyway, 2 first things we usually control. We don't have self-driving Postgres yet, so we control it. And we can perform plan flip analysis if we have such tool. But... Michael: Wait, there's 1 more thing. It's not available everywhere, but another thing that does affect this is, for example, if you're using hints with pg_hint_plan.
Nikolay: Okay, hints. Michael: I think that's a big topic, though, in production. Nikolay: That's a great topic. Let's consider the exclusion because it's not a major approach right now. It's not, it's like very rare. Is that true? I hope so. Michael: I think in other databases, this is actually often 1 of the main. Yeah, and Nikolay: you just try to open another like gate or something and I agree let's open it but let's just finish with this second plan while I think about second plan. Again, if you change major version, you control it, you can predict
plan flip. If you change settings, the same thing. You control it, you can predict. If changing pg_statistic content due to data changes leads to some plan flip. This is hard to predict. How to predict it? When I think about it, I want to be able to check the second plan. Or maybe first 5 plans which were like almost winning because in this case I can say okay I see the winning plan current plan
has cost say 500 but also there is a second plan which has cost 490 and it's quite close. If it's close, say, below 20%, if difference is below 20% I will start thinking might it flip soon because this is why I want second plan. Michael: I think this is incredibly tricky though. Maybe it's simple, but for example, parameters matter a lot. Like per query, different parameters. If you get, for example...
Nikolay: Oh yes, parameters matter. Yeah, I agree. Michael: Yeah, a huge amount. If you look at the solutions that SQL Server and Oracle have, bear with me because I think SQL Server and Oracle are a bit further ahead of Postgres. On this, maybe DB2 sometimes has some features in this kind of area. But the solutions they've ended up implementing are around forcing the existing. Yeah, so plan pinning. Nikolay: Aurora has it. Postgres Aurora has it. Michael: Interesting. So yeah, these are the solutions that other databases have ended up implementing rather than detection of, rather than trying
to predict a plan flip, what they try and do is pin it or freeze it and then maybe require approval when it does flip. So instead of trying to predict it, they prevent it and then have you have it as like an opt-in thing. Maybe this change for the better, But maybe you want someone to approve it first. Nikolay: So there are several things here. You touched several things. First, depending on parameters. I agree with you. But in a system which is quite big, we have usually like Pareto principle is working basically.
Like A lot of workload is defined by a limited number of specific plans. And this is how we... In our plan flip analysis, we have methodology to identify not only normalized queries from pg_stat_statements, but also particular examples, particular parameters. So we would reproduce exact queries with exact execution, so plans, which contribute to workload the most.
For example, contribute in terms of calls or in terms of total execution and planned time. So we want to cover like 80% of workload in several directions, in several metrics. And this is the magic, like how we collect those samples and prepare our testing query set for testing, so it would represent the majority of workload. Once we found a way to do it, there is some tail.
Those queries are less important because if you just think about database time in terms of execution and planning, if some query defines only minority of that time, even if plan flips, it wouldn't affect the whole cluster so much right yeah, that's make sense right Michael: It does and actually reminds me of a different idea Nikolay: Maybe I'm wrong you know what it might be like okay now It's only like 1% of the whole time and London due to plan flip boom authority half of time
Michael: It reminds me though of something that I think could help here, it's not in Postgres, actually maybe there's a couple of examples in Postgres core at the moment, but the idea that the plan is chosen and then is stuck to no matter what during execution, it could be, I think Tom has actually brought this up at the end of our call on performance cliffs, it could be adaptive at runtime. If it realizes it's doing, it's chosen a bad plan, instead of sticking to it till Nikolay: the end. We should invite Andrey Lepikhov. Andrey Lepikhov, who I think, yeah, who can talk about this adaptive
query optimization a lot. I consider him an expert in this area. Michael: Great. But do you see what I mean? It's able to adapt during execution. Maybe we avoid the issue, or at least it's way less bad when it happens. Nikolay: Yeah. And also, like, to finish my thought, maybe I said maybe this approach is wrong. Maybe if some plan which contributed only like 1% to whole database time, now suddenly half. Because of this, I think my gut told me that we need multiple
directions. 1 of the directions when we collect this working set, we also order by calls. So it should be quite... So for a query which had the plan flip to influence the whole cluster so much that it causes downtime, it should be quite frequent enough, I think. If it's super infrequent, It should be fine. If it's not super frequent, it means that it won't occupy multiple backends in parallel. Michael: Unless it gets really bad, yeah.
Nikolay: Unless you forgot to tune the statement timeout or transaction timeout. True. Yeah, we have multiple vectors and I think this is a good approach, good enough approach, maybe it can be improved. So I'm very curious about this area. And I'm coming from practice. Michael: Yeah, I do think there's still complex cases where you might have clusters of even the same query in pg_stat_statements could have 4 different plans that are actually all good. But let's say you're a multi-tenant system, maybe small customers look like, or tiny customers have this plan, huge customers that
are typical have this plan, huge customers that have this weird distribution, or maybe like healthcare customers look different for some other concern. Yeah. Nikolay: Yeah. Yeah. And in analysis, again, I said, like, we collect multiple plans per query ID. Why multiple? Because of this. But so we like say top 5 for each query ID, we collect multiple them. But all of them are like, we take from this top end by each metric. So we try to capture like the concept of how influential this
query is and multiple plans might appear there. It would be great to have plan ID, right, and shift aggregation logic there, but we have query ideas remain. I Michael: think pganalyze, Luke, I think did some work on a plan ID. Nikolay: Yeah, yeah, there's a, I saw it and we, yeah, I commented on LinkedIn and it's great work. Absolutely needed, I think. Yeah, it's great work. So then, like, if we have this, the question, 2 questions, like,
first, like, how to understand that some plan candidate is approaching and suddenly can flip soon. Because data is in large tables, data usually is not changing too much suddenly. I agree with you. Michael: It's like gradual. Nikolay: Yeah. Yeah, it's quite gradual. So the planner behavior involving large tables, it's dangerous when we have 2 plans. This is my hypothesis, right? So again, for me, a lot of open questions still. It might be on the edge.
And I want to feel this on the edge for each query idea or for each plan in advance, slightly in advance to be able to predict. I think someone recently had some work in this area to show Plan candidates. I might be mistaken, but I saw something somewhere It didn't dive deep and the second thing is in my opinion hints and plan freeze are 2 great features I can I put them to enterprise But yeah basket like this is for me?
It's enterprise thing because it's interesting doing hints doing in plant phase for larger systems, probably we should have both. But I also understand the reasoning that it makes system complex and it requires a lot of management. There is a price you pay. If you put something, you start to be responsible for it. You need to maintain it. And if you throw some plan, the same thing, you, you're responsible now, right? Basically, do we have capacity to manage it?
Because maybe later it helps less and maybe then it's not helping but opposite. That's the problem. Michael: Absolutely. But I do think it's interesting that these other databases, given the choice between the 2, went in that direction. I'm not saying it's a good idea, but they've clearly thought about it and there's some merit to it. And it's been working for many years, of course, with issues, of course, with unintended side effects, but it is a solution I think worth considering. So yeah, this is good.
I thought it was actually really interesting. Nikolay: A lot of open questions. Of course I wish, I saw you send me this Postgres 19 improvements in terms of plan management and hidden plans mentioned there. I wish Aurora team open sourced this. It's not Aurora specifically. It's called APG plan, MGMT extension. APG plan management. Yeah. So it's great thing. And I wish it existed in the open source ecosystem.
I think eventually it should exist in open source ecosystem. Because when I would use it, When suddenly we have plan flip, we know what plan we would prefer, and let's just pin it right now before we apply permanent solution, just mitigate as fast as possible without any deployments. Michael: But let's say we're Clark, and we know we've got 15 to 20 really important queries, they're on the hot path for every single auth request, they're really frequent, they're thousands
of times a second, we could just, we could deliberately pin all of those or put hints into all of those Nikolay: to make Michael: them really unlike... Pardon me? Nikolay: So this is different. So what I'm saying is temporarily pin before we apply permanent solution. What you say, permanent pin, which means there might be some cost you will pay to maintain it. Michael: Maybe yes, but my point is at least we then can guarantee it won't go bad.
Like it maybe it will gradually degrade and we can look at fixing if it's going to degrade it. I think it's more likely to degrade gradually instead of this all of a sudden, which I think we can then fix Monday to Friday 9 till 5 instead of when there's a huge database outage. Nikolay: And again, just partition your tables. I know it costs some effort, but with AI it's easier. Michael: And gather statistics on the parent Partition. Nikolay: Yeah, which is another topic. It's fixed. Laurenz Albe from CYBERTEC fixed it.
I think it will be in 19. Michael: Nice. So automatic gathering of statistics. Nikolay: So it's a headache during upgrades especially. I think it's solved now. I mean now it means Michael: in the future. Nikolay: In the future. Yeah. Great talk. Nice 1. Yeah. Enjoyed. Thank you. Michael: Likewise. Thanks so much and catch you next time.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...