News for hackers. Stuff that matters.
Re:Data denormalization is broken by nostrademons on Friday September 30, 2016 @08:17PM (#12615980)
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.

Re:Data denormalization is broken by catnaroek on Friday September 30, 2016 @08:29PM (#12616011)
> 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.