Quick Tour of Postgres Logical Replication - Reacting to Changes (Part 3)

August 17, 2026

This post was written for Blaugust 2026

Ok so now we have a beatiful way to 1-way table rows between two pg instances in our sleep in part 1
in part 2, we also learned what happen if there is comm issues between instances and how replication atumatically resume and catches up once those sissues are resolved.

now considering that usually db rarely exists in isolation
usually dbs are just way for an application using said db to manage/store their data

Replication keeps the subscriber’s data synchronized, but sometimes copying the data isn’t enough. The downstream application may need to run additional business logic whenever something changes.

so what if said applicaiton need to be aware of the sync’d changes?
cuz right now we do db to db and the application is completely in the dark
thing maybe your downstream app need to run some more application logic when a user is being sync’d, say rebuild a user’s search index or invalidate a cache or send a notification or whatever
how can we achieve that?

The Inbox Pattern

The replicated table contains the data. The inbox table contains the work that needs to be performed because that data changed.

So basically once pg logical replication will finish replicating the rows
a trigger whill invoce a pg function that will insert a row in our inbox table for each change

this so a app worker containing the actual applicaiton logic can pick up rows from the inbox and actually doing some needed processing

once the processing is confirmed, rows from the inbox table is possible to del so they dont get pick up again by the next worker

quick diagram:

Publisher
    │
    │ logical replication
    ▼
Subscriber
    │
    ├── users / groups / ...
    │
    └── trigger
          │
          ▼
      inbox table
          │
          ▼
       App workers
          │
          ▼
    application logic

Set up an Inbox Table

Your inbox_events

 1-- on the subscriber
 2
 3CREATE TABLE event_inbox (
 4    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 5    table_name   TEXT NOT NULL,
 6    operation    TEXT NOT NULL,
 7    old_data     JSONB,
 8    new_data     JSONB,
 9    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
10);

this is nice cuz in case of UPDATE you get both the row data before and after the operation, so its easy to diff and see what’s going on

Capture Replicated Changes with a Trigger Function

The trigger that will prepare and insert rows in the event_inbox

 1-- on the subscriber
 2
 3CREATE OR REPLACE FUNCTION log_change()
 4RETURNS TRIGGER AS $$
 5BEGIN
 6    INSERT INTO event_inbox (
 7        table_name,
 8        operation,
 9        old_data,
10        new_data
11    )
12    VALUES (
13        TG_TABLE_NAME,
14        TG_OP,
15        CASE WHEN TG_OP IN ('UPDATE', 'DELETE')
16             THEN to_jsonb(OLD)
17        END,
18        CASE WHEN TG_OP IN ('INSERT', 'UPDATE')
19             THEN to_jsonb(NEW)
20        END
21    );
22
23    RETURN COALESCE(NEW, OLD);
24END;
25$$ LANGUAGE plpgsql;

for convinience, ive added here the table_name (which in our case could be one of users, groups, groups_users)

operation is either INSERT, UPDATE, or DELETE

new & old -> {insert explanation here}

so basically each row contains everything the app needs to properly react to changes

also this is just an example, feel free to include whatever other columns/data you may feel convinient (like maybe a timestamp? idk)

Set Up the Trigger on the Tables

The function doesn’t run by itself. We need to attach it to each replicated table we want to monitor. Here, we’ll capture changes to users, groups, and groups_users:

 1-- on the subscriber
 2
 3CREATE TRIGGER users_events
 4    AFTER INSERT OR UPDATE OR DELETE ON users
 5    FOR EACH ROW
 6    EXECUTE FUNCTION log_change();
 7
 8CREATE TRIGGER groups_events
 9    AFTER INSERT OR UPDATE OR DELETE ON groups
10    FOR EACH ROW
11    EXECUTE FUNCTION log_change();
12
13CREATE TRIGGER groups_users_events
14    AFTER INSERT OR UPDATE OR DELETE ON groups_users
15    FOR EACH ROW
16    EXECUTE FUNCTION log_change();

and then set the triggers to always enable:

1ALTER TABLE users
2    ENABLE ALWAYS TRIGGER users_events;
3
4ALTER TABLE groups
5    ENABLE ALWAYS TRIGGER groups_events;
6
7ALTER TABLE groups_users
8    ENABLE ALWAYS TRIGGER groups_users_events;

This is important because by default, PostgreSQL skips regular triggers for changes coming through logical replication. ENABLE ALWAYS tells PostgreSQL to run our trigger for those replicated changes too. Without it, the event_inbox would never see the changes coming from the publisher.

One thing to keep in mind: if the subscription performs an initial table synchronization, the trigger will also see those initial INSERTs. So setting this up on an already-populated table can fill the inbox with events for the existing data, not just future changes.

Check if everything is wired up correctly

Now let’s make sure the whole pipeline actually works end-to-end.

Insert a new user on the publisher:

1-- on the publisher
2
3INSERT INTO users (name, email, enabled)
4VALUES ('Pookie', '[email protected]', true);

Check if it was synced to the subscriber:

 1-- on the subscriber
 2
 3SELECT id, name, email, enabled
 4FROM users
 5WHERE name = 'Pookie';
 6
 7 id |  name  |           email           | enabled
 8----+--------+---------------------------+---------
 9  6 | Pookie | pookie@featurefactory.lol | t
10(1 row)

Good, the replication is working. Now check if the trigger on the subscriber caught it:

1-- on the subscriber
2
3SELECT * FROM event_inbox;
4
5 id | table_name | operation |            old_data            |                         new_data
6----+------------+-----------+--------------------------------+-----------------------------------------------------------
7  1 | users      | INSERT    |                                | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"}
8(1 row)

One row appeared in event_inbox for our INSERT. The trigger fired automatically after the apply worker wrote the row.

Now try an UPDATE on the publisher:

1-- on the publisher
2
3UPDATE users
4SET email = '[email protected]'
5WHERE name = 'Pookie';

Check event_inbox on the subscriber:

1-- on the subscriber
2
3SELECT * FROM event_inbox;
4
5 id | table_name | operation |            old_data            |                         new_data
6----+------------+-----------+--------------------------------+-----------------------------------------------------------
7  1 | users      | INSERT    |                                | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"}
8  2 | users      | UPDATE    | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"} | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"}
9(2 rows)

Now we have two rows: one for the INSERT and one for the UPDATE. Notice old_data has the previous values and new_data has the updated ones.

And a DELETE:

1-- on the publisher
2
3DELETE FROM users
4WHERE name = 'Pookie';

 1-- on the subscriber
 2
 3SELECT * FROM event_inbox;
 4
 5 id | table_name | operation |            old_data            |                         new_data
 6----+------------+-----------+--------------------------------+-----------------------------------------------------------
 7  1 | users      | INSERT    |                                | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"}
 8  2 | users      | UPDATE    | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"} | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"}
 9  3 | users      | DELETE    | {"id": 6, "name": "Pookie", "email": "[email protected]", "enabled": true, "created_at": "2026-08-17T16:00:00.123456+00:00"} |
10(3 rows)

For the DELETE, new_data is NULL (nothing to show) and old_data captures the row as it was before deletion.

The full pipeline is working: replication syncs the change, the trigger captures it, and the event lands in event_inbox ready for downstream processing.

Actually doing the work

So right now we have an inbox table that keeps filling up, but nothing is reading from it.

That’s where the app worker comes in. Basically, a script picks up rows from the inbox, processes them, and deletes them once it’s done so they don’t get picked up again. You could run it as a cronjob every minute, depending on your latency requirements.

A worker run looks something like this:

 1# pseudo-code, don't try to run it lol
 2
 3BEGIN TRANSACTION
 4
 5rows = db.query("""
 6    SELECT *
 7    FROM event_inbox
 8    ORDER BY id
 9    LIMIT 10
10    FOR UPDATE SKIP LOCKED
11""")
12
13for row in rows:
14    handle(row)
15
16    db.execute(
17        "DELETE FROM event_inbox WHERE id = %s",
18        row["id"],
19    )
20
21COMMIT

FOR UPDATE SKIP LOCKED is useful here because you might have multiple worker runs happening at the same time. FOR UPDATE locks the rows being processed, while SKIP LOCKED makes other workers skip rows that are already claimed instead of waiting for them.

That’s the general gist, but feel free to adapt it to your own needs.

What Happens When a Worker Fails?

If the worker dies before committing, PostgreSQL rolls the whole thing back, and the FOR UPDATE locks are released.

The rows the worker claimed are therefore still in the inbox, ready to be picked up by the next worker. The system is cool with retries: no separate retry mechanism is needed for this (basic) case.

only thing, external side effects requires some consideration. If the worker sends an email and then crashes before committing, the inbox row remains and the email may be sent again on retry. I guess that’s the tradeoff.

So the basic flow is:

crash -> rollback -> locks released -> row remains -> retry 🙏

PostgreSQL nicely handles the transactional part for us, we just need to make processing safe to repeat. When processing can happen more than once, handlers should be idempotent.