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

Re: Storage space lost during an UPDATE

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Vitaly Belman <vitalyb(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Storage space lost during an UPDATE
Date: 2004-07-08 16:41:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Saturday 03 July 2004 6:37 am, Vitaly Belman wrote:
> I ran the following query on my database:
> ---------------
> update bv_descriptions set description = REPLACE(description,
> '\\n', '\n'); commit;
> ---------------
> It finished fine but to my surprise when I ran "df" on the server
> the drive usage jumped from 44% to 60% which is additional  650MB.
> I tried to restart the server but it didn't do any good, eventually
> I tried to do a FULL VACUUM on that table and it did the trick and
> cleaned the lost space.
> I am still confused about what happened... What took these
> additional 650MB?
> I could understand this phenomenon if it happened before I did
> COMMIT... But why did it stay after COMMIT too?

This is expected. It has to do with MVCC, not commit. PostgreSQL 
creates a duplicate record for each record that was updated. This is 
how MVCC hangs on to "old" records that may still be in use by other 

A regular vacuum only checks for space within the file that can be 
reused by new records (non-current records that are no longer held by 
a transaction). Vacuum full actually reclaims physical space on the 
disk. By way of example:

vacuum full sometable

Space used by table = x

update all records in sometable

Space used by table = 2x (well, not actually 2x but probably somewhere 
around that)


Space used by sometable still = 2x but there is reusable space within 
the table

update all records

Space used by sometable still somewhere around 2x - updated records 
filled unused space in file.

vacuum full sometable

space used by table = x


In response to


pgsql-novice by date

Next:From: mike gDate: 2004-07-09 06:01:39
Subject: Re: [LONG] Need help on pg_dump!
Previous:From: M. BastinDate: 2004-07-08 14:53:30
Subject: Re: Extended Query: Parse Command: syntax?

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