public inbox for [email protected]  

help / color / mirror / Atom feed

From: Kirk Wolak <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Adam Brusselback <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: Andrey Borodin <[email protected]>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
Date: Tue, 9 Dec 2025 00:08:58 -0500
Message-ID: <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com> (raw)
In-Reply-To: <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
References: <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>

On Mon, Dec 8, 2025 at 3:58 PM Adam Brusselback <[email protected]>
wrote:

> Attached is a patch implementing support for a WHERE clause in REFRESH
> MATERIALIZED VIEW.
>
> The syntax allows for targeted refreshes:
>     REFRESH MATERIALIZED VIEW mv WHERE invoice_id = ANY('{1,2,3}');
>     REFRESH MATERIALIZED VIEW CONCURRENTLY mv WHERE customer_id = 42;
>     REFRESH MATERIALIZED VIEW mv WHERE order_date >= '2023-01-01';
>
> I was inspired to implement this feature after watching the Hacking
> Postgres discussion on the topic:
> https://www.youtube.com/watch?v=6cZvHjDrmlQ
>
> +1 (But I was in that hacking session).

  Our situation was a wonderful MV with all the columns we needed (some
hard to calculate) to augment search data done millions of times/day.  It
was a thing of beauty.  Until we realized we needed to update 1 record
(vendor inventory UPDATE date/time) every time we processed a file
(something we do 24x7, a hundred times each hour!

  For that ONE field, we ended up doing REFRESH MV concurrently;  OVER
2,000 times per day.

  Our understanding is that many people run into this exact issue.  The
cache needs small frequent updates.
(After reading the code that handles MVs, we just created our own TABLE,
and maintain it with a scheduler to rebuild HOURLY,
and when we process the file, a Simple UPDATE is issued for the one column).

  While this "Works", the CONCEPT of this patch (untested by me, as of
yet), would have fixed this with far less effort,
and would be easier to maintain.

  After I review the code, I will add additional comments.

  I am curious what others think?  (And FWIW, I believe that the larger the
MV, the MORE this feature is needed,
vs refreshing the ENTIRE view).

Regards...

>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
  In-Reply-To: <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox