Unlogged materialized views
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253126psql -h localhost -U postgresBuilt from patchset v5 (message #5), August 25, 2026 at 12:39 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253126_5 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253126_5 && git checkout t253126_5Patchset v5 (message #5) is on t253126_5
Hello!
I like to bring up supporting unlogged materialized views again. I am
aware that there were several discussions about them in the past, but
all of those seem quite old, the last one is from 7 years ago[1]/messages/by-id/CAKLmikNZ6qquFkh_eF9kWm5daTJy0PXhNt-xHT+iniYfaKkgZg@mail.gmail.com.
There are also two other related discussion with recent activity:
* incremental view maintenance[2]/messages/by-id/CAE8JnxMPbVtHgApfQ2Gny-a+nbVQN+=Zm4-yhsY7EJTFWVm1nQ@mail.gmail.com
* support for WHERE clauses for materialized view refresh[3]/messages/by-id/CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com
The reasoning behind all of these is probably similar as my root cause
for this proposal: refresh materialized view on huge views is slow and
results in wal churn. The above patches aim to make the changes
smaller, while I'd like to revisit the possibility of making
materialized views unlogged to avoid wal completely.
In some scenarios, crash-safety isn't a requirement for them, as it is
okay if certain features aren't available until a materialized view
rebuilds, or if queries complete slower until it does. Unlogged
materialized views would be perfect for this.
The general consensus in the old threads seems to be that unlogged
materialized views should become unpopulated after a crash instead of
empty, and that's also my assessment. The issue was and is just the
how:
pg_class currently has a relispopulated field: 0 for unpopulated
materialized views, 1 otherwise. With this approach, making them
unpopulated at or after recovery would require catalog changes during
recovery, which isn't possible.
That's why my suggestion is an alternative approach: refactor this field:
* rename it to relpopulated to avoid confusion (relispopulated becomes
a helper function)
* make it an integer: 1 for anything logged that is populated, even
normal materialized views, 0 for unpopulated logged materialized
views, and a special epoch counter for unlogged materialized views
That counter is a "concatenation" of two things: the timelineid, and a
newly introduced pg_control field, the unlogged reset generation
counter (unloggedResetGen): starts at 0, increases by 1 every time
during crash recovery.
When an unlogged materialized view is populated, we write the current
counter to pg_class.relpopulated. When we use it, we check if the
stored epoch counter is the same as the current one for the server -
if not, it's empty. We have to also disable access during recovery.
With that, standby behavior, replica promotion, crash recovery, and
everything else I tested seem to work as expected.
In my initial patchset I didn't address two possible improvements, but
I want to mention them:
* automatically refreshing unlogged views after crash recovery in a
background worker
* providing a health-check function that tells users if all unlogged
materialized views were already refreshed
These could be implemented either in core/contrib or as independent extensions.
Please see the attached patchset, what do you think about this approach?
[1]: /messages/by-id/CAKLmikNZ6qquFkh_eF9kWm5daTJy0PXhNt-xHT+iniYfaKkgZg@mail.gmail.com
[2]: /messages/by-id/CAE8JnxMPbVtHgApfQ2Gny-a+nbVQN+=Zm4-yhsY7EJTFWVm1nQ@mail.gmail.com
[3]: /messages/by-id/CAMjNa7eFzTQ5=oZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw@mail.gmail.com
Attachments:
t253126_10001-Add-durable-unlogged-reset-generation-counter.patchapplication/octet-stream; name=0001-Add-durable-unlogged-reset-generation-counter.patchDownload+59-2
0003-Add-unlogged-materialized-views.patchapplication/octet-stream; name=0003-Add-unlogged-materialized-views.patchDownload+953-20
0002-Replace-pg_class.relispopulated-with-epoch-capable-r.patchapplication/octet-stream; name=0002-Replace-pg_class.relispopulated-with-epoch-capable-r.patchDownload+205-61
Hi Zsolt,
I like to bring up supporting unlogged materialized views again.
+1 from me. Most of the MVs I've built over the years were:
denormalized lookup tables behind a search screen, pre-aggregated
rollups for reporting, cached joins that were too slow to run per
request.
Losing some of those to a crash would have been fine as refresh
time for them wasn't a huge issue upon recovery.
* automatically refreshing unlogged views after crash recovery in a
background worker
Something still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.
Thanks,
Adam
Hey Zsolt,
+1
Will take a look at the patch
Thanks,
Dharin
On Mon, Aug 10, 2026 at 9:13 PM Adam Brusselback <adambrusselback@gmail.com>
wrote:
Show quoted text
Hi Zsolt,
I like to bring up supporting unlogged materialized views again.
+1 from me. Most of the MVs I've built over the years were:
denormalized lookup tables behind a search screen, pre-aggregated
rollups for reporting, cached joins that were too slow to run per
request.
Losing some of those to a crash would have been fine as refresh
time for them wasn't a huge issue upon recovery.* automatically refreshing unlogged views after crash recovery in a
background workerSomething still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.Thanks,
Adam
Thanks for the feedback!
Something still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.
I agree that this would be more useful in core or in contrib, and I can implement it as part of this patch or as a follow up, but before that, I'd like some feedback if people agree with the current approach or not, as with a different approach we might need a completely different solution for that.
Hello!
I attached a rebased version, otherwise there are no changes.
On Tue, Aug 11, 2026 at 10:08 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
Show quoted text
Thanks for the feedback!
Something still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.I agree that this would be more useful in core or in contrib, and I can implement it as part of this patch or as a follow up, but before that, I'd like some feedback if people agree with the current approach or not, as with a different approach we might need a completely different solution for that.