MIME-Version: 1.0
From: Bartosz Stalewski <bartekstalewski@gmail.com>
Date: Fri, 7 Mar 2025 12:16:38 +0100
Message-ID: <CAE0uZgfFxgpNjC1t6zOict9hXi9=6S3zAmfpV+Xe-ZTBWJbCew@mail.gmail.com>
Subject: Vacuum related question
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003eb4ca062fbec234"
Archived-At: <https://www.postgresql.org/message-id/CAE0uZgfFxgpNjC1t6zOict9hXi9%3D6S3zAmfpV%2BXe-ZTBWJbCew%40mail.gmail.com>
Precedence: bulk

--0000000000003eb4ca062fbec234
Content-Type: text/plain; charset="UTF-8"

Hello All!

I'm currently using 17.2 version and working on optimizing vacuums.
I would be greateful if you could confirm (or deny) my idea.

Current situation:
Every 4-5 days vacuum to prevent wraparound is running in my DB, which of
course is bad. There are a few tables that are constantly being updated
(both inserts and updates) - they are quite big - ranging from a few
hundred million to over a billion rows.
Other tables get only inserts (with similar size - they may reach over a
billion rows).

Solution:
I would like to have a quick vacuum to freeze old tuples - it would be
quick because it would omit indexes (because switch INDEX_CLEANUP would be
set as false). Additionally for tables with dead tuples I would run vacuum
with INDEX_CLEANUP set as true during weekend. Omitting indexes makes a
huge difference in terms of vacuum time.

Would it be a problem from performance point of view (e.g. accumulating
some bloat?) if tables that do not contain dead tuples are vacuumed only
with flag INDEX_CLEANUP set as false? I have a feeling that it is fine, but
unfortunately I couldn't find any clear evidence when browsing Internet.

Thanks in advance,
Bartek

--0000000000003eb4ca062fbec234
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello All!<br><br>I&#39;m currently using 17.2 version and=
 working on optimizing vacuums.<br>I would be greateful if you could confir=
m (or deny) my idea.<br><br>Current situation:<br>Every 4-5 days vacuum to =
prevent wraparound is running in my DB, which of course is bad. There are a=
 few tables that are constantly being updated (both inserts and updates) - =
they are quite big - ranging from a few hundred million to over a billion r=
ows.<br>Other tables get only inserts (with similar size - they may reach o=
ver a billion rows).<br><br>Solution:<br>I would like to have a quick vacuu=
m to freeze old tuples - it would be quick because it would omit indexes (b=
ecause switch INDEX_CLEANUP would be set as false). Additionally for tables=
 with dead tuples I would run vacuum with INDEX_CLEANUP set as true during =
weekend. Omitting indexes makes a huge difference in terms of vacuum time.<=
br><br>Would it be a problem from performance point of view (e.g. accumulat=
ing some bloat?) if tables that do not contain dead tuples are vacuumed onl=
y with flag INDEX_CLEANUP set as false? I have a feeling that it is fine, b=
ut unfortunately I couldn&#39;t find any clear evidence when browsing Inter=
net.<br><br>Thanks in advance,<br>Bartek</div>

--0000000000003eb4ca062fbec234--