Re: 4G row table?

On Thu, 19 Dec 2002 14:10:58 -0500, george young <gry@ll.mit.edu>
wrote:
>with 4 billion(4e9) rows.
>How big will the primary index on the first seven columns be?

If you manage to pack the key into 8 bytes (by using a custom 1 byte
integer datatype) and if there are no NULLs:

  75 GB with a 100% fill factor,
 114 GB with a  66% fill factor,
realistically something in between.  Note that frequent updates can
cause index growth.

>Will this schema work at all? 

You have a somewhat unusual identifier : payload ratio (8B : 1b).  It
depends on the planned use, but I'm not sure if *any* database is the
right solution.  You have "only" 30670848000 (30G) possible different
key combinations, more than 1/8 of them (4G) are actually used.  A
7-dimensional array of double-bits (1 bit to indicate a valid value
and 1 bit payload) would require not more than 8 GB.

If you plan to use a database because you have to answer ad-hoc
queries, you will almost certainly need additonal indices.

Servus
 Manfred