Re: Logical replication, need to reclaim big disk space

Στις 20/5/25 12:17, ο/η Moreno Andreo έγραψε:

>
>
> On 19/05/25 20:49, Achilleas Mantzios wrote:
>>
>> 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.
> Yeah, it's always been famous for its robustness, and that's why I 
> chose PostgreSQL more than 10 years ago, and, in spite of how a 
> "normal" user treats his PC, we never had corruption (only where 
> FS/disk were failing, but that's not PG fault)
>>>>> 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 ?
> If we could host on our own hardware I'd not be here talking. Maybe we 
> would have a 10-node full-mesh multimaster architecture with barman 
> backup on 2 separate SANs.
> But we are a small company that has to balance performance, 
> consistency, security and, last but not latter, costs. And margins are 
> tightening.
>>
>>>> **
>>>>
>>>>> 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.
> Since we cannot host on our hardware, the only thing is to keep an eye 
> on costs and migrate (yeah, more work) when it's becoming expensive. 
> Every solution is proprietary, if you want to run on cloud. Even the 
> VMs where PostgreSQL is running.
>>>>>>
>>>>>>
>>>>>>> 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.
> Normally it's run via COPY commands, I can throttle COPY or break it 
> in batches. At the moment, while the schema is offline, we disconnect 
> replication from the bytea tables, feed them, wait for checkpoints to 
> return normal and then resume replication between tables before 
> putting schema online. This is safe, even if far from being optimized. 
> It's a migration tool, it won't be used forever, just to move 
> customers from their current architecture to new cloud one.
>>>>>>> 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.
>>
> We followed this idea when the application (old version) was on 
> customer premises, so backups and operations were simple and getting 
> in trouble (e.g. customer deleting a directory from their PC) has 
> happened a very few times, just when they launched disk cleanup on 
> windows :-)
>
> Now we host a full cloud solution, so we got rid of many potential 
> problems generated by the end user, but bumped into other, as you 
> certainly imagine. We have to keep it consistent, fast, reliable, 
> keeping an eye on costs.
> You are right, but the more I was working with this solution, the more 
> I'm having the impression of dealing with something heavy, hard to 
> mantain because of these rarely-accessed files that sum up most of my 
> data. Maybe it's just my impression, maybe I need some expertise in an 
> area that's still quite new for me.
> At the moment that seems a good compromise between stability and 
> costs. Maybe in one year I'll be in your position (considering getting 
> everything back), but for now we are thinking forward in that way.
>
Makes perfect sense.
> This been said, the original question :-)
> Would be VACUUM FULL a risky operation? Has it to be done on all 
> nodes, obviously in a low-traffic and low-access timing (night)?
VACUUM affects the physical blocks. In a physical streaming replication 
scenario that might (or not) potentiallyt affect read-only queries on 
the hot standby (depending on usage and settings). Normally I cannot see 
how a VACUUM (plain or FULL) would interact with logical replication in 
any way. But again, since you run PgEdge specific, you have to ask them.
>>
>>>>>>> Thanks for your help.
>>>>>>> Moreno.-
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>