Vacuum and Large Objects

Hi

I am having problems recovering storage from a Postgres 9.05 database that
is used to hold large XML blocks for a week, before they are archived off
line.

The main tables are partitioned in daily partitions, and these are easy to
manage, however the DB keeps growing despite using Vacuum (daily at 0700)
and autovacuum (this does not seem to run, although the process is running).
The system is insert only, and partitions are dropped when over 7 days of
age.

I believe the issue lies with pg_largeobject, it is split between 88 files
of approx. 1G each.

The Postgres settings are default, EXCEPT

grep ^[a-z] postgresql.conf

listen_addresses = '*' # what IP address(es) to listen on;

port = 5432 # (change requires restart)

max_connections = 1000 # (change requires restart)

shared_buffers = 256MB # min 128kB

work_mem = 4MB # min 64kB

maintenance_work_mem = 256MB # min 1MB

vacuum_cost_delay = 20ms # 0-100 milliseconds

checkpoint_segments = 32 # in logfile segments, min 1, 16MB
each

checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 -
1.0

checkpoint_warning = 60s # 0 disables

archive_mode = off # allows archiving to be done

constraint_exclusion = partition # on, off, or partition

log_destination = 'stderr' # Valid values are combinations of

logging_collector = on # Enable capturing of stderr and csvlog

silent_mode = on # Run server silently.

log_checkpoints = on

log_line_prefix = '%t %d %u ' # special values:

log_statement = 'none' # none, ddl, mod, all

track_activities = on

track_counts = on

autovacuum = on # Enable autovacuum subprocess? 'on'

log_autovacuum_min_duration = 250 # -1 disables, 0 logs all actions
and

autovacuum_max_workers = 3 # max number of autovacuum
subprocesses

autovacuum_naptime = 3min # time between autovacuum runs

autovacuum_vacuum_threshold = 500 # min number of row updates before

autovacuum_analyze_threshold = 100 # min number of row updates before

autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before
vacuum

autovacuum_analyze_scale_factor = 0.05 # fraction of table size before
analyze

autovacuum_vacuum_cost_delay = 5ms # default vacuum cost delay for

autovacuum_vacuum_cost_limit = 200 # default vacuum cost limit for

statement_timeout = 0 # in milliseconds, 0 is disabled

datestyle = 'iso, dmy'

lc_messages = 'en_GB.UTF-8' # locale for system error
message

lc_monetary = 'en_GB.UTF-8' # locale for monetary
formatting

lc_numeric = 'en_GB.UTF-8' # locale for number
formatting

lc_time = 'en_GB.UTF-8' # locale for time formatting

default_text_search_config = 'pg_catalog.english'

Besides running VACUUM FULL pg_largeobject;, is there a way I can get
autovacuum to start and clear this up?

All the best

Simon

Simon Windsor

Eml: <mailto:simon(dot)windsor(at)cornfield(dot)org(dot)uk> simon(dot)windsor(at)cornfield(dot)org(dot)uk

Tel: 01454 617689

Mob: 07590 324560

"There is nothing in the world that some man cannot make a little worse and
sell a little cheaper, and he who considers price only is that man's lawful
prey."