On Thu, 19 Dec 2002 14:10:58 -0500, george young <gry(at)ll(dot)mit(dot)edu>
>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.
In response to
pgsql-performance by date
|Next:||From: Josh Berkus||Date: 2002-12-20 17:01:28|
|Subject: Re: 4G row table?|
|Previous:||From: Manfred Koizar||Date: 2002-12-20 08:53:56|
|Subject: Re: EXISTS vs IN vs OUTER JOINS|