Re: Logical replication, need to reclaim big disk space

On 20/05/25 12:58, Achilleas Mantzios wrote:
>
> Στις 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. This makes me think I misread or misinterpreted something. They
already suggested me that to use VACUUM FULL on both nodes, but that
"thing" I read (or I'm convinced to have) made me think twice before
crashing everything. Two experts' according words is quite enough for me.
I will start this evening and see what happens.

Thanks for the help and the very interesting discussion.
>>>
>>>>>>>> Thanks for your help.
>>>>>>>> Moreno.-
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>