Performance Impact of PostgreSQL Triggers

First things first, what are PostgreSQL triggers?

PostgreSQL triggers are special functions that automatically execute in response to specific database events, such as inserts, updates, or deletes, on a table (or on materialised views with some limitations).

Think of them as built-in listeners: when something changes in your data, a trigger can step in and run additional logic.

PostgreSQL triggers are often used for:

• Enforcing business rules;
• Maintaining audit logs;
• Propagating changes to related tables;
• Powering patterns like event sourcing or the outbox pattern.

Because triggers run inside the database engine, they ensure your logic is always applied, regardless of which part of your application the change is coming from. This makes them a powerful tool for ensuring consistency and decoupling event-driven logic from application code.
This article explores how you can leverage triggers to implement an outbox pattern, and what performance trade-offs come with that choice (if any).

Wait, what is an outbox pattern?

In distributed systems, it is often necessary to capture and reliably propagate changes from one service to others, for example updating downstream systems or sending events to a message broker when a new order is placed.

The outbox pattern helps achieve this by writing changes to a dedicated outbox table within the same transaction as the original data change.

Why is that necessary?

Instead of trying to publish events immediately after a database write — which risks inconsistencies if something fails — the application or database writes a change event to a dedicated outbox table as part of the same transaction that updates the business data.

This ensures consistency and prevents data loss due to partial failures. This way, both the main data and the event are committed together: either they both succeed, or they both fail.

What happens next?

A separate downstream process continuously reads new entries from the outbox table and safely publishes them to a message queue (e.g. Kafka, RabbitMQ) or sends them to other services. This decouples event publishing from the core business logic and allows for reliable, eventually consistent communication across systems.

Are PostgreSQL triggers performant enough?

More specifically, are they performant enough to be a viable option here?

PostgreSQL triggers offer a lightweight and reliable way to implement the outbox pattern. They allow automatic insertion into the outbox table immediately after a row is added or updated, without changing the application logic. This keeps the system decoupled, consistent, and easy to maintain, especially in architectures that rely on event-driven communication.

In this article, we are going to explore the overhead that comes from using PostgreSQL triggers when inserting records into test tables. With a simple outbox pattern, we are going to compare performance across three scenarios:

1. No triggers
2. Insert-only triggers
3. Join triggers

We did this benchmarking with pgbench on a lightweight AWS EC2 t2.micro instance running PostgreSQL 15.12.

By the end of this post, you will know:

• How insert-only and join-based triggers affect latency and throughput;
• Whether triggers are viable for real-time data change capture at scale;
• How to implement and benchmark these triggers on your own.

Test scenarios

We tested three different setups:

1. No triggers (baseline): Direct inserts into orders and order_lines tables;
2. Insert-only Trigger: After each insert, a trigger adds a JSON payload to the outbox table;
3. Join trigger: For each insert into order_lines, a trigger performs a join with the orders table and inserts a denormalised payload into the outbox.

Benchmark setup

• Instance Type: AWS EC2 t2.micro
• PostgreSQL Version: 15.12
• Tool used: pgbench (it simulates concurrent clients running SQL statements, providing a realistic measure of latency and throughput.
• Benchmark parameters:
  • Transactions: 1,000,000
  • Clients: 10
  • Threads: 4

Let’s prepare for benchmarking

Before diving into benchmarking, we need to set up the schema and triggers. Below is a breakdown of each piece of SQL and its purpose in this test.

1. Create orders and order_lines tables

These tables simulate a simple e-commerce domain: the orders table represents customer purchases, and the order_lines table represents the individual items in each order. We will use them as the primary data source for our triggers.

2. Create outbox tables

The outbox tables capture change events generated by the triggers.

We are using two variations:

One to store raw JSON payloads (insert-only trigger):

And one to store joined, denormalised order and order_line data (join trigger):

3. Trigger functions

These functions define the logic that runs when a new record is inserted:

The insert-only function stores the inserted row as-is in JSON format.

The join function looks up the related orders data and stores a combined record.

4. Trigger definitions

These triggers hook into the AFTER INSERT event on the orders and order_lines tables. They call the appropriate trigger function depending on the test scenario.

Triggers do not implicitly form their own transactions; instead, they execute within the transaction context of the SQL statement that fired them. This ensures that both the main data change and the outbox event succeed or fail together, maintaining consistency.

5. Insert scripts for pgbench

The following SQL scripts simulate application inserts during the benchmark.

insert_orders.sql inserts rows into orders (used in baseline and insert-only tests):

insert_order_lines.sql inserts rows into order_lines (used in the join trigger test):

6. Benchmark commands

These commands run the actual pgbench test using the insert scripts under different scenarios, viz. without triggers (baseline), with the insert-only trigger, and with the join trigger:

Performance results

Key observations

Based on results of the tests we performed, we made the following observations:

There is minimal overhead from triggers

Enabling an insert-only trigger increased average latency by 2.7% and reduced throughput (TPS) by around 2.6%.

The join-based trigger introduced only a 0.17% increase in latency compared to insert-only.

Under our benchmark load, we did not encounter any significant fluctuations of CPU utilisation between the two trigger options.

This suggests that PostgreSQL handles JSON serialisation and joins within triggers efficiently.

The outbox pattern is scalable

Despite the additional workload of writing to an outbox table, TPS remained high (~3,465+), showing that PostgreSQL can sustain a robust change-tracking mechanism with minimal performance degradation.

We got zero failures

All three tests successfully processed 1 million transactions each, even with concurrent clients and added triggers. This demonstrates high reliability and consistency with trigger-based approaches.

Conclusion

The use of PostgreSQL triggers to implement an outbox pattern adds minimal overhead during inserts. Even under high transaction volumes and concurrency, PostgreSQL maintains excellent performance, with only slight reductions in TPS and latency. This makes triggers a practical solution for Change Data Capture (CDC) in microservice architectures.

It is worth noting that the performance cost may increase under larger datasets or more complex joins. Hence, for production systems, it is important to test under representative loads.

If you found this article on PostgreSQL triggers helpful, make sure to visit the Infinite Lambda Blog for more insights.