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