MIME-Version: 1.0
References: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
In-Reply-To: 
 <CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com>
From: vellaipandiyan sm <vellaipandiyan.sm@gmail.com>
Date: Thu, 21 May 2026 10:47:55 +0530
Message-ID: 
 <CAGXjcjn3Mf2QpAz-jqkNQ=uJcjpShDtZNPn0B6v_R2YcmQ=m3A@mail.gmail.com>
Subject: Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW
To: Adam Brusselback <adambrusselback@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a3ad5b06524d0991"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGXjcjn3Mf2QpAz-jqkNQ%3DuJcjpShDtZNPn0B6v_R2YcmQ%3Dm3A%40mail.gmail.com>
Precedence: bulk

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

Hello hackers,

I reviewed the REFRESH MATERIALIZED VIEW ... WHERE patch and had a few
questions around concurrency semantics.

- The original DELETE -> INSERT approach exposing a consistency gap makes
sense, especially once tuple locks disappear after DELETE. The newer FOR
UPDATE + single-CTE approach seems safer, though I wonder whether
overlapping refreshes could still encounter deadlock scenarios around
UPSERT conflicts.

- The CONCURRENTLY behavior also feels somewhat unintuitive here. With
WHERE refreshes, the non-CONCURRENT path appears more permissive for
writers than CONCURRENTLY WHERE, which seems opposite to the expectation
established by normal REFRESH MATERIALIZED VIEW semantics.

- It may also help to document the intended guarantees around overlapping
partial refreshes and concurrent DML on base tables.

Overall, the use case seems quite valuable for selective high-churn refresh
workloads.

Thanks for working on this patch.

Regards,
Vellaipandiyan

On Thu, May 21, 2026 at 10:44=E2=80=AFAM Adam Brusselback <adambrusselback@=
gmail.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
>
> This allows the user to restrict the refresh operation to a subset of the
> view. The qualification is applied to the view's output columns. The
> optimizer can then push this condition down to the underlying base tables=
,
> avoiding a full scan when only a known subset of data has changed.
>
> Implementation notes:
>
> 1. The grammar accepts an optional WHERE clause. We forbid volatile
> functions in the clause to ensure correctness.
>
> 2. Non-Concurrent Partial Refresh: When `CONCURRENTLY` is not specified,
> the operation performs an in-place modification using a `ROW EXCLUSIVE`
> lock.
>     *   This mode requires a unique index to ensure constraint violations
> are handled correctly (e.g., when a row's values change such that it
> "drifts" into or out of the `WHERE` clause scope).
>     *   It executes a Prune + Upsert strategy:
>         * `DELETE` all rows in the materialized view that match the
> `WHERE` clause.
>         * `INSERT` the new data from the source query.
>     *   It uses `ON CONFLICT DO UPDATE` during the insert phase to handle
> concurrency edge cases, ensuring the refresh is robust against constraint
> violations.
>
> 3. Concurrent Partial Refresh: When `CONCURRENTLY` is specified, it uses
> the existing diff/merge infrastructure (`refresh_by_match_merge`), limiti=
ng
> the scope of the diff (and the temporary table population) to the rows
> matching the predicate. This requires an `EXCLUSIVE` lock and a unique
> index, consistent with existing concurrent refresh behavior. It is much
> slower than `Non-Concurrent Partial Refresh`
>
> 4. The execution logic uses SPI to inject the predicate into the source
> queries during execution.
>
> I have attached a benchmark suite to validate performance and correctness=
:
>
> *   `setup.sql`: Creates a schema `mv_benchmark` modeling an invoicing
> system (`invoices` and `invoice_lines`). It includes an aggregated
> materialized view (`invoice_summary`) and a control table
> (`invoice_summary_table`).
> *   `workload_*.sql`: pgbench scripts simulating a high-churn environment
> (45% inserts, 10% updates, 45% deletes) to maintain roughly stable datase=
t
> sizes while generating significant refresh work.
> *   `run_benchmark_comprehensive.sh`: Orchestrates the benchmark across
> multiple scale factors and concurrency levels.
>
> The benchmark compares strategies for keeping a summary up to date (vs
> baseline):
> *   Partial Refresh: Triggers on the base table collect modified IDs and
> execute `REFRESH MATERIALIZED VIEW ... WHERE ...`.
> *   Materialized Table (Control): A standard table maintained via complex
> PL/pgSQL triggers (the traditional manual workaround).
> *   Full Refresh (Legacy): Manually refresh the view after changes.
>
> Results are below:
> Concurrency: 1 client(s)
>
> -------------------------------------------------------------------------=
---------
> Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
> ---------- ------ | ------------ | ------------ ------------ ------------
> 20000           1 | 5309.05      | 0.002x        0.437x        0.470x
>
> 20000          50 | 1209.32      | 0.010x        0.600x        0.598x
>
> 20000        1000 | 56.05        | 0.164x        0.594x        0.576x
>
> 400000          1 | 5136.91      | 0    x        0.450x        0.487x
>
> 400000         50 | 1709.17      | 0    x        0.497x        0.482x
>
> 400000       1000 | 110.35       | 0.006x        0.507x        0.460x
>
>
> Concurrency: 4 client(s)
>
> -------------------------------------------------------------------------=
---------
> Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
> ---------- ------ | ------------ | ------------ ------------ ------------
> 20000           1 | 19197.50     | 0x            0.412x        0.435x
>
> 20000          50 | 1016.14      | 0.007x        0.966x        1.036x
>
> 20000        1000 | 9.94         | 0.708x        1.401x        1.169x
>
> 400000          1 | 19637.36     | 0x            0.436x        0.483x
>
> 400000         50 | 4669.32      | 0x            0.574x        0.566x
>
> 400000       1000 | 23.26        | 0.029x        1.147x        0.715x
>
>
> Concurrency: 8 client(s)
>
> -------------------------------------------------------------------------=
---------
> Scale       Batch | Baseline TPS | Full (Rel)   Partial (Rel) Table (Rel)
> ---------- ------ | ------------ | ------------ ------------ ------------
> 20000           1 | 30358.32     | 0x            0.440x        0.457x
> 20000          50 | 262.75       | 0.026x        2.943x        2.740x
> 20000        1000 | 11.28        | 0.575x        0.840x        0.578x
> 400000          1 | 36007.15     | 0x            0.430x        0.464x
> 400000         50 | 6664.58      | 0x            0.563x        0.494x
> 400000       1000 | 11.61        | 0.058x        1.000x        1.277x
>
>
>
> In these tests, the partial refresh behaves as O(delta) rather than
> O(total), performing comparably to the manual PL/pgSQL approach but with
> significantly lower code complexity for the user.
>
> I recognize that adding a WHERE clause to REFRESH is an extension to the
> SQL standard. I believe the syntax is intuitive, but I am open to
> discussion regarding alternative implementation strategies or syntax if t=
he
> community feels a different approach is warranted.
>
> New regression tests are included in the patch.
>
> This is my first time submitting a patch to PostgreSQL, so please bear
> with me if I've missed anything or made any procedural mistakes. I'm happ=
y
> to address any feedback.
>
> Thanks,
> Adam Brusselback
>

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

<div dir=3D"ltr">Hello hackers,<br><br>I reviewed the REFRESH MATERIALIZED =
VIEW ... WHERE patch and had a few questions around concurrency semantics.<=
br><br>- The original DELETE -&gt; INSERT approach exposing a consistency g=
ap makes sense, especially once tuple locks disappear after DELETE. The new=
er FOR UPDATE + single-CTE approach seems safer, though I wonder whether ov=
erlapping refreshes could still encounter deadlock scenarios around UPSERT =
conflicts.<br><br>- The CONCURRENTLY behavior also feels somewhat unintuiti=
ve here. With WHERE refreshes, the non-CONCURRENT path appears more permiss=
ive for writers than CONCURRENTLY WHERE, which seems opposite to the expect=
ation established by normal REFRESH MATERIALIZED VIEW semantics.<br><br>- I=
t may also help to document the intended guarantees around overlapping part=
ial refreshes and concurrent DML on base tables.<br><br>Overall, the use ca=
se seems quite valuable for selective high-churn refresh workloads.<br><br>=
Thanks for working on this patch.<br><br>Regards,<br>Vellaipandiyan</div><b=
r><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Thu, May 21, 2026 at 10:44=E2=80=AFAM Adam Brusselback &=
lt;<a href=3D"mailto:adambrusselback@gmail.com">adambrusselback@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr">Attached is a patch implementing support for a WHERE clause =
in REFRESH MATERIALIZED VIEW.<br><br>The syntax allows for targeted refresh=
es:<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 customer_id =3D 42;<br>=C2=A0 =C2=A0 REFRESH MATERIALIZED VIEW mv=
 WHERE order_date &gt;=3D &#39;2023-01-01&#39;;<br><br>I was inspired to im=
plement this feature 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>This allow=
s the user to restrict the refresh operation to a subset of the view. The q=
ualification is applied to the view&#39;s output columns. The optimizer can=
 then push this condition down to the underlying base tables, avoiding a fu=
ll scan when only a known subset of data has changed.<br><br>Implementation=
 notes:<br><br>1. The grammar accepts an optional WHERE clause. We forbid v=
olatile functions in the clause to ensure correctness.<br><br>2. Non-Concur=
rent Partial Refresh: When `CONCURRENTLY` is not specified, the operation p=
erforms an in-place modification using a `ROW EXCLUSIVE` lock.<br>=C2=A0 =
=C2=A0 * =C2=A0 This mode requires a unique index to ensure constraint viol=
ations are handled correctly (e.g., when a row&#39;s values change such tha=
t it &quot;drifts&quot; into or out of the `WHERE` clause scope).<br>=C2=A0=
 =C2=A0 * =C2=A0 It executes a Prune + Upsert strategy:<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 * `DELETE` all rows in the materialized view that match the `=
WHERE` clause.<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 * `INSERT` the new data from =
the source query.<br>=C2=A0 =C2=A0 * =C2=A0 It uses `ON CONFLICT DO UPDATE`=
 during the insert phase to handle concurrency edge cases, ensuring the ref=
resh is robust against constraint violations.<br><br>3. Concurrent Partial =
Refresh: When `CONCURRENTLY` is specified, it uses the existing diff/merge =
infrastructure (`refresh_by_match_merge`), limiting the scope of the diff (=
and the temporary table population) to the rows matching the predicate. Thi=
s requires an `EXCLUSIVE` lock and a unique index, consistent with existing=
 concurrent refresh behavior. It is much slower than `Non-Concurrent Partia=
l Refresh` <br><br>4. The execution logic uses SPI to inject the predicate =
into the source queries during execution.<br><br>I have attached a benchmar=
k suite to validate performance and correctness:<br><br>* =C2=A0 `setup.sql=
`: Creates a schema `mv_benchmark` modeling an invoicing system (`invoices`=
 and `invoice_lines`). It includes an aggregated materialized view (`invoic=
e_summary`) and a control table (`invoice_summary_table`).<br>* =C2=A0 `wor=
kload_*.sql`: pgbench scripts simulating a high-churn environment (45% inse=
rts, 10% updates, 45% deletes) to maintain roughly stable dataset sizes whi=
le generating significant refresh work.<br>* =C2=A0 `run_benchmark_comprehe=
nsive.sh`: Orchestrates the benchmark across multiple scale factors and con=
currency levels.<br><br>The benchmark compares strategies for keeping a sum=
mary up to date (vs baseline):<br>* =C2=A0 Partial Refresh: Triggers on the=
 base table collect modified IDs and execute `REFRESH MATERIALIZED VIEW ...=
 WHERE ...`.<br>* =C2=A0 Materialized Table (Control): A standard table mai=
ntained via complex PL/pgSQL triggers (the traditional manual workaround).<=
br>* =C2=A0 Full Refresh (Legacy): Manually refresh the view after changes.=
<br><br>Results are below:<br>Concurrency: 1 client(s)<br>-----------------=
-----------------------------------------------------------------<br>Scale =
=C2=A0 =C2=A0 =C2=A0 Batch | Baseline TPS | Full (Rel) =C2=A0 Partial (Rel)=
 Table (Rel) <br>---------- ------ | ------------ | ------------ ----------=
-- ------------<br>20000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | 5309.05 =C2=
=A0 =C2=A0 =C2=A0| 0.002x =C2=A0 =C2=A0 =C2=A0 =C2=A00.437x =C2=A0 =C2=A0 =
=C2=A0 =C2=A00.470x =C2=A0 =C2=A0 =C2=A0 <br>20000 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A050 | 1209.32 =C2=A0 =C2=A0 =C2=A0| 0.010x =C2=A0 =C2=A0 =C2=A0 =
=C2=A00.600x =C2=A0 =C2=A0 =C2=A0 =C2=A00.598x =C2=A0 =C2=A0 =C2=A0 <br>200=
00 =C2=A0 =C2=A0 =C2=A0 =C2=A01000 | 56.05 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.1=
64x =C2=A0 =C2=A0 =C2=A0 =C2=A00.594x =C2=A0 =C2=A0 =C2=A0 =C2=A00.576x =C2=
=A0 =C2=A0 =C2=A0 <br>400000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01 | 5136.91 =
=C2=A0 =C2=A0 =C2=A0| 0 =C2=A0 =C2=A0x =C2=A0 =C2=A0 =C2=A0 =C2=A00.450x =
=C2=A0 =C2=A0 =C2=A0 =C2=A00.487x =C2=A0 =C2=A0 =C2=A0 <br>400000 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 50 | 1709.17 =C2=A0 =C2=A0 =C2=A0| 0 =C2=A0 =C2=A0x =
=C2=A0 =C2=A0 =C2=A0 =C2=A00.497x =C2=A0 =C2=A0 =C2=A0 =C2=A00.482x =C2=A0 =
=C2=A0 =C2=A0 <br>400000 =C2=A0 =C2=A0 =C2=A0 1000 | 110.35 =C2=A0 =C2=A0 =
=C2=A0 | 0.006x =C2=A0 =C2=A0 =C2=A0 =C2=A00.507x =C2=A0 =C2=A0 =C2=A0 =C2=
=A00.460x =C2=A0 =C2=A0 =C2=A0 <br><br>Concurrency: 4 client(s)<br>--------=
--------------------------------------------------------------------------<=
br>Scale =C2=A0 =C2=A0 =C2=A0 Batch | Baseline TPS | Full (Rel) =C2=A0 Part=
ial (Rel) Table (Rel) <br>---------- ------ | ------------ | ------------ -=
----------- ------------<br>20000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | 19=
197.50 =C2=A0 =C2=A0 | 0x =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00.412x =
=C2=A0 =C2=A0 =C2=A0 =C2=A00.435x =C2=A0 =C2=A0 =C2=A0 <br>20000 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A050 | 1016.14 =C2=A0 =C2=A0 =C2=A0| 0.007x =C2=A0 =
=C2=A0 =C2=A0 =C2=A00.966x =C2=A0 =C2=A0 =C2=A0 =C2=A01.036x =C2=A0 =C2=A0 =
=C2=A0 <br>20000 =C2=A0 =C2=A0 =C2=A0 =C2=A01000 | 9.94 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 | 0.708x =C2=A0 =C2=A0 =C2=A0 =C2=A01.401x =C2=A0 =C2=A0 =C2=A0 =
=C2=A01.169x =C2=A0 =C2=A0 =C2=A0 <br>400000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A01 | 19637.36 =C2=A0 =C2=A0 | 0x =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A00.436x =C2=A0 =C2=A0 =C2=A0 =C2=A00.483x =C2=A0 =C2=A0 =C2=A0 <br>400=
000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 50 | 4669.32 =C2=A0 =C2=A0 =C2=A0| 0x =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00.574x =C2=A0 =C2=A0 =C2=A0 =C2=A00.5=
66x =C2=A0 =C2=A0 =C2=A0 <br>400000 =C2=A0 =C2=A0 =C2=A0 1000 | 23.26 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0| 0.029x =C2=A0 =C2=A0 =C2=A0 =C2=A01.147x =C2=A0 =
=C2=A0 =C2=A0 =C2=A00.715x =C2=A0 =C2=A0 =C2=A0 <br><br>Concurrency: 8 clie=
nt(s)<br>------------------------------------------------------------------=
----------------<br>Scale =C2=A0 =C2=A0 =C2=A0 Batch | Baseline TPS | Full =
(Rel) =C2=A0 Partial (Rel) Table (Rel) <br>---------- ------ | ------------=
 | ------------ ------------ ------------<br>20000 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 1 | 30358.32 =C2=A0 =C2=A0 | 0x =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A00.440x =C2=A0 =C2=A0 =C2=A0 =C2=A00.457x<br>20000 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A050 | 262.75 =C2=A0 =C2=A0 =C2=A0 | 0.026x =C2=A0 =C2=A0=
 =C2=A0 =C2=A02.943x =C2=A0 =C2=A0 =C2=A0 =C2=A02.740x<br>20000 =C2=A0 =C2=
=A0 =C2=A0 =C2=A01000 | 11.28 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.575x =C2=A0 =
=C2=A0 =C2=A0 =C2=A00.840x =C2=A0 =C2=A0 =C2=A0 =C2=A00.578x<br>400000 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01 | 36007.15 =C2=A0 =C2=A0 | 0x =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00.430x =C2=A0 =C2=A0 =C2=A0 =C2=A00.464x<br>=
400000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 50 | 6664.58 =C2=A0 =C2=A0 =C2=A0| 0x =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00.563x =C2=A0 =C2=A0 =C2=A0 =C2=A0=
0.494x<br>400000 =C2=A0 =C2=A0 =C2=A0 1000 | 11.61 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| 0.058x =C2=A0 =C2=A0 =C2=A0 =C2=A01.000x =C2=A0 =C2=A0 =C2=A0 =C2=A01.=
277x<br><br><br><br>In these tests, the partial refresh behaves as O(delta)=
 rather than O(total), performing comparably to the manual PL/pgSQL approac=
h but with significantly lower code complexity for the user.<br><br>I recog=
nize that adding a WHERE clause to REFRESH is an extension to the SQL stand=
ard. I believe the syntax is intuitive, but I am open to discussion regardi=
ng alternative implementation strategies or syntax if the community feels a=
 different approach is warranted.<br><br>New regression tests are included =
in the patch.<br><br>This is my first time submitting a patch to PostgreSQL=
, so please bear with me if I&#39;ve missed anything or made any procedural=
 mistakes. I&#39;m happy to address any feedback.<br><br>Thanks,<br>Adam Br=
usselback</div>
</blockquote></div>

--000000000000a3ad5b06524d0991--