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

Re: Why do things slow down without a VACUUM?

From: GH <grasshacker(at)over-yonder(dot)net>
To: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why do things slow down without a VACUUM?
Date: 2001-04-30 02:17:43
Message-ID: 20010429211743.B14349@over-yonder.net (view raw or flat)
Thread:
Lists: pgsql-general
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(at)postgresql(dot)org)

In response to

Responses

pgsql-general by date

Next:From: Lincoln YeohDate: 2001-04-30 03:23:57
Subject: Re: Why do things slow down without a VACUUM?
Previous:From: Lincoln YeohDate: 2001-04-30 01:14:04
Subject: Why do things slow down without a VACUUM?

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