> It’s nice to have that as an automatic feature rather than something that you have to create for yourself with stored procedures (doing e.g. dynamic programming in the DB), but a matview is still a matview — it’s a real table taking up space on disk and getting replicated through the WAL.
Ah, yeah at the end of the day both unlogged materialized views aren't supported by postgres[0]. Also a patch that introduces temporary materialized views is stuck behind some refactoring that started in 2019[1] which is unfortunate.
> (explanation of materialize)
Thanks for this -- I think I get more clearly what you're getting out of materialize. I'm not sure I quite understand the stream analogy because the data being buffered here feels chunked (though I guess it could be incrementally maintained with some more smarts), but I think I understand the issue that basically ongoing queries need access to this temporarily-useful partial result. Basically a cross-session intermediate query result cache.
This feels like it could be reduced to a custom table access method plugin, but if that temporary table patch gets merged it could be even easier to hack something together.
Ah, yeah at the end of the day both unlogged materialized views aren't supported by postgres[0]. Also a patch that introduces temporary materialized views is stuck behind some refactoring that started in 2019[1] which is unfortunate.
> (explanation of materialize)
Thanks for this -- I think I get more clearly what you're getting out of materialize. I'm not sure I quite understand the stream analogy because the data being buffered here feels chunked (though I guess it could be incrementally maintained with some more smarts), but I think I understand the issue that basically ongoing queries need access to this temporarily-useful partial result. Basically a cross-session intermediate query result cache.
This feels like it could be reduced to a custom table access method plugin, but if that temporary table patch gets merged it could be even easier to hack something together.
[0]: https://www.postgresql-archive.org/CREATE-UNLOGGED-MATERIALI...
[1]: https://www.postgresql.org/message-id/CAKLmikMgkuwFKHem%2BSf...