Making High-Ingest Search Work on Postgres Replicas

Fast writes are easy to ask for and much harder to deliver once replication enters the picture. That became obvious while building pg_search, a Postgres extension aimed at search and analytics workloads where data arrives continuously and needs to become queryable right away. Think live dashboards, ecommerce catalogs, or recommendation pipelines: these systems do not get long pauses to batch indexing work later. They need steady ingest and near-immediate searchability. 🚀

Standard Postgres full-text indexing usually leans on B-tree or GIN structures. Those options are solid for read performance, but they are not especially friendly to heavy, sustained write traffic. To push ingest throughput higher, the implementation moved to a data structure that is famously better for writes: the Log-Structured Merge tree, or LSM tree.

That decision solved one problem and created another. Write throughput improved, but physical replication stopped being safe. Postgres replicas could no longer be trusted to stay logically correct under the new indexing behavior. The root issue was that normal WAL shipping handles physical correctness well, yet that alone was not enough to make a sophisticated structure like an LSM tree safe on replicas. ⚠️

This article walks through the key ideas behind that failure and the fix:

• what an LSM tree is,

• what “replication-safe” actually means,

• how WAL shipping protects structural validity,

• why atomic logging was required,

• and how hot_standby_feedback helped preserve logical correctness.

  

An LSM tree is a write-focused structure used in systems such as RocksDB and Cassandra. Its basic strategy is simple: avoid expensive random disk writes by turning them into sequential work.

Incoming updates first land in an in-memory buffer known as a memtable, which is cheap to modify. After that buffer fills, it is written to disk as a sorted and immutable segment file, often called an SSTable. Over time, these disk segments are arranged into levels based on size. New data enters at the top, and background processes gradually merge and rewrite smaller segments into larger ones through compaction. 📌

Compaction is what keeps the structure practical. It merges overlapping data, removes superseded entries, and pushes information downward into larger, more stable layers. That pattern is exactly why LSM trees can absorb high write volume far better than index structures that must constantly update data in place.

For a replicated datastore to be trustworthy, it has to satisfy two separate conditions across replicas: physical consistency and logical consistency.

Physical consistency means the replica’s bytes describe a valid on-disk state. Pages and blocks are well-formed, and every copied structure corresponds to a state that genuinely existed on the primary at some moment.

Logical consistency goes further. It means the replica reflects a coherent database view, one that some transaction on the primary could actually have observed. ✅

Those are not the same thing. A replica can be structurally valid while still showing an impossible intermediate view of the data. One way to picture it is to imagine copying a book. If every page is duplicated exactly, that is physical consistency. But if the copy happens halfway through an edit, the result may contain incomplete ideas or mismatched content. Logical consistency is what you get when the copied version still makes sense to a reader.

Postgres also supports both physical replication and logical replication, and so does ParadeDB. The focus here is strictly on physical replication.

In a primary-standby setup, Postgres uses the Write-Ahead Log (WAL) to record low-level storage changes before applying them on the primary. Those binary changes are then streamed to the standby and replayed in order. That is the mechanism behind hot standby replication and near-real-time synchronization.

This model is excellent at preserving physical correctness. As the primary modifies storage blocks, the standby replays the same block-level edits in sequence. The result is a replica whose on-disk contents remain valid and synchronized at the storage level. 🛠️

But that guarantee has a boundary: WAL shipping ensures the replica replays valid block edits, not necessarily that a larger multi-block operation appears atomically as one coherent logical action.

Atomicity matters because Postgres locks on the primary are not replayed on replicas. Reproducing every lock there would require tight timing coordination, hurt performance, and reduce the usefulness of standby nodes for reads.

Instead, WAL replay operates incrementally. Postgres takes a lock on a buffer, applies a change, releases the lock, and moves on. That approach works well until a data structure spans many buffers. Then the risk appears: one part of the structure may be replayed before another, exposing an intermediate state that is structurally valid block-by-block but unsafe as a whole. ⚠️

pg_search uses an unrolled linked list of Postgres buffers. Each node stores the read validity of a batch of LSM segments. On the primary, hand-over-hand locking, also called lock coupling, keeps that list physically sound while it is being modified. After each buffer update, the corresponding WAL record becomes visible atomically on the replica.

The harder case is when several list entries must change together. A newly compacted segment may replace multiple older segments at once. On the primary alone, a coarse global lock over the list could protect that transition. But the standby cannot rely on that global lock because replica replay does not coordinate across multiple nodes and multiple buffers in the same way.

The fix was to avoid depending on coarse locking for correctness. For multi-node changes, pg_search uses a Copy-on-Write (CoW) clone of the list and then atomically swaps the head pointer. That design keeps the visible transition atomic from the replica’s point of view. 📌 In broader terms, atomic operations reduce danger because they remove dependence on locks the standby cannot observe.

Making structure updates atomic is necessary, but it still is not sufficient. Even if individual WAL actions and index operations are safe at the block level, VACUUM can still disrupt longer-running work spread across multiple WAL records.

Postgres relies on MVCC so concurrent writers do not block each other. Instead of rewriting rows in place, updates and deletes create multiple tuple versions. Old versions become “dead,” but they are not immediately removed. They stay on disk until a later VACUUM cleans them up.

That cleanup is also logged in WAL, shipped to standbys, and replayed there. The subtle problem is that tuple visibility is local to each server, while VACUUM replay is global. A standby may still be reading a tuple version it considers valid across a series of WAL-applied operations, while the primary decides that version is old enough to remove. If the standby replays the cleanup before the read is done, the query can fail. 🚨

So a replica may remain physically fine while losing logical coherence for an in-progress read.

This class of issue is not exclusive to LSM trees. Any Postgres deployment with read replicas and heavy writes can hit similar conflicts, even with B-tree indexes. If a VACUUM runs on the primary while a long-running query executes on a standby, Postgres may abort that read. In many ordinary systems, though, VACUUM runs only every few hours, so the conflict window is limited and often acceptable.

LSM trees change that operational profile completely. Compaction is not occasional maintenance; it is a constant part of the system. In a workload with high ingest throughput, compaction may run many times per minute, or even many times per second. 📈

That sharply raises the number of opportunities for conflict.

Compaction, like VACUUM, rewrites data on the primary and eventually needs to know when old data is no longer needed by active reads. For pg_search, that means the system must determine when old segments are safe to delete without breaking standby queries still depending on them.

The practical answer came from an optional Postgres setting: hot_standby_feedback.

When enabled, hot_standby_feedback allows the standby to report back to the primary about what data still needs to remain visible from the replica’s perspective. That lowers the chance that tuples are removed too early and gives pg_search enough information to decide when old segments can safely disappear. ✅

To understand why, it helps to look at tuple version metadata. Every Postgres tuple has two important fields: xmin and xmax.

• xmin records the transaction ID (XID) that created that tuple version.

• xmax records the XID that updated or deleted that version, marking it obsolete.

Because XIDs increase sequentially, xmin can be treated as a rough stand-in for creation time, and xmax as a stand-in for the moment the tuple was last replaced or removed. 🛠️

With hot_standby_feedback enabled, the replica periodically sends the smallest xmin still pinned by any active query. In other words, it tells the primary the oldest tuple version that standby reads may still need.

Armed with that signal, the primary can defer cleanup. If it sees that a standby query still depends on a tuple that would otherwise be eligible for removal, it can postpone VACUUM-related cleanup until that read finishes. The same principle lets pg_search hold off on deleting old LSM segments until they are no longer required by replica queries.

Even with hot_standby_feedback, standby nodes still depend on WAL replay receiving instructions that are safe to execute in the exact order and timing they arrive. That is the deeper challenge here: local optimizations on the primary do not automatically translate into globally safe behavior across replicas.

To make an LSM tree work correctly under Postgres physical replication, two things were required:

• atomic logging for physical safety of the structure itself,

• and hot_standby_feedback for logical safety during cleanup and compaction.

That tradeoff was worth making because it preserves very high search ingest performance without giving up correctness. 🚀 A write-optimized index can deliver the throughput needed for search-heavy applications, but only if its replication story is designed as carefully as its on-disk layout.

🔍 TL;DR Summary

• 🚀 LSM trees improved write throughput for pg_search, which was essential for real-time search and analytics workloads.

• 🔐 Physical consistency and logical consistency are different; a replica can be structurally valid while still exposing an impossible read state.

• ⚙️ WAL shipping preserves block-level correctness, but multi-buffer index updates need atomic design to stay safe on replicas.

• 🧹 VACUUM and LSM compaction can invalidate standby reads if cleanup happens before the replica is finished with older data.

• 📨 hot_standby_feedback lets standbys tell the primary which tuple versions are still needed, reducing premature cleanup.

• ✅ The final solution combined atomically logged LSM operations with hot_standby_feedback to keep replication safe.