Re: Database denormalization

On Mon, Feb 13, 2012 at 15:48, JG <vhz95(at)rocketmail(dot)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 for the 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/Denormalization that says:
>
> "The preferred method is to keep the logical design normalised, but allow the database management system (DBMS) to store additional 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