4 ms·

I've found them to be pretty limited as a feature - really they are just some syntactical sugar over "create table as select ... ". If you start to need any ki

I've found them to be pretty limited as a feature - really they are just some syntactical sugar over "create table as select ... ". If you start to need any kind of flexibility over refreshing the table, you'll need to move to using a plain old table anyway.

asah 6y ago
This was my impression too - without incremental update, I don't see the benefit ?
- gavinray 6y ago
  I'm someone with limited DB wizardry, but a large admiration for Postgres so take this for what it's worth: It's my understanding that there is a proposed feature, with a proof-of-concept floating around, called "Incremental View Maintenance", which is auto-update of dependent Materialized Views when their dependent base-tables change. https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... Materialized view with IVM option created by CRATE INCREMENTAL MATERIALIZED VIEW command. Noted this syntax is just tentative, so it may be changed. When a materialized view is created, AFTER triggers are internally created on its all base tables. When the base tables is modified (INSERT, DELETE, UPDATE), this view is updated incrementally in the trigger function. I think there are some performance/technical things that are getting sorted out with this, but it would be a killer feature. It's the one thing I wish Postgres had that it doesn't. You have to use triggers to do this currently.
  - Normal_gaussian 6y ago
    Yes, this is a killer feature. There are many instances of what are essentially application side caches that try (and often fail) to do this; its non-trivial to do right, and I am greatly looking forwards to it. In particular, an event-sourced world becomes much easier to implement and maintain with the DB handling all the incremental magic.
- Mouse47 6y ago
  For what it's worth SQL server supports incremental updates for materialized views. I think they're called indexed views, though
  - lazulicurio 6y ago
    Yep! And they're pretty magical. Beyond materializing joins, you can use COUNT_BIG to greatly speed up common DISTINCT queries and the two row trick[1] to enforce complex constraints. [1] https://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/ https://spaghettidba.com/2011/08/03/enforcing-complex-constr...
    - hultner 6y ago
      Well they are magical until you need to do something a bit more complex, like window functions, where they’ll not work again. I’ve worked around this by using indexed views for sub parts of a larger regular view but it feels a bit hacks since you need to hint the server to actually use the indexed view or it will just use it as a regular view which I don’t understand the reasoning behind. But for simple things they’re magical.
      - lazulicurio 6y ago
        > Well they are magical until you need to do something a bit more complex, like window functions. The best method I've personally found for window functions is cross/outer apply + narrow indexes with included columns. A lot of times you can get away without an indexed view at all. > you need to hint the server to actually use the indexed view or it will just use it as a regular view which I don’t understand the reasoning behind SQL Server Enterprise will use indexed views automatically. But you gotta shell out the big bucks for that improved query planner.

asah 6y ago

This was my impression too - without incremental update, I don't see the benefit ?

gavinray 6y ago
I'm someone with limited DB wizardry, but a large admiration for Postgres so take this for what it's worth: It's my understanding that there is a proposed feature, with a proof-of-concept floating around, called "Incremental View Maintenance", which is auto-update of dependent Materialized Views when their dependent base-tables change. https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... Materialized view with IVM option created by CRATE INCREMENTAL MATERIALIZED VIEW command. Noted this syntax is just tentative, so it may be changed. When a materialized view is created, AFTER triggers are internally created on its all base tables. When the base tables is modified (INSERT, DELETE, UPDATE), this view is updated incrementally in the trigger function. I think there are some performance/technical things that are getting sorted out with this, but it would be a killer feature. It's the one thing I wish Postgres had that it doesn't. You have to use triggers to do this currently.
- Normal_gaussian 6y ago
  Yes, this is a killer feature. There are many instances of what are essentially application side caches that try (and often fail) to do this; its non-trivial to do right, and I am greatly looking forwards to it. In particular, an event-sourced world becomes much easier to implement and maintain with the DB handling all the incremental magic.
Mouse47 6y ago
For what it's worth SQL server supports incremental updates for materialized views. I think they're called indexed views, though
- lazulicurio 6y ago
  Yep! And they're pretty magical. Beyond materializing joins, you can use COUNT_BIG to greatly speed up common DISTINCT queries and the two row trick[1] to enforce complex constraints. [1] https://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/ https://spaghettidba.com/2011/08/03/enforcing-complex-constr...
  - hultner 6y ago
    Well they are magical until you need to do something a bit more complex, like window functions, where they’ll not work again. I’ve worked around this by using indexed views for sub parts of a larger regular view but it feels a bit hacks since you need to hint the server to actually use the indexed view or it will just use it as a regular view which I don’t understand the reasoning behind. But for simple things they’re magical.
    - lazulicurio 6y ago
      > Well they are magical until you need to do something a bit more complex, like window functions. The best method I've personally found for window functions is cross/outer apply + narrow indexes with included columns. A lot of times you can get away without an indexed view at all. > you need to hint the server to actually use the indexed view or it will just use it as a regular view which I don’t understand the reasoning behind SQL Server Enterprise will use indexed views automatically. But you gotta shell out the big bucks for that improved query planner.