pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate, truncate and vacuum
3+ messages / 3 participants
[nested] [flat]
* update, truncate and vacuum
@ 2007-07-26 22:36 Scott Feldstein <scott.feldstein@hyperic.com>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Feldstein @ 2007-07-26 22:36 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: update, truncate and vacuum
@ 2007-07-26 22:59 Dave Dutcher <dave@tridecap.com>
parent: Scott Feldstein <scott.feldstein@hyperic.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dave Dutcher @ 2007-07-26 22:59 UTC (permalink / raw)
To: 'Scott Feldstein' <scott.feldstein@hyperic.com>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: update, truncate and vacuum
@ 2007-07-26 23:17 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 3+ messages in thread
From: Luke Lonergan @ 2007-07-26 23:17 UTC (permalink / raw)
To: Scott Feldstein <Scott.Feldstein@hyperic.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-26 23:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-26 22:36 update, truncate and vacuum Scott Feldstein <scott.feldstein@hyperic.com>
2007-07-26 22:59 ` Dave Dutcher <dave@tridecap.com>
2007-07-26 23:17 Re: update, truncate and vacuum Luke Lonergan <LLonergan@greenplum.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox