That way you increase contention and harm scalability. In sqlite it does not matter as writes are single-threaded anyway. But in other DBMSes it does. What's more: in PostgreSQL you increase bloat as every update creates a new row version.

Most of the times the solution is not pre-aggregation but proper indexes on foreign key columns (missing fk indexes is one of the most common mistakes in RDB design).

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.

Yeah if index + count(*) works fine, then that is enough. This will usually not be a purely index-only scan though in practice, in MVCC databases. Since it sometimes has to inspect the actual row to see if its visible to the current transaction or not. So it's "mostly" an index scan. If you're not getting an index-only scan, count(*) can become a bottleneck in my experience even with FK indexes.

[All just my understanding]