MIME-Version: 1.0
References: <Z2DE4lDX4tHqNGZt@dev.null> <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
In-Reply-To: <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
From: Gurjeet Singh <gurjeet@singh.im>
Date: Thu, 23 Jan 2025 22:33:29 -0800
Message-ID: <CABwTF4U3xkF=ZRi2pztUDxohoN8h6XL10=QmTtuTXoMjzu5-zg@mail.gmail.com>
Subject: Re: Disabling vacuum truncate for autovacuum
To: Postgres Hackers <pgsql-hackers@postgresql.org>
Cc: Will Storey <will@summercat.com>, Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/mixed; boundary="000000000000daab6f062c6de99b"
Archived-At: <https://www.postgresql.org/message-id/CABwTF4U3xkF%3DZRi2pztUDxohoN8h6XL10%3DQmTtuTXoMjzu5-zg%40mail.gmail.com>
Precedence: bulk

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

(moving discussion to -hackers, for patch-review)

On Mon, Dec 16, 2024 at 11:30=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at> wrote:
>
> On Mon, 2024-12-16 at 16:25 -0800, Will Storey wrote:
> > I would like to disable vacuum's truncate behaviour for autovacuum.
> > Previously I had an outage due to its access exclusive lock when it was
> > replicated to a hot standby.

I can attest to one production outage caused by this behaviour of autovacuu=
m.
The truncate operation performed by autovacuum, when being replayed on the
replica, was blocked by a query. Any new queries on that relation were bloc=
ked
by replication.

> > When that outage happened it was from a VACUUM call in a cronjob rather
> > than autovacuum. I now run such VACUUMs with TRUNCATE false which avoid=
s
> > the issue for these. However I've realized that autovacuum could cause =
this
> > as well.
> >
> > I believe the only way to disable this for autovacuum is by changing th=
e
> > vacuum_truncate storage parameters on tables. (Ignoring the now removed
> > old_snapshot_threshold option).
>
> Yes, you can only do that table by table.

That is unfortunate. Although doing so provides a granular control over whi=
ch
relations one would like to exclude from truncation, it may not always be
desirable; a DBA/sysadmin may want to prevent this problem system-wide.

Also, this not really scalable since it requires that a maintenance operati=
on
regularly connect to every database and apply this setting to all the relat=
ions,
for the fear that there may be new objects somewhere in the cluster since l=
ast
maintenance, which may cause this problem. It would be error prone, too,
considering that the list of databases in a cluster may change over time. A=
nd
then there's the added burden of monitoring the status of this maintenance
operation to ensure it's running successfully every time.

Turning on a system-wide setting that disables autovacuum truncation may lo=
ok
like a heavy hammer, but in certain situations this may be preferable to th=
e
risk of causing outage in production systems. It may be preferable to let t=
he
system consume disk space by not truncating the tables, as opposed to runni=
ng
the risk of blocked queries. Disk is cheap, and is possibly already being
monitored in a production system.

I understand Jeremy's contention upthread against adding such a feature at
global level, but I'm in favor of adding this feature since it prevents a s=
udden
and unpredictable impact on production systems, and instead leads to a grad=
ual
escalation of the problem that can be monitored and addressed by a sysadmin=
/DBA
at a time that's convenient for them.

> > I am also wondering if having an autovacuum setting to control it would=
 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 truncatin=
g
relations. The user is allowed to change the behaviour and disable relation
truncations system-wide by setting autovacuum_disable_vacuum_truncate =3D t=
rue.
Better parameter names welcome :-)

One additional improvement I can think of is to emit a WARNING or NOTICE me=
ssage
that truncate operation is being skipped, perhaps only if the truncation
would've freed up space over a certain threshold.

Perhaps there's value in letting this parameter be specified at database le=
vel,
but I'm not able to think of a reason why someone would want to disable thi=
s
behaviour on just one database. So leaving the parameter context to be the =
same
as most other autovacuum parameters: SIGHUP.

Best regards,
Gurjeet
http://Gurje.et

--000000000000daab6f062c6de99b
Content-Type: application/x-patch; 
	name="autovacuum_disable_relation_truncation.v1.patch"
Content-Disposition: attachment; 
	filename="autovacuum_disable_relation_truncation.v1.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m6adce9p0>
X-Attachment-Id: f_m6adce9p0

Y29tbWl0IDg0ZThlZWJiODdiYzJjNThmZWFlODQ3ZWZkOTk1YmMwNTU3MDE2ODgKQXV0aG9yOiBH
dXJqZWV0IFNpbmdoIDxndXJqZWV0QHNpbmdoLmltPgpEYXRlOiAgIFRodSBKYW4gMjMgMTk6Mzc6
MzMgMjAyNSAtMDgwMAoKICAgIFZlcnNpb24gMQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Bv
c3RtYXN0ZXIvYXV0b3ZhY3V1bS5jIGIvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRvdmFjdXVt
LmMKaW5kZXggMGFiOTIxYTE2OS4uMTdlMjJjMDhlNSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
cG9zdG1hc3Rlci9hdXRvdmFjdXVtLmMKKysrIGIvc3JjL2JhY2tlbmQvcG9zdG1hc3Rlci9hdXRv
dmFjdXVtLmMKQEAgLTExNSw2ICsxMTUsNyBAQAogICogR1VDIHBhcmFtZXRlcnMKICAqLwogYm9v
bAkJYXV0b3ZhY3V1bV9zdGFydF9kYWVtb24gPSBmYWxzZTsKK2Jvb2wJCWF1dG92YWN1dW1fZGlz
YWJsZV92YWN1dW1fdHJ1bmNhdGUgPSBmYWxzZTsKIGludAkJCWF1dG92YWN1dW1fd29ya2VyX3Ns
b3RzOwogaW50CQkJYXV0b3ZhY3V1bV9tYXhfd29ya2VyczsKIGludAkJCWF1dG92YWN1dW1fd29y
a19tZW0gPSAtMTsKQEAgLTI4MTEsMTIgKzI4MTIsMTYgQEAgdGFibGVfcmVjaGVja19hdXRvdmFj
KE9pZCByZWxpZCwgSFRBQiAqdGFibGVfdG9hc3RfbWFwLAogCQkJKCF3cmFwYXJvdW5kID8gVkFD
T1BUX1NLSVBfTE9DS0VEIDogMCk7CiAKIAkJLyoKLQkJICogaW5kZXhfY2xlYW51cCBhbmQgdHJ1
bmNhdGUgYXJlIHVuc3BlY2lmaWVkIGF0IGZpcnN0IGluIGF1dG92YWN1dW0uCi0JCSAqIFRoZXkg
d2lsbCBiZSBmaWxsZWQgaW4gd2l0aCB1c2FibGUgdmFsdWVzIHVzaW5nIHRoZWlyIHJlbG9wdGlv
bnMKLQkJICogKG9yIHJlbG9wdGlvbiBkZWZhdWx0cykgbGF0ZXIuCisJCSAqIGluZGV4X2NsZWFu
dXAgaXMgdW5zcGVjaWZpZWQgYXQgZmlyc3QgaW4gYXV0b3ZhY3V1bS4gdHJ1bmNhdGUgaXMKKwkJ
ICogdW5zcGVjaWZpZWQsIHVubGVzcyBpdCBpcyBkaXNhYmxlZCB2aWEgdGhlIEdVQyBwYXJhbWV0
ZXIuCisJCSAqCisJCSAqIFRoZSB1bnNwZWNpZmllZCBvcHRpb25zIHdpbGwgYmUgZmlsbGVkIGlu
IHdpdGggdXNhYmxlIHZhbHVlcyB1c2luZworCQkgKiB0aGVpciByZWxvcHRpb25zIChvciByZWxv
cHRpb24gZGVmYXVsdHMpIGxhdGVyLgogCQkgKi8KIAkJdGFiLT5hdF9wYXJhbXMuaW5kZXhfY2xl
YW51cCA9IFZBQ09QVFZBTFVFX1VOU1BFQ0lGSUVEOwotCQl0YWItPmF0X3BhcmFtcy50cnVuY2F0
ZSA9IFZBQ09QVFZBTFVFX1VOU1BFQ0lGSUVEOworCQl0YWItPmF0X3BhcmFtcy50cnVuY2F0ZSA9
IGF1dG92YWN1dW1fZGlzYWJsZV92YWN1dW1fdHJ1bmNhdGUKKwkJCQkJCQkJCT8gVkFDT1BUVkFM
VUVfRElTQUJMRUQKKwkJCQkJCQkJCTogVkFDT1BUVkFMVUVfVU5TUEVDSUZJRUQ7CiAJCS8qIEFz
IG9mIG5vdywgd2UgZG9uJ3Qgc3VwcG9ydCBwYXJhbGxlbCB2YWN1dW0gZm9yIGF1dG92YWN1dW0g
Ki8KIAkJdGFiLT5hdF9wYXJhbXMubndvcmtlcnMgPSAtMTsKIAkJdGFiLT5hdF9wYXJhbXMuZnJl
ZXplX21pbl9hZ2UgPSBmcmVlemVfbWluX2FnZTsKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0
aWxzL21pc2MvZ3VjX3RhYmxlcy5jIGIvc3JjL2JhY2tlbmQvdXRpbHMvbWlzYy9ndWNfdGFibGVz
LmMKaW5kZXggMzhjYjllOTcwZC4uNDAzNjhlMzM5YyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
dXRpbHMvbWlzYy9ndWNfdGFibGVzLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvbWlzYy9ndWNf
dGFibGVzLmMKQEAgLTE1MDksNiArMTUwOSwxNiBAQCBzdHJ1Y3QgY29uZmlnX2Jvb2wgQ29uZmln
dXJlTmFtZXNCb29sW10gPQogCQlOVUxMLCBOVUxMLCBOVUxMCiAJfSwKIAorCXsKKwkJeyJhdXRv
dmFjdXVtX2Rpc2FibGVfdmFjdXVtX3RydW5jYXRlIiwgUEdDX1NJR0hVUCwgVkFDVVVNX0FVVE9W
QUNVVU0sCisJCQlnZXR0ZXh0X25vb3AoIkRpc2FibGVzIGF1dG92YWN1dW0gYmVoYXZpb3Igb2Yg
dHJ1bmNhdGlpbmcgcmVsYXRpb25zLiIpLAorCQkJTlVMTAorCQl9LAorCQkmYXV0b3ZhY3V1bV9k
aXNhYmxlX3ZhY3V1bV90cnVuY2F0ZSwKKwkJZmFsc2UsCisJCU5VTEwsIE5VTEwsIE5VTEwKKwl9
LAorCiAJewogCQl7InRyYWNlX25vdGlmeSIsIFBHQ19VU0VSU0VULCBERVZFTE9QRVJfT1BUSU9O
UywKIAkJCWdldHRleHRfbm9vcCgiR2VuZXJhdGVzIGRlYnVnZ2luZyBvdXRwdXQgZm9yIExJU1RF
TiBhbmQgTk9USUZZLiIpLApkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvcG9zdG1hc3Rlci9hdXRv
dmFjdXVtLmggYi9zcmMvaW5jbHVkZS9wb3N0bWFzdGVyL2F1dG92YWN1dW0uaAppbmRleCA1NGUw
MWM4MWQ2Li5jNzU5NzQwN2QyIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9wb3N0bWFzdGVyL2F1
dG92YWN1dW0uaAorKysgYi9zcmMvaW5jbHVkZS9wb3N0bWFzdGVyL2F1dG92YWN1dW0uaApAQCAt
MjgsNiArMjgsNyBAQCB0eXBlZGVmIGVudW0KIAogLyogR1VDIHZhcmlhYmxlcyAqLwogZXh0ZXJu
IFBHRExMSU1QT1JUIGJvb2wgYXV0b3ZhY3V1bV9zdGFydF9kYWVtb247CitleHRlcm4gUEdETExJ
TVBPUlQgYm9vbCBhdXRvdmFjdXVtX2Rpc2FibGVfdmFjdXVtX3RydW5jYXRlOwogZXh0ZXJuIFBH
RExMSU1QT1JUIGludCBhdXRvdmFjdXVtX3dvcmtlcl9zbG90czsKIGV4dGVybiBQR0RMTElNUE9S
VCBpbnQgYXV0b3ZhY3V1bV9tYXhfd29ya2VyczsKIGV4dGVybiBQR0RMTElNUE9SVCBpbnQgYXV0
b3ZhY3V1bV93b3JrX21lbTsK
--000000000000daab6f062c6de99b--