pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tony and Bryn Reina <reina_ga@hotmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database storage bloat
Date: Fri, 9 Apr 2004 07:09:11 +0200
Message-ID: <BAY8-DAV64SOlbpH71y00020ce6@hotmail.com> (raw)
References: <272e4be7.0404080115.39f1cb36@posting.google.com>
	<20040408063838.Q93611@megazone.bigpanda.com>
	<BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com>
	<15732.1081446563@sss.pgh.pa.us>

> I don't see orders-of-magnitude bloat here though.  You've got 16 bytes
> of useful data per row (which I suppose was 12 bytes in the flat file?).
> There will be 28 bytes of overhead per table row.  In addition the index
> will require 12 data bytes + 12 overhead bytes per entry; allowing for
> the fact that b-tree only likes to pack pages about 2/3ds full, we could
> estimate index size as about 36 bytes per original row, giving an
> aggregate bloat factor of 6.67X compared to a binary flat file if the
> flat file needed 12 bytes per row.
>
> The only way I could see to get to a 65X bloat factor would be if you'd
> repeatedly updated the table rows without vacuuming.

Thanks Tom (and everyone else). At least I feel more comfortable that
there's not something obvious. I did perform a 'vacuum full', but still no
dice. At least thanks to Stephan I've narrowed my search down to one table
and I have a ballpark theoretical bloat to shoot for (6 .67X). I'm going to
go back and try to determine if I'm correctly interpretting the part of the
flat file that holds this table's data.

-Tony



view thread (13+ messages)

Message-ID: <BAY8-DAV64SOlbpH71y00020ce6@hotmail.com>
Permalink:  ../BAY8-DAV64SOlbpH71y00020ce6@hotmail.com/
Also on:    postgresql.org/message-id/BAY8-DAV64SOlbpH71y00020ce6@hotmail.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: reina_ga@hotmail.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com
  Subject: Re: Database storage bloat
  In-Reply-To: <BAY8-DAV64SOlbpH71y00020ce6@hotmail.com>
  References: <272e4be7.0404080115.39f1cb36@posting.google.com> <20040408063838.Q93611@megazone.bigpanda.com> <BAY8-DAV48dqvhu1NQJ0001f557@hotmail.com> <15732.1081446563@sss.pgh.pa.us> <BAY8-DAV64SOlbpH71y00020ce6@hotmail.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