🔗 Listen to database changes through the Postgres WAL

Welcome back, friends!

Did you hear the news about the inventor of the Knock-Knock joke?
He got a No-bell prize.

Alright, this post will be a bit longer since we’ll dive deeply into the Postgres WAL and will explore how to listen for database changes through it.

The Problem

Let’s say you want to react to changes in your Postgres database, for example to send out realtime notifications or to track the changes in an audit log. You consult Google (or let’s be honest: your favorite LLM) and learn about NOTIFY/pg_notify and triggers.

NOTIFY/pg_notify

You decide to try pg_notify on a small, not so active database table. It works! Great.

You roll it out to a larger, much more active database table. It works, but suddenly your query times explode! Instead of doing 1 million transactions per second* you can now do only 3 (*These numbers were exaggerated for dramatic effect)

So, what happened?

The problem with Postgres’ NOTIFY is that all notifications go through a single queue!

Even if you have 20 database connections making 20 transactions in parallel, all of them need to wait for their turn to lock the notification queue, add their notification, and unlock the queue again. This creates a bottleneck especially in high-throughput databases.

pg_notify also has other limitations:

• Its notification size is limited to 8000 bytes. This might not be enough to send all the data in the notification and you’ll need to query the database for the full dataset which adds additional load to your database.
• It does not wait for an acknowledgment of the listener and does not retry messages which means if your listener is dead or can’t receive the message, the notification is lost.
• If you call pg_notify or NOTIFY inside a trigger, it will get called 100,000 times and send out 100,000 notifications if you change 100,000 rows in a single transaction which from a performance perspective is … not ideal.

You realize that pg_notify doesn’t work for you. But there must be another way, right?

There is and it’s called the Postgres Write-Ahead Log (WAL).