MIME-Version: 1.0
From: Pavol Sekeres <pavol.sekeres8@gmail.com>
Date: Thu, 12 Jun 2025 15:14:22 +0200
Message-ID: <CA+RPunpqzys-rqsH9Xia8KWJ2+mR=V_TXe4Ca+JDV7bvmym8TQ@mail.gmail.com>
Subject: db maintanance problem VACUUM FULL
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d8f95206375fb544"
Archived-At: <https://www.postgresql.org/message-id/CA%2BRPunpqzys-rqsH9Xia8KWJ2%2BmR%3DV_TXe4Ca%2BJDV7bvmym8TQ%40mail.gmail.com>
Precedence: bulk

--000000000000d8f95206375fb544
Content-Type: text/plain; charset="UTF-8"

Hi,

We recently updated our production database to PostgreSQL 12.22 from the
9.6.24 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?

If yes, is there any other solution outside locking the database?
Should we try to solve this problem by creating a logical replica of the
database?
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'.

Thank you for your answer,
Best regards,
Pavol Sekeres

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

<div dir=3D"ltr">Hi,<div><br></div><div>We recently updated our production =
database to PostgreSQL 12.22 from the 9.6.24 version.</div><div>We didn&#39=
;t want to make a big jump.</div><div>It is around 2 TB in size with one st=
and-by replica of equal size.</div><div>The database is more than 5 years o=
ld.</div><div></div><div><br></div><div>We do run AUTOVACUUM processes on a=
ll tables periodically.</div><div>We have never run VACUUM FULL on any tabl=
e.</div><div>This is because we can&#39;t afford to lock out tables for a l=
ong time.</div><div><br></div><div>Tables can be more than 100GB 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 might get event=
ually bloated.</div><div><br></div><div>Can this be a problem?</div><div><b=
r></div><div>If yes, is there any other solution outside locking the databa=
se?</div><div>Should we try to solve this problem by creating a logical rep=
lica of the database?</div><div>We would then promote the replica to primar=
y.</div><div>After that, we would drop the old database.</div><div><div>Is =
this possible without a big downtime?</div><div>Is this even a good idea?</=
div></div><div><br></div><div>Our database uses a wal_level &#39;replica=
9;.</div><div>As I understand it, this setting would first have to be switc=
hed to &#39;logical&#39;.</div><div></div><div><br></div><div>Thank you for=
 your answer,</div><div>Best regards,</div><div>Pavol Sekeres</div><div><br=
></div></div>

--000000000000d8f95206375fb544--