PostgreSQL logical replication as a cache feed

PostgreSQL support in redfly comes after SQL Server, which is available now, and after MongoDB, which is next. We are saying that plainly up front so nobody reads this as a description of something they can switch on today. It describes how the engine reports changes and how we use that, because the approach is decided even though it is later in the queue.

Every write to PostgreSQL is first recorded in the write-ahead log, a sequential file the database appends to before it touches the tables. That log exists so the database can recover after a crash. Logical replication reuses it for a second purpose: it decodes the log into a stream of row-level changes, inserts, updates and deletes with the values of each row, and hands that stream to anyone who subscribes. redfly subscribes.

Publications: what the database agrees to share

A publication is a named list of tables, defined inside the database, whose changes are to be published. It can cover every table or a chosen few, and it can be limited to certain kinds of change, for example inserts and updates but not deletes. Creating one changes nothing about the tables; it is a declaration of what is available to subscribers.

For a cache feed the publication matches the set of tables redfly serves. Adding a table later means adding it to the publication and letting redfly backfill it, which is work someone does deliberately rather than a switch that flips itself. Tables outside the publication are simply never seen, which is a clean way to keep sensitive data out of the sync path entirely.

One detail matters for correctness. Each published table needs a way to identify a row, normally its primary key, so that an update or delete in the stream can be matched to the right row in Redis. A table without one needs extra setup before its updates and deletes can travel through the stream; we treat that as something to resolve before sync starts, not to work around while running.

Replication slots: the database's promise to keep the log

A replication slot is the other half. It is a named bookmark on the server that marks how far a particular subscriber has read in the write-ahead log. Its purpose is to guarantee that the database will not discard any log the subscriber has not yet consumed, so that a subscriber that disconnects, whether for a restart or a network fault, can reconnect and continue from where it left off without missing a change.

That guarantee is what a cache feed needs. When the sync process comes back after an interruption, it resumes from the slot's position and applies the changes it missed, in order. Nothing has to be rescanned and Redis does not have to be rebuilt. The database has, in effect, agreed to wait for its reader.

A slot is a promise, and promises cost disk.

The operational caution every administrator should know

The promise cuts both ways. A slot holds write-ahead log on disk until its subscriber has consumed it. If the subscriber stops reading and the slot stays in place, the log keeps accumulating. On a busy database that can fill the disk, and a PostgreSQL server that cannot write its log stops accepting writes. This is a common way logical replication hurts a production system, and it is entirely avoidable.

The rules of the road are straightforward:

• Monitor the lag on every slot, measured as how much log it is holding back, and alert on growth.
• Drop a slot the moment its subscriber is permanently retired; an orphaned slot never releases anything.
• Set a ceiling on how much log a slot may retain, which recent PostgreSQL versions support, and accept that a subscriber which falls behind that ceiling will need a fresh backfill.
• Run the subscriber as a supervised service, so a crash is restarted rather than discovered at the disk-full alarm.

Our design treats the slot as something we own and answer for. A slot that has been invalidated by the retention ceiling is a recovery case for the sync orchestrator, whose job is backfill, drift detection and recovery: the affected tables are rebuilt from the current data rather than by asking someone to intervene by hand. The failure mode still exists; the point is that it is caught early and recovered in a known way.

Streamed, not polled

Unlike SQL Server, where redfly reads change tracking on a short interval, PostgreSQL pushes each change to us as it is saved. There is no interval to tune and no question to ask; the stream arrives. The lag between a write and Redis reflecting it is the time to decode the change and apply it.

The stream carries the full row values for inserts and updates, and the key for deletes, so the stream itself holds what is needed to keep the copy in step. Schema changes are the case to plan for: they do not travel through the stream, so a column added to a published table is something the sync process must notice and handle, and it is one of the things the orchestrator's drift detection exists to catch.

redfly fits here as the subscriber that owns the publication, minds the slot, and keeps Redis in step from the stream, once PostgreSQL support arrives after SQL Server and MongoDB.