6 ms·

This makes no sense and is typically the opposite of what I'd build. The more business logic (various validation rules and processing) you have in the database

This makes no sense and is typically the opposite of what I'd build. The more business logic (various validation rules and processing) you have in the database (via stored procedures) the less problems you have, not least because all that processing becomes ACID. The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. If you implement business logic in front end applications, you're asking for trouble. Many places I've worked didn't allow software outside the database to do anything more than call a stored procedure. Application servers did come into fashion at one point, with predictable results: subtle timing bugs, inconsistent processing with different results depending on which application server processed the data, a general difficulty in finding the source of bugs, security problems. Everyone would be a lot less stressed if they did everything in the database.

count 3y ago
Oracle, DB2 or SQLServer? >The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. Ah, but by shifting the logic to the app, I can now query multiple databases (of disparate types even! vector, document, object, relational, etc.), each optimized for its particular data and query types and combine the result. That's very....not easy in stored procedures. I can also scale out different tiers of the application based on its needs (heavy compute, gpu-based, etc.). How do you do that when everything is a stored proc on a database?
- dkjaudyeqooe 3y ago
  Well depends on the requirements, does it? I'm not suggesting you try to stick all your processing of all things inside a database, I'm saying put all critical processing of data stored in said database within it. Put all related data in the same database where is makes sense. Whatever data doesn't belong in that database can be merely referenced from it. > I can now query multiple databases This is another thing I disagree with. Wherever possible related data should be centralised to improve ACID properties and to create a reliable single "source of truth". I know this is unfashionable. with everyone claiming they need massive scale (which does cause some issues with this design), but much like microservices, it ends up being mostly busy work for programmers and related professions. Having said that, multiple databases, if largely orthogonal to each other present no real problems, and the same design concept is applied to each.
  - bvirb 3y ago
    FWIW I think I agree with you if you consider "critical processing of data" data validations (IME via constraints, checks, triggers, etc...). I think where things can really go wrong is when the DB accepts anything the application layer thinks is valid. My favorite ORM says the same thing: https://sequel.jeremyevans.net/rdoc/files/doc/validations_rdoc.html#label-Data+Integrity https://sequel.jeremyevans.net/rdoc/files/doc/validations_rd... I wouldn't be surprised if more people agree with that as well, maybe just using different terms.
    - dkjaudyeqooe 3y ago
      > I think where things can really go wrong is when the DB accepts anything the application layer thinks is valid. No, my view is the opposite of that: the database doesn't allow anything invalid to enter the database.
      - bvirb 3y ago
        Hmm well if I understand you correctly that's what I was saying as well: Things go wrong when the DB trusts the application layer (rather than doing its own validation) -- e.g. the DB should control what is valid data, not the application layer.
Galanwe 3y ago
That is the exact opposite of my experience. I try very hard to have the database as dumb as possible. - Having mixed workloads on the database servers make it almost impossible to profile and tune. By definition, a database answers a lot of queries from a lot of clients. If these queries are hybrid data fetching / compute, then it's pretty much impossible to make sense of any system metrics. - Having hybrid workloads usually lead to considering DB servers as black boxes (because you cannot make sense of their resource usage easily). This in turn leads to having hyper pumped up DB servers with both crazy RAM, crazy CPU, crazy caches, crazy SSDs and crazy HDDs. - DB vendors of course understand the previous point, thus why Oracle switched to a "pay per core on the machine" model... - The DB logic layer ecosystem is most of the time subpar compared to a more traditional programming language. If you depend on your DB server performing a lot of work, you will want to make these awesome queries accessible. That is, you will want stored procs, extensions, etc. Dealing with those is a huge pain to test, version, update... - Overall all RDBMS have special SQL extensions all over the place. You're probably using tons of those without even knowing it. With time, vendor locking will become an immense burden and you'll be forced to pay millions to Oracle to keep your business running.
el_nahual 3y ago
The problem with having business logic in the DB is not a performance problem, it's a maintainability, testing, and documentation problem.
- dkjaudyeqooe 3y ago
  You've demonstrated the real issue here, but not the one you claim, it's that programmers have no idea how to use (SQL) databases or how they work. The idea that software has those negative characteristics merely because it resides within a database, is laughable.
  - setr 3y ago
    All of the PLSQL dialects are pretty much universally mediocre at best. They don’t (de-)compose well, they don’t version-control well, they don’t document well, they don’t debug well — they pretty much lack any of the learnings and tooling that occurred in application languages over the last 20 years. You’re basically dealing with all of the problems of a poorly designed DSL made by your favorite coworker. You could do something more sane like running lang-extensions on the db so you have access to a “real” language to specify your business logic in, but then you haven’t really changed anything except to run your app logic on the DB server and doesn’t really have anything to do with the RDBMS at that point. Moving business logic into the schema/queries can be fine, but you still have the issues that you can’t document it particularly well, can’t version control it well, can’t test it well, can’t trust your performance tuning well, etc. Which all derives from DBMS expecting to be treated as “living systems”, and has nothing to do with knowing/not knowing SQL. And the problem with modifying living systems is that you really don’t want to be fucking around with it because anything could potentially send it reeling (see: surgeons). You really want it to be stable… so moving your unstable business logic out is almost a necessity
    - nicoburns 3y ago
      A lot of the stuff I see people writing application code for doesn't require any more complex SQL features than joins, aggregations and filters. i.e the stuff plain old SQL is really good at.

count 3y ago

Oracle, DB2 or SQLServer? >The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. Ah, but by shifting the logic to the app, I can now query multiple databases (of disparate types even! vector, document, object, relational, etc.), each optimized for its particular data and query types and combine the result. That's very....not easy in stored procedures. I can also scale out different tiers of the application based on its needs (heavy compute, gpu-based, etc.). How do you do that when everything is a stored proc on a database?

dkjaudyeqooe 3y ago
Well depends on the requirements, does it? I'm not suggesting you try to stick all your processing of all things inside a database, I'm saying put all critical processing of data stored in said database within it. Put all related data in the same database where is makes sense. Whatever data doesn't belong in that database can be merely referenced from it. > I can now query multiple databases This is another thing I disagree with. Wherever possible related data should be centralised to improve ACID properties and to create a reliable single "source of truth". I know this is unfashionable. with everyone claiming they need massive scale (which does cause some issues with this design), but much like microservices, it ends up being mostly busy work for programmers and related professions. Having said that, multiple databases, if largely orthogonal to each other present no real problems, and the same design concept is applied to each.
- bvirb 3y ago
  FWIW I think I agree with you if you consider "critical processing of data" data validations (IME via constraints, checks, triggers, etc...). I think where things can really go wrong is when the DB accepts anything the application layer thinks is valid. My favorite ORM says the same thing: https://sequel.jeremyevans.net/rdoc/files/doc/validations_rdoc.html#label-Data+Integrity https://sequel.jeremyevans.net/rdoc/files/doc/validations_rd... I wouldn't be surprised if more people agree with that as well, maybe just using different terms.
  - dkjaudyeqooe 3y ago
    > I think where things can really go wrong is when the DB accepts anything the application layer thinks is valid. No, my view is the opposite of that: the database doesn't allow anything invalid to enter the database.
    - bvirb 3y ago
      Hmm well if I understand you correctly that's what I was saying as well: Things go wrong when the DB trusts the application layer (rather than doing its own validation) -- e.g. the DB should control what is valid data, not the application layer.

Galanwe 3y ago

That is the exact opposite of my experience. I try very hard to have the database as dumb as possible. - Having mixed workloads on the database servers make it almost impossible to profile and tune. By definition, a database answers a lot of queries from a lot of clients. If these queries are hybrid data fetching / compute, then it's pretty much impossible to make sense of any system metrics. - Having hybrid workloads usually lead to considering DB servers as black boxes (because you cannot make sense of their resource usage easily). This in turn leads to having hyper pumped up DB servers with both crazy RAM, crazy CPU, crazy caches, crazy SSDs and crazy HDDs. - DB vendors of course understand the previous point, thus why Oracle switched to a "pay per core on the machine" model... - The DB logic layer ecosystem is most of the time subpar compared to a more traditional programming language. If you depend on your DB server performing a lot of work, you will want to make these awesome queries accessible. That is, you will want stored procs, extensions, etc. Dealing with those is a huge pain to test, version, update... - Overall all RDBMS have special SQL extensions all over the place. You're probably using tons of those without even knowing it. With time, vendor locking will become an immense burden and you'll be forced to pay millions to Oracle to keep your business running.

el_nahual 3y ago

The problem with having business logic in the DB is not a performance problem, it's a maintainability, testing, and documentation problem.

dkjaudyeqooe 3y ago
You've demonstrated the real issue here, but not the one you claim, it's that programmers have no idea how to use (SQL) databases or how they work. The idea that software has those negative characteristics merely because it resides within a database, is laughable.
- setr 3y ago
  All of the PLSQL dialects are pretty much universally mediocre at best. They don’t (de-)compose well, they don’t version-control well, they don’t document well, they don’t debug well — they pretty much lack any of the learnings and tooling that occurred in application languages over the last 20 years. You’re basically dealing with all of the problems of a poorly designed DSL made by your favorite coworker. You could do something more sane like running lang-extensions on the db so you have access to a “real” language to specify your business logic in, but then you haven’t really changed anything except to run your app logic on the DB server and doesn’t really have anything to do with the RDBMS at that point. Moving business logic into the schema/queries can be fine, but you still have the issues that you can’t document it particularly well, can’t version control it well, can’t test it well, can’t trust your performance tuning well, etc. Which all derives from DBMS expecting to be treated as “living systems”, and has nothing to do with knowing/not knowing SQL. And the problem with modifying living systems is that you really don’t want to be fucking around with it because anything could potentially send it reeling (see: surgeons). You really want it to be stable… so moving your unstable business logic out is almost a necessity
  - nicoburns 3y ago
    A lot of the stuff I see people writing application code for doesn't require any more complex SQL features than joins, aggregations and filters. i.e the stuff plain old SQL is really good at.