Skip site navigation (1) Skip section navigation (2)

Re: Database storage bloat

From: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
To: reina_ga(at)hotmail(dot)com (Tony Reina), pgsql-admin(at)postgresql(dot)org
Subject: Re: Database storage bloat
Date: 2004-04-08 09:57:45
Message-ID: 200404080257.46102.uwe@oss4u.com (view raw or flat)
Thread:
Lists: pgsql-admin
-----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-----


In response to

Responses

pgsql-admin by date

Next:From: Gaetano MendolaDate: 2004-04-08 11:34:22
Subject: CONTEXT: in log file
Previous:From: Tony ReinaDate: 2004-04-08 09:15:07
Subject: Database storage bloat

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group