Subject: pgsql: Add vacuum_truncate configuration parameter.

From: Nathan Bossart <[email-masked]>
Date: Thu, 20 Mar 2025 15:17:09 +0000

Add vacuum_truncate configuration parameter.

This new parameter works just like the storage parameter of the
same name: if set to true (which is the default), autovacuum and
VACUUM attempt to truncate any empty pages at the end of the table.
It is primarily intended to help users avoid locking issues on hot
standbys.  The setting can be overridden with the storage parameter
or VACUUM's TRUNCATE option.

Since there's presently no way to determine whether a Boolean
storage parameter is explicitly set or has just picked up the
default value, this commit also introduces an isset_offset member
to relopt_parse_elt.

Suggested-by: Will Storey <[email-masked]>
Author: Nathan Bossart <[email-masked]>
Co-authored-by: Gurjeet Singh <[email-masked]>
Reviewed-by: Laurenz Albe <[email-masked]>
Reviewed-by: Fujii Masao <[email-masked]>
Reviewed-by: Robert Treat <[email-masked]>
Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/0164a0f9ee12e0eff9e4c661358a272ecd65c2d4

Modified Files
--------------
doc/src/sgml/config.sgml                      | 29 +++++++++++++++++++++++++++
doc/src/sgml/ref/create_table.sgml            | 13 ++++--------
doc/src/sgml/ref/vacuum.sgml                  |  3 ++-
src/backend/access/common/reloptions.c        | 14 ++++++++++++-
src/backend/commands/vacuum.c                 | 17 ++++++++++++----
src/backend/utils/misc/guc_tables.c           | 10 +++++++++
src/backend/utils/misc/postgresql.conf.sample |  4 ++++
src/include/access/reloptions.h               |  1 +
src/include/commands/vacuum.h                 |  1 +
src/include/utils/guc_tables.h                |  1 +
src/include/utils/rel.h                       |  1 +
src/test/regress/expected/vacuum.out          | 27 +++++++++++++++++++++++++
src/test/regress/sql/vacuum.sql               | 10 +++++++++
13 files changed, 116 insertions(+), 15 deletions(-)

---

From: Tom Lane <[email-masked]>
Date: Thu, 20 Mar 2025 11:30:54 -0400

> default value, this commit also introduces an isset_offset member

Uh, what?  Why is it a good idea to distinguish those states?
Seems like that risks some very surprising behavior, ie if the
default is "true", why shouldn't that act exactly like an
explicit setting of "true"?

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 20 Mar 2025 08:39:53 -0700

> explicit setting of "true"?

In order to implement what amounts to coalesce(…) for settings (use global
value unless the table value overrides) one needs a sentinel value that
means unset because settings cannot take on the null value.  There is no
such possible sentinel value for boolean so another field is required.  The
hazards of choosing Boolean instead of text for settings.

David J.

---

From: David Rowley <[email-masked]>
Date: Fri, 21 Mar 2025 12:07:00 +1300

> explicit setting of "true"?

I was thinking about this yesterday as the topic of a
user-configurable options for truncation threshold came up in [1]. I
find it a bit annoying the boolean vacuum_truncate reloption was added
(a few years ago) as we could have instead added a more flexible
truncate_scale_factor that could be set to -1 to disable truncation.
Maybe it's too late now as reloptions are not easy to remove. Adding
this GUC now does put us a bit further down the path of the boolean
option.

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 21 Mar 2025 09:39:26 -0700

> this GUC now does put us a bit further down the path of the boolean

Not seeing much point in trying to get rid of the on/off switch.  It just
won't make sense to choose a tunable value of zero to disable something,
and probably should be prohibited.

I'm seeing an implementation detail discussion here, not a behavior one.
The field complaint that we don't let the DBA control this at the GUC level
is valid and reasonably solved.  The "default" behavior hasn't changed but
now instead of hard-coding the default we moved it to a GUC.  The storage
parameter is no longer documented as having a default, which is correct.
It now behaves like most of the other storage parameters in that if unset a
GUC provides the value.

David J.

---

From: David Rowley <[email-masked]>
Date: Mon, 24 Mar 2025 11:22:43 +1300

On Sat, 22 Mar 2025 at 05:40, David G. Johnston
<[email-masked]> wrote:
> Not seeing much point in trying to get rid of the on/off switch.  It just won't make sense to choose a tunable value of zero to disable something, and probably should be prohibited.

Can you explain what does not make sense about it?  We have plenty of
GUCs and reloptions where -1 means "inherit the setting from somewhere
else". Do those also not make sense, or is this one somehow different?

> I'm seeing an implementation detail discussion here, not a behavior one.  The field complaint that we don't let the DBA control this at the GUC level is valid and reasonably solved.  The "default" behavior hasn't changed but now instead of hard-coding the default we moved it to a GUC.  The storage parameter is no longer documented as having a default, which is correct.  It now behaves like most of the other storage parameters in that if unset a GUC provides the value.

The reason I was pointing this out is that I wanted to ensure that
this was considered before we release code with the new GUC. It's true
removing a GUC isn't as hard as removing a reloption and we already
have the reloption. If everyone thinks we'll likely not need
user-tunable options to specify the number of pages required before
truncation occurs then that's fine.  The problem I see is that we
already have lots of GUCs and it'd be nice not to have semi-redundant
ones in the future because we've failed to consider something.  I was
just pointing out the "something" part that we might want to consider.

David