Обсуждение: 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)

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?

thanks,
Scott

1) Yes

All rows are treated the same, there are no in place updates.

2) No

Truncate recreates the object as a new one, releasing the space held by the old one.

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:   Scott Feldstein [mailto:scott.feldstein@hyperic.com]
Sent:   Thursday, July 26, 2007 06:44 PM Eastern Standard Time
To:     pgsql-performance@postgresql.org
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)

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?

thanks,
Scott

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

> 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