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.