A weekly podcast about all things PostgreSQL
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.