Skip to main content

Emit structured Postgres data change events with wal2json

A common thing I see in an enterprise system is that when an end-user does some action, say add a user, the underlying web of subsystems adds the user to multiple databases in separate transactions. Each of these transactions may happen in varying order and, even worse, can fail, leaving the system in an inconsistent state.

A better way could be to write the user data to some main database and then other subsystems like search indexes, pull/push the data to other interested parties, thus eliminating the need for multiple end-user originating boundary transactions.

That's the theory part; how about a technical solution.

The idea of this post came from the koodia pinnan alla podcast about event-driven systems and CDC. One of the discussion topics in the show is emitting events from Postgres transaction logs. 

I built an utterly simple change emitter and reader using Postgres with the wal2json transaction decoding plugin and a custom go event parser. I'll stick to the boring "add user" example, so there is a main system with a database table "users".

In a nutshell, the Postgres wal2json plugin is used to decode a given database's replication events to JSON. The replication events are created from the database's WAL log, which implies we are as near as possible to the source of truth.

I pipe the change events of the "main" database to a file.

pg_recvlogical -d main --slot user-slot --start -o pretty-print=1 -f - > events.log 

The output looks like this:


The simple go program reads and filters the events and just prints out what happened in a human-readable format.

The DB changes above and the go event parser output.


Does building this make any sense? 

Yes, for understanding how to reliably emit data change events from near the source of truth. Your enterprise solution will need something more sophisticated though.

Labels:

Comments

Post a Comment

Popular posts from this blog

I'm not a passionate developer

A family friend of mine is an airlane pilot. A dream job for most, right? As a child, I certainly thought so. Now that I can have grown-up talks with him, I have discovered a more accurate description of his profession. He says that the truth about the job is that it is boring. To me, that is not that surprising. Airplanes are cool and all, but when you are in the middle of the Atlantic sitting next to the colleague you have been talking to past five years, how stimulating can that be? When he says the job is boring, it is not a bad kind of boring. It is a very specific boring. The "boring" you would want as a passenger. Uneventful.  Yet, he loves his job. According to him, an experienced pilot is most pleased when each and every tiny thing in the flight plan - goes according to plan. Passengers in the cabin of an expert pilot sit in the comfort of not even noticing who is flying. As someone employed in a field where being boring is not exactly in high demand, this sounds pro...
Post a Comment
Read more

Bird is causing high CPU on my macOS

There is no lack of people complaining about MacOS Tahoe, mostly about rounded corners and inconsistent design decisions. I have not paid that much attentention to that, but there is one mac bug i have paid attention to. It hasn't been a visual or ux but rather few system processes pegging the CPU. trustd , alongside with ecosystemd and ecosystemanalyticsd all reported high CPU usage. I can't exactly recall when this started, it might have predated my Tahoe upgrade but anyway, the trio of processes all had high CPU usage. Sure, it may have been the virtual efficiency cores and whether it affeced battery life or slowed down other processes i don't know to be hon...
Post a Comment
Read more

Ousterhout's law

Back in the day, everyone was using Winamp. It is a music player with the user interface of a mixing console. The bloody thing had an equalizer on the front page! As an amateur music producer, I know that EQ is a powerful tool but making changes that sound good is difficult, to say the least. Mixing engineers spend considerable effort with the artist to balance the frequency ranges to arrive at the desired musical outcome. I bet the 15-year-old me butchered a lot of songs with the thing. Now we are using Spotify, a player with basically a search bar and a play button. I ran into something called Ousterhout's Law on the  Operating Systems: Three Easy Pieces book . Here is a quote from the book TIP: AVOID VOO-DOO CONSTANTS (OUSTERHOUT’S LAW) Avoiding voo-doo constants is a good idea whenever possible. Unfortunately, as in the example above, it is often difficult. One could try to make the system learn a good value, but that too is not straightforward. The frequent result: a configura...
Post a Comment
Read more