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)
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°
In response to
pgsql-novice by date
|Next:||From: Kevin Salisbury||Date: 2012-04-13 15:41:41|
|Subject: Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on
|Previous:||From: Steve Horn||Date: 2012-04-13 14:57:00|
|Subject: Large update and disk usage|