From: "Dave Dutcher" <dave@tridecap.com>
To: "'Scott Feldstein'" <scott.feldstein@hyperic.com>,
	<pgsql-performance@postgresql.org>
References: <B195EEA2-7B5D-444F-8F48-46BB88FCFFA8@hyperic.com>
Subject: Re: update, truncate and vacuum
Date: Thu, 26 Jul 2007 17:59:58 -0500
Message-ID: <073e01c7cfd8$b0d9d560$2e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcfP1sIdlTS8ILn+T+qVCOqhqSHdfgAAXttg
In-Reply-To: <B195EEA2-7B5D-444F-8F48-46BB88FCFFA8@hyperic.com>

> From: Scott Feldstein
> Subject: [PERFORM] update, truncate and vacuum
> 
> Hi,
> I have a couple questions about how update, truncate and 
> vacuum would work together.
> 
> 1) If I update a table foo (id int, value numeric (20, 6)) 
> with update foo set value = 100 where id = 1
> 
> Would a vacuum be necessary after this type of operation 
> since the updated value is a numeric? (as opposed to a sql 
> type where its size could potentially change i.e varchar)

Yes a vacuum is still necessary.  The type doesn't really matter.  Postgres
effectively does a delete and insert on all updates.

 
> 2) After several updates/deletes to a table, if I truncate 
> it, would it be necessary to run vacuum in order to reclaim the space?

No a vacuum is not necessary after a truncate because the whole data file is
deleted once a truncate commits.  There aren't any dead rows because there
aren't any rows.

Dave