On Thu, 2020-03-05 at 19:40 +1300, David Rowley wrote:
> I think we need to move forward with doing something to cope with
> INSERT-only tables not being auto-vacuumed.
>
> I think the patch you have is something along the lines to what I'd
> have imagined we should do. However, there are a few things that I'd
> do a different way.
Thanks for the review, and that's good news.
> 1. I'd go for 2 new GUCs and reloptions.
> autovacuum_vacuum_insert_threshold (you're currently calling this
> autovacuum_vacuum_insert_limit. I don't see why the word "limit" is
> relevant here). The other GUC I think should be named
> autovacuum_vacuum_insert_scale_factor and these should work exactly
> the same way as autovacuum_vacuum_threshold and
> autovacuum_vacuum_scale_factor, but be applied in a similar way to the
> vacuum settings, but only be applied after we've checked to ensure the
> table is not otherwise eligible to be vacuumed.
Yes, "threshold" is better than "limit" I have renamed the GUC and
the reloption.
I disagree about the scale_factor (and have not added it to the
updated version of the patch). If we have a scale_factor, then the
time between successive autovacuum runs would increase as the table
gets bigger, which defeats the purpose of reducing the impact of each
autovacuum run.
Since autovacuum skips pages where it has nothing to do, we can expect
that runs on a large table won't be much more expensive than runs on a
smaller table, right?
> 3. The name "insert_only" does not seem the best for the new boolean
> variable that you're using in various places. That name seems to be
> too closely related to our current intended use case. Maybe
> skip_index_cleanup is more to the point.
I originally called the variable "skip_indexes", but when I decided
that such vacuum runs also aggressively freeze the table, I thought
that the name was misleading and renamed it.
I won't put up a fight about this, though.
> 4. Are you sure you mean "Maximum" here? Isn't it the minimum? At
> least it will be once you add both options. Otherwise, I think Maximum
> is not the correct word. Perhaps "The threshold"
>
> + {"autovacuum_vacuum_insert_limit", PGC_SIGHUP, AUTOVACUUM,
> + gettext_noop("Maximum number of tuple inserts prior to vacuum."),
> + NULL
> + },
I had actually been debating whether to use "maximum" or "minimum".
I realize now that this strange uncertainty stems from the fact that
there is (yet) only a single parameter to govern this.
The updated patch desctibes the GUC as
"Number of tuple inserts prior to vacuum."
> 5. I think the new field in this struct should be named
> vacuum_insert_threshold
>
> @@ -252,6 +252,7 @@ typedef struct AutoVacOpts
> {
> bool enabled;
> int vacuum_threshold;
> + int vacuum_ins_limit;
I agree as above, renamed.
> 6. Are you sure you meant to default this to 50?
>
> index e58e4788a8..9d96d58ed2 100644
> --- a/src/backend/utils/misc/postgresql.conf.sample
> +++ b/src/backend/utils/misc/postgresql.conf.sample
> @@ -598,6 +598,8 @@
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row updates before
> # vacuum
> +#autovacuum_vacuum_insert_limit = 50 # max number of row inserts before
> + # vacuum
>
> Seems excessive given there's no scale factor in the current patch.
That was a mistake.
I chose 10000000 as the actual default value, but forgot to put the
same value into "postgresql.conf".
> 7. I know you know.... missing docs... would be good to get those.
The updated version of the patch has documentation.
I just wanted to get a feeling if my patch would be killed cold before
I went to the effort of writing documentation.
> 8. Should we care when setting the insert counter back to 0 if
> auto-vacuum has skipped pages?
Since this is only an approximate value anyway, I decided not to care.
I don't know if that is acceptable.
> 9. You should add a new column to the pg_stat_all_tables view to allow
> visibility of the insert since the last vacuum. The column should be
> named n_ins_since_vacuum. This seems like the best combination of
> n_mod_since_analyze and n_tup_ins.
Done.
> 10. I'm slightly worried about the case where we don't quite trigger a
> normal vacuum but trigger a vacuum due to INSERTs then skip cleaning
> up the indexes but proceed to leave dead index entries causing indexes
> to become bloated. It does not seem impossible that given the right
> balance of INSERTs and UPDATE/DELETEs that this could happen every
> time and the indexes would just become larger and larger.
I understand.
This might particularly be a problem with larger tables, where
a normal autovacuum is rare because of the scale_factor.
Perhaps we can take care of the problem by *not* skipping index
cleanup if "changes_since_analyze" is substantially greater than 0.
What do you think?
> 11. We probably do also need to debate if we want this on or off by
> default. I'd have leaned towards enabling by default if I'd not
> personally witnessed the fact that people rarely* increase auto-vacuum
> to run faster than the standard cost settings. I've seen hundreds of
> servers over the years with all workers busy for days on something
> they'll never finish quickly enough. We increased those settings 10x
> in PG12, so there will be fewer people around suffering from that now,
> but even after having reduced the vacuum_cost_delay x10 over the PG11
> settings, it's by no means fast enough for everyone. I've mixed
> feelings about giving auto-vacuum more work to do for those people, so
> perhaps the best option is to keep this off by default so as not to
> affect the people who don't tune auto-vacuum. They'll just suffer the
> pain all at once when they hit max freeze age instead of more
> gradually with the additional load on the workers. At least adding
> this feature gives the people who do tune auto-vacuum some ability to
> handle read-only tables in some sane way.
>
> An alternative way of doing it would be to set the threshold to some
> number of million tuples and set the scale_factor to 0.2 so that it
> only has an effect on larger tables, of which generally people only
> have a smallish number of.
Yes, I think that disabling this by default defeats the purpose.
Knowledgeable people can avoid the problem today by manually scheduling
VACUUM runs on insert-only tables, and the functionality proposed here
is specifically to improve the lives of people who don't know enough
to tune autovacuum.
My original idea was to set the threshold to 10 million and have no scale
factor.
Updated patch attached.
Yours,
Laurenz Albe
From 4eb99e65fadb8a2180d405d1fa5081205eff2d54 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Thu, 5 Mar 2020 14:53:43 +0100
Subject: [PATCH] Autovacuum tables that have received only inserts
Add "autovacuum_vacuum_insert_threshold" GUC and reloption
with a default value of 10000000.
Any table that has received more inserts since it was
last vacuumed (and that is not vacuumed for another
reason) will be autovacuumed with the options
"index_cleanup off, freeze".
This avoids the known problem that insert-only tables
are never autovacuumed until they need to have their
anti-wraparound autovacuum, which then can be massive
and disruptive.
To track the number of inserts since the last vacuum,
introduce a StatTabEntry "inserts_since_vacuum" that
gets reset to 0 after a vacuum. This value is available
in "pg_stat_*_tables" as "n_ins_since_vacuum".
---
doc/src/sgml/config.sgml | 20 +++++++++
doc/src/sgml/maintenance.sgml | 10 +++++
doc/src/sgml/monitoring.sgml | 5 +++
doc/src/sgml/ref/create_table.sgml | 15 +++++++
src/backend/access/common/reloptions.c | 11 +++++
src/backend/catalog/system_views.sql | 1 +
src/backend/postmaster/autovacuum.c | 41 +++++++++++++++----
src/backend/postmaster/pgstat.c | 5 +++
src/backend/utils/adt/pgstatfuncs.c | 16 ++++++++
src/backend/utils/misc/guc.c | 9 ++++
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/bin/psql/tab-complete.c | 2 +
src/include/catalog/pg_proc.dat | 5 +++
src/include/pgstat.h | 1 +
src/include/postmaster/autovacuum.h | 1 +
src/include/utils/rel.h | 1 +
16 files changed, 138 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c1128f89ec..32016c7403 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7244,6 +7244,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</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>
+ <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of inserted tuples needed to trigger a
+ <command>VACUUM</command> in any one table.
+ The default is 10000000 tuples.
+ 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 table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
<term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
<indexterm>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index ec8bdcd7a4..9d1ef87db2 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -788,6 +788,16 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple
since the last vacuum are scanned.
</para>
+ <para>
+ Tables that have received more than
+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>
+ inserts since they were last vacuumed and are not eligible for vacuuming
+ based on the above criteria will be vacuumed to reduce the impact of a future
+ anti-wraparound vacuum run.
+ Such a vacuum will aggressively freeze tuples, and it will not clean up dead
+ index tuples.
+ </para>
+
<para>
For analyze, a similar condition is used: the threshold, defined as:
<programlisting>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 87586a7b06..05fd6260b7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2837,6 +2837,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<entry><type>bigint</type></entry>
<entry>Estimated number of rows modified since this table was last analyzed</entry>
</row>
+ <row>
+ <entry><structfield>n_ins_since_vacuum</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Estimated number of rows inserted since this table was last vacuumed</entry>
+ </row>
<row>
<entry><structfield>last_vacuum</structfield></entry>
<entry><type>timestamp with time zone</type></entry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4a2b6f0dae..243e674b09 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1460,6 +1460,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="reloption-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
+ <term><literal>autovacuum_vacuum_insert_threshold</literal>, <literal>toast.autovacuum_vacuum_insert_threshold</literal> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="reloption-autovacuum-vauum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
<term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>float4</type>)
<indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 5325dd3f61..5a7cfa980b 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -233,6 +233,15 @@ static relopt_int intRelOpts[] =
},
-1, 0, INT_MAX
},
+ {
+ {
+ "autovacuum_vacuum_insert_threshold",
+ "Number of tuple inserts prior to vacuum",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ -1, 0, INT_MAX
+ },
{
{
"autovacuum_analyze_threshold",
@@ -1501,6 +1510,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_vacuum_insert_threshold", RELOPT_TYPE_INT,
+ offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_threshold)},
{"autovacuum_vacuum_cost_limit", RELOPT_TYPE_INT,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f681aafcf9..b2729f1771 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -573,6 +573,7 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
+ pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e3a43d3296..11f2bbd0a7 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -116,6 +116,7 @@ int autovacuum_max_workers;
int autovacuum_work_mem = -1;
int autovacuum_naptime;
int autovacuum_vac_thresh;
+int autovacuum_vac_ins_thresh;
double autovacuum_vac_scale;
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
@@ -330,7 +331,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ bool *insert_only);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -2056,6 +2058,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ bool insert_only;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2096,7 +2099,8 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &insert_only);
/* Relations that need work are added to table_oids */
if (dovacuum || doanalyze)
@@ -2149,6 +2153,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ bool insert_only;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2179,7 +2184,8 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &insert_only);
/* ignore analyze for toast tables */
if (dovacuum)
@@ -2780,6 +2786,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
bool wraparound;
+ bool insert_only;
AutoVacOpts *avopts;
/* use fresh stats */
@@ -2816,7 +2823,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &insert_only);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
@@ -2885,11 +2893,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
(dovacuum ? VACOPT_VACUUM : 0) |
(doanalyze ? VACOPT_ANALYZE : 0) |
(!wraparound ? VACOPT_SKIP_LOCKED : 0);
- tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT;
+ tab->at_params.index_cleanup = insert_only ? VACOPT_TERNARY_DISABLED : VACOPT_TERNARY_DEFAULT;
tab->at_params.truncate = VACOPT_TERNARY_DEFAULT;
/* As of now, we don't support parallel vacuum for autovacuum */
tab->at_params.nworkers = -1;
- tab->at_params.freeze_min_age = freeze_min_age;
+ tab->at_params.freeze_min_age = insert_only ? 0 : freeze_min_age;
tab->at_params.freeze_table_age = freeze_table_age;
tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age;
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
@@ -2961,7 +2969,8 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ bool *insert_only)
{
bool force_vacuum;
bool av_enabled;
@@ -2969,6 +2978,7 @@ relation_needs_vacanalyze(Oid relid,
/* constants from reloptions or GUC variables */
int vac_base_thresh,
+ vac_ins_thresh,
anl_base_thresh;
float4 vac_scale_factor,
anl_scale_factor;
@@ -3005,6 +3015,10 @@ relation_needs_vacanalyze(Oid relid,
? relopts->vacuum_threshold
: autovacuum_vac_thresh;
+ vac_ins_thresh = (relopts && relopts->vacuum_ins_threshold >= 0)
+ ? relopts->vacuum_ins_threshold
+ : autovacuum_vac_ins_thresh;
+
anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0)
? relopts->analyze_scale_factor
: autovacuum_anl_scale;
@@ -3023,6 +3037,9 @@ relation_needs_vacanalyze(Oid relid,
av_enabled = (relopts ? relopts->enabled : true);
+ /* process indexes by default */
+ *insert_only = false;
+
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age;
if (xidForceLimit < FirstNormalTransactionId)
@@ -3076,6 +3093,16 @@ relation_needs_vacanalyze(Oid relid,
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh);
*doanalyze = (anltuples > anlthresh);
+ /*
+ * If the number of inserted tuples exceeds the limit
+ * and no vacuum is necessary for other reasons,
+ * vacuum without cleaning up indexes.
+ */
+ if (!(*dovacuum) && vac_ins_thresh < tabentry->inserts_since_vacuum)
+ {
+ *dovacuum = true;
+ *insert_only = true;
+ }
}
else
{
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 462b4d7e06..78bd5cf90d 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -4791,6 +4791,7 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->n_live_tuples = 0;
result->n_dead_tuples = 0;
result->changes_since_analyze = 0;
+ result->inserts_since_vacuum = 0;
result->blocks_fetched = 0;
result->blocks_hit = 0;
result->vacuum_timestamp = 0;
@@ -5921,6 +5922,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
tabentry->n_live_tuples = tabmsg->t_counts.t_delta_live_tuples;
tabentry->n_dead_tuples = tabmsg->t_counts.t_delta_dead_tuples;
tabentry->changes_since_analyze = tabmsg->t_counts.t_changed_tuples;
+ tabentry->inserts_since_vacuum = tabmsg->t_counts.t_tuples_inserted;
tabentry->blocks_fetched = tabmsg->t_counts.t_blocks_fetched;
tabentry->blocks_hit = tabmsg->t_counts.t_blocks_hit;
@@ -5950,10 +5952,12 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
{
tabentry->n_live_tuples = 0;
tabentry->n_dead_tuples = 0;
+ tabentry->inserts_since_vacuum = 0;
}
tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;
tabentry->n_dead_tuples += tabmsg->t_counts.t_delta_dead_tuples;
tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples;
+ tabentry->inserts_since_vacuum += tabmsg->t_counts.t_tuples_inserted;
tabentry->blocks_fetched += tabmsg->t_counts.t_blocks_fetched;
tabentry->blocks_hit += tabmsg->t_counts.t_blocks_hit;
}
@@ -6187,6 +6191,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len)
tabentry->n_live_tuples = msg->m_live_tuples;
tabentry->n_dead_tuples = msg->m_dead_tuples;
+ tabentry->inserts_since_vacuum = 0;
if (msg->m_autovacuum)
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7e6a3c1774..aac248a86e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -196,6 +196,22 @@ pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
}
+Datum
+pg_stat_get_ins_since_vacuum(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->inserts_since_vacuum);
+
+ PG_RETURN_INT64(result);
+}
+
+
Datum
pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 464f264d9a..f692cefa03 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3063,6 +3063,15 @@ static struct config_int ConfigureNamesInt[] =
50, 0, INT_MAX,
NULL, NULL, NULL
},
+ {
+ {"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Number of tuple inserts prior to vacuum."),
+ NULL
+ },
+ &autovacuum_vac_ins_thresh,
+ 10000000, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
{
{"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Minimum number of tuple inserts, updates, or deletes prior to analyze."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e58e4788a8..8b6d0288f4 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -598,6 +598,8 @@
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
+#autovacuum_vacuum_insert_threshold = 10000000 # max number of row inserts before
+ # vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b6b08d0ccb..fea178dc97 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1071,6 +1071,7 @@ static const char *const table_storage_parameters[] = {
"autovacuum_multixact_freeze_table_age",
"autovacuum_vacuum_cost_delay",
"autovacuum_vacuum_cost_limit",
+ "autovacuum_vacuum_insert_threshold",
"autovacuum_vacuum_scale_factor",
"autovacuum_vacuum_threshold",
"fillfactor",
@@ -1085,6 +1086,7 @@ static const char *const table_storage_parameters[] = {
"toast.autovacuum_multixact_freeze_table_age",
"toast.autovacuum_vacuum_cost_delay",
"toast.autovacuum_vacuum_cost_limit",
+ "toast.autovacuum_vacuum_insert_threshold",
"toast.autovacuum_vacuum_scale_factor",
"toast.autovacuum_vacuum_threshold",
"toast.log_autovacuum_min_duration",
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..0527b785a6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5129,6 +5129,11 @@
proname => 'pg_stat_get_mod_since_analyze', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
prosrc => 'pg_stat_get_mod_since_analyze' },
+{ oid => '8872',
+ descr => 'statistics: number of tuples inserted since last vacuum',
+ proname => 'pg_stat_get_ins_since_vacuum', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_ins_since_vacuum' },
{ oid => '1934', descr => 'statistics: number of blocks fetched',
proname => 'pg_stat_get_blocks_fetched', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3a65a51696..7ccbb3191e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -646,6 +646,7 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter n_live_tuples;
PgStat_Counter n_dead_tuples;
PgStat_Counter changes_since_analyze;
+ PgStat_Counter inserts_since_vacuum;
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index d40ed55531..5798157874 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -32,6 +32,7 @@ extern int autovacuum_max_workers;
extern int autovacuum_work_mem;
extern int autovacuum_naptime;
extern int autovacuum_vac_thresh;
+extern int autovacuum_vac_ins_thresh;
extern double autovacuum_vac_scale;
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 44ed04dd3f..12d4c30146 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -252,6 +252,7 @@ typedef struct AutoVacOpts
{
bool enabled;
int vacuum_threshold;
+ int vacuum_ins_threshold;
int analyze_threshold;
int vacuum_cost_limit;
int freeze_min_age;
--
2.21.1
