How Postgres Triggers Can Simplify Your Back End Development
themythicalengineer.com
How Postgres Triggers Can Simplify Your Back End Development
1–10 of 112 posts
Re: How Postgres Triggers Can Simplify Your Back End Development
#2There’s so much value in database triggers when they’re done right. Anything where live stats are needed gets a whole lot easier with triggers rather than counts.
Re: How Postgres Triggers Can Simplify Your Back End Development
#3I 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.
Re: How Postgres Triggers Can Simplify Your Back End Development
#4There’s so much value in database triggers when they’re done right. Anything where live stats are needed gets a whole lot easier with triggers rather than counts.
Then you discover Materialised Views... :) Magic.
Re: How Postgres Triggers Can Simplify Your Back End Development
#5We 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, developer tooling, observability etc..
Re: How Postgres Triggers Can Simplify Your Back End Development
#6I 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.
How do these techiques work with replication and sharding? Or do you just use cloud managed pg like AWS RDS to not think about it?
Re: How Postgres Triggers Can Simplify Your Back End Development
#7I 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.
Most people on modern engineering teams haven't built systems services themselves. I've seen this cause a lot of insecurity on making choices, because they don't have the experience in how to actually assess what will work or won't. It's far easier to fall back on the industry hive mind, e.g. "nobody ever got fired for buying $X".
Re: How Postgres Triggers Can Simplify Your Back End Development
#8My 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 needed. PostgreSQL is a real powerhouse.
Re: How Postgres Triggers Can Simplify Your Back End Development
#9I wouldn’t call this “simplified”.
Personally, it’s much more valuable to have all business logic in one place, in a single language, available at a glance. The perforance gain isn’t worth the increased complexity in codebase.
Re: How Postgres Triggers Can Simplify Your Back End Development
#10I worked at a company which relied on significant use of Postgres triggers and it was not simplified in my mind due to:
- Engineers being more comfortable expressing the required business logic in the other languages they were working in then PL/pgSQL
- Challenging to write tests for the triggers
- Harder to deploy variations for testing if needed