4 ms·

Are Stored Procedures and Triggers Anti-Patterns in the Cloud Native World?

34 points by kiyanwang 5y ago

libpcap 5y ago
No.
sparker72678 5y ago
In many contexts the trade offs make using these features painful/dangerous/not worth it. 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).
- convolvatron 5y ago
  I certainly agree that its generally an awful idea in practice...would you agree that its potentially a really good idea in theory?
  - sparker72678 5y ago
    If there’s anything I can agree on it’s that most things in software development are potentially really good ideas in theory and awful in practice.
- KronisLV 5y ago
  And yet, i once talked with a developer (who primarily worked just in procedural SQL), who claimed that having a separate back end oftentimes makes no sense whatsoever: the endless shuffling around of data, transforming it in the back end, especially for something like reports. 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 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/#Databases_do-you-have-tests-in-your-database https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da...
- pjmlp 5y ago
  There are source control tools for Oracle and SQL Server, debugging tools, code profilers, and no need to waste bandwidth with data. I would rather reach for them than the currently trendy ORM framework.
- mamcx 5y ago
  "Many" are not that much many. Refuse to use the full capabilities of a RDBMs is like refuse to use the full capabilities of a NoSQL. 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.
- throwaway81523 5y ago
  I had an application where being able to write a customized aggregation function (like SUM, MAX, etc. but your own function) would have been a huge help, but unfortunately the database was an RDS MySQL instance where that is impossible. MySQL requires those functions to be written in C, which RDS doesn't allow (you can do it with self-hosted MySQL of course). There was once a version of MariaDB that allowed writing those functions in Javascript, which would have been fine, but I don't think it was ever released. 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.
- deleted 5y ago
  [deleted]
- rvdginste 5y ago
  > 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. 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.
ddek 5y ago
Do we need the in the Cloud Native World bit of the question? I'd argue the answer is *yes* wherever.
- gfody 5y ago
  I don't think we should deem whole sets of features anti-patterns just because a lot of people got themselves into messes. many very nice database solutions I've seen would be non-starters w/o triggers, the alternative would've been something heinous outside the db. wondering why the new "cloud native" products lack these features is fair but I don't think it's appropriate to call spanner and cockroach an rdbms as I think "rdbms" implies some degree of general purposeness
fendy3002 5y ago
At least I don't agree with user-defined function part in the article. It's far easier to check for 100 words limit in app layers, though the deployment may pose a challenge there. 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.
gfody 5y ago
if you're trying to have a general purpose relational database (eg well-modeled/normalized/hygienic/integrous/etc.) things like triggers, indexed/materialized views, and udfs become essential tools for maintaining a logical layer (the tables/views you present to users and applications) separate from the physical layer (the tables holding your raw data) 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)
bni 5y ago
Triggers, SPs, and referential integrity are of value if you need and care about data integrity and correctness. For systems most likely to run in public cloud: High availability and scalability is valued much more.
entropicgravity 5y ago
Unless you have an important performance issue that only they can solve it's better to have that code where debugging is easier.

34 points by kiyanwang 5y ago

No.

In many contexts the trade offs make using these features painful/dangerous/not worth it. 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).

convolvatron 5y ago
I certainly agree that its generally an awful idea in practice...would you agree that its potentially a really good idea in theory?
- sparker72678 5y ago
  If there’s anything I can agree on it’s that most things in software development are potentially really good ideas in theory and awful in practice.
KronisLV 5y ago
And yet, i once talked with a developer (who primarily worked just in procedural SQL), who claimed that having a separate back end oftentimes makes no sense whatsoever: the endless shuffling around of data, transforming it in the back end, especially for something like reports. 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 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/#Databases_do-you-have-tests-in-your-database https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da...
pjmlp 5y ago
There are source control tools for Oracle and SQL Server, debugging tools, code profilers, and no need to waste bandwidth with data. I would rather reach for them than the currently trendy ORM framework.
mamcx 5y ago
"Many" are not that much many. Refuse to use the full capabilities of a RDBMs is like refuse to use the full capabilities of a NoSQL. 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.
throwaway81523 5y ago
I had an application where being able to write a customized aggregation function (like SUM, MAX, etc. but your own function) would have been a huge help, but unfortunately the database was an RDS MySQL instance where that is impossible. MySQL requires those functions to be written in C, which RDS doesn't allow (you can do it with self-hosted MySQL of course). There was once a version of MariaDB that allowed writing those functions in Javascript, which would have been fine, but I don't think it was ever released. 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.
deleted 5y ago
[deleted]
rvdginste 5y ago
> 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. 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.

ddek 5y ago

Do we need the in the Cloud Native World bit of the question? I'd argue the answer is *yes* wherever.

gfody 5y ago
I don't think we should deem whole sets of features anti-patterns just because a lot of people got themselves into messes. many very nice database solutions I've seen would be non-starters w/o triggers, the alternative would've been something heinous outside the db. wondering why the new "cloud native" products lack these features is fair but I don't think it's appropriate to call spanner and cockroach an rdbms as I think "rdbms" implies some degree of general purposeness

fendy3002 5y ago

At least I don't agree with user-defined function part in the article. It's far easier to check for 100 words limit in app layers, though the deployment may pose a challenge there. 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.

gfody 5y ago

if you're trying to have a general purpose relational database (eg well-modeled/normalized/hygienic/integrous/etc.) things like triggers, indexed/materialized views, and udfs become essential tools for maintaining a logical layer (the tables/views you present to users and applications) separate from the physical layer (the tables holding your raw data) 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)

bni 5y ago

Triggers, SPs, and referential integrity are of value if you need and care about data integrity and correctness. For systems most likely to run in public cloud: High availability and scalability is valued much more.

entropicgravity 5y ago

Unless you have an important performance issue that only they can solve it's better to have that code where debugging is easier.