From: | Wicher <wicher+pglist(at)gavagai(dot)nl> |
---|---|
To: | |
Cc: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Advice on using materialized views |
Date: | 2021-12-12 12:11:36 |
Message-ID: | 20211212131136.476d4de2@tipi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 09 Dec 2021 16:06:27 +0000
"Phil Endecott" <spam_from_pgsql_lists(at)chezphil(dot)org> wrote:
> Thanks both for your replies.
>
> Wicher wrote:
> > On Mon, 06 Dec 2021 18:48:47 +0000
> > "Phil Endecott" <spam_from_pgsql_lists(at)chezphil(dot)org> wrote:
> >> 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.
> >
> > You won't necessarily need to rewrite the "recent data" view definitions, I think. What is
> > deemed "recent" depends on what's in the materialized views (it'd be anything newer than
> > whatever is in there). The good news is that you can simply query for that :-)
> > So trivially, in your "the data that is more recent than the stuff from the materialized
> > views" non-materialized view you'd use a definition like
> > SELECT .... WHERE sometimestamp > (select max(sometimestamp) from the_materialized_view)
> > or something along those lines.
>
>
> I guess I was hoping that someone would suggest a more "magic"
> way to do this sort of thing. Actually I'm a bit surprised that
> materialised views don't event have a way to either
>
> - Refresh a materialised view whenever a source table is modified;
>
> - Refresh a materialised view whenever it is read, if a source table
> has changed since it was last refreshed.
>
> Beyond that, I could imagine smart updates where e.g. if you
> modify source table rows with primary key K, then you only need
> to refresh materialised view rows derived from K.
>
> I think this could all be done on top of triggers. I wonder, do any
> other databases do things like this automagically?
>
Not too long ago I asking the list something similar but came up short:
https://www.postgresql.org/message-id/20211129102315.058595fe@tipi
Here's my own take on that problem area, tangent to your question. This
project aims to do (or make it easier to do) the things you mention:
https://git.sr.ht/~nullenenenen/DBSamizdat
It supports your first use case out of the box, and may make your second use case easier
to accommodate, give it a go :-)
There's a sweet spot for materialized views. But at some point (volume/computational
load/freshness requirements) it becomes necessary to use tables instead so that you can
indeed implement efficient partial recalculation. As far as I know. I too am curious
about other approaches.
From | Date | Subject | |
---|---|---|---|
Next Message | PGSQL DBA | 2021-12-13 02:04:43 | Re: Need to know more about pg_test_fsync utility |
Previous Message | Michael Lewis | 2021-12-11 16:31:50 | Re: Postgresql + containerization possible use case |