pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (13+ messages)  latest in thread

Message-ID: <200404081026.22195.scrawford@pinpointresearch.com>
Permalink:  ../200404081026.22195.scrawford@pinpointresearch.com/
Also on:    postgresql.org/message-id/200404081026.22195.scrawford@pinpointresearch.com

 · 

reply

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