Does VACUUMLO result in lots of writes in the file system?

Hi everyone,

I have some Postgres 11.16 hosted by some Ubuntu 18.04 LTS using
BTRFS as file system. Am running a script named btrfs-auto-snapshot
creating snapshots on a regular basis, like every 15 minutes, hourly
etc.

My databases are used by some custom application storing files as-is
as large objects in those databases for historical reasons. Those
files might be some KiB, some MiB or even a few GiB in size, though
the latter is pretty seldom. That custom app provides some cleanup
daemon doing three things:

1. checking some flag if some file has been deleted or not and if so,
deleting some row with the corresponding file info.

2. as that doesn't remove the LO itself, the shell app VACUUMLO is
called after removing all rows to remove all LOs.

3. after everthing finished, VACUUM ANALYZE is called on
pg_largeobject to updates stats. Though, it's important to note that
FULL is NOT used regularly.

During the last few weeks I see that lots of free space gets consumed
during those operations, dozens of GiBs for some reason. Up to a point
where the file system free space gets exhausted, which shouldn't
happen too easily according to the database sizes. Many of them aren't
even the half of the size of free space.

I know that VACUUM FULL rewrites files and might temporarily consume
as much space as a database is in size, maybe a little more. I don't
have that much free space for some databases, so am not doing this.

But what's with the tool VACUUMLO? Does it rewrite some files as well,
like only some instead of all of those 1 GiB data files? I always
expected that it manages flags and free blocks infos only, like if LOs
are deleted manually using SQL, without actually rewriting data files.

In the end, there's some process creating dozens of GiBs of data which
are preserved by the automatically created snapshots and I don't
understand where this is coming from. After deleting those snapshots
manually, the free space is back again.

It might be additionally important to note that regarding runtime logs
of my cleanup daemons, snapshots always happen BEFORE and AFTER those
daemons only, not in the middle of their processing. So it looks to me
that during the cleanup really new files get created which are used
afterwards. If it was temporary data only, that shouldn't show up too
much in the snapshots or get deleted with the frequently ones.

Any further ideas? Thanks!

Mit freundlichen Grüßen

Thorsten Schöning

--
AM-SoFT IT-Service - Bitstore Hameln GmbH
Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: Thorsten.Schoening@AM-SoFT.de
Web:    http://www.AM-SoFT.de/

Tel:   05151-  9468- 0
Tel:   05151-  9468-55
Mobil:  0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen jederzeit zur Verfügung.

Mit freundlichen Grüßen,

Thorsten Schöning


Telefon: +49 (0)515 94 68 - 0
Fax:
E-Mail: TSchoening@am-soft.de

AM-Soft IT-Service - Bitstore Hameln GmbH
Brandenburger Straße 7c
31789 Hameln

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen und ist ausschliesslich für den
Adressatenbestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Wenn Sie
nichtder richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender
undvernichten Sie diese E-Mail. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede
Veröffentlichung,Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Massnahmen im Vertrauen
auferlangte Information untersagt.  

This e-mail may contain confidential and/or privileged information and is intended solely for the addressee. Access to
thisemail by anyone else is unauthorized. If you are not the intended recipient (or have received this e-mail in error)
pleasenotify the sender immediately and destroy this e-mail. If you are not the intended recipient, any disclosure,
copying,distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.  

Hinweise zum Datenschutz: bitstore.group/datenschutz