AW: running vacuumlo periodically - Mailing list pgsql-general

From Zwettler Markus (OIZ)
Subject AW: running vacuumlo periodically
Date
Msg-id a29c8b4f5cb24f4a8df5aa09926071a0@zuerich.ch
Whole thread Raw
Responses Re: AW: running vacuumlo periodically
List pgsql-general
I run "vacuumlo" in batches (-l) which worked well.

I found table "pg_catalog.pg_largeobjects" to be massively bloated afterwards.

I tried "vacuum full pg_catalog.pg_largeobjects" but run out of diskspace (although having 250G diskspace free;
databasesize = 400G).
 


Question: 
Will "vacuum full pg_catalog.pg_largeobjects" need less diskspace when "maintenance_work_mem" is increased?




> -----Ursprüngliche Nachricht-----
> Von: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
> Gesendet: Donnerstag, 28. Januar 2021 18:04
> An: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@postgresql.org
> Betreff: AW: running vacuumlo periodically?
> 
> > -----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
> 
> 


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Foreign table performance issue / PostgreSQK vs. ORACLE
Next
From: Laurenz Albe
Date:
Subject: Re: AW: running vacuumlo periodically