Re: Re: Tables grow in size when issuing UPDATEs! Why??

> On Wed, 28 Mar 2001, Daniel ?erud wrote:
>
> > Hi,
> > I have noticed a strange thing when dealing with postgreSQL.
> >
> > Foreword: this is all about postgres tables increasing in
> > size extremely fast when doing updates. My sainness is
> > decreasing in approximatly the same speed. Please look.
> > 10 times is enough for it to get 16k big. I realised that it
> > increases in size EXTREMELY fast when only UDPATing. I find
> > this strange, as I update timestamp in my real database
> > often and in 5 minutes it is 2 megabytes big and containing
> > only 18 rows! (NO BULLSHIT!!)
> >
> > Please help me with this I am quite desperate...
>
> Postgres uses a non-overwriting storage manager. You are
going
> to want to vacuum the table regularly to cut the table back
> to just rows that are visible.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
unregister command
> (send "unregister YourEmailAddressHere" to
majordomo(at)postgresql(dot)org)
>

I noticed it greatly affects the speed.
Is there some way you can turn that feature off as I am
doing speed analysis of postgresql (well, i'm kinda
tweaking). How often should you run it? I guess it is bad to
run it after every update.

Another thought: doing _only_ inserts and selects wont
affect the speed of the db?

Anyway, thanks alot! I will read up on it right away.

Daniel Åkerud

At 09:26 PM 28-03-2001 GMT, Daniel ?erud wrote:
>
>I noticed it greatly affects the speed.
>Is there some way you can turn that feature off as I am
>doing speed analysis of postgresql (well, i'm kinda
>tweaking). How often should you run it? I guess it is bad to
>run it after every update.
>
>Another thought: doing _only_ inserts and selects wont
>affect the speed of the db?
>

Yeah. I also noticed that update + indexed select repeatedly on the same
row slows down over time, whereas insert + indexed select doesn't seem to
slow down for some reason. Maybe it has something to do with the indexes,
or the MVCC thingy - scanning through multiple expired rows.

The speed goes down quite significantly from the initial peak, so
benchmarks on a pristine database aren't really reliable for real world
scenarios unless you vacuum every 10000 updates or so ;). But it's a shame
because Postgres is blazingly fast at the start.

Link.