Skip to content

Data compare

  • PostgreSQL
  • MySQL
  • MariaDB
  • MongoDB

Data compare finds the rows that differ between the tables of a source and a target database: rows missing from the target, rows that differ, and rows only in the target. It then writes the statements that make the target match, for you to apply or save as a script. You can untick single rows to leave them out.

It compares PostgreSQL, MySQL and MariaDB databases with each other, and a MongoDB database with another MongoDB database, where the rows are documents and the tables are collections.

Data compare of Larchwood against staging: per-table counts of inserts, updates, deletes and identical rows matched by checksum ranges, and the ten updated rows of shop.products with the old prices struck through above the new ones.Data compare of Larchwood against staging: per-table counts of inserts, updates, deletes and identical rows matched by checksum ranges, and the ten updated rows of shop.products with the old prices struck through above the new ones.
Data compare checksums key ranges, then shows exactly which rows differ and how.

Tables pair by name, and each pair needs a primary key or a unique NOT NULL key that both sides share. Tables without one are listed as not compared, with the reason.

When both sides are the same engine family (PostgreSQL with PostgreSQL, or MySQL and MariaDB with each other), Querybara does not read every row:

  1. It walks the source table’s key space in ranges of 10,000 rows.
  2. For each range, each server computes a row count and a checksum of the range itself.
  3. Ranges whose counts and checksums match are done; no row leaves the server.
  4. A range that does not match is split in two at the median key of the side with more rows, and each half is checked the same way, until a range holds 1,000 rows or fewer.
  5. Only those small ranges are streamed from both sides in key order and compared row by row.

Across engine families the text forms of values differ, so checksums are off and every row is streamed and compared as canonical values.

Checksums, not rowsData compare splits the key space into ranges, compares checksums computed on each server, skips matching ranges, bisects a mismatched range down to 1,000 rows, streams only those rows, and writes a sync script.Source · larchwoodTarget · larchwood_staging0–10k0–10kmatch✓10k–20k10k–20kmatch✓20k–30k20k–30ka91f ≠ 07c230k–40k30k–40kmatch✓40k–50k40k–50kmatch✓50k–60k50k–60kmatch✓≤ 1,000 rowsdata-sync.sqlUPDATE shop.orders SET …INSERT INTO shop.orders …sumsumsumsumsumsumsumsumsumsumsumsumrow 24,107

Checksums, not rows

  1. Data compare walks the key space of the table in ranges of 10,000 rows.
  2. Each server computes a checksum for each range. Only the checksums travel.
  3. Ranges whose checksums match are done without reading a single row.
  4. A range that differs is halved, and halved again, until each part holds at most 1,000 rows.
  5. Only those rows are streamed from both sides and compared row by row.
  6. The differences become a sync script of batched DELETE, UPDATE and INSERT statements.

Collections pair by name. Views, time series collections and collections on one side only are listed as not compared, with the reason.

There are no server-side checksums for documents, so Querybara reads both collections in full, sorted by _id, and compares the documents with the same _id. Field order is ignored by default; number types are compared, so 1 and NumberLong(1) differ unless you tick Ignore number types.

  1. Choose Data in the toolbar’s Compare group, which takes the database in front as the source and asks for one when none is. Or right-click a connection, a database or a PostgreSQL schema in the sidebar (on MongoDB, a connection or a database) and choose Compare data with…, which fills in the source. Compare: Compare Data… in the command palette opens a blank compare, also with no connection open.
  2. Pick the Source connection and Source database, and the target’s.
  3. Open Options and choose which differences to sync and how values compare (see below).
  4. Choose Compare data.

The compare runs as a job, with progress in the Jobs panel. The result lists each table with its key and the counts of Inserts, Updates, Deletes and Identical rows, and how it was compared: how many ranges matched by checksum, or “rows streamed”.

Click a table to page through its differences on the Inserts, Updates and Deletes tabs, with Previous and Next. Inserts show the source row, deletes the target row, and updates both with the changed cells highlighted. Columns… narrows the compared columns of that table for the next compare. A MongoDB collection shows each document’s _id, the top-level fields concerned and their values; an update shows only the fields that differ, on both sides.

The grid shows the first 10,000 differences per table and action; the counts, the script and the apply cover every row.

Every row of the grid has a tick box, ticked to start with. Untick a row to leave it out of the sync script and the apply; the tick box in the grid’s header ticks or unticks every row of that tab. Unticked rows are dimmed, the tab shows how many rows are ticked (Deletes (0 of 1)), and the change count next to Apply… and the review count only the ticked rows. Ticking a row of a table you had unticked ticks the table too.

Rows past the first 10,000 cannot be unticked one by one, but they follow the header tick box: untick it and they are left out too. After an apply, Querybara compares again, and the rows you unticked are among the differences that remain.

Option What it does
Insert rows missing from the target Includes inserts in the sync
Update rows that differ Includes updates in the sync
Delete rows only in the target Includes deletes in the sync
Ignore columns (every table) Leaves columns such as updated_at out of the compare
Float tolerance Treats floating-point values within this distance as equal
Trim text Compare as is, Trailing spaces (CHAR padding), or Leading and trailing spaces
Compare text case-insensitively Ignores letter case in text
Disable foreign key checks while applying PostgreSQL needs a superuser for this
Disable triggers while applying PostgreSQL; needs table ownership

A MongoDB compare offers Insert documents missing from the target, Update documents that differ and Delete documents only in the target, and these:

Option What it does
Ignore fields (every collection) Leaves top-level fields such as updatedAt out of the compare
Ignore field order { a, b } equals { b, a }; on by default
Ignore number types 1, 1.0 and Long(1) are equal

An ignored field is still copied when a document is updated, because updates replace the whole document.

Tick the tables to sync (or Sync every table), then:

  • Export sync script… writes the statements to a file.
  • Apply… opens Review and apply the data changes with the target, the script, and how it runs.

Each table’s changes run in their own transaction: deletes first, child tables before their parents, then updates and inserts, parents first. A failing statement rolls its table back and stops; tables before it stay changed. After applying, Querybara compares again and reports whether differences remain.

On MongoDB the script is a mongosh script. Each collection’s deletes run first, then its updates (whole documents replaced by _id) and inserts, in batches. MongoDB writes here run outside transactions, so a failing command stops the apply and the commands before it stay applied.

Saved comparisons work as in structure sync: Save comparison… keeps the connections and data settings. A saved data comparison can run on a schedule; a run that finds differences writes the sync script.

querybara data-compare compares one table, or one MongoDB collection. It exits 0 with no differences for the selected actions, 1 with differences, and 2 on errors.

Terminal window
querybara data-compare prod staging --table shop.products
querybara data-compare prod staging --table shop.products --actions insert,update --out sync.sql
querybara data-compare prod staging --table shop.products --apply --yes
querybara data-compare mongodb://prod/shop mongodb://staging/shop --table customers --out sync.js

Documents Querybara 0.1.19 · built from89dc752