3 ms·

Materialized views are a superior, or at least less error-prone alternative to triggers, since they don't depend on the programmer's ability to get state transi

Materialized views are a superior, or at least less error-prone alternative to triggers, since they don't depend on the programmer's ability to get state transitions right (whenever you manually update aggregates).

nostrademons 10y ago
What's the performance characteristics of materialized views? My understanding is that they require running & storing the full query. For a SELECT COUNT(star) FROM TableName this is trivial, but if it's SELECT COUNT(star) FROM BigTableName WHERE ComplexWhereCondition it can get very expensive. For the simple count/max aggregates on a single relation that the blog post is talking about, the trigger logic seems straightforward - you just need a trigger on a single table, for insert/delete/truncate, + update for max or if there are conditions. If there's more than one relation involved or if the condition is complex, then it can get pretty complicated.
- catnaroek 10y ago
  > My understanding is that they require running & storing the full query. This is right. Having a single materialized view containing nested queries is not a good idea. But you can factor out the nested queries into their own materialized views. The propagation of updates between materialized views can achieve an effect similar to that of complex triggers. I should've been more careful with my original comment, though. I'm not saying triggers are worthless. But, whenever a materialized view can do the job, it's usually preferable.
- icebraining 10y ago
  PipelineDB (based on Postgres) seems to get the best of both worlds by allowing you to specify a query, like materialized views, but refreshing those results based on inserts/updates, like triggers: https://www.pipelinedb.com/ https://www.pipelinedb.com/ Not affiliated, not even a user, just found it interesting when it was posted here.

3 ms·

Materialized views are a superior, or at least less error-prone alternative to triggers, since they don't depend on the programmer's ability to get state transi

by catnaroek 10y ago

nostrademons 10y ago
What's the performance characteristics of materialized views? My understanding is that they require running & storing the full query. For a SELECT COUNT(star) FROM TableName this is trivial, but if it's SELECT COUNT(star) FROM BigTableName WHERE ComplexWhereCondition it can get very expensive. For the simple count/max aggregates on a single relation that the blog post is talking about, the trigger logic seems straightforward - you just need a trigger on a single table, for insert/delete/truncate, + update for max or if there are conditions. If there's more than one relation involved or if the condition is complex, then it can get pretty complicated.
- catnaroek 10y ago
  > My understanding is that they require running & storing the full query. This is right. Having a single materialized view containing nested queries is not a good idea. But you can factor out the nested queries into their own materialized views. The propagation of updates between materialized views can achieve an effect similar to that of complex triggers. I should've been more careful with my original comment, though. I'm not saying triggers are worthless. But, whenever a materialized view can do the job, it's usually preferable.
- icebraining 10y ago
  PipelineDB (based on Postgres) seems to get the best of both worlds by allowing you to specify a query, like materialized views, but refreshing those results based on inserts/updates, like triggers: https://www.pipelinedb.com/ https://www.pipelinedb.com/ Not affiliated, not even a user, just found it interesting when it was posted here.