"Healing" a table after massive updates

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: "Healing" a table after massive updates
Date: 2008-09-11 14:01:07
Message-ID: 0836165E8EE50F40A3DD8F0D8713726701056C1D@azsmsx421.amr.corp.intel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi:

I have a job that loads a large table, but then has to "update" about
half the records for various reasons. My perception of what happens on
update for a particular recors is...

- a new record will be inserted with the updated value(s).

- The old record is marked as being obselete.

- Not sure what happens to index elements that pointed to the original
(now obselete) record. Is it updated to point directly at the newly
inserted record? Or does it use the obselete record as a "link" to the
newly inserted record?

My concern is that the resulting table is not in optimal shape for
queries. I would like to get rid of the obseleted records (vacuum I
believe) but also "heal" the table in terms of filling in the holes left
where those deleted records used to be (will gather more records per
disk block read if record density on disk is greater). Is there a way
to do this?

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gauthier, Dave 2008-09-11 14:16:22 Re: "Healing" a table after massive updates
Previous Message Peter Eisentraut 2008-09-11 13:58:38 Re: xml queries & date format