From: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | AW: running vacuumlo periodically? |
Date: | 2021-01-28 17:03:32 |
Message-ID: | 7bd95c7b8c5d451c9c1cac538984ea0b@zuerich.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> Gesendet: Donnerstag, 28. Januar 2021 17:39
> An: Zwettler Markus (OIZ) <Markus(dot)Zwettler(at)zuerich(dot)ch>; pgsql-
> general(at)postgresql(dot)org
> Betreff: Re: running vacuumlo periodically?
>
> On Thu, 2021-01-28 at 13:18 +0000, Zwettler Markus (OIZ) wrote:
> > Short question. Is it recommended - or even best practice – to run vacuumlo
> periodically as a routine maintenance task?
> >
> > We don't do it. I think if this would be recommended it would have been
> implemented as an autotask like autovacuum. No?
>
> It is recommended to run it regularly if
> - you are using large objects
> - you don't have a trigger in place that deletes large objects that you don't
> need any more
>
> Only a small minority of people do that, so it wouldn't make sense to automatically
> run that on all databases.
>
> Avoid large objects if you can.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
[Zwettler Markus (OIZ)]
We didn't recognize that an application is using large objects and didn't delete them.
Now we found >100G dead large objects within the database. :-(
Is there any _GENERIC_ query which enables monitoring for orphaned objects (dead LO)?
select oid from pg_largeobject_metadata m where not exists
(select 1 from ANY_EXISTING_TABLE_WITHIN_THE_DATABASE where m.oid = ANY_COLUMN_CONTAINING_OIDs);
check_postgres.pl doesn't have any generic check for it. :-(
Thanks, Markus
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2021-01-28 18:18:26 | Re: vacuum is time consuming |
Previous Message | Bruce Momjian | 2021-01-28 16:50:56 | Re: Postgres freelancing sites |