Re: database file size bloat

On Fri, 14 Apr 2000, Matthew Arnison wrote:

> the bloat is a big problem. i just checked it again, and the db has
> balloooned to 20 megs again, with i think 2650 unused pages. this is after
> vacuuming it last night. i guess we need to setup the vacuum script to run
> every hour. i am worried about this locking out users during the
> vacuuming, although i guess if it happens more often, it should take less
> time.
> 
> meanwhile, as for upgrading, i think i'll try 6.5.3 first.
> 
> version 7 is still in beta. is it atleast as stable as 6.5.3? is it
> atleast as fast as 6.5.3? 

it is generally felt in the developer lists that each release is generally
more stable, and faster, then the previous one.  I just put v7.0beta5 in
place as the backend to the PostgreSQL Search function off of
http://www.postgresql.org and with >5million tuples loaded already in the
dictionary table, queries are still taking <1min to complete ...






 > 
> this is a live site allright.
> 
> thanks for your advice,
> matthew.
> 
> On Thu, 13 Apr 2000, Ed Loehr wrote:
> 
> > Matthew Arnison wrote:
> > > 
> > > three times now this week (on two different servers) the raw database on
> > > disk has ballooned in size, from about 10 megs to 50 megs in two cases,
> > > and from about 10 megs to 250 megs in another case.
> > > 
> > > a VACUUM VERBOSE ANALYZE; cleans it back down to the proper size, but
> > > meanwhile all the queries take half a minute, instead of less than a
> > > second. and our load average skyrockets.
> > 
> > Hi Matthew,
> > 
> > I have no explanation for the bloat, but it is a well-known "postgresqlism"
> > that you should consider running vacuum analyze at least nightly, possibly
> > more frequently.  [I run it hourly.]
> > 
> > Also, there are about 300 reasons to consider upgrading to 7.0, most having
> > to do with bug fixes and performance improvements.  Unfortunately, there
> > may be a few incompatibilities (particularly in some pl/pgsql
> > incantations), so don't assume a seamless upgrade.
> > 
> > Regards,
> > Ed Loehr
> > 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org