From: Richard Huxton <dev@archonet.com>
To: Worky Workerson <worky.workerson@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Warehouse Schema
Date: Thu, 25 May 2006 10:54:44 +0100
Message-ID: <44757EE4.8040807@archonet.com> (raw)
In-Reply-To: <ce4072df0605241150j6781291bjbd64b74655607e44@mail.gmail.com>
References: <ce4072df0605241150j6781291bjbd64b74655607e44@mail.gmail.com>
Worky Workerson wrote:
> 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
> actually
> do of the data.
As much as you can and still afford the hardware to support it.
> 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
> respective
> 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)
> );
Assuming categories and types are fixed sets this isn't really
normalisation, you're just substituting one representation with another.
That's assuming you enforce valid representations on table_A.
Of course, if the categories or types can change then you'll want to use
foreign-keys. Try int2 rather than int4 even (although check the
manuals for details to see if it'll pack them closer).
> I figure that the normalized fact 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 because the other tables are fairly small and should
> stay
> resident in memory. Is this reasoning valid?
That's the trade-off.
> 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
> integer
> IDs which, as I don't directly control the other installations, is not
> guaranteed.
>
> Any suggestions to the above "problems"?
If you're comfortable with a little "C" coding, write your own type.
That's really what you're trying to do here. Accept text input and
display text output but store in a compact form.
HTH
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, worky.workerson@gmail.com
Subject: Re: Warehouse Schema
In-Reply-To: <44757EE4.8040807@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox