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-----
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