Logical replication, need to reclaim big disk space

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Logical replication, need to reclaim big disk space
Date: 2025-05-16 15:45:59
Message-ID: 7be05164-f62e-49ec-87c8-9c3512904d07@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
    we are moving our old binary data approach, moving them from bytea
fields in a table to external storage (making database smaller and
related operations faster and smarter).
In short, we have a job that runs in background and copies data from the
table to an external file and then sets the bytea field to NULL.
(UPDATE tbl SET blob = NULL, ref = 'path/to/file' WHERE id = <uuid>)

This results, at the end of the operations, to a table that's less than
one tenth in size.
We have a multi-tenant architecture (100s of schemas with identical
architecture, all inheriting from public) and we are performing the task
on one table per schema.

The problem is: this is generating BIG table bloat, as you may imagine.
Running a VACUUM FULL on an ex-22GB table on a standalone test server is
almost immediate.
If I had only one server, I'll process a table a time, with a nightly
script, and issue a VACUUM FULL to tables that have already been processed.

But I'm in a logical replication architecture (we are using a
multimaster system called pgEdge, but I don't think it will make big
difference, since it's based on logical replication), and I'm building a
test cluster.

I've been instructed to issue VACUUM FULL on both nodes, nightly, but
before proceeding I read on docs that VACUUM FULL can disrupt logical
replication, so I'm a bit concerned on how to proceed. Rows are cleared
one a time (one transaction, one row, to keep errors to the record that
issued them)

I read about extensions like pg_squeeze, but I wonder if they are still
not dangerous for replication.

Thanks for your help.
Moreno.-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Achilleas Mantzios 2025-05-16 19:33:29 Re: Logical replication, need to reclaim big disk space
Previous Message yi zhao 2025-05-15 19:44:48 Re: unexpected pageaddr in WAL segment