Nikolay: Everyone who is running
Postgres has autovacuum, and

most of people heard about autovacuum
and vacuuming and bloat

and this garbage collection.

But a lot of databases grow with
problems being unnoticed and

they start tuning autovacuum
too late.

So this topic today is brought
by you Michael, right?

Hi Michael.

Michael: Hi Nik.

Nikolay: And we definitely had
this topic discussed in the past

in terms of like vacuum and bloat,
but we never discussed the

vacuum itself and how to tune it
properly and what are the best

practices.

And yeah, I'm glad that you brought
it.

Let's talk about this.

Michael: Sounds good.

Yeah.

I'm looking forward to hearing
your views on this kind of from

a seeing lots and lots of different
clients perspective because

I get the impression dealing with
people less frequently that

Often it's left really late before
people start tuning or to

vacuum and when I say late a startup
will grow to relatively

significant size before touching
those parameters And I often

think they could have avoided quite
a few headaches and got into

issues slower, if that makes sense,
by just changing a few settings

much earlier.

So yeah, I was keen to get your
thoughts on If you see the same

and also like, when would you advise
people like startup life's

busy, when would you advise people
looking at this kind of thing?

Nikolay: Yeah.

First of all, every single client
which comes to us, we talk

about autovacuum tuning on any
platform.

Doesn't matter.

Self-managed, any managed service.

We always touch this topic and
always like in hundred percent

of cases there is something that
should be done there.

Which means your point is absolutely
right.

It should be done earlier because
when people come to us for

help it's already, they experience
already some problems, some

troubles, right, and this is a
reactive approach.

Michael: And just to clarify, when
you say 100 percent, do you

mean some percentage have done
loads of tuning and still have

issues and some percentage have
done nothing at all.

What's the kind of distribution
like?

Nikolay: It's hard to say.

Actually I checked how many clusters
we observed lately.

It was like 140 or something where
we used thorough like comprehensive

health analysis and tuning and
recommendations and work closely

and so on.

So I'd say 0% have an ideal picture
in terms of how autovacuum

is tuned.

Most customers running on managed
services, especially RDS, have,

let me say it, half-ass tuned
autovacuum.

This is new term we can use here
because this is like, it amazes

me why it's so.

We can dive into details soon.

And only few, like literally that's
a several, I saw they have

very well tuned autovacuum and
we just shift focus to why it's

still not enough and what we should
do with specific pieces of

workload.

Michael: Sure, makes sense.

Nikolay: Local tuning already,
what to do about it.

And in most cases there we also
involve, we focus on specific

workload, but also we go, of course,
beyond just autovacuum

tuning, because usually this is
like some pathological workload

or some, there are some problems
which are not conflicting, how

to say, like they are blocking
autovacuum work, right?

Yes.

Before we dive into details, maybe
let's just explain to wider

audience what autovacuum is, because
I think many still misunderstand

it.

Recently I talked to, you know,
like our focus is fast growing

startups and I talked to technical
founders quite often.

These guys are super smart.

Technically they understand, they
definitely understand throughput,

latency, all the numbers and so
on.

But I see they just didn't have
time to dive into autovacuum,

what it is.

They heard bloat issue, MVCC, Postgres
is very widely criticized,

right?

But what autovacuum is and what to
do about it, what they need

to have, it's like, it's always
a good topic to discuss and dive

into.

So my simple explanation, autovacuum
is just garbage collection,

first of all.

It has a few more jobs, but the
main job, original job, is garbage

collection.

When you have updates, deletes,
or failed inserts, by the way,

the third part is usually forgotten,
but failed inserts, all

of them produce successful updates,
successful deletes, and failed

inserts, rolled back inserts.

They all produce dead row versions
called tuples, right?

Michael: Probably also rolled back
updates.

Nikolay: Oh yes, you're right.

Yeah.

Successful deletes, rolled back
inserts, or any updates.

Yeah.

Besides HOT updates, which are
nuances, right?

Even HOT

Michael: updates, yeah.

Nikolay: If we want to be thorough,
like we are destroying my

intention to provide simple explanation.

Let's keep it short.

So it's first of all garbage collections.

So if, especially for guys who
run Postgres on machines with a

lot of cores, like typical like
Intel machine 96 cores or 48

or like AMD hundreds of cores,
right?

Michael: They- We have very, we
have a very different definition

of what typical is for-

Nikolay: Well, typical big, large
database.

Michael: Sure, sure.

Nikolay: So it's, it amazes me,
like I, I saw, I told you, I

saw Postgres clusters achieving
10, 20 terabytes with default

autovacuum settings.

Somehow surviving, already having
5 replicas.

It's insane.

Michael: This is what I meant in
terms of like, should people

be doing it earlier or is it okay?

There's a certain argument.

Nikolay: It's not

Michael: okay, no.

Yeah, I think I agree, but as you
say, they've somehow got by

even to that large extent.

But I've also seen smaller systems
really struggling because

autovacuum hasn't been tuned.

So far fewer, like maybe not even
a terabyte, but struggling

because of a certain workload.

So yeah, it's not just size, right?

Nikolay: It can be a table of 2
gigabytes in size, but it's mostly

bloated, like 99.9%.

I saw it many times, especially
with queue-like workloads.

And then it can suffer, small database,
but it suffers from the

fact that autovacuum is not tuned
and maybe something is blocking

it.

Garbage collection job is blocking
it.

So we need to combine both things
here.

But my point is, autovacuum is
just garbage collection.

If you tune Postgres, or maybe
your provider tuned, for example,

to have bigger shared_buffers,
bigger cache, right, and a work_mem

for sort and join operations and
so on.

If it's already tuned and max_connections
has increased, maybe

a pooler is in front of it, like
you tuned it to handle workload.

Why the heck garbage collection
remains untuned?

It should be tuned together with
everything else, right?

So it should be.

But we see it's often lagging in
tuning.

And it can be a local workload
on small database already suffering,

or it can be a big cluster and
garbage collection mostly default.

autovacuum is mostly default.

But that being said, we should
mention that there are a few more

jobs that the vacuum has.

Maintenance statistics.

Yeah?

Do you want to

Michael: stick to the vacuum ones
first?

Because I think the name autovacuum
is quite a good name in

that it's all of the jobs that
vacuum does but automated But

with 1 addition, which is there
is also an auto-analyze

feature Right, which is what you're
just about to talk about

in terms of statistics, but I feel
like the other vacuum jobs

are worth—do you think of it like
as garbage collection, statistics,

and then the other vacuum jobs?

Nikolay: — It's just easier to
memorize.

Statistics also should be maintained
up to date.

If it's lagging, it may affect
performance, might affect performance.

But you're right, there are also
a couple of more jobs.

Transaction ID wraparound.

If you have a lot of transactions
per day, we know there are

sometimes some like 1000000000
per day.

It's a lot.

In this case, churn is high and
int4 is not enough.

Half of int4 is not enough.

transaction ID wraparound prevention
should be very frequent

in each tuple, in each row version.

And also there is Another very
important job, especially for

performance, it's maintaining visibility
maps.

It's a bitmap with 2 bits for each
page.

Page is 8 kibibytes, right?

And 2 bits, is the whole page fully
visible, All visible?

Means all tuples are visible to
all transactions.

And second is all frozen.

Yeah, just previous topic.

Transaction ID wraparound prevention.

So not to visit this page again
if we know that all the tuples

are in the past, they're already
marked frozen, so they're definitely

in the past, even if their xmin,
xmax look like they are in the

future.

This is freezing.

It marks like...

All frozen bit means like it's
a whole page in the past.

The next vacuum will just skip
it.

That's optimization as well, right?

So for more frequent vacuuming,
not to do the same job once again,

right?

Michael: I want to come back to
that, but before we do, The all

visible is really important for
index only scan performance,

right?

So if an index only scan can know
that every single tuple on

that page is visible to all transactions
then it can serve the

result it finds in the index because
it knows it can't have changed.

So that's a really neat optimization
without having to do what

it calls a heap fetch.

So it's like fetching the data
from the table instead of from

the index.

So if you ever if you see an index
only scan that ends up with

a lot of heap fetches that's because
Postgres couldn't...

Yeah it might it might be that
information was in the index but

because it Postgres didn't know
for sure that it hadn't changed

it still went and got it from the
heap.

So it might be the exact same information
that it already had

from the index, but there's this
inefficiency if vacuum hasn't

run recently enough.

And it only takes, because it's
that bit 0 or 1, it only takes

1 tuple on the page to have changed
for any of the tuples on

that page to lose out on index
only scans.

Nikolay: I think if information
is not in the index, you mean

if we select columns which are
not part of index definition,

it will be index scan immediately,
not index only scan.

Michael: Yeah, of course.

Nikolay: And index only scan already
means that information is

present.

Information means values, but what
is never present in indexes

is visibility information.

This xmin, xmax.

So For that we need heap, but if
visibility map bit says for

this page it's all visible, this
can be skipped.

So if the fetch's number goes lower,
ideally it should be 0,

then it's a perfect situation,
it's a true index-only scan.

If it's high, it approaches the
performance of index scan, which

is like always consulting heap.

Yeah, naming should be better here,
but my position on naming,

I'm always like, index scan could
be renamed to something which

would be explicitly mentioning
that heap is always consulted.

Anyway, this is a different topic.

I think we covered quite well what
autovacuum does, and again,

the main job is just garbage collection.

Also, I wanted to mention, autovacuum
is improving a lot from

release to release.

And it's enabled by default, of
course.

It should be disabled only by experts.

For example, PgQ, Skype engineers
decided to disable autovacuum

for queue partitions.

And it's also disabled in PgQue tool,
which is built on top of

PgQ.

Name is awful, right?

So anyway, there autovacuum is
disabled, but normally it should

be never disabled, and it's enabled
by default.

And it's done, drop, and so on.

So it's just a garbage collection
plus additional features and

it's improving from release to
release and Funny fact I recently

purchased some Chinese modern auto
vacuum for home with washing

feature.

It's very similar, like advancement,
like I had a few years ago,

I had a couple of them and they
were now there, like so many

features, so it became much smarter.

And they have multiple functions.

So I enjoy how it works really.

But it requires some tuning as
well.

Michael: Yeah, I was gonna say,
Bruce brought this up in our

episode with him, didn't he?

He said people complain about vacuum
and bloat with Postgres

and MVCC, but he's saying, which
version are you complaining

about?

Because it is continually getting
better.

But I would say that some of the
defaults are still very conservative,

or maybe make sense at smaller
table sizes.

And they do have scale factors
built in, but they make less and

less sense I think as tables get
bigger, some of them.

Nikolay: So my position on defaults,
they are all BS defaults.

They are like for, I call it Postgres,
kitchen kettle Postgres,

which should have, I don't know,
like 1 gigabyte memory and some

Raspberry Pi or something, a smart
pod or something.

This is it.

For them it's okay, but for any
even like small cluster with

8 cores, 16 gig of memory, it's
not enough at all.

And the settings can be split to
3 categories for simplicity.

Number of workers, which requires
restart.

The only thing which requires restarted
number is number of workers,

which is 3 by default.

If you have 8 cores, 3 maybe is
okay.

It's okay.

If you have 32, 64, 48, 96 cores,
3 is not enough, but we see

it all the time.

People come to us, 96 cores, 3
workers.

This is 1 thing.

Michael: You say it's not enough.

I think you're right in the sense
it's not a sensible default.

Like why not give it more in case
it needs it?

Nikolay: Right.

Michael: But as you said, sometimes
people come to you and they've

only had 3 workers the whole time
and somehow it's not fallen

over.

Nikolay: It's grinding through
challenges.

Yeah.

Like super bloated tables, super
bloated indexes, and it's a

lot of dirt to clean, which is
great for us.

We have opportunity to help them.

Instead of framing, oh, how bad
it is, I actually have it, and

it's actually our product strategy.

We frame it, oh, great, we can
help you really quickly, really

quickly.

Either way, we have low-hanging
fruit.

Let's go.

With our tooling and methodologies,
it's super simple.

This low-hanging fruit, let's go,
and your database will fill

and breathe quite soon.

Much better.

You can even consider downgrading
or postponing upgrade for a

couple of years after this.

Yeah.

And this is great.

Yeah, this is 1 thing.

And this is super important thing,
we just discussed.

This is how garbage collection
works and updates, deletes, and

sometimes inserts.

They do just part of the job.

Right?

And these poor autovacuum workers
need to do the rest of the

job.

Michael: The tidying up afterwards,

Nikolay: yeah.

You have 1,000 max_connections,
meaning you have up to 1,000

back-end workers which produce
all those dead tuples.

And only 3 that should catch up
with them.

It's nonsense, right?

It feels like it's nonsense.

So it should be tuned.

And simple rule, like we say, like
actually let's consider like

25% of vCPU count, But we should
do it very properly, thinking

about memory management and so
on.

I know there is opposition to this
view, but this is my position.

It's a simple case, just let's
give more workers, especially

if you have partitioning.

Because, fun fact, a worker processing
a table, if it's a huge

table, it will be processing it
sequentially, your table and

its indexes, and that's it.

If it's a partitioned table, it
can be distributed.

Each partition can be, you can
parallelize this, right?

So it makes sense to have more
workers if you have more partitions.

And it's great to have partitions
and more workers together.

This is the best situation.

And 2 more categories.

1 defines frequency of revisiting.

Frequency, how frequent workers
come to do stuff.

And another defines how fast they
can throughput, basically,

or throttling.

I think

Michael: throttling's the better
way of thinking about it, yeah.

Nikolay: Throttling, yeah, limiting.

It's cost_limit and cost_delay.

And how often they visit defines
by scale factors and threshold,

but I'm okay to think about only
scale factors, actually.

At first glance, it's enough.

So we have 3 scale factors today,
right?

Or maybe there is also in Postgres
18, there is

autovacuum_vacuum_max_threshold.

This is a new stuff in Postgres
18, which changes the picture.

So usually we talked about only
scale factor and threshold.

Scale factor by default, 3 scale
factors, but the basic 1 is

autovacuum_vacuum_scale_factor,
right?

And it is either 10 or 20 percent.

So I think 20 percent, right?

Michael: Yeah, so I suppose because
the threshold by default

is 0.2, right, so 20 percent.

Let's say you've got 1000000000
rows in the table.

It doesn't wait to get to 200 million,
it starts at 100 million.

Nikolay: Yeah, so we don't allow
more than 100 million, which

is a huge number.

Maybe huge, maybe not.

100 million dead tuples in the
table.

Michael: It's quite a lot though,
yeah.

Nikolay: Yeah, so the basic is
the vacuum scale factor.

This is the first thing.

And it's 20% by default, which
means you need to accumulate 20%

of dead tuples in the table before
it starts garbage collection.

When I just say this, I had recent
experiences.

When I said this to some startup
CTO who which is growing and

so on he like, oh We don't have
garbage collection before 20%

of dirt accumulated This is

Michael: Of course when when things
are small, it's not a big

deal.

But when things get big, this is
just a huge number.

And it's not just...

Nikolay: Yeah, it's...

Let's say you have like almost
terabyte size table in terms of

size, billion rows unpartitioned,
which is common, I see this.

20% means 200 million rows, dead
tuples, right?

Which is by the way above that
threshold introduced in Postgres

18.

So, it's like, wow.

Michael: But it's not just storage,
right?

It's also polluting caches and
indexes.

It's everything.

Nikolay: Oh yes, oh yes.

This is big point.

And actually, let me add 1 important
notice here.

Usually, it's not a big deal for
smaller tables.

But in some cases, we see local...

So if, for example, 1 row updated
many times, thousands of times.

For 1 logical row, you have many
versions.

And globally, like table is big,
but for this not lucky row,

You have so many versions and they
can be, might be scattered

among many pages.

So, they are sitting there as data
tuples and for this specific

query select where id equals something.

This means that all of them need
to be fetched to be checked

in terms of visibility for this
transaction and it degrades for

a particular ID, like local degradation.

Lots of queries are fine, but this
unlucky because it had a lot

of updates or something, right?

It became really degraded.

And it's like, what's happening
here?

This is where p99s or something
are good to have because average

will hide it, average latency and
so on.

And this means you should be vacuuming
more frequently, even

on smaller tables.

So this local degradation due to
dead tuple for particular logical

rows, dead tuple count is high.

So it's happening, and it's happening
silently.

That's the problem.

And tooling, which is like lacking
p99s and so on, is not helping

us to understand.

That's why still logging of slow
queries helps, and auto_explain

helps to catch these like edge
cases and start analyzing why

it's happening.

The next day you select the same
row, maybe it's already vacuumed

and degradation has gone.

But in that moment, if you go back
in time and select it, you

see so many buffers.

This particular query, it's very
like it's primary key lookup,

but too many buffers somehow.

That's why.

So it's important to feel it because
it's really like subtle

problem.

It's hard to catch it if you don't
know where to look at.

Yeah.

And you're right.

So scale factors.

And since Postgres 13, there is
also autovacuum_vacuum_insert_scale_factor,

and there is also
scale factor for analyzing.

For insert, it's really great.

I remember Darafei, the guy who,
from Belarus, I think, he introduced

it.

It was so obvious, why didn't we
have it?

We insert a lot with double table
in size, but vacuum doesn't

come.

And we need vacuum not to clean
up the tuples after successful

insert, but to rebuild visibility
maps, first of all.

And yeah, it is great because if
you have select count or something,

you want index-only scan to be
with low heap fetches, as you

mentioned.

So anyway, there are these scale
factors, And it's enough at

first glance to look only at them.

Yes, you can go deeper and think
about thresholds and how additional

tuning, but scale factors is already
enough.

You want them to be really low
in OLTP workloads, just 1%.

Sometimes 2%, sometimes 1%, I see
people choose, but 1% is is

simple rule.

Set it to 1 percent for all scale
factors and benefit.

Michael: And to give people an
idea of how drastic a change that

is, these scale factors are 20
percent for the vacuum threshold,

10 percent for the analyze threshold,
and I think 20% also for

the insert scale factor.

Nikolay: Yes.

So

Michael: dropping from 20 to 1
might feel like a huge change,

it is quite a huge change, But
you're not repeating the same

work, right?

Like it's not, there's a tiny bit
of repetition.

Yeah, it's, this is a slight difference
to analyze, isn't it,

in that if you run vacuum twice
in quick succession on the same

table, It's not doing all of the
same work again.

It's done a lot of the cleanup.

It's like tidying a room.

Nikolay: Some work

Michael: can

Nikolay: be done again.

For example, it's very nuanced
here.

It's super interesting.

Like you mentioned, it's not only
about data storage.

It's polluting memory, WAL.

WAL means replication, WAL backups.

It's polluting everything.

And memory is super, memory is
expensive these days, right?

So we should optimize, not about
data storage, we should think

about how many pages we need to
keep in this working set.

But the ideal situation when you
raise the workers, make a vacuum

to visit us frequently with lowering
scale factors, all of them

to say 1% is very rough rule here.

But also partition quite well,
because in this case some partitions,

especially if it's historic data,
they will be processed with

marked all visible, all frozen,
and very rarely those pages will

be touched.

So vacuuming will be like a breeze,
like skipping, right?

Unlike if it's a huge, messy heap
with terabyte in size or so,

and any page can be touched at
any given time.

It means it will be need to process
again.

Of course it means that if you
have infrequent vacuuming of a

huge table, of course you're doing
work less, because maybe this

page was visited multiple times
before it was vacuumed.

And if you start vacuuming frequently
and between 2 processing

these pages are revisited by some
rates, then it needs to be

vacuumed again.

So it's multiple processing of
the same page versus a single

processing.

So we cannot say it will be, but
it's highly optimized.

Vacuum is highly optimized.

But if you have a huge table, not
partition, maybe it will be

like frequency, high frequency
vacuuming will cost you some extra,

but it's worth it usually.

So there are nuances here and trade-offs,
of course.

But usually in OLTP, we very strongly
recommend to lower down

scale factors so vacuuming happens
more frequently.

And the fact is that if you're
on managed service, I see it's

quite common.

This is a big problem.

RDS included, for example.

They don't care about this somehow
and they keep defaults.

I would like to hear their reasoning
why they don't proactively

tune it for the customers.

And because I

Michael: mean, I mean, I mean,
I'm just makes, because they're

a scale factor, right?

They can make sense at any scale,
right?

Like it, it's fine when you're
small, These are inexpensive things

to run on small tables, and it
works when it's big.

There's no reason not to tune it
from the start for all customers.

You don't have to be careful about
which instance size.

Nikolay: And I don't complain,
because as I said, I enjoy when

I see the problem.

And I'm excited to help and have
low-hanging fruit and see

how health next day is much better.

But of course we need also automated
reindexing, we need the

pg_repack to be used and so on.

By the way, we mentioned that Postgres
19 will have...

Yeah.

Yeah, it will have...

Michael: REPACK and REPACK CONCURRENTLY.

Nikolay: That's awesome.

And we should somehow revisit this
topic, maybe inviting the

guy who made it, right?

Michael: Yeah, I think it was...

Antonín, right?

Yeah, Antonín was...

We had him on, didn't we, to talk
about pg_squeeze, which inspired

the work.

Nikolay: Remember in the very end
of the episode, I learned about

these plans to make VACUUM FULL
concurrently basically, which

is like became REPACK CONCURRENTLY.

I couldn't believe, I had like
doubts, is it like, but here we

go, like this is happening a year
later, it's planned to be released,

I hope it won't be reverted, it's
a super important feature to

have, it's interesting how out
of the box.

Michael: I have to admit when I,
yeah, I saw that REPACK, they

introduced effectively a rename
of VACUUM FULL to REPACK and that

was going to make 19 but it didn't
have a concurrently option

and I was like, oh, that's such
a shame but it makes sense.

Nikolay: Another year of waiting.

Michael: And then about a week
later, REPACK CONCURRENTLY gets

committed as well.

So yeah, that was great.

Nikolay: It's yeah, it was a mood
swing for me as well.

It was like, oh no.

Oh, yeah.

So anyway, back to scale factors.

This is probably 1 of the most
important to lower scale factors

for OLTP workloads and benefit
from more frequent vacuuming

and analyzing as well, right?

Michael: Just a question, is there
any stage that people are

at where you wouldn't recommend
this.

I was thinking when should people
be thinking about these things

but I think those 2 are such simple
or those 3 let's say are

such simple settings to change
you could change them from the

start and never think about it
again like

Nikolay: globally I

Michael: was thinking.

Nikolay: This is complex and this
is complex thing in terms of

we cannot talk about autovacuum
tuning completely without analyzing

what's blocking the work.

Michael: If anything.

Nikolay: Yes and that's why I'm
like we recently developed a

dashboard for xmin horizon analysis
because we we probably need

to revisit it once again in terms
of long transactions versus

pure xmin horizon blocker analysis
and autovacuum throughput

analysis and again like this I
cannot recommend enough Laurenz

Albe recent blog post about how
he monitors autovacuum.

Combining with what, how we monitor
autovacuum, I think it's

much like we leveled up monitoring
of autovacuum blockers basically,

right?

So xmin horizon, there are 5 reasons
why it can be blocked.

And if you keep those problems
unresolved for hours, or sometimes

dozens of hours, tuning autovacuum
to run more frequently, especially

if you reduce also autovacuum_naptime, how
long it sleeps between runs,

and raised workers.

And also, some problems might happen.

For example, it might start consuming
too much resources just

trying to do something.

It cannot do something.

And also, logging can be, you can
have observer effect.

If there is a, like it tries to
do something, it cannot, but

it reports and many workers report.

And if you, at extreme, you have
log_autovacuum_min_duration

set to 0, meaning all attempts
are logged.

In this case, if you have 25 workers,
for example, and they cannot

do, and they autovacuum_naptime 1 second,
it's a storm of logs.

This is 1 problem I can definitely
imagine.

Michael: You mean 1 millisecond?

Nikolay: No, by default autovacuum_naptime
is 30 seconds.

Michael: What am I thinking of
that's 2 milliseconds by default?

Nikolay: Oh, actually I was wrong.

I'm looking at it right now, it's
60, 1 minute.

autovacuum_naptime is by default 1 minute,
which is quite long in my opinion,

so we usually say let's choose
a few seconds, but I admit aggressive

tuning without resolution of xmin horizon
blockers might lead

to storm of logging and resource
utilization as well.

So this is what I can think of
from top of my head.

Michael: Yeah, so sorry, I was
thinking of cost, there's a cost

delay.

Nikolay: Right, this is the last
thing we haven't touched.

So as I said, There are like throughput
in terms of number of

workers, there is frequency, how
often vacuum and analyzing happens,

and also the remaining piece, big
piece is throttling.

You said we should say throttling,
right?

So 2 parameters.

And in Postgres, who is responsible
for naming?

So there is an attempt to save
on some defaults or something.

There is some parameters that have
relationships.

Inheritance, right?

So there are 2 parameters,
cost_delay and cost_limit.

There are vacuum_cost_delay,
vacuum_cost_limit, this pair.

And by default, I think cost_delay
is 0, means it's unthrottled.

If you run vacuum manually, it's
unthrottled.

It tries to go full speed, usually
bounded by I/O, disk.

Right?

And there is also autovacuum_vacuum_cost_delay,

autovacuum_vacuum_cost_limit, 2 parameters.

1 of which I think
autovacuum_vacuum_cost_limit is set to minus

1, which means it inherits from
vacuum_cost_limit, which is set

to 2 milliseconds, right?

It was 200 milliseconds and then
it was, or 20 milliseconds in

the past before Postgres.

Michael: Yeah, the delay is, I've
just looked it up:

autovacuum_vacuum_cost_delay is

Nikolay: 2 milliseconds.

Yeah, I confused everything, I
see.

So autovacuum_vacuum_cost_delay
is 2 milliseconds and it was

before Postgres 12 it was 30, so
it became 2 in Postgres 12 which

is great.

And autovacuum_vacuum_cost_limit
is minus 1 means that it inherits

from vacuum_cost_limit Which is
by default 200

Michael: Which is like an arbitrary
unit of like work right and

there's some various things that
make up, yeah.

Nikolay: Yeah, I your CPU cycles,
there is some math.

What I don't like about acquisition
of EDB, of SecondQuadrant

by EDB, they just killed the blog.

Blog was brilliant.

And they had a great blog post
about some math.

If you think only about the I/O part,
all defaults before Postgres

12 yielded to 8 MB/s of reads.

So with 120 milliseconds, it was
roughly 8 mb of reads.

So bumping 20 milliseconds to 2
milliseconds, I remember it was

first considered to raise cost
limit, but then decided to reduce

frequency of this
throttling, right?

Because it makes things smoother,
right?

So going down from 20 milliseconds
to 2 milliseconds and maintaining

the cost_limit 200, inherited from
vacuum_cost_limit.

I don't like this, just put straightforward
defaults.

It doesn't hurt.

Minus 1 makes life of humans much
harder, much harder, right?

Like it's, okay, anyway, maybe
I should just propose it.

Maybe it wasn't proposed.

Maybe it was proposed.

I don't know.

It's My battle over dozens of,
over decades already.

This is happening decades, this
suffering from memorizing what

minus 1 means.

We forgot actually memory management.

Fourth piece, right?

There is minus 1 there as well.

autovacuum_work_mem is minus 1
by default, which means you need

to go and consult and there is
big interesting trick happened

in Postgres 17, separate story.

So that's

Michael: maintenance_work_mem,
right?

Nikolay: Yeah, maintenance_work_mem.

If we have time, we can talk about
that as well and dive into

it a little bit.

So, minus 1 for
autovacuum_vacuum_cost_limit inherited 200,

this like arbitrary cost, not arbitrary
actually, but some cost

like 2 milliseconds.

Since Postgres 12, meaning in all
current versions, it means by

default that can just take reads
of 80 MB/s,

roughly, which is not enough in
modern systems.

Ah, and it's shared among all workers,
unless you start tuning

a table level, which makes things
much more complex because then

quotas with throttling becomes
local for this table, for this

worker which is processing specific
table.

I prefer not to go there.

We can have a separate episode
for table level analysis.

Tuning, table level tuning.

So big picture is like by default
it has only 80 MB/s

of reads and if you
have powerful, even EBS volume

which I don't know it's a nitro,
old nitro architecture so it

definitely NVMe, like it has
good throughput.

80 is tiny among all workers.

Again, this is garbage collection
for everything.

So it should have several hundred
megabytes per second at least.

But the good thing is, for example,
RDS fixes this, especially

on Aurora we see enormous quota.

Throttling there is quite good
in terms of like it has a lot

of power.

But in many other cases, we see
it needs attention and we need

to give more like, how to say,
work capacity because otherwise

it's too slow And that's not good.

Michael: It never catches up, if
that makes sense.

Nikolay: It's a kind of situation.

You see all the workers you allocated,
they're always busy.

This is number 1, how you can understand
that something is not

okay.

And second, we have a special report,
we have a query, I have

a blog post about that, and we
also have it in our monitoring,

like a queue or line of tables
which sit there and should be

already processed but not processed
yet.

And this, like, basically a wait
line for tables.

And I think Laurenz also has in
his blog post a slightly different

analysis.

But the idea is the same.

How bad is it, the picture?

It should be done already, but
not yet done.

This is how you also feel this
situation happening.

Michael: From the other side of
things, I saw a good post recently

by Jeremy Schneider about the risk
of completely removing throttling.

So he wrote a good post recently
about an issue they encountered

once.

I think they reduced the...

Nikolay: I haven't seen it.

Sometimes people put to Cron.

We haven't touched this specific
cases related to MultiXact

member situation when people have
this approaching and they need

to vacuum separately.

Switching of indexes because index
vacuuming because for that

problem you don't need, and this
is actually another job to clean

up those things.

You don't need indexes to be involved
and this slows down the

whole thing if you have many indexes.

So anyway, I wonder, I need to
look at that, but, and when you

do manual vacuuming it's unthrottled,
as I said, because

vacuum_cost_delay is 0.

So it's not checking at all.

Let's go.

Michael: But I think that's, isn't
that because every now and

again, you want to run a manual
vacuum as like an emergency procedure?

Like,

Nikolay: yeah, it makes sense.

I agree.

But if it's throttled and it's
unthrottled, Actually, this is

super interesting for me.

In the past, I had problems with
unthrottled vacuum, but it was

when we had some enterprise disks
and self-hosted Postgres and

our capacity was like 400 megabytes
per second.

I also am very curious right now
in Postgres 18 with AIO and

so on, like how a single worker
might behave.

Remember I told you I was super
impressed to see the pg_basebackup,

which is single threaded
and I expected 100 megabytes

of reads from it.

Suddenly it amazed me, approaching
like gigabyte per second of

reads in Postgres 18.

So maybe a vacuum also, I'm not
sure, like this I haven't visited.

So- Yeah,

Michael: I think it was me that
suggested it might be AIO related.

Nikolay: Yeah, but let me correct
myself.

So there is fourth piece memory,
super important.

And if we don't think about it,
it might bite badly, especially

if we raise workers.

So all our clients, we say, okay,
we raised workers, but we're

jumping from say Postgres 15 to
17, you must limit memory for

autovacuum in advance because
it's by default minus 1 means

it takes the value from maintenance_work_mem.

And maintenance_work_mem people
usually raise quite quickly thinking

they will have much faster index
creation.

So they put 2 gigabytes or so.

Now I think like 25 workers, 2
gigabytes is already a lot, right?

And the problem is like before
Postgres 17, you might not notice

it because autovacuum workers
suddenly limit themselves with

1 gigabyte and then suddenly jump
to no obvious limit anymore.

No 1 gigabyte limit anymore for
autovacuum workers.

And If you have 4 gigabytes, I
saw 8 gigabytes, like these things,

right?

And many workers, it's huge memory
consumption.

Michael: Or risk, like not necessarily,
but there's the risk

that they could take up that much,
right?

It's not pre-allocated or anything.

It's just that's the budget they
have.

Nikolay: It can influence and it
can increase risks of out of

memory.

—

Michael: Yeah, yeah, yeah.

— OOM killer.

Nikolay: —

Michael: So, but then you can set,
yeah, great.

But then you can set the autovacuum_work_mem.

I've forgotten the exact name of
it.

Nikolay: — Yeah, I found like half
a gigabyte, some math should

be done there, and we usually like,
we have it, like some formulas.

So usually half a gigabyte, even
maybe lower, and it will be

okay, right?

It's interesting topic, but yeah,
so usually for maintenance

work, again, I wish like this minus
1 is killing me, like decayed

already.

Like why we don't put explicit
value right away?

Why this like I need to go in some
maze and remember.

It took me many years to settle
in terms of these, like, relationship

between vacuum and autovacuum
settings.

And for non-expert who is not working
with Postgres settings

every day, it's impossible to memorize.

Right?

It's super tricky.

Michael: So you would have hard-coded
values for each even if

they start with the same value?

Nikolay: Doesn't hurt.

Yeah, so if we have maintenance_work_mem by default, I don't know,

what is it, like 128 megabytes
or so, like maybe more.

Just copy paste.

This is not the place where we
should save on copy pasting.

We shouldn't save like few bytes.

Minus 1 is 2 bytes, right?

128, it's okay, a few bytes more.

This is not the place where we
should save it, right?

This is this complexity, extra
complexity, which is like, okay,

I need to redirect this frustration
to proper place.

Michael: Yeah, by the way it's
64 megabytes default

maintenance_work_mem.

I didn't realize it was

Nikolay: that low.

Postgres, yeah, Postgres.

Yeah.

Michael: All right, awesome.

And actually, the last thing I
wanted to say, so as a little

summary, if people have less than
8 vCPUs, maybe leave, you can

probably leave the workers in place,
but still change the thresholds

and it's just those 3 thresholds
will get you like 90% of the

way there probably.

I think most of...

Reduce, you said down to 1% I think
that makes a lot of sense.

I've generally advised people to
like 5 or 2% as like a starting

point but 1% makes a lot of sense.

It's a bit more, but I think it

Nikolay: won't save you from large
tables It won't save you from

this problem of local degradation
for particular rows It's just

like maybe you need to think about
it additionally, but overall

it's like good, very rough tuning
for

Michael: all the teams.

Yeah, absolutely.

I think that'll get a lot of people
a lot further.

Most providers let you set that
globally.

I did come across 1 recently that
only lets you change these

things at a table level, at which
point my advice would be focus

on your important tables and just
change a few of those.

But that was only 1 provider.

Nikolay: Or just migrate.

Michael: Or migrate, yeah.

That small task of migrating database.

But yeah, so that sounds wise.

And then, yeah, memory tuning,
make sure it's not using

maintenance_work_mem if you've upped that.

Smart advice.

Nikolay: Yeah, there are other
parameters, but it's like already

deeper things.

Michael: Yeah, sounds good.

Nikolay: My simple rough tuning
list, raise number of workers.

This requires restart, unfortunately,
then make visits more frequent,

decreasing scale factors to 1%,
for example, roughly, and give

more power, unless it's already
done by provider, raising, for

example, raising cost_limit.

Oh yeah, cost_limit, right?

And default settings, as I said,
Postgres default settings, because

not providers default settings.

So it's roughly 80 MB/s for disk reads.

Think how much you want, like maybe
just raise it, maybe sometimes

4 or 5 times, depending on disk
capacity.

It's very important to understand
that.

And finally, autovacuum_work_mem,
set it explicitly, minus 1 sucks in all

senses.

It's just very hard to deal with.

I like

Michael: that.

I like setting it explicitly, even
if you plan to keep it the

same as your maintenance_work_mem
now.

Nikolay: Yes, like freeze it before
upgrading to 17 for example.

Or before touching number of autovacuum
workers.

Just make it explicit so to simplify
math analysis including

with AI so it won't jump additional
like mind hop.

Michael: And just avoiding that
future change where somebody

wants to increase maintenance_work_mem
and doesn't think

Nikolay: at

Michael: that moment about
autovacuuming.

Nikolay: Because these are 2 separate
things.

They operational, they are very
separate.

Indexing and vacuuming, autovacuuming.

Yeah, and that's it.

These 4 areas for rough tuning,
it's enough.

It should become bloat situation
much better, but there are also

blockers.

If You can tune a lot, tune everything
perfectly, but if you

have some xmin horizon blocker,
and we have episode about xmin horizon,

then it won't help.

Bloat will accumulate anyway.

Michael: Yeah.

Great.

All right.

Nikolay: Good.

Yeah.

Michael: Nice one, Nik.

Thanks so much.

Nikolay: Yeah, I hope it was helpful.

See you next time.

Michael: See you next time.