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].
There are also two other related discussion with recent activity: * incremental view maintenance[2] * support for WHERE clauses for materialized view refresh[3] 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]: https://www.postgresql.org/message-id/CAKLmikNZ6qquFkh_eF9kWm5daTJy0PXhNt-xHT%2BiniYfaKkgZg%40mail.gmail.com [2]: https://www.postgresql.org/message-id/CAE8JnxMPbVtHgApfQ2Gny-a%2BnbVQN%2B%3DZm4-yhsY7EJTFWVm1nQ%40mail.gmail.com [3]: https://www.postgresql.org/message-id/CAMjNa7eFzTQ5%3DoZMQiB2bMkez5KP4A77JC7SRjeVEkOrh7cUHw%40mail.gmail.com
0001-Add-durable-unlogged-reset-generation-counter.patch
Description: Binary data
0003-Add-unlogged-materialized-views.patch
Description: Binary data
0002-Replace-pg_class.relispopulated-with-epoch-capable-r.patch
Description: Binary data
