Re: Storage space lost during an UPDATE

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