Skip site navigation (1) Skip section navigation (2)

Re: Large update and disk usage

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 (view raw or flat)
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°

In response to

pgsql-novice by date

Next:From: Kevin SalisburyDate: 2012-04-13 15:41:41
Subject: Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux
Previous:From: Steve HornDate: 2012-04-13 14:57:00
Subject: Large update and disk usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group