Comparison Performance

LiveCompare has been optimized for use on production systems and has various parameters for tuning, described later. 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.

The use case above can be considered a general use case. For low-load, testing, migration and other specific scenarios, it might be possible to improve speed by changing the data_fetch_mode setting to use server-side cursors. Each kind of server side cursors, in our experiments, provides an increase in performance on use cases involving either small or large tables.

Security Considerations for the User

For PostgreSQL 13 and older, LiveCompare requires an user that is able to read all data being compared. PostgreSQL 14 introduced a new role pg_read_all_data , which can be used for LiveCompare.

When logical_replication_mode = bdr , LiveCompare requires a user that has been granted the bdr_superuser role. When logical_replication_mode = pglogical , LiveCompare requires a user that has been granted the pglogical_superuser role.

To apply the DML scripts in PGD, then all divergent connections (potentially all data connections) require a user that has been granted the bdr_superuser in order to disable bdr.xact_replication .

If PGD is being used, LiveCompare will associate all fixed rows with a replication origin called bdr_local_only_origin . LiveCompare will also apply 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 will change role to the owner of the table in order 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 have ability to switch role to the table owner.