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.
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.
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.