From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Large update and disk usage |
Date: | 2012-04-13 15:35:48 |
Message-ID: | 20120413153548.GA744@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Steve Horn <steve(at)stevehorn(dot)cc> wrote:
> (Postgres 9.1 on CentOS)
>
> Performing an update to two columns on a table with 40 million records, all in
> one transaction.
>
> The size of the table on disk (according to pg_relation_size) is 131GB. My
> question is: when an update to all of these rows is performed, how much disk
> space should I provision?
You can expect the size twice.
>
> Also would be nice to understand how Postgres physically handles large updates
> like this. (Does it create a temporary or global temporary table, and then drop
> it when the transaction is committed?)
No, all records are marked (and only marked) as deleted (yes, only
marked, no really deleted), and for every as deleted marked record a new
one is created.
After the COMMIT, and after the VACUUM-process, the deleted are
re-usable for new records. Only a VACUUM FULL returns the free space to
the operation system (and requires a exclusive table lock)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Salisbury | 2012-04-13 15:41:41 | Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux |
Previous Message | Steve Horn | 2012-04-13 14:57:00 | Large update and disk usage |