Are Stored Procedures and Triggers Anti-Patterns in the Cloud Native World?
blog.yugabyte.com
blog.yugabyte.com
It’s code that’s much harder to get visibility into, harder to track over time, harder to reason about in the context of a larger codebase, and harder to measure and understand the performance implications of.
And for most cases, all this downside with little/no upside.
Sometimes you run into a situation where they’re clearly the best choice, but I would think long and hard about alternatives before embracing them (again, for most projects, not all).
While there is some merit to either end of the spectrum (e.g. not thinking about the schema or SQL much on the one end, letting the ORM do the heavy lifting, vs doing a large amount of in database processing), i've found that preferences in that regard largely depend on what any particular person is familiar with.
Personally, i think that processing data within the database makes sense for large bulk operations, however otherwise databases are largely unsuitable for anything apart from being the data layer for CRUD, or analytic queries - whenever you try to create any sorts of complex algorithms in SQL, without fail you'll end up with code that isn't readable, isn't extensible and doesn't compose as well as even Java libraries would. Databases will also lose out on logging, debugging (e.g. breakpoints) and oftentimes scalability - it's easier to scale instances of a REST API horizontally than it is to scale a database engine, given that most of them scale vertically.
Of course, Yugabyte or TiDB might try to be exceptions in regards to my last point, but the latter doesn't support stored procedures and triggers altogether, so scaling can have serious limitations: https://docs.pingcap.com/tidb/stable/mysql-compatibility
On the other hand, trying to do everything in the app is just asking for horrible schemas that have been automatically generated and aren't as well normalized as they should be, awkward framework specific ORM functionality and pitfalls (Hibernate lazy/eager init in particular), as well as having an insane amount of opportunities to run into the N+1 problem or issues with how linked data is represented and persisted.
In summary, there are pitfalls with either approach and i'd just suggest that any team consider what they know how to use better and what better fits their needs. So far, in my experience, it's mostly doing apps within the application, not shying away from SQL when necessary, but also considering the very few use cases when using stored procedures or triggers indeed makes sense, and then looking for ways to comment them as much as possible. Testing them, however, is another story, which should reflect upon certain shortcomings: https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da...
I would rather reach for them than the currently trendy ORM framework.
In fact, the ideal is to code all in a rdbms-first language. All this complexity is because codebases are split in multiple mismatched paradigms, langs and stacks.
Being able to write ordinary mathematical functions (UDF's, user defined functions) can also be a big help. I remember doing some completely crazy stuff to do those entirely with MySQL builtins, because again, loading C functions wasn't permitted in RDS.
I think this really depends on the developers and their tools. I've seen at least one system that is built on the database using hundreds of stored procedures and triggers, and that was still maintainable. The reason that it was maintainable was that there was a code and naming style that was used consistently throughout the project, that each trigger and stored procedure was documented, and that the developers had a good tool to maintain everything.
Maybe in practice it's the exception, but good coding style can help just as it would help with code in a service.
> And for most cases, all this downside with little/no upside.
Well... obviously there is a very important upside and that is performance: you are executing code very close to the data and this can give you a big performance gain. If you execute business code on an application server, you have to first fetch the data from the database, then manipulate the data and then send the data back so that the database can store it. If you execute business code inside the database server, you don't need the round-trip (which goes typically over a network connection) and this can result in much better performance.
Personally I never built a system that relies on triggers and stored procedures. I'm used to working with an ORM and implementing business logic in the service. I do rely on database features (different types of constraints) to protect data consistency as much as possible. Still, at some point I would love to build a system that has its business logic in the database just to get the experience and to really know how that works. I also wonder how performant the language integrations (which might be more expressive than the default) are that database systems sometimes offer.
tools like spanner and cockroach aren't meant to be general-purpose - these are purpose built for certain types of scenarios where you're probably already using a bunch of code to implement the physical layer (and where there's often no separation at all)
But still the worse part of user defined function is if they're being used on select statement. They can be a performance bottleneck while not being clearly visible.
For systems most likely to run in public cloud: High availability and scalability is valued much more.