Re: When do I Vacuum ?

Andrew Gould wrote:
> One way of tracking changes would be a last_change
> date field which would be updated every time a row was
> updated. Of course, if the database is big enough
> that your worried about resource utilization by
> vacuum, then you might not like the additional space
> consumption of a purely administrative field.

You can activate the per table statistics collection in the
postgres config file. You'll then find the number of
INSERT/UPDATE/DELETEed tuples as well as number of sequential
and index scans done per table (since the last postmaster
restart by default) in pg_stat_user_tables.

Now you setup a table for remembering a timestamp, the
current row count and these access counters per table. A
little script run by cron periodically remembers all this
info.

This historical data will give you a detailed, per table
access profile over time, so you can setup different
vacuuming schedules per table. Vacuum huge tables with low
update rate less frequent than small tables with high update
rate, do the vacuuming when these tables get the lowest
access, and you'll get the most out of your server.

I wanted that statistics collector not just for the fun of
doing it. For Joe-User's little WebDB all this is surely
overkill. But somebody running a serious server with a
complex schema and a couple hundred MB of data might consider
it beeing worth done.

One of these days I will think about a standard set of
analyzis tools we can add to contrib. Stay tuned and happy
vacuuming.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com