Re: Tuple storage overhead

On 16/04/10 10:41, Peter Bex wrote:
> Hi all,
>
> I have a table with three columns: one integer and two doubles.
> There are two indexes defined (one on the integer and one on one
> of the doubles).  This table stores 700000 records, which take up
> 30 Mb according to pg_relation_size(), and the total relation size
> is 66 Mb.
[snip]
> Is there a way to reduce the per-tuple storage overhead?

Short answer - no.

The database has to track visibility of every row - when it was
inserted, deleted etc to support the MVCC concurrency system.

http://www.postgresql.org/docs/8.4/static/storage-page-layout.html

That means 24 bytes of overhead (on most systems) for each row. That's
higher than some other RDBMS but they'll all have some overhead.

> The reason I'm asking is that I have tons of tables like this,
> and some data sets are much bigger than this.  In a relatively
> simple testcase I'm importing data from text files which are
> 5.7 Gb in total, and this causes the db size to grow to 34Gb.

Anything from double to ten times the size isn't unexpected, depending
on row-sizes and how many indexes you are talking about.

--
   Richard Huxton
   Archonet Ltd