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.-
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 |