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@xxxxxxxxxx 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@xxxxxxxxxx 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 Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten 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 auf erlangte Information untersagt. This e-mail may contain confidential and/or privileged information and is intended solely for the addressee. Access to this email by anyone else is unauthorized. 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. 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
