Re: How to avoid database bloat

> >>AFAICT the vacuum is doing what it is supposed to, and the problem has
> >>to be just that it's not being done often enough.  Which suggests either
> >>an autovacuum bug or your autovacuum settings aren't aggressive enough.
> >
> >  -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10
> >
> >  That is autovacuum settings. Should be aggressive enough I think?
>
> Might e aggressive enough, but might not.  I have seen some people run
> -V 0.1.  Also you probably don't need -A that low.  This could an issue
> where analyze results in an inaccurate reltuples value which is
> preventing autovacuum from doing it's job.  Could you please run it with
> -d 2 and show us the relevant log output.

  Relevant parts are below. And we had to set so aggressive analyze because
otherwise planer statistics were getting old too fast. As I said table has
very
high turnover most of the records live here only for a few seconds.

  And one more question - anyway why table keeps growing? It is shown that
it occupies
<10000 pages and max_fsm_pages = 200000 so vacuum should keep up with the
changes?
Or is it too low according to pg_class system table? What should be the
reasonable value?

select sum(relpages) from pg_class;
  sum
-------
 77994
(1 row)

  Thanks,

  Mindaugas

[2005-06-03 09:30:31 EEST] DEBUG:   Performing: ANALYZE "queue"
[2005-06-03 09:30:31 EEST] INFO:      table name: database."queue"
[2005-06-03 09:30:31 EEST] INFO:         relid: 465440;   relisshared: 0
[2005-06-03 09:30:31 EEST] INFO:         reltuples: 98615.000000;  relpages:
6447
[2005-06-03 09:30:31 EEST] INFO:         curr_analyze_count: 39475111;
curr_vacuum_count: 30
953987
[2005-06-03 09:30:31 EEST] INFO:         last_analyze_count: 39475111;
last_vacuum_count: 30
913733
[2005-06-03 09:30:31 EEST] INFO:         analyze_threshold: 10861;
vacuum_threshold: 43700

[2005-06-03 09:31:11 EEST] DEBUG:   Performing: VACUUM ANALYZE "queue"
[2005-06-03 09:31:12 EEST] INFO:      table name: database."queue"
[2005-06-03 09:31:12 EEST] INFO:         relid: 465440;   relisshared: 0
[2005-06-03 09:31:12 EEST] INFO:         reltuples: 99355.000000;  relpages:
6447
[2005-06-03 09:31:12 EEST] INFO:         curr_analyze_count: 39480332;
curr_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO:         last_analyze_count: 39480332;
last_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO:         analyze_threshold: 10935;
vacuum_threshold: 50677