aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/access/common/reloptions.c22
-rw-r--r--src/backend/catalog/system_views.sql1
-rw-r--r--src/backend/postmaster/autovacuum.c31
-rw-r--r--src/backend/postmaster/pgstat.c16
-rw-r--r--src/backend/utils/adt/pgstatfuncs.c16
-rw-r--r--src/backend/utils/misc/guc.c20
-rw-r--r--src/backend/utils/misc/postgresql.conf.sample5
-rw-r--r--src/bin/psql/tab-complete.c4
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/include/catalog/pg_proc.dat5
-rw-r--r--src/include/pgstat.h1
-rw-r--r--src/include/postmaster/autovacuum.h2
-rw-r--r--src/include/utils/rel.h2
-rw-r--r--src/test/regress/expected/rules.out3
14 files changed, 125 insertions, 5 deletions
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index ec207d3b26c..e136116d7ba 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -235,6 +235,15 @@ static relopt_int intRelOpts[] =
},
{
{
+ "autovacuum_vacuum_insert_threshold",
+ "Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ -2, -1, INT_MAX
+ },
+ {
+ {
"autovacuum_analyze_threshold",
"Minimum number of tuple inserts, updates or deletes prior to analyze",
RELOPT_KIND_HEAP,
@@ -400,6 +409,15 @@ static relopt_real realRelOpts[] =
},
{
{
+ "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",
"Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples",
RELOPT_KIND_HEAP,
@@ -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 5a6dc616301..83d00c6cdef 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 da75e755f0f..7e97ffab27d 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,15 @@ 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;
+
+ /* -1 is used to disable insert vacuums */
+ vac_ins_base_thresh = (relopts && relopts->vacuum_ins_threshold >= -1)
+ ? 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 +3074,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 +3086,18 @@ 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)",
- NameStr(classForm->relname),
- vactuples, vacthresh, anltuples, anlthresh);
+ if (vac_ins_base_thresh >= 0)
+ elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
+ NameStr(classForm->relname),
+ vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh);
+ else
+ elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)",
+ NameStr(classForm->relname),
+ vactuples, vacthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
- *dovacuum = force_vacuum || (vactuples > vacthresh);
+ *dovacuum = force_vacuum || (vactuples > vacthresh) ||
+ (vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
*doanalyze = (anltuples > anlthresh);
}
else
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 4763c24be94..ab42df7e1b4 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -4701,6 +4701,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;
@@ -5831,6 +5832,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;
@@ -5860,10 +5862,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;
}
@@ -6098,6 +6102,18 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len)
tabentry->n_live_tuples = msg->m_live_tuples;
tabentry->n_dead_tuples = msg->m_dead_tuples;
+ /*
+ * It is quite possible that a non-aggressive VACUUM ended up skipping
+ * various pages, however, we'll zero the insert counter here regardless.
+ * It's currently used only to track when we need to perform an
+ * "insert" autovacuum, which are mainly intended to freeze newly inserted
+ * tuples. Zeroing this may just mean we'll not try to vacuum the table
+ * again until enough tuples have been inserted to trigger another insert
+ * autovacuum. An anti-wraparound autovacuum will catch any persistent
+ * stragglers.
+ */
+ tabentry->inserts_since_vacuum = 0;
+
if (msg->m_autovacuum)
{
tabentry->autovac_vacuum_timestamp = msg->m_vacuumtime;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cea01534a5e..6d66ff8b448 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -197,6 +197,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)
{
Oid relid = PG_GETARG_OID(0);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 53665971f53..79bc7ac8cac 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3103,6 +3103,15 @@ static struct config_int ConfigureNamesInt[] =
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"),
+ NULL
+ },
+ &autovacuum_vac_ins_thresh,
+ 1000, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+ {
{"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Minimum number of tuple inserts, updates, or deletes prior to analyze."),
NULL
@@ -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.2, 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 f01e43b8189..e9f8ca775da 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -598,9 +598,14 @@
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
+#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
+ # before vacuum. -1 disables insert
+ # vacuums
#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.2 # fraction of inserts over table
+ # size before insert 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 ae35fa4aa98..ca8f0d75a62 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/catversion.h b/src/include/catalog/catversion.h
index 8aa18483e4e..58ff619e8a5 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202003241
+#define CATALOG_VERSION_NO 202003281
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87d25d4a4bd..ac8ad8dbf08 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 a07012bf4ba..763c1ee2bdd 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 d40ed55531d..7277effe034 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 39cdcddc2b1..2a13d8aad0c 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -257,6 +257,7 @@ typedef struct AutoVacOpts
{
bool enabled;
int vacuum_threshold;
+ int vacuum_ins_threshold;
int analyze_threshold;
int vacuum_cost_limit;
int freeze_min_age;
@@ -268,6 +269,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 a2077bbad4d..7245b0e13b8 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,
@@ -2052,6 +2053,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,
@@ -2095,6 +2097,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,
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 13:04:35 +0000