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:

	{
	"change": [
	{
	"kind": "update",
	"schema": "public",
	"table": "users",
	"columnnames": ["id", "name"],
	"columntypes": ["bigint", "text"],
	"columnvalues": [14, "emily"],
	"oldkeys": {
	"keynames": ["id"],
	"keytypes": ["bigint"],
	"keyvalues": [14]
	}
	}
	]
	}
	{
	"change": [
	{
	"kind": "insert",
	"schema": "public",
	"table": "users",
	"columnnames": ["id", "name"],
	"columntypes": ["bigint", "text"],
	"columnvalues": [16, "emma"]
	}
	]
	}
	{
	"change": [
	{
	"kind": "update",
	"schema": "public",
	"table": "users",
	"columnnames": ["id", "name"],
	"columntypes": ["bigint", "text"],
	"columnvalues": [16, "emily"],
	"oldkeys": {
	"keynames": ["id"],
	"keytypes": ["bigint"],
	"keyvalues": [16]
	}
	}
	]
	}

view raw events.log.json hosted with ❤ by GitHub

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.