Postgres extensions in Zig

There is a tool for this:

- Github:
[](https://github.com/xataio/pgzx)

- Youtube:
[](https://www.youtube.com/watch?v=T0RbtS1RDgk&t=1s)

I wanted to experiment **Change Data Capture** on Postgres. Why? for analytics for example, or to replicate Postgres into a SQLite database, as a cache, so that all the reads go to SQLite instead of Postgres, and any app/container can embed a SQLite file.

Here, I am interested by real time streams.

Postgres doc:

[write-ahead log](https://www.postgresql.org/docs/current/wal.html)
```txt
47.2.1. Logical Decoding
Logical decoding is the process of extracting all persistent changes
to a database's tables into a coherent, easy to understand format
which can be interpreted without detailed knowledge of the database's internal state.

In PostgreSQL, logical decoding is implemented by decoding the contents of the [write-ahead log],
which describe changes on a storage level, into an application-specific form such as a stream of tuples
or SQL statements.
```

Other technics?

- LISTEN/NOTIFY, `pg_notify()` is fire and forget. If you don't sit in front, it is lost. Furthermore, every message goes through it, bottlenecking your database.
- triggers: this is using queries to the database, thus puts again lots of pressure on it
- Then, Postgres cannot send (no kind of SSE) messages for safety reasons.
- thus, the logs!

But you can enable a WAL journal and a listener on the log tail via Logical replication (this has to be set on the database).
This means that Postgres replicates all the __intentions__ of your database (the _event source_ in other words), not the results itself (the Physical Replication).

So you want to receive streams of this, and parse this (this is the fun part).

The problem is WAL bloat: you **must** consume the messages, otherwise the journal will explode and crash Postgres.

Explanation in the video:
[](https://www.youtube.com/watch?v=pxBfmG3InkQ&t=13s)

Elixir via `Postgrex` can connect and in theory ingest the WAL too (), but you have to be sure that this process is fast enough and you get all the events....

A **Replication Slot** is a persistent object on the PostgreSQL server that manages the server's tracking of a client's consumption of the Write-Ahead Log (WAL). It solves the crucial problem of data loss and log retention for replication clients. I used the builtin plugin `pgoutput`.

```sql
SELECT * FROM pg_create_logical_replication_slot(
'my_zig_slot', -- Slot name
'pgoutput' -- Output plugin name
);
```

I experimented an "external bridge". You get streams from the Postgres WAL journal (logical replication) with `libpq`, and push the message MessagePack encoded ().

The bridge receives, sends a binary to a NATS server, and the bridge sends an "ack" - more precisely a `Standby Status Update` - to tell Postgres to prune the message (via a `Last Sequence Number`).

This POC works:

- the bridge is running nad created a NATS stream.
- from Elixir, I hammer Postgrs with 10_000 INSERT, UPDATE one of tow, and DELETE one out of five;
- the bridge gets `pgoutput` streams
- the bridge forwards - via the NATS client- the selected ones (eg INSERT, UPDATE, DELETE) on a named stream to the NATS server (via TCP).
- the bridge sends back to Postgres a `Standby Status Update` with the LSN for Postgres to prune the message
- Then Elixir has a NATS client in mode PULL that subscribed to the named stream.
- Elixir gets the message, decodes it with (I encoded in MessagePack on the Zig side, but this is optional),
- and send an `:ack` back to the NATS server to prune this message.

I print the Zig logs to see the WAL journal LSN advanced and length.

**Q: Why a message broker?**
Because you want to ensure that WAL messages are all consumed, and you want to send them somewhere that can buffer and distribute it for ingestion. This is an ETL like pipeline.
This is how OpenTelemetry data via Promtail/Alloy is sent to Grafana, or how Kafka via Debezium works. This bridge is doing what Alloy or Debezium does, and NATS is Grafana or Kafka, the message broker.

Of course, this bridge is doing a tiny part of this, and only works with Postgres. It can even reconnect if the link PG/bridge is down.

Now we are assured that the WAL streams are consumed and pruned, no bloat.
Then the messages are buffered by the broker and the consumer in PULL mode has time to do his stuff, so no messages are lost.

So NATS with JetStream enabled is good because it is a ~popular~, lightweight, battle tested solution: the "bridge" (or extension, see later) streams the WAL, and pushes on a NATS/JetStream stream. Then in NATS, you can define a TTL or max_messages, so NATS itself will not explode.

Lastly, by using a unique identifier in the messages sent by the bridge to the NATS server, you are ensured of _idempotency_, meaning the consumer will get _at most once_ the message.

Now, the bridge is a standalone executable; it vendors the compiled `libpq` and `nats.c` static library.

So our mini ETL pipeline is:

`[Postgres server] --> [BRIDGE] --> [NATS SERVER]--> [Elixir consumer]`

> Elixir has a NATS/JetStream client () and in particular features Broadway.

Using a high level language is **much** easier . Your only fear is race conditions.

The good part is that the bridge is tiny (<10MB) and the NATS server too (<20MB), and memory consumption is very low.

But, you can build a __Postgres extension__ in Zig! This makes more sense.

However, this does seems pretty...tedious...!!

> Of course, this already exists, but in Rust: , so nothing new under the sun, as expected.

> The industry standard is [Debezium](https://debezium.io/) because it takes care of schema shifting (what if I change a column name, or add one? etc..)

[](https://www.youtube.com/watch?v=kYZdCdsb794&t=267s)

### What is Logical Replication?

An enlightening video:

[](https://www.youtube.com/watch?v=OvSzLjkMmQo&t=1435s)