MIME-Version: 1.0
References: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
In-Reply-To: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
From: Kirk Wolak <wolakk@gmail.com>
Date: Tue, 9 Dec 2025 00:08:58 -0500
Message-ID: 
 <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Adam Brusselback <adambrusselback@gmail.com>,
 Nikolay Samokhvalov <nik@postgres.ai>,
	Andrey Borodin <x4mmm@yandex-team.ru>
Content-Type: multipart/alternative; boundary="0000000000004ff2de06457dead9"
Archived-At: 
 <https://www.postgresql.org/message-id/CACLU5mST1LhC3ibaKGNch_%3D06S2cmbjR4PnoUSupKs%2Brtgdeyw%40mail.gmail.com>
Precedence: bulk

--0000000000004ff2de06457dead9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Dec 8, 2025 at 3:58=E2=80=AFPM Adam Brusselback <adambrusselback@gm=
ail.com>
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 =3D ANY('{1,2,3}');
>     REFRESH MATERIALIZED VIEW CONCURRENTLY mv WHERE customer_id =3D 42;
>     REFRESH MATERIALIZED VIEW mv WHERE order_date >=3D '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=3D6cZvHjDrmlQ
>
> +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...

>

--0000000000004ff2de06457dead9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Dec 8, 2025 at 3:58=E2=80=AFPM Ad=
am Brusselback &lt;<a href=3D"mailto:adambrusselback@gmail.com">adambrussel=
back@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_co=
ntainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
>Attached is a patch implementing support for a WHERE clause in REFRESH MAT=
ERIALIZED VIEW.<br><br>The syntax allows for targeted refreshes:<br>=C2=A0 =
=C2=A0 REFRESH MATERIALIZED VIEW mv WHERE invoice_id =3D ANY(&#39;{1,2,3}&#=
39;);<br>=C2=A0 =C2=A0 REFRESH MATERIALIZED VIEW CONCURRENTLY mv WHERE cust=
omer_id =3D 42;<br>=C2=A0 =C2=A0 REFRESH MATERIALIZED VIEW mv WHERE order_d=
ate &gt;=3D &#39;2023-01-01&#39;;<br><br>I was inspired to implement this f=
eature after watching the Hacking Postgres discussion on the topic: <a href=
=3D"https://www.youtube.com/watch?v=3D6cZvHjDrmlQ" target=3D"_blank">https:=
//www.youtube.com/watch?v=3D6cZvHjDrmlQ</a><br><br></div></blockquote><div>=
+1 (But I was in that hacking session).<br></div><div><br></div><div>=C2=A0=
 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.=C2=A0 It =
was a thing of beauty.=C2=A0 Until we realized we needed to update 1 record=
 (vendor inventory UPDATE date/time) every time we processed a file (someth=
ing we do 24x7, a hundred times each hour!</div><div><br></div><div>=C2=A0 =
For that ONE field, we ended up doing REFRESH MV concurrently;=C2=A0 OVER 2=
,000 times per day.</div><div><br></div><div>=C2=A0 Our understanding is th=
at many people run into this exact issue.=C2=A0 The cache needs small frequ=
ent updates.</div><div>(After reading the code that handles MVs, we just cr=
eated our own TABLE, and maintain it with a scheduler to rebuild HOURLY,</d=
iv><div>and when we process the file, a Simple UPDATE is issued for the one=
 column).</div><div><br></div><div>=C2=A0 While this &quot;Works&quot;, the=
 CONCEPT of this patch (untested by me, as of yet), would have fixed this w=
ith far less effort,</div><div>and would be easier to maintain.</div><div><=
br></div><div>=C2=A0 After I review the code, I will add additional comment=
s.</div><div><br></div><div>=C2=A0 I am curious what others think?=C2=A0 (A=
nd FWIW, I believe that the larger the MV, the MORE this feature is needed,=
</div><div>vs refreshing the ENTIRE view).<br><br>Regards...</div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div>

--0000000000004ff2de06457dead9--