This post is part of Pattern Labs, our research and engineering series on the systems behind Pattern: the AI that evaluates cases, the infrastructure that runs it, and what it takes to make both hold up at mass tort scale. Previous installments looked at how models behave when asked to evaluate a case. This one goes a layer down, to the database infrastructure behind docket-wide reporting. Logical replication is the PostgreSQL feature that let us retire a set of hand-maintained scheduled jobs and keep a separate analytics database near-live instead. What follows is both a crash course and a set of guidelines for deploying it safely.
Pattern Data wouldn't have a particularly apt name if we had no data. Indeed, we have a lot of data. A single mass tort litigation can have hundreds of thousands of cases and each case can have hundreds of potential data points. We provide our clients (law firms) with meaningful insights into this mountain of data via both custom and self-serve reporting infrastructure. Perhaps unsurprisingly, we do not want the queries driving the reporting infrastructure hitting the app database server. This would cause undue and highly variable load on the server. This necessitates having at least two database servers: one for OLTP and one for OLAP. Having an analytics database server sounds great on paper, but it's useless without data. How do we efficiently propagate our application data to it? How do we ensure that data is near-live?
Prior to leveraging logical replication, we had a constellation of Airflow jobs that repeatedly shuttled data at varying periods from our app database's read replica to the analytics database. Building these jobs was somewhat onerous, as we had to eyeball DDL alignment, write our own watermarking again and again, fine-tune the times at which these jobs are run, etc. No single engineer could keep a complete picture of the precise data flow in their head. Jobs that unknowingly bit off more than they could chew might spiral into repeated failures or spike replication lag on the read replica, which could negatively ripple out to the quality and availability of reporting overall.
Postgres gives us two different ways to replicate a database: physical replication or logical replication.
Physical replication sends the write-ahead log (WAL) byte-for-byte to a standby. The standby is a block-level clone of the primary. It has the same tables, same indexes, etc. down to the on-disk layout. It's read-only, we get the whole database or nothing, and it has to run the same major version of Postgres. This is an appropriate tool for high availability and read replicas.
Logical replication works at a higher level. Instead of copying WAL verbatim, the publisher decodes WAL into a stream of logical row changes (this row was inserted, that row was updated, this other one was deleted) and the subscriber replays those changes as ordinary SQL. There is a bit more nuance but this mental model is close enough for government work. The unit of replication is the table rather than the whole database. Postgres calls the source a publication and the destination a subscription, connected by a replication slot and an output plugin (often pgoutput). Importantly, the subscriber database is not read-only.
That last distinction is what makes logical replication a good fit for us. Because the subscriber applies plain SQL, the analytics database is a fully independent, writable Postgres database that also keeps some tables in sync with the app. That provides some nice properties:
A physical read replica lacks all of those properties. It is a read-only mirror locked to the same version and has no room for an analytics-specific structure that makes the reporting queries fast. Our Airflow jobs were shuttling data out of our read replica to a mutable analytics database to give us those properties, but at the cost of considerable engineering elbow grease and maintenance burden. This made logical replication's value proposition particularly compelling to explore, as we could potentially shed the engineering cost we had been paying outright.
Logical replication isn't free, however. There are pitfalls that must be understood to effectively employ it:
UPDATE and DELETE can't be applied.None of these are dealbreakers but they are things to be aware of so we can design around them. In doing so, the payoff is a near-live analytics database that costs the app server almost nothing. It costs us engineers little, so long as we use it thoughtfully.
Let's make this concrete with a heavily simplified slice of our data model: a single document table. It's easy to follow along on one local Postgres instance. We'll use two databases, app (the publisher) and analytics (the subscriber), on the same server.
The publisher needs wal_level set to logical. Locally that can be passed as a config flag (-c wal_level=logical on the Docker container), or ALTER SYSTEM SET wal_level = logical followed by a restart. On RDS it's the rds.logical_replication = 1 parameter, which flips wal_level for us but requires a reboot since it's a static parameter.
On the publisher, we create the table and a publication including it:
\c app
create table document (
id bigint primary key,
case_id bigint not null,
file_name text not null,
page_count int not null
);
create publication pub_analytics for table document;There's a wrinkle when trying the next bit locally. By default, CREATE SUBSCRIPTION connects back to the publisher and asks it to create the replication slot. This hangs when the publisher and subscriber live on the same instance. The workaround is to pre-create the slot and tell the subscription to reuse it. Still on the app database (publisher):
-- Same-instance testing only. On separate instances (our real setup),
-- CREATE SUBSCRIPTION creates the slot for us and this step isn't needed.
select pg_create_logical_replication_slot('sub_analytics', 'pgoutput');Now to the subscriber. Since DDL doesn't replicate, we create the table by hand here too and with a schema the publisher's rows will fit into:
\c analytics
create table document (
id bigint primary key,
case_id bigint,
file_name text,
page_count int
);
create subscription sub_analytics
connection 'host=127.0.0.1 port=5432 dbname=app user=postgres password=password'
publication pub_analytics
with (create_slot = false, slot_name = 'sub_analytics', copy_data = true);The copy_data = true is the default behavior, but we include it for clarity. It means the subscriber does an initial COPY of everything already in document before it starts applying the live change stream. We insert a row on the publisher:
-- On app
insert into document values (1, 100, 'complaint.pdf', 12);Just a moment later it's on the subscriber too, with no application code involved:
-- On analytics
select * from document;
-- ┌────┬─────────┬───────────────┬────────────┐
-- │ id │ case_id │ file_name │ page_count │
-- ├────┼─────────┼───────────────┼────────────┤
-- │ 1 │ 100 │ complaint.pdf │ 12 │
-- └────┴─────────┴───────────────┴────────────┘This is the happy path and it really is that simple. It's more instructive to break it on purpose. We add a column on the publisher and insert a row that uses it:
-- On app
alter table document add column mime_type text;
insert into document values (2, 100, 'answer.pdf', 8, 'application/pdf');Row 2 never shows up on the subscriber. The apply worker is stuck: it's receiving a row with a mime_type column that its version of document doesn't have. The server log says exactly this, repeatedly as it retries the apply:
ERROR: logical replication target relation "public.document" is missing replicated column: "mime_type"This is a critical thing to remember about logical replication wherever you deploy it: schema changes are a coordination problem across two databases, and the order we apply those schema changes matters. While we do have an error on our hands in the above example, Postgres's refusal to advance past the erroring apply and its default-enabled behavior of retrying indefinitely are especially helpful. If Postgres were to instead skip over the erroring apply, we would very quickly have a data integrity problem on the subscriber.
The conventional advice is a per-operation ordering matrix. To add a column, do the subscriber first and then the publisher, so the subscriber already has somewhere to put the new values by the time they arrive. To drop a column, reverse it: publisher first, so it stops sending the column before the subscriber loses the place to put it. Widening a type is subscriber-first, and so on. This advice stems from one principle: the subscriber must be able to accept everything the publisher sends.
The trouble with a matrix is that a real deploy doesn't arrive neatly labeled "this is an add" or "this is a drop." It's often a big pile of migrations. Our tooling introduces an additional constraint: the analytics-side migrations only become available to the admin UI that runs them after a deploy, and the deploy is what runs the application-side migrations. So for adds, the matrix's subscriber-first order isn't practical without introducing extra steps.
Rather than track the matrix per deploy, we follow a single rule:
Run the application (publisher) migrations first, then the analytics (subscriber) migrations.
The reason this works comes down to an asymmetry between adds and drops, which we can confirm with some local testing.
Adding a column publisher-first is transiently broken but self-healing. This is the stuck state from the example above. While the publisher has the column and the subscriber doesn't, the apply worker errors with missing replicated column and replication pauses, so WAL accumulates on the publisher. The moment we run the analytics migration and the subscriber gets its column, the apply worker picks up where it left off and catches up and nothing is permanently hosed. We've only paid for a bit of lag and that's it.
We can convince ourselves of this on our local database:
\c analytics
alter table document add column mime_type text;After doing so, the apply worker is able to proceed and we have matching data across the publisher and subscriber:
┌────┬─────────┬───────────────┬────────────┬─────────────────┐
│ id │ case_id │ file_name │ page_count │ mime_type │
├────┼─────────┼───────────────┼────────────┼─────────────────┤
│ 1 │ 100 │ complaint.pdf │ 12 │ Ø │
│ 2 │ 100 │ answer.pdf │ 8 │ application/pdf │
└────┴─────────┴───────────────┴────────────┴─────────────────┘Dropping a column publisher-first is fine, given one condition. The publisher stops sending the column, and the subscriber keeps its now-unused column around until we drop it too. The condition is that the leftover column on the subscriber side has to be nullable (or have a default). After the publisher-side drop, when the publisher inserts a new row, that row arrives at the subscriber without the dropped column and the subscriber fills it in. If the leftover column were NOT NULL with no default, that insert would fail on the subscriber:
ERROR: null value in column "drop_me" of relation "document" violates not-null constraintWe make almost everything nullable on the analytics side (more on that later), so this condition holds by construction.
Doing a drop the other way around, subscriber-first, is the case to avoid. The publisher sends a column the subscriber no longer has, and the apply worker jams with the same missing replicated column error as a premature add. This will not self-heal by dropping the same column on the publisher as the erroring transaction in the WAL stream remains unable to be applied (still no place to put the column on the subscriber). A nuance: if no transaction touches that table in the window between dropping on the subscriber and dropping on the publisher, nothing gets replicated that references the column and we luck out with no error. We don't want to lean on luck and we never have to thanks to our single rule.
So the same order, publisher first and subscriber second, is safe for drops and only briefly costly for adds. We find this to be a much easier thing to get right on every deploy than a matrix.
There is an obvious wrinkle: what about a single deploy that both adds a column to a replicated table and drops one? The matrix wants opposite orders for the two operations, but the single rule still handles it. Running the app-side add and drop then the analytics-side add and drop will transiently pause from the add if transactions touch the table (the missing replicated column error again), but then will self-heal once the analytics column lands, just like a plain add.
Our single rule helps keep deploys safe, but it does nothing to prevent the schemas drifting apart in other ways: a column that exists on the app but was never added to analytics, a type that doesn't line up, a table that lost its primary key. To catch those, we have a test that fails CI when the two schemas fall out of alignment in a way that would impact replication.
It works entirely off the Postgres system catalogs, so it checks the actual shape of both databases rather than anything we've declared in Scala. It reads each table's columns, primary keys, and replica identity straight from pg_class, pg_namespace, pg_attribute, pg_constraint, and pg_index. For example, listing the tables in a schema along with whether each has a primary key and what its replica identity is set to:
private def listTables(schema: String): ConnectionIO[List[PgTable]] =
sql"""
select
c.relname,
c.relreplident::text,
exists (
select
from pg_constraint pc
where pc.conrelid = c.oid and pc.contype = 'p'
) as has_pk
from pg_class c
join pg_namespace n
on c.relnamespace = n.oid
where n.nspname = $schema
and c.relkind = 'r'
and c.relname <> 'flyway_schema_history'
order by c.relname
""".query[(String, String, Boolean)].to[List].map(_.map(PgTable.apply.tupled))Our tests make three assertions:
Running this test suite in CI gives us a high degree of confidence that the changes we make are safe and will not break replication. If you are considering using logical replication, we strongly recommend carving out time to build a proper test suite.
Every subscriber table without a primary key has a replica identity. This is the second gotcha from the intro, and a particularly nasty one to hit in production. To apply an UPDATE or DELETE, the subscriber has to find the target row. It does that using the source table's replica identity, which is the primary key by default. If a replicated table has no primary key on the subscriber side and nothing standing in for it, the apply worker errors:
ERROR: logical replication target relation "public.the_table" has neither
REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have
REPLICA IDENTITY FULLThis is a very subtle thing. INSERTs work, so everything looks healthy until the first UPDATE or DELETE arrives, and then the subscription pauses on that spot in the WAL stream. Rather than simply check for a primary key, the test reads the source table's relreplident and decides what the analytics side needs in order to satisfy it:
test("every analytics table without a PK has a replica identity") {
ZIO.serviceWithZIO[Transactor[Task]]: tnx =>
for
analyticsTables <- listTables(analyticsSchema).transact(tnx)
mainTables <- listTables(mainSchema).transact(tnx)
mainByName = mainTables.map(t => t.name -> t).toMap
perTableViolations <- ZIO.foreach(analyticsTables): at =>
// This test does its checks only if the analytics table lacks a PK.
// Most tables have a PK on both sides, so a missing PK on analytics
// is a signal that there might be evil afoot.
if at.hasPk then
ZIO.succeed(Nil)
else
mainByName.get(at.name) match
// The shape-compatibility test below this one covers the case
// of no main counterpart, so skip doing stuff in this branch.
case None =>
ZIO.succeed(Nil)
// Source is REPLICA IDENTITY FULL: it sends the whole row, so
// analytics can find the row without a matching key.
case Some(mt) if mt.relreplident == "f" =>
ZIO.succeed(Nil)
case Some(mt) if mt.relreplident == "n" =>
ZIO.succeed(List(s" - ${at.name}: main needs a replica identity"))
// Source is REPLICA IDENTITY USING INDEX: analytics needs a unique
// index on the same columns to apply UPDATE/DELETE.
case Some(mt) if mt.relreplident == "i" =>
for
mainIdentCols <- listReplicaIdentityIndexColumns(mainSchema, at.name).transact(tnx)
analyticsIdxSets <- listUniqueIndexColumnSets(analyticsSchema, at.name).transact(tnx)
yield
if analyticsIdxSets.contains(mainIdentCols) then
Nil
else
val cols = mainIdentCols.mkString(", ")
List(s" - ${at.name}: analytics needs same unique index main has on ($cols)")
case Some(mt) if mt.hasPk =>
ZIO.succeed(List(s" - ${at.name}: analytics needs same PK that main has"))
case Some(_) =>
ZIO.succeed(List(s""" - ${at.name}: main needs PK or replica identity of "using index" or "full""""))
violations = perTableViolations.flatten
yield assertTrue(violations.isEmpty).label(
if (violations.isEmpty) then
""
else
s"Tables with replica identity issues:\n${violations.mkString("\n")}"
)
}Every subscriber table is shape-compatible with its publisher counterpart. The subscriber needs every column the publisher sends, by name and with a compatible type. The test walks the columns on both sides (via pg_attribute and format_type) and flags anything missing or mismatched. We make one deliberate exception: an analytics column may be text even when the source column is something else. Subscriptions use the text representation of values by default, so a text column on the analytics side accepts anything. That lets us store the app's enum columns as plain text on analytics and not chase every enum change across two databases:
test("every analytics table is shape-compatible with its main counterpart") {
ZIO.serviceWithZIO[Transactor[Task]]: tnx =>
for
analyticsTables <- listTables(analyticsSchema).transact(tnx)
mainTables <- listTables(mainSchema).transact(tnx)
mainTableNames = mainTables.map(_.name).toSet
perTableViolations <- ZIO.foreach(analyticsTables): at =>
if (!mainTableNames.contains(at.name)) {
ZIO.succeed(List(s" - ${at.name}: no main counterpart"))
} else {
for
mainCols <- listColumns(mainSchema, at.name).transact(tnx)
mainColsByName = mainCols.map(c => c.name -> c).toMap
analyticsCols <- listColumns(analyticsSchema, at.name).transact(tnx)
analyticsColsByName = analyticsCols.map(c => c.name -> c).toMap
yield
val missingOrMismatched = mainCols.flatMap: mc =>
analyticsColsByName.get(mc.name) match
case None =>
List(s" - ${at.name}.${mc.name}: missing from analytics (main type: ${mc.typeName})")
case Some(ac) if !typesReplicationCompatible(mc.typeName, ac.typeName) =>
List(s" - ${at.name}.${mc.name}: type mismatch (main=${mc.typeName}, analytics=${ac.typeName})")
case Some(_) =>
Nil
val onlyOnAnalytics = analyticsCols.flatMap: ac =>
if mainColsByName.contains(ac.name) then
Nil
else
List(s" - ${at.name}.${ac.name}: on analytics but not on main (analytics type: ${ac.typeName})")
missingOrMismatched ++ onlyOnAnalytics
}
violations = perTableViolations.flatten
yield assertTrue(violations.isEmpty).label(
if (violations.isEmpty) then
""
else
s"Analytics tables incompatible with main:\n${violations.mkString("\n")}"
)
}
def typesReplicationCompatible(mainType: String, analyticsType: String): Boolean =
mainType == analyticsType || analyticsType == "text"Every subscriber column is nullable other than primary key and unique index columns. We deliberately keep the analytics side's constraints looser than the app's. A NOT NULL or CHECK on the subscriber that the publisher doesn't enforce is just another way to reject a row that was valid upstream and break replication. As mentioned previously, we've made almost everything on analytics nullable, primary keys aside. The test enforces it: a column can be NOT NULL only if it's part of a primary key or unique index. We would rather have the data land and sort out its shape in the reporting layer than have a surprise constraint block replication.
test("every analytics column is nullable (other than PK and unique index cols)") {
ZIO.serviceWithZIO[Transactor[Task]]: tnx =>
for
tables <- listTables(analyticsSchema).transact(tnx)
perTableViolations <- ZIO.foreach(tables): t =>
for
cols <- listColumns(analyticsSchema, t.name).transact(tnx)
uniqueIdxSets <- listUniqueIndexColumnSets(analyticsSchema, t.name).transact(tnx)
uniqueIdxCols = uniqueIdxSets.toSet.flatten
yield cols.collect:
case c if c.notNull && !uniqueIdxCols.contains(c.name) =>
s" - ${t.name}.${c.name}"
violations = perTableViolations.flatten
yield assertTrue(violations.isEmpty).label(
if (violations.isEmpty) then
""
else
s"Analytics columns that should be nullable:\n${violations.mkString("\n")}"
)
},Together these three checks turn "the subscriber must accept everything the publisher sends" into something verified on every PR rather than something each engineer must remember while writing an app migration.
If there's one thing to monitor, it's the replication slot.
The slot is what makes the publisher retain WAL long enough for the subscriber to consume it. This is desirable when the subscriber briefly falls behind, but also what becomes a problem if the subscriber goes away and doesn't come back. The slot has no idea the subscriber is gone. It keeps retaining WAL, and WAL keeps growing until the publisher runs out of disk.
Since we run on RDS, our first line of defense is CloudWatch. Before and during any deploy or initial table sync, we watch:
OldestReplicationSlotLag: how far behind the slot is.TransactionLogsDiskUsage and ReplicationSlotDiskUsage: whether retained WAL is growing without bound.COPY can raise both, and they should settle once the table reaches a steady state.Beyond the metrics, a few small queries can go a long way. On the publisher, this is the core health check. active should be true and wal_status should be reserved:
select slot_name, active, wal_status, safe_wal_size, invalidation_reason, inactive_since
from pg_replication_slots;To see how much WAL the slot is pinning:
select
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal_size,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as unflushed_wal_size
from pg_replication_slots
where slot_type = 'logical';On the subscriber, pg_stat_subscription_stats tells us whether rows are applying cleanly (any non-zero error or conflict count means they aren't), and pg_subscription_rel tells us where each table is in its lifecycle, with every table eventually reaching r ("ready") after its initial copy.
Postgres does give us guardrails for the disaster case. max_slot_wal_keep_size (Postgres 13+) caps how much WAL a slot can retain, and idle_replication_slot_timeout (Postgres 18+) drops slots that have sat idle too long. Both work by invalidating the slot though, which means that after the slot is re-established, we must do a full re-sync of the analytics database. The point of monitoring is to catch a lagging subscriber and get it healthy again before the slot is invalidated, but it's important to have the knobs available to prevent an outage.
Logical replication has been in Postgres since version 10, and it's improved with almost every major release. Row filters and column lists for publications arrived in 15, and version 18 brought a batch of operational niceties that are worth calling out.
On the subscriber side, there is now rich conflict logging. When a row from the publisher can't be applied because it collides with a row that already exists on the subscriber, older versions reported whatever low-level error the apply happened to hit. Take the case where a row with some key already exists on the subscriber and the publisher sends an insert with that same key. On Postgres 17, the apply worker surfaces the raw unique-constraint violation:
ERROR: duplicate key value violates unique constraint "document_pkey"
DETAIL: Key (id)=(2) already exists.
CONTEXT: processing remote data for replication origin "pg_16403" during message type "INSERT" for replication target relation "public.document" in transaction 753, finished at 0/2172CA0That tells us a key collided and not much else. Nothing labels it as a conflict, and there's no sign of what the two rows actually were. Postgres 18 makes conflicts first-class, and the same collision now logs as:
ERROR: conflict detected on relation "public.document": conflict=insert_exists
DETAIL: Key already exists in unique index "document_pkey", modified in transaction 414176.
Key (id)=(2); existing local row (2, 100, local.pdf, 5); remote row (2, 100, remote.pdf, 9).
CONTEXT: processing remote data for replication origin "pg_2724063" during message type "INSERT" for replication target relation "public.document" in transaction 414177, finished at 24/A6EDC110Now the message names the conflict type (insert_exists) and shows both the existing local row and the incoming remote row, so we can see exactly what diverged. Related bookkeeping lands in pg_stat_subscription_stats, which received a per-conflict-type breakdown (confl_insert_exists, confl_update_exists, confl_update_missing, confl_delete_missing, confl_update_origin_differs, confl_delete_origin_differs, confl_multiple_unique_conflicts) on top of the plain apply_error_count it had before.
Two other 18 changes are worth a mention: idle_replication_slot_timeout (the guardrail from the previous section), and parallel apply of streamed transactions becoming the default, which is why we sometimes see more than one apply worker per subscription in pg_stat_subscription. These things don't change the essence of logical replication but they definitely improve its operation.
Logical replication turned out to be a great fit for the problem we started with: getting app data into an analytics database that is near-live and without putting undue load on the app database or mental burden on us engineers. The crash course is short (publications, subscriptions, a slot, and a stream of row changes), but production-izing is where most of the work is. DDL is a two-database coordination problem that a single ordering rule and a catalog-driven test suite keep in check, rows need a replica identity, and the slot needs watching so retained WAL never becomes the app's problem.
That gets data into our analytics database. What we do with it once it's there (aggregations galore) is a topic for a future installment. Thanks for reading!
Jason Shipman is a Staff Software Engineer at Pattern Data.
Why separate the analytics database from the application database?
Reporting queries that run across an entire docket place heavy and highly variable load on a server. Running them against the application database would degrade the application itself. Separating the two means reporting can scale independently, with indexes and derived tables built specifically for analytical queries.
Why choose logical replication over a physical read replica?
A physical replica is a read-only, block-level clone locked to the same Postgres major version. Logical replication sends row changes that the subscriber applies as ordinary SQL, so the analytics database stays writable, can hold its own indexes and derived tables, can replicate a subset of tables, and can be upgraded independently.
What breaks most often with logical replication?
Schema changes. DDL does not replicate, so any column added on the publisher before it exists on the subscriber pauses the apply worker until the subscriber catches up. Tables without a primary key or replica identity are the other common failure, and they look healthy until the first UPDATE or DELETE arrives.
How do you keep two database schemas from drifting apart?
A single deploy ordering rule (application migrations first, analytics migrations second) plus a CI test suite that reads the Postgres system catalogs directly and fails the build when the two schemas fall out of alignment in a way that would break replication.
What should you monitor once logical replication is running?
The replication slot. It retains write-ahead log on the publisher until the subscriber consumes it, so a subscriber that falls behind and never recovers will grow retained WAL until the publisher runs out of disk. Slot lag, retained WAL size, and apply error counts are the signals worth alerting on.