CData Sync

The "Enhanced CDC" feature added in CData Sync V24.3 significantly accelerates replication performance. To quantify the improvement, we conducted a comparative analysis against the conventional "CDC feature," focusing on replication completion time. Our findings demonstrate a significant reduction, with Enhanced CDC achieving completion times approximately nine times faster. This article provides a comprehensive overview of our experimental setup and replication procedures.

What is Enhanced CDC?

The Enhanced Change Data Capture (CDC) feature for jobs in Sync enables you to capture changes in real time. The modified data captured in real time is first stored as a file in the staging area of CData Sync. The mechanism for capturing changes is the CDC engine, explained in greater detail in the Help documentation on the CData Sync page.

For context, a comprehensive overview of traditional CDC can be found here: What is Change Data Capture, How Does it Work, & What Are its Benefits?

Key Factors Contributing to Performance Improvements

It's important to note from the outset that both traditional and enhanced CDC rely on the transaction log as the source for identifying data changes.

Traditional CDC operates by accessing the data source at scheduled intervals. When the job runs, if updates are confined to the target tables, it efficiently retrieves only those changes. However, if numerous updates occur in other, non-target tables, traditional CDC must navigate through and skip these irrelevant modifications. The diagram illustrates this: the process reverts to the position indicated by the orange line, bypasses the changes in Table-B, and then proceeds to retrieve the changes in Table-A (at the far right of the diagram).

In contrast, Enhanced CDC continuously monitors the transaction log, progressively advancing its read position (the orange line). Even when encountering changes in other tables, it swiftly skips over them, thereby reducing the time needed to locate the relevant change data.

Performance Benchmarking

To assess performance, we established an environment with PostgreSQL as the data source and SQL Server as the synchronization destination. Our comparative approach involved executing the following sequence of actions separately for both traditional and Enhanced CDC:

1. Creation of "Table A" for replication purposes.
2. Insertion of 50 records into "Table A".
3. Execution of the initial replication job for "Table A" (replicating 50 records).
4. Creation of "Table B".
5. Insertion of 1 million records into "Table B".
6. Addition of 10,000 records to "Table A".
7. Execution of a differential replication job for "Table A" 10 minutes after step 3.

As depicted in the diagram above, measurements were taken while a substantial volume of updates was applied to a non-target table (Table B).

Measurements Using Traditional CDC Mode

Our initial tests employed the traditional CDC method. We began by creating a table named postgres_cdc_1 with 20 columns and populating it with approximately 50 records. Subsequently, before job creation, we established a replication slot in PostgreSQL:

SELECT pg_create_logical_replication_slot('sync_slot1', 'test_decoding');

We then proceeded to create a change data capture job, specifying this replication slot.

The initial job for postgres_cdc_1 was executed, successfully replicating the initial 50 records.

Next, we created a non-target table named postgres_cdc_2 and inserted 1 million records into it.

This action introduced a backlog of changes from a table unrelated to our current job. Following this, we added 10,000 records to our target table for synchronization, postgres_cdc_1.

With these preparatory steps complete, the system's state mirrored the illustration below, where the change tracking position was significantly delayed.

The job was executed at the scheduled time, and completed the replication of the 10,000 new records in 54 seconds.

Having established the performance baseline for traditional CDC, we moved on to evaluating Enhanced CDC performance.

Measurements Using Enhanced CDC Mode

We then proceeded to measure the performance when utilizing the Enhanced CDC feature. We replicated the table creation process. Following this, we deleted the replication slot created for the traditional CDC and established a publication and a new replication slot specifically for Enhanced CDC:

SELECT slot_name, active FROM pg_replication_slots;
SELECT pg_drop_replication_slot('sync_slot1');
CREATE PUBLICATION cdatasync_pub1 FOR TABLE cdata.postgres_enhanced_cdc_1;
SELECT pg_create_logical_replication_slot('sync_enhanced_slot1', 'pgoutput');

With the target replication table and replication slot configured, we executed the initial replication.

Mirroring the previous test, we created a separate table, inserted 1 million records, and then added 10,000 records to the designated replication target table.

The addition of 10,000 records is shown below.

At this juncture, the system was poised to read the changes specifically from Table-A on the far right.

However, with Enhanced CDC, the changed data is retrieved immediately, so the 10,000 new records were linked almost instantly.

At this stage, the changed data resided as files within the staging area, awaiting the scheduled synchronization time. For clarity, the staging area's role in the architecture is depicted below. The staging area serves as a repository where change data is stored locally as a file.

When the scheduled time arrived, the replication from the staging area to the target system was executed. Since the change data had already been captured, the job completed in a mere 6 seconds.

Performance Comparison Results: Traditional CDC vs. Enhanced CDC

The outcomes of our testing are summarized in the table below:

Replicating 10,000 Changes

The fundamentally different timing of change data acquisition resulted in a substantial disparity in the time required for changes to ultimately reflect in the destination. In real-world scenarios involving updates across a larger number of tables, we anticipate an even more pronounced performance gap. Notably, these test results remained consistent across multiple iterations.

In Conclusion

As demonstrated, the newly introduced "Enhanced CDC" functionality significantly enhances the performance of change data capture replication. CData Sync offers a 30-day free trial, encouraging you to experience these benefits firsthand!

Free Trial | CData Sync | ETL / ELT Pipeline that Starts with No Code