Re: Configuration Advice

On 1/17/07, Steve <cheetah(at)tanabi(dot)org> wrote:
>
> However, I will look into this and see if I can figure out this
> average value. This may be a valid idea, and I'll look some more at it.

It must be, Oracle sells it pretty heavily as a data warehousing feature
;). Oracle calls it a materialized view, and the basic premise is you have
a "change" log (called a materialized log by Oracle) and you have a job that
runs through the change log and applies the changes to the materialized
view.

If you are using aggregates, be careful and make sure you use simple forms
of those aggregates. For example, if you are using an "average" function
then you should have two columns sum and count instead. Some aggregates are
too complex and cannot be represented by this solution and you will find
that you can't update the summary tables, so definitely try to stay away
from complex aggregates if you do not need them.

Here is a link to a PL/pgSQL effort that tries to simulate materialized
views:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

I don't know how complete it is, and it looks like there was a project
started but has been abandoned for the last 3 years.

--
Chad
http://www.postgresqlforums.com/