Обсуждение: db maintanance problem VACUUM FULL
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
On Thu, 2025-06-12 at 15:14 +0200, Pavol Sekeres wrote: > 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. But you should have. v12 is out of support. > It is around 2 TB in size with one stand-by replica of equal size. > > 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? Yes. Check for bloat in suspicious tables using the "pgstattuple" extension. > If yes, is there any other solution outside locking the database? There are the third-party tools pg_squeeze and pg_repack. > 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? Yes, that is possible. > Is this even a good idea? Logical replication can be complicated. One of the above tools would be simpler. > Our database uses a wal_level 'replica'. > As I understand it, this setting would first have to be switched to 'logical'. Right. Yours, Laurenz Albe
On Thu, Jun 12, 2025 at 9:14 AM 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'.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Thu, Jun 12, 2025 at 9:24 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2025-06-12 at 15:14 +0200, Pavol Sekeres wrote:
> 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.
But you should have. v12 is out of support.
The dreaded Software Compatibility Matrix often restricts how big of a jump is possible when upgrading databases (and even Python).
Multi-phase upgrades spanning months are quite common.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!