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

Re: Database storage bloat

From: "Tony and Bryn Reina" <reina_ga(at)hotmail(dot)com>
To: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Database storage bloat
Date: 2004-04-08 12:51:14
Message-ID: BAY8-DAV64ao8KK8ZtP0001e646@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Yep. That's after a 'vacuum verbose analyze'.

-Tony

----- Original Message ----- 
From: "Uwe C. Schroeder" <uwe(at)oss4u(dot)com>
To: "Tony Reina" <reina_ga(at)hotmail(dot)com>; <pgsql-admin(at)postgresql(dot)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-----


In response to

Responses

pgsql-admin by date

Next:From: Tony and Bryn ReinaDate: 2004-04-08 13:01:26
Subject: Re: Database storage bloat
Previous:From: Terry HamptonDate: 2004-04-08 12:36:37
Subject: Location of a new column

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