In this lesson, you will learn how PostgreSQL transforms physical Write-Ahead Logs (WAL) into a stream of logical data changes that external applications can consume.
From Bytes to Logic
So far, we have discussed how PostgreSQL uses WAL to ensure durability. These logs are physical: they record low-level changes to data pages on disk. However, if an external system needs to know that a specific row in the users table was updated, reading raw page modifications is nearly impossible.
Logical decoding bridges this gap. It is a mechanism that reads the WAL, filters out irrelevant physical noise (like page splits or index maintenance), and reconstructs the high-level SQL operations—INSERT, UPDATE, or DELETE—that occurred within a transaction.
The Role of Logical Replication Slots
The "transport" for this data is the Logical Replication Slot. When you create a slot, you are essentially telling the database: "Keep track of where I am in the WAL stream, and do not recycle any WAL files until I have processed them."
The decoding process works in three distinct phases inside the server:
- The Reorder Buffer: Because transactions can run concurrently and are written to the WAL interleaved, the system buffers the changes in memory. It holds them until the transaction commits, ensuring that only successful changes are streamed.
- Output Plugins: PostgreSQL uses a pluggable architecture. A plugin (like
pgoutput) takes the decoded row changes and formats them into a protocol that the client can understand. - Snapshot Consistency: The system creates a point-in-time snapshot of the database state so that the logical stream reflects the data exactly as it existed at the time of the change.
A Concrete Example
If you perform an UPDATE on a row, the WAL record might simply show a change to a byte range in a heap file. The logical decoding process looks at the catalog information (which we discussed in Lesson 18) to identify the table and columns involved. It translates that byte change into:
UPDATE public.users SET email = 'new@example.com' WHERE id = 101;
This reconstructed SQL command is then pushed out to the replication slot, ready for an external consumer to read.
Key Takeaway: Logical decoding transforms raw, physical WAL modifications into human-readable row-level events by using replication slots to track progress and output plugins to serialize the data for external consumption.