On Thu, 2020-03-19 at 21:39 +1300, David Rowley wrote: > > According to my reckoning, that is the remaining objection to the patch > > as it is (with ordinary freezing behavior). > > > > How about a scale_factor od 0.005? That will be high enough for large > > tables, which seem to be the main concern here. > > I agree with that, however, I'd thought 0.01, just so we're still > close to having about 100 times less work to do for huge insert-only > tables when it comes to having to perform an anti-wraparound vacuum.
Fine with me. > > I am still sorry to see more proactive freezing go, which would > > reduce the impact for truly insert-only tables. > > After sleeping on it, here is one last idea. > > > > Granted, freezing with vacuum_freeze_min_age = 0 poses a problem > > for those parts of the table that will receive updates or deletes. > > But what if insert-triggered vacuum operates with - say - > > one tenth of vacuum_freeze_min_age (unless explicitly overridden > > for the table)? That might still be high enough not to needlessly > > freeze too many tuples that will still be modified, but it will > > reduce the impact on insert-only tables. > > I think that might be a bit too magical and may not be what some > people want. I know that most people won't set > autovacuum_freeze_min_age to 0 for insert-only tables, but we can at > least throw something in the documents to mention it's a good idea, > however, looking over the docs I'm not too sure the best place to note > that down. I was afraid that idea would be too cute to appeal. > I've attached a small fix which I'd like to apply to your v8 patch. > With that, and pending one final look, I'd like to push this during my > Monday (New Zealand time). So if anyone strongly objects to that, > please state their case before then. Thanks! I have rolled your edits into the attached patch v9, rebased against current master. Yours, Laurenz Albe
From 3ba4b572d82969bbb2af787d1bccc72f417ad3a0 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Thu, 19 Mar 2020 20:26:43 +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.01. Any table that has received more inserts since it was last vacuumed (and that is not vacuumed for another reason) will be autovacuumed. 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, Andres Freund Discussion: https://postgr.es/m/CAC8Q8t+j36G_bLF=+0imo6jgnwnlnwb1tujxujr-+x8zcct...@mail.gmail.com --- doc/src/sgml/config.sgml | 41 +++++++++++++++++++ doc/src/sgml/maintenance.sgml | 23 ++++++++--- 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 | 22 ++++++++-- 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, 198 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 70854ae298..d1ee8e53f2 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7301,6 +7301,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> @@ -7342,6 +7362,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.01 (1% of table size). + 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..904fbffd94 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -777,11 +777,24 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple <xref linkend="guc-autovacuum-vacuum-scale-factor"/>, and the number of tuples is <structname>pg_class</structname>.<structfield>reltuples</structfield>. - The number of obsolete tuples is obtained from the statistics - collector; it is a semi-accurate count updated by each - <command>UPDATE</command> and <command>DELETE</command> operation. (It - is only semi-accurate because some information might be lost under heavy - load.) If the <structfield>relfrozenxid</structfield> value of the table is more + The table is also vacuumed if the number of tuples inserted since the last + vacuum have exceeded the defined insert threshold, which is defined as: +<programlisting> +vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples +</programlisting> + where the vacuum insert base threshold is + <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>, + and vacuum insert scale factor is + <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>. + Such vacuums may allow the table's <structfield>relfrozenxid</structfield> + to be advanced and will reduce the chances of and the impact of any + aggressive vacuums. + The number of obsolete tuples and the number of inserted tuples are + obtained from the statistics collector; it is a semi-accurate count updated by each + <command>UPDATE</command>, <command>DELETE</command> and + <command>INSERT</command> operation. (It is only semi-accurate because + some information might be lost under heavy load.) If the + <structfield>relfrozenxid</structfield> value of the table is more than <varname>vacuum_freeze_table_age</varname> transactions old, an aggressive vacuum is performed to freeze old tuples and advance <structfield>relfrozenxid</structfield>; otherwise, only pages that have been modified diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 5bffdcce10..280c2d7fe6 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2849,6 +2849,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 ec207d3b26..0b8af4bce1 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", @@ -398,6 +407,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", @@ -1514,6 +1532,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, @@ -1538,6 +1558,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 b8a3f46912..717b20cd64 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 da75e755f0..bf6483359c 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; @@ -2969,16 +2971,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 +3011,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; @@ -3059,9 +3073,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,12 +3085,12 @@ 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); + *dovacuum = force_vacuum || (vactuples > vacthresh) || (instuples > vacinsthresh); *doanalyze = (anltuples > anlthresh); } else diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index d29c211a76..3c376bc3cc 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -4692,6 +4692,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; @@ -5822,6 +5823,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; @@ -5851,10 +5853,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; } @@ -6088,6 +6092,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 cea01534a5..6d66ff8b44 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 af876d1f01..fa5ac28d6a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3102,6 +3102,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."), @@ -3549,6 +3558,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.01, 0.0, 1e10, + 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 aa44f0c9bf..9988fe40f5 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -599,9 +599,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.01 # 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 ae35fa4aa9..ca8f0d75a6 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1078,6 +1078,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", @@ -1092,6 +1094,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 87d25d4a4b..ac8ad8dbf0 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 851d0a7246..76d093a422 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -647,6 +647,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 c7304611c3..90c7ed396d 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, @@ -2049,6 +2050,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, @@ -2092,6 +2094,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
