Re: Database denormalization - Mailing list pgsql-general

From Marti Raudsepp
Subject Re: Database denormalization
Date
Msg-id CABRT9RCCchdAf9hLoU_U=RUvWxJQZH_mtj6N9Xk141LGodTqSQ@mail.gmail.com
Whole thread Raw
In response to Database denormalization  (JG <vhz95@rocketmail.com>)
List pgsql-general
On Mon, Feb 13, 2012 at 15:48, JG <vhz95@rocketmail.com> wrote:
> I would like to ask weather PostgreSQL does database denormalization at runtime.
>
> To specify further, the question is, can I count on PostgreSQL to denormalize the database when it would be better
forthe performance, or should I always denormalize the database and all the querys myself. 

Even the Oracle and MSSQL features you mention, don't "denormalize the
database" themselves -- you have to design and query from those
indexed/materialized views manually.

But no, PostgreSQL does not natively support materialized views, so
it's probably easier to work with a denormalized schema to begin with.
Or you can create denormalized copies of your data and keep it in sync
yourself -- via triggers or periodically regeneretaing the whole
materialized copy.

(Normal indexes are technically also a "denormalization technique";
obviously PostgreSQL supports those ;)

> I have looked for answers on the subject, but all I managed to find was a wiki article at
http://en.wikipedia.org/wiki/Denormalizationthat says: 
>
> "The preferred method is to keep the logical design normalised, but allow the database management system (DBMS) to
storeadditional redundant information on disk to optimise query response. [...]" 

This sounds good in theory, but as always, these features have their
costs. So it's a tradeoff over performance.

Regards,
Marti

pgsql-general by date:

Previous
From: JG
Date:
Subject: Database denormalization
Next
From: Venkat Balaji
Date:
Subject: Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue