Michael: Hello and welcome to Postgres.FM, a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard, and as usual, I'm joined

by my co-host, Nikolay, founder of Postgres.AI.

Hey, Nikolay.

Nikolay: Hi, Michael.

Michael: Hello.

Today, we are excited to have a special guest, Tomas Vondra,

who is a Postgres major contributor and committer now working

at Microsoft on their Postgres team.

Welcome to the show, Tomas.

Tomas: Hello, everyone.

Michael: Right, so a few months ago, you gave an excellent talk

about where performance cliffs come from, and we have been excited

to talk to you about this.

So perhaps as a starting point could you let us know a little

bit like how you got interested in this as a topic or why you

chose to talk about it there.

Tomas: So I think I started thinking about performance glyphs

when I actually started working on Postgres or started using

Postgres.

Because performance lists are something people often run into

and have to investigate changes, sudden changes in behavior after

small changes in the query.

A single additional row in a query could kind of like flip the

behavior in a way that is completely like unexpected and unpredictable

and it's causing problems for like robustness and so on.

And I actually started with Postgres as a user or a developer

using Postgres.

And 1 of the things I was responsible for was investigating performance

problems.

So that's how I actually came to work on performance cliffs.

But after I started working on development of Postgres, you also

see the other side of performance cliffs, which is like why it

actually happens in practice, because the database needs to do

some decisions.

So it's also an engineering problem which is interesting for

me as a developer committer.

And I've been working on a planner some of the time and That's

where some of the performance was actually happen, right?

Because of decisions made either during planning or then during

the execution.

So I've been reading some papers and that's actually where I

learned that there even isn't like a name for this, which is

the performance cliff.

And there is a whole area of research for robustness, like making

robust decisions where the performance cliffs do not happen at

all or not as often.

So that's why I'm interested in this.

Michael: Nice, and yeah, I really like the term performance cliff.

It's so intuitive in that, from
the example you were talking

about kind of adding 1 additional
row.

So if we go back a few steps, if
we're adding 1 row incrementally

up until the cliff, we might get
a certain gradient, might even

be relatively horizontal and then
all of a sudden there's a massive

spike and that's like the cliff.

I'm probably thinking of it as
a spike but it maybe a cliff would

be a drop.

So yeah I really like that as a
phrase.

You mentioned kind of planner versus
execution time cliffs and

I guess we're talking about individual
execution more than kind

of system level cliffs, mostly
here.

From your experience as a user,
or maybe since you've, since

you've been looking at like trying
to prioritize which things

to work on, where do you see the
majority of cliffs coming from?

Like plan time, execution time,
some, some mix.

Tomas: So that's a good question.

I don't know because I'm inherently
biased, right?

The fact that a lot of performance
groups, for example, happen

during planning means that you
can't actually see a lot of cliffs

that happen later, right?

So I don't have a very good like
data to explain that or to say

that most of the cliffs happen
at some point.

But I would say that the sooner
the decision needs to be done

in the query planning and query
execution, The sooner you need

to do that, the less information
you actually have to do a good,

robust decision.

And the more likely it is that
it will be wrong, right?

Because for example, we do planning
based on statistics, and

those are inherently inaccurate,
right?

There will be a lot of details
that we remove from the statistics

to keep it small, and therefore,
those are the things that can

actually cause problems.

And then there is, of course, the
thing that even the cost model

itself is like extremely simplified,
right?

So I would say that the main problems
is that we are making a

lot of decisions early in the query
execution.

And because of that, we are kind
of like fixed in a certain execution

path, and that may not be the optimal
1, right?

So that's why I think the performance
glitches come from kind

of.

Nikolay: I wanted to ask, there
should be a challenge not only

about like planner versus executor,
but also their, both of them

behavior, like in a single session
when there is no like a multi-user

situation versus some various contention
situations when we have

many, many sessions working in
parallel and all of them have

some issue.

For example, unfamous lightweight
local log manager happens when

during planning planner needs to
log all the indexes for the

table and we exceed 16.

Fast-path becomes not available
for some relations.

And we can not see it when we just
checking the single query,

but we, like it becomes a performance
cliff immediately when

we have thousands of transactions
per second doing the same thing

and planning becomes problem, right?

I'm curious, how do you think this
should be understood, like

with what tools and earlier, not
when you already fell from that

cliff, right?

But before, this is the trickiest,
like how to diagnose cliffs

which are going to happen soon
with our system.

Tomas: So, I understand the example
that you've been describing,

but I think that's a slightly different
performance cliff and

not what I usually mean by that
term, because you are kind of

like talking about like resource
exhaustion, right?

You have a resource, which is like
a lock queue or like the buffer

for the fast-path locks, which was
increased or like made larger

in Postgres 18.

Nikolay: So we should have

Tomas: the problem.

Nikolay: And it's configurable
now, right?

I think it's maybe configurable.

Yes.

Tomas: It's ties to expected number
of transactions per connection,

so it is configurable in this way,
yes.

So it's not fixed size like before.

But I think what I mean by performance
cliffs and what I describe

in my talk is mostly performance
cliffs that are done because

of like a binary decision somewhere
in the processing, right?

And the locking bottleneck is not
that, right?

I mean, like, yes, you do have
the case where you can either

fit the fast path lock or not,
right?

And if not, you have to do something
much more expensive.

That's a problem, but it's not
the thing that I had in mind thinking

about performance.

Nikolay: Yeah, now I remember when
I was checking your talk,

this is exactly what I thought,
like we have different views

on this term.

And I grabbed this term from various
blog posts where people

talked about multixact, SLRU
buffer problem, like it was also

a cliff.

Sub-transactions, also calling
this performance cliff, like saying

sub-transactions are cursed, don't
use them at all, because there

is a cliff that is hidden there
and so on.

So this definition is very different
from yours.

I understand, yeah, yeah, I agree
with you.

Tomas: Yeah, I don't feel like
I'm the authority to define what

exactly that means.

I'm trying to more explain what
I've been describing, like the

cases that I've been discussing
in my talk.

Because also the reasons why that
happens are slightly different,

right?

For the performance, as I understand them, or as I discuss them,

it's more like an engineering question because you have multiple

algorithms that you can choose from.

Each of those algorithms is kind of optimal, best suited for

certain type of conditions or selectivity ranges or something

like that.

And therefore, if you make the decision based on the assumption

that you know the correct answer, right?

And I think the bottlenecks that you've been describing are more

about like say changes in the hardware available.

Because years ago when we initially sized the number of

fast-path locks, I think 16 was probably fine.

It was reasonable.

You probably didn't have that many tables or connections, and

it wasn't such a problem.

But nowadays we have many more CPUs, many more cores available

usually.

And we also have things like partitioning, which can completely

explode the number of things that you need to think.

So I think it's more like a consequence of changes in Postgres

that leads to hitting this bottleneck.

I'm not saying it's not a valid problem.

It definitely is.

We need to improve that.

But it's a slightly different thing.

Nikolay: Right, yeah, I agree, yeah.

Michael: The topic's already huge if we only limit it to single

session, single query performance cliffs.

So if we think about that, just those, you talked about both

reducing kind of the frequency of these, so making them less

likely to happen, but also reducing the severity of them.

So it kind of accepting they will happen, make them less of a

cliff or less of a drop when they do.

Can you talk us through some things from a user perspective that

we can do to reduce the severity and or to reduce the likelihood

of running into them?

Tomas: That's a good question.

I think it probably depends on each individual case of the performance

cliff.

And in some cases you probably can't do anything.

But it's just good to know that this is what's happening.

In the talk, which you can see on YouTube, and I'm actually invited

to do the talk at the San Francisco meetup in a week.

But the first example that I gave in that talk is about the number

of elements in the in condition, right?

Where column in and then array of elements.

And there is like a flip because we exactly switch from 1 algorithm

to the other, like from linear search to hash.

But that's hard-coded, right?

There's nothing the User can do
about that.

The one thing you could do is to
pick the right size of the element,

of the list, right?

But that's not a very practical
thing.

But I think in many cases just
knowing what's causing the problem

can be a very useful thing because
you can say like okay, this

is what's happening, I can't do
anything about that, I have quantified

the impact, and we have just to
live with that for now.

I can imagine patches doing something,
like improving that by

adjusting the point where exactly
that flips, by doing some better

estimation.

But at this point, people can't
do anything about that.

For some of the cases in planning,
you can do the usual thing,

like trying to convince the planner
to do the right decision,

right?

And there are different ways to
do that.

I don't know what's the best solution
to that.

Michael: Things I really liked
were, I think proactively people

can adjust some of the cost parameters
like we still have random

page cost as for as the default
even on most cloud providers,

so I feel like things like that
when we've mostly got SSDs these

days just feel like they're going
to be more likely to push plan

flips at the wrong times.

Some of these, if there are other
cost signals that are like

massively out as well, I'm not
aware of any, but that feels like

a foot gun or something that if
people change proactively or

reduce proactively, they'd be less
likely to encounter this kind

of thing.

I'm also aware you did a lot of
the work on extended statistics

and that feels like another tool
that, used well, could start

to teach the planner about certain
relationships and make cardinality

errors far less likely.

And again, maybe then less likely
to have plan flips at the worst,

not the worst times.

When we do have the plan flip,
it's more likely to be closer

to the optimal point.

So we're less likely to have a
severe cliff at least.

What do you think?

Tomas: I think you are absolutely
right that like tuning some

of the parameters for like random
page costs, for example, and

something like that is exactly
one of the things you can do to

convince the planner to make the
correct decisions more often,

right?

Because you are essentially moving
the flip point between the

costs closer to the actual flip
point, right, between the execution

costs, like measured in time, for
example.

So that's definitely true.

I think most of the time we actually
do the correct decision

like for regular queries, because
the selectivity where we would

have a problem is kind of like
in the middle, right, somewhere.

And usually queries do have like
very low or very high selectivity,

something.

So in between is not very common,
it can happen.

But I don't really have a perfect
solution to that.

Extended statistics definitely
can help, but it's still the very

early decision.

It's going to help with improving
that in some cases.

And I know you had a whole podcast
about that.

The problem with the extended statistics
is that it still is

like an extremely simplified representation,
approximation of

the data.

That's the whole point of having
statistics, to have a very compact

thing, very compact data set, which
you can use to do the planning

quickly.

Which means that it definitely
is missing some of the details.

And that can be fatal, right?

So I think what we need to focus
on in the future is kind of

like making the execution part
a bit more robust, right?

Like moving some of the decisions
to that instead of expecting

the decisions in planning to be
perfectly correct all the time,

to kind of like allow the execution
to actually recover from

mistakes.

Nikolay: Change the mind on the
fly, right?

Change the execution path.

Tomas: Kind of, right?

Or like, yes, there are different
approaches to that.

Some are kind of like difficult
to do correctly, like replanning,

for example, right?

Or you could have like a typical
problem for a planner, it's

like a sequence of nested loops,
right?

When you have underestimated the
join size or like a condition

selectivity, then you pick a nested
loop and then it explodes

and takes forever.

So there are approaches which kind
of blend 2 different paths

through the join, and either do
like a hash join or like a nested

loop, right?

But there are like practical issues,
what you can do limiting

the behavior of the optimizer,
right?

Because if you want to do this,
then for example, you cannot

emit any tuples from that join
until you know that that's the

correct approach, right?

Because then you wouldn't be actually
able to flip to the other,

and so on.

Nikolay: And I see Another challenge,
it's like user experience.

We all are very, we're scared about
the probability of plan flips

sometimes, right?

And it's dangerous, especially
when you do upgrade, major upgrade,

Plan flips are not uncommon.

And I know, for example, AWS Aurora,
RDS Aurora, has extension

which is not open to freeze plans
so no flips can be possible.

What you described, like if I see
some plan in explain, but then

during execution plan can be changed,
it makes like my head thinking,

like how can I like control it?

I'm not controlling, I just understand
what's happening, right,

Because explain will lie in this
case, right?

Or maybe you see something that
could help to see 2 plans and

explain, or 3 plans and we know
that.

Tomas: But we already can show
this kind of stuff in the plan,

right?

Where when you have a sub-plan,
we can either have a regular

plan or a hashed sub-plan, I think.

So we can show alternative plans.

I don't think that would be a problem.

Obviously, that wouldn't tell you
what exactly happened during

execution.

It would need some additional improvements.

But yes, it adds ambiguity to the
explained plan, right?

Because it would tell you, these
are the possibilities, right?

You don't know what's going to
happen for, and it could actually

differ between executions of the
same query even, right?

But that's the price for higher
robustness, right?

Nikolay: Right.

I'm just thinking observability
tools, many people want them

to be improved, like to track...

pg_stat_statements is not enough to
have plans, 1 normalized query

can have multiple plans.

In the past, there were attempts
to have pg_stat_plans or something.

There are also extensions which
allow you to see the plan in

for ongoing query, right?

I think if this way is like this
direction is going to be like

norm to alterate the plan on the
fly that observability tooling

needs improvement improvements
for sure because we need to understand

what's happening and auto_explain
as well right probably it should

report initial plan and then additional
plan or something like

this, alternations.

So I just feel the bigger need
to improve observability part

of Postgres as well.

Tomas: Yes.

I have nothing against observability.

Nikolay: Not a question, just feeling.

Tomas: And I think I haven't tried
like pg_stat_plans for a while,

but I think it still exists, it's
still available, right?

I think it's very useful.

I think there's also a patch to
actually add ability to watch

a plan as it's being executed,
right?

Which can actually help, that would
be a tremendous benefit when

investigating queries that never
complete, where you can't actually

get like explain analyze and so
on, or just watch the execution

of a query in another backend.

So there is a patch for that.

It's definitely not going to be in Postgres 18, but I think Rafael

actually is working on that.

I hope I didn't get the name wrong.

Yeah, but again, I don't think that's necessarily about the performance

only, right?

That's about monitoring in general.

Michael: This concept of robustness is really interesting.

I don't think I'd come across it as defined a term until reading

some of the papers that you link to at the end of your talk and

I found it fascinating.

I think it aligns well with my experience of what people really

care about in performance.

I think that, Correct me if I'm wrong, but I got the impression

it's really about trying to avoid really bad worst case situations

at the cost of potentially slightly worse average execution time,

for example, if we want to optimize based on time.

So this idea that on average, we might perform slightly worse,

but our worst case will be nowhere near as bad as the, so the

P99 would be way better or way less severe, but the P50 might

be a bit higher.

Is that about right?

Tomas: Yeah, I think that's exactly right.

You're exchanging the, you know, worse average performance for

having to do less investigations of performance issues.

And I think that's perfectly reasonable trade-off.

I mean, I do like working on micro-optimizations and micro-benchmarks

and everything.

That's great.

But in the end, someone needs to be using the database, right?

That's the point.

And if they do have, I don't know, until people had like 1 database

and they cared about that only, that was fine.

As soon as you have like a larger fleet of database servers that

are actually used by people or by applications or something,

you definitely will value robustness, like not having issues,

even if the peak performance is maybe 25% lower or something

like that.

Because it's much easier to spend a bit more on the hardware

than having to run a large team investigating and fixing issues

and firefighting all the time.

That just doesn't work at scale.

I think it's also what some of the Cloud hosting providers realize.

It's a bit against my nature because I definitely do want to

do the best performance possible.

I've been benchmarking a lot and doing performance-related patches

for a long time.

At some point, I think it's kind of pointless, right?

Because people will not be actually able to use that in production

anyway.

Michael: Yeah.

I mean, there are definitely...

It feels like there are cases and
there are people trying to

get the best of both worlds right
trying to get a faster than

we currently have solution that
is also more robust like we had

we had Peter Geoghegan on to talk about
the work he's been doing

on like in the in the direction
of skip scan, but that that work

seems to make some decisions post
planning execution as to whether

it's going to skip or continue
trying to skip or just run through

the index sequentially depending
on certain statistics it gathers

along the way.

That strikes me as work in this
direction that in the real world

doesn't seem to give up too much
on the optimal path either.

Tomas: So I think you are talking
about the multi-dimensional

access method that Peter was talking
about.

And I think it's actually a win-win
situation in this case, right,

because he actually can do better
decisions based on the a priori

information that he has at the
planning stage, right?

So he actually is not sacrificing
anything.

He actually, he's improving the
behavior in many cases.

So I think it's amazing what he's
doing with indexes.

And I've been grateful to actually
collaborate with him on the

index prefetching patch.

So, and his comments were very
useful.

And I think it's actually Peter
Geoghegan who pointed out like a

quote from Goetz Graefe, who is
one of the authors of the patches,

who said that choice is confusion.

By adding more different ways to
execute a query to the optimizer,

then you are just giving it more
chance to make a mistake, right?

So I think the robustness, like
the main parts of the features

that improve robustness needs to
be at the execution path, right?

Kind of like adjusting based on
the actual data, right?

Michael: Yeah, so we've got probably
more than this, but 3 main

times we're having to make, or
the planners having to make decisions

that are really impactful for plan
flips.

We've got scan type, so we've got
kind of sequential scan, index

scan, bitmap scan, index only scan,
perhaps some others if you've

got extensions installed.

And then we've got join algorithm.

And then we've also got join order.

I've read a bit about from based
on your talk, I've read a bit

about the first 2, but nothing
about the last 1 in terms of execution

time optimizations.

Is there anything around that that
you'd be looking into?

Tomas: So the first 2 parts that
you mentioned, that's like,

there are actually, you know, papers
proposing like more robust,

on average slower, but more robust
execution paths.

I think that would be interesting
to have some of those as extensions

in Postgres.

Because we do have the custom scan
interface and we can actually

inject a different path.

So that would be fine.

For the join order search, I'm
not sure.

I'm aware of some issues in that.

But I think for the typical queries,
the algorithm actually works

quite fine.

But there are some examples of
queries where that actually blows

up.

And I don't know if that's what
you've been pointing at, but

like StarJoin, for example, is
a great example, because we end

up exploring n factorial of different
plans.

And we can actually improve that.

I actually posted like a proof
of concept patch for improving

this case.

But there's a lot of work that
needs to be done on that.

But that could actually help tremendously
because I think in

another talk, I compared like performance
for different kinds

of workloads since like Postgres
8, I think.

And on like a star join with, I
mean, OLTP star join, which means

like you select 1 row from, or
a small number of rows from the

fact table, and then have like
20 dimensions, you know, joining

to.

So for that, we didn't actually
improve throughput at all since

Postgres 8.

Right?

Michael: Wow.

Tomas: Which is like crazy, like
compared to the order or 2 orders

of magnitude better throughput
for the other OLTP workloads.

I think that's a clear bottleneck
there.

And it's exactly because the join
order search is like not great.

We do have something we call genetic
optimization, but I think

almost no 1 actually uses that
because the default configuration

parameters actually force us to
do a different thing.

So we don't actually use the genetic
optimizer at all.

Nikolay: Well, when we have 20
tables joined, it's used, right?

Because there's threshold.

Tomas: No, no, no, no, no, no,
no.

Because first we split that at
8 into smaller join searches,

right?

And we do the regular algorithm
for that.

Nikolay: I see.

Tomas: So you would actually need
to change the defaults, and

only then would the genetic algorithm
be used.

Nikolay: I didn't know this.

Okay.

Tomas: Yeah.

Good.

I didn't know that either because
that's when I started doing

the benchmarks to compare the different
versions.

I was wondering, why is the genetic
optimizer not actually visible

anywhere right and it's because
of this so people don't use that.

Michael: When you enabled it, did it improve on later versions

versus version 8?

Tomas: I think it did.

Okay.

But I don't remember the details.

I haven't like included that in the charts at all, for example.

But that's a good question.

I don't know.

Michael: Thinking about like next steps for Postgres.

What would you be most excited to see people working on or what

are you looking to work on next that you're excited about?

Tomas: So I definitely would like to see someone working on the

robustness patches for either the SmoothScan, I do have a proof

of concept patch for that, or the G-Join, I think they call it.

That's a robust algorithm for joins.

I think that would be extremely useful and I'm 99% sure it could

be done as an extension.

So that makes it much easier to hack on without breaking everything

else.

And it also makes it immediately available for users, which I

think is, that's very useful.

And then I think there are actually some like smaller patches

where people could improve like individual performance clips,

right?

If I go back to the example that I used in the talk, I can imagine

people experimenting with choosing the flip, the number at which

we flip from a linear search to a hash search, right?

So I can imagine people doing some runtime measurements and like

doing that.

I think another like problem for robustness is adjusting time

compilation for us, right?

Which adds like a completely different level of robustness issues,

like unpredictable behavior.

Nikolay: We just switched

Tomas: it off.

Nikolay: We just switched it off.

Michael: Yeah, yeah.

But that's great.

Tomas: Yes, I think that's the default recommendation, but it's

also quite sad, right?

Because the just-in-time compilation can be really helpful for

some queries.

Michael: But it's a robustness decision, right?

Yes.

Like Nikolay is often, and I've seen people as well, choosing

potentially worse on average performance in certain queries for

not getting those outliers.

So it's exactly about robustness.

Tomas: Yes, I think that's a great example showcasing the decisions

people make, right?

And they almost always choose better
robustness if that's a production

workload, right?

I think that's fine, right?

And I think we could have like
a...

I don't know if you had a session
about just-in-time compilation,

but I think there's a whole set
of things people could do to

improve that.

It could be better planning or
it could be using a different

library for JIT.

Because a lot of that, a lot of
the problems that we have is

actually due to LLVM not being
a well-suited library for the

kind of JIT that we do for the
use case, right?

Michael: Yeah, interesting.

We haven't done an episode on it.

I think you're right, it would
be a good 1 to do.

Who would you recommend we talk
to about that?

Is it Andres or who is it?

Tomas: I think Andres is like the
1 person who knows about that

most because I think he's the 1
implementing that.

So you can also complain to him
and he's the right person.

But I think there are actually
other people who submitted some

patches for like improvements for
like

Nikolay: cool

Tomas: I think that was like 2
years ago there was a blog post

about someone actually implementing
like custom JIT and that

might be another good guest I think
I

Michael: completely forgot about
that that's a good idea and

the the other thing is I think
it's based solely around like

a limit of, a cost limit.

That feels very crude to me.

Tomas: Yes.

As I say, like the planning is
very, very simplistic.

It makes like essentially the decision
for the whole query at

once, right?

Michael: Yeah.

Tomas: I mean like the fact that
it's based on like cost limited,

that's roughly fine, but then crossing
the whole limit, crossing

the cost limit for the whole query,
can also enable just-in-time

compilation in parts of the query
where that's not really useful,

right?

So that's definitely 1 of the things
that we could improve.

I mean, like it's not a perfect
solution for all the cases, but

it would be, I think, better approach.

But I'm sure like there are reasons
why it was done like this

right so I don't want to like make
like a definitive statements

about like solutions.

Michael: Yeah would be an interesting
discussion and was there

anything you want anything we should
have asked about that we

didn't?

Tomas: No, I think we discussed
like a lot of different like

parts of the that I mentioned in the talk so I think that's fine.

Michael: It's a really excellent talk and we didn't we definitely

didn't discuss it all and it comes across really well with examples.

So I will include the link to the talk and the slides as well

for those links.

And I'll include a link to the San Fran talk.

So if anybody's got questions themselves, I guess you're attending

live so they can ask them at the meetup as well.

Tomas: Yeah, or they can just like send me an email or something.

I also do something I call office hours, where people can just

let me know what they would like to talk about.

And it could be like a badge they are considering or whatever.

And we can definitely like schedule call or something.

That's also what I do for the community.

Yes.

Michael: Yeah, that's awesome.

I did actually want to say thank you for everything you're doing,

because you're also doing some mentoring and some patch ideas

for new potential committers.

And I think that that kind of work is very, very valued and very,

very valuable.

So thank you.

Awesome.

Well, thanks so much for joining us, Tomas.

And yeah, I hope you have a great week.

Tomas: Bye.