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: Uwe C. Schroeder <uwe@oss4u.com>
To: pgsql-admin@postgresql.org
Subject: Re: Database storage bloat
Date: Thu, 8 Apr 2004 14:51:14 +0200
Message-ID: <BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com> (raw)
References: <272e4be7.0404080115.39f1cb36@posting.google.com>
	<200404080257.46102.uwe@oss4u.com>

Yep. That's after a 'vacuum verbose analyze'.

-Tony

----- Original Message ----- 
From: "Uwe C. Schroeder" <uwe@oss4u.com>
To: "Tony Reina" <reina_ga@hotmail.com>; <pgsql-admin@postgresql.org>
Sent: Thursday, April 08, 2004 11:57 AM
Subject: Re: [ADMIN] Database storage bloat


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Did you run vacuum full after your import ?

On Thursday 08 April 2004 02:15 am, Tony Reina wrote:
> I'm developing a database for scientific recordings. These recordings
> are traditionally saved as binary flat files for simplicity and
> compact storage. Although I think ultimately having a database is
> better than 1,000s of flat files in terms of data access, I've found
> that the database (or at least my design) is pretty wasteful on
> storage space compared with the binary flat files.
>
> In particular, I tried importing all of the data from a binary flat
> file that is 1.35 MB into a PostgreSQL database (a very small test
> file; average production file is probably more like 100 MB). The
> database directory ballooned from 4.1 MB to 92 MB (a bloat of 65X the
> original storage of the binary flat file).
>
> Now I know that table design and normalizing is important. As far as
> my partner and I can tell, we've made good use of normalizing (no
> redundancy), we've set field sizes to their theoretical skinniness,
> and we've made use of foreign keys and views. I'm also aware that
> indicies/keys and other database internals will necessarily make the
> DBMS solution bloated in terms of storage space. However, a 65X bloat
> in space seems excessive.
>
> Has anyone run across similar storage concerns? I'd be interested in
> knowing if I just have really poorly designed tables, or if something
> else is going on here. I figure a bloat of 3-4X would be permissible
> (and possibly expected). But this bloat just seems too much.
>
> Thanks.
> -Tony
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

- -- 
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAdSIajqGXBvRToM4RApqaAJ9wOZa8NAWqgPk4ZZpWgeh1L2VwqQCdHKgv
3ruwuPZRC/rOxX3nb1q6khE=
=BoL3
-----END PGP SIGNATURE-----




view thread (13+ messages)  latest in thread

Message-ID: <BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com>
Permalink:  ../BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com/
Also on:    postgresql.org/message-id/BAY8-DAV64ao8KK8ZtP0001e646@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, uwe@oss4u.com
  Subject: Re: Database storage bloat
  In-Reply-To: <BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com>
  References: <272e4be7.0404080115.39f1cb36@posting.google.com> <200404080257.46102.uwe@oss4u.com> <BAY8-DAV64ao8KK8ZtP0001e646@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