> You should consider the fact that complexity will still be there, but it will be abstracted away inside the database.
You have got to be kidding me.
How Postgres Triggers Can Simplify Your Back End Development
71–80 of 112 posts
Re: How Postgres Triggers Can Simplify Your Back End Development
#72Why 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…
You don’t have to write business logic in the db. You essentially listen for notifications from workers who implement the business logic depending on the db changes. It is essentially like a poor man’s message queue for us.
Re: How Postgres Triggers Can Simplify Your Back End Development
#73Earlier 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…
This comes up a lot, and I'm always surprised that more people don't know about Flyway and "repeatable migrations": https://flywaydb.org/documentation/tutorials/repeatable
> Repeatable migrations are very useful for managing database objects whose definition can then simply be maintained in a single file in version control. Instead of being run just once, they are (re-)applied every time their checksum changes.
Re: How Postgres Triggers Can Simplify Your Back End Development
#74Earlier 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…
> 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. This is basically the clickbait in the wider world affecting software development, even though it might not look like it. Boiled down to the essentials, we are telling each other the software version of "Here's How to Lose 10 Pounds in Time for Summer." way more often th…
It’s unfortunately hard for me to point to a source because everything I know I absorbed from more experienced engineers on hardware projects.
Re: How Postgres Triggers Can Simplify Your Back End Development
#7590% of the time this kind of thing should be done in stored procedures, not triggers. You know when you are calling a stored procedure; you cannot do it accidentally. Triggers can cause things to happen "by magic" if you aren't keenly aware that they are there. They also complicate large updates.
Triggers to do something that's simple and always required, e.g. updating a primary key index for a new row (before autoincrement was available) can be OK, but use them sparingly.
I like putting business logic in the database, because you only write it once and not for each client application. Client applications and platforms and their development languages come and go a lot more frequently than databases. But I use stored procedures almost always, and rarely triggers.
Re: How Postgres Triggers Can Simplify Your Back End Development
#76All true, but still a bad idea. Splitting your concerns across multiple systems will eventually bite you. It's too hard to reason about, and unless you want to make some very hard yards, there's not even sensible source control.
Re: How Postgres Triggers Can Simplify Your Back End Development
#7790% of the time this kind of thing should be done in stored procedures, not triggers. You know when you are calling a stored procedure; you cannot do it accidentally. Triggers can cause things to happen "by magic" if you aren't keenly aware that they are there. They also complicate large updates. Triggers to do something that's simple and always required, e.g. updating a primary key index for a new row (before autoin…
[deleted]
Re: How Postgres Triggers Can Simplify Your Back End Development
#78We use PG Notify at work extensively and it's the source of a lot of pain and suffering. Not because of the functionality itself, more so because what we did to ourselves by using it in the way we did. I think this could be great for certain projects, but there is a lot room to put yourself into a situation that's hard to maintain if you/you're team doesn't possess the right amount of discipline around documentation,…
> more so because what we did to ourselves by using it in the way we did.
what was the cause of pain?
Re: How Postgres Triggers Can Simplify Your Back End Development
#79Earlier 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.
Ok, so how do I checkout a version and view the file/schema tree?
If you like, you can view my comment above as saying 'if they included that tooling, not just the similar tree of changes stored, it would be better'.
How to get from one state to the next is interesting to the computer, not to me (not after I've initially written it/done it in prod anyway), I'm interested in the result, where do we stand after all of the migrations (currently on disk having checked out whatever).
Re: How Postgres Triggers Can Simplify Your Back End Development
#80Earlier quoted context omitted.
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.
It can work great, but if not carefully introduced it's one typo away from a disaster in prod that nobody understands. You just need somebody to introduce a code path with longish transactions interacting with the queue and not have a reasonable prolonged load test in your deployment pipeline. Given how easy other queues are to set up I wouldn't default to Postgres on many teams.