4 ms·

Note the following passage: "It's clear to me that a certain proportion of application developers are allergic to putting business logic in the database system

by Dowwie 8y ago

Note the following passage: "It's clear to me that a certain proportion of application developers are allergic to putting business logic in the database system, despite the fact that batch processing logic is often a great deal faster when implemented that way. Perhaps the fact that clients are now able to cede full control of transaction scope to procedural code running on the database server (code that can be written in a familiar scripting language) will help application developers to get over the understandable aversion. Hope springs eternal."

danpalmer 8y ago
I'm one of these application developers, and I'm still seeing a lot in my way to moving logic to the database. From coding in Python with Django, I'd need to give up version control as I know it, use a different Python environment, give up unit testing as I know it (and as it integrates with the rest of my application), authentication (because that's owned by Django), distributed tasks and queueing, performance monitoring (as I know it), etc. I would really like to see a Proof of Concept of a modern web framework like Django/Rails, but with a strong focus on moving as much to the database as possible - user management, queues, etc, as I think there are many benefits to be had from pushing more into the database. Until something like that exists though, I think moving logic to the database will lose development tooling and process that we are used to, and end up re-implementing logic in multiple places which isn't great.
- manigandham 8y ago
  That statement is about batch processing, like ETL work, that can and should be close to the data. SQL is still just code and can be saved in git along with a CI/CD deployment process as well. OLTP transactions for running a basic webapp don't need to move there, and the data still needs to be retrieve anyway, but things like a queue, the actual push and pop functions can easily be wrapped in SQL functions that make it easy and safe to call from multiple apps.
- zie 8y ago
  We don't use django, but we basically do this, we put python in the DB (PL/pythonu) and it's hosted in a VCS repo and use liquibase[0] to handle getting it into the DB, along with our schema's, etc. Debugging can be a little harder, but testing generally isn't you know the calling signature(s) PG uses so writing a class for UnitTest/pytest is straightforward. Our APP logins ARE DB logins for our desktop product(s), but for web we have a single DB user that web apps log into the DB as. We'd love for web logins to become PG logins as well, but we haven't found a very performant way of doing that yet. PG sessions are a heavy startup cost. The functionality for us, isn't the same between the web and desktop product(s) which helps a lot. We push access control into the DB for the most part, using row, column and table level access controls PG provides us. So our desktop product has basically no access controls in it, since the DB provides all of that at login time. We have a few power users that connect directly with SQL, and we have zero issues just handing them access, since the DB controls what they can/can not see just fine. Sadly so many tools just assume their DB connection is the equivalent to root, and can just go do whatever it wants, which makes lots of tools, especially reporting tools, hard to just hand directly to end users. [0] http://www.liquibase.org/ http://www.liquibase.org/
  - camel_Snake 8y ago
    I think Postgrest[0] takes an approach to authorization you may want to look into. My understanding is they use one base user that is granted the ability to switch roles to reflect the current user once they're authed by your app server. Have you transitioned your business logic into your DB from your app or is that approach something you started with? [0] http://postgrest.org/en/stable/auth.html http://postgrest.org/en/stable/auth.html
    - zie 8y ago
      We started with it. It's always best to offload your security/privacy posture onto something well known and supported rather than rolling your own, if possible. It's hard stuff to get right, and PG does it really well, especially in more recent versions. Row/Column security in the 8.X series was a bit painful :) We looked into doing something similar, but it didn't scale to our workload and had a ton of edge cases we kept getting caught up in. If a breach on the PG connection happens with that approach, the world gets super bad, super fast, as if I remember right, you need to be a super user to switch roles like that. That's very, very worrisome exposing super-user credentials like that to a website. If I remember right, we had to throw the connection away after using it for some reason, so the startup-cost gets cut some, but if you have a mass influx of users and your pool size is not gigantic, it gets painful waiting for new PG connections to come up. Plus the cost of holding open so many PG connections if your pool size is too large.... And our product has a module where all users attack it pretty much all at once, twice a day... this was back in the 8.X series of PG, so maybe it's a lot better now... If postgrest manages to make it scale to our workload and took care of all the edge cases well, it would be super awesome. I'll have to check into how they handle that.. maybe it's something we can look into adding in the future, thanks!
nijave 8y ago
That bothered me. I've worked on applications that store business logic in the database and they are a lot harder to maintain and verify. You could also say the same thing about assembly but there's a reason nobody's jumping on that
paulddraper 8y ago
I've noticed the same and will compare it graphics programming. Loads of GUI applications write their UIs in application code. Eventually they have to reach to an actual graphics buffer, or OpenGL or similar lib, but only in the most simplistic, elementary way possible. (Sort of like `SELECT * FROM user WHERE id = 4`.) That keeps 98% your logic in one place, with one language, one composition paradigm, and one set of tools. For programs that have demanding graphics needs, however, they will start writing greater and greater amounts of code into shaders, to be run on the GPU. It's more complex to split logic like this, and the capabilities of shader languages aware far beneath those of general purpose programming languages. But when it matters, you'll happily take the improved performance. It's great to see PostgreSQL doing what it can to close the gap and make this easier. tl;dr SQL queries are the graphics shaders of server programs.

by Dowwie 8y ago

danpalmer 8y ago

I'm one of these application developers, and I'm still seeing a lot in my way to moving logic to the database. From coding in Python with Django, I'd need to give up version control as I know it, use a different Python environment, give up unit testing as I know it (and as it integrates with the rest of my application), authentication (because that's owned by Django), distributed tasks and queueing, performance monitoring (as I know it), etc. I would really like to see a Proof of Concept of a modern web framework like Django/Rails, but with a strong focus on moving as much to the database as possible - user management, queues, etc, as I think there are many benefits to be had from pushing more into the database. Until something like that exists though, I think moving logic to the database will lose development tooling and process that we are used to, and end up re-implementing logic in multiple places which isn't great.

manigandham 8y ago
That statement is about batch processing, like ETL work, that can and should be close to the data. SQL is still just code and can be saved in git along with a CI/CD deployment process as well. OLTP transactions for running a basic webapp don't need to move there, and the data still needs to be retrieve anyway, but things like a queue, the actual push and pop functions can easily be wrapped in SQL functions that make it easy and safe to call from multiple apps.
zie 8y ago
We don't use django, but we basically do this, we put python in the DB (PL/pythonu) and it's hosted in a VCS repo and use liquibase[0] to handle getting it into the DB, along with our schema's, etc. Debugging can be a little harder, but testing generally isn't you know the calling signature(s) PG uses so writing a class for UnitTest/pytest is straightforward. Our APP logins ARE DB logins for our desktop product(s), but for web we have a single DB user that web apps log into the DB as. We'd love for web logins to become PG logins as well, but we haven't found a very performant way of doing that yet. PG sessions are a heavy startup cost. The functionality for us, isn't the same between the web and desktop product(s) which helps a lot. We push access control into the DB for the most part, using row, column and table level access controls PG provides us. So our desktop product has basically no access controls in it, since the DB provides all of that at login time. We have a few power users that connect directly with SQL, and we have zero issues just handing them access, since the DB controls what they can/can not see just fine. Sadly so many tools just assume their DB connection is the equivalent to root, and can just go do whatever it wants, which makes lots of tools, especially reporting tools, hard to just hand directly to end users. [0] http://www.liquibase.org/ http://www.liquibase.org/
- camel_Snake 8y ago
  I think Postgrest[0] takes an approach to authorization you may want to look into. My understanding is they use one base user that is granted the ability to switch roles to reflect the current user once they're authed by your app server. Have you transitioned your business logic into your DB from your app or is that approach something you started with? [0] http://postgrest.org/en/stable/auth.html http://postgrest.org/en/stable/auth.html
  - zie 8y ago
    We started with it. It's always best to offload your security/privacy posture onto something well known and supported rather than rolling your own, if possible. It's hard stuff to get right, and PG does it really well, especially in more recent versions. Row/Column security in the 8.X series was a bit painful :) We looked into doing something similar, but it didn't scale to our workload and had a ton of edge cases we kept getting caught up in. If a breach on the PG connection happens with that approach, the world gets super bad, super fast, as if I remember right, you need to be a super user to switch roles like that. That's very, very worrisome exposing super-user credentials like that to a website. If I remember right, we had to throw the connection away after using it for some reason, so the startup-cost gets cut some, but if you have a mass influx of users and your pool size is not gigantic, it gets painful waiting for new PG connections to come up. Plus the cost of holding open so many PG connections if your pool size is too large.... And our product has a module where all users attack it pretty much all at once, twice a day... this was back in the 8.X series of PG, so maybe it's a lot better now... If postgrest manages to make it scale to our workload and took care of all the edge cases well, it would be super awesome. I'll have to check into how they handle that.. maybe it's something we can look into adding in the future, thanks!

nijave 8y ago

That bothered me. I've worked on applications that store business logic in the database and they are a lot harder to maintain and verify. You could also say the same thing about assembly but there's a reason nobody's jumping on that

paulddraper 8y ago

I've noticed the same and will compare it graphics programming. Loads of GUI applications write their UIs in application code. Eventually they have to reach to an actual graphics buffer, or OpenGL or similar lib, but only in the most simplistic, elementary way possible. (Sort of like `SELECT * FROM user WHERE id = 4`.) That keeps 98% your logic in one place, with one language, one composition paradigm, and one set of tools. For programs that have demanding graphics needs, however, they will start writing greater and greater amounts of code into shaders, to be run on the GPU. It's more complex to split logic like this, and the capabilities of shader languages aware far beneath those of general purpose programming languages. But when it matters, you'll happily take the improved performance. It's great to see PostgreSQL doing what it can to close the gap and make this easier. tl;dr SQL queries are the graphics shaders of server programs.