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@Yahoo.com #



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