Re:PostgreSQL views and materialized views and how they infl...
by garyclarke27 on Thursday July 14, 2022 @12:59PM
(#32098603)
Postgres is an amazing database. It’s only significant weakness now is in Materialized views, with their lack of incremental refresh. Was disappointing to see there was no progress towards this in v15.
[ Parent
| on HN ]
Re:PostgreSQL views and materialized views and how they infl...
by gunnarmorling on Thursday July 14, 2022 @03:03PM
(#32100161)
That work towards incrementally updated views is happening and progressing. For now, it's a separate extension, though: https://github.com/sraoss/pg_ivm.
Re:PostgreSQL views and materialized views and how they infl...
by cadbox1 on Thursday July 14, 2022 @08:34PM
(#32103115)
I wanted incremental refresh in Postgres as well and found that you can manage your own table to get something close.
Basically you create a regular table in place of a materialised one, only aggregate data newer than what's currently in the table then store the new aggregates in table. Repeat this an interval.
https://github.com/cadbox1/prawn-stack/blob/master/src/backe...
I use this to show page view data aggregated by hour without calculating it on each request using Lambda
Re:PostgreSQL views and materialized views and how they infl...
by edmundsauto on Thursday July 14, 2022 @02:03PM
(#32099452)
I also wish this were in core posgres, but if you use a build tool like DBT you can enable this type of thing.
Re:PostgreSQL views and materialized views and how they infl...
by snicker7 on Thursday July 14, 2022 @02:45PM
(#32099957)
Interestingly, DBT does not support creating materialized views.
Re:PostgreSQL views and materialized views and how they infl...
by mritchie712 on Thursday July 14, 2022 @02:56PM
(#32100079)
yeah, this issue has been open for years. We need them for Clickhouse for our product.
Re:PostgreSQL views and materialized views and how they infl...
by edmundsauto on Friday July 15, 2022 @01:49AM
(#32104994)
What's the effective difference between a matview and just a table?