In general counts are just so commonly needed in 1:N relationships. Specifically counts grouped by "status" of the object on the N side of the relationship. I've never seen a CRUD app where this didn't eventually make the feature list.

So I just pre-emptively break "normal form" and maintain counts in the database right from the start. I either update it with multiple stmts everytime in a txn, or with triggers. In sqlite I prefer triggers mostly because I know the operation and edge cases of sqlite trigger impl better than I know other DBs'.

Caching can just be invalidation-based in this case.

▲

mkleczek 8 days ago | parent | next [-]

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).

	▲	the_sleaze_ 8 days ago \| parent \| next [-]
		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.
	▲	porridgeraisin 8 days ago \| parent \| prev [-]
		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]

▲

lblume 8 days ago | parent | prev | next [-]

Denormalization in order to improve performance like this does not seem like the right tradeoff to me. You are making the database much less robust, and anomalies much more likely, in what should be as simple as what the original post mentions.

▲

wavemode 8 days ago | parent | prev [-]

This shouldn't require denormalization. `SELECT COUNT(*)` ought to be very fast if you have a foreign key index.