On Mon, Apr 30, 2001 at 09:14:04AM +0800, some SMTP stream spewed forth:
> Hi,
>
> Why do things slow down after a lot of updates without a VACUUM? This
> slow-down doesn't seem to happen as much with a lot of inserts.
>
> Don't the indexes know which is the valid updated row? Or does Postgresql
> still have to search for it amongst deleted/invalid rows?
>
> I can understand why the database would bloat up and/or slow down slightly
> without a vacuum. But why such a significant slowdown? So much so that
> people have to vacuum every two hours.
As it seems you know, PostgreSQL "leaves behind" the stagnant rows after
an UPDATE or DELETE; it merely sets a flag (IIRC) to that effect.
As more and more stagnant data accumulates, PostgreSQL has to dig through
more and more data on the disk (or in cache) which (IIRC) causes the
slowdown. (This is also true with regard to indexes/indices and stagnant
data.)
VACUUMS are good. :)
Actually, having to vacuumn is a Very Bad Thing.
Somebody should do something about it, I suppose.
The online (http://www.postgresql.org/) documentation has some
information about why the old data are not cleaned out on the fly.
Cheers,
dan
>
> Cheerio,
> Link.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)