MIME-Version: 1.0
References: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
 <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com>
In-Reply-To: 
 <CACLU5mST1LhC3ibaKGNch_=06S2cmbjR4PnoUSupKs+rtgdeyw@mail.gmail.com>
From: Adam Brusselback <adambrusselback@gmail.com>
Date: Tue, 9 Dec 2025 11:27:58 -0500
Message-ID: 
 <CAMjNa7fJUwcOxf+qV8g+tCQ-3E-YAiKgE_Qs6u-xjdxe12T0SQ@mail.gmail.com>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
To: Kirk Wolak <wolakk@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f881e90645876579"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMjNa7fJUwcOxf%2BqV8g%2BtCQ-3E-YAiKgE_Qs6u-xjdxe12T0SQ%40mail.gmail.com>
Precedence: bulk

--000000000000f881e90645876579
Content-Type: text/plain; charset="UTF-8"

>   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.

Thanks for the feedback and the use case, Kirk.

Regarding that specific scenario where a single column ("last updated" or
similar) churns significantly faster than the heavy-computation columns:
Even with this patch, you might find it beneficial to separate that
high-velocity column into its own small materialized view (or regular view)
and join it to the main MV at query time. That will reduce the bloat you
get on the main MV by quite a lot, especially if you have very wide rows
(which it seems like you do).

I initially tried to implement logic that would allow for direct UPDATEs
(which would enable HOT updates). However, to handle rows that matched the
predicate but were no longer present in the new source data, I had to run
an anti-join to identify them for deletion. That approach caused
performance issues, so I settled on the "Prune + Upsert" strategy (DELETE
matching rows, then INSERT from source).

Because this patch performs a delete/insert cycle, updating that one
timestamp column will still result in rewriting the whole tuple in the MV.

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

That said, 2,000 refreshes per day is nothing for this implementation,
provided your updates are selective enough and your queries allow for
predicate push-down to the base tables.

I look forward to your thoughts after reviewing the code.

Thanks,
Adam Brusselback

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

<div dir=3D"ltr">&gt; =C2=A0 Our situation was a wonderful MV with all the =
columns we needed (some<br>&gt; hard to calculate) to augment search data d=
one millions of times/day.=C2=A0 It<br>&gt; was a thing of beauty.=C2=A0 Un=
til we realized we needed to update 1 record<br>&gt; (vendor inventory UPDA=
TE date/time) every time we processed a file<br>&gt; (something we do 24x7,=
 a hundred times each hour!<br>&gt;<br>&gt; =C2=A0 For that ONE field, we e=
nded up doing REFRESH MV concurrently; =C2=A0OVER<br>&gt; 2,000 times per d=
ay.<br><br>Thanks for the feedback and the use case, Kirk.<br><br>Regarding=
 that specific scenario where a single column (&quot;last updated&quot; or =
similar) churns significantly faster than the heavy-computation columns:<br=
>Even with this patch, you might find it beneficial to separate that high-v=
elocity column into its own small materialized view (or regular view) and j=
oin it to the main MV at query time. That will reduce the bloat you get on =
the main MV by quite a lot, especially if you have very wide rows (which it=
 seems like you do).<br><br>I initially tried to implement logic that would=
 allow for direct UPDATEs (which would enable HOT updates). However, to han=
dle rows that matched the predicate but were no longer present in the new s=
ource data, I had to run an anti-join to identify them for deletion. That a=
pproach caused performance issues, so I settled on the &quot;Prune + Upsert=
&quot; strategy (DELETE matching rows, then INSERT from source).<br><br>Bec=
ause this patch performs a delete/insert cycle, updating that one timestamp=
 column will still result in rewriting the whole tuple in the MV.<br><br>&g=
t; =C2=A0 For that ONE field, we ended up doing REFRESH MV concurrently; =
=C2=A0OVER<br>&gt; 2,000 times per day.<br><br>That said, 2,000 refreshes p=
er day is nothing for this implementation, provided your updates are select=
ive enough and your queries allow for predicate push-down to the base table=
s.<br><br>I look forward to your thoughts after reviewing the code.<br><br>=
Thanks,<br>Adam Brusselback</div>

--000000000000f881e90645876579--