Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How Postgres Triggers Can Simplify Your Back End Development

themythicalengineer.com

Post reply on HN
Print view View original

61–70 of 112 posts

Re: How Postgres Triggers Can Simplify Your Back End Development

#61

Post by avereveard »

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…

it is also a very restrictive environment, you either only trigger on a single in transaction table, or you risk having triggers tripping other triggers, limiting the approach scale anyway. at which point a well managed transaction from an active record or a data gateway will do miles better.

Re: How Postgres Triggers Can Simplify Your Back End Development

#62

Post by hermanradtke »

OJFord wrote: post #53
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…

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today , not a chain of changes that only tell you that after computing the combined effect. Even if they just created a generated file of the final schema, that sat in version control and errored the makemigrations check (just like a missing migration) if it was out of sync, th…

> you want a declarative source of truth for what the schema looks like today, not a chain of changes that only tell you that after computing the combined effect

Applying a series of migrations to get a final db schema is not much different than a version control system like git.

Re: How Postgres Triggers Can Simplify Your Back End Development

#63

Post by bob1029 »

sabzetro wrote: post #39

Business logic in the database screams anti-pattern to me. How do we know who created the rule, edited the rule? How can we reason about the sequence in which these rules are executed based on larger use cases with complex interactions. Seems like a fire waiting to happen.

> How do we know who created the rule, edited the rule? How can we reason about the sequence in which these rules are executed based on larger use cases with complex interactions.

You are presumably operating inside of a database, a place where the above concerns can be tracked in ~3 additional columns. More complex rule arrangements can be addressed with additional tables & relations. If you are starting from a blank schema, everything is possible. As noted by others here, you either go all-in, or all-out. The middle ground where half the logic is in the database and half is in GitHub is where things get yucky.

Consider the simplification angle. There are some techniques that allow for running entire apps directly out of the database. You might not even need Node, .NET, Go, Rust, etc. Hypothetically, if 100% of the things are in a database, you can simply record the binary log to S3 and have a perfect log of everything over time. Imagine how easy it would be to set up a snapshot of a given environment on a developer machine. Inversely, you could directly ship a developer's machine to production. You can also do some crazy shit where you merge bin logs from different timelines via marker transactions.

The other major advantage includes being able to update production while its live, even if production is running on a single box. I saw a particular PL/SQL install earlier in my career that was utilized for this exact property - production literally could not ever drop a single transaction or stop servicing them. Latency beyond 2 seconds could be catastrophic for system stability. Production could come down, but it had to be all or nothing. Think - shutting down a nuclear reactor and the amount of time you are locked out due to the subsequent safety and restart checklists. You absolutely need a way to safely & deterministically update in-between live, serialized transactions or you can't run your business effectively.

Re: How Postgres Triggers Can Simplify Your Back End Development

#64

Post by SinParadise »

sabzetro wrote: post #39

Business logic in the database screams anti-pattern to me. How do we know who created the rule, edited the rule? How can we reason about the sequence in which these rules are executed based on larger use cases with complex interactions. Seems like a fire waiting to happen.

I'd say depends on the complexity of the logic itself. I would never write triggers with any logical branching, but for simple update table B when table A is updated? I definitely see the value in that.

Re: How Postgres Triggers Can Simplify Your Back End Development

#65

Post by heipei »

hermanradtke wrote: post #62
OJFord wrote: post #53

Earlier quoted context omitted.

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today , not a chain of changes that only tell you that after computing the combined effect. Even if they just created a generated file of the final schema, that sat in version control and errored the makemigrations check (just like a missing migration) if it was out of sync, th…

> you want a declarative source of truth for what the schema looks like today, not a chain of changes that only tell you that after computing the combined effect Applying a series of migrations to get a final db schema is not much different than a version control system like git.

It’s completely different as git does not store deltas but snapshots of whole files and file trees.

Re: How Postgres Triggers Can Simplify Your Back End Development

#66

Post by Scubabear68 »

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 have worked with systems with nearly 100k lines code in Oracle stored procs, and another legendary place with around 4 million.

It was a nightmare. Deployments were very difficult, there was little tooling, reasoning about the system was difficult, and of course running so much code in Oracle required very expensive licenses.

And it is much harder to hire hard core PL/SQL devs over Java, C#, Python or whatever.

You really don’t want significant code in the DB. It can be useful for some cases, like automating audit tables, but that is about it.

And for triggers - I feel for anyone maintaining business logic located in triggers. What a debugging hell that can be.

Re: How Postgres Triggers Can Simplify Your Back End Development

#68

Post by airocker »

We use triggers and notifications extensively.!it is great because we don’t have to run a message queue . the only concerns are that notification has a size limit that is quite small. Also, it is harder to implement multiple workers who get only some of the notifications to load balance.

Re: How Postgres Triggers Can Simplify Your Back End Development

#69

Post by simonw »

OJFord wrote: post #53
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…

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today , not a chain of changes that only tell you that after computing the combined effect. Even if they just created a generated file of the final schema, that sat in version control and errored the makemigrations check (just like a missing migration) if it was out of sync, th…

If you really, really need to be able to see a SQL schema representing the current state, a cheap trick is to run an automation on every deploy that snapshots the schema and writes it to a GitHub repository.

I do a version of that for my own (Django-powered) blog here: https://github.com/simonw/simonwillisonblog-backup/blob/main...

Re: How Postgres Triggers Can Simplify Your Back End Development

#70

Post by spprashant »

Triggers and stored procedures should only handle logic which you do not expect to change. They are notoriously hard to test and debug.

They are coupled tightly with database schema design. Making changes to them needs careful consideration and a extensive testbed environment which cannot be mocked with a fraction of the data.

Post reply on HN