--- title: "Implementation of Change Data Capture Using TimescaleDB for Shoplogix Industrial Monitoring Services" published: 2025-05-22T08:59:22.000-04:00 updated: 2026-01-23T17:35:52.000-05:00 excerpt: "Learn how Shoplogix implements Change Data Capture Using TimescaleDB for its Industrial Monitoring Services." tags: Dev Q&A, TimescaleDB, Monitoring & Alerting authors: Carlos Olmos, Austin Schaffer, Rob Cook --- > **TimescaleDB is now Tiger Data.** _This is an installment of our Community Member Spotlight series, in which we invite our customers to share their work, spotlight their success, and inspire others with new ways to use technology to solve problems._ ## Introduction At **Shoplogix IMS**, we build **Industrial IoT** (IIoT) solutions, integrating software and hardware designed around batteryless wireless sensors. These sensors continuously monitor critical manufacturing assets, delivering real-time insights. As our platform evolved and scaled, the need to propagate real-time database changes—like sensor activations, configuration updates, or asset status transitions—across different systems became essential. Such capabilities power interactive dashboards, automated workflows, and analytics pipelines. To address these requirements, we developed a streamlined **Change Data Capture (CDC)** pipeline utilizing **TimescaleDB**, PostgreSQL triggers, and Kafka Connect. This solution allowed us to stream database changes into Kafka seamlessly, without modifying existing table schemas or relying on PostgreSQL's write-ahead logs. In this post, we'll explore the details of our CDC architecture, highlight TimescaleDB's strengths, and demonstrate how we've achieved continuous synchronization across downstream systems with minimal overhead. **Proof of Concept** To better explain the concepts of our approach, we have prepared a public repository that mimics the implementation laid out in this article. This repository provides a fully containerized proof of concept (PoC) demonstrating a Change Data Capture (CDC) pipeline using TimescaleDB, Kafka, Kafka Connect, and a JDBC Source Connector. [https://github.com/carlospsikick/timescale-cdc](https://github.com/carlospsikick/timescale-cdc) ## Change Data Capture 101 Change Data Capture (CDC) captures [database operations](https://www.tigerdata.com/learn/guide-to-postgresql-database-operations) (INSERT, UPDATE, DELETE) at the row level, converting these changes into structured events for immediate downstream consumption. Unlike traditional batch processes, CDC supports real-time data propagation. ![Change Data Capture Data Flow](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/Change-Data-Capture-Data-Flow-1.png) Change Data Capture Data Flow While tools like Debezium rely on transaction logs, requiring elevated privileges or logical replication, our approach employs PostgreSQL triggers. This method offers full control, simplicity, and compatibility with managed environments, seamlessly integrating with TimescaleDB. ## The Shoplogix IMS IIoT Ecosystem Our data model comprises sensor-generated time-series data (e.g., temperature, pressure, vibration) and metadata describing assets (such as types, serial numbers, and locations). Combined, these data streams offer a comprehensive view of the operational state of industrial assets, enabling visualization, anomaly detection, and predictive analytics. ![IMS Event Streams](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/IMS-Event-Streams-2.png) __IMS Event Streams__ As our business expands, we continuously evolve our ecosystem to support new sensor types, analytics, and integrations with external systems. Central to these developments is **Event Streaming**, which facilitates data transformations and redirections without impacting the broader infrastructure. **Change Data Capture (CDC)** is vital in this context, translating row-level database changes into real-time events that downstream systems can immediately process. ## Implementing CDC with Timescale ![IMS CDC Pipeline](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/IMS-CDC-Pipeline.png) IMS CDC Pipeline In our CDC pipeline the database changes triggered by APIs or microservices are captured in real time using database triggers and logged into a dedicated CDC times series schema. A **Kafka Connect JDBC Source Connector** polls these CDC Tables and Views, and streams the captured changes as events into Kafka topics. These event topics can then be consumed by various subscribers, enabling real-time data propagation across microservices, analytics platforms, and external systems in a decoupled and scalable manner. Let’s take a closer look: - A typical schema holds the tables, hypertables, and views that the APIs and microservices interact with. - **Metadata Tables** and **Time-Series Hypertables** are the primary sources of change. - Each table has associated PostgreSQL **triggers** that fire on INSERT, UPDATE, or DELETE events. - These triggers invoke a centralized **CDC PSQL Function** that resides in the **CDC schema.** ![CDC Implementation](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/CDC-Implementation-1.png) CDC Implementation - The **CDC Function** processes row-level changes and inserts structured change records into a dedicated **CDC Log time-series** [**Hypertable**](https://www.tigerdata.com/blog/database-indexes-in-postgresql-and-timescale-cloud-your-questions-answered). - This log acts as an append-only ledger of all data modifications across different tables. - The **CDC Log Hypertable** is the canonical source of truth for all captured changes. - Using a [**TimescaleDB Hypertable**](https://docs.timescale.com/use-timescale/latest/hypertables/) is important because it provides all the time-series data functionality: compression, retention policies, automatic partitioning, etc. - One or more **CDC Views (e.g., View A and View B)** expose subsets of the log tailored to specific use cases or consuming systems. - \- For example, View A could filter changes from assets, and View B from sensor anomaly data. - **Kafka Connect** runs a **JDBC Source Connector** that periodically polls these CDC Views. - Each view is mapped to a corresponding **Kafka topic** (e.g., Topic A and Topic B). - This allows downstream consumers to subscribe to granular, schema-specific change events. - Once data is published to Kafka, it’s available in real time to any **event-driven microservice**, **stream processor**, or **analytics pipeline**. - This architecture enables **low-latency, decoupled integration** between databases and consumers in an event-streaming ecosystem. ### The CDC Function **cdc.change\_data\_capture()** ![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/cdc-change-data-capture-3.png) This function serves as the core mechanism for capturing data changes—inserts, updates, and deletes—from any table that invokes it via a trigger. - Trigger Scope: Designed to be attached to a table with AFTER INSERT, AFTER UPDATE, or AFTER DELETE triggers. - Event Logging: When a row is inserted, updated, or deleted, the function executes. It logs the following into cdc.event\_log: - _\- ts_: The timestamp of the change (NOW()) - _\- schema\_name_: The schema of the table where the change occurred (TG\_TABLE\_SCHEMA) - _\- table\_name_: The table name (TG\_TABLE\_NAME) - _\- operation_: The type of operation (TG\_OP — either 'INSERT', 'UPDATE', or 'DELETE') - _\- before_: A JSON representation of the row before the change (row\_to\_json(OLD)) - _\- after_: A JSON representation of the row after the change (row\_to\_json(NEW)) - Return Value: Returns NEW, which is standard for triggers on INSERT or UPDATE, ensuring that the row modification proceeds. _Note: To capture events from a TimescaleDB Hypertable we have to change the function a little bit, but the functionality is the same. See the code repo for more details._ ### The CDC Event Log Hypertable The **cdc.event\_log** is the central audit and event tracking table. It stores detailed, structured records of every change captured by the cdc.change\_data\_capture() trigger function. - Unifies change events across all tables into a single schema - Is ideal for Kafka Connect JDBC polling ![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/cdc-event-log-hypertable-2.png) Defining the cdc.event\_log table as a Timescale hypertable has significant performance and scalability benefits for CDC workloads. It enables efficient time-based partitioning, making incremental polling and historical queries faster. Hypertables are optimized for high-throughput inserts, ideal for the append-only nature of CDC logs. Timescale also offers native features like automated data retention, compression through [columnar storage](https://www.tigerdata.com/blog/building-columnar-compression-in-a-row-oriented-database), and real-time analytics capabilities, allowing you to manage storage effectively and build responsive downstream applications. Importantly, this setup integrates seamlessly with tools like Kafka Connect, without altering your connector configuration. Column Description: - _ts_ (timestamp with time zone): The exact time the change occurred. This is useful for ordering events chronologically and for incremental polling in CDC pipelines. - _schema\_name_ (text): The name of the schema where the change originated. Helps identify the source context in multi-schema databases. - _table\_name_ (text): The name of the table where the row was modified. Enables routing of events to topic-specific or table-specific consumers. - _operation_ (text): The type of database operation: 'INSERT', 'UPDATE', or 'DELETE'. Used to interpret the semantics of the before and after fields. - _before_ (json): A JSON snapshot of the row before the change (used for UPDATE and DELETE). Null on INSERT. - _after_ (json): A JSON snapshot of the row after the change (used for INSERT and UPDATE). Null on DELETE. - _event\_id_ (bigint): A unique identifier for the event. This is generated by a SEQUENCE and is essential for incremental polling (e.g., using event\_id > last\_seen\_id). ### CDC Events Views Creating views like cdc.event\_log\_assets enables clean separation of events from a shared CDC log into table-specific or domain-specific streams. These views simplify Kafka topic routing. Mapping each view to a unique Kafka topic reduces downstream filtering, improving performance by narrowing the data scope for polling connectors. These views also provide a flexible layer for schema shaping, enrichment, and transformation, making the CDC pipeline more modular, scalable, and easier to maintain. ![CDC Events Views](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/CDC-Events-Views-1.png) ## CDC Trigger To begin capturing change events for a particular table, all we need to do is add the trigger function to its definition. For example, to monitor \`dataschema.assets\`: ![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2025/05/create-trigger-1.png) ## Key Takeaways - PostgreSQL triggers capture changes into a CDC schema.  - A central function logs structured changes to a TimescaleDB hypertable.  - CDC Views provide tailored change logs.  - Kafka Connect polls these views, streaming changes to Kafka. This enables low-latency, decoupled integration. ## Resources - [TimescaleDB on GitHub](https://github.com/timescale/timescaledb) - [Apache Kafka & Kafka Connect](https://kafka.apache.org) - [Apache Kafka JDBC Connector](https://github.com/Aiven-Open/jdbc-connector-for-apache-kafka) - [Proof of Concept Repo](https://github.com/carlospsikick/timescale-cdc)