X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D81759F94EA for ; Wed, 24 May 2006 15:50:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 18881-06 for ; Wed, 24 May 2006 15:50:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id E3ABA9F9398 for ; Wed, 24 May 2006 15:50:20 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id o63so24248nfa for ; Wed, 24 May 2006 11:50:19 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=L1ckpvBOfu3FIdQB2l+1W1I5EhY8wsBd5ggf/xt5LzQICp6ksO/G4HNoGyAUVVhuSQEJ+aPmUaU91IKvEzq2kGX0Ya+zLH9QhQZJOlDm704Iy5q/V5g5PB5xqkO2+41Xnf+GIhErf7oBHulbDnApT+Tblze/jEmJQHihWkcwrN4= Received: by 10.49.71.16 with SMTP id y16mr5901266nfk; Wed, 24 May 2006 11:50:19 -0700 (PDT) Received: by 10.48.164.19 with HTTP; Wed, 24 May 2006 11:50:19 -0700 (PDT) Message-ID: Date: Wed, 24 May 2006 14:50:19 -0400 From: "Worky Workerson" To: pgsql-sql@postgresql.org Subject: Warehouse Schema MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_178909_9783756.1148496619322" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/186 X-Sequence-Number: 25217 ------=_Part_178909_9783756.1148496619322 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I'm developing a schema for a large data warehouse (10 billion records) and had a couple of questions about how to optimize it. My biggest question is on the design of the fact table and how much normalization I should actuall= y do of the data. The data is going to be keyed by IP address, stored as a non-unique IP4 type. Each record is also tagged with various attributes, such as a category and a type. Assuming that a category and type are VARCHAR, would it make sense to normalize these out of the fact table into their respectiv= e tables and key them by an INTEGER? I.e. CREATE TABLE big_fact_table_A ( identifier IP4, data1 BYTEA, data2 BYTEA, ... dataN BYTEA, category VARCHAR(16), type VARCHAR(16) ); ... vs ... CREATE TABLE big_fact_table_B ( identifier IP4, data1 BYTEA, data2 BYTEA, ... dataN BYTEA, category INTEGER REFERENCES categories (category_id), type INTEGER REFERENCES types (type_id) ); I figure that the normalized fact table should be quicker, as the integer i= s much smaller than the varchar. On query, however, the table will need to b= e joined against the two other tables (categories, types), but I still figure that this is a win because the other tables are fairly small and should sta= y resident in memory. Is this reasoning valid? The downside to this (from my perspective) is that the data comes in the form of big_fact_table_A and could be easily COPYed straight into the table. with big_fact_table_B it looks like I will have to do the "unJOIN" in a script. Also, I have separate installations of the warehouse (with different data sources) and it will be difficult to share data between them unless their categories/types tables are keyed with exactly the same intege= r IDs which, as I don't directly control the other installations, is not guaranteed. Any suggestions to the above "problems"? ------=_Part_178909_9783756.1148496619322 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I'm developing a schema for a large data warehouse (10 billion records) and= had a couple of questions about how to optimize it.  My biggest quest= ion is on the design of the fact table and how much normalization I should = actually do of the data. =20

The data is going to be keyed by IP address, stored as a non-unique= IP4 type.  Each record is also tagged with various attributes, such a= s a category and a type.  Assuming that a category and type are VARCHA= R, would it make sense to normalize these out of the fact table into their = respective tables and key them by an INTEGER?=20 I.e.

CREATE TABLE big_fact_table_A (
    identifier IP4,
    data1   BYTEA,
    data2   BYTEA,
    ...
    dataN  BYTEA,
    category    VARCHAR(16),
    type        VARCHAR(1= 6)
);

... vs ...

CREATE TABLE big_fact_table_B (
  =   identifier IP4,
    data1   BYTEA,
&n= bsp;   data2   BYTEA,
    ...
&nbs= p;   dataN  BYTEA,
    category  = ;  INTEGER REFERENCES categories (category_id),
    type        INTEG= ER REFERENCES types (type_id)
);

I figure that the normalized fac= t table should be quicker, as the integer is much smaller than the varchar.=   On query, however, the table will need to be joined against the two = other tables (categories, types), but I still figure that this is a win bec= ause the other tables are fairly small and should stay resident in memory.&= nbsp; Is this reasoning valid?

The downside to this (from my perspective) is that the data comes i= n the form of big_fact_table_A and could be easily COPYed straight into the= table.  with big_fact_table_B it looks like I will have to do the &qu= ot;unJOIN" in a script.  Also, I have separate installations of t= he warehouse (with different data sources) and it will be difficult to shar= e data between them unless their categories/types tables are keyed with exa= ctly the same integer IDs which, as I don't directly control the other inst= allations, is not guaranteed.

Any suggestions to the above "problems"?
------=_Part_178909_9783756.1148496619322--