Thread: Autovacuum, too often?

Hi chaps,

Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the
developersand I intend to eradicate them eventually (the tables, not the developers). 

These tables are constantly being autovacuumed, approximately once a minute, it's not causing any problem and seems to
bekeeping them vacuumed.  But I'm constantly re-assessing our autovacuum settings to make sure they're adequate, and no
matterhow much I read up on autovacuum I still feel like I'm missing something. 

I just wondered what peoples opinions were on handling this sort of vacuuming? Is that too often?

The general autovaccum settings set more for our central tables are threshold 500, scale_factor 0.2. I guess I could
setspecific settings for the tables in pg_autovacuum, or I could exclude them in there and run a vacuum from cron once
aday or something. 

Here's a typical log message:

2008-09-19 11:40:10 BST [12917]: [1-1]: [user=]: [host=]: [db=]:: LOG:  automatic vacuum of table
"TEMP.reports.online":index scans: 1 
        pages: 21 removed, 26 remain
        tuples: 2356 removed, 171 remain
        system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec

Any comments would be appreciated.

> From: Harald Armin Massa <haraldarminmassa@gmail.com>
> Hello Glyn,
>
> Our legacy apps have some permanent tables that they use
> for tempory data
> > and constantly clear out, I've kicked the
> developers and I intend to
> > eradicate them eventually (the tables, not the
> developers).
> >
>
> and what is the problem with this usage? That is a
> perfectly valid thing to
> do; PostgreSQL can handle that for centuries; no need to
> kick the developers
> :)
>

In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their
application...

Glyn Astill <glynastill@yahoo.co.uk> wrote:
>
> > From: Harald Armin Massa <haraldarminmassa@gmail.com>
> > Hello Glyn,
> >
> > Our legacy apps have some permanent tables that they use
> > for tempory data
> > > and constantly clear out, I've kicked the
> > developers and I intend to
> > > eradicate them eventually (the tables, not the
> > developers).
> > >
> >
> > and what is the problem with this usage? That is a
> > perfectly valid thing to
> > do; PostgreSQL can handle that for centuries; no need to
> > kick the developers
> > :)
> >
>
> In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their
application...

*shrug*  Our experience has been that PostgreSQL is much better at sorting
than anything we could write with our high-pressure deadlines.  Additionally,
information sometimes needs to be truncated (with LIMIT) after it's
sorted, so having PG do all the work results in less network bandwidth
and less memory usage by the application.

Maybe that's not _always_ the right answer, but it seems to be a good
answer 99% of the time.  Sounds like your developers are using the
database for what it was intended for, instead of just doing single
row selects like a lot of amateurs I've come across.

--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

> From: Bill Moran <wmoran@collaborativefusion.com>

>
> Maybe that's not _always_ the right answer, but it
> seems to be a good
> answer 99% of the time.  Sounds like your developers are
> using the
> database for what it was intended for, instead of just
> doing single
> row selects like a lot of amateurs I've come across.
>

In some places I agree it's totally valid, but in a lot of cases here it's just unnecessary.  They have a set of really
flexiblepre written routines to sort datasets in the application, and I'm talking really stupid use here - like having
asmall list of items in an array where an array sort could be done, but instead writing it all back and reading it
again.constantly.