On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango <elanelango@xxxxxxxxx> wrote: [...] > With the above query pattern with intensive updates and deletes, I need to > do some aggressive vacuuming. > > Current strategy:I am running with default autovacuum settings (postgres > 9.1.9) and I tried doing a 'vacuum full' for the 8 high traffic tables > (Tables1 and Tables3) every night. But after a point, the 'vacuum full's > started timing out (with 4min timeout) every night. I think this is because > the table is growing bigger (~5GB) and doing a vacuum full every night is > probably not feasible. > > Going with the default autovacuum settings and not doing 'vacuum full' at > all is also not enough for my usecase. Whenever vacuum full succeeded every > night, it did seem to reclaim a considerable amount of space. So I assume, > autovacuum is not able to reclaim all space. It is able to reclaim space at the end of the table (truncate empty (no live tuples) pages in the tail), and depending on the autovacuum settings you might get the result that is very close to the full vacuum. Keep in mind that to reuse the space of deleted or updated tuples (on the low level (tuple level) any update is roughly delete+insert) vacuum/autovacuum should mark them as available first. So, to avoid bloat, you need your vacuum to manage this faster than your logic produces new dead tuples. The allowed bloat fraction can be controlled with _threashold and _scale_factor parameters. > What approach should I take? Do I require 'vacuum full'? What autovaccum > settings should I tweak so that I can avoid vacuum full, if possible, and > maintain a steady state without bloating the tables? I do not think you need vacuum full here. Start with the settings below. They are pretty aggressive, so, after some time, by analyzing logs (log_autovacuum_min_duration = 0 sets to log all autovacuum calls), you might came to a decision to ease these settings if it will be needed. log_autovacuum_min_duration = 0 autovacuum_max_workers = 5 autovacuum_naptime = 10s autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 5ms -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray.ru@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
