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
