The database has a memory

Most developers use PostgreSQL every day without knowing what happens beneath an INSERT.

I wanted to understand it properly, so I built pgstream: a tool that reads database changes in real time from PostgreSQL’s internal log.

The project changed how I think about PostgreSQL. It is not just a place where rows live. It is a system constantly recording, ordering, protecting, and replaying reality.

What is the WAL?

Whenever you insert, update, or delete a row, PostgreSQL does not immediately change its actual data files.

First, it records the change in the Write-Ahead Log—the WAL.

That order is the reason PostgreSQL can recover safely if a server crashes halfway through a write. The database has already written down what it intended to do before it changes the underlying files.

The best mental model is a journal:

  1. PostgreSQL records the change.
  2. PostgreSQL applies the change to its data files.
  3. If something fails, PostgreSQL can replay the journal and recover.

I had read that explanation in documentation before. Building against it made the idea click: the WAL is PostgreSQL’s memory of every committed change.

WAL records are not human-readable

The WAL is stored as raw binary records. You cannot open a file and simply read:

INSERT users (...)
UPDATE orders (...)
DELETE sessions (...)

PostgreSQL provides logical replication for this. Its built-in pgoutput plugin turns the raw WAL stream into meaningful protocol messages such as:

  • INSERT
  • UPDATE
  • DELETE
  • relation metadata
  • keepalive heartbeats

That is the layer pgstream reads.

Replication slots: the bookmark in the stream

To consume logical changes, a program creates a replication slot.

A replication slot acts like a durable bookmark. PostgreSQL remembers the last log position your consumer has confirmed, then keeps the required WAL records available until the consumer has caught up.

This matters because a reader can stop and restart without silently losing database changes.

That is a powerful idea: the database is not just sending events once and hoping you receive them. It remembers where you are.

Building pgstream

I built pgstream in Go using pglogrepl, which handles the low-level PostgreSQL replication protocol.

The project has four main responsibilities.

1. Connector

The connector opens a replication connection, creates or attaches to a replication slot, and starts logical replication from the correct log sequence number.

2. Decoder

The decoder was the hardest part.

PostgreSQL sends several message types. A reliable reader must understand table-relation metadata, inserts, updates, deletes, and keepalive messages.

The difficult part is that row events do not arrive with a friendly object like this:

{
  table: "users",
  operation: "INSERT",
  values: { id: 1, name: "Mujib" }
}

They arrive as protocol messages and raw values. The decoder has to interpret them correctly.

3. Relation cache

Before PostgreSQL sends a row-level event, it sends a Relation message describing the table: its name, columns, and types.

That metadata must be stored and reused later.

Without it, you may receive raw row values but have no reliable way to know which value belongs to which column.

This was the key implementation detail:

A row event only becomes meaningful when it is combined with the relation metadata PostgreSQL sent earlier.

4. Event handler

Once the decoder has turned the stream into a structured event, the handler can display it cleanly in the terminal or send it somewhere else.

That is where an internal database record becomes something useful:

INSERT  users
id: 42
email: mujib@example.com
created_at: 2026-05-09T10:24:00Z

The part that surprised me

The speed.

Run an INSERT in pgAdmin, and the change appears in the terminal almost immediately. Watching it happen makes replication feel less like an abstract infrastructure concept and more like a living system.

This is the same category of mechanism that powers:

  • real-time dashboards
  • database change-data-capture pipelines
  • search-index synchronization
  • audit streams
  • cross-region replication

What I learned

Before building this, I understood PostgreSQL as a database.

After building it, I started understanding PostgreSQL as a system.

WAL, logical decoding, replication slots, relation metadata, and LSNs are not random internals. They are the machinery that lets PostgreSQL stay reliable under failure while still moving changes through the system.

Go was also new territory for me. Coming from JavaScript and C, goroutines and channels felt different immediately. They made it natural to think about streaming systems as concurrent pipelines instead of one long blocking process.

Try it yourself

The project is open source:

Inspect pgstream on GitHub

If you want to see what is happening beneath your PostgreSQL database, clone it, run it, make a change, and watch the system remember it.