Triggers and me

April 7, 2023
PGSQL Phriday PostgreSQL

I couldn’t let you tell your opinions about triggers without giving mine! So, this is my PGSQL Phriday contribution!


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.

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”:

PGSQL Phriday #015: Primary keys: UUID, CUID, or TSID?

February 3, 2024
PGSQL Phriday PostgreSQL

PGSQL Phriday #015: UUID: let's fight!

January 27, 2024
PGSQL Phriday PostgreSQL

PGSQL Phriday #010: Log analysis

July 7, 2023
PGSQL Phriday PostgreSQL