dead tuples and VACUUM

From: Dima Tkach <dmitry(at)openratings(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: dead tuples and VACUUM
Date: 2003-05-31 18:15:04
Message-ID: 3ED8F128.4060205@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, everybody!

I wanted to ask a few questions about VACUUM, and dead tuples in general

Suppose, I have a table with a few million rows, and every now and then
(say, monthly) every row in the table is updated.

First of all, it is my understanding that this monthly job will
double the size of the table (create a copy of every tuple it is
updating). Is that right?

Now, if I run VACUUM on that table, it is supposed to reclaim those dead
tuples.. Will it actually? I remember some discussions about 7.2, where
it was mentioned that plain (not FULL) VACUUM doesn't move rows between
pages (so, it seems to me, that if every row is updated, plain vacuum is
useless). Is it any better in 7.3? Or does it still have to be VACUUM
FULL?

And finally, if I do *not* run VACUUM, and let it just sit there with
the doubled number of tuples, what will happen next month, when every
row gets updated again? Will it be able to reuse the dead tuples then,
or will it just keep creating the new ones?

My problem is that, as I described above, I am afraid that plain VACUUM
just won't help, and running VACUUM FULL takes *days* on my database,
and we just cannot afford that much downtime (in fact, it is quicker,
for some reason to dump and reload the table then to vacuum full it!).

I'd love to hear what you guys know about it!

Thanks a lot!

Dima

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-05-31 18:25:38 Re: default locale considered harmful? (was Re: [GENERAL]
Previous Message Bruce Momjian 2003-05-31 18:00:57 Re: psql: FATAL: Missing or erroneous pg_hba.conf file