Re: Database storage bloat

On Thursday 08 April 2004 5:51 am, Tony and Bryn Reina wrote:
> Yep. That's after a 'vacuum verbose analyze'.

No, he asked if you had run a "vacuum full". A "standard" vacuum just
marks space available for reuse - it does not shrink file sizes. A
"vacuum full" will shrink the files on disk.

Are you doing many updates on the table that is the space-using
culprit? Each record that is updated is created anew. The old record
is marked as "defunct" when no remaining transactions need that
record. If you were to, say, update the entire table to change a
single character field you would basically double the size of the
table.

A vacuum will mark the space that is no longer used as available for
reuse so if you run regular vacuums you should achieve a fairly
static size.

One thing to consider: since this is how PostgreSQL achieves MVCC you
may want to try to avoid updates to a table that has huge record
sizes.

For example, say you wanted to store a "last viewed date" for each
file. If you had that date in the same table with your data every
view would add considerable "bloat" to your table. If, instead, your
file is in a table by itself along with a sequence or other unique
key then the "last viewed date" or other changing data could be put
into a separate table linked to the file storage table by that unique
ID. Updating the date or other info about the file would only
increase the bloat in the other table and the bloat would be small.

Performance would probably improve as well due to smaller file sizes
and less read/write action on the disks.

Cheers,
Steve