Re: 4G row table?

On Thu, 19 Dec 2002 14:10:58 -0500, george young <gry(at)ll(dot)mit(dot)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