solutions for data comparison between large tables

[ymbian]

In most production environment scenarios, the rows in the source and target table is very large(say tens of millions of records),while the different rows are very small. However, PgCompare needs to first copy the data from the source and target to PG(divide origin columns into PK and non-PK columns) in order to find the small amount of different datas. Is pgcompare suitable for such cases , and what is the best practice in such cases? Is it reasonable to change its solution to in-memory comparison in the future for PgCompare?

[cbrianpace]

Tried a few in-memory options and where they break down is processing data in parallel from two different connections. Research continues. With very large tables, the key is to use the table filter option to only compare small subsets of data. For example, perform a compare of the entire table. Subsequent compares would use a filter like last_update > current_timestamp - interval '7 days' (for example). Another option is to use a range if the primary key column is an incrementing number.

[cbrianpace]

Look at the copy-table feature in v0.4.0. This will duplicate table mappings so you can build multiple compare scenarios on a single table.

[ymbian]

Tried a few in-memory options and where they break down is processing data in parallel from two different connections. Research continues. With very large tables, the key is to use the table filter option to only compare small subsets of data. For example, perform a compare of the entire table. Subsequent compares would use a filter like last_update > current_timestamp - interval '7 days' (for example). Another option is to use a range if the primary key column is an incrementing number.

"where they break down is processing data in parallel from two different connections"
--- do you mean that in-memory comparison must use source database or target database as the baseline, consume the data from the other database, and then perform the comparison?

"For example, perform a compare of the entire table. Subsequent compares would use a filter like last_update > current_timestamp - interval '7 days' (for example). "

---

As you have mentioned in README.md:
"pgCompare is a Java-based tool for validating data consistency after replication or migration between databases."

To ensure data consistency after migration, we need to perform a compare of the entire table. Can we start the comparison before the migration task completes ? It seems if both the value of PK_HASH and the value of COLUMNN_HASH in source are equals to the the value in the target, PgCompare can not find out the difference if the values are updated later.

[cbrianpace]

You can start the compare during data migration but you will need to identify some filter to use on source and target for table filter so that the exact subset is used to compare on both sides. For example, if you have a table with a column called id that is an incrementing column and the data is loading in order. Then you could set table filter on both source and target to check for id < 100000 for the first compare. For subsequent runs, change the filter to be between, id between 100001 and 200000.