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.
