On Tue, 2020-03-10 at 13:53 +1300, David Rowley wrote:
> 1. Do we need to change documentation on freeze_min_age to mention
> that it does not apply in all cases? I'm leaning towards not changing
> this as `VACUUM FREEZE` is also an exception to this, which I don't
> see mentioned.
I agree with that. Too little documentation is bad, but too much of
it can also confuse and make it hard to find the needle in the haystack.
> 2. Perhaps the documentation in maintenance.sgml should mention that
> the table will be vacuumed with the equivalent of having
> vacuum_freeze_min_age = 0, instead of:
>
> "Such a vacuum will aggressively freeze tuples."
>
> aggressive is the wrong word here. We call it an aggressive vacuum if
> we disable page skipping, not for setting the vacuum_freeze_min_age to
> 0.
Agreed, see below.
> 3. The following DEBUG3 elog should be updated to include the new values:
>
> elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
> NameStr(classForm->relname),
> vactuples, vacthresh, anltuples, anlthresh);
Done.
> Someone might be confused at why auto-vacuum is running if you don't
> put those in.
>
> 4. This would be nicer if you swapped the order of the operands to the
> < condition and replaced the operator with >. That'll match the way it
> is done above.
>
> /*
> * If the number of inserted tuples exceeds the threshold and no
> * vacuum is necessary for other reasons, run an "insert-only" vacuum
> * that freezes aggressively.
> */
> if (!(*dovacuum) && vacinsthresh < tabentry->inserts_since_vacuum)
> {
> *dovacuum = true;
> *freeze_all = true;
> }
>
> It would also be nicer if you assigned the value of
> tabentry->inserts_since_vacuum to a variable, so as to match what the
> other code there is doing. That'll also make the change for #3 neater.
Changed that way.
> 5. The following text:
>
> A threshold similar to the above is calculated from
> <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> and
> <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>.
> Tables that have received more inserts than the calculated threshold
> since they were last vacuumed (and are not eligible for vacuuming for
> other reasons) will be vacuumed to reduce the impact of a future
> anti-wraparound vacuum run.
>
> I think "... will be vacuumed with the equivalent of having <xref
> linkend="guc-vacuum-freeze-min-age"/> set to <literal>0</literal>".
> I'm not sure we need to mention the reduction of impact to
> anti-wraparound vacuums.
Done like that.
I left in the explanation of the purpose of this setting.
Understanding the purpose of the GUCs will make it easier to tune them
correctly.
> 6. Please run the regression tests and make sure they pass. The
> "rules" test is currently failing due to the new column in
> "pg_stat_all_tables"
Oops, sorry. I ran pgindent, but forgot to re-run the regression tests.
Done.
Attached is V5, which also fixes the bug discovered my Masahiko Sawada.
He made an interesting suggestion which we should consider before committing.
Yours,
Laurenz Albe
From 83fa15ef51836ae07a235f6070637857c9c6a9c8 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Tue, 10 Mar 2020 19:58:39 +0100
Subject: [PATCH] Autovacuum tables that have received only inserts
Add "autovacuum_vacuum_insert_threshold" and
"autovacuum_vacuum_insert_scale_factor" GUC and reloption.
The default value for the threshold is 10000000.
The scale factor defaults to 0, which means that it is
effectively disabled, but it offers some flexibility
to tune the feature similar to other autovacuum knobs.
Any table that has received more inserts since it was
last vacuumed (and that is not vacuumed for another
reason) will be autovacuumed, freezing as many tuples as
possible.
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".
Author: Laurenz Albe, based on a suggestion from Darafei Praliaskouski
Reviewed-by: David Rowley, Justin Pryzby, Masahiko Sawada
Discussion: https://postgr.es/m/CAC8Q8t+j36G_bLF=+0imo6jgnwnlnwb1tujxujr-+x8zcct...@mail.gmail.com
---
doc/src/sgml/config.sgml | 41 ++++++++++++++
doc/src/sgml/maintenance.sgml | 12 ++++
doc/src/sgml/monitoring.sgml | 5 ++
doc/src/sgml/ref/create_table.sgml | 30 ++++++++++
src/backend/access/common/reloptions.c | 22 ++++++++
src/backend/catalog/system_views.sql | 1 +
src/backend/postmaster/autovacuum.c | 56 ++++++++++++++++---
src/backend/postmaster/pgstat.c | 5 ++
src/backend/utils/adt/pgstatfuncs.c | 16 ++++++
src/backend/utils/misc/guc.c | 20 +++++++
src/backend/utils/misc/postgresql.conf.sample | 4 ++
src/bin/psql/tab-complete.c | 4 ++
src/include/catalog/pg_proc.dat | 5 ++
src/include/pgstat.h | 1 +
src/include/postmaster/autovacuum.h | 2 +
src/include/utils/rel.h | 2 +
src/test/regress/expected/rules.out | 3 +
17 files changed, 220 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c1128f89ec..0ed1bb9d5e 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>
@@ -7285,6 +7305,27 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
+ <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_insert_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is 0.0, which means that the table size has no effect.
+ 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-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
<term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
<indexterm>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index ec8bdcd7a4..1d5833b26d 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -788,6 +788,18 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple
since the last vacuum are scanned.
</para>
+ <para>
+ A threshold similar to the above is calculated from
+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> and
+ <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>.
+ Tables that have received more inserts than the calculated threshold
+ since they were last vacuumed (and are not eligible for vacuuming for
+ other reasons) will be vacuumed with the equivalent of having
+ <xref linkend="guc-vacuum-freeze-min-age"/> set to <literal>0</literal>.
+ This is designed to reduce the impact of a future anti-wraparound
+ vacuum run.
+ </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..20acede7e3 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1475,6 +1475,36 @@ 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-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
+ <term><literal>autovacuum_vacuum_insert_scale_factor</literal>, <literal>toast.autovacuum_vacuum_insert_scale_factor</literal> (<type>float4</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_scale_factor</varname> </primary>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="reloption-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
<term><literal>autovacuum_analyze_threshold</literal> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 5325dd3f61..245b0af535 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",
+ "Minimum number of tuple inserts prior to vacuum",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ -1, 0, INT_MAX
+ },
{
{
"autovacuum_analyze_threshold",
@@ -385,6 +394,15 @@ static relopt_real realRelOpts[] =
},
-1, 0.0, 100.0
},
+ {
+ {
+ "autovacuum_vacuum_insert_scale_factor",
+ "Number of tuple inserts prior to vacuum as a fraction of reltuples",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ -1, 0.0, 100.0
+ },
{
{
"autovacuum_analyze_scale_factor",
@@ -1501,6 +1519,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,
@@ -1525,6 +1545,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_delay)},
{"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_scale_factor)},
+ {"autovacuum_vacuum_insert_scale_factor", RELOPT_TYPE_REAL,
+ offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_scale_factor)},
{"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_scale_factor)},
{"user_catalog_table", RELOPT_TYPE_BOOL,
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..1a3ca4b097 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -117,6 +117,8 @@ int autovacuum_work_mem = -1;
int autovacuum_naptime;
int autovacuum_vac_thresh;
double autovacuum_vac_scale;
+int autovacuum_vac_ins_thresh;
+double autovacuum_vac_ins_scale;
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
int autovacuum_freeze_max_age;
@@ -330,7 +332,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 *freeze_all);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -2056,6 +2059,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ bool freeze_all;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2096,7 +2100,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,
+ &freeze_all);
/* Relations that need work are added to table_oids */
if (dovacuum || doanalyze)
@@ -2149,6 +2154,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ bool freeze_all;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2179,7 +2185,8 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &freeze_all);
/* ignore analyze for toast tables */
if (dovacuum)
@@ -2780,6 +2787,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
bool wraparound;
+ bool freeze_all;
AutoVacOpts *avopts;
/* use fresh stats */
@@ -2816,7 +2824,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,
+ &freeze_all);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
@@ -2889,9 +2898,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
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 = freeze_all ? 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_min_age = freeze_all ? 0 : multixact_freeze_min_age;
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
@@ -2961,7 +2970,8 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ bool *freeze_all)
{
bool force_vacuum;
bool av_enabled;
@@ -2969,16 +2979,20 @@ relation_needs_vacanalyze(Oid relid,
/* constants from reloptions or GUC variables */
int vac_base_thresh,
+ vac_ins_base_thresh,
anl_base_thresh;
float4 vac_scale_factor,
+ vac_ins_scale_factor,
anl_scale_factor;
/* thresholds calculated from above constants */
float4 vacthresh,
+ vacinsthresh,
anlthresh;
/* number of vacuum (resp. analyze) tuples at this time */
float4 vactuples,
+ instuples,
anltuples;
/* freeze parameters */
@@ -3005,6 +3019,14 @@ relation_needs_vacanalyze(Oid relid,
? relopts->vacuum_threshold
: autovacuum_vac_thresh;
+ vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
+ ? relopts->vacuum_ins_scale_factor
+ : autovacuum_vac_ins_scale;
+
+ vac_ins_base_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 +3045,9 @@ relation_needs_vacanalyze(Oid relid,
av_enabled = (relopts ? relopts->enabled : true);
+ /* normal autovacuum shouldn't freeze aggressively */
+ *freeze_all = false;
+
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age;
if (xidForceLimit < FirstNormalTransactionId)
@@ -3059,9 +3084,11 @@ relation_needs_vacanalyze(Oid relid,
{
reltuples = classForm->reltuples;
vactuples = tabentry->n_dead_tuples;
+ instuples = tabentry->inserts_since_vacuum;
anltuples = tabentry->changes_since_analyze;
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+ vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
/*
@@ -3069,13 +3096,24 @@ relation_needs_vacanalyze(Oid relid,
* reset, because if that happens, the last vacuum and analyze counts
* will be reset too.
*/
- elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
+ elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
NameStr(classForm->relname),
- vactuples, vacthresh, anltuples, anlthresh);
+ vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh);
*doanalyze = (anltuples > anlthresh);
+
+ /*
+ * If the number of inserted tuples exceeds the threshold and no
+ * vacuum is necessary for other reasons, run an "insert-only" vacuum
+ * that freezes as many tuples as possible.
+ */
+ if (!(*dovacuum) && (instuples > vacinsthresh))
+ {
+ *dovacuum = true;
+ *freeze_all = 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..9f4f6a4272 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("Minimum 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."),
@@ -3510,6 +3519,17 @@ static struct config_real ConfigureNamesReal[] =
0.2, 0.0, 100.0,
NULL, NULL, NULL
},
+
+ {
+ {"autovacuum_vacuum_insert_scale_factor", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Number of tuple inserts prior to vacuum as a fraction of reltuples."),
+ NULL
+ },
+ &autovacuum_vac_ins_scale,
+ 0.0, 0.0, 100.0,
+ NULL, NULL, NULL
+ },
+
{
{"autovacuum_analyze_scale_factor", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e58e4788a8..04ae8784f8 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -598,9 +598,13 @@
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
+#autovacuum_vacuum_insert_threshold = 10000000 # min 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
+#autovacuum_vacuum_insert_scale_factor = 0.0 # fraction of table size before
+ # vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b6b08d0ccb..efc7b20659 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1071,6 +1071,8 @@ static const char *const table_storage_parameters[] = {
"autovacuum_multixact_freeze_table_age",
"autovacuum_vacuum_cost_delay",
"autovacuum_vacuum_cost_limit",
+ "autovacuum_vacuum_insert_scale_factor",
+ "autovacuum_vacuum_insert_threshold",
"autovacuum_vacuum_scale_factor",
"autovacuum_vacuum_threshold",
"fillfactor",
@@ -1085,6 +1087,8 @@ 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_scale_factor",
+ "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..7277effe03 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,8 @@ extern int autovacuum_work_mem;
extern int autovacuum_naptime;
extern int autovacuum_vac_thresh;
extern double autovacuum_vac_scale;
+extern int autovacuum_vac_ins_thresh;
+extern double autovacuum_vac_ins_scale;
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
extern int autovacuum_freeze_max_age;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 44ed04dd3f..476d859d81 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;
@@ -263,6 +264,7 @@ typedef struct AutoVacOpts
int log_min_duration;
float8 vacuum_cost_delay;
float8 vacuum_scale_factor;
+ float8 vacuum_ins_scale_factor;
float8 analyze_scale_factor;
} AutoVacOpts;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 634f8256f7..031005ac59 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1778,6 +1778,7 @@ pg_stat_all_tables| SELECT c.oid AS relid,
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,
@@ -2034,6 +2035,7 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.n_live_tup,
pg_stat_all_tables.n_dead_tup,
pg_stat_all_tables.n_mod_since_analyze,
+ pg_stat_all_tables.n_ins_since_vacuum,
pg_stat_all_tables.last_vacuum,
pg_stat_all_tables.last_autovacuum,
pg_stat_all_tables.last_analyze,
@@ -2077,6 +2079,7 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
pg_stat_all_tables.n_live_tup,
pg_stat_all_tables.n_dead_tup,
pg_stat_all_tables.n_mod_since_analyze,
+ pg_stat_all_tables.n_ins_since_vacuum,
pg_stat_all_tables.last_vacuum,
pg_stat_all_tables.last_autovacuum,
pg_stat_all_tables.last_analyze,
--
2.21.1
