Skip site navigation (1) Skip section navigation (2)

Re: 4G row table?

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: gry(at)ll(dot)mit(dot)edu
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 4G row table?
Date: 2002-12-20 10:27:18
Message-ID: vnp50vgu2htr6tvoijh486n037pkll40jl@4ax.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-12-20 17:01:28
Subject: Re: 4G row table?
Previous:From: Manfred KoizarDate: 2002-12-20 08:53:56
Subject: Re: EXISTS vs IN vs OUTER JOINS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group