Re: "Healing" a table after massive updates

On Thu, 2008-09-11 at 07:01 -0700, Gauthier, Dave wrote:
> 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?

Depends on the version of Postgres. Prior to 8.3, the obsolete tuples
and index entries are dead. In 8.3, the updates are HOT updates, it
will not leave the dead tuples or index. 8.3 might be a big help for
you. It could remove the need to vacuum this table entirely.

> 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?

Regular VACUUM is the correct operation to get rid of the dead tuples.

If you want to compact the the table, you either need to use CLUSTER or
VACUUM FULL + REINDEX.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.