Advice on using materialized views

From: "Phil Endecott" <spam_from_pgsql_lists(at)chezphil(dot)org>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Advice on using materialized views
Date: 2021-12-06 18:48:47
Message-ID: 1638816527952@dmwebmail.dmwebmail.chezphil.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Experts,

Does anyone have any suggestions about how to use materialized
views in combination with regular views for the most recent data?
Example:

- I have a raw data table, indexed on a timestamp column, to which
new data is regularly being added.

- I have a set of views that produce hourly/daily/monthly summaries
of this data. Querying these views is slow, so I'd like to make
them materialized views.

- But I'd like the most recent data to be included in the results
of my queries. So I think I need a combined view that is the
union of the materialized view and a non-materialised view for
the recent values.

I can imagine how to implement this but it feels rather clunky;
I need a parallel set of views for the "old" and "new" data, and
I need to modify the definition of a view that filters the "new"
values from the raw table each time the materialised view is
refreshed.

I'm wondering if anyone has an advice or examples of how best
to do this.

Many thanks, Phil.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2021-12-06 19:19:38 Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
Previous Message Pavel Stehule 2021-12-06 18:22:15 Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT