Re: Logical replication, need to reclaim big disk space

On 19/5/25 17:38, Moreno Andreo wrote:

>
>
> On 19/05/25 14:41, Achilleas Mantzios wrote:
>>
>>
>> On 5/19/25 09:14, Moreno Andreo wrote:
>>>
>>> On 16/05/25 21:33, Achilleas Mantzios wrote:
>>>> On 16/5/25 18:45, Moreno Andreo wrote:
>>>>
>>>>> 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.
>>>>>
>>>> So? toasted data are kept on separate TOAST tables, unless those 
>>>> bytea cols are selected, you won't even touch them. I cannot 
>>>> understand what you are trying to achieve here.
>>>>
>>>> Years ago, when I made the mistake to go for a coffee and let my 
>>>> developers "improvise" , the result was a design similar to what 
>>>> you are trying to achieve. Years after, I am seriously considering 
>>>> moving those data back to PostgreSQL.
>>> The "related operations" I was talking about are backups and 
>>> database maintenance when needed, cluster/replica management, etc. 
>>> With a smaller database size they would be easier in timing and 
>>> effort, right?
>> Ok, but you'll lose replica functionality for those blobs, which 
>> means you don't care about them, correct me if I am wrong.
> I'm not saying I don't care about them, the opposite, they are 
> protected with Object Versioning and soft deletion, this should assure 
> a good protection against e.g. ransomware, if someone manages to get 
> in there (and if this happens, we'll have bigger troubles than this).
PostgreSQL has become very popular because of ppl who care about their data.
>>> We are mostly talking about costs, here. To give things their names, 
>>> I'm moving bytea contents (85% of total data) to files into Google 
>>> Cloud Storage buckets, that has a fraction of the cost of the disks 
>>> holding my database (on GCE, to be clear ).
>> May I ask the size of the bytea data (uncompressed) ?.
> single records vary from 150k to 80 MB, the grand total is more than 
> 8,5 TB in a circa 10 TB data footprint
>>> This data is not accessed frequently (just by the owner when he 
>>> needs to do it), so no need to keep it on expensive hardware.
>>> I've already read in these years that keeping many big bytea fields 
>>> in databases is not recommended, but might have misunderstood this.
>>
>> Ok, I assume those are unimportant data, but let me ask, what is the 
>> longevity or expected legitimacy of those ? I haven't worked with 
>> those just reading  :
>>
>> https://cloud.google.com/storage/pricing?_gl=1*1b25r8o*_up*MQ..&gclid=CjwKCAjwravBBhBjEiwAIr30VKfaOJytxmk7J29vjG4rBBkk2EUimPU5zPibST73nm3XRL2h0O9SxRoCaogQAvD_BwE&gclsrc=aw.ds#storage-pricing
>>
>> would you choose e.g. "*Anywhere Cache storage" ?
>> *
>>
> Absolutely not, this is *not* unimportant data, and we are using 
> Standard Storage, for 0,02$/GB/month + operations, that compared to a 
> 0.17$/GB/month of an SSD or even more for the Hyperdisks we are using, 
> is a good price drop.
How about hosting your data in your own storage and spend 0$/GB/month ?
>>
>> **
>>
>>> Another way would have been to move these tables to a different 
>>> tablespace, in cheaper storage, but it still would have been 3 times 
>>> the buckets cost.
>>>
>> can you actually mount those Cloud Storage Buckets under a supported 
>> FS in linux and just move them to tablespaces backed by this storage ?
>>
> Never tried, I mounted this via FUSE and had some simple operations in 
> the past, but not sure it can handle database operations in terms of 
> I/O bandwidth
>>
>>> Why are you considering to get data back to database tables?
>> Because now if we need to migrate from cloud to on-premise, or just 
>> upgrade or move the specific server which holds those data I will 
>> have an extra headache. Also this is a single point of failure, or 
>> best case a cause for fragmented technology introduced just for the 
>> sake of keeping things out of the DB.
> This is managed as an hierarchical disk structure, so the calling 
> server may be literally everywhere, it just needs an account (or a 
> service account) to get in there ,
and you are locked in a proprietary solution. and at their mercy of any 
future increases in cost.
>>>>
>>>>
>>>>> 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.
>>>>>
>>>> So you use PgEdge , but you wanna lose all the benefits of 
>>>> multi-master , since your binary data won't be replicated ...
>>> I don't think I need it to be replicated, since this data cannot be 
>>> "edited", so either it's there or it's been deleted. Buckets have 
>>> protections for data deletions or events like ransomware attacks and 
>>> such.
>>> Also multi-master was an absolute requirement one year ago because 
>>> of a project we were building, but it has been abandoned and now a 
>>> simple logical replication would be enough, but let's do one thing a 
>>> time.
>> Multi-master is cool, you can configure your pooler / clients to take 
>> advantage of this for full load balanced architecture, but if not a 
>> strict requirement , you can live without it, as so many of us, and 
>> employ other means of load balancing the reads.
> That's what we are doing, it's a really cool feature, but I 
> experienced (maybe because it uses old pglogical extension) that the 
> replication is a bit fragile, especially when dealing with those bytea 
> fields (when I ingest big loads, say 25-30 GB or more), it happened to 
> break replication, and recreating a replica from scratch with "normal 
> size" tables is not a big deal, since it can be achieved 
> automatically, because they normally fit in shared memory and can be 
> transferred by the replicator, but you can imagine what would be the 
> effort and the downtime necessary to create a base backup, transfer it 
> to the replica, build the DB and restart a 10-TB database (ATM we are 
> running with a 2-node cluster).
Break this in batches, use modern techniques for robust data loading, in 
smaller transactions, if you have to.
>>>>> 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)
>> Mind if you shared the specific doc ?
> Obviously I can't find it from a quick search, I'll search deeper, I 
> don't think it went off a dream :-).
>>>>>
>>>> PgEdge is based on the old pg_logical, the old 2ndQuadrant 
>>>> extension, not the native logical replication we have since pgsql 
>>>> 10. But I might be mistaken.
>>> Don't know about this, it keeps running on latest pg versions (we 
>>> are about to upgrade to 17.4, if I'm not wrong), but I'll ask
>>>>> I read about extensions like pg_squeeze, but I wonder if they are 
>>>>> still not dangerous for replication.
>>>>>
>>>> What's pgEdge take on that, I mean the bytea thing you are trying 
>>>> to achieve here.
>>> They are positive, it's they that suggested to do VACUUM FULL on 
>>> both nodes... I'm quite new to replication, so I'm searching some 
>>> advise here.
>>
>> As I told you, pgEdge logical replication (old 2ndquadrant BDR) != 
>> native logical replication. You may look here :
>>
>> https://github.com/pgEdge/spock
>>
>> If multi-master is not a must you could convert  to vanilla 
>> postgresql and focus on standard physical and logical replication.
>>
> No, multimaster is cool, but as I said, the project has been 
> discontinued and it's not a must anymore. This is the first step, 
> actually. We are planning to return to plain PostgreSQL, or CloudSQL 
> for PostgreSQL, using logical replication (that seems the most 
> reliable of the two). We created a test case for both the options, and 
> they seem to be OK for now, even if I have still to do adequate stress 
> tests. And when I'll do the migration, I'd like to be migrating plain 
> data only and leave blobs where they are.

as you wish. But this design has inherent data infra fragmentation as 
you understand.

Personally I like to let the DB take care of the data, and I take care 
of the DB, not a plethora of extra systems that we need to keep 
connected and consistent.

>>>>> Thanks for your help.
>>>>> Moreno.-
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>