Moving one tenant with logical replication row filters

One tenant has outgrown the shared database — or needs its own for contractual reasons — and has to move without a long outage. Since PostgreSQL 15, logical replication can do that for a single tenant: a publication with a row filter, WHERE (tenant_id = 1), copies only that tenant’s rows and keeps streaming its changes until you switch over.

We did it on PostgreSQL 17.10, between two throwaway containers with default settings and wal_level = logical on the source: 311,205 invoices and 1,244,820 invoice lines across 100 tenants, moving the largest (60,000 invoices, 240,000 lines). The copy itself turned out to be the easy part. The step before it can stop every tenant from changing or deleting anything.

Why do UPDATE and DELETE fail after creating a publication with a row filter?

Because the filter column is not part of the replica identity. We created the publication:

create publication move_t1 for table
  tenants where (id = 1),
  invoices where (tenant_id = 1),
  invoice_lines where (tenant_id = 1);

and immediately afterwards, on the source:

update invoices set total_cents = total_cents + 1 where id = 5;
ERROR:  cannot update table "invoices"
DETAIL:  Column used in the publication WHERE expression is not part of the replica identity.

The same for DELETE on invoice_lines, and for an update of a row that belongs to tenant 50 — a tenant that is not moving at all. Inserts kept working. The documentation states the rule: if a publication publishes updates or deletes, “the row filter WHERE clause must contain only columns that are covered by the replica identity”. By default the replica identity is the primary key, id, and tenant_id is not in it. The moment the publication exists, your product can create invoices but not change or delete one, for every tenant.

So the replica identity has to change first, before the publication is created.

What does REPLICA IDENTITY FULL cost?

Two ways to get tenant_id into the replica identity: REPLICA IDENTITY FULL (the whole old row goes into the WAL), or a unique index that contains it, (tenant_id, id), as REPLICA IDENTITY USING INDEX. We measured an update of 115,738 invoices of nine other tenants, twice per variant, each on a fresh copy with a checkpoint just before:

FULL cost 19% more WAL and no measurable time. The index route also requires tenant_id to be NOT NULL. The extra index cost 22% more WAL and 33% more time in this update — and an index is one more thing every insert maintains, after the move too, unless you drop it. For a move that lasts hours, FULL for its duration and back to DEFAULT afterwards is the cheaper of the two here. With primary keys that already start with tenant_id, the question does not arise at all. On the target, FULL costs little as long as it has the primary key, which pg_dump -s brings along: the subscriber uses it to find the rows to change.

How long do the copy and the catch-up take?

With the schema created on the target (pg_dump -s of the three tables) and the subscription started, all three tables reached the ready state in 1.3 seconds: 1 tenant, 60,000 invoices, 240,000 lines. The initial copy uses the same row filter, so the other 99 tenants never reached the target.

Then we kept writing: 1,000 new invoices with 4,000 lines for the moving tenant, an update of every tenth invoice of that tenant, and an update of 115,738 invoices of other tenants, which the source decodes and filters out. We recorded the source’s WAL position after the last write; the slot had confirmed it within 0.7 seconds, and the target held exactly what the source held for that tenant: 61,000 invoices and 244,000 lines, with identical checksums over ids and amounts — so the updates arrived too.

That catch-up is the core of the tenant’s outage: stop their writes, record pg_current_wal_lsn() once, wait until the slot’s confirmed_flush_lsn passes that recorded position — not the current one, which keeps moving because other tenants keep writing — then set the sequences and point their connections at the new database. We did not time those last two steps.

What does logical replication not bring along?

The sequences. The documentation is explicit — “Sequence data is not replicated” — and the first insert on the new database showed what that means:

ERROR:  duplicate key value violates unique constraint "invoices_pkey"
DETAIL:  Key (id)=(1) already exists.

The target’s sequence stood at 1 while the tenant’s invoices carried ids up to 311,205 and beyond. Set every sequence on the target before the tenant writes there, as part of the cut-over, not after the first error. The safest value is the source sequence’s own value: max(id) on the target is only this tenant’s highest id, which is enough as long as the new database never receives ids from anywhere else.

A stopped subscription is not harmless. We disabled the subscription and updated the invoices of 29 other tenants on the source. The replication slot then held back 91 MB of WAL that could not be removed. With the default max_slot_wal_keep_size = -1, “replication slots may retain an unlimited amount of WAL files”: a move that stalls over a weekend fills the source’s disk. Drop the subscription (which drops its slot) as soon as the move is done, and set a limit while it runs — knowing that a slot that exceeds the limit becomes invalid, and the move then starts over with a fresh copy.

A checklist for moving one tenant

1. Replica identity first: FULL (or a unique index with tenant_id) on every table whose filter column is not in the replica identity, before CREATE PUBLICATION … WHERE. Otherwise every tenant’s updates and deletes on those tables fail. tenants where (id = 1) needs nothing: id is its primary key.
2. Schema on the target from pg_dump -s, without the publication.
3. Subscribe and wait until pg_subscription_rel shows every table ready.
4. Cut over: stop the tenant’s writes, record the source’s WAL position once, wait until the slot has confirmed it, setval every sequence, switch the connection.
5. Clean up: drop the subscription, set the replica identity back, and remove the tenant from the source — how long that takes, and what stays on disk, is in deleting one tenant in PostgreSQL.
6. Limit the slot with max_slot_wal_keep_size for as long as the move runs, and watch it.

If the tenant only needs a copy rather than a live move, exporting one tenant with pg_dump is simpler. Which isolation model you move them to is in multi-tenant architecture models.

What we did not measure

Schema changes during the move, a tenant whose tenant_id changes, row-level security on the target, large objects, and a source under real production load: our write load was a burst, not a day.

Sources: the PostgreSQL 17 documentation on row filters, logical replication restrictions (sequences) and replication settings (max_slot_wal_keep_size), read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10, on 1 October 2026.