Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option
authorDavid Rowley <drowley@postgresql.org>
Thu, 6 Apr 2023 23:40:31 +0000 (11:40 +1200)
committerDavid Rowley <drowley@postgresql.org>
Thu, 6 Apr 2023 23:40:31 +0000 (11:40 +1200)
commit1cbbee03385763b066ae3961fc61f2cd01a0d0d7
treedbc0fc9f326d9622860592e18c49937247ef04f4
parent5279e9db8e8da3c310c0068da2de98df5a714b2e
Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option

Add new options to the VACUUM and ANALYZE commands called
BUFFER_USAGE_LIMIT to allow users more control over how large to make the
buffer access strategy that is used to limit the usage of buffers in
shared buffers.  Larger rings can allow VACUUM to run more quickly but
have the drawback of VACUUM possibly evicting more buffers from shared
buffers that might be useful for other queries running on the database.

Here we also add a new GUC named vacuum_buffer_usage_limit which controls
how large to make the access strategy when it's not specified in the
VACUUM/ANALYZE command.  This defaults to 256KB, which is the same size as
the access strategy was prior to this change.  This setting also
controls how large to make the buffer access strategy for autovacuum.

Per idea by Andres Freund.

Author: Melanie Plageman
Reviewed-by: David Rowley
Reviewed-by: Andres Freund
Reviewed-by: Justin Pryzby
Reviewed-by: Bharath Rupireddy
Discussion: https://postgr.es/m/20230111182720.ejifsclfwymw2reb@awork3.anarazel.de
17 files changed:
doc/src/sgml/config.sgml
doc/src/sgml/ref/analyze.sgml
doc/src/sgml/ref/vacuum.sgml
src/backend/commands/vacuum.c
src/backend/commands/vacuumparallel.c
src/backend/postmaster/autovacuum.c
src/backend/storage/buffer/README
src/backend/storage/buffer/freelist.c
src/backend/utils/init/globals.c
src/backend/utils/misc/guc_tables.c
src/backend/utils/misc/postgresql.conf.sample
src/bin/psql/tab-complete.c
src/include/miscadmin.h
src/include/storage/bufmgr.h
src/include/utils/guc_hooks.h
src/test/regress/expected/vacuum.out
src/test/regress/sql/vacuum.sql