Re: Advice on using materialized views

On Mon, 6 Dec 2021 at 18:48, Phil Endecott
<spam_from_pgsql_lists@chezphil.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.