aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/config.sgml30
-rw-r--r--doc/src/sgml/ref/analyze.sgml20
-rw-r--r--doc/src/sgml/ref/vacuum.sgml24
-rw-r--r--src/backend/commands/vacuum.c95
-rw-r--r--src/backend/commands/vacuumparallel.c14
-rw-r--r--src/backend/postmaster/autovacuum.c18
-rw-r--r--src/backend/storage/buffer/README12
-rw-r--r--src/backend/storage/buffer/freelist.c61
-rw-r--r--src/backend/utils/init/globals.c5
-rw-r--r--src/backend/utils/misc/guc_tables.c11
-rw-r--r--src/backend/utils/misc/postgresql.conf.sample3
-rw-r--r--src/bin/psql/tab-complete.c4
-rw-r--r--src/include/miscadmin.h9
-rw-r--r--src/include/storage/bufmgr.h5
-rw-r--r--src/include/utils/guc_hooks.h2
-rw-r--r--src/test/regress/expected/vacuum.out19
-rw-r--r--src/test/regress/sql/vacuum.sql15
17 files changed, 322 insertions, 25 deletions
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index bcc49aec45d..992e9440012 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2001,6 +2001,36 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-vacuum-buffer-usage-limit" xreflabel="vacuum_buffer_usage_limit">
+ <term>
+ <varname>vacuum_buffer_usage_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_buffer_usage_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the size of the
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ used by the <command>VACUUM</command> and <command>ANALYZE</command>
+ commands. A setting of <literal>0</literal> will allow the operation
+ to use any number of <varname>shared_buffers</varname>. Otherwise
+ valid sizes range from <literal>128 KB</literal> to
+ <literal>16 GB</literal>. If the specified size would exceed 1/8 the
+ size of <varname>shared_buffers</varname>, the size is silently capped
+ to that value. The default value is <literal>256 KB</literal>. If
+ this value is specified without units, it is taken as kilobytes. This
+ parameter can be set at any time. It can be overridden for
+ <xref linkend="sql-vacuum"/> and <xref linkend="sql-analyze"/>
+ when passing the <option>BUFFER_USAGE_LIMIT</option> option. Higher
+ settings can allow <command>VACUUM</command> and
+ <command>ANALYZE</command> to run more quickly, but having too large a
+ setting may cause too many other useful pages to be evicted from
+ shared buffers.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-logical-decoding-work-mem" xreflabel="logical_decoding_work_mem">
<term><varname>logical_decoding_work_mem</varname> (<type>integer</type>)
<indexterm>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2f94e89cb0e..94e30f1ce74 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -28,6 +28,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ BUFFER_USAGE_LIMIT [ <replaceable class="parameter">string</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -96,6 +97,25 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</varlistentry>
<varlistentry>
+ <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+ <listitem>
+ <para>
+ Specifies the
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ ring buffer size for <command>ANALYZE</command>. This size is used to
+ calculate the number of shared buffers which will be reused as part of
+ this strategy. <literal>0</literal> disables use of a
+ <literal>Buffer Access Strategy</literal>. When this option is not
+ specified, <command>ANALYZE</command> uses the value from
+ <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
+ allow <command>ANALYZE</command> to run more quickly, but having too
+ large a setting may cause too many other useful pages to be evicted from
+ shared buffers.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index b6d30b57648..dd0fbb8cb75 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -39,6 +39,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
PARALLEL <replaceable class="parameter">integer</replaceable>
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
+ BUFFER_USAGE_LIMIT [ <replaceable class="parameter">string</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -346,6 +347,29 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
+ <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+ <listitem>
+ <para>
+ Specifies the
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ ring buffer size for <command>VACUUM</command>. This size is used to
+ calculate the number of shared buffers which will be reused as part of
+ this strategy. <literal>0</literal> disables use of a
+ <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
+ is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used
+ for both the vacuum and analyze stages. This option can't be used with
+ the <option>FULL</option> option except if <option>ANALYZE</option> is
+ also specified. When this option is not specified,
+ <command>VACUUM</command> uses the value from
+ <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
+ allow <command>VACUUM</command> to run more quickly, but having too
+ large a setting may cause too many other useful pages to be evicted from
+ shared buffers.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 9386c08a556..1980e7664bc 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -57,6 +57,7 @@
#include "utils/acl.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
+#include "utils/guc_hooks.h"
#include "utils/memutils.h"
#include "utils/pg_rusage.h"
#include "utils/snapmgr.h"
@@ -121,6 +122,26 @@ static bool vac_tid_reaped(ItemPointer itemptr, void *state);
static int vac_cmp_itemptr(const void *left, const void *right);
/*
+ * GUC check function to ensure GUC value specified is within the allowable
+ * range.
+ */
+bool
+check_vacuum_buffer_usage_limit(int *newval, void **extra,
+ GucSource source)
+{
+ /* Value upper and lower hard limits are inclusive */
+ if (*newval == 0 || (*newval >= MIN_BAS_VAC_RING_SIZE_KB &&
+ *newval <= MAX_BAS_VAC_RING_SIZE_KB))
+ return true;
+
+ /* Value does not fall within any allowable range */
+ GUC_check_errdetail("\"vacuum_buffer_usage_limit\" must be 0 or between %d KB and %d KB",
+ MIN_BAS_VAC_RING_SIZE_KB, MAX_BAS_VAC_RING_SIZE_KB);
+
+ return false;
+}
+
+/*
* Primary entry point for manual VACUUM and ANALYZE commands
*
* This is mainly a preparation wrapper for the real operations that will
@@ -139,6 +160,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = false;
bool process_main = true;
bool process_toast = true;
+ int ring_size;
bool skip_database_stats = false;
bool only_database_stats = false;
MemoryContext vac_context;
@@ -151,6 +173,12 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
/* By default parallel vacuum is enabled */
params.nworkers = 0;
+ /*
+ * Set this to an invalid value so it is clear whether or not a
+ * BUFFER_USAGE_LIMIT was specified when making the access strategy.
+ */
+ ring_size = -1;
+
/* Parse options list */
foreach(lc, vacstmt->options)
{
@@ -161,6 +189,48 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
verbose = defGetBoolean(opt);
else if (strcmp(opt->defname, "skip_locked") == 0)
skip_locked = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "buffer_usage_limit") == 0)
+ {
+ const char *hintmsg;
+ int result;
+ char *vac_buffer_size;
+
+ if (opt->arg == NULL)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("buffer_usage_limit option requires a valid value"),
+ parser_errposition(pstate, opt->location)));
+ }
+
+ vac_buffer_size = defGetString(opt);
+
+ if (!parse_int(vac_buffer_size, &result, GUC_UNIT_KB, &hintmsg))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value: \"%s\": is invalid for buffer_usage_limit",
+ vac_buffer_size),
+ hintmsg ? errhint("%s", _(hintmsg)) : 0));
+ }
+
+ /*
+ * Check that the specified size falls within the hard upper and
+ * lower limits if it is not 0. We explicitly disallow -1 since
+ * that behavior can be obtained by not specifying
+ * BUFFER_USAGE_LIMIT.
+ */
+ if (result != 0 &&
+ (result < MIN_BAS_VAC_RING_SIZE_KB || result > MAX_BAS_VAC_RING_SIZE_KB))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("buffer_usage_limit option must be 0 or between %d KB and %d KB",
+ MIN_BAS_VAC_RING_SIZE_KB, MAX_BAS_VAC_RING_SIZE_KB)));
+ }
+
+ ring_size = result;
+ }
else if (!vacstmt->is_vacuumcmd)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -266,6 +336,17 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
errmsg("VACUUM FULL cannot be performed in parallel")));
/*
+ * BUFFER_USAGE_LIMIT does nothing for VACUUM (FULL) so just raise an
+ * ERROR for that case. VACUUM (FULL, ANALYZE) does make use of it, so
+ * we'll permit that.
+ */
+ if (ring_size != -1 && (params.options & VACOPT_FULL) &&
+ !(params.options & VACOPT_ANALYZE))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL")));
+
+ /*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
*/
if (!(params.options & VACOPT_ANALYZE))
@@ -366,7 +447,19 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
MemoryContext old_context = MemoryContextSwitchTo(vac_context);
- bstrategy = GetAccessStrategy(BAS_VACUUM);
+ Assert(ring_size >= -1);
+
+ /*
+ * If BUFFER_USAGE_LIMIT was specified by the VACUUM or ANALYZE
+ * command, it overrides the value of VacuumBufferUsageLimit. Either
+ * value may be 0, in which case GetAccessStrategyWithSize() will
+ * return NULL, effectively allowing full use of shared buffers.
+ */
+ if (ring_size == -1)
+ ring_size = VacuumBufferUsageLimit;
+
+ bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, ring_size);
+
MemoryContextSwitchTo(old_context);
}
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index e200d5caf82..87ea5c52426 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -88,6 +88,12 @@ typedef struct PVShared
int maintenance_work_mem_worker;
/*
+ * The number of buffers each worker's Buffer Access Strategy ring should
+ * contain.
+ */
+ int ring_nbuffers;
+
+ /*
* Shared vacuum cost balance. During parallel vacuum,
* VacuumSharedCostBalance points to this value and it accumulates the
* balance of each parallel vacuum worker.
@@ -365,6 +371,9 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
maintenance_work_mem / Min(parallel_workers, nindexes_mwm) :
maintenance_work_mem;
+ /* Use the same buffer size for all workers */
+ shared->ring_nbuffers = GetAccessStrategyBufferCount(bstrategy);
+
pg_atomic_init_u32(&(shared->cost_balance), 0);
pg_atomic_init_u32(&(shared->active_nworkers), 0);
pg_atomic_init_u32(&(shared->idx), 0);
@@ -1018,8 +1027,9 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
pvs.indname = NULL;
pvs.status = PARALLEL_INDVAC_STATUS_INITIAL;
- /* Each parallel VACUUM worker gets its own access strategy */
- pvs.bstrategy = GetAccessStrategy(BAS_VACUUM);
+ /* Each parallel VACUUM worker gets its own access strategy. */
+ pvs.bstrategy = GetAccessStrategyWithSize(BAS_VACUUM,
+ shared->ring_nbuffers * (BLCKSZ / 1024));
/* Setup error traceback support for ereport() */
errcallback.callback = parallel_vacuum_error_callback;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 90cfc18f100..53c8f8d79cb 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2349,11 +2349,21 @@ do_autovacuum(void)
}
/*
- * 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
- * point is for VACUUM not to blow out the shared cache.
+ * Optionally, create a buffer access strategy object for VACUUM to use.
+ * We use the same BufferAccessStrategy object for all tables VACUUMed by
+ * this worker to prevent autovacuum from blowing out shared buffers.
+ *
+ * VacuumBufferUsageLimit being set to 0 results in
+ * GetAccessStrategyWithSize returning NULL, effectively meaning we can
+ * use up to all of shared buffers.
+ *
+ * If we later enter failsafe mode on any of the tables being vacuumed, we
+ * will cease use of the BufferAccessStrategy only for that table.
+ *
+ * XXX should we consider adding code to adjust the size of this if
+ * VacuumBufferUsageLimit changes?
*/
- bstrategy = GetAccessStrategy(BAS_VACUUM);
+ bstrategy = GetAccessStrategyWithSize(BAS_VACUUM, VacuumBufferUsageLimit);
/*
* create a memory context to act as fake PortalContext, so that the
diff --git a/src/backend/storage/buffer/README b/src/backend/storage/buffer/README
index a775276ff2c..011af7aff3e 100644
--- a/src/backend/storage/buffer/README
+++ b/src/backend/storage/buffer/README
@@ -229,12 +229,12 @@ update hint bits). In a scan that modifies every page in the scan, like a
bulk UPDATE or DELETE, the buffers in the ring will always be dirtied and
the ring strategy effectively degrades to the normal strategy.
-VACUUM uses a 256KB ring like sequential scans, but dirty pages are not
-removed from the ring. Instead, WAL is flushed if needed to allow reuse of
-the buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's
-buffers were sent to the freelist, which was effectively a buffer ring of 1
-buffer, resulting in excessive WAL flushing. Allowing VACUUM to update
-256KB between WAL flushes should be more efficient.
+VACUUM uses a ring like sequential scans, however, the size of this ring is
+controlled by the vacuum_buffer_usage_limit GUC. Dirty pages are not removed
+from the ring. Instead, WAL is flushed if needed to allow reuse of the
+buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's buffers
+were sent to the freelist, which was effectively a buffer ring of 1 buffer,
+resulting in excessive WAL flushing.
Bulk writes work similarly to VACUUM. Currently this applies only to
COPY IN and CREATE TABLE AS SELECT. (Might it be interesting to make
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index f122709fbe2..1c804fd2f59 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -540,8 +540,7 @@ StrategyInitialize(bool init)
BufferAccessStrategy
GetAccessStrategy(BufferAccessStrategyType btype)
{
- BufferAccessStrategy strategy;
- int nbuffers;
+ int ring_size_kb;
/*
* Select ring size to use. See buffer/README for rationales.
@@ -556,13 +555,13 @@ GetAccessStrategy(BufferAccessStrategyType btype)
return NULL;
case BAS_BULKREAD:
- nbuffers = 256 * 1024 / BLCKSZ;
+ ring_size_kb = 256;
break;
case BAS_BULKWRITE:
- nbuffers = 16 * 1024 * 1024 / BLCKSZ;
+ ring_size_kb = 16 * 1024;
break;
case BAS_VACUUM:
- nbuffers = 256 * 1024 / BLCKSZ;
+ ring_size_kb = 256;
break;
default:
@@ -571,22 +570,66 @@ GetAccessStrategy(BufferAccessStrategyType btype)
return NULL; /* keep compiler quiet */
}
- /* Make sure ring isn't an undue fraction of shared buffers */
- nbuffers = Min(NBuffers / 8, nbuffers);
+ return GetAccessStrategyWithSize(btype, ring_size_kb);
+}
+
+/*
+ * GetAccessStrategyWithSize -- create a BufferAccessStrategy object with a
+ * number of buffers equivalent to the passed in size.
+ *
+ * If the given ring size is 0, no BufferAccessStrategy will be created and
+ * the function will return NULL. ring_size_kb must not be negative.
+ */
+BufferAccessStrategy
+GetAccessStrategyWithSize(BufferAccessStrategyType btype, int ring_size_kb)
+{
+ int ring_buffers;
+ BufferAccessStrategy strategy;
+
+ Assert(ring_size_kb >= 0);
+
+ /* Figure out how many buffers ring_size_kb is */
+ ring_buffers = ring_size_kb / (BLCKSZ / 1024);
+
+ /* 0 means unlimited, so no BufferAccessStrategy required */
+ if (ring_buffers == 0)
+ return NULL;
+
+ /* Cap to 1/8th of shared_buffers */
+ ring_buffers = Min(NBuffers / 8, ring_buffers);
+
+ /* NBuffers should never be less than 16, so this shouldn't happen */
+ Assert(ring_buffers > 0);
/* Allocate the object and initialize all elements to zeroes */
strategy = (BufferAccessStrategy)
palloc0(offsetof(BufferAccessStrategyData, buffers) +
- nbuffers * sizeof(Buffer));
+ ring_buffers * sizeof(Buffer));
/* Set fields that don't start out zero */
strategy->btype = btype;
- strategy->nbuffers = nbuffers;
+ strategy->nbuffers = ring_buffers;
return strategy;
}
/*
+ * GetAccessStrategyBufferCount -- an accessor for the number of buffers in
+ * the ring
+ *
+ * Returns 0 on NULL input to match behavior of GetAccessStrategyWithSize()
+ * returning NULL with 0 size.
+ */
+int
+GetAccessStrategyBufferCount(BufferAccessStrategy strategy)
+{
+ if (strategy == NULL)
+ return 0;
+
+ return strategy->nbuffers;
+}
+
+/*
* FreeAccessStrategy -- release a BufferAccessStrategy object
*
* A simple pfree would do at the moment, but we would prefer that callers
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 1b1d8142548..011ec18015a 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -139,7 +139,10 @@ int max_worker_processes = 8;
int max_parallel_workers = 8;
int MaxBackends = 0;
-int VacuumCostPageHit = 1; /* GUC parameters for vacuum */
+/* GUC parameters for vacuum */
+int VacuumBufferUsageLimit = 256;
+
+int VacuumCostPageHit = 1;
int VacuumCostPageMiss = 2;
int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8062589efd5..e8e8245e91f 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2225,6 +2225,17 @@ struct config_int ConfigureNamesInt[] =
},
{
+ {"vacuum_buffer_usage_limit", PGC_USERSET, RESOURCES_MEM,
+ gettext_noop("Sets the buffer pool size for VACUUM, ANALYZE, and autovacuum."),
+ NULL,
+ GUC_UNIT_KB
+ },
+ &VacuumBufferUsageLimit,
+ 256, 0, MAX_BAS_VAC_RING_SIZE_KB,
+ check_vacuum_buffer_usage_limit, NULL, NULL
+ },
+
+ {
{"shared_memory_size", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the size of the server's main shared memory area (rounded up to the nearest MB)."),
NULL,
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ee49ca39370..e715aff3b81 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -157,6 +157,9 @@
# mmap
# (change requires restart)
#min_dynamic_shared_memory = 0MB # (change requires restart)
+#vacuum_buffer_usage_limit = 256 # size of vacuum and analyze buffer access strategy ring.
+ # 0 to disable vacuum buffer access strategy
+ # range 128kB to 16GB
# - Disk -
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index e38a49e8bd7..6614fd2e628 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2662,7 +2662,7 @@ psql_completion(const char *text, int start, int end)
* one word, so the above test is correct.
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
- COMPLETE_WITH("VERBOSE", "SKIP_LOCKED");
+ COMPLETE_WITH("VERBOSE", "SKIP_LOCKED", "BUFFER_USAGE_LIMIT");
else if (TailMatches("VERBOSE|SKIP_LOCKED"))
COMPLETE_WITH("ON", "OFF");
}
@@ -4620,7 +4620,7 @@ psql_completion(const char *text, int start, int end)
"DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
"INDEX_CLEANUP", "PROCESS_MAIN", "PROCESS_TOAST",
"TRUNCATE", "PARALLEL", "SKIP_DATABASE_STATS",
- "ONLY_DATABASE_STATS");
+ "ONLY_DATABASE_STATS", "BUFFER_USAGE_LIMIT");
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
COMPLETE_WITH("ON", "OFF");
else if (TailMatches("INDEX_CLEANUP"))
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 06a86f9ac1f..14bd574fc24 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -263,6 +263,15 @@ extern PGDLLIMPORT double hash_mem_multiplier;
extern PGDLLIMPORT int maintenance_work_mem;
extern PGDLLIMPORT int max_parallel_maintenance_workers;
+/*
+ * Upper and lower hard limits for the buffer access strategy ring size
+ * specified by the VacuumBufferUsageLimit GUC and BUFFER_USAGE_LIMIT option
+ * to VACUUM and ANALYZE.
+ */
+#define MIN_BAS_VAC_RING_SIZE_KB 128
+#define MAX_BAS_VAC_RING_SIZE_KB (16 * 1024 * 1024)
+
+extern PGDLLIMPORT int VacuumBufferUsageLimit;
extern PGDLLIMPORT int VacuumCostPageHit;
extern PGDLLIMPORT int VacuumCostPageMiss;
extern PGDLLIMPORT int VacuumCostPageDirty;
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 788aa279ba0..6ab00daa2ea 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -260,7 +260,12 @@ extern Size BufferShmemSize(void);
extern void AtProcExit_LocalBuffers(void);
/* in freelist.c */
+
extern BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype);
+extern BufferAccessStrategy GetAccessStrategyWithSize(BufferAccessStrategyType btype,
+ int ring_size_kb);
+extern int GetAccessStrategyBufferCount(BufferAccessStrategy strategy);
+
extern void FreeAccessStrategy(BufferAccessStrategy strategy);
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index aeb3663071f..f722fb250aa 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -33,6 +33,8 @@ extern bool check_autovacuum_max_workers(int *newval, void **extra,
GucSource source);
extern bool check_autovacuum_work_mem(int *newval, void **extra,
GucSource source);
+extern bool check_vacuum_buffer_usage_limit(int *newval, void **extra,
+ GucSource source);
extern bool check_backtrace_functions(char **newval, void **extra,
GucSource source);
extern void assign_backtrace_functions(const char *newval, void *extra);
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index e5a312182eb..eae27437883 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -350,6 +350,25 @@ SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
f
(1 row)
+-- BUFFER_USAGE_LIMIT option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+ANALYZE (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+-- try disabling the buffer usage limit
+VACUUM (BUFFER_USAGE_LIMIT 0) vac_option_tab;
+ANALYZE (BUFFER_USAGE_LIMIT 0) vac_option_tab;
+-- value exceeds max size error
+VACUUM (BUFFER_USAGE_LIMIT 16777220) vac_option_tab;
+ERROR: buffer_usage_limit option must be 0 or between 128 KB and 16777216 KB
+-- value is less than min size error
+VACUUM (BUFFER_USAGE_LIMIT 120) vac_option_tab;
+ERROR: buffer_usage_limit option must be 0 or between 128 KB and 16777216 KB
+-- integer overflow error
+VACUUM (BUFFER_USAGE_LIMIT 10000000000) vac_option_tab;
+ERROR: value: "10000000000": is invalid for buffer_usage_limit
+HINT: Value exceeds integer range.
+-- incompatible with VACUUM FULL error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', FULL) vac_option_tab;
+ERROR: BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL
-- SKIP_DATABASE_STATS option
VACUUM (SKIP_DATABASE_STATS) vactst;
-- ONLY_DATABASE_STATS option
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index a1fad43657c..51d7b1fecc7 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -272,6 +272,21 @@ SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
FROM pg_class c, pg_class t
WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab';
+-- BUFFER_USAGE_LIMIT option
+VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+ANALYZE (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
+-- try disabling the buffer usage limit
+VACUUM (BUFFER_USAGE_LIMIT 0) vac_option_tab;
+ANALYZE (BUFFER_USAGE_LIMIT 0) vac_option_tab;
+-- value exceeds max size error
+VACUUM (BUFFER_USAGE_LIMIT 16777220) vac_option_tab;
+-- value is less than min size error
+VACUUM (BUFFER_USAGE_LIMIT 120) vac_option_tab;
+-- integer overflow error
+VACUUM (BUFFER_USAGE_LIMIT 10000000000) vac_option_tab;
+-- incompatible with VACUUM FULL error
+VACUUM (BUFFER_USAGE_LIMIT '512 kB', FULL) vac_option_tab;
+
-- SKIP_DATABASE_STATS option
VACUUM (SKIP_DATABASE_STATS) vactst;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 18:11:34 +0000