Listen to Database Changes Through the Postgres WAL • Hacker News Board

Re: Listen to Database Changes Through the Postgres WAL

#51

Post by stuxnet79 » Mon, Nov 17, 2025, 6:15 PM UTC

greener_grass wrote: post #48

This is how Debezium works. It is probably best to use that unless there is a strong reason against.

Concur on this. I always thought that Debezium only supported MySQL binlog since that is the world I operate in. I did not know that it was also an option for Postgres CDC via the WAL. Now that I know, I would recommend defaulting to it.

Even with Debezium I have run into myriad issues. It blows my mind that someone would want to roll their own logic to set up replicas. IMHO this post should be advertised more as a fun intellectual exercise.

Re: Listen to Database Changes Through the Postgres WAL

#52

Post by skunkworker » Mon, Nov 17, 2025, 10:11 PM UTC

After working with the Postgres WAL through logical replication in the last few months on a work project. My largest gripe is that some specific behaviors (like how a WAL receiver process should respond to a fast-shutdown on the database backend), aren't well documented outside of asking questions on the postgres discord.

Specifically: If the server requests a reply on a heartbeat, the status update should include the heartbeat's LSN on the next loop. But a standby status update includes the LSN values + 1.

I was able to get it working and properly disconnecting to a fast shutdown, but when you get into the internals of the logical WAL receiver loop, it can get nuanced.

And my largest compliment is that the Postgres discord is filled with some extremely knowledgeable and helpful people. I was able to figure out some really specific and nuanced behavior around the different status messages being sent to the primary server, thanks to the in-depth responses there.

Re: Listen to Database Changes Through the Postgres WAL

#53

Post by cryptonector » Mon, Nov 17, 2025, 11:59 PM UTC

There is also Debezium. I think there are other CDC (change data capture) systems built around PG logical replication, which is itself built around logical decoding of the WAL. It's a fairly obvious idea, though I wish:

- PG did logical logging natively instead of via WAL decoding

- PG logged commit records in the WAL so that any code replaying transactions from following the logical replication stream can isolate each transaction's changes and serialize everything correctly

Re: Listen to Database Changes Through the Postgres WAL

#54

Post by rickstanley » Tue, Nov 18, 2025, 2:15 AM UTC

JoelJacobson wrote: post #6

> 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. We're currently working hard on optimizing LISTEN/NOTIFY: htt…

We tried to use LISTEN/NOTIFY for notification purposes, or rather, a queue in which the order mattered (well, kind of, we were aiming for the best scenario where a customer would not perform a specific task in a matter of milliseconds, because we could afford this expectation), in a .NET application using Npgsql.

The listeners were scattered between replicas, so we took advantage of Advisory Locks (https://www.postgresql.org/docs/current/explicit-locking.htm...) by choosing a unique key (unique to the data being sent) and before performing any task on the JSON payload, that the notification would send, we would first check the lock and continue the routine.

The NOTIFY routine was triggered after an insert in an Outbox table, so we could replay it if it failed for some reason.

Unfortunately I don't remember the exact reason we didn't use it, but I was bit sceptical for 2 reasons:

- I had a feeling that I was use this feature in a wrong way; and

- I read both the article and comments from this HN entry: https://news.ycombinator.com/item?id=44490510, and my first point felt validated;

but to this day I'm still unsure. Anyway, since it was a complementary system, it didn't hurt to leave it out, we had another background job that would process the outbox table regardless, but I felt it could/would give something closer to "real time" in our system.