Nik and Michael discuss autovacuum, including what it does, and the basics of why and how to tune it. Here are some links to things they mentioned: autovacuum https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM autovacuum configuration parameters https://www.postgresql.org/docs/current/runtime-config-vacuum.html#RUNTIME-CONFIG-AUTOVACUUM What’s Missing in Postgres? (our episode with Bruce Momjian) https://postgres.fm/episodes/what-s-missing-in-postgres pg_squeeze (our episode with Antonín Houska) https://postgres.fm/episodes/pg_squeeze My queries to monitor autovacuum (post by Laurenz Albe) https://www.cybertec-postgresql.com/en/monitor-autovacuum-my-queries/ Autovacuum Tuning Basics (post by Tomas Vondra, originally for 2nd Quadrant blog) https://www.enterprisedb.com/blog/autovacuum-tuning-basics Zero autovacuum_vacuum_cost_delay, Write Storms, and You (post by Jeremy Schneider) https://ardentperf.com/2026/04/12/zero-autovacuum_cost_delay-write-storms-and-you/ Our episode on long-running transactions / xmin horizon https://postgres.fm/episodes/long-running-transactions ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...