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

51–60 of 112 posts

Re: How Postgres Triggers Can Simplify Your Back End Development

#51

Post by javajosh »

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…

> Django migrations are the gold standard here in my opinion

Intriguing. In JavaWorld it's really only Flyway and Liquibase, and the important self-imposed rule, "Thou shalt never remove a column from a table."

Re: How Postgres Triggers Can Simplify Your Back End Development

#52

Post by ukd1 »

Using triggers when you have a single-codebase is prone to obscuring where things happen - is it in code, or in a trigger somewhere? However, when you have multiple different codebases touching the same db, it can be great at enforcing things to happen in the same way across these.

Re: How Postgres Triggers Can Simplify Your Back End Development

#53

Post by OJFord »

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…

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, that would be a significant improvement IMO. But I think the Django maintainers at least would say they want the ORM DSL to be that. (But it's way too incomplete, you'd be limited to a tiny subset of postgres, and even then you have to be on board with that being a reasonable description of your schema, not wanting the actual db schema anywhere.)

Re: How Postgres Triggers Can Simplify Your Back End Development

#54

Post by tuyguntn »

It's easy to start with when your project is small, especially for quick fixes and improvements (e.g. total # of orders made by user), later it will become a mess and makes your project difficult to maintain, because you usually don't test your database in unit tests and database migrations are still a thing (from one db to another, from one type of columns to another and so on)

Re: How Postgres Triggers Can Simplify Your Back End Development

#55

Post by AmericanChopper »

marcosdumay wrote: post #49
AmericanChopper wrote: post #16

Earlier quoted context omitted.

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 t…

The problem isn’t really that it’s hard to document. The problem is that when you don’t use triggers, you don’t have to consult documentation to know what’s going to happen after an insert (or an update, or delete…). People who haven’t used triggers would assume that a row will simply be inserted. But if you use triggers this is no longer a safe assumption.

Re: How Postgres Triggers Can Simplify Your Back End Development

#56

Post by __jem »

ysavir wrote: post #44
masklinn wrote: post #38

Earlier quoted context omitted.

> 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.

There's also a real friction here with modern devops tooling. We have great off the shelf patterns now for doing blue/green deployments, monitoring, etc. Having to run a migration every time you want to update some business logic feels a lot worse even if it has some marginal benefits in terms of single source of truth.

Re: How Postgres Triggers Can Simplify Your Back End Development

#57

Post by brightball »

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…

You can say similar things about any API based development though. Triggers are just a part of the database API.

Databases exist to manage data, not just store and retrieve it.

Re: How Postgres Triggers Can Simplify Your Back End Development

#58

Post by brightball »

javajosh wrote: post #51
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…

> Django migrations are the gold standard here in my opinion Intriguing. In JavaWorld it's really only Flyway and Liquibase, and the important self-imposed rule, "Thou shalt never remove a column from a table."

I've always resisted the Django migration approach personally. One of the things I disliked most about it.

Re: How Postgres Triggers Can Simplify Your Back End Development

#60

Post by asguy »

hansvm wrote: post #37
thinkingkong wrote: post #3

I love these types of techniques. Need a basic no nonsense queue? Postgres. Need a basic reporting infrastructure? Postgres. Need a document store? Postgres. But every single time this comes up, people on the engineering teams Ive been on all throw their hands up and accuse folks of overengineering or underengineering. You need rabbit or kafka. We should move to mongo. Etc. Thats the part thats hard.

Depending on the team I'd still opt for using "anything else" rather than Postgres as a queue. The tendency to have long-lived connections combined with a flow of short-lived messages will yield a runaway queue (due to MVCC) at low enough message rates that even an early-stage startup might notice.

Maybe, but at “mid” production scale I’ve used Postgres plenty as the substrate for queues and managing requests for FSMs and their state changes, and didn’t run into these problems.

Like everything, it depends on the application.

Post reply on HN