Thread: Large update and disk usage

Steve Horn <steve@stevehorn.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°