Re: Database storage bloat

On Thu, 8 Apr 2004, 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).

Well, an important question is where is that space going? It'd be
interesting to give a breakup by the directories and then which files (and
using the contrib/oid2name to get which table/indexes/etc they are).

At least 16MB of that is probably going into the transaction log (IIRC
that's the default size for the segments) in pg_xlog.