Hi,
Attached patch adds 'autovacuum_table_priority' to the current list of
automatic vacuuming settings. It's used in sorting of vacuumed tables in
autovacuum worker before actual vacuum.
The idea is to give possibility to the users to prioritize their tables
in autovacuum process.
--
---
Regards,
Ildus Kurbangaliev
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c45979dee4..b7383a7136 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -6250,6 +6250,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-table-priority" xreflabel="autovacuum_table_priority">
+ <term><varname>autovacuum_table_priority</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_table_priority</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the priority of the table in automatic
+ <command>VACUUM</command> operations. 0 by default, bigger value gives
+ more priority.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect1>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 46276ceff1..2476dfe943 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -291,6 +291,15 @@ static relopt_int intRelOpts[] =
},
-1, -1, INT_MAX
},
+ {
+ {
+ "autovacuum_table_priority",
+ "Sets the priority of the table in autovacuum process",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ 0, INT_MIN, INT_MAX
+ },
{
{
"toast_tuple_target",
@@ -1353,6 +1362,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_table_age)},
{"log_autovacuum_min_duration", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, log_min_duration)},
+ {"autovacuum_table_priority", RELOPT_TYPE_INT,
+ offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, priority)},
{"toast_tuple_target", RELOPT_TYPE_INT,
offsetof(StdRdOptions, toast_tuple_target)},
{"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 702f8d8188..174de4a08c 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -174,15 +174,22 @@ typedef struct avw_dbase
PgStat_StatDBEntry *adw_entry;
} avw_dbase;
-/* struct to keep track of tables to vacuum and/or analyze, in 1st pass */
+/* struct for vacuumed or analyzed relation */
typedef struct av_relation
+{
+ Oid ar_relid;
+ int ar_priority; /* bigger- more important, used for sorting */
+} av_relation;
+
+/* struct to keep track of toast tables to vacuum and/or analyze, in 1st pass */
+typedef struct av_toastrelation
{
Oid ar_toastrelid; /* hash key - must be first */
Oid ar_relid;
bool ar_hasrelopts;
AutoVacOpts ar_reloptions; /* copy of AutoVacOpts from the main table's
* reloptions, or NULL if none */
-} av_relation;
+} av_toastrelation;
/* struct to keep track of tables to vacuum and/or analyze, after rechecking */
typedef struct autovac_table
@@ -1919,6 +1926,16 @@ get_database_list(void)
return dblist;
}
+/* qsort comparator for av_relation, using priority */
+static int
+autovac_comparator(const void *a, const void *b)
+{
+ av_relation *r1 = (av_relation *) lfirst(*(ListCell **) a);
+ av_relation *r2 = (av_relation *) lfirst(*(ListCell **) b);
+
+ return r2->ar_priority - r1->ar_priority;
+}
+
/*
* Process a database table-by-table
*
@@ -1932,7 +1949,7 @@ do_autovacuum(void)
HeapTuple tuple;
HeapScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;
+ List *optables = NIL;
List *orphan_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
@@ -2021,7 +2038,7 @@ do_autovacuum(void)
/* create hash table for toast <-> main relid mapping */
MemSet(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(Oid);
- ctl.entrysize = sizeof(av_relation);
+ ctl.entrysize = sizeof(av_toastrelation);
table_toast_map = hash_create("TOAST to main relid map",
100,
@@ -2101,9 +2118,14 @@ do_autovacuum(void)
effective_multixact_freeze_max_age,
&dovacuum, &doanalyze, &wraparound);
- /* Relations that need work are added to table_oids */
+ /* Relations that need work are added to optables */
if (dovacuum || doanalyze)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ av_relation *rel = (av_relation *) palloc(sizeof(av_relation));
+ rel->ar_relid = relid;
+ rel->ar_priority = relopts != NULL ? relopts->priority : 0;
+ optables = lappend(optables, rel);
+ }
/*
* Remember TOAST associations for the second pass. Note: we must do
@@ -2112,7 +2134,7 @@ do_autovacuum(void)
*/
if (OidIsValid(classForm->reltoastrelid))
{
- av_relation *hentry;
+ av_toastrelation *hentry;
bool found;
hentry = hash_search(table_toast_map,
@@ -2168,7 +2190,7 @@ do_autovacuum(void)
relopts = extract_autovac_opts(tuple, pg_class_desc);
if (relopts == NULL)
{
- av_relation *hentry;
+ av_toastrelation *hentry;
bool found;
hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found);
@@ -2186,7 +2208,12 @@ do_autovacuum(void)
/* ignore analyze for toast tables */
if (dovacuum)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ av_relation *rel = (av_relation *) palloc(sizeof(av_relation));
+ rel->ar_relid = relid;
+ rel->ar_priority = relopts != NULL ? relopts->priority : 0;
+ optables = lappend(optables, rel);
+ }
}
heap_endscan(relScan);
@@ -2282,6 +2309,9 @@ do_autovacuum(void)
MemoryContextSwitchTo(AutovacMemCxt);
}
+ /* Sort tables by autovacuum priority */
+ optables = list_qsort(optables, autovac_comparator);
+
/*
* Create a buffer access strategy object for VACUUM to use. We want to
* use the same one across all the vacuum operations we perform, since the
@@ -2300,9 +2330,9 @@ do_autovacuum(void)
/*
* Perform operations on collected tables.
*/
- foreach(cell, table_oids)
+ foreach(cell, optables)
{
- Oid relid = lfirst_oid(cell);
+ Oid relid = ((av_relation *) lfirst(cell))->ar_relid;
autovac_table *tab;
bool skipit;
int stdVacuumCostDelay;
@@ -2437,6 +2467,9 @@ do_autovacuum(void)
if (!tab->at_relname || !tab->at_nspname || !tab->at_datname)
goto deleted;
+ ereport(DEBUG1, (errmsg("automatic vacuum of table \"%s.%s.%s\"",
+ tab->at_datname, tab->at_nspname, tab->at_relname)));
+
/*
* We will abort vacuuming the current table if something errors out,
* and continue with the next one in schedule; in particular, this
@@ -2787,7 +2820,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
if (classForm->relkind == RELKIND_TOASTVALUE &&
avopts == NULL && table_toast_map != NULL)
{
- av_relation *hentry;
+ av_toastrelation *hentry;
bool found;
hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found);
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index aa8add544a..106cdf49ca 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -269,6 +269,7 @@ typedef struct AutoVacOpts
int multixact_freeze_max_age;
int multixact_freeze_table_age;
int log_min_duration;
+ int priority;
float8 vacuum_scale_factor;
float8 analyze_scale_factor;
} AutoVacOpts;
diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out
index c4107d5ca1..1f154baddf 100644
--- a/src/test/regress/expected/reloptions.out
+++ b/src/test/regress/expected/reloptions.out
@@ -50,23 +50,25 @@ CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
ERROR: invalid value for integer option "fillfactor": true
-- Simple ALTER TABLE
ALTER TABLE reloptions_test SET (fillfactor=31,
- autovacuum_analyze_scale_factor = 0.3);
+ autovacuum_analyze_scale_factor = 0.3,
+ autovacuum_table_priority = 1
+);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
- reloptions
-------------------------------------------------------------------------------
- {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
+ reloptions
+----------------------------------------------------------------------------------------------------------
+ {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3,autovacuum_table_priority=1}
(1 row)
-- Set boolean option to true without specifying value
ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
- reloptions
------------------------------------------------------------------------------
- {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
+ reloptions
+---------------------------------------------------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_table_priority=1,autovacuum_enabled=true,fillfactor=32}
(1 row)
-- Check that RESET works well
-ALTER TABLE reloptions_test RESET (fillfactor);
+ALTER TABLE reloptions_test RESET (fillfactor, autovacuum_table_priority);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
reloptions
---------------------------------------------------------------
diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql
index c9119fd863..c503e3aa04 100644
--- a/src/test/regress/sql/reloptions.sql
+++ b/src/test/regress/sql/reloptions.sql
@@ -32,7 +32,9 @@ CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
-- Simple ALTER TABLE
ALTER TABLE reloptions_test SET (fillfactor=31,
- autovacuum_analyze_scale_factor = 0.3);
+ autovacuum_analyze_scale_factor = 0.3,
+ autovacuum_table_priority = 1
+);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
-- Set boolean option to true without specifying value
@@ -40,7 +42,7 @@ ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
-- Check that RESET works well
-ALTER TABLE reloptions_test RESET (fillfactor);
+ALTER TABLE reloptions_test RESET (fillfactor, autovacuum_table_priority);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
-- Resetting all values causes the column to become null
