Split a database by region — move just the rows for one region

Data-residency splits with a per-table --where predicate: keep US users in a US-region database and move only the EU users' rows into an EU-region database, in one shot or as a continuous filtered sync.

A common data-residency task: one database holds users from every country, and you now want each region's data to live in its own regional database — US users' rows in a US-region database, EU users' rows in an EU-region one. This is the region-move problem with a filter on top: you are not copying the whole database to the new region, only the rows that belong there. sluice does this with a per-table --where predicate (ADR-0173): each filtered table gets a native-SQL boolean scoping it to the target region, and the destination ends up holding only those rows.

This guide walks the concrete case — a region column on each table, splitting EU rows into an EU-region database — as a one-shot filtered migrate (the natural fit: "the EU database should end up with only the EU rows") and as a continuous filtered sync (zero-downtime, and it keeps handling users whose region changes). Read Before you start first — the referential-integrity point is the one that bites.

Before you start #

• You need a column to filter on. The predicate is native source SQL over the source's own columns — region = 'EU', country IN ('DE','FR','IE'), tenant_id = 42. If "which region" is derivable but not stored (e.g. only a free-text address), materialize it first — a generated or backfilled region column on each table you'll filter — so both the copy and (for a sync) the client-side evaluator can read it directly.
• Filter every related table by the same region key — this is the load-bearing rule. A relational schema couples your filters through its foreign keys. If orders references users and you filter users to region = 'EU' but copy orders whole, the EU target gets orders pointing at users it never received — the deferred ADD CONSTRAINT FOREIGN KEY then fails with SQLSTATE 23503 and sluice refuses loudly (SLUICE-E-WHERE-FK-ORPHAN, naming the constraint). The fix is to filter the whole referential closure consistently: put region = 'EU' on users, orders, order_items, and every table that hangs off them, so every kept child's parent is kept too. This requires that an EU order's user is itself EU — i.e. the region assignment is consistent down the FK graph. Where it genuinely isn't (a shared reference table, say), either don't filter that table or accept a degraded constraint with --allow-degraded-fks (Postgres target: the FK is added NOT VALID, still rejecting new orphaning writes; you run VALIDATE CONSTRAINT after reconciling). See the field note on this trap.
• The predicate is source-dialect and not portable. It runs on (or is evaluated against) the source, so use the source engine's SQL. That's fine here — both ends are usually the same engine — but a Postgres-source predicate uses Postgres syntax, a MySQL-source one uses MySQL syntax.
• Provisioning the regional target is exactly the region-move flow. Create the destination database in the EU region and connect to it just as in Move PlanetScale regions → Provision the target (same global connect host, region chosen by credential; ?tls=true; admin role for DDL). Everything there applies; this guide only adds the --where filtering on top.

Option A — one-shot filtered migrate #

The direct answer to "the EU database should end up with only the EU rows." Give each table a --where scoping it to EU; the predicate is pushed down into the source read (SELECT … WHERE <chunk_bounds> AND (region = 'EU')) and evaluated on the source, so only EU rows ever cross the wire — index-aware, no client-side scan. Preview with --dry-run, run it, then verify with the same predicates:

# preview
sluice migrate \
    --source-driver postgres --source "$SLUICE_SOURCE" \
    --target-driver postgres --target "$EU_TARGET" \
    --where "users=region = 'EU'" \
    --where "orders=region = 'EU'" \
    --where "order_items=region = 'EU'" \
    --dry-run

# copy only the EU rows
sluice migrate \
    --source-driver postgres --source "$SLUICE_SOURCE" \
    --target-driver postgres --target "$EU_TARGET" \
    --where "users=region = 'EU'" \
    --where "orders=region = 'EU'" \
    --where "order_items=region = 'EU'"

# verify — the SAME --where, so counts compare matching-source vs target
sluice verify \
    --source-driver postgres --source "$SLUICE_SOURCE" \
    --target-driver postgres --target "$EU_TARGET" \
    --where "users=region = 'EU'" \
    --where "orders=region = 'EU'" \
    --where "order_items=region = 'EU'"

Tables you don't name are copied in full — so name every table that carries region-scoped data; leave a genuinely global reference table (say currencies) unfiltered and it's copied whole, which is usually what you want. A --where that matches zero rows still creates the (empty) table.

On MySQL the flags are identical — only the driver and the predicate dialect change. Use --source-driver mysql / --target-driver mysql for self-hosted MySQL, or --source-driver planetscale / --target-driver planetscale for PlanetScale MySQL (see the region-move guide for the PlanetScale DSN + admin-role setup), and write the predicate in MySQL SQL:

sluice migrate \
    --source-driver planetscale --source "$SLUICE_SOURCE" \
    --target-driver planetscale --target "$EU_TARGET" \
    --where "users=region = 'EU'" \
    --where "orders=region = 'EU'" \
    --where "order_items=region = 'EU'"

The predicate is native source SQL either way — a MySQL source takes MySQL syntax, a Postgres source takes Postgres syntax — because it is pushed down to and evaluated by the source. For a MySQL string literal, mind the quoting: single-quote the value and, if your shell needs it, escape as usual.

Option B — continuous filtered sync (zero-downtime) #

If you can't freeze writes, run a sync with the same predicates. The cold-start snapshot pushes the filter down exactly like migrate, then the CDC leg keeps the EU target current — and it handles the case a one-shot copy can't: a user whose region changes. sluice evaluates the predicate on both the before- and after-image of every change and translates it to the correct target operation:

sluice sync start --stream-id eu-split \
    --source-driver postgres --source "$SLUICE_SOURCE" \
    --target-driver postgres --target "$EU_TARGET" \
    --where "users=region = 'EU'" \
    --where "orders=region = 'EU'" \
    --where "order_items=region = 'EU'"

Watch it catch up and gate cutover on freshness with sync status / sync health, then cutover / sync stop --wait / verify — the same cutover flow as a region move.

ALTER TABLE users       REPLICA IDENTITY FULL;
ALTER TABLE orders      REPLICA IDENTITY FULL;
ALTER TABLE order_items REPLICA IDENTITY FULL;

The other side of the split #

This produces the EU database. To also slim the original down to only US rows, you have two clean options: point a second filtered run at a fresh US-region database (--where "users=region = 'US'", and so on) and cut both over together; or, if the original database stays in the US region and you only need to remove the migrated EU rows, delete them there once the EU target is verified and live. Keep the EU sync tailing until you've cut EU traffic over, so no EU write is lost in the gap.

Next steps #

• Move PlanetScale regions — the un-filtered region move; its "Before you start" and "Provision the target" apply here verbatim.
• Filtered / subset migration (operator guide) — the full --where reference: push-down, the FK-orphan caveat, and the CDC row-move semantics in depth.
• You can't filter a parent without orphaning its children, the row-move before-image trap, and the predicate you evaluate twice — the three field notes behind this feature.
• Command reference and error codes — --where, --allow-degraded-fks, and the three SLUICE-E-WHERE-* codes.