> 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.
Re:Data denormalization is broken
by icebraining on Saturday October 01, 2016 @04:16AM
(#12617010)
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/
Not affiliated, not even a user, just found it interesting when it was posted here.
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.