MIME-Version: 1.0
References: <Z2DE4lDX4tHqNGZt@dev.null> <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
 <CABwTF4U3xkF=ZRi2pztUDxohoN8h6XL10=QmTtuTXoMjzu5-zg@mail.gmail.com>
 <f82fc7fbb69a8c8000ad74ced50381257fe57838.camel@cybertec.at>
 <CA+TgmoZMXN19eorKdeiiFCv3AJFVaUAfkzRuamnt8A9U8uJSqg@mail.gmail.com> <Z7Tl2d7HrG1AQEOc@nathan>
In-Reply-To: <Z7Tl2d7HrG1AQEOc@nathan>
From: Gurjeet Singh <gurjeet@singh.im>
Date: Thu, 27 Feb 2025 20:29:16 -0800
Message-ID: <CABwTF4XPc1_y=khhjErd=Oz8R20ZH05KigiAnMjjA+028QbohQ@mail.gmail.com>
Subject: Re: Disabling vacuum truncate for autovacuum
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, 
	Postgres Hackers <pgsql-hackers@postgresql.org>, Will Storey <will@summercat.com>
Content-Type: multipart/mixed; boundary="00000000000001bd2f062f2c42f8"
Archived-At: <https://www.postgresql.org/message-id/CABwTF4XPc1_y%3DkhhjErd%3DOz8R20ZH05KigiAnMjjA%2B028QbohQ%40mail.gmail.com>
Precedence: bulk

--00000000000001bd2f062f2c42f8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jan 27, 2025 at 1:55=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at> wrote:
>
> On Thu, 2025-01-23 at 22:33 -0800, Gurjeet Singh wrote:
> > > > I am also wondering if having an autovacuum setting to control it w=
ould be
> > > > a good idea for a feature.
> > >
> > > I'm all for that.
> >
> > Please see attached an initial patch to disable truncation behaviour in
> > autovacuum. This patch retains the default behavior of autovacuum trunc=
ating
> > relations. The user is allowed to change the behaviour and disable rela=
tion
> > truncations system-wide by setting autovacuum_disable_vacuum_truncate =
=3D true.
> > Better parameter names welcome :-)
>
> I hope it is possible to override the global setting with the "vacuum_tru=
ncate"
> option on an individual table.

Current patch behaviour is that if the autovacuum_vacuum_truncate is false,=
 then
autovacuum will _not_ truncate any relations. If the parameter's value is t=
rue
(the default), then the relation's reloption will be honored.

A table-owner, or the database-owner, may enable truncation of a table, as =
they
may be trying to be nice and return the unused disk space back to the
OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for t=
he
health of the entire db instance, as well as of any replicas of the db
instance),
wants to disable truncation across all databases to ensure that the replica=
tion
does not get stuck, then IMHO Postgres should empower the sysadmin to make
that decision, and override the relation-level setting enabled by the table=
-
owner or the database-owner.

> > One additional improvement I can think of is to emit a WARNING or NOTIC=
E message
> > that truncate operation is being skipped, perhaps only if the truncatio=
n
> > would've freed up space over a certain threshold.
>
> Interesting idea, but I think it is independent from this patch.

Agreed. I'll consider writing a separate patch for this.

> > Perhaps there's value in letting this parameter be specified at databas=
e level,
> > but I'm not able to think of a reason why someone would want to disable=
 this
> > behaviour on just one database. So leaving the parameter context to be =
the same
> > as most other autovacuum parameters: SIGHUP.
>
> I can imagine setting that on only a certain database. Different database=
s
> typically have different applications, which have different needs.

Makes sense. I don't think anything special needs to be done in the patch t=
o
address this.

> Eventually, the patch should have documentation and regression tests.

Documentation added. Pointers on if, where, and what kind of tests to add w=
ill
be appreciated.

On Mon, Jan 27, 2025 at 12:38=E2=80=AFPM Robert Haas <robertmhaas@gmail.com=
> wrote:
>
> On Mon, Jan 27, 2025 at 4:55=E2=80=AFAM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> > My suggestion for the parameter name is "autovacuum_disable_truncate".
>
> Then it would have a different name than the reloption, and the
> opposite sense. In most cases, we've been able to keep those matching
> -- autovacuum vs. autovacuum_enabled being, I believe, the only
> current mismatch.

If we want to maintain the convention of autovacuum parameters names to be =
of
the format "autovacuum_<vacuum-option's-name>" then I believe the name
autovacuum_vacuum_truncate (boolean) would be better, as compared to my ori=
ginal
proposal (autovacuum_disable_vacuum_truncate), or Laurenz's proposal above.=
 The
default value should be true, to match the current autovacuum behaviour.

> Also, how sure are we that turning this off globally is a solid idea?
> Off-hand, it doesn't sound that bad: there are probably situations in
> which autovacuum never truncates anything anyway just because the tail
> blocks of the relations always contain at least 1 tuple. But we should
> be careful not to add a setting that is far more likely to get people
> into trouble than to get them out of it. It would be good to hear what
> other people think about the risk vs. reward tradeoff in this case.

Taking silence from others to be a sign of no opposition, I'm moving forwar=
d
with the patch.

On Tue, Feb 18, 2025 at 11:56 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:
>
> On Mon, Jan 27, 2025 at 03:38:39 PM -0500, Robert Haas wrote:
> > Also, how sure are we that turning this off globally is a solid idea?

> In any case, it's
> already possible to achieve $SUBJECT with a trivial script that sets
> storage parameters on all tables, so IMHO it would be silly to withhold a
> global setting that does the same thing just on principle.

+1

For documentation of this GUC, I borrowed heavily from the relevant section=
s of
CREATE TABLE and VACUUM docs.

There are 3 ways I wrote one of the sentences in the docs. I picked the las=
t
one, as it is concise and clearer than the others. If others feel a differe=
nt
choice of words would be better, I'm all ears.

         If <literal>false</literal>, autovacuum will not perform the
         truncation, even if the <literal>vacuum_truncate</literal> option =
has
         been set to <literal>true</literal> for the table being processed.

         If <literal>false</literal>, autovacuum will not perform the
         truncation, and it ignores the <literal>vacuum_truncate</literal>
         option for the tables it processes.

         If <literal>false</literal>, autovacuum will not perform the trunc=
ation
         on any tables it vacuums. The <literal>vacuum_truncate</literal> o=
ption
         on the tables is ignored.

PS: Nathan, your latest email arrived as I was preparing this email and pat=
ch,
so this email and patch does not address concerns, if any, in your latest e=
mail.
I will try to respond to it soon.

Best regards,
Gurjeet
http://Gurje.et

--00000000000001bd2f062f2c42f8
Content-Type: application/octet-stream; 
	name="autovacuum_disable_relation_truncation.v2.patch"
Content-Disposition: attachment; 
	filename="autovacuum_disable_relation_truncation.v2.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m7o9wf5h0>
X-Attachment-Id: f_m7o9wf5h0

ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9jb25maWcuc2dtbCBiL2RvYy9zcmMvc2dtbC9jb25m
aWcuc2dtbAppbmRleCBlNTU3MDBmMzViOC4uMTdkOTRkN2NlNTggMTAwNjQ0Ci0tLSBhL2RvYy9z
cmMvc2dtbC9jb25maWcuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvY29uZmlnLnNnbWwKQEAgLTg5
MzQsNiArODkzNCw0MiBAQCBDT1BZIHBvc3RncmVzX2xvZyBGUk9NICcvZnVsbC9wYXRoL3RvL2xv
Z2ZpbGUuY3N2JyBXSVRIIGNzdjsKICAgICAgICA8L2xpc3RpdGVtPgogICAgICAgPC92YXJsaXN0
ZW50cnk+CiAKKyAgICAgIDx2YXJsaXN0ZW50cnkgaWQ9Imd1Yy1hdXRvdmFjdXVtLXZhY3V1bS10
cnVuY2F0ZSIgeHJlZmxhYmVsPSJhdXRvdmFjdXVtX3ZhY3V1bV90cnVuY2F0ZSI+CisgICAgICAg
PHRlcm0+PHZhcm5hbWU+YXV0b3ZhY3V1bV92YWN1dW1fdHJ1bmNhdGU8L3Zhcm5hbWU+ICg8dHlw
ZT5ib29sZWFuPC90eXBlPikKKyAgICAgICA8aW5kZXh0ZXJtPgorICAgICAgICA8cHJpbWFyeT48
dmFybmFtZT5hdXRvdmFjdXVtX3ZhY3V1bV90cnVuY2F0ZTwvdmFybmFtZT48L3ByaW1hcnk+Cisg
ICAgICAgIDxzZWNvbmRhcnk+Y29uZmlndXJhdGlvbiBwYXJhbWV0ZXI8L3NlY29uZGFyeT4KKyAg
ICAgICA8L2luZGV4dGVybT4KKyAgICAgICA8L3Rlcm0+CisgICAgICAgPGxpc3RpdGVtPgorICAg
ICAgICA8cGFyYT4KKyAgICAgICAgIEVuYWJsZXMgb3IgZGlzYWJsZXMgYXV0b3ZhY3V1bSB0byB0
cnkgdG8gdHJ1bmNhdGUgb2ZmIGFueSBlbXB0eSBwYWdlcworICAgICAgICAgYXQgdGhlIGVuZCBv
ZiB0aGUgdGFibGVzLCBhcyBpdCBwcm9jZXNzZXMgdGhlbS4gVGhlIGRlZmF1bHQgdmFsdWUgaXMK
KyAgICAgICAgIDxsaXRlcmFsPnRydWU8L2xpdGVyYWw+LgorCisgICAgICAgICBJZiA8bGl0ZXJh
bD50cnVlPC9saXRlcmFsPiwgYXV0b3ZhY3V1bSB3aWxsIHBlcmZvcm4gdGhlIHRydW5jYXRpb24g
YW5kCisgICAgICAgICB0aGUgZGlzayBzcGFjZSBmb3IgdGhlIHRydW5jYXRlZCBwYWdlcyBpcyBy
ZXR1cm5lZCB0byB0aGUgb3BlcmF0aW5nCisgICAgICAgICBzeXN0ZW0uCisKKyAgICAgICAgIFRo
aXMgaXMgbm9ybWFsbHkgdGhlIGRlc2lyZWQgYmVoYXZpb3IgYW5kIGlzIHRoZSBkZWZhdWx0IHVu
bGVzcyB0aGUKKyAgICAgICAgIDxsaXRlcmFsPnZhY3V1bV90cnVuY2F0ZTwvbGl0ZXJhbD4gb3B0
aW9uIGZvciBhIHRhYmxlIGJlaW5nIHZhY3V1bWVkCisgICAgICAgICBoYXMgYmVlbiBzZXQgdG8g
PGxpdGVyYWw+ZmFsc2U8L2xpdGVyYWw+LgorCisgICAgICAgICBJZiA8bGl0ZXJhbD5mYWxzZTwv
bGl0ZXJhbD4sIGF1dG92YWN1dW0gd2lsbCBub3QgcGVyZm9ybSB0aGUgdHJ1bmNhdGlvbgorICAg
ICAgICAgb24gYW55IHRhYmxlcyBpdCB2YWN1dW1zLiBUaGUgPGxpdGVyYWw+dmFjdXVtX3RydW5j
YXRlPC9saXRlcmFsPiBvcHRpb24KKyAgICAgICAgIG9uIHRoZSB0YWJsZXMgaXMgaWdub3JlZC4K
KworICAgICAgICAgTm90ZSB0aGF0IHRoZSB0cnVuY2F0aW9uIHJlcXVpcmVzIDxsaXRlcmFsPkFD
Q0VTUyBFWENMVVNJVkU8L2xpdGVyYWw+CisgICAgICAgICBsb2NrIG9uIHRoZSB0YWJsZS4KKwor
ICAgICAgICAgU2V0dGluZyB0aGlzIG9wdGlvbiB0byA8bGl0ZXJhbD5mYWxzZTwvbGl0ZXJhbD4g
bWF5IGJlIHVzZWZ1bCB0byBhdm9pZAorICAgICAgICAgPGxpdGVyYWw+QUNDRVNTIEVYQ0xVU0lW
RTwvbGl0ZXJhbD4gbG9jayBvbiB0aGUgdGFibGUgdGhhdCB0aGUKKyAgICAgICAgIHRydW5jYXRp
b24gcmVxdWlyZXMuCisKKyAgICAgICAgPC9wYXJhPgorICAgICAgIDwvbGlzdGl0ZW0+CisgICAg
ICA8L3Zhcmxpc3RlbnRyeT4KKwogICAgICA8L3ZhcmlhYmxlbGlzdD4KICAgICA8L3NlY3QyPgog
CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9wb3N0bWFzdGVyL2F1dG92YWN1dW0uYyBiL3NyYy9i
YWNrZW5kL3Bvc3RtYXN0ZXIvYXV0b3ZhY3V1bS5jCmluZGV4IGRkYjMwM2Y1MjAxLi5kODI2MWE5
YjE5ZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRvdmFjdXVtLmMKKysr
IGIvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRvdmFjdXVtLmMKQEAgLTExNSw2ICsxMTUsNyBA
QAogICogR1VDIHBhcmFtZXRlcnMKICAqLwogYm9vbAkJYXV0b3ZhY3V1bV9zdGFydF9kYWVtb24g
PSBmYWxzZTsKK2Jvb2wJCWF1dG92YWN1dW1fdmFjdXVtX3RydW5jYXRlID0gdHJ1ZTsKIGludAkJ
CWF1dG92YWN1dW1fd29ya2VyX3Nsb3RzOwogaW50CQkJYXV0b3ZhY3V1bV9tYXhfd29ya2VyczsK
IGludAkJCWF1dG92YWN1dW1fd29ya19tZW0gPSAtMTsKQEAgLTI4MTIsMTIgKzI4MTMsMTYgQEAg
dGFibGVfcmVjaGVja19hdXRvdmFjKE9pZCByZWxpZCwgSFRBQiAqdGFibGVfdG9hc3RfbWFwLAog
CQkJKCF3cmFwYXJvdW5kID8gVkFDT1BUX1NLSVBfTE9DS0VEIDogMCk7CiAKIAkJLyoKLQkJICog
aW5kZXhfY2xlYW51cCBhbmQgdHJ1bmNhdGUgYXJlIHVuc3BlY2lmaWVkIGF0IGZpcnN0IGluIGF1
dG92YWN1dW0uCi0JCSAqIFRoZXkgd2lsbCBiZSBmaWxsZWQgaW4gd2l0aCB1c2FibGUgdmFsdWVz
IHVzaW5nIHRoZWlyIHJlbG9wdGlvbnMKLQkJICogKG9yIHJlbG9wdGlvbiBkZWZhdWx0cykgbGF0
ZXIuCisJCSAqIGluZGV4X2NsZWFudXAgaXMgdW5zcGVjaWZpZWQgYXQgZmlyc3QgaW4gYXV0b3Zh
Y3V1bS4gdHJ1bmNhdGUgaXMKKwkJICogdW5zcGVjaWZpZWQsIHVubGVzcyBpdCBpcyBkaXNhYmxl
ZCB2aWEgdGhlIEdVQyBwYXJhbWV0ZXIuCisJCSAqCisJCSAqIFRoZSB1bnNwZWNpZmllZCBvcHRp
b25zIHdpbGwgYmUgZmlsbGVkIGluIHdpdGggdXNhYmxlIHZhbHVlcyB1c2luZworCQkgKiB0aGVp
ciByZWxvcHRpb25zIChvciByZWxvcHRpb24gZGVmYXVsdHMpIGxhdGVyLgogCQkgKi8KIAkJdGFi
LT5hdF9wYXJhbXMuaW5kZXhfY2xlYW51cCA9IFZBQ09QVFZBTFVFX1VOU1BFQ0lGSUVEOwotCQl0
YWItPmF0X3BhcmFtcy50cnVuY2F0ZSA9IFZBQ09QVFZBTFVFX1VOU1BFQ0lGSUVEOworCQl0YWIt
PmF0X3BhcmFtcy50cnVuY2F0ZSA9IChhdXRvdmFjdXVtX3ZhY3V1bV90cnVuY2F0ZSA9PSBmYWxz
ZSkKKwkJCQkJCQkJCT8gVkFDT1BUVkFMVUVfRElTQUJMRUQKKwkJCQkJCQkJCTogVkFDT1BUVkFM
VUVfVU5TUEVDSUZJRUQ7CiAJCS8qIEFzIG9mIG5vdywgd2UgZG9uJ3Qgc3VwcG9ydCBwYXJhbGxl
bCB2YWN1dW0gZm9yIGF1dG92YWN1dW0gKi8KIAkJdGFiLT5hdF9wYXJhbXMubndvcmtlcnMgPSAt
MTsKIAkJdGFiLT5hdF9wYXJhbXMuZnJlZXplX21pbl9hZ2UgPSBmcmVlemVfbWluX2FnZTsKZGlm
ZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3RhYmxlcy5jIGIvc3JjL2JhY2tl
bmQvdXRpbHMvbWlzYy9ndWNfdGFibGVzLmMKaW5kZXggYWQyNWNiYjM5YzUuLmQxNDQzMjU2MTEw
IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y190YWJsZXMuYworKysgYi9z
cmMvYmFja2VuZC91dGlscy9taXNjL2d1Y190YWJsZXMuYwpAQCAtMTUzMSw2ICsxNTMxLDE2IEBA
IHN0cnVjdCBjb25maWdfYm9vbCBDb25maWd1cmVOYW1lc0Jvb2xbXSA9CiAJCU5VTEwsIE5VTEws
IE5VTEwKIAl9LAogCisJeworCQl7ImF1dG92YWN1dW1fdmFjdXVtX3RydW5jYXRlIiwgUEdDX1NJ
R0hVUCwgVkFDVVVNX0FVVE9WQUNVVU0sCisJCQlnZXR0ZXh0X25vb3AoIkRpc2FibGVzIGF1dG92
YWN1dW0gYmVoYXZpb3Igb2YgdHJ1bmNhdGlpbmcgcmVsYXRpb25zLiIpLAorCQkJTlVMTAorCQl9
LAorCQkmYXV0b3ZhY3V1bV92YWN1dW1fdHJ1bmNhdGUsCisJCXRydWUsCisJCU5VTEwsIE5VTEws
IE5VTEwKKwl9LAorCiAJewogCQl7InRyYWNlX25vdGlmeSIsIFBHQ19VU0VSU0VULCBERVZFTE9Q
RVJfT1BUSU9OUywKIAkJCWdldHRleHRfbm9vcCgiR2VuZXJhdGVzIGRlYnVnZ2luZyBvdXRwdXQg
Zm9yIExJU1RFTiBhbmQgTk9USUZZLiIpLApkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvcG9zdG1h
c3Rlci9hdXRvdmFjdXVtLmggYi9zcmMvaW5jbHVkZS9wb3N0bWFzdGVyL2F1dG92YWN1dW0uaApp
bmRleCA2YTk1ZTVmNTViZC4uZDBiZDZmNDk4YzcgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRlL3Bv
c3RtYXN0ZXIvYXV0b3ZhY3V1bS5oCisrKyBiL3NyYy9pbmNsdWRlL3Bvc3RtYXN0ZXIvYXV0b3Zh
Y3V1bS5oCkBAIC0yOCw2ICsyOCw3IEBAIHR5cGVkZWYgZW51bQogCiAvKiBHVUMgdmFyaWFibGVz
ICovCiBleHRlcm4gUEdETExJTVBPUlQgYm9vbCBhdXRvdmFjdXVtX3N0YXJ0X2RhZW1vbjsKK2V4
dGVybiBQR0RMTElNUE9SVCBib29sIGF1dG92YWN1dW1fdmFjdXVtX3RydW5jYXRlOwogZXh0ZXJu
IFBHRExMSU1QT1JUIGludCBhdXRvdmFjdXVtX3dvcmtlcl9zbG90czsKIGV4dGVybiBQR0RMTElN
UE9SVCBpbnQgYXV0b3ZhY3V1bV9tYXhfd29ya2VyczsKIGV4dGVybiBQR0RMTElNUE9SVCBpbnQg
YXV0b3ZhY3V1bV93b3JrX21lbTsK
--00000000000001bd2f062f2c42f8--