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

vacuuming and reindexing

From: "Tena Sakai" <tsakai(at)gallo(dot)ucsf(dot)edu>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: vacuuming and reindexing
Date: 2009-03-20 22:03:58
Message-ID: FE44E0D7EAD2ED4BB2165071DB8E328C04E84A32@egcrc-ex01.egcrc.org (view raw or flat)
Thread:
Lists: pgsql-admin
Hi Everybody,

I have a table that I add rows every evening.
A rule I have for this table is that no row
ever gets deleted.  It has 600+ million rows.

The last time I did it, it took nearly 4 hours
to vacuum and 13 hours 40 minutes to reindex.

My rudimental understanding of vacuuming is
that when the rows get "deleted" it is only
marked as "deleted" but the data is left alone
until the act of vacuuming takes place.  The
vacuuming really gets rid of rows and pushes
the data in such the way there is no "hole."
And therefore after vacuuming, it is necessary
to reindex (and analyze) the table.

Is this a correct understanding?

If it is (and as new rows get added new indexes
are also built for the new rows), the fact that
there is no deletion means there is no necessity
for vacuuming this particular table?

I appreciate any thoughts on this matter.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu



Responses

pgsql-admin by date

Next:From: DMDate: 2009-03-20 22:40:39
Subject: pg_restore error - Any Idea?
Previous:From: Caleb CushingDate: 2009-03-20 19:47:16
Subject: Re: pg_dump formatting

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