Skip to content

table-diff

This command compares the data in the specified table across nodes in a cluster and generates a diff report if any inconsistencies are found.

Syntax

./ace table-diff <cluster_name> <schema.table_name> [options]
  • cluster_name: The name of the pgEdge cluster in which the table resides.
  • schema.table_name: The schema‑qualified name of the table to compare across nodes.

Options

Option Alias Description
--dbname <name> -d Database name. Defaults to the first DB in the cluster config.
--block-size <int> -b Rows processed per comparison block. Default 100000. Honours ace.yaml limits unless --override-block-size is set.
--concurrency-factor <float> -c CPU ratio for concurrency (0.0–4.0, e.g. 0.5 uses half of available CPUs). Default 0.5.
--compare-unit-size <int> -u Recursive split size for mismatched blocks. Default 10000.
--output <json\|html> -o Report format. Default json. When html, both JSON and HTML files share the same timestamped prefix.
--nodes <list> -n Comma-separated node list or all. Up to three-way diffs are supported.
--table-filter <WHERE> -F Optional SQL WHERE clause applied on every node before hashing.
--against-origin <node> Limit the diff to rows whose node_origin matches this Spock node id or name (useful for failed-node recovery).
--until <timestamp> Optional commit timestamp fence (RFC3339) applied with --against-origin and --table-filter; excludes newer rows.
--max-connections <int> -M Maximum database connections per node. When set, caps the connection pool regardless of concurrency factor. Default: derived from --concurrency-factor.
--override-block-size -B Skip block-size safety checks defined in ace.yaml.
--quiet -q Suppress progress output. Results still write to the diff file.
--debug -v Enable verbose logging.
--schedule -S Run the diff repeatedly on a timer (requires --every).
--every <duration> -e Go duration string (for example, 15m, 1h30m). Used with --schedule.

Example

Compare a table across all nodes and write a JSON report (add --output html to emit both JSON and HTML using the same <schema>_<table>_diffs-<timestamp> prefix):

./ace table-diff acctg public.customers_large

Sample Output (with differences)

2025/07/22 12:03:51 INFO Cluster acctg exists
2025/07/22 12:03:51 INFO Connections successful to nodes in cluster
2025/07/22 12:03:51 INFO Table public.customers_large is comparable across nodes
2025/07/22 12:03:51 INFO Using 16 CPUs, max concurrent workers = 16
Hashing initial ranges: 171 / 177 [=======================================================================>---] 1s | 0s
Analysing mismatches: 2 / 2 [=================================================================================] 0s | done
2025/07/22 12:03:53 INFO Table diff comparison completed for public.customers_large
2025/07/22 12:03:53 WARN ✘ TABLES DO NOT MATCH
2025/07/22 12:03:53 WARN Found 99 differences between n1/n2
2025/07/22 12:03:53 WARN Found 99 differences between n2/n3
2025/07/22 12:03:53 INFO Diff report written to public_customers_large_diffs-20250722120353.json

Sample Output (no differences)

2025/07/22 12:05:59 INFO Cluster acctg exists
2025/07/22 12:05:59 INFO Connections successful to nodes in cluster
2025/07/22 12:05:59 INFO Table public.customers_large is comparable across nodes
2025/07/22 12:05:59 INFO Using 16 CPUs, max concurrent workers = 16
Hashing initial ranges: 168 / 177 [======================================================================>----] 1s | 0s
2025/07/22 12:06:01 INFO Table diff comparison completed for public.customers_large
2025/07/22 12:06:01 INFO ✔ TABLES MATCH

How table-diff Works

ACE optimises comparisons with multiprocessing and block hashing:

  • It splits work into blocks (--block-size) and uses multiple workers per node (--concurrency-factor) to compute hashes. If hashes mismatch for a block, rows are materialised and, if necessary, recursively split using --compare-unit-size.
  • Runtime factors include host resources (CPU/memory), allowed parallelism, table size and row width (e.g., large JSON/bytea/embedding columns can slow hashing), distribution of differences (widely scattered diffs trigger more block fetches), and network latency to database nodes.

Relations ACE does not compare

ACE compares heap tables, including partitioned tables whose partitions are all heap tables. It refuses everything else in the pre-checks, with a message saying why:

  • Foreign tables (file_fdw, postgres_fdw, and so on) are refused. schema-diff and repset-diff list the foreign tables they skip.
  • Views and materialized views are refused. schema-diff compares views as DDL only and lists the ones it skipped for data comparison. When a table with the same name and a leading underscore exists next to the view (for example public._events beside public.events), the message mentions it. That table may be the one behind the view: tiering extensions such as coldfront rename the real table this way and put a view in its place. If that is the case, compare the underscore table. The cold tier behind a coldfront view is a shared Iceberg catalog, so it has no per-node copies to compare, and a coldfront decoupled table has no PostgreSQL data at all.
  • Partitioned tables with foreign partitions, and inheritance parents with foreign children, are refused, naming the foreign relations. Comparing the heap parts of such a table while skipping the foreign parts is future work.
  • Sequences, indexes, composite types, and TOAST tables are refused with a message naming what the relation is.

If a view does stand in front of a coldfront tiered table, note that the archiver moves rows from the hot table into Iceberg on a schedule, and the drop of an archived partition reaches the other nodes through replicated DDL. In that window the hot table legitimately differs between nodes. To compare only rows still above the archive watermark, filter on the replicated watermark table:

ace table-diff my-cluster public._events \
  --table-filter "ts >= (SELECT cutoff_time FROM coldfront.archive_watermark WHERE schema_name = 'public' AND table_name = 'events')"

Tuning tips

  1. Tune --block-size and --concurrency-factor for your hardware and data profile.
  2. Use --table-filter/-F to narrow scope on very large tables.
    • Filters are applied inline (no temporary views) and recorded in the diff summary as both the raw filter and the effective filter (which also includes --against-origin/--until if set).
  3. Prefer --output html when you'll manually review diffs.
  4. Use --override-block-size sparingly; the guardrails in ace.yaml prevent allocations that can overwhelm memory.
  5. The max_diff_rows setting caps the number of differing rows that table-diff collects in a single run.
    • The cap applies globally across all node pairs combined, not to each pair individually. On a three-node cluster that diffs n1/n2 and n1/n3 at the same time, both pairs share the single cap. As a result, each pair can report fewer differences than the table actually contains.
    • The table-diff stopped after reaching max_diff_rows warning indicates that the run hit the cap. Treat the reported result as partial whenever the warning appears. Re-run table-diff after each repair pass, and continue until the command reports TABLES MATCH.

Large divergence

When the number of differences between nodes is very large (approaching or exceeding max_diff_rows), table-diff emits a warning and stops enumeration early:

Recovery-focused options

Use --against-origin when you need a diff scoped to transactions from a failed node; combine with --until to fence at a known commit timestamp. The diff summary records: - only_origin (raw id), only_origin_resolved (node name if known), origin_only (bool) - until (commit timestamp fence) - table_filter (raw) and effective_filter (combined predicates)

When running table-repair on an origin-only diff, you must pass --recovery-mode (see table-repair docs) or the repair will refuse to run.

Scheduling runs

Add --schedule --every=<duration> to keep the diff running on a loop. The command performs an initial comparison immediately, then repeats after each interval until you stop the process:

./ace table-diff --schedule --every=1h my-cluster public.orders

All other flags (--nodes, --block-size, --output, and so on) are reused for every iteration.