© Laurenz Albe 2022 Recently, a customer asked me why a bulk load into a PostgreSQL table kept slowing down as time went by. They saw that it was a trigger on the table that took longer and longer, and in that trigger, the updates were getting slower all the time. Now slow updates are […]

© Laurenz Albe 2022 Table partitioning is one of the best-liked features out of the more recent PostgreSQL developments. However, there is no support for automatic partition creation yet. This article shows what you can do to remedy that. Use cases for automatic partition creation There are essentially two use cases: Create partitions triggered by […]

© Laurenz Albe 2021 In this article, I’ll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification. Triggers in PostgreSQL A trigger in PostgreSQL consists of two parts: a trigger function the […]

Just like in most databases, in PostgreSQL a trigger is a way to automatically respond to events. Maybe you want to run a function if data is inserted into a table. Maybe you want to audit the deletion of data, or simply respond to some UPDATE statement. That is exactly what a trigger is good […]

What are the performance differences between normal and generic audit triggers? Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how does it look from the performance side? To quickly recap the previous blog post: the most common approaches for […]

  Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for. It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”. […]

  Inspired by my co-worker’s recent blog post, I decided to revisit the old question of rules vs. triggers and run a little benchmark to see which one does better. About rules While triggers are well known to most application developers and database administrators, rules are less well known. The full name “query rewrite rule” […]

By Kaarel Moppel – In my last post I described what to expect from simple PL/pgSQL triggers in performance degradation sense, when doing some inspection/changing on the incoming row data. The conclusion for the most common “audit fields” type of use case was that we should not worry about it too much and just create […]

First, the big question – should we be using good old triggers at all? Well, actually I’m not going to recommend anything here, as it’s an opinionated topic:) People well-versed in databases would probably see good use cases for them, whereas modern application developers would mostly say it’s an awful practice – doing some “magic […]

UPDATED 21 March 2023: Tracking database changes and tracing users has always been a vitally important part of PostgreSQL database security and application security. Especially when critical data are stored, it might be interesting to know who has changed which data when and how. Generic Changelog Triggers in PostgreSQL are the key to tracking changes […]