The only way to recover a large amount of space like that is to issue a vacuum full. Autovacuum will not issue a vacuum
full.
Vacuuming your tables without vacuum full does not recover space on the system. It marks recoverable space as usable.
Theidea with autovacuum is that most databases will exist in a steady state with respect to inserts/deletes/updates,
andregular vacuuming will help you maintain a steady size - autovacuum gives you an easy method of automatically
vacuumingthe tables when necessary. For more information about vacuuming in general, especially concerning vacuum full
andretrieving system space, you'll want to read:
http://www.postgresql.org/docs/8.0/interactive/maintenance.html#VACUUM-FOR-SPACE-RECOVERY
Jason Minion
jason.minion@sigler.com
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Szabolcs BALLA
Sent: Friday, April 28, 2006 7:03 AM
To: Robin Iddon; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Autovacuum probably not working?
Robin Iddon írta:
> Szabolcs BALLA wrote:
>
>> Hi,
>>
>> I delete 30M record from the db (8.1.0). I made query about size of
>> the tables, but after 1 day the size of the tables not changed.
>> (Autovacuum settings was original) So, I change the autovacuum
>> setting but after another day the size not changed.
>>
>> autovacuum_naptime = 10
>> autovacuum_vacuum_threshold = 1000
>> autovacuum_analyze_threshold = 500
>> autovacuum_vacuum_scale_factor = 0.1
>> autovacuum_analyze_scale_factor = 0.1 autovacuum_vacuum_cost_delay =
>> -1 autovacuum_vacuum_cost_limit = -1
>>
>>
>
> Check your FSM sizes in postgresql.conf ...
>
> Robin
>
>
shared_buffers = 153600
work_mem = 524288
maintenance_work_mem = 786432
max_fsm_pages = 510000
max_fsm_relations = 10000
Cost-Based Vacuum Delay is on default settings.
So, i thing my settings are ok. What do you think?
Thanks,
Szabek
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings