How Postgres Triggers Can Simplify Your Back End Development

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

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.<p>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 all in one place.

We use PG Notify at work extensively and it&#x27;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.<p>I think this could be great for certain projects, but there is a lot room to put yourself into a situation that&#x27;s hard to maintain if you&#x2F;you&#x27;re team doesn&#x27;t possess the right amount of discipline around documentation, developer tooling, observability etc..

What&#x27;s old is new again. In 2007 we were using triggers and stored procedures heavily with mysql and a java app. Unfortunately we were also reliant on read replicas. Some of this replication behaviour did not translate well with the mix of these functions and auto incrementing IDs. Sometimes it would result in foreign key constraint violations and all of a sudden our replication would stop. This was even worse when we tried multi master setups. I spent years dealing with this. Ultimately we dropped the use of the most complex queries and shifted them into code which made the replication more stable but at the cost of Dev time.<p>Morale of the story, use it with caution. I know postgres is different but when you start turning you database into a ball of mud things get dangerously difficult to debug and fix.

90% 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 &quot;by magic&quot; if you aren&#x27;t keenly aware that they are there. They also complicate large updates.<p>Triggers to do something that&#x27;s simple and <i>always</i> required, e.g. updating a primary key index for a new row (before autoincrement was available) can be OK, but use them sparingly.<p>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.

I feel like we&#x27;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.<p>Don&#x27;t write business logic in the datastore.

&gt; Triggers should be used with caution since they can obscure critical logic and create an illusion of automatic processes<p>Summarizes why in my opinion using triggers is rather risky and confusing. You introduce side effects to operations that one might suspect are CRUDlike. Your code is made non-atomic, in that you need knowledge of what happens elsewhere to guess why it&#x27;s behaving a certain way. On small projects it&#x27;s rather tempting, but small projects become large projects that then get given to someone else to maintain, and 4y later someone will spend a week trying to understand why the amount column gets updated to another value that they&#x27;re pushing.<p>The only use that I find safe is for database metadata, say if you&#x27;re using triggers to keep track of write origins, or schema metadata. For everything that&#x27;s business logic, I&#x27;d stay away from them

I love these types of techniques. Need a basic no nonsense queue? Postgres. Need a basic reporting infrastructure? Postgres. Need a document store? Postgres.<p>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.<p>Thats the part thats hard.

&#x27;Ate Mongo<p>Luv Postgres<p>Simple as<p>A quick note for anyone thinking about triggers: if there&#x27;s any case whatsoever that you&#x27;re going to have more than one row insert into a table per transaction, please use statement level triggers -- especially if you&#x27;re doing network calls inside the trigger. Triggers execute within the same transaction, they&#x27;re synchronous, and will soak up resources.<p>Hell, if you&#x27;re using network calls in your triggers... please don&#x27;t. Use `LISTEN&#x2F;NOTIFY` or queues to offload that to some other process (whether postgres itself or another process), so PG isn&#x27;t left hanging around waiting for a network return.

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

The article mentions it at the very bottom, but I almost never reach for triggers because they are obscure places to put application logic. On more than one occasion I&#x27;ve been burned by not realizing that the code in the backend did not represent the whole picture of business logic. It&#x27;s more complexity, requiring more documentation, adding another point of failure that probably isn&#x27;t necessary.

One major disadvantage of triggers is the inability to do canary deployments and vastly increased complexity of rolling deployments. When SQL code lives within the application, we can trivially run multiple variants of such code simultaneously. Running alternate version of a trigger for e.g. 10% of traffic is way harder.<p>What I would recommend instead is making use of CTE (Common Table Expression), because DML (modifying queries) inside `WITH` are allowed and taking leverage of `RETURNING` keyword in both `UPDATE` and `INSERT` we can execute multiple inter-dependent updates within single query.<p>With such approach we can trivially run multiple versions of an application in parallel (during deployment, for canary deployment etc.) and we have similar performance advantage of a single roundtrip to database. Additional advantage is the fact that there is only one statement which means that our query will see consistent database view (with very common read committed isolation level it is easy to introduce race conditions unless optimistic locking is used carefully).

I wouldn’t call this “simplified”.<p>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.

Wasn&#x27;t this something that Oracle pushed aggressively like in the 80s or 90s and then everyone agreed it was a maintainability living hell? Is this a thing again for some reason I&#x27;m missing?

Triggers and stored procedures should only handle logic which you do not expect to change. They are notoriously hard to test and debug.<p>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.

I worked at a company which relied on significant use of Postgres triggers and it was not simplified in my mind due to:<p>- Engineers being more comfortable expressing the required business logic in the other languages they were working in then PL&#x2F;pgSQL<p>- Challenging to write tests for the triggers<p>- Harder to deploy variations for testing if needed

If you plan on never changing away from postgres, never having to shard, never needing to do anything that a trigger can’t support, then it is a good option. Which may be true for the vast majority of the apps. You also need sql expertise in addition to app dev expertise.

All true, but still a bad idea. Splitting your concerns across multiple systems will eventually bite you. It&#x27;s too hard to reason about, and unless you want to make some very hard yards, there&#x27;s not even sensible source control.

A colleague of mine talks about the Law of Conservation of Complexity. It boils down to &quot;the complexity will have to go somewhere&quot;.<p>You can make the development of your backend more simple, by shoving the complexity into the database, meaning your backend just does less. That in itself does not make your application any simpler.

For Django there&#x27;s <a href="https:&#x2F;&#x2F;github.com&#x2F;Opus10&#x2F;django-pgtrigger">https:&#x2F;&#x2F;github.com&#x2F;Opus10&#x2F;django-pgtrigger</a> that makes it possible to define triggers right in your models, so you have everything in one place.

Business logic in the database screams anti-pattern to me.<p>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.<p>Seems like a fire waiting to happen.

I&#x27;m torn on this subject. It&#x27;s not a simplification in my view, but just one way to achieve a goal that has pros and cons.<p>The big pro is that you no longer need to remember to update tableB, which is derived from data in tableA due to performance, in your application code every time you update tableA.<p>The cons are that:<p>* You add more state to your DB<p>* You can&#x27;t express the logic in your backend language<p>Thinking more about it, I don&#x27;t think the cons outweigh the pros. I would prefer this trigger logic to be part of the DBMS, so I can express the logic in my backend language and also avoid increasing the dependency of my application logic on DB state.

One of the reasons we use database triggers is that we have a legacy system running on Rails and a new system in Typescript. The old system has an entity that is similar to the new systems entity but a bit different. While in this limbo of sunsetting the old system, we have triggers on the old entity when it changes to update the new entity. The thing is, these triggers invoke a lambda which does the business logic for migrating old row to new row. We could also have the old system maybe make an API call to the new system and skip triggers altogether.

It&#x27;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&#x27;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)

&gt; You should consider the fact that complexity will still be there, but it will be abstracted away inside the database.<p>You have got to be kidding me.

I used them once when I was a young engineer, then quickly realised how much I hated them because of how much they obscured the logic of my application. I hadn&#x27;t even started to worry about migrations at that point.<p>Been first in all my teams since then to loudly voice my opposition when someone suggests it as a quick fix for something more complicated

Heh I vaguely recall at Etsy, predating my time, that a significant amount of business logic was done using stored procedures and triggers.<p>They migrated away from it at some point, but some of the people who handled that migration were still around when I was there. Didn’t sound fun at all, sounded like a horrific nightmare.

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.

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.

We have a trigger that is 1400 lines long. Try debugging that when there is a problem.

And then someone does something to the database that disables the triggers. Or just one of them. Or someone adds the same trigger a second time. It requires a lot of discipline to keep it sane.

Not about the core of the article, but how often are folks using something other than the id column as primary key and having id be a foreign key? This seems extremely confusing to me.

&gt;You should consider the fact that complexity will still be there, but it will be abstracted away inside the database.<p>Where Git isn&#x27;t looking...

Or maybe just use stored procs, which may be better for the wallet example here.

I don&#x27;t like the idea of moving application backend logic to the database.

When you have one problem, you decided to use Postgres Trigger.<p>Now you have two problems.

That&#x27;s just written to trigger me right?<p>Right?

Betbola138