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.

Comments

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...

How my work has changed in the past few years

When I started at my current job 3ish years ago, we had nothing but a few PowerPoint slides and a Figma prototype. I recall talking with one of the founders, whom I had worked with some 10 years ago, and pointing out that the very next step after PowerPoint and Figma was still the same as it had always been: translating those slides and mockups into CRUDs, forms, and other basic building blocks. Sure, all the new fancy tools and frameworks (we had jQuery then) help, but I still had to tinker with very basic primitives like tables and forms. I could not just say, "here is a table, here is a form." Well, yes, technically I could, but I still needed to write a lot of code to make a working form or table. This was around the time of GPT-3, Copilot beta, and very early Cursor. I did attempt to use them, but they could only perform very basic tasks and were pretty much useless for frontend work. We, being one of the first-wave "ChatGPT wrapper" startups has naturally pus...

Building a OTel sink that follows GenAI semantic conventions

I've been working on an OTel sink recently. To be more specific, it concerns receiving telemetry data from LLM interactions. Fortunately, there is a spec for all of this called GenAI Semantic Conventions . That is what I based my work on, so I can make assumptions about key attributes to highlight in the sink's UI, which is actually a CLI in my case. I dogfooded my handrolled sink using Pydantic AI (my favorite LLM framework), and all good, it follows the spec well. Key attributes flow in as specified. Pydantic AI also includes some of its own attributes, but that is expected. However, pick something like Claude Code, (via anthropic agent sdk), the reality is a bit different. It does not follow the spec at all. It has its own custom attribute layout . Same direction, different flavor with LangChain. The Python source has zero opentelemetry imports anywhere. None. The ls_* namespace ( ls_provider , ls_model_name , ls_temperature ) is its own thing, with LangSmith as the on...

Koodihommia

Search This Blog