Varan Download Varan →

How to migrate between databases

Nobody loses a week to throughput. They lose it to the load dying at 80% on a type or a foreign key nobody knew was broken. This is how to find that out while the target is still empty.

1. Write the schema as SELECTs

Each target table is declared together with the query that fills it. The projection is the schema — a SELECT already names the columns, expresses the transformation, and carries types Varan can settle from the census. There is no second language to learn.

PLAN MIGRATE ONTO pg_prod__shop AS
  TABLE users
    FROM SELECT id, email, lower(name) AS name FROM mysql_customers
    KEY id,
  TABLE orders
    FROM SELECT id, customer_id AS user_id, total FROM mysql_orders
    KEY id
    FOREIGN KEY (user_id) REFERENCES users (id),
  TABLE order_items
    FROM SELECT id, order_id, sku, qty FROM mongo_line_items
    KEY id
    FOREIGN KEY (order_id) REFERENCES orders (id)

Three different databases, one target schema. One rule keeps it unambiguous everywhere:

• FROM SELECT … FROM x always names a source table.
• TABLE, KEY and REFERENCES always name the target schema.

So REFERENCES users can only mean a TABLE declared in this statement. Pointing it at a source is an error with a message, never a silent misreading. It is standard SQL foreign-key syntax on purpose: every reader already knows it, and it survives composite keys — (a, b) REFERENCES t (c, d) pairs positionally.

2. Run PLAN — it touches nothing

PLAN is pure. It parses the statement, resolves each column’s type against the census, emits the CREATE TABLE for the target engine, measures your link, and reports anything unsettled. It creates no table, no temporary object and no rows, so you can iterate on the design as many times as you like — and the output is safe to read, check into a repo, or hand to a DBA to run somewhere else.

The DDL carries its evidence in comments, so every type choice can be checked:

CREATE TABLE shop.order_items (
  id        TEXT    NOT NULL,  -- objectId in all 501,338 values (counted)
  order_id  TEXT    NOT NULL,  -- objectId in all 501,338 values (counted)
  sku       TEXT,               -- string in 498,001 values; absent in 3,337
  qty       BIGINT              -- int in all 501,338 values (counted)
);

NOT NULL is derived from the values that exist, never from how often a field is present — getting that backwards is what makes a load fail on row one.

WITH options

Option	Meaning
KEY <columns>	Required. Makes the load resumable and a re-run non-duplicating. Composite: KEY tenant_id, event_id.
MODE upsert | append	upsert (default) writes idempotently. append inserts.
BATCH <size>	Target bytes per batch, e.g. BATCH 64MB.

3. Read the referential check

This is the part a schema tool cannot do, because every other one reads metadata and Varan reads the data:

REFERENTIAL CHECK

  orders.user_id → users.id
    124,067 of 124,067 matched — safe to declare

  order_items.order_id → orders.id
    1,204 of 501,338 values have no matching orders.id
    Declaring this key will fail the load. Fix the data, or drop
    the FOREIGN KEY and add the constraint once the rows are in.

Declaring a foreign key your data does not satisfy is exactly how a load dies at 80%, and it is not a failure you can recover by retrying. Varan already counts every value, so it can say this while the target is still empty.

The same graph gives you two more things for free:

• Load order — parents before children, derived from the keys rather than remembered.
• Cycles reported, not invented — if no load order can satisfy the keys, Varan says so instead of emitting DDL that cannot execute.

4. Check what your link can sustain

The same job takes two hours or eighteen days depending on an uplink nobody thinks about, so PLAN measures it and grades it:

Grade	Throughput
excellent	≥ 100 MB/s
good	≥ 25 MB/s
moderate	≥ 6 MB/s
bad	below that

The estimate is based on what actually crosses the wire, not on what the table weighs. Measured on real corpora, the transport format alone is 5× denser on dense rows and 19× on a wide sparse schema — quoting days for a job that takes hours is not a safe kind of wrong, because it sends someone off to build infrastructure they did not need. Past about a working day, Varan says the job belongs somewhere else. That is advice about placement, never a refusal; it is your machine.

5. Run it

Drop the PLAN and the same statement executes:

MIGRATE ONTO pg_prod__shop AS
  TABLE users …

Or migrate a single table with a plain projection:

MIGRATE
  SELECT _id AS id, actor.login AS actor_login
  FROM mongo_events
  WHERE type = 'PushEvent'
INTO pg_prod__analytics__events
WITH (KEY id, MODE upsert, BATCH 64MB)

6. Pause it. Close the laptop.

• Batches are sized by bytes, not rows. Ten thousand rows is 2 MB of order lines or 500 MB of event documents; a fixed row count varies by 250× in the work it represents.
• The checkpoint is written after the batch lands, never before. That leaves a window in which a batch was applied but not recorded, so on resume it is applied again. This is inherent, not a bug to paper over — and it is exactly why KEY is required and every write is an upsert.

Unfinished jobs are listed in the sidebar under Unfinished migrations until you resume or discard them, because a half-migrated target nobody remembers is worse than one that plainly failed.

Action	Effect
Pause	Stops after the current batch completes
Resume	Picks up from the checkpoint
Discard	Forgets the checkpoint. Rows already written stay.

A percentage is shown only when the byte total is known. An invented one is worse than none — it is the number people plan around.

What this does not do

• It is not continuous replication. A migration is a job you run, not a stream Varan tails.
• It does not install anything on either server. Everything crosses your machine, which is also why the link is the ceiling.
• It will not invent a meaning your data does not carry. Where a column holds two types, Varan reports both and asks; it does not pick one for you.

Get the beta →