Comparison performance

LiveCompare is optimized for use on production systems and has various parameters for tuning. Comparison rounds are read-only workloads. An example use case compared 43,109,165 rows in 6 tables in 9m 17s with 4 connections and 4 workers, giving comparison performance of approximately 77k rows per second, or 1 billion rows in <4 hours.

This use case is a general use case. For low-load, testing, migration, and other specific scenarios, you might be able to improve speed by changing the data_fetch_mode setting to use server-side cursors. In our experiments, each kind of server-side cursors provides an increase in performance on use cases involving either small or large tables.

Security considerations for the user

For PostgreSQL 13 and earlier, LiveCompare requires a user that can read all data being compared. PostgreSQL 14 introduced a new role, pg_read_all_data, that can be used for LiveCompare.

When logical_replication_mode = bdr , LiveCompare requires a user with the bdr_superuser role. When logical_replication_mode = pglogical , LiveCompare requires a user with the pglogical_superuser role.

To apply the DML scripts in PGD, all divergent connections (potentially all data connections) require a user with the bdr_superuser role to disable bdr.xact_replication .

If PGD is being used, LiveCompare associates all fixed rows with a replication origin called bdr_local_only_origin . LiveCompare also applies the DML with the transaction datetime far in the past, so if there are any PGD conflicts with real DML being executed on the database, LiveCompare DML always loses the conflict.

With the default setting of difference_fix_start_query , the transaction in apply scripts changes role to the owner of the table to prevent database users from gaining access to the role applying fixes by writing malicious triggers. As a result, the user for the divergent connection needs to be able to switch role to the table owner.