Autovacuum not vacuuming pg_largeobject

Denis Gasparin <denis@edistar.com> writes:

I'm a bit concerned about the autovacuum daemon.
Today I runned a vacuum full during a normal maintainance task and I
noticed that the size of pg_largeobject
decreased from 14GB to 4GB...

Every night we have a procedure that deletes large object no more
referenced using the vacuumlo program.

This program issues delete commands to the pg_largeobject table in order
to erase the rows of the los no more referenced.

Autovacuum is up and running... but now i'm thinking it doesn't examine
system tables such as pg_largeobject...

Am I wrong?

Yeah, you're wrong. The difference is that plain vacuum does not try
very hard to reduce the length of a table file --- it just frees up
space within the file for reuse. vacuum full will actually move things
from the end of the file to free space nearer the head of the file,
so that it can shorten the file.

What I suspect the above observations really prove is you don't have
max_fsm_pages set high enough, and so pg_largeobject was bloating because
the free space was being forgotten instead of reused.

regards, tom lane

Yeah, you're wrong. The difference is that plain vacuum does not try
very hard to reduce the length of a table file --- it just frees up
space within the file for reuse. vacuum full will actually move things
from the end of the file to free space nearer the head of the file,
so that it can shorten the file.

What I suspect the above observations really prove is you don't have
max_fsm_pages set high enough, and so pg_largeobject was bloating because
the free space was being forgotten instead of reused.

I tried to issue the vacuum command on one of my database and i got the
following log messages:

LOG: max_fsm_relations(1000) equals the number of relations checked
HINT: You have at least 1000 relations. Consider increasing the
configuration parameter

I suspect I must increase max_fsm_relations.
The value of max_fsm_pages is 20000 (the default value).
I suspect I must change this to a higher value... but how high should
this value be?

Thank you in advance,
Denis

Denis Gasparin wrote:

Yeah, you're wrong. The difference is that plain vacuum does not try
very hard to reduce the length of a table file --- it just frees up
space within the file for reuse. vacuum full will actually move things
from the end of the file to free space nearer the head of the file,
so that it can shorten the file.

What I suspect the above observations really prove is you don't have
max_fsm_pages set high enough, and so pg_largeobject was bloating because
the free space was being forgotten instead of reused.

I tried to issue the vacuum command on one of my database and i got the
following log messages:

LOG: max_fsm_relations(1000) equals the number of relations checked
HINT: You have at least 1000 relations. Consider increasing the
configuration parameter

I suspect I must increase max_fsm_relations.
The value of max_fsm_pages is 20000 (the default value).
I suspect I must change this to a higher value... but how high should
this value be?

First increase max_fsm_relations to more than the number of tables,etc
in your system. Then, after it's running a while, do the same vacuum
command and see if it gives you a hint to increase that parameter too.