Re: How to avoid database bloat

> Looked like pg_autovacuum is operating as expected.  One of the annoying
> limitations of pg_autovacuum in current releases is that you can't set
> thresholds on a per table basis.  It looks like this table might require
> an even more aggressive vacuum threshold.  Couple of thoughts, are you
> sure it's the table that is growing and not the indexes? (assuming this
> table has indexes on it).

  Yes I am sure (oid2name :) ).

> >  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?
> >
> >
>
> Does the table keep growing?  Or does it grow to a point an then stop
> growing?  It's normal for a table to operate at a steady state size that
> is bigger that it's fresly "vacuum full"'d size.  And with -V set at 0.5
> it should be at a minimum 50% larger than it's minimum size.  Your email
> before said that this table went from 20M to 70M but does it keep
> going?  Perhaps it would start leveling off at this point, or some point
> shortly there-after.

  Yes it keeps growing. And the main problem is that performance starts to
suffer from that. Do not forget that we are talking about 100+ insert/
update/select/delete cycles per second.

> Anyway, I'm not sure if there is something else going on here, but from
> the log it looks as though pg_autovacuum is working as advertised.

  Something is out there :). But how to fix that bloat? More aggressive
autovacuum settings? Even larger FSM?
  Do not know if that matters but database has very many connections to
it (400-600) and clients are doing mostly asynchronous operations.

  How to find out where this extra space gone?

  Thanks,

  Mindaugas