Thread: running vacuumlo periodically?
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?
Thanks, Markus
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
> -----Ursprüngliche Nachricht----- > Von: Laurenz Albe <laurenz.albe@cybertec.at> > Gesendet: Donnerstag, 28. Januar 2021 17:39 > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql- > general@postgresql.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
On Thu, 2021-01-28 at 17:03 +0000, Zwettler Markus (OIZ) wrote: > 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. :-( You'll have to examine the application and see which tables reference the large objects. Looking for columns with type "oid" will help. I recommend that you add an "ON UPDATE OR DELETE" trigger that unlinks orphaned large objects. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com