Re: Database denormalization

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "JG" <vhz95(at)rocketmail(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Database denormalization
Date: 2012-02-14 13:27:26
Message-ID: D960CB61B694CF459DCFB4B0128514C2077EBAE4@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

JG wrote:
> 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.

PostgreSQL does not do such things automatically. You'll have to do so
yourself.

> 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. In this
> case it is the DBMS software's responsibility to ensure that any
redundant copies are kept consistent.
> This method is often implemented in SQL as indexed views (Microsoft
SQL Server) or materialised views
> (Oracle)."
>
> So in the case of PostgreSQL, do I also have to use views or are there
some other ways?

Views won't help you.

A materialized view is actually a table that holds a (possibly
aggregated)
copy of data from elsewhere in the database.

Apart from materialized views, you can denormalize for performance by
adding columns to tables that store a copy of information from another
table,
with the benefit that you can avoid joins to access the information.

The problem you have to solve when you use denormalization techniques
is to keep the data and the copy consistent.

Yours,
Laurenz Albe

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marti Raudsepp 2012-02-14 14:06:15 Re: Let-bindings in SQL statements
Previous Message Venkat Balaji 2012-02-14 12:21:22 Re: [GENERA]: Postgresql-9.1.1 synchronous replication issue