Date: Mon, 18 Jul 2022 10:14:43 +0200
From: =?utf-8?Q?Thorsten_Sch=C3=B6ning?= <tschoening@am-soft.de>
Organization: AM-SoFT IT-Service - Bitstore Hameln GmbH
Message-ID: <83405677.20220718101443@am-soft.de>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Does VACUUMLO result in lots of writes in the file system?
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/83405677.20220718101443%40am-soft.de>
Precedence: bulk

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=C3=BC=C3=9Fen

Thorsten Sch=C3=B6ning

--=20
AM-SoFT IT-Service - Bitstore Hameln GmbH
Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister f=C3=BCr IT u=
nd 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 Ham=
eln
AG Hannover HRB 221853 - Gesch=C3=A4ftsf=C3=BChrer: Janine Galonska


F=C3=BCr R=C3=BCckfragen stehe ich Ihnen jederzeit zur Verf=C3=BCgung.=20

Mit freundlichen Gr=C3=BC=C3=9Fen,=20

Thorsten Sch=C3=B6ning


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

AM-Soft IT-Service - Bitstore Hameln GmbH
Brandenburger Stra=C3=9Fe 7c
31789 Hameln

Diese E-Mail enth=C3=A4lt vertrauliche und/oder rechtlich gesch=C3=BCtzte I=
nformationen und ist ausschliesslich f=C3=BCr den Adressaten bestimmt. Jegl=
icher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist=
 untersagt. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irr=
t=C3=BCmlich erhalten haben, informieren Sie bitte sofort den Absender und =
vernichten Sie diese E-Mail. Sollten Sie nicht der f=C3=BCr diese E-Mail be=
stimmte Adressat sein, ist Ihnen jede Ver=C3=B6ffentlichung, Vervielf=C3=A4=
ltigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Massnah=
men im Vertrauen auf erlangte Information untersagt.=20

This e-mail may contain confidential and/or privileged information and is i=
ntended solely for the addressee. Access to this email by anyone else is un=
authorized. If you are not the intended recipient (or have received this e-=
mail in error) please notify the sender immediately and destroy this e-mail=
=2E If you are not the intended recipient, any disclosure, copying, distrib=
ution or any action taken or omitted to be taken in reliance on it, is proh=
ibited and may be unlawful.=20

Hinweise zum Datenschutz: bitstore.group/datenschutz