From: | Menelaos PerdikeasSemantix <mperdikeas(dot)semantix(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | redundant fields in table for "performance optimizations" |
Date: | 2012-08-21 21:07:55 |
Message-ID: | CABEh7vebXoYT4cSQ2E9P2MnR-zsMO5S9H2bJfmraEbwVzGVfrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Let's say you have a father-child (or master-detail if you wish) hierarchy
of tables of not just 2 levels, but, say, 5 levels.
E.g. tables A, B, C, D and E organized in successive 1-to-N relationships:
A ----1-to-N-----> B
B ----1-to-N-----> C
C ----1-to-N-----> D
D ----1-to-N-----> E
with appropriate foreign keys:
* from E to D
* from D to C
* from C to B
* from B to A
This is normalized so far. Now assume that it is the case than in some
queries on table E you also need to report a field that only exists on
table A. This will mean a JOIN between five tables: E, D, C, B and A. Some
questions follow:
[1] assuming tables having a number of rows in the order of 100,000, after
how many levels of depth would you feel justified to depart from the
normalized schema and introduce some redundancy to speed up the queries?
[2] is adding redundant fields and extra foreign keys (say directly from E
to A) the best way to do this in 2012? Shouldn't some indexing and fine
tuning suffice ?
[3] do you feel this is a legitimate concern in a modern PostgreSQL
database running on high end (200,000 USD) hardware and serving no more
than 1000 concurrent users with table sizes at the lowest (more detailed)
level of the hierarchy in the order of a few tens of millions of rows at
the most and dropping by a factor of 20 for each level up ?
Menelaos.
From | Date | Subject | |
---|---|---|---|
Next Message | Darren Duncan | 2012-08-21 21:17:50 | Re: redundant fields in table for "performance optimizations" |
Previous Message | Sébastien Lorion | 2012-08-21 18:25:34 | Re: Amazon High I/O instances |