Comment by tsimionescu
1 day ago
The way you model data and store it in your database is fundamentally a part of your business logic. The same data can be modeled in many different ways, with different trade-offs for different use cases. Especially if you have a large amount of data, you can't just work with it as is, you need to know how you will use it and model it in a way that makes the common operations fast enough. As your application evolves, this may change, and even require data migrations.
None of this means you have to or even should use stored procedures, triggers, or listen/notify. I'm just making the point that there is no clean separation between "data" and "business logic".
Can't upvote this enough. The point is not that procedures outside of the DB is wrong, nor is it that procedures should always go into the DB. It's that you should look at the context and decide what the best way to solve the problem is.
Agreed. I used triggers frequently for things like incrementing/decrementing count fields for dashboards because it's the only way to guarantee those numbers are correct while ensuring something in the application hasn't bypassed a callback or handler to modify the data.
You only need to cover three scenarios and it's very simple to implement. Recorded added +1, Record removed -1, Record moved +1 & -1.
If you have counts that are more complicated, it doesn't work but this solution easily beats semi-frequent COUNT queries.