Subject: Re: New GUC autovacuum_max_threshold ?

From: Joe Conway <[email-masked]>
Date: Fri, 26 Apr 2024 09:40:05 -0400

On 4/26/24 09:31, Robert Haas wrote:
> On Fri, Apr 26, 2024 at 9:22 AM Joe Conway <[email-masked]> wrote:
>> Although I don't think 500000 is necessarily too small. In my view,
>> having autovac run very quickly, even if more frequently, provides an
>> overall better user experience.
> 
> Can you elaborate on why you think that? I mean, to me, that's almost
> equivalent to removing autovacuum_vacuum_scale_factor entirely,
> because only for very small tables will that calculation produce a
> value lower than 500k.

If I understood Nathan's proposed calc, for small tables you would still 
get (thresh + sf * numtuples). Once that number exceeds the new limit 
parameter, then the latter would kick in. So small tables would retain 
the current behavior and large enough tables would be clamped.

> We might need to try to figure out some test cases here. My intuition
> is that this is going to vacuum large tables insanely aggressively.

It depends on workload to be sure. Just because a table is large, it 
doesn't mean that dead rows are generated that fast.

Admittedly it has been quite a while since I looked at all this that 
closely, but if A/V runs on some large busy table for a few milliseconds 
once every few minutes, that is far less disruptive than A/V running for 
tens of seconds once every few hours or for minutes ones every few days 
-- or whatever. The key thing to me is the "few milliseconds" runtime. 
The short duration means that no one notices an impact, and the longer 
duration almost guarantees that an impact will be felt.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

---

From: Robert Haas <[email-masked]>
Date: Fri, 26 Apr 2024 10:12:48 -0400

> the current behavior and large enough tables would be clamped.

Right. But with a 500k threshold, "large enough" is not very large at
all. The default scale factor is 20%, so the crossover point is at 2.5
million tuples. That's pgbench scale factor 25, which is a 320MB
table.

> doesn't mean that dead rows are generated that fast.

That is true, as far as it goes.

> duration almost guarantees that an impact will be felt.

Sure, I mean, I totally agree with that, but how is a vacuum on a
large table going to run for milliseconds? If it can skip index
vacuuming, sure, then it's quick, because it only needs to scan the
heap pages that are not all-visible. But as soon as index vacuuming is
triggered, it's going to take a while. You can't afford to trigger
that constantly.

Let's compare the current situation to the situation post-patch with a
cap of 500k. Consider a table 1024 times larger than the one I
mentioned above, so pgbench scale factor 25600, size on disk 320GB.
Currently, that table will be vacuumed for bloat when the number of
dead tuples exceeds 20% of the table size, because that's the default
value of autovacuum_vacuum_scale_factor. The table has 2.56 billion
tuples, so that means that we're going to vacuum it when there are
more than 510 million dead tuples. Post-patch, we will vacuum when we
have 500 thousand dead tuples. Suppose a uniform workload that slowly
updates rows in the table. If we were previously autovacuuming the
table once per day (1440 minutes) we're now going to try to vacuum it
almost every minute (1440 minutes / 1024 = 84 seconds).

Unless I'm missing something major, that's completely bonkers. It
might be true that it would be a good idea to vacuum such a table more
often than we do at present, but there's no shot that we want to do it
that much more often. The pgbench_accounts_pkey index will, I believe,
be on the order of 8-10GB at that scale. We can't possibly want to
incur that much extra I/O every minute, and I don't think it's going
to finish in milliseconds, either.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Wed, 1 May 2024 18:19:03 +0000

I've been following this discussion and would like to add my
2 cents.

> that much more often. 

This is really an important point.

Too small of a threshold and a/v will constantly be vacuuming a fairly large 
and busy table with many indexes. 

If the threshold is large, say 100 or 200 million, I question if you want autovacuum 
to be doing the work of cleanup here?  That long of a period without a autovacuum 
on a table means there maybe something  misconfigured in your autovacuum settings. 

At that point aren't you just better off performing a manual vacuum and
taking advantage of parallel index scans?

Regards,

Sami Imseih
Amazon Web Services (AWS)

---

From: Robert Haas <[email-masked]>
Date: Wed, 1 May 2024 14:50:57 -0400

> taking advantage of parallel index scans?

As far as that last point goes, it would be good if we taught
autovacuum about several things it doesn't currently know about;
parallelism is one. IMHO, it's probably not the most important one,
but it's certainly on the list. I think, though, that we should
confine ourselves on this thread to talking about what the threshold
ought to be.

And as far as that goes, I'd like you - and others - to spell out more
precisely why you think 100 or 200 million tuples is too much. It
might be, or maybe it is in some cases but not in others. To me,
that's not a terribly large amount of data. Unless your tuples are
very wide, it's a few tens of gigabytes. That is big enough that I can
believe that you *might* want autovacuum to run when you hit that
threshold, but it's definitely not *obvious* to me that you want
autovacuum to run when you hit that threshold.

To make that concrete: If the table is 10TB, do you want to vacuum to
reclaim 20GB of bloat? You might be vacuuming 5TB of indexes to
reclaim 20GB of heap space - is that the right thing to do? If yes,
why?

I do think it's interesting that other people seem to think we should
be vacuuming more often on tables that are substantially smaller than
the ones that seem like a big problem to me. I'm happy to admit that
my knowledge of this topic is not comprehensive and I'd like to learn
from the experience of others. But I think it's clearly and obviously
unworkable to multiply the current frequency of vacuuming for large
tables by a three or four digit number. Possibly what we need here is
something other than a cap, where, say, we vacuum a 10GB table twice
as often as now, a 100GB table four times as often, and a 1TB table
eight times as often. Or whatever the right answer is. But we can't
just pull numbers out of the air like that: we need to be able to
justify our choices. I think we all agree that big tables need to be
vacuumed more often than the current formula does, but we seem to be
rather far apart on the values of "big" and "more".

---

From: David Rowley <[email-masked]>
Date: Thu, 2 May 2024 14:02:46 +1200

> almost every minute (1440 minutes / 1024 = 84 seconds).

I've not checked your maths, but if that's true, that's not going to work.

I think there are fundamental problems with the parameters that drive
autovacuum that need to be addressed before we can consider a patch
like this one.

Here are some of the problems that I know about:

1. Autovacuum has exactly zero forward vision and operates reactively
rather than proactively.  This "blind operating" causes tables to
either not need vacuumed or suddenly need vacuumed without any
consideration of how busy autovacuum is at that current moment.
2. There is no prioritisation for the order in which tables are autovacuumed.
3. With the default scale factor, the larger a table becomes, the more
infrequent the autovacuums.
4. Autovacuum is more likely to trigger when the system is busy
because more transaction IDs are being consumed and there is more DML
occurring. This results in autovacuum having less work to do during
quiet periods when there are more free resources to be doing the
vacuum work.

In my opinion, the main problem with Frédéric's proposed GUC/reloption
is that it increases the workload that autovacuum is responsible for
and, because of #2, it becomes more likely that autovacuum works on
some table that isn't the highest priority table to work on which can
result in autovacuum starvation of tables that are more important to
vacuum now.

I think we need to do a larger overhaul of autovacuum to improve
points 1-4 above.  I also think that there's some work coming up that
might force us into this sooner than we think.  As far as I understand
it, AIO will break vacuum_cost_page_miss because everything (providing
IO keeps up) will become vacuum_cost_page_hit. Maybe that's not that
important as that costing is quite terrible anyway.

Here's a sketch of an idea that's been in my head for a while:

Round 1:
1a) Give autovacuum forward vision (#1 above) and instead of vacuuming
a table when it (atomically) crosses some threshold, use the existing
scale_factors and autovacuum_freeze_max_age to give each table an
autovacuum "score", which could be a number from 0-100, where 0 means
do nothing and 100 means nuclear meltdown.  Let's say a table gets 10
points for the dead tuples meeting the current scale_factor and maybe
an additional point for each 10% of proportion the size of the table
is according to the size of the database (gives some weight to space
recovery for larger tables).  For relfrozenxid, make the score the
maximum of dead tuple score vs the percentage of the age(relfrozenxid)
is to 2 billion. Use a similar maximum score calc for age(relminmxid)
2 billion.
1b) Add a new GUC that defines the minimum score a table must reach
before autovacuum will consider it.
1c) Change autovacuum to vacuum the tables with the highest scores first.

Round 2:
2a) Have autovacuum monitor the score of the highest scoring table
over time with buckets for each power of 2 seconds in history from
now. Let's say 20 buckets, about 12 days of history. Migrate scores
into older buckets to track the score over time.
2b) Have autovacuum cost limits adapt according to the history so that
if the maximum score of any table is trending upwards, that autovacuum
speeds up until the score buckets trend downwards towards the present.
2c) Add another GUC to define the minimum score that autovacuum will
be "proactive". Must be less than the minimum score to consider
autovacuum (or at least, ignored unless it is.).  This GUC would not
cause an autovacuum speedup due to 2b) as we'd only consider tables
which meet the GUC added in 1b) in the score history array in 2a).
This stops autovacuum running faster than autovacuum_cost_limit when
trying to be proactive.

While the above isn't well a well-baked idea. The exact way to
calculate the scores isn't well thought through, certainly. However, I
do think it's an idea that we should consider and improve upon.  I
believe 2c) helps solve the problem of large tables becoming bloated
as autovacuum could get to these sooner when the workload is low
enough for it to run proactively.

I think we need at least 1a) before we can give autovacuum more work
to do, especially if we do something like multiply its workload by
1024x, per your comment above.

David

---

From: Frédéric Yhuel <[email-masked]>
Date: Thu, 2 May 2024 08:44:26 +0200

Le 01/05/2024 à 20:50, Robert Haas a écrit :
> Possibly what we need here is
> eight times as often. Or whatever the right answer is.

IMO, it would make more sense. So maybe something like this:

vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples, 
vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000);

(it could work to compute a score, too, like in David's proposal)

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Thu, 2 May 2024 16:01:23 +0000

> autovacuum to run when you hit that threshold.

Vacuuming the heap alone gets faster the more I do it, thanks to the 
visibility map. However, the more indexes I have, and the larger ( in the TBs),
the indexes become, autovacuum workers will be 
monopolized vacuuming these indexes.

At 500k tuples, I am constantly vacuuming large indexes
and monopolizing autovacuum workers. At 100 or 200
million tuples, I will also monopolize autovacuum workers
as they vacuums indexes for many minutes or hours. 

At 100 or 200 million, the monopolization will occur less often, 
but it will still occur leading an operator to maybe have to terminate
the autovacuum an kick of a manual vacuum. 

I am not convinced a new tuple based threshold will address this, 
but I may also may be misunderstanding the intention of this GUC.

> reclaim 20GB of heap space - is that the right thing to do? If yes,

No, I would not want to run autovacuum on 5TB indexes to reclaim 
a small amount of bloat.

Regards,

Sami

---

From: Robert Haas <[email-masked]>
Date: Tue, 7 May 2024 10:31:00 -0400

> quiet periods when there are more free resources to be doing the

I agree with all of these points. For a while now, I've been thinking
that we really needed a prioritization scheme, so that we don't waste
our time on low-priority tasks when there are high-priority tasks that
need to be completed. But lately I've started to think that what
matters most is the rate at which autovacuum work is happening
overall. I feel like prioritization is mostly going to matter when
we're not keeping up, and I think the primary goal should be to keep
up. I think we could use the same data to make both decisions -- if
autovacuum were proactive rather than reactive, that would mean that
we know something about what is going to happen in the future, and I
think that data could be used both to decide whether we're keeping up,
and also to prioritize. But if I had to pick a first target, I'd
forget about trying to make things happen in the right order and just
try to make sure we get all the things done.

> 1024x, per your comment above.

I guess I view it differently. It seems to me that right now, we're
not vacuuming large tables often enough. We should fix that,
independently of anything else. If the result is that small and medium
sized tables get vacuumed less often, then that just means there were
never enough resources to go around in the first place. We haven't
taken a system that was working fine and broken it: we've just moved
the problem from one category of tables (the big ones) to a different
category of tables. If the user wants to solve that problem, they need
to bump up the cost limit or add hardware. I don't see that we have
any particular reason to believe such users will be worse off on
average than they are today. On the other hand, users who do have a
sufficiently high cost limit and enough hardware will be better off,
because we'll start doing all the vacuuming work that needs to be done
instead of only some of it.

Now, if we start vacuuming any class of table whatsoever 1024x as
often as we do today, we are going to lose. But that would still be
true even if we did everything on your list. Large tables need to be
vacuumed more frequently than we now do, but not THAT much more
frequently. Any system that produces that result is just using a wrong
algorithm, or wrong constants, or something. Even if all the necessary
resources are available, nobody is going to thank us for vacuuming
gigantic tables in a tight loop. The problem with such a large
increase is not that we don't have prioritization, but that such a
large increase is fundamentally the wrong thing to do. On the other
hand, I think a more modest increase is the right thing to do, and I
think it's the right thing to do whether we have prioritization or
not.

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 7 May 2024 16:17:02 -0500

> think it's the right thing to do whether we have prioritization or

This is about how I feel, too.  In any case, I +1'd a higher default
because I think we need to be pretty conservative with these changes, at
least until we have a better prioritization strategy.  While folks may opt
to set this value super low, I think that's more likely to lead to some
interesting secondary effects.  If the default is high, hopefully these
secondary effects will be minimized or avoided.

-- 
Nathan Bossart

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Wed, 8 May 2024 17:30:44 +0000

> secondary effects will be minimized or avoided.

There is also an alternative of making this GUC -1 by default, which
means it has not effect and any value larger will be used in the threshold
calculation of autovacuunm. A user will have to be careful not to set it too low, 
but that is going to be a concern either way.

This idea maybe worth considering as it does not change the default
behavior of the autovac threshold calculation, and if a user has cases in 
which they have many tables with a few billion tuples that they wish to 
see autovacuumed more often, they now have a GUC to make 
that possible and potentially avoid per-table threshold configuration.

Also, I think coming up with a good default will be challenging,
and perhaps this idea is a good middle ground.

Regards,

Sami

---

From: Robert Haas <[email-masked]>
Date: Thu, 9 May 2024 10:58:56 -0400

> but that is going to be a concern either way.

Personally, I'd much rather ship it with a reasonable default. If we
ship it disabled, most people won't end up using it at all, which
sucks, and those who do will be more likely to set it to a ridiculous
value, which also sucks. If we ship it with a value that has a good
chance of being within 2x or 3x of the optimal value on a given user's
system, then a lot more people will benefit from it.

> and perhaps this idea is a good middle ground.

Maybe. I freely admit that I don't know exactly what the optimal value
is here, and I think there is some experimentation that is needed to
try to get some better intuition there. At what table size does the
current system actually result in too little vacuuming, and how can we
demonstrate that? Does the point at which that happens depend more on
the table size in gigabytes, or more on the number of rows? These are
things that someone can research and about which they can present
data.

As I see it, a lot of the lack of agreement up until now is people
just not understanding the math. Since I think I've got the right idea
about the math, I attribute this to other people being confused about
what is going to happen and would tend to phrase it as: some people
don't understand how catastrophically bad it will be if you set this
value too low. However, another possibility is that it is I who am
misunderstanding the math. In that case, the correct phrasing is
probably something like: Robert wants a completely useless and
worthless value for this parameter that will be of no help to anyone.
Regardless, at least some of us are confused. If we can reduce that
confusion, then people's ideas about what values for this parameter
might be suitable should start to come closer together.

I tend to feel like the disagreement here is not really about whether
it's a good idea to increase the frequency of vacuuming on large
tables by three orders of magnitude compared to what we do now, but
rather than about whether that's actually going to happen.

---

From: Frédéric Yhuel <[email-masked]>
Date: Mon, 13 May 2024 17:14:53 +0200

Le 09/05/2024 à 16:58, Robert Haas a écrit :
> value too low.

FWIW, I do agree with your math. I found your demonstration convincing. 
500000 was selected with the wet finger.

Using the formula I suggested earlier:

your table of 2.56 billion tuples will be vacuumed if there are
more than 10 million dead tuples (every 28 minutes).

If we want to stick with the simple formula, we should probably choose a 
very high default, maybe 100 million, as you suggested earlier.

However, it would be nice to have the visibility map updated more 
frequently than every 100 million dead tuples. I wonder if this could be 
decoupled from the vacuum process?

---

From: Robert Haas <[email-masked]>
Date: Mon, 13 May 2024 16:22:53 -0400

> 500000 was selected with the wet finger.

Good to know.

> more than 10 million dead tuples (every 28 minutes).

Yeah, so that is about 50x what we do now (twice an hour vs. once a
day). While that's a lot more reasonable than the behavior that we'd
get from a 500k hard cap (every 84 seconds), I suspect it's still too
aggressive.

I find these things much easier to reason about in gigabytes than in
time units. In that example, the table was 320GB and was getting
vacuumed after accumulating 64GB of bloat. That seems like a lot. It
means that the table can grow from 320GB all the way up until 384GB
before we even think about starting to vacuum it, and then we might
not start right away, depending on resource availability, and we may
take some time to finish, possibly considerable time, depending on the
number and size of indexes and the availability of I/O resources. So
actually the table might very plausibly be well above 400GB before we
get done processing it, or potentially even more. I think that's not
aggressive enough.

But how much would we like to push that 64GB of bloat number down for
a table of this size? I would argue that if we're vacuuming the table
when it's only got 1GB of bloat, or 2GB of bloat, that seems
excessive. Unless the system is very lightly loaded and has no
long-running transactions at all, we're unlikely to be able to vacuum
aggressively enough to keep a 320GB table at a size of 321GB or 322GB.
Without testing or doing any research, I'm going to guess that a
realistic number is probably in the range of 10-20GB of bloat. If the
table activity is very light, we might be able to get it even lower,
like say 5GB, but the costs ramp up very quickly as you push the
vacuuming threshold down. Also, if the table accumulates X amount of
bloat during the time it takes to run one vacuum, you can never
succeed in limiting bloat to a value less than X (and probably more
like 1.5*X or 2*X or something).

So without actually trying anything, which I do think somebody should
do and report results, my guess is that for a 320GB table, you'd like
to multiply the vacuum frequency by a value somewhere between 3 and
10, and probably much closer to 3 than to 10. Maybe even less than 3.
Not sure exactly. Like I say, I think someone needs to try some
different workloads and database sizes and numbers of indexes, and try
to get a feeling for what actually works well in practice.

> decoupled from the vacuum process?

Yes, but if a page has had any non-HOT updates, it can't become
all-visible again without vacuum. If it has had only HOT updates, then
a HOT-prune could make it all-visible. I don't think we do that
currently, but I think in theory we could.

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 17 Jun 2024 22:06:59 -0500

I didn't see a commitfest entry for this, so I created one to make sure we
don't lose track of this:

	https://commitfest.postgresql.org/48/5046/

-- 
nathan

---

From: Frédéric Yhuel <[email-masked]>
Date: Tue, 18 Jun 2024 12:36:42 +0200

> 	https://commitfest.postgresql.org/48/5046/

OK thanks!

By the way, I wonder if there were any off-list discussions after 
Robert's conference at PGConf.dev (and I'm waiting for the video of the 
conf).

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 7 Aug 2024 16:39:57 -0500

I've attached a new patch to show roughly what I think this new GUC should
look like.  I'm hoping this sparks more discussion, if nothing else.

On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
> By the way, I wonder if there were any off-list discussions after Robert's
> conference at PGConf.dev (and I'm waiting for the video of the conf).

I don't recall any discussions about this idea, but Robert did briefly
mention it in his talk [0].

[0] https://www.youtube.com/watch?v=RfTD-Twpvac

-- 
nathan
>From ccfaee370835d755b663357bf139fea729a5f454 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v2 1/1] autovacuum_max_threshold

---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 11 +++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.c                   |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 76 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a1a1d58a43..10855b0e19 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8615,6 +8615,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+      <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>autovacuum_max_threshold</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum number of updated or deleted tuples needed to
+        trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+        the value calculated with
+        <varname>autovacuum_vacuum_threshold</varname> and
+        <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+        100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+        maximum number of updated or deleted tuples that will trigger a
+        <command>VACUUM</command> operation.  This parameter can only be set in
+        the <filename>postgresql.conf</filename> file or on the server command
+        line; but the setting can be overridden for individual tables by
+        changing storage parameters.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
       <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 93b3f664f2..19b5ea7421 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1596,6 +1596,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 49fd35bfc5..c18ee4c497 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-1, 0, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 4e4a0ccbef..d43d48abc8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -119,6 +119,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2887,6 +2888,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2925,6 +2928,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2966,6 +2970,10 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3039,6 +3047,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c0a52cdcc3..9aae183786 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3403,6 +3403,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9ec9f97e92..e7cec694f3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -663,6 +663,8 @@
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 024469474d..f3ce76e23d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1295,6 +1295,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1311,6 +1312,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index cae1e8b329..efbab7bfa9 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -32,6 +32,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 8700204953..bcc6e2607b 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.3 (Apple Git-146)