4 ms·

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of glosses over it as a possibility. Almost nobody is even considering it as an option in 2023.

Micoloth 3y ago
Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?
- candiddevmike 3y ago
  Few reasons: - Enforce application logic with constraints so you don't have to duplicate it - Use triggers and get access to things like old and new without having to create a bunch of transactions - If you have multiple apps sharing a DB, you either keep your business logic consistent across them vs just doing it in the database - You can version your business logic with your schema
- __jem 3y ago
  Friction with modern devops practices is a big one.
- xboxnolifes 3y ago
  The tooling around maintaining logic-in-db is worse than the tooling for logic-out-of-db.
- simonw 3y ago
  I think the main thing is that most people still aren't working with a good migrations system to manage changes to their schema... which means logic held in database triggers and stored procedures quickly becomes a non-version-controlled not-properly-tested mess. Good migration systems exist, and people should use them! I held off on doing interesting things with triggers for more than a decade. In the past year I've started leaning into them much more heavily (actually using them in SQLite) because I have confidence that I can both write good tests for them and have good migrations automation in place for version-controlling my schema.
  - deleted 3y ago
    [deleted]
- marcosdumay 3y ago
  Postgres has plugins for running the entire backend. People don't like it for a lot of reasons. Making privilege escalation harder is a big one, but also, all the CPU (and memory) load on serializing that data is CPU that could be used managing the distributed processes problems that only the DBMS can solve. Personally, I think access management on those tools needs to improve a lot before they get usable. But also, the data-oriented languages have some issues, and the non data-oriented ones don't gain much by running inside the database. IMO, we are missing a really good data-oriented language. But I don't see any gain from running it inside the database.
- JohnBooty 3y ago
  After several decades in the business, I have learned not to underestimate the massive power of trendiness. A lot of the argument against it is: SQL is stodgy and uncool, and I want to use Cool Language XYZ. And honestly, I don't entirely blame people for that. If you want to work in this industry you need to do things the "cool" way or you'll never even be considered for roles. Gotta keep that resume looking good. People also don't appreciate how performant it can be. Depending on what you're doing it can be orders of magnitude more performant to do things in-database versus shuffling things back and forth. (It can also be less performant...) There are definitely cons to that approach. One is scalability. It's easier to scale your app layer horizontally than it is to scale your database server vertically. This isn't necessarily an issue: a modern beefy server CPU with 64-128 cores and a TB or two of RAM is more than 99.9% of companies need, is really not that expensive, and is probably a lot cheaper than more complicated setups and extra devops headcount. But that's not cool either. Two is the language/skills mismatch. You've got an app layer in one language, a frontend in another, and now potentially a data storage layer written in a third. This is a valid concern, but also nobody seems to use it as an argument against Javascript frontends, so apparently sometimes it's cool and sometimes it isn't. Debugging stored procedures sort of sucks. That's fair. (But also, nobody is saying to rewrite your entire app, or even most of it, in the DB layer) Common migration tools often don't really have explicit support for stored procs and things like that, but AFAIK they do let you run arbitrary SQL DDL stuff, so I don't think this is a hard barrier.
vsareto 3y ago
>Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL, it's very tedious day-to-day compared to writing C#.
- marcosdumay 3y ago
  > Having done some of this in T-SQL Don't expect your experience with any other DBMS to give you an idea about how nice it is to program in Postgres. It's still not as nice as creating some independent code. But Postgres is quite nice to program in.
  - dangets 3y ago
    Genuinely curious what your experiences of postgres programming you are fond of. Are you talking about functions & procedures in pgsql or are you using an extension to enable a different language? Do you have any interesting blog links?
    - marcosdumay 3y ago
      Yes, I meant that plpgsql is on a complete different level from t-sql (and t-sql is already on a different level from pl/sql). But also, you don't need extensions to enable other languages, postgres allows those out of the box (through the C-ABI). Anyway, that is not very compelling, because plpgsql is quite good already.
- totallywrong 3y ago
  I've never done it so I won't comment, but Postgres supports Perl, Tcl, and Python besides SQL.
  - pmarreck 3y ago
sodapopcan 3y ago
I run a bunch of business logic in the database but have no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps?
- JohnBooty 3y ago
  How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps? Yeah, I think our position is often misunderstood as "put everything in the database layer." That is definitely not how I think. I just think moving stuff to the database layer is one possible tool in the toolshed. One thing I like to use it for is when it's a "low level" database concern, like generating an audit trail whenever a particular table is changed. To me, populating that audit trail is clearly a database concern and not an application concern. (And if there are heavy writes to that table, the performance difference may be large)
  - jmull 3y ago
    > How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? Not the previous poster but... I would think of it as an application layer that is running at the database. It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth. I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways. The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.
pmarreck 3y ago
I completely avoided the need for maintaining an additional dependency (ElasticSearch) simply by taking advantage of fulltext search in Postgres. This did involve some writing of triggers and stored procedure code (which was generated by server-side code, but anyway), which is technically application logic. There are no perfect solutions in engineering, only tradeoffs.
- JohnBooty 3y ago
  I love this! There are definitely cons to moving functionality to Postgres, but avoiding an additional dependency can be a massive pro.

Micoloth 3y ago

Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?

candiddevmike 3y ago
Few reasons: - Enforce application logic with constraints so you don't have to duplicate it - Use triggers and get access to things like old and new without having to create a bunch of transactions - If you have multiple apps sharing a DB, you either keep your business logic consistent across them vs just doing it in the database - You can version your business logic with your schema
__jem 3y ago
Friction with modern devops practices is a big one.
xboxnolifes 3y ago
The tooling around maintaining logic-in-db is worse than the tooling for logic-out-of-db.
simonw 3y ago
I think the main thing is that most people still aren't working with a good migrations system to manage changes to their schema... which means logic held in database triggers and stored procedures quickly becomes a non-version-controlled not-properly-tested mess. Good migration systems exist, and people should use them! I held off on doing interesting things with triggers for more than a decade. In the past year I've started leaning into them much more heavily (actually using them in SQLite) because I have confidence that I can both write good tests for them and have good migrations automation in place for version-controlling my schema.
- deleted 3y ago
  [deleted]
marcosdumay 3y ago
Postgres has plugins for running the entire backend. People don't like it for a lot of reasons. Making privilege escalation harder is a big one, but also, all the CPU (and memory) load on serializing that data is CPU that could be used managing the distributed processes problems that only the DBMS can solve. Personally, I think access management on those tools needs to improve a lot before they get usable. But also, the data-oriented languages have some issues, and the non data-oriented ones don't gain much by running inside the database. IMO, we are missing a really good data-oriented language. But I don't see any gain from running it inside the database.
JohnBooty 3y ago
After several decades in the business, I have learned not to underestimate the massive power of trendiness. A lot of the argument against it is: SQL is stodgy and uncool, and I want to use Cool Language XYZ. And honestly, I don't entirely blame people for that. If you want to work in this industry you need to do things the "cool" way or you'll never even be considered for roles. Gotta keep that resume looking good. People also don't appreciate how performant it can be. Depending on what you're doing it can be orders of magnitude more performant to do things in-database versus shuffling things back and forth. (It can also be less performant...) There are definitely cons to that approach. One is scalability. It's easier to scale your app layer horizontally than it is to scale your database server vertically. This isn't necessarily an issue: a modern beefy server CPU with 64-128 cores and a TB or two of RAM is more than 99.9% of companies need, is really not that expensive, and is probably a lot cheaper than more complicated setups and extra devops headcount. But that's not cool either. Two is the language/skills mismatch. You've got an app layer in one language, a frontend in another, and now potentially a data storage layer written in a third. This is a valid concern, but also nobody seems to use it as an argument against Javascript frontends, so apparently sometimes it's cool and sometimes it isn't. Debugging stored procedures sort of sucks. That's fair. (But also, nobody is saying to rewrite your entire app, or even most of it, in the DB layer) Common migration tools often don't really have explicit support for stored procs and things like that, but AFAIK they do let you run arbitrary SQL DDL stuff, so I don't think this is a hard barrier.

vsareto 3y ago

>Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL, it's very tedious day-to-day compared to writing C#.

marcosdumay 3y ago
> Having done some of this in T-SQL Don't expect your experience with any other DBMS to give you an idea about how nice it is to program in Postgres. It's still not as nice as creating some independent code. But Postgres is quite nice to program in.
- dangets 3y ago
  Genuinely curious what your experiences of postgres programming you are fond of. Are you talking about functions & procedures in pgsql or are you using an extension to enable a different language? Do you have any interesting blog links?
  - marcosdumay 3y ago
    Yes, I meant that plpgsql is on a complete different level from t-sql (and t-sql is already on a different level from pl/sql). But also, you don't need extensions to enable other languages, postgres allows those out of the box (through the C-ABI). Anyway, that is not very compelling, because plpgsql is quite good already.
totallywrong 3y ago
I've never done it so I won't comment, but Postgres supports Perl, Tcl, and Python besides SQL.
- pmarreck 3y ago

sodapopcan 3y ago

I run a bunch of business logic in the database but have no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps?

JohnBooty 3y ago
How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps? Yeah, I think our position is often misunderstood as "put everything in the database layer." That is definitely not how I think. I just think moving stuff to the database layer is one possible tool in the toolshed. One thing I like to use it for is when it's a "low level" database concern, like generating an audit trail whenever a particular table is changed. To me, populating that audit trail is clearly a database concern and not an application concern. (And if there are heavy writes to that table, the performance difference may be large)
- jmull 3y ago
  > How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? Not the previous poster but... I would think of it as an application layer that is running at the database. It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth. I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways. The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.

pmarreck 3y ago

I completely avoided the need for maintaining an additional dependency (ElasticSearch) simply by taking advantage of fulltext search in Postgres. This did involve some writing of triggers and stored procedure code (which was generated by server-side code, but anyway), which is technically application logic. There are no perfect solutions in engineering, only tradeoffs.

JohnBooty 3y ago
I love this! There are definitely cons to moving functionality to Postgres, but avoiding an additional dependency can be a massive pro.