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: 200407080941.46912.scrawford@pinpointresearch.com (view raw or flat)
Thread:
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 
transactions.

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)

vacuum

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

Cheers,
Steve

In response to

Responses

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-2014 The PostgreSQL Global Development Group