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".
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.
I'm unlikely to get it myself today, and by tomorrow I've probably already forgotten it :-(
You're self-aware and are writing about it, why not maintain and add it to your todo list if this is a recurring issue?
But it's oversimplified a case of "high queue load f* up the availability/timings for other DB operations" (and themself).
And thats a generic problem you have, even if just due to "generic CPU/WAL/disk load" if you put your queue into your DB even iff that specific lock would be somehow solved with some atomic concurrent algorithms or similar (not sure if that even is possible).
So in general make your storage db, and queue a different service (and you cache too), even if it uses the same kind of storage. (Through technically there are clever in-between solutions which run their own queue service but still use you DB for final storage but have a ton of caching, in memory locking etc. to remove a huge part of the load from the DB. )
In databases where your domain is also your physical data model, coupling business logic to the database can work quite well, if the DBMS supports that.
https://medium.com/@paul_42036/entity-workflows-for-event-dr...
Then why bother with a relational database? Relations and schemas are business logic, and I'll take all the data integrity I can get.
I'm personally Code is King, and I have my reasons (like everyone else)
Now if I could specify that I want Order.customer_name to come from a specific other model and be updated automatically the ORM could automatically create a trigger to update that field when the customer table is updated.
Obviously this is a very simplistic example but there are many more, including versioning and soft deletes that could be incredibly useful. But the key is that the ORM has to generate the code for the triggers and stored procedures. Doing that manually is possible now but (a) uses a different language even than regular SQL which not everyone is familiar with, and (b) there is no type checking for what you are doing. The ORM model definitions are the main source of truth about the shape of your database, so I want to use them as such.
Data locality is king. Everything comes down to physical things such as blocks on the SSD, network interconnect, RAM, L3, L2, L1 cache and registers. Are those customer fields in the same page as whatever else you need? If so, most of the work is already done. Yes, you can save some network bandwidth transferring things that aren't needed but does it matter? It might but it might not. The key is to know what matters and reason about things from the perspective of the machines actually doing the work.
Generally customers don't care about religious views. Make understanding the actual machine and associated latencies your religion instead. The reason to write a stored proc or do some processing in the database is entirely about data locality, not to keep the drooling masses from messing things up. A library is fine for that.
The only reasons I can think of:
- you're rewriting a legacy system and migrate parts incrementally
- data compliance
- you're running a dangerous database setup
I try my best to avoid putting any business logic inside databases and see stored procedures only as a temporary solution.
The DB can make much stronger guarantees about transactions and updates the closer that logic happens to itself. In the world of cloud computing, this can be a cost savings for ingress/egress too.
Maybe throw your colleagues out the window instead if they don't know what they are talking about. I'm not anti/pro SPROC at all, but I am anti anti-reality. People that don't understand the vast differences in latencies between in process and out of process work should not exist in the industry.
There are things that work better, are safer and simpler to do on the database, and things that work better, are safer and simpler in code. And those things might change depending on context, technology, requirements, size of project, experience of contributors, etc.
Forcing round pegs into square holes will always lead to brittle code and brittle products, often for more cost (mental and financial!) than actually using each tool correctly.
Both have their place in business.
RDBMS are extremely well-tested pieces of software that do their job incredibly well. To think that you could do better, or even equally as well, is hubris. If you want to trade those guarantees for “velocity” go right ahead, but you also need to take into account the inevitable incidents and recoveries that will occur.
I guess some will argue that their business logic is special and really is so tightly coupled to the data definition that it belongs in the database, and I’m not going to claim those use cases don’t exist, but I’ve seen over-coupling far more often than under-coupling.
This is why I say: Applications come and go, but data is forever.
Back to the topic: Lots of potential bugs and data corruption issues are solved by moving part of the business logic to the database. Other people already covered two things: data validation and queue atomicity.
On the other hand, lots of potential issues can also arise by putting other parts of business logic to the database, for example, calling HTTPS endpoints from inside the DB itself is highly problematic.
The reality is that the world is not black and white, and being an engineer is about navigating this grey area.
You can either execute SQL in your migration or use add_check_constraint.
For sharding, Vitess kind of supports them; Citus fully supports them.
You’re correct that they do impact performance to an extent, but as a counter argument, if your data is incorrect, it doesn’t matter how quickly you wrote it.
Really, FKs are typically implemented internally by RDBMSes as TRIGGERs that do what you expect FKs to do, which means they really are nothing more than syntactic sugar.
I've seen people who disagree with that statement and say that having a separate back end component often leads to overfetching and in-database processing is better. I've worked on some systems where the back end is essentially just passing data to and from stored procedures.
It was blazing fast, but working with it absolutely sucked - though for whatever reason the people who believe that seem to hold those views quite strongly.
Of course, this is sometimes abused and taken to extremes in a microservices architecture where each service has their own database and you end up with nastiness like data duplication and distributed locking.
Not to mention the difficulty in maintaining referential integrity with all of that duplicated data. There are various workarounds, but at that point it feels very much like we’re recreating a shared DB, but shittily, and netting zero benefits.
The DB is - or should be - the source of truth for your application. Also, since practically everyone is using cloud RDBMS with (usually) networked storage, the latency is atrocious. Given those, it seems silly to rely on an application to react to and direct changes to related data.
For example, if you want to soft-delete customer data while maintaining the ability to hard-delete, then instead of having an is_deleted and/or deleted_at column, have a duplicate table or tables, and an AFTER DELETE trigger on the originals that move the tuples to the other tables.
Or if you want to have get_or_create without multiple round trips (and you don’t have Postgres’ MERGE … RETURNING), you can easily accomplish this with a stored procedure.
Using database features shouldn’t be seen as verboten or outdated. What should be discouraged is not treating things like stored procedures and triggers as code. They absolutely should be in VCS, should go the same review process as anything else, and should be well-documented.
More broadly, not knowing how to write SQL is a very solvable problem, and frankly anyone accessing an RDBMS as a regular part of their job should know it. Even if you’re always using an ORM, you should understand what it’s doing so you can understand the EXPLAIN output you’ll probably be looking at eventually.
With entity framework code first, Microsoft made it possible for generations of developers to barely touch a database.
A lot of Devs have poor database skills nowadays.
Which suits the cloud sellers who want to push managed platforms