Why we read the WAL instead of using triggers

The first prototype of Driftbase used triggers. Every table you wanted to sync got anAFTER INSERT OR UPDATE OR DELETE trigger that wrote a row into an outbox table, and a worker polled the outbox. It took a weekend to build and three months to give up on.

Triggers are part of your transaction

A trigger runs inside the writing transaction. Every insert into orders becomes two inserts, on your hottest table, holding locks for longer. One customer’s nightly batch job got 30% slower the day they installed us. That is not a tax you should pay for a search index.

Polling an outbox gets the order wrong

Outbox rows get IDs when they are written, not when they commit. A long transaction that started first can commit last, and a worker that reads “everything after ID 9,000” skips it forever. You can patch that with a time window and a re-scan, and now you have duplicates to reason about instead.

The WAL already has the answer

Postgres writes every committed change to its write-ahead log, in commit order, whether you ask it to or not. Logical decoding with the built-in pgoutput plugin turns that into a stream of row changes. Reading it costs one replication connection and adds nothing to the write path. Deletes, which triggers on partitioned tables and TRUNCATE love to lose, are in the stream like everything else.

What you have to get right instead

• Slot retention. An unread slot keeps WAL on disk. Driftbase alerts on slot size and, on Team and Scale, can drop and recreate a slot that crosses your limit.
• Replica identity. Updates and deletes need the old key. Tables without a primary key need REPLICA IDENTITY FULL, and we tell you which ones.
• Schema changes. DDL is not in the logical stream. We detect relation changes from the stream’s metadata messages and re-plan the pipeline.

If that sounds like more work than a trigger, it is, once. That’s the part we do for you. Try it free.