How Postgres Triggers Can Simplify Your Back End Development • Hacker News Board

Re: How Postgres Triggers Can Simplify Your Back End Development

#41

Post by ysavir » Sun, Apr 23, 2023, 3:50 PM UTC

simonw wrote: post #22

bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

I think this problem can be robustly solved if you have the right mechanisms in place: 1. Migrations. Your schema needs to live in version control, and changes to your schema must be applied by an automated system. Django migrations are the gold standard here in my opinion, but you can stitch together a custom system if you need to, one that tracks which migrations have been run already and provides a mechanism to ap…

But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?

Re: How Postgres Triggers Can Simplify Your Back End Development

#42

Post by whstl » Sun, Apr 23, 2023, 3:50 PM UTC

bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

Business rules are already often in multiple separated places in a codebase. If you mean "it should be in the same git repository", then triggers should be already in migrations.

But IMO, even migrations are a stopgap solution to this problem. In some cases, such as in ORMs like Rails' ActiveRecord, even the column names aren't present in the model by default, they are only migration files.

The real solution is putting the definition of the triggers (and all other database things too) closer to the code that operates on them. In models, for example. And models should enforce that only the defined triggers are present in the database. Of course, since most ORM users aren't really big DB users, this isn't exactly common. Until we get features like this, I agree that triggers can be confusing.

Re: How Postgres Triggers Can Simplify Your Back End Development

#43

Post by chank » Sun, Apr 23, 2023, 3:50 PM UTC

I feel like we're coming full circle 30+ years and have to re-learn the perils of db triggers. When to use them and when to not. These days most of the db has been abstracted away from developers using ORMs so it must seem like the discovery of something new when in fact we already know.

Don't write business logic in the datastore.

Re: How Postgres Triggers Can Simplify Your Back End Development

#44

Post by ysavir » Sun, Apr 23, 2023, 3:51 PM UTC

masklinn wrote: post #38

Aqueous wrote: post #27

Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist…

> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.

I worked for a short while at a place that tried following a similar dogma. Hiring was incredibly difficult, as was retaining people (such as myself). Writing business logic in code instead of DB functions is much more approachable than keeping it in the DB.

Re: How Postgres Triggers Can Simplify Your Back End Development

#45

Post by simonw » Sun, Apr 23, 2023, 3:54 PM UTC

ysavir wrote: post #41

simonw wrote: post #22

Earlier quoted context omitted.

I think this problem can be robustly solved if you have the right mechanisms in place: 1. Migrations. Your schema needs to live in version control, and changes to your schema must be applied by an automated system. Django migrations are the gold standard here in my opinion, but you can stitch together a custom system if you need to, one that tracks which migrations have been run already and provides a mechanism to ap…

But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?

Triggers protect you against bugs.

If your application logic needs to remember to update a denormalized column somewhere it's very easy for a bug to slip in in the future when someone adds a new piece of Python code but forgets to update the denormalized column.

With triggers you can eliminate that source of bugs entirely.

This is even more valuable if you have more than one system interacting with a single database.

Re: How Postgres Triggers Can Simplify Your Back End Development

#46

Post by eastbound » Sun, Apr 23, 2023, 3:57 PM UTC

throwaway173738 wrote: post #20

bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

Yeah I was expecting the post author to discuss the trade-off being made here because it’s really important to do so. The biggest complaint I have with these pithy articles is that they try to sell you on a particular trade-off without explaining what the deal is. It makes me think the author: 1. Just discovered this. 2. Implemented a bunch of them. 3. Hasn’t been maintaining this solution for more than a couple of m…

DB upgrades are a pain. Triggers must be deployed with “UPDATE” instructions, it’s not as easy as keeping code in git.

Re: How Postgres Triggers Can Simplify Your Back End Development

#47

Post by paulryanrogers » Sun, Apr 23, 2023, 3:58 PM UTC

simonw wrote: post #45

ysavir wrote: post #41

Earlier quoted context omitted.

But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?

Triggers protect you against bugs. If your application logic needs to remember to update a denormalized column somewhere it's very easy for a bug to slip in in the future when someone adds a new piece of Python code but forgets to update the denormalized column. With triggers you can eliminate that source of bugs entirely. This is even more valuable if you have more than one system interacting with a single database.

I did this with robust DB constraints and selective use of triggers and stored functions. It can feel like a strait jacket long term. Though it did help protect against less competent DBAs breaking things and avoid some footguns.

Re: How Postgres Triggers Can Simplify Your Back End Development

#48

Post by paulryanrogers » Sun, Apr 23, 2023, 3:59 PM UTC

eastbound wrote: post #46

throwaway173738 wrote: post #20

Earlier quoted context omitted.

Yeah I was expecting the post author to discuss the trade-off being made here because it’s really important to do so. The biggest complaint I have with these pithy articles is that they try to sell you on a particular trade-off without explaining what the deal is. It makes me think the author: 1. Just discovered this. 2. Implemented a bunch of them. 3. Hasn’t been maintaining this solution for more than a couple of m…

DB upgrades are a pain. Triggers must be deployed with “UPDATE” instructions, it’s not as easy as keeping code in git.

Triggers are DDL, unless you've implemented them in the app layer.

Re: How Postgres Triggers Can Simplify Your Back End Development

#49

Post by marcosdumay » Sun, Apr 23, 2023, 4:01 PM UTC

AmericanChopper wrote: post #16

bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

Table triggers are the ultimate foot gun in this respect. They are highly obscured! Stored procedures split your business logic too, but when you want to go and look at your database logic, it’s at least where you’re expecting to find it, and not built into a table. I’d highly recommend people avoid them, unless you feel that you really need them _and_ you have very robust development processes. As soon as you deploy…

> from that moment you have to check every DML statement

If that's the case, you have a documentation problem. It should be easy to decide if there are side effects or not just by looking at the DML and the metadata you need for it.

In fact, the case here is that the trigger on the article is an incredibly bad one. It's not a natural consequence of the table, or the database structure. It's probably not even always true to the business rules, what is the one property triggers must have no matter what.

Re: How Postgres Triggers Can Simplify Your Back End Development

#50

Post by SOLAR_FIELDS » Sun, Apr 23, 2023, 4:02 PM UTC

simonw wrote: post #45

ysavir wrote: post #41

Earlier quoted context omitted.

But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?

Triggers protect you against bugs. If your application logic needs to remember to update a denormalized column somewhere it's very easy for a bug to slip in in the future when someone adds a new piece of Python code but forgets to update the denormalized column. With triggers you can eliminate that source of bugs entirely. This is even more valuable if you have more than one system interacting with a single database.

Triggers can also be significantly more performant depending on what you are doing.

But yeah having the business logic split like this is a hard sell. There needs to be an extremely good reason to do it. It adds developer overhead because not only do you have to maintain another test harness and deal with an entirely separate system encapsulating yours, every time you touch business logic you’re now wasting some brainpower deciding where it should live.