Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1uFxGD-00Aqbh-S4
	for pgsql-general@arkaria.postgresql.org; Fri, 16 May 2025 15:46:10 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1uFxGC-002h7v-UK
	for pgsql-general@arkaria.postgresql.org; Fri, 16 May 2025 15:46:08 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <moreno.andreo@evolu-s.it>)
	id 1uFxGC-002h7m-JK
	for pgsql-general@lists.postgresql.org; Fri, 16 May 2025 15:46:08 +0000
Received: from mail.evolu-s.it ([77.81.232.174])
	by makus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
	(Exim 4.96)
	(envelope-from <moreno.andreo@evolu-s.it>)
	id 1uFxG6-00266i-0T
	for pgsql-general@postgresql.org;
	Fri, 16 May 2025 15:46:07 +0000
dkim-signature: v=1; a=rsa-sha256; d=evolu-s.it; s=dkim;
	c=relaxed/relaxed; q=dns/txt; h=From:Subject:Date:Message-ID:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	bh=tUX9WHTuWCf1eyj/I2N+4krtFnEXiJjvE/ztNXpOEck=;
	b=fAx4GBYXTRP7dbe7SACbUIV40IFfH+5rrY4AyaFHpDVC4XPLBRzq5pZ6eZf6acF1AWUo7M1MsWIgSLfALPYBiSZqoXcBTU6QBGauO5hRpnNP5mjdSJ/OGeP15AikgTLNgDGcDEwSjqsMc7K03vPerctUtCe7OfnjpnMio0KfgJw=
Received: from [192.168.1.103] (res-129401d.ppp.twt.it [83.217.179.105])
	by mail.evolu-s.it with ESMTPSA
	(version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128)
	; Fri, 16 May 2025 17:45:59 +0200
Message-ID: <7be05164-f62e-49ec-87c8-9c3512904d07@evolu-s.it>
Date: Fri, 16 May 2025 17:45:59 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US, it
To: PostgreSQL mailing lists <pgsql-general@postgresql.org>
From: Moreno Andreo <moreno.andreo@evolu-s.it>
Subject: Logical replication, need to reclaim big disk space
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/7be05164-f62e-49ec-87c8-9c3512904d07%40evolu-s.it>
Precedence: bulk

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