How to compare two databases, Prod and Staging

When we talk about comparing two databases, Prod and Staging, we are actually talking about two distinct comparisons that are worth keeping clearly separate. The first concerns the schema: tables, columns, types, constraints, indexes. The second concerns the data: which rows are present, column values, row-by-row divergences. The tools, workflows, and constraints are not the same for the two.

This need comes up regularly before a deployment, after a migration, to validate that a change has propagated correctly, or simply to understand why a behavior differs between environments. This article surveys the common approaches and explains how QoreDB handles the topic on the desktop client side.

Comparing schemas: pg_dump, Liquibase, Flyway

The most direct approach for comparing two PostgreSQL schemas is to dump both databases with pg_dump --schema-only and then compare the two SQL files with diff. This method has the advantage of being available everywhere, of installing nothing extra, and of producing a textual result that is easy to archive. It also has limits: the order of columns or constraints in the dump can generate noise, and the raw diff still has to be interpreted by a human.

For teams that version their migrations, Liquibase and Flyway offer dedicated commands. Liquibase provides diff and diffChangeLog, which directly produce an applicable changelog. Flyway, in its Teams and Enterprise editions, offers a similar mechanism. These tools solve the textual-noise problem and integrate with a CI pipeline, but they assume you have already adopted the tool to manage your migrations.

For a lighter schema diff without committing to a migration tool, migra (PostgreSQL) or schemacrawler (multi-engine) generate migration SQL between two databases. This is useful for one-off cases, particularly before a deployment, to confirm that the release branch really applies what was prepared in Staging.

Comparing data: homegrown SQL or dedicated tools

Comparing data is a distinct problem, more costly and more treacherous. The best-known homegrown SQL method relies on EXCEPT or MINUS between two tables present in the same cluster, or through a foreign connection such as postgres_fdw. You get the rows present on one side and not the other. It is simple to write, but it doesn't tell you which columns differ on the common rows, and it scans everything without a filter.

A more efficient variant for large volumes consists of comparing checksums by block. You slice the table into primary-key ranges, compute an aggregated hash per range on both sides, and recurse only into the ranges that diverge. This is the approach used by Datafold, data-diff (open source) and several modern ETL tools. It works very well for comparing two warehouses in place, but the installation and configuration are not trivial.

When the need is more interactive, more exploratory, more oriented toward debugging a specific case, these tools are oversized. A developer who wants to understand why product X is different in Prod and Staging wants to see the two rows side by side, not configure a job. This is where the GUI angle makes full sense.

The GUI approach: seeing both sides at once

QoreDB includes a Visual Data Diff feature in its interface. The idea is to reuse the same paradigm as a code diff in Git: two panels side by side, highlighting of added, deleted, and modified rows, and statistics in a banner. The source on each side can be a table directly, or the result of an arbitrary query.

The component is designed to work between two different connections, not just between two tables of the same database. You pick the Prod connection on the left, the Staging connection on the right, the namespace and table on each side, and the tool automatically detects the common columns. Columns that exist on only one side are flagged, and clearly temporal audit columns (created_at, updated_at) are identified as trivial and can be excluded from the computation.

Row matching relies on key columns. By default, QoreDB tries to detect the primary key of both tables and uses it. If detection fails, or if you want to compare on another criterion (a business identifier, for example), the user explicitly selects the matching columns in the configuration panel. Without a key, the diff falls back to a row-by-row comparison by index, which is rarely what you want on real datasets.

Why a client-side diff makes sense

Doing the diff in the desktop client has a few interesting properties. The two sets of rows pass through the developer's workstation, they are not exposed to a third-party service, and the comparison writes nothing to either of the two databases. This is exactly the profile of an interactive, one-off use on reasonable volumes: you want to understand a divergence on a table of a hundred or a few thousand rows, not replicate continuously.

For very large volumes, the block-checksum approach remains more suitable and that is what you should aim for. QoreDB's Visual Data Diff is not meant to replace Datafold on a warehouse of hundreds of millions of rows. It targets the more frequent case where a developer or an SRE wants, in a few clicks, to see where two environments diverge and understand what needs fixing.

How it works in practice

In terms of use, you open a Diff tab in QoreDB. You configure the left source (Prod connection, schema, table or SQL query) then the right source (Staging connection, schema, table). You run the execution of each side independently, which lets you adjust a query without redoing the other side. Once both results are loaded, you select the key columns and trigger the comparison.

The result appears in a grid with four statuses per row: unchanged, added on the right, deleted on the right, modified. The statistics banner lets you quickly filter on a status, for example showing only the modifications. Cells whose value differs are highlighted in the grid, which makes them immediately easy to spot on wide tables. The results can be exported to CSV or JSON for sharing or archiving.

Which approach for which need

In short, the right tool depends on the need. For a versionable and applicable schema diff, Liquibase or Flyway do the job. For a quick dump between two servers, pg_dump and migra are enough. For a massive, automated data comparison, the checksum approach of tools like data-diff or Datafold is the right answer.

For the interactive case a developer runs into daily, understanding a one-off divergence between Prod and Staging on a given table, a desktop client that shows both sides and highlights what changes remains the fastest means. This is what QoreDB offers with its Visual Data Diff, in the same UI as the query and navigation tabs, with no external tool to plug in.