5 ms·

Stored procs, triggers and suchlike actually are outdated and mostly wrong. Have you considered that people moved away from them for good reasons, not just igno

Stored procs, triggers and suchlike actually are outdated and mostly wrong. Have you considered that people moved away from them for good reasons, not just ignorance? Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! I have written more TSQL than I would care to admit, and it’s a hell you couldn’t pay me enough to go back to. The “consistency must be enforced at all costs” just turned out not to be true. Worked at many places at moderate scale since my old dba days that don’t use fks. It just doesn’t actually matter, I can’t recall any serious bugs or outages related to dangling rows. Plus, you end up having multiple databases anyway for scale and organizational reasons so fks are useless in that environment anyway. On the other hand, I’m all for indexes and complex querying. not just k-v.

leoqa 2y ago
I think you touched on the real reason: we separate services now and they all have different databases. We shifted up to the application layer not because of technical reasons but social reasons.
- dalyons 2y ago
  Your timing is off, we as an industry ditched stored procs and friends before (micro)services became a trend. For the reasons I said. I’m not sure why it’s so hard to accept that we stopped using them because they were bad, not for some other gotcha reason (social, ignorance, fads, etc)
  - btreecat 2y ago
    Maybe because claiming they are bad with out explaining why, giving explicit examples, or really digging into the nuance of the more than one feature mentioned makes the claim feel unsubstantiated.
    - dalyons 2y ago
      Pretty sure I did say why. “ Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! ” and I forgot “generates non obvious non local changes” for triggers.
      - ako 2y ago
        What is so hard to source control with stored procedures, I’ve writing them in text files for over 30years, version controlled in rcs, cvs, svn and git. No problem, it’s just the deployment automation that works a bit different. Also, oracle plsql is way better than ms t-sql, it mostly feels like pascal/modula2 with embedded sql statements.
        dalyons 2y ago
        yes, you can hack something together that keeps them in source control, and then some automations to run them in a CD way. But now your business logic change is half in application code, and half in sprocs, and you have a versioning and deployment coordination problem. Which you can work around too. But why? making deployments and debugging of buis logic hard for what end? Just keep all the business logic in your app code, then its deployed and tested as one artifact, and everyones happier. Also i hope you're not suggesting anyone pick up oracle for a new project these days :)
        ako 2y ago
        What do you mean hack to keep them in source control? They're just sql source code files, like any other source code you can simply commit to version control? Wouldn't advise anyone to use Oracle, but neither would i advise them to use sql-server. Usually Postgres is good enough, although oracle plsql is still nicer than postgres plsql (e.g, packages).
        dalyons 2y ago
        i feel like i addressed this in a different subthread https://news.ycombinator.com/reply?id=40526231&goto=threads%3Fid%3Ddalyons%2340526231 https://news.ycombinator.com/reply?id=40526231&goto=threads%... > You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code.
myaccountonhn 2y ago
It makes me think that for many projects, recutils would be a better fit if you’re not gonna use the advanced functionality anyway. It should do just fine on medium sized DBs and is so much simpler.
The_Colonel 2y ago
Hard agree. But I like foreign keys (ON DELETE RESTRICT), they are the last layer of defense against application bugs messing up the database.
- dalyons 2y ago
  i totally get that position. I used to be in that camp, but in my last 3 jobs at companies of >10mil users, we had to get rid of FKs for other reasons (perf, dist system stuff). I kind of expected bad data corruption issues.... but they just never happened ¯\_(ツ)_/¯. Not sure if that was strong cultures of automated testing, or what. So now i no longer think they're worth the tradeoffs. YMMV with this one, i could see how it might pan out differently in other environments
  - pbowyer 2y ago
    Did these involve financial transactions? Other situations I will have dangling rows without issues, but whenever money comes into the picture I like to have as much consistency enforced as I can. But I'm aware that doesn't play nicely with distributed systems.
    - dalyons 2y ago
      Yes actually. financial transactions are often used as an example that demands strong consistency. But it’s not true in the real world for the most part. for any given money movement (a debit and a credit) there’s a good chance one of those legs is over the network - you’re either interacting with a third party api, or a proprietary internal product, or similar. So you can’t have transactional consistency, or fks. Further, for every movement there is layers of reconciliation that happens hours or days later, that fixes any inconsistency. Finance / fintech is actually among the most eventually consistent systems out there, so it’s sort of ironic its use in a lot of transactional examples. Exception is true banking ledgers maintained at banks, but even then you’d be surprised. Entirely different systems handle loans and checking accounts
      - 2y ago
psadri 2y ago
I think the implementations had a bad DX - but the concepts were not incorrect. Not enforcing referential integrity, or foregoing schemas all together (hello document stores and then doing the equivalent of SQL joins and aggregations in nested loops within code) is just plain lazy / ignorant that comes to bite you in the ass later.
deleted 2y ago
[deleted]
Arch-TK 2y ago
Most of the actual drawbacks you describe (Lack of source control and "weird/bad language" are just wrong. You can source control this stuff just fine and if you don't like the language nothing stops you from transpiling.) are caused by how databases are being used not by their inherent design. That's what I meant when I said it's weird people keep picking these particular databases for projects which don't end up using any of the features. Stored procedures, triggers and all the other features people seem to refuse to use work mostly fine if you actually design the rest of the product around them and put a modicum of effort into them. "It doesn't scale" - that can be said of the fundamental database design itself. If you need to scale hard then you need to pick a distributed database.
- dalyons 2y ago
  I dont really know why im arguing this, there are 100s of blog posts out there for decades that explain why sprocs are considered bad. The marketplace of ideas has spoken, and we've moved on. I'll never likely never convince you, but here goes my take expanded some more. You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code. Transpile? why on earth would anyone bother with that, it just makes it even more complicated and impossible to debug. without that, TSQL/PSQL/PGSQL are objectively very awkward languages for imperative business logic. People are not familiar with them, it makes it hard to jump from regular code to *SQL code to read the logic flow, nevermind debugging them with breakpoints and suchlike. Splitting your buis logic between database and app makes it much more awkward to test too. Just write your business logic in application code. Scale - making the hardest to scale piece of your architecture , the traditional rdmbs, an execution environment for arbitrarily complex business logic is going to add a lot of extra load. Meaning you're going to have scaling problems sooner than you otherwise would. Just write your business logic in application code, you can scale those pods/servers horizontally easy peasy, they're cheap. Look you can make any technology work if you try hard enough, but sprocs are almost all downsides these days. The one minor upside is some atomicness, but you can get that with a client side transaction and a few more round trips. There's just IMO no reason to pick them anymore and pay these costs, given what we've learned. BTW I could have made this clearer in the original comment but i love RDBMSs - i love schemas, indexes, complex querying, and transactions. It is not weird to keep picking these databases to get those things. I just will never again use them as a programming environment.

leoqa 2y ago

I think you touched on the real reason: we separate services now and they all have different databases. We shifted up to the application layer not because of technical reasons but social reasons.

dalyons 2y ago
Your timing is off, we as an industry ditched stored procs and friends before (micro)services became a trend. For the reasons I said. I’m not sure why it’s so hard to accept that we stopped using them because they were bad, not for some other gotcha reason (social, ignorance, fads, etc)
- btreecat 2y ago
  Maybe because claiming they are bad with out explaining why, giving explicit examples, or really digging into the nuance of the more than one feature mentioned makes the claim feel unsubstantiated.
  - dalyons 2y ago
    Pretty sure I did say why. “ Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! ” and I forgot “generates non obvious non local changes” for triggers.
    - ako 2y ago
      What is so hard to source control with stored procedures, I’ve writing them in text files for over 30years, version controlled in rcs, cvs, svn and git. No problem, it’s just the deployment automation that works a bit different. Also, oracle plsql is way better than ms t-sql, it mostly feels like pascal/modula2 with embedded sql statements.
      - dalyons 2y ago
        yes, you can hack something together that keeps them in source control, and then some automations to run them in a CD way. But now your business logic change is half in application code, and half in sprocs, and you have a versioning and deployment coordination problem. Which you can work around too. But why? making deployments and debugging of buis logic hard for what end? Just keep all the business logic in your app code, then its deployed and tested as one artifact, and everyones happier. Also i hope you're not suggesting anyone pick up oracle for a new project these days :)
        ako 2y ago
        What do you mean hack to keep them in source control? They're just sql source code files, like any other source code you can simply commit to version control? Wouldn't advise anyone to use Oracle, but neither would i advise them to use sql-server. Usually Postgres is good enough, although oracle plsql is still nicer than postgres plsql (e.g, packages).
        dalyons 2y ago
        i feel like i addressed this in a different subthread https://news.ycombinator.com/reply?id=40526231&goto=threads%3Fid%3Ddalyons%2340526231 https://news.ycombinator.com/reply?id=40526231&goto=threads%... > You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code.

myaccountonhn 2y ago

It makes me think that for many projects, recutils would be a better fit if you’re not gonna use the advanced functionality anyway. It should do just fine on medium sized DBs and is so much simpler.

The_Colonel 2y ago

Hard agree. But I like foreign keys (ON DELETE RESTRICT), they are the last layer of defense against application bugs messing up the database.

dalyons 2y ago
i totally get that position. I used to be in that camp, but in my last 3 jobs at companies of >10mil users, we had to get rid of FKs for other reasons (perf, dist system stuff). I kind of expected bad data corruption issues.... but they just never happened ¯\_(ツ)_/¯. Not sure if that was strong cultures of automated testing, or what. So now i no longer think they're worth the tradeoffs. YMMV with this one, i could see how it might pan out differently in other environments
- pbowyer 2y ago
  Did these involve financial transactions? Other situations I will have dangling rows without issues, but whenever money comes into the picture I like to have as much consistency enforced as I can. But I'm aware that doesn't play nicely with distributed systems.
  - dalyons 2y ago
    Yes actually. financial transactions are often used as an example that demands strong consistency. But it’s not true in the real world for the most part. for any given money movement (a debit and a credit) there’s a good chance one of those legs is over the network - you’re either interacting with a third party api, or a proprietary internal product, or similar. So you can’t have transactional consistency, or fks. Further, for every movement there is layers of reconciliation that happens hours or days later, that fixes any inconsistency. Finance / fintech is actually among the most eventually consistent systems out there, so it’s sort of ironic its use in a lot of transactional examples. Exception is true banking ledgers maintained at banks, but even then you’d be surprised. Entirely different systems handle loans and checking accounts
    - 2y ago

psadri 2y ago

I think the implementations had a bad DX - but the concepts were not incorrect. Not enforcing referential integrity, or foregoing schemas all together (hello document stores and then doing the equivalent of SQL joins and aggregations in nested loops within code) is just plain lazy / ignorant that comes to bite you in the ass later.

deleted 2y ago

[deleted]

Arch-TK 2y ago

Most of the actual drawbacks you describe (Lack of source control and "weird/bad language" are just wrong. You can source control this stuff just fine and if you don't like the language nothing stops you from transpiling.) are caused by how databases are being used not by their inherent design. That's what I meant when I said it's weird people keep picking these particular databases for projects which don't end up using any of the features. Stored procedures, triggers and all the other features people seem to refuse to use work mostly fine if you actually design the rest of the product around them and put a modicum of effort into them. "It doesn't scale" - that can be said of the fundamental database design itself. If you need to scale hard then you need to pick a distributed database.

dalyons 2y ago
I dont really know why im arguing this, there are 100s of blog posts out there for decades that explain why sprocs are considered bad. The marketplace of ideas has spoken, and we've moved on. I'll never likely never convince you, but here goes my take expanded some more. You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code. Transpile? why on earth would anyone bother with that, it just makes it even more complicated and impossible to debug. without that, TSQL/PSQL/PGSQL are objectively very awkward languages for imperative business logic. People are not familiar with them, it makes it hard to jump from regular code to *SQL code to read the logic flow, nevermind debugging them with breakpoints and suchlike. Splitting your buis logic between database and app makes it much more awkward to test too. Just write your business logic in application code. Scale - making the hardest to scale piece of your architecture , the traditional rdmbs, an execution environment for arbitrarily complex business logic is going to add a lot of extra load. Meaning you're going to have scaling problems sooner than you otherwise would. Just write your business logic in application code, you can scale those pods/servers horizontally easy peasy, they're cheap. Look you can make any technology work if you try hard enough, but sprocs are almost all downsides these days. The one minor upside is some atomicness, but you can get that with a client side transaction and a few more round trips. There's just IMO no reason to pick them anymore and pay these costs, given what we've learned. BTW I could have made this clearer in the original comment but i love RDBMSs - i love schemas, indexes, complex querying, and transactions. It is not weird to keep picking these databases to get those things. I just will never again use them as a programming environment.