I couldn’t let you tell your opinions about triggers without giving mine! So, this is my PGSQL Phriday contribution!
Disclaimer 🔗
My opinion comes from years of practicing as a production DBA, then as a database consultant. As such a professional, my opinion is biased because I am never called when it works! I’ve always been called when there are problems (big problems, usually) so that I see the worst developers can do and never the best. I try to be aware of that bias, but it’s not that easy.
Why I would like developers to stop using triggers 🔗
Triggers are good when used by the internal of Postgres to enforce foreign keys, partitioning, and so on. But each time you think “Oh I can do that with a trigger”, please reconsider.
I think of triggers given to developers as very sharp knives given to toddlers. Please, stop hurting my little baby databases! I’ve seen so many databases that poorly managed and taken care of that it’s heartbreaking! Triggers are not the only culprits (of course), but it is often enough that when a university teacher, a friend of mine, asked me how he could teach triggers to his students, my answer was “Please don’t. Teach them CTEs, window functions, filter clause, advanced aggregation, but not triggers!”.
Triggers will come with a performance hit that can hurt very badly when your database size will grow. Maybe it’s worth changing your application query instead? If you’re worried about having to write several queries to update several tables as the result of an update on one table, maybe it’s time to learn about CTE.
Is there a legitimate use case for triggers? 🔗
That’s a question I’ve been struggling with for a long time. First, some vocabulary, when I write “triggers”, I mean the ones that will be written specifically for your database, not an extension that uses triggers under the hood. if the extension is used in a lot of production contexts, the probability it is badly written is lesser.
- People often think of auditing as a legitime use case for triggers. I don’t. Auditing DDl (Data Definition Language) or DML (Data Modifying Language) can be done by logging your DDL or DML queries in Postgres log (in CSV format, my favorite), and then you can mount these files into another database with Foreign Data Wrappers to query it with SQL. If you think you need more choices to pick the objects and/or events you’d like to audit use the pgaduit extension. If you need an example of how to exploit Postgres log with SQL, look at my own extension pglog.
- Another use case that is often given is adding “technical fields” into the table, like who made the change, when, and so on. My answer to that is: that’s the same purpose as auditing, so use auditing!
What I’m trying to say is that you should very carefully take these kinds of design decisions because it will have implications in the future for you, your team and your company.
In a nutshell 🔗
As you have understood, I do hate triggers because very often, it feels like the devs were lazy and decided to “make a small change in the database” rather than making that same small change in the application. Being lazy when you’re working in IT is a great thing because you will automate a task when you have to do it more than once. When it comes to taking design decisions, being lazy might be a curse.
So, let’s create 2 “trigger” rules based on the 2 “optimization rules”:
- if you’re not an expert, don’t do it.
- if you’re an expert, don’t do it yet.