pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWarehouse Schema
2+ messages / 2 participants
[nested] [flat]
* Warehouse Schema
@ 2006-05-24 18:50 Worky Workerson <worky.workerson@gmail.com>
2006-05-25 09:54 ` Re: Warehouse Schema Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Worky Workerson @ 2006-05-24 18:50 UTC (permalink / raw)
To: pgsql-sql
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.
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)
);
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?
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"?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Warehouse Schema
2006-05-24 18:50 Warehouse Schema Worky Workerson <worky.workerson@gmail.com>
@ 2006-05-25 09:54 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2006-05-25 09:54 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-05-25 09:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-24 18:50 Warehouse Schema Worky Workerson <worky.workerson@gmail.com>
2006-05-25 09:54 ` Richard Huxton <dev@archonet.com>
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