The Hidden Costs of Change Data Capture: Understanding the Trade-offs of CDC on Proxy Solutions like DataSunrise

Change Data Capture (CDC) is a widely used approach in data-driven enterprises to track changes in a database. CDC allows organizations to monitor data modifications (such as inserts, updates, and deletes) and efficiently propagate these changes to downstream systems. While CDC can provide value in maintaining data consistency across multiple systems, implementing CDC using proxy solutions, such as DataSunrise, can lead to significant performance problems and operational headaches.

In this article, we’ll explore what CDC is, the challenges involved in implementing it using proxy solutions like DataSunrise, and why this practice is considered inefficient. We’ll also include detailed examples to illustrate the issues and performance impact associated with this approach.

What is Change Data Capture (CDC)?

Change Data Capture (CDC) is a mechanism to identify and track changes in a database in real-time or near-real-time. By capturing insert, update, and delete operations, CDC ensures that data changes are made available for analytics, data warehousing, ETL processes, and data replication purposes. CDC has become crucial for use cases such as:

• Data replication to maintain synchronization between different databases.
• Feeding data into streaming systems for real-time analytics.
• Auditing and compliance monitoring.

CDC can be implemented in various ways, such as:

1. Log-Based CDC. Reads directly from database transaction logs.
2. Trigger-Based CDC. Uses database triggers to capture changes.
3. Polling-Based CDC. Polls tables periodically to detect changes.
4. Proxy-Based CDC. Uses a middleware proxy to intercept and log data changes.

In this article, we focus specifically on proxy-based CDC and the problems it introduces, particularly in the context of solutions like DataSunrise.

How CDC Works with Proxy Solutions like DataSunrise

DataSunrise acts as a middleware proxy that sits between the application and the database, intercepting all incoming SQL queries. It aims to provide security, audit, and CDC functionalities, which means it must capture every change made to data.

To implement CDC, DataSunrise needs to determine the exact modifications for every update operation. This process typically requires it to:

1. Execute a SELECT statement before an update, using the same conditions as the UPDATE statement to capture the current state of the data.
2. Execute another SELECT statement after the update (or use database features like RETURNING) to capture the updated values.

These additional steps significantly increase the number of queries being executed on the database, which leads to performance degradation.

Performance Implications of CDC via Proxy Solutions

1. Increased Number of Queries

One of the major drawbacks of implementing CDC via a proxy is the additional SELECT queries required to capture the “before” and “after” states of the data. Let’s consider the following scenario:

Example Scenario. Update Operation

Suppose an application executes an UPDATE query to modify customer data:

UPDATE customers SET balance = balance + 100 WHERE customer_id = 12345;

To implement CDC, DataSunrise needs to capture both the previous and new states of the data. This involves the following steps

:

Pre-Update Snapshot. DataSunrise issues a SELECT statement to capture the current values:

SELECT * FROM customers WHERE customer_id = 12345;

This query captures the “before” state, including the value of balance before the update is applied.

Apply the Update. The original UPDATE query is executed:

UPDATE customers SET balance = balance + 100 WHERE customer_id = 12345;

Post-Update Snapshot. DataSunrise then issues another query to capture the “after” state:

SELECT * FROM customers WHERE customer_id = 12345;

Alternatively, if supported, it might use the RETURNING clause:

UPDATE customers SET balance = balance + 100 WHERE customer_id = 12345 RETURNING *;

Impact. For every UPDATE query, there are now two additional SELECT statements (or an alternative RETURNING mechanism). This approach triples the number of queries executed on the database, resulting in:

• Increased Database Load. The database must handle a significantly higher number of queries.
• Increased Network Traffic. More data is transferred between the proxy and the database.
• Latency Issues. The round trips for the additional queries introduce latency, which can lead to slower response times for the application.
2. Locking and Potential Deadlocks

Another major concern with executing additional SELECT statements is the impact on database locks and the risk of deadlocks.

Example Scenario. Data Locking and Deadlocks

Consider the following example where multiple concurrent transactions are updating the same set of records:

• Transaction A attempts to update the balance for customer_id = 12345.
• At the same time, Transaction B tries to update a different field, such as email, for the same customer.

To implement CDC, DataSunrise first needs to read the existing values for both transactions. The pre-update SELECT statements issued by both transactions may acquire shared locks on the records. However, when the UPDATE statements are executed, both transactions need exclusive locks.

This can lead to a situation where:

• Transaction A holds a shared lock for the SELECT on customer_id = 12345.
• Transaction B also holds a shared lock for the same SELECT.
• When both transactions attempt to acquire exclusive locks for the UPDATE, they become mutually blocked, leading to a deadlock.

Deadlocks result in one or more transactions being aborted, which affects the reliability of the system. The increased number of SELECT queries also means more locks are being held for longer durations, increasing the likelihood of deadlocks occurring in a high-concurrency environment.

3. Load Amplification

CDC via proxy solutions leads to load amplification on the database. For every change operation (insert, update, delete), multiple additional operations are generated, amplifying the load by at least a factor of three. This can have severe consequences:

• CPU and I/O Overhead. The database server has to process many more queries, resulting in increased CPU and disk I/O usage.
• Query Contention. With a greater number of queries being executed, there is increased contention for database resources like CPU, memory, and locks. This can lead to longer query wait times and reduced throughput.
• Scalability Challenges. The additional load makes it challenging to scale the database to accommodate more users or higher transaction volumes. The proxy solution itself can become a bottleneck, limiting the overall scalability of the system.
4. Inefficiency with Complex SQL Queries

For certain complex SQL queries, using a RETURNING clause might not be feasible. For example, consider an UPDATE involving a JOIN across multiple tables:

UPDATE customers
SET balance = balance + 100
FROM transactions
WHERE customers.customer_id = transactions.customer_id AND transactions.status = 'completed';

In such cases, it may not be possible to use a RETURNING clause to capture all updated values, forcing DataSunrise to issue additional SELECT queries. This results in even more complex query execution plans and further strains the database.

Real-World Example: Performance Benchmark

Consider a scenario where a retail application has a database with a high volume of transactions. The application performs 1,000 update operations per second on a table storing customer order information. Let’s compare the impact of using CDC via DataSunrise versus using a log-based CDC mechanism:

• Without CDC. The application performs 1,000 UPDATE operations per second.
• Log-Based CDC. Changes are captured directly from the transaction log, resulting in no additional queries being executed by the application.
• Proxy-Based CDC via DataSunrise:
• 1,000 Pre-Update SELECT statements.
• 1,000 Update statements.
• 1,000 Post-Update SELECT statements (or equivalent).

This results in 3,000 queries per second instead of the original 1,000. The database needs to handle three times the load, leading to:

• Higher CPU and Memory Utilization. Increased load means more resources are required.
• Query Latency. Increased round trips add latency to each transaction, impacting the end-user experience.
• Scalability Issues. The database struggles to scale beyond the original transaction volume due to the load amplification.

Alternatives to Proxy-Based CDC

To avoid the performance pitfalls associated with CDC via proxy solutions like DataSunrise, consider the following alternatives:

1. Log-Based CDC:
• Log-based CDC reads directly from the transaction log, which is maintained by the database. This approach is efficient because it does not require additional SELECT statements or interfere with the application’s normal workflow.
• Examples of log-based CDC tools include Debezium, Oracle GoldenGate, and AWS Database Migration Service (DMS).
2. Trigger-Based CDC:
• Database triggers can be used to capture changes at the row level. However, triggers can also introduce overhead, especially for high-volume tables.
• This approach may be suitable for small to medium workloads where the complexity of managing triggers is justified.
3. Database Native CDC:
• Some databases provide native CDC capabilities that are optimized for capturing changes with minimal overhead. For example, SQL Server offers a built-in CDC feature, and PostgreSQL supports logical replication.

Also, implementing CDC for monitoring purposes by alternative means allows monitoring only changes. SELECT and UPDATE/DELETE queries that do not produce any changes will not be included in monitoring.

Conclusion

Implementing Change Data Capture (CDC) using a proxy-based solution like DataSunrise can lead to significant performance and stability issues, primarily due to the increased number of queries and the potential for data locks and deadlocks. The need for additional SELECT queries before and after each update creates an excessive load on the database, which can severely degrade performance, especially in high-concurrency environments.

Instead of relying on proxy-based CDC, it is advisable to use more efficient alternatives like log-based CDC, trigger-based CDC, or native database CDC features. These approaches capture changes without adding unnecessary overhead, ensuring that your application and database can scale efficiently while maintaining data integrity.

Ultimately, the choice of CDC implementation should be made based on a careful assessment of performance requirements, scalability needs, and operational complexity. It is important to proceed from the goals and understand the limitations of each technology. And perhaps for full monitoring, it is necessary to combine different technologies. By avoiding the pitfalls of proxy-based CDC, you can ensure that your system remains performant and reliable, even as data volumes grow.