I have to admit I do the same as GP here. However I would always do both - index and keep a count on the parent as a first step.
increase contention and harm scalability
Contention, concurrent connections, high-throughput and the associated race conditions are absolute table stakes for RDBMs. They just won't be taken seriously if they can't handle thousands of concurrent updates. So imho for 90% of projects this just won't be an issue.
PostgreSQL you increase bloat as every update creates a new row version
This is true, but the complexity has to live somewhere. There will be as many rows added to a materialized view as there will be in the original table.
Most of the times the solution is not pre-aggregation
This is wrong. Caching = pre-aggregation and is almost always either the solution or a part of the solution to scalability, latancy etc. Don't compute what you can retrieve.
Comments
I have to admit I do the same as GP here. However I would always do both - index and keep a count on the parent as a first step.
Contention, concurrent connections, high-throughput and the associated race conditions are absolute table stakes for RDBMs. They just won't be taken seriously if they can't handle thousands of concurrent updates. So imho for 90% of projects this just won't be an issue.
This is true, but the complexity has to live somewhere. There will be as many rows added to a materialized view as there will be in the original table.
This is wrong. Caching = pre-aggregation and is almost always either the solution or a part of the solution to scalability, latancy etc. Don't compute what you can retrieve.