From: Steve Crawford <scrawford@pinpointresearch.com>
To: Tony and Bryn Reina <reina_ga@hotmail.com>
To: Uwe C. Schroeder <uwe@oss4u.com>
To: pgsql-admin@postgresql.org
Subject: Re: Database storage bloat
Date: Thu, 8 Apr 2004 10:26:22 -0700
Message-ID: <200404081026.22195.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com>
References: <272e4be7.0404080115.39f1cb36@posting.google.com>
<200404080257.46102.uwe@oss4u.com>
<BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, reina_ga@hotmail.com, uwe@oss4u.com
Subject: Re: Database storage bloat
In-Reply-To: <200404081026.22195.scrawford@pinpointresearch.com>
References: <272e4be7.0404080115.39f1cb36@posting.google.com> <200404080257.46102.uwe@oss4u.com> <BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com> <200404081026.22195.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox