3 ms·

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 t

• edmundsauto 4y ago
  I also wish this were in core posgres, but if you use a build tool like DBT you can enable this type of thing.
  • snicker7 4y ago
    Interestingly, DBT does not support creating materialized views.
    • mritchie712 4y ago
      yeah, this issue has been open for years. We need them for Clickhouse for our product. https://github.com/dbt-labs/dbt-core/issues/1162 https://github.com/dbt-labs/dbt-core/issues/1162
    • edmundsauto 4y ago
      What's the effective difference between a matview and just a table?
• gunnarmorling 4y ago
  That work towards incrementally updated views is happening and progressing. For now, it's a separate extension, though: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm.
• cadbox1 4y ago
  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/backend/services/rollup/rollup-router.ts#L22-L36 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 https://prawn.cadell.dev/ https://prawn.cadell.dev/