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 Nikolay, founder of Postgres.AI.
Hello, Nikolay.
Nikolay: Hi, Michael.
Michael: Today is no normal episode though, because we are delighted
to be joined by Melanie Plageman, who is a Database Internals
Engineer working at Microsoft and major contributor and committer
to PostgreSQL.
It's a real honor to have you on the show, Melanie.
Welcome.
Melanie: Thank you.
I'm excited to be here.
Michael: We're excited to have you.
There are so many things we could have chosen as a topic today,
But off the back of a couple of talks you've given at Postgres
conferences, we've opted to go for the topic of getting started
with benchmarking.
Would you mind giving us a little background about why this is
a topic you're particularly interested in?
Melanie: Yeah, sure.
So since I started working at Microsoft a few years ago, a lot
of the work that our team does is performance related.
So I would say as a Postgres engineer, even for features that
are not performance related, you have to make sure that you understand
their performance impact.
But we tend to work on things like AIO and other features where
you wouldn't do them if they didn't have a positive performance
impact.
So assessing that is a huge part of my job.
And it's an area where every little detail matters.
And the things that don't matter are kind of counterintuitive.
And it's just, it's kind of an art and it's so hard to learn.
And it's like a dark art, you know, and it's, I think I've been
really blessed in that I've gotten to talk to and learn from
people that know how to do it, like Andres Freund and other people
at Microsoft and in the community, but not everyone gets to learn
from people that have 15 years or 20 years of experience.
So I like talking about it and trying to see if I can help people
get started and demystify what I can about it.
Nikolay: Yeah, that's a good point.
So you think it can be improved, right?
And people can understand benchmarks better, right?
What are the tools, do you think, for that to find solutions
better and understand bottlenecks faster and so on?
Yes.
Misleading results we have all the time, like we think we found
something, but it's wrong, and then you like 1 day later, you
realize that it's completely opposite sometimes, right?
So, so you think it can be improved, right?
Melanie: Right.
So I think one of the things that users have going for them is
that they have a baseline understanding of how you should configure
your database.
And some of the mistakes that you maybe, that I made as a beginning
with performance work, you might not make.
So I remember like the first set of benchmarks that I did, I
didn't configure shared buffers at all.
This was years ago, so I give myself a little bit of an out.
But basically, I was looking at it and I was saying, this improvement,
you know, that I, this is supposed to be an improvement and it
looks like it's actually hurting performance.
And I was showing Andres and he was like, well, tell me how you
configured it and what, you know, what kind of, I didn't change
any configuration.
And he was like, okay, we'll just throw all of this away.
And so some of the basic stuff around benchmarking, I think that's
something that developers who maybe don't have as much user experience
have to learn, but users have that.
I think one of the things that I see happen more than you would
think is that there's some unexplained performance impact or,
like a lot of times you're, you develop a change, you want to
see how does it improve performance, and when it doesn't, or
it has an effect that you can't explain, you have to start investigating.
One of the things that people almost never do, at least performance
engineers, they don't look at the logs, and it's like maybe because
it's a development branch, right?
Like maybe you actually had a bug and it's crashing or, you know,
in the logs, there's something that's actually, and that's what's
slowing it down.
And it's such a basic thing that you don't think about.
You're like looking at IO stat and looking at these more advanced
tools when we really just need to look at the log.
Nikolay: Or it was fast because it didn't do any work, right?
Melanie: Yeah.
Nikolay: And looking at logs, you can notice it.
Yeah.
Good point.
Sometimes, even some companies we had, I won't name it, but sometimes
some benchmarks are published and they claim something, and if
you have experience, 10 plus years, you quickly have some ideas
what's wrong with those benchmarks.
For example, full-text search in Postgres is bad.
It shows capital O of N.
You think, how come?
It's not possible.
Like, you quickly realize it's not possible.
And you look at this and realize, oh, yeah, they just didn't
build GIN index at all.
Right?
But if you don't have experience, it's easy to make errors, like
right, to have errors, mistakes.
And my idea, like it would be good if something would help people
to brainstorm what's wrong or what else to look at as early as
possible.
So we need some brainstorming mechanism, right?
What do you think?
Melanie: Yeah, almost like a checklist or that kind of thing.
I think that's one of the reasons why when I started out with development,
I thought, you know, with Postgres, there's not enough basic
resources like for performance work that give you a checklist.
But then I think I realized over time, like I sort of moved towards
the, you almost need that one-on-one interaction with someone
who has more experience because it's just so individual and so
it's just so hard to give general advice to people.
So I think that there are basic questions you can ask yourself.
There's a big difference between I think a lot of users when
they do benchmarking, I think a common case is they're going
to install, they're thinking about upgrading.
And so they're like, let me just see if it's slower or faster,
whatever, right?
In that case, using pgbench for that is not necessarily going
to help you, right?
Because first of all, benchmarking what you actually want to
benchmark with pgbench or any benchmarking tool is, is hard.
And second of all, what if that's not like your workload at all?
You know, so let's say that in Postgres there were changes to
the way that vacuum works and you run a benchmark for less than
a few minutes.
Well, like, did you even end up having anything be vacuumed?
You almost have to think about what do I actually care about?
So if you're a user who's worried about upgrading, like, I guess
you guys would know better than me, but you'll probably have
options like running your maybe non in not production, you can
run your real workload and on a newer version and see how it
is.
And like, that's the ideal scenario.
But if that's not an option for you, then I think what you have
to do is really think about what are the highest risk areas for
me?
Like, do I have one critical report I have to run?
Or, you know, I need to make sure that I'm able to reach this
TPS at this time of day, or whatever it is.
And then you have to have a way to reproduce that.
Like you almost have to have a minimal repro for the performance
question that you're asking and be able to test it.
And I would say even then, like, I don't know if you're gonna
get a perfect answer outside of actual production environment.
Like the people, the use case that users have for benchmarking,
I haven't heard of one where I think it's going to work out
for them.
But I don't know, maybe you can tell me what people want to use
Nikolay: it for.
Let me shamelessly ask you your opinion about the approach I
ended up having for upgrades with our customers, big and small,
doesn't matter.
So I was a big fan of replaying workload like 5 years ago, but
we don't have tooling for it.
And mirroring, like PgCat, I think, has mirroring, but it's hard.
It should be in between clients and server and it's very critical
infrastructure.
Usually pgBouncer is there and you cannot just quickly replace
it with PgCat and so on and overhead and so on.
And mirroring also is tricky thing actually.
So what I ended up doing, I split it into 2 things.
First thing is load testing generally with pgbench.
And usually, we have already done by hackers and so on, like
performance farms or something.
And we can check it additionally with some synthetic workload,
pgbench, not pgbench, sysbench, doesn't matter.
So this is one thing.
And usually we rely on community results or we can extend them
as well.
But as for our workload for our project, Instead of replaying
the whole, we think, okay, we forget about log manager, buffer
pool, and so on, all those components, and we care only about
plan flips.
We want just to check the planner behavior, and we just grab
query examples, like 100 of them, which are most critical in
terms of total time or calls or something from pg_stat_statements.
Sometimes we collect multiple examples for one normalized query
in pg_stat_statements.
And then this is our testing set.
And then we don't care about machine we use, actually.
It can be smaller machine.
We just have exact clone of production database to have the same
data and pg_statistic.
And then we also adjust settings, all planner settings and work_mem,
which is not planner setting, but also affects planner behavior.
And then we just reproduce plans, upgrade, reproduce plans again,
and compare and see if some plans changed.
And also we use BUFFERS, of course, and we see like costs and
BUFFERS we usually look at.
We don't care about timing because it can be smaller machine,
cache type can be different.
And if some plan flip occurs, we quickly find it.
And it can happen like in small machine, like in shared environment,
basically.
It's not, pgbench is not needed for plan flip check.
Melanie: Right.
Nikolay: And that's it.
Melanie: Yeah, I think if I understand correctly, at some point
you use pgbench, and then you realize that it wasn't actually
going to help you with this particular case.
Nikolay: It's super hard to reproduce actual workload.
Melanie: Yeah, so I support that.
I think really, honestly, what you're doing, which is looking
for plan flips and starting with pg_stat_statements is probably
the best thing that you could do to try to make sure that you're
going to, when you upgrade that it's going to work out for you.
And because I think that's one of the only things that you can
realistically reproduce because like the planner doesn't take
into account other things that are happening.
Each plan is planned in isolation with the statistics and the
data that you're talking about.
It's not looking at overall system load.
I mean, maybe we should do things like that, but planner doesn't
do that right now.
So it's actually possible to do that.
So I think that's a really good sort of precautionary thing that
you can do to see if the next version of Postgres is going to
cause you problems.
So yes, I sign off on that.
But that makes sense as a strategy.
Nikolay: Before that, we were using pgbench and we took most
frequent and most time-consuming queries from pg_stat_statements
and we called it crafted workload.
We've tried to find some parameters or examples of queries and
put them to pgbench using hyphen F and then add sign to balance
somehow like to have some balanced workload and then ignore some
errors if they happen like foreign key violation or something.
I cannot say it worked well.
It helped, but then I just realized, why do we care about buffer
pool and so on?
Let's just split this.
Melanie: So you were trying to, you were doing that before upgrading
to see if you would get the same.
Nikolay: I stopped doing this because It's hard, actually, time
consuming.
And if we care about plan flips, that's it.
We have a recipe.
If we care about improvements in some components of Postgres,
we can research them using some simple workloads like pgbench.
That's it.
Melanie: Right.
Yeah.
You really can't use pgbench to replicate real workloads because,
so for example, if I want to analyze a patch that I'm doing and
I want to replicate some scenario, I might run multiple instances
of pgbench at the same time.
So I run 1 that's doing a SELECT query, and another 1 that's
doing a transactional workload.
And you can combine different pgbenches, and you can sequence
them, and that kind of thing.
But ultimately, each pgbench is going to do the same thing, no
matter what kind of custom script you provide.
So you can there's different variables and you can do things
like have variables in the script and then interpolate random
numbers and things like that.
But in a real system, you'll have some kind of work happening
and then another kind of work happening that's different work
and like interspersed or at unpredictable intervals and with
pgbench in the, you know, sort of during a pgbench run, all
of the workers are going to be doing the same thing, you know,
whatever's in the pgbench script.
So you can't get, it's very, very hard to replicate realistic
scenarios with it.
And there's lots of creative things you can do.
Like you can have some if statement logic where you're like,
okay, if this worker is this worker number, then do this other
thing.
And like, but at that point, I think it's not useful if you're
trying to understand what the performance impact is going to
be for your real world workload.
Like I can see users maybe using it to figure out, to understand
a particular quirk or something like that.
Or maybe if you're thinking about changing your hardware, using
a different SKU from the same vendor or something like that,
you can try to understand how it might affect general Postgres
workloads.
And then it could be helpful.
But pgbench is like mainly a developer tool.
And I mean, that's what it's marketed at.
It's not really marketed as something, not that it's marketed,
but it's not, I don't think it's represented to anyone as a tool for
users to understand Postgres performance of their workloads.
But I mean, all the other benchmarks that are out there, I think
pgbench kind of gets lumped in with them.
And it's not really useful for any of the things other than development.
In my opinion, it's very hard to make it useful for like comparing
whatever Postgres to MySQL or that kind of, I mean, cause you
can't use
Nikolay: them.
Different hardware options, for example, it can be useful.
Melanie: Yeah, you can use it for that for sure.
Yeah, but it's definitely serves a different purpose than the
TPC benchmarks, for example, which allow you to compare across,
you know, databases.
And then of course we also have a community of people that are
doing performance testing from version to version of Postgres
that's different than just testing 1 patch and saying, what is
this patch doing?
They're like sort of looking for broad themes and regressions
across versions.
And you can use pgbench for that, but ultimately, I would say
it's easier or more meaningful to do something like run the TPC
benchmarks when you're looking to see if Postgres has a regression
from 1 version to another for the purpose of community work.
Michael: You mentioned we've got a community of people doing
that.
I've seen a couple, but I don't know of many.
Do you have any names or things I could look into?
Melanie: So I think the most, the benchmarker that I follow closely
and that many people follow is Mark Callaghan.
Michael: Yeah, great.
Melanie: Yeah, so he's great.
And he actually came to PGConf.dev this year and I got to meet
him.
He's kind of like one of my, I was starstruck when I met him people,
you know, and he's like super nice, you know, in that.
Nikolay: I like his blog's name, Small Datum.
Melanie: Yeah, yeah.
And he's great because he has, I mean, he's an engineer who has
a ton of experience in doing performance work for a long time,
you know, in MySQL mainly, the MySQL community.
And then with benchmarking, describing your methodology is so
important.
So he does that in great detail.
And because he's active and he's doing these benchmarks like
all the time.
He's providing a lot of really useful information for the community
around, you know, how he does version to version of Postgres,
different types of benchmarks, different on different sized machines
and that kind of thing.
So he's great.
And if you want to learn more about benchmarking, I would say
his blog posts are fairly advanced.
So like, if you're getting started, it might not be the best
resource.
I don't know.
But I still am kind of like, okay, let me read through this.
And you know, he's getting down to looking at how does your CPU
frequency governor affect your benchmark results, right?
So it's definitely at the point of, it's definitely past configuring
shared buffers for sure.
So he's great.
And then I would say within the Postgres community, I mean, there's
developers like Tomas Vondra and of course Andres Freund who do benchmarks
when they're developing and then usually they'll look at the
holistic, you know, release.
But most of those results are just getting posted on hackers
and they're not sort of like, because it takes so much time to
do what Mark does and sort of describe your methodology, like
double check every result, investigate every discrepancy, and
then publish it and sort of be accountable for it because people
will come after you, they'll be like, no, that is not a regression
or whatever.
You have to be ready to, to like defend your work.
And it's, it's a full, it's almost like a full-time job in itself.
So a lot of the benchmarks that get done within the community
are people
doing them and then just saying, hey, I found this.
Can anyone else look into it?
So there's also Alexander Lakhin runs GPCDS and I think GPCH
also every release and does a lot of investigation.
And he's great because he does so much investigation of bugs
and like reporting of bugs and reproducing very hard to reproduce
bugs.
So if he finds some sort of regression, performance regression,
he'll also bisect it down to the commit that actually caused
it.
And as a developer, it's nice to have someone doing that
level of analysis.
And so again, like that's not, he's not necessarily publishing
a methodology post and all of that.
Then there's some people that maintain the kits for,
because if you want to implement the TPC benchmarks, it's not like
you just run it.
Like you have, it's more involved than that.
There are people that maintain different kits to like the
bash scripts and stuff.
And I think Mark Wong just did a new one for TPC-DS.
So he has a lot of database internals experience.
And then he was involved with the TPC council for a while.
And so he kind of is an expert on just the TPC benchmarks.
And so he tends to put together these kits to try to help people
because it's not that straightforward.
And then I know there's some ongoing work with the Postgres performance
farm initiative, but it's really hard, right, to agree upon what
is a useful benchmark in general to run.
So you can find some regression or whatever.
And it's like, in order to actually prove that that's correct
or valid is a lot of work on the part of everyone else.
So I think in some ways, publishing benchmarks, you can't just
say I set up this farm and I ran all these benchmarks and now
I expect people to go look at them.
As a benchmarker, you're kind of accountable for the analysis
and trying to find out if your results are valid.
Nikolay: Practically useful.
Practically useful.
I found 1 day, I found, we found, my team found bottleneck in
WAL sender for logical replication.
It was so easy to reproduce.
You just create some transactions with delete and roll back them.
And like at some point very quickly you reach a hundred percent
of CPU for WAL sender.
And it led to bad conclusions that in production we won't be
able to use logical for many things, but it turned out that this
kind of workload doesn't happen in the wild at all, because it's
like kind of very specific kind of workload.
So I guess this bottleneck maybe it's not worth fixing right
now, right?
I mean, it's like, so it's interesting.
Melanie: Yeah, you can definitely report things like that on
the hackers mailing list or on performance mailing lists and
have a discussion about, you know, if it's kind of the right
thing to focus on and also, you know, if it's reproducible.
Nikolay: I did some discussions with some hackers about this
and didn't meet understanding that it's worth fixing.
And then I realized in production, we don't see such bottleneck
at all.
And I just like postpone this research for future maybe.
What do you think about observability tools to be used in feature
benchmarks?
What's too like extensions or additional tools and so on what
to look at?
To avoid observer effect as well, right?
Melanie: Yeah, I mean, I tend to not worry too much about the
observer effect, depending on what tools I'm using, because I,
you have to do some, I mean, as long as you don't have like log
min duration statement set to 0 or something like that.
But if you're comparing to another version of Postgres, and you're
using the same observability tools, it's sort of the price you
have to pay, some overhead.
But every person that does performance work is different.
When I started, I made the mistake of trying to like generalize
and make some of my tools useful to other people and no one wants
that.
So I learned that the hard way.
But personally, I find that it's very hard to look at aggregated
numbers at the end of a benchmark run and make sense of it.
And like, that might be what you present to other people, because
they don't want to look at every detailed, you know, graph that
you produced or chart that you produced.
But while I'm doing the work, I can't do it at all without visual
representations and charts and things like that.
So I use for that, I mean, for the actual charting, I use like
Matplotlib and pandas and that kind of thing.
But I have all sorts of scripts that do things because I use
a lot of data input sources.
So, I mean, one of the things that depends on the patch, but I,
so I'll query pg_stat_io, or, you know, depending on what the
patch is, pg_stat_activity or pg_stat_all_tables, and then gather
certain information every 2 seconds or every 1 second.
And then I have scripts that take this output and then I'm able
to just basically make it CSVs and then load it into pandas data
frames.
So for example, I've been working on vacuum recently, and then
one of the things that you consider when you're doing vacuum performance
work is how often vacuum work is being done.
So, you might want to look at the wait events and look at, you
know, how many if autovacuum workers are waiting on vacuum delay,
wait events a lot and like not actually doing the vacuuming work
and if you have the right autovacuum settings to actually be
able to observe the thing you're trying to observe.
So, you know, just gathering that information and then plotting
it correctly and that kind of thing.
Another thing, because Postgres uses buffered I/O right now,
I tend to use external tools for I/O observability, like iostat,
because you can't actually tell with, you know, reads and writes
in the Postgres statistics, that could be a read or write to the
kernel buffer cache.
You could be reading from there and that's obviously going to
be quite different than actually reads from disk.
So I use iostat depending on what I'm doing.
And then also there's different files like, well, I use Linux,
so there's different CPU statistics or memory usage statistics
that you can get.
And so a lot of what my scripts do is just query whatever files
somewhere in the file system that has that information over time
at different intervals so that I can see, okay, what's the memory
utilization or what kinds of effects is this patch having on
my resource utilization?
So that's a source that I use.
And then I also use some different Postgres extensions, again,
depending on the patch.
So pg_buffercache, I look at the...
Again, I just query it at intervals and then see in terms of
the shared buffers that I have, how many are pinned.
And it helps me to see how the patch is affecting the behavior
of shared buffers and utilization there.
So it's, there's a, Basically all of the statistics views are
fair game.
All of the, the output from pgbench itself.
So I parse that.
So there's progress output, which is like, you know, pgbench
does some averaging and that kind of thing.
So I also parse the execution reports that come out, which is
like, they call it a transaction, very confusing.
It's literally just an execution of the script by a worker.
So you might get an average TPS, but depending on, you know,
because of how it's calculated, that might not actually show
you the P99 latency of an individual execution of one of the execution
scripts.
So I typically parse all the execution reports.
There's one line for each execution.
And then do various analysis of that and plot that.
So that's an input and there's always new sources and it's funny,
I'll take some analysis challenges and say, I haven't figured
out like why there's this weird spike in IO at this one particular
time.
And then he's like, well, here's this other input source you
could use to investigate this.
And I was like, I have 55 input sources or what?
How is it possible?
There's something else that I could look at.
But I guess like everything is caused by something.
And so it's possible to find out what it is, I guess, if you
look hard enough.
Nikolay: Right.
Don't you feel like it'll be so good to have P99, P95 in pgbench?
It would be so good.
It would.
It would work well, right?
I'm also using pgbench all the time, like 10 seconds, 30 seconds.
It's so great to see how like caches are filled and so on, like,
but lack of percentiles.
Melanie: Yeah.
Yeah.
So if you do get the execution reports, you can just like, then
read them in with some Python tooling.
Nikolay: Execution reports, it's like hyphen R or
Melanie: It's dash dash L and then you provide a log prefix.
Yeah.
So that gives you the actual time that each execution of the
script.
Nikolay: So this is so slow.
No.
Melanie: Well, I mean, it's not free.
Yeah, that's true.
But I think if you wanted the P99 latency, I'm sure there's statistical
methods for getting it without recording every execution, but
like you would need something like that.
Nikolay: Yeah.
Do we put it, do you use the actual file there or like some RAM
disk, or memory or something?
Melanie: I just, I use tmpfs and then when it's over, I copy
it somewhere to a disk.
Yeah.
Nikolay: Good idea.
I will add it to my tool set.
Melanie: Yeah.
At first, I was pretty apprehensive about doing it because it
sounded like a lot of work, but once you actually have the execution,
you literally just like the statistics modules in Python, you
can just give it the quantile you want and then it just calculates
it's 2 lines of code or 3 or 4 or whatever.
Nikolay: Yeah, and you mentioned pg_stat_io.
This was great when I first saw it, like this should have existed many
years already, and we discussed it with Michael, and we here in
PostgresFM often discuss lack of buffers in plans, for
example, right?
In individual query plans.
Buffers is like, we try to advertise it's so important to have
buffers in execution plans.
And this is a macro level, like high level.
So good to have it, right?
But unfortunately, I cannot say I've used it because it's only in
Postgres 16.
We don't have it in most production systems yet, still.
But maybe next year, I will start feeling it in production with
our customers.
Thank you for doing this.
My question is how you came up with this idea originally?
Melanie: Well, I can't take credit for it.
So Andres has done a lot of performance work for years and years.
So when I joined Microsoft, one of the first projects he suggested
was to add some view that would improve observability of I/O.
And at that time, we didn't have the shared memory stats system.
So, the patch was much bigger at that point because of the way
the stats collector worked.
So I did an early version of it, and then I used it in my own
development, and I ended up working on some of the AIO work and
found it really useful for that.
And then I ended up doing some review of the shared memory stats
patch.
And after that, it made the pg_stat_io patch much smaller and
simpler.
So then I went back to it.
But I honestly, like For me, I just looked at it as this helps
me as a developer doing benchmarking.
So it's cool that users like it because I don't have that perspective
really.
Michael: Obviously it's useful for benchmarking itself, but I'm
wondering about the other way around, like, did you have to look
at its overhead and how did you go about that?
Melanie: Yeah.
So it was much easier, like I said, once we had shared memory
stats, because the way that it didn't need any extra infrastructure
is basically just like another data structure, couple other data
structures.
And then everything works the way that it does now.
We already had some counting for different kinds of reads and
writes, because that's how for explain, analyze and that kind
of thing, we had those counters.
And then there was some reads and writes there in pg_stat_io,
all tables at 1 of the views that has some io things.
And then there was some for pg_stat_statements.
So there was some places where we already had some IO counting.
And then the other places where we didn't, there wasn't too much
risk because like if you're adding some timing for, you know,
whatever, background writer or checkpointer, there are processes
that it's okay to add a tiny bit of overhead.
So you didn't have to think too much about it.
I think the hard thing was actually finding all of the places
that we could possibly do IO for the types of IO that we were
representing and then thinking about how to categorize it.
And especially for the different buffer access strategies.
So like that's one of the things that was completely not surfaced
to users was how buffer access strategies work and how they reuse
buffers.
And so for copy and vacuum and things like that, literally you
have no information about that.
So I think just figuring out how to represent all of that, that
was probably like the least performance work that patch did
in terms of the impact, performance impact of it, just because there
was a lot of instrumentation already, just that was not complete,
if that makes sense.
Michael: So you're saying like we're already paying a lot of
the overhead, like we're running Postgres 15, for example, we're
doing a lot of that counting anyway.
So storing it, but like also displaying it in an extra view,
there's very little added in actual instrumentation.
Okay, cool.
Melanie: Yeah, I mean, like we had for backends that were doing
queries, like counting of reads and writes and hits in most places.
So the places that it was sort of net new were mostly for types
of processes that we don't mind a little bit of overhead if there
was.
And it would be so small.
I mean, it's measuring IO, right?
So like the, if you're doing IO, the overhead of that will cover
any sort of overhead of instrumentation for the most part.
Nikolay: Unless it's hits, actually, it also counts hits.
And I found in some cases, for example, monitoring systems try
to avoid storing these metrics at all, for example, from pg_stat_statements.
statements.
But I had some incidents where we lacked hits numbers.
But pg_stat_io has hits, right?
I'm looking at the documentation just to confirm.
Melanie: Yeah, it does have hits.
And this
Nikolay: might be kind of overhead of counting.
This might be interesting because it doesn't have real IO, I
mean, in terms of disk.
Melanie: Yeah, it actually was already being counted.
We had hits somewhere else, one of the other views, or maybe it
was for pg_stat_statements, I'm not sure.
So that was already kind of being counted.
And when you go to get something from shared buffers, there's
locking and all sorts
Nikolay: of
Melanie: other things that happen that are expensive enough that
it was pretty negligible.
Nice.
Nikolay: Yeah, okay.
But there are some benchmarks proving that Timescale didn't add.
Or like just...
Melanie: Wait, you want to see my receipts?
Nikolay: Yeah, yeah.
I'm just curious.
It's just a general feeling that it doesn't bring overhead or
there's a solid proof of it in terms of benchmarks.
Melanie: That's a good point.
It's mostly based on a feeling.
So, like...
Nikolay: I'm curious, is it worth checking this, for example?
Because maybe we can strip it out somehow or compare 15 versus
16 in various cases and see if...
Melanie: I would look at those benchmarks, but I guess my gut
feeling would be that it, because we already were doing, when
track_io_timing is on, we already were doing a lot of the measurements
that it wouldn't be much different.
Nikolay: But actually, I can answer myself.
We have benchmarks with insane number.
Oh,
Melanie: you do?
Okay.
Nikolay: No, no, I mean, 15, we compare all versions and we try
to reach as many TPS as possible.
Achieved how many, Michael?
4 million?
Michael: Nearly.
3.75,
Nikolay: I think.
Yeah, just select only pgbench, right?
And we didn't see any degradation for version 16 at all.
It's already some kind of proof that there is no overhead here.
And we have wait events and flame graphs and so on.
We would notice it.
Michael: But Nikolay, you wouldn't have had TrackIO timing on.
Nikolay: I want to check it.
And maybe repeat it.
No way.
Yeah, I will check.
Michael: If you did, you should repeat again.
Maybe you'll get 4 million.
Melanie: Okay, I'm checking right now.
Nikolay: Good point.
Michael: But yeah, this is great.
Like, it's super interesting what, like how do you choose which
things to benchmark and which things not to then?
Melanie: Yeah.
So I think developing intuition around that is, so I actually
became a software engineer, like my, the first thing that I worked
on professionally was Greenplum, which is a fork of Postgres.
So almost all of my software engineering experience comes from
being a Postgres engineer.
I was pretty new to software engineering, right?
So I think a lot of software engineers develop intuition around,
like, you know, if you take a lock every millisecond versus every
30 seconds, like, which 1 of those is okay, or whatever, right?
Like, it obviously depends on the context.
But I think there's a lot of sort of performance intuition that
people develop just from being software engineers.
And that's sort of like the first step of, you know, thinking
about, okay, well, is it okay for me to take an exclusive lock
here?
Do I need to find a lock-free design for this or whatever it
is?
And that's when you have something where you had a performance
question and you said, is it okay that I do X, then you have
to think about, okay, how would I benchmark this?
And in a lot of cases, maybe not benchmark, how would I profile
this?
How would I evaluate or microbenchmark this or something like
that?
So, For example, in 17, I worked on some refactoring of heap pruning,
which is kind of scary for different reasons because of like
data corruption stuff.
But from the perspective of performance, You also, because we
do on access heap pruning when you're doing a select query or
reading in the buffer to do an update, changing that code, adding
any sort of.
Additional instructions, you know, potentially has overhead.
So of course, like if you're doing actual heap pruning, then you
have IO from the WAL and you have IO from writing out the dirty
buffer and that kind of thing.
And so you have to think about is the thing that I'm changing
Actually going to matter in the context that it's in?
And then I think evaluate from there.
So and also how important would it be if there was a difference,
right?
So like, on every SELECT query, if it's a little bit more expensive,
that's obviously bad, even if it's only a tiny bit more expensive.
So I, I and Heikki did a lot of microbenchmarking around those
changes to make sure that they seemed right.
And then you have to design the benchmark so that it's actually
exercising what you think might be the bottleneck, which is its
own challenge.
And then like, right now I'm working on something where it has
to do with improving eager freezing so that you freeze more data,
VACUUM freezes more data sooner, And there's a bunch of different
pieces to it.
But 1 of them is a new sort of responsibility that Background
Writer would have for maintaining a new data structure that contains
some new statistics.
And 1 of the questions I got in review was like, have you profiled
this to make sure that it doesn't have an overhead?
And my first reaction was like, but it's Background Writer.
No 1 cares about Background Writer's performance, you know, like
kind of like, it's different than on every SELECT query, you're
going to have some overhead.
But again, it was doing something that was like more, I'd say,
heavyweight.
Like it's doing some calculations that could take time and doing
them while holding an exclusive lock.
And so even though it's Background Writer, like, it's worth proving
that, you know, the number of extra instructions, for example,
is tolerable.
And that's a judgment call.
But what you want is a paper trail, too, that you, like, did
your due diligence.
Which is why it was funny when you were asking me about pg_stat_io's
performance impact, because in that case, I kind of thought,
it's negligible compared to the fact that you're doing IO first
of all, and second of all, we had a lot of that instrumentation
there.
But for most other things, like, I haven't had a patch other
than that probably in the last couple years where there wasn't
a discussion of what the performance impact could be and then
benchmarking or profiling.
Nikolay: Nice.
Yeah, Michael, it was on.
Tracking IO timing was on.
Michael: Well, that's good news for Melanie.
There's potential for 4 million.
Nikolay: Yeah, and we didn't see a difference between 15 and
16, so I mean...
Wow, wow, wow.
Yeah, I already asked our robot to double-check with tracking
our timing off, so we'll see this as well.
Yeah, good.
Interesting.
I hope we'll have more TPS.
I’m curious what you are working on right now and direction of
future work, ideas, and so on.
Melanie: Yeah, so I mean, I started a project last release that
had to do with reducing the amount of WAL emitted by vacuum.
And that was like kind of a refactoring, but it spun out a lot
of other projects.
And I think I sort of wanted to take a break from vacuum this
release, but it didn't work out that way.
So I’m working on a couple of different things around vacuum.
And some of them are some leftover pieces around combining WAL
records that vacuum used to emit, like up to 6 WAL records per
block.
So we've combined a few of them and there's more to do.
So that's 1 of the things, but sort of the more probably exciting
work is around the freezing work.
So that basically was born out of Peter Geoghegan a few years ago,
I think it was '16, did some work to have us do more eager freezing.
So one of the things we hear a lot from users is they have, say,
insert-only tables and none of the data gets frozen.
And then all of a sudden they have anti-wraparound vacuums and
then eventually their system becomes read-only.
But even before that anti-wraparound vacuums, if you don't have
any actual like pruning or vacuuming to do, it can be expensive
from an IO perspective.
And so it can slow your whole system down.
And so being more aggressive about freezing data sooner was something
that people had been asking about.
And so Peter worked on that and it's really difficult to find
a heuristic that works for determining whether or not to freeze
something because if you have a table where you're doing a lot
of updates, like a standard pgbench built-in workload, then
it's a waste to freeze things because freezing emits WAL and
it dirties the page and that kind of thing.
So finding a heuristic that freezes the stuff you want to freeze
and doesn't freeze the stuff you don't want to freeze is then
a journey.
And one of the parts that's really hard is that you have to try
to come up with all of the workloads that are important, the
best case and worst case, especially the worst case workloads
that users might have and think about how it would perform.
And that's so hard.
So that took a lot of time.
And I spent a lot of time improving my different benchmarking
setups and coming up with these different workloads and evaluating
them, looking at them.
So that's one thing I've been working on.
And then last release, I also, so there's the new read stream
API that does vector I/O, does larger reads that Thomas Munro
did last release.
And I worked on the different users.
So for sequential scans and this, I had some additional users
that didn't go into 17 because their performance effects that
you have to sort of analyze really closely and they weren't ready
And one of
Nikolay: them was...
Did you
Michael: use the word analyze deliberately there?
Was it analyze?
Melanie: Yeah.
No, no, analyze went in actually.
Michael: Oh, cool.
Nice.
Melanie: That was the laws work.
Yeah.
So analyze went in, but a bitmap heap scan and vacuum didn't.
Michael: Got it.
Melanie: So vacuum is an interesting one because streaming vacuum
sounds great, but vacuum uses a ring buffer to keep it from having
too much of a negative effect on shared buffers.
So you don't wash out all of the things that are resident and
shared buffers from your workload.
It reuses buffers, which means that it has to write WAL, typically,
and there's a lot of calculation around how big the ring is such
that WAL writer can help you or help vacuum to write out the
WALs.
So vacuum's not doing all of it.
And if you do I/O and if you do reads in a different size, that
means that you're dirtying and needing to clean buffers at a
different rate.
And that actually was affecting how many individual WAL writes
and syncs that we would do.
So like, in a given Fsync, you can have different amounts of
data, but each Fsync's a system call and has overhead, right?
So you want to amortize that cost over a large enough amount
of data and also have WAL writer help to do some of the work.
So by doing bigger I/Os, we are actually making the performance
worse in some cases because of these interactions with additional
WAL syncs.
So Thomas Munro has been working on a, I think he's calling
it streaming write-behind.
It's kind of a way of thinking about, for the purposes eventually
of AIO, but of how backends and maintenance processes and writes
in general in Postgres can sort of make sure that they are cleaning
up and doing writes in large enough, if they're doing larger
writes, right, that they're actually being conscious of when
they should do those writes based on the WAL that it's required
that you emit.
So like if you're doing reads, you don't have to really think
about that.
But if you're writing out data, the WAL associated with those
dirty buffers has to be flushed first.
So you have to think about when do I wanna do writes?
Because you need to think about when do you actually want to
do the WAL writes.
So he's been working on that and it's a tough problem.
A lot of benchmarking, a lot of thinking about it and buffer
access strategies really complicate it.
And those are used for large selects.
Those are used for, you know, copy, vacuum.
So he's doing some work around that and I'll probably sort of
jump in after some of this vacuum stuff is done and try to work
on some of the AIO work that's coming up.
And I think that ultimately, like just between those things,
that'll probably be most of what I end up doing on my own.
But I would like to see there's some for pg_stat_io, there's been
a patch to add WAL-related IO statistics to it that's been around
for about a release.
And we haven't basically what we have, we haven't come to an
agreement on what the right thing to do is because you can technically,
like our thought was that we would have the block size for, like,
you can configure, you know, your block size for Postgres.
You can configure it for WAL.
And that they would be in units of block size, WAL block size.
But that actually isn't always the unit that we do reads and writes
in exactly, like it usually is.
But so now we're actually talking about rejiggering pg_stat_io,
especially in light of vector.io to change it so that it's not
looking at, you know, if you have a thousand writes and then
you have the block size and then you multiply them to get the
number of bytes, do we change it to represent it differently
and put it just in the number of bytes?
And how do you represent that?
If you're doing it, do you want the right number of writes to
be the number of system calls.
So we were just thinking about how to actually represent it,
but I would love to see WAL stats go into pg_stat_io in 18 also.
So we'll see.
And there's also a bunch of other exciting things going on, like
Masahiko Sawada is working on parallelizing the first and third
phases of vacuum.
So he did that really exciting work in 17 on TID store and making
it so that I think that's my favorite feature from 17.
Nikolay: Can you explain it to?
Michael: Yeah, I don't know that.
Melanie: Yeah, so you know, like one of people's biggest complaints
about vacuum is when they have to do multiple rounds of index
vacuuming because the maintenance work mem, even if you set it
to a high value, you might end up filling it up with dead tids
and then you have to do a round of index vacuuming.
And if your indexes are very big, you can imagine that that ends
up really affecting your vacuum performance.
So what he did was introduce a new data structure that organized
the dead tids in a way that was much, much, much more efficient.
And that made it so that you're using way less memory for the
actual dead tid storage, but then it also, you can end up having,
you can end up sort of changing it on the fly.
You could, I mean, basically you're not tied to the same restrictions
that we had around the size of maintenance work mem as before.
So most people are going to not need ever to do multiple passes
of index vacuuming.
So I think that people with very large indexes and large tables
are going to see their vacuum performance be a lot better.
Nikolay: I have big customers with big partitioned tables.
We talk about partitioning for years and it's hard and they will
benefit.
So vacuuming will be faster, basically, right?
Melanie: Yeah.
I mean, if you don't have this problem with the multiple index
vacuuming passes, then maybe not.
But it's something we hear a lot from sophisticated customers.
Nikolay: And also, I remember in PostgreSQL 17, before that,
it was only up to 1 gigabyte could be used for.
Melanie: Yeah, that restriction is lifted now.
Nikolay: Is it related to this work or?
Melanie: Yes, it is related,
Nikolay: yeah.
I suspected so, good.
Melanie: Yeah.
So that's really cool.
And I think it's something that's hard to explain unless you've
had the problem.
I think if you haven't had this problem, you're not really thinking
about it.
But for people that do have this problem, I think it's going
to make a big difference.
He's building on that work and there's a lot of things that we
can do with vacuum because of having, so this read stream API
will make it somewhat easier to do parallelization of the first
and third phases of vacuuming.
So he's working on that as well.
And then I think, you know, there's the, the dreams of having
global indexes involved a step where you persisted the dead TIDs
to disk, because otherwise you wouldn't be able to.
Basically, there's, there's discussion of being able to split
up the phases of vacuuming and be able to vacuum indexes at some
point and come back to it and do it later.
Like vacuum, just do the first stage of vacuuming.
And if you want to do the phases of vacuum separately, you have
to have the dead tuples that you need, basically what index
entries you need to delete, you need to save that somewhere that's
not in memory.
And so there's some modifications to the TID store, but they
can make and probably make it easy to persist.
And I don't know that he's planning on working on that now, but
Dilip had done some work on dead TID storage.
And so I think there's like a lot of exciting things around vacuum
that'll be happening.
And there's also been discussion, which I know has happened in
the past, but new discussion about auto vacuum scheduling.
And not just auto vacuum scheduling, but cost-based delay and
how to change that to be more adaptive and to use statistics
basically that are collected while vacuuming to decrease the
delay adaptively while vacuuming if you're not able to finish
the vacuum appropriately and things like that.
So we'll see.
Nikolay: So we talked to Peter Geoghegan in the past as well.
And I remember the duplication.
So you both sound not like people dreamed to get rid of vacuum
completely, but you sound like there is a big potential for improvements
and it's happening and so it's going to stay, but with improvements.
This is like I'm trying to simplify for our audience.
Melanie: The undo people?
I mean, they still
Nikolay: would have needed vacuum.
The undo people, right?
Melanie: I think that there has been a lot more work on vacuum
this release than there has been
Nikolay: 17 or 18
Melanie: 17 and 18
Nikolay: 17 18.
Yeah, yeah, that's great.
That's great.
So it's, yeah, many people need it.
I mean, Postgres instances.
So yeah.
Michael: I like this kind of work.
I think Peter Geoghegan's done a lot of work in the last few releases,
and you and others that focus on things that will help people.
Well, almost everybody will benefit without having to change
anything.
And that's, those are so powerful.
They're so easily forgotten or easily ignored, but everybody
benefits, it gets better for almost everybody without A, noticing
and B, having to do anything.
Just so powerful, those kind of changes.
So thank you for working on those and encouraging others to as
well.
Appreciate it a lot.
Melanie: Yeah, the dream is that we get rid of all of those auto
vacuum gucks because that's terrible.
Like, there's, I don't know, the fact that there are so many
blog posts.
I mean, yeah, like they should all be gone.
Your auto vacuum configuration should be basically nothing.
I mean, the system should figure out what to do.
Nikolay: We often discuss outdated defaults, but this is a radical
position.
Melanie: Right.
Yeah.
I mean, it's not going to happen overnight, but I think my vision,
a lot of people I think want this.
I think the cost-based delay system is really hard to understand
because it's not intuitive.
The relationship, like scale factor and all of that, It's just
like, what is that?
I mean, I understand why it happened from a developer perspective,
but I think that getting vacuum to do the right thing is like
our job as engineers and shouldn't be users' jobs, basically.
So we'll see.
Michael: Wow.
Well, we don't do clips on this show, but if we did, I think
that would be one.
Nikolay: Also number of workers, 3 workers.
If you have like almost 200 cores and you have only 3 workers
by default, this is insane, right?
Melanie: Yeah.
Well, actually, so I will take it back.
I would say that the one configuration that I think users should
be able to provide is the max number of workers because like
that's your system preference, right?
Like, I mean, maybe not the number of workers, the minimum number,
but the maximum number.
You should be able to keep the system from having 50 processes
just doing vacuuming work if you want.
Right?
I think that they should be.
Nikolay: Because this is second phase of rights.
We need it.
Right?
Because if you just deleted something or updated something, work
is not done until it's vacuumed and you need more workers anyway.
Michael: So- Also, Postgres doesn't know how many cores, right?
Nikolay: Doesn't know.
Melanie: It doesn't take that into account.
Yeah.
I mean, but like people are allowed to use their servers for
other things at the same time. I maybe you don't, but like we sort
of we don't assume that we have access to everything, I guess.
Michael: Yeah, makes sense.
Nikolay: Thank you for coming.
It was very interesting. Thank you for this work again.
Thank you for pg_stat_io work and so on, and benchmarks.
I like, we will keep an eye on it and good luck.
Good luck.
Melanie: Thank you.
Michael: Yeah, thanks so much.
Melanie: Thanks.
Michael: Bye.