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
