Re: Advice on using materialized views

On Mon, 6 Dec 2021 at 18:48, Phil Endecott
<spam_from_pgsql_lists(at)chezphil(dot)org> wrote:
>
> - 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.

Assuming your table is insert-only:

How about instead of using a materialized view at all, you define a
table of hourly summaries which your script updates, and define a view
which merges that with an on-the-fly summary of main table rows newer
than the most recent summarised hour.