Skip site navigation (1) Skip section navigation (2)

Re: Avoiding vacuum full on an UPDATE-heavy table

From: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
To: "Bill Montgomery" <billm(at)lulu(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Avoiding vacuum full on an UPDATE-heavy table
Date: 2004-05-21 19:22:40
Message-ID: 54139.192.154.91.225.1085167360.squirrel@192.154.91.225 (view raw or flat)
Thread:
Lists: pgsql-performance
> Is there any way to avoid doing a periodic VACUUM FULL on this table,
> given the fairly radical usage pattern? Or is the (ugly) answer to
> redesign our application to avoid this usage pattern?

Yes, you should be able to doing avoid periodic VACUUM FULL.  The problem
is that your table needs to be vacuumed MUCH more often.  What should
happen is that assuming you have enough FSM space allocated and assuming
you vacuum the "right" amount, your table will reach a steady state size. 
As you could see your from you vacumm verbose output your table was almost
entriely dead space.

pg_autovacuum would probably help as it monitors activity and vacuumus
tables accordingly.  It is not included with 7.3.x but if you download it
and compile yourself it will work against a 7.3.x server.

Good luck,

Matthew



In response to

Responses

pgsql-performance by date

Next:From: Marty ScholesDate: 2004-05-21 20:10:56
Subject: Re: PostgreSQL caching
Previous:From: Neil ConwayDate: 2004-05-21 19:02:32
Subject: Re: PostgreSQL caching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group