MIME-Version: 1.0
References: <CA+RPunpqzys-rqsH9Xia8KWJ2+mR=V_TXe4Ca+JDV7bvmym8TQ@mail.gmail.com>
In-Reply-To: <CA+RPunpqzys-rqsH9Xia8KWJ2+mR=V_TXe4Ca+JDV7bvmym8TQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 12 Jun 2025 09:26:34 -0400
Message-ID: <CANzqJaBtawqxCN0U5zKW9ov-Dicrnt00bfYVUNpYC=fvKiHF8Q@mail.gmail.com>
Subject: Re: db maintanance problem VACUUM FULL
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000945e3406375fe1aa"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBtawqxCN0U5zKW9ov-Dicrnt00bfYVUNpYC%3DfvKiHF8Q%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jun 12, 2025 at 9:14=E2=80=AFAM Pavol Sekeres <pavol.sekeres8@gmail=
.com>
wrote:

> Hi,
>
> We recently updated our production database to PostgreSQL 12.22 from the
> 9.6.24 version.
>

Will you soon make another jump to a supported version?


> We didn't want to make a big jump.
> It is around 2 TB in size with one stand-by replica of equal size.
> The database is more than 5 years old.
>
> We do run AUTOVACUUM processes on all tables periodically.
> We have never run VACUUM FULL on any table.
> This is because we can't afford to lock out tables for a long time.
>
> Tables can be more than 100GB in size.
> They are being updated daily.
> Also due to GDPR old data is erased on a daily basis.
> We think these tables might get eventually bloated.
>
> Can this be a problem?
>

What are your autovacuum settings?  If they're aggressive, then no it's not
a problem.


> If yes, is there any other solution outside locking the database?
>

Every weekend, I delete records older than 90 days from 600GB and 300GB
tables.  Because the vacuumdb of the two tables is in bash script that
deletes the old data, and my autovacuum settings are pretty agressive, they
hover around 20% free space right after the purge.

I also disable autovacuum on those tables before the DELETE and enable it
after vacuumdb.

Should we try to solve this problem by creating a logical replica of the
> database?
>

Just to eliminate bloat?


> We would then promote the replica to primary.
> After that, we would drop the old database.
> Is this possible without a big downtime?
> Is this even a good idea?
>
> Our database uses a wal_level 'replica'.
> As I understand it, this setting would first have to be switched to
> 'logical'.
>

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jun 12, 2025 at 9:14=E2=80=AFAM P=
avol Sekeres &lt;<a href=3D"mailto:pavol.sekeres8@gmail.com">pavol.sekeres8=
@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contai=
ner"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi,=
<div><br></div><div>We recently updated our production database to PostgreS=
QL 12.22 from the 9.6.24 version.</div></div></blockquote><div><br></div><d=
iv>Will you soon make another jump to a supported version?</div><div>=C2=A0=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><d=
iv>We didn&#39;t want to make a big jump.</div><div>It is around 2 TB in si=
ze with one stand-by replica of equal size.</div><div>The database is more =
than 5 years old.</div><div></div><div><br></div><div>We do run AUTOVACUUM =
processes on all tables periodically.</div><div>We have never run VACUUM FU=
LL on any table.</div><div>This is because we can&#39;t afford to lock out =
tables for a long time.</div><div><br></div><div>Tables can be more than 10=
0GB in size.</div><div>They are being updated daily.</div><div>Also due to =
GDPR old data is erased on a daily basis.</div><div>We think these tables m=
ight get eventually bloated.</div><div><br></div><div>Can this be a problem=
?</div></div></blockquote><div><br></div><div>What are your autovacuum sett=
ings?=C2=A0 If they&#39;re aggressive, then no it&#39;s not a problem.</div=
><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr"><div>If yes, is there any other solution outside locking the data=
base?</div></div></blockquote><div><br></div><div>Every weekend, I delete r=
ecords older than 90 days from 600GB and 300GB tables.=C2=A0 Because the va=
cuumdb of the two tables is in bash script that deletes the old data, and m=
y autovacuum settings are pretty agressive, they hover around 20% free spac=
e right after the purge.</div><div>=C2=A0</div><div>I also disable autovacu=
um on those tables before the DELETE and enable it after vacuumdb.</div><di=
v><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr"><div>Should we try to solve this problem by creating a logical replica =
of the database?</div></div></blockquote><div><br></div><div>Just to elimin=
ate bloat?</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"ltr"><div>We would then promote the replica to primary.=
</div><div>After that, we would drop the old database.</div><div><div>Is th=
is possible without a big downtime?</div><div>Is this even a good idea?</di=
v></div><div><br></div><div>Our database uses a wal_level &#39;replica&#39;=
.</div><div>As I understand it, this setting would first have to be switche=
d to &#39;logical&#39;.</div></div></blockquote><div>=C2=A0</div></div><spa=
n class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"=
gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sau=
ce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&g=
t; lobster!</div></div></div></div></div></div>

--000000000000945e3406375fe1aa--