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

-----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