Обсуждение: Database denormalization

Hi


I would like to ask weather PostgreSQL does database denormalization at runtime.

That is, for example, if I have a normalized database and I use lots of querys that would run faster on a denormalized
database,than will PostgreSQL create a denormalized version of the database for internal use. 

To specify further, the question is, can I count on PostgreSQL to denormalize the database when it would be better for
theperformance, or should I always denormalize the database and all the querys myself. 


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. In this case it is the DBMS software's
responsibilityto ensure that any redundant copies are kept consistent. This method is often implemented in SQL as
indexedviews (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?


Thanks

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

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

On Tue, Feb 14, 2012 at 6:27 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> 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 standard reference for materialized views is the page by j gardner:

http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views