Streaming PostgreSQL CDC to ClickHouse for Real-Time Banking Analytics

PostgreSQL excels at transactional workloads but struggles when analytical queries—requiring large scans and aggregations—run against production data, impacting application performance. To address this, organizations replicate operational data to dedicated analytics systems. However, batch ETL introduces latency. Change Data Capture (CDC) offers a better approach: it captures changes directly from PostgreSQL and streams them to downstream systems in real time, keeping analytics synchronized with production while minimizing source database load.

In this guide, we'll use Estuary to stream CDC events from a PostgreSQL database containing banking transaction data into ClickHouse, enabling real-time analytics on transaction activity, account balances, and customer behavior. This architecture allows PostgreSQL to remain the source of truth for transactional operations while ClickHouse serves as the analytics engine, delivering fast insights without affecting production workloads.

To stream PostgreSQL banking data to ClickHouse in real time, use Change Data Capture (CDC) to capture row-level inserts, updates, and deletes and continuously materialize them into ClickHouse. In this tutorial, you’ll build that pipeline with Estuary using a simulated banking database containing customers, accounts, merchants, and transactions.

PostgreSQL will remain the operational source of truth, while ClickHouse handles analytical queries and dashboards. You’ll also verify that PostgreSQL updates reach ClickHouse and build a dashboard for transaction and suspicious-activity monitoring.

By the end of this guide, you will have:

• A working PostgreSQL-to-ClickHouse CDC pipeline.
• Banking tables continuously materialized into ClickHouse.
• A repeatable method for verifying replicated updates.
• A ClickHouse dashboard for banking transaction and fraud monitoring.

Why Stream Postgres CDC Data to Clickhouse? A Banking Perspective

When streaming transactional data from PostgreSQL to ClickHouse, the right strategy depends on your use case, infrastructure, and data requirements. In general, real-time Change Data Capture (CDC) is the best approach for banking systems, while manual bulk loading followed by periodic updates is suitable for historical migrations or one-time data loads.

This architecture provides several critical benefits for banking operations:

Real-time fraud detection: CDC streams transactions and account updates as they occur, enabling fraud detection systems to analyze patterns and flag suspicious activity instantly rather than waiting for batch reports the next morning.

Instant compliance reporting: ClickHouse's columnar architecture excels at aggregations and complex queries on massive transaction datasets—generating regulatory reports, risk assessments, and audit logs orders of magnitude faster than querying production PostgreSQL.

Cost effective at scale: ClickHouse handles millions of daily transactions and years of historical data at a fraction of the infrastructure cost required to scale PostgreSQL for both transactional and analytical workloads.

Operational resilience: PostgreSQL remains focused on serving customer-facing transactions—deposits, withdrawals, account inquiries—at full speed, while ClickHouse handles all reporting, compliance dashboards, and business analytics. Neither workload interferes with the other, ensuring customers experience zero latency while the bank gains real-time visibility into operations.

Our Use Case: A Real-Time Banking Analytics Pipeline

In this guide, we will use a simulated banking transactional database in PostgreSQL containing customers, merchants, accounts, and transactions. As new transactions are inserted, account details are updated, or transaction statuses change, PostgreSQL CDC will capture those changes and stream them into ClickHouse in real time using Estuary. Once the data lands in ClickHouse, it can be used to analyze customer behavior, monitor transaction patterns, detect potential fraud activity, and power dashboards that reflect the latest changes from the operational database. 

This setup demonstrates how PostgreSQL can remain the source of truth for transactional data while ClickHouse handles fast, high-volume analytical queries.

PostgreSQL CDC to ClickHouse Architecture

PostgreSQL acts as the source of truth for banking transaction data, while Estuary captures inserts, updates, and deletes from PostgreSQL using CDC. These change events are then streamed into ClickHouse, where the data is optimized for analytical queries. ClickHouse serves as the real-time analytics layer, supporting dashboards for transaction volume, customer activity, merchant trends, and potential fraud detection without adding analytical load to PostgreSQL.

Prerequisites

Before getting started, make sure you have the following:

1. An Estuary account to capture PostgreSQL change data and stream it downstream into ClickHouse.
2. A ClickHouse Cloud account to serve as the analytics destination.
3. Docker installed to run the sample PostgreSQL environment.
4. An ngrok account. Since the PostgreSQL database will run locally in Docker, you'll need a secure way to expose it to the internet so Estuary can connect to it. Ngrok creates a public tunnel to your local database.
5. Clone this GitHub repository which contains the Docker environment and sample banking workload that continuously generates transactions in PostgreSQL.

Once these prerequisites are in place, you're ready to configure PostgreSQL for Change Data Capture (CDC) and stream the data into ClickHouse.

Stream PostgreSQL CDC to ClickHouse: Step by Step Guide

Step 1: Set up PostgreSQL with CDC Using Docker

To demonstrate PostgreSQL CDC in a realistic scenario, we'll first create a simulated banking dataset and load it into a local PostgreSQL instance running on our machine using Docker. This local PostgreSQL database will serve as the operational source system for the pipeline, storing customers, merchants, accounts, and transaction records.

Clone this repository, which contains the PostgreSQL setup required to demonstrate the CDC pipeline for the banking data. The repo includes an init.sql file which initializes the banking tables, creates the Estuary CDC user, grants the permissions for logical replications, watermark table and publication, and adds the banking table to the publication. For more information on configuring PostgreSQL for Estuary, refer to the linked documentation. After running the init.sql file, the PostgreSQL database is ready to be configured as a capture source in Estuary. 

Finally, from the root directory of the repository, run the following command in your terminal:

bash
docker compose up --build

This should start streaming some logs about the database and the data generation service into your terminal. The data stream will also create a new banking transaction every 10 seconds to simulate a live banking application and configure Postgres CDC through the creation of PostgreSQL publication for logical replication. You should see the log messages look similar to this:

To check your output in Postgres, run the command:

bash
docker exec -it banking_postgres psql -U postgres -d banking_db

View the Seeded Table: 

sql
\dt banking.*

SELECT COUNT(*) FROM banking.transactions;

Step 2: Expose your Local Postgres database to the internet via ngrok

The next step is to make the database available for other services. To do this in one quick command, we can use ngrok, a free CLI tool that enables tunneling of services.

In the new terminal, expose the Postgres database to the internet by running the following:

bash
ngrok config add-authtoken $YOUR_AUTHTOKEN
ngrok tcp 5432

This should start the ngrok tunnel and show you the following screen:

Take note of the first URL in the Forwarding section, the one that begins with tcp://. You’ll need this in the next step.

Test that your database was exposed to the internet correctly by running the following command in a new terminal: 

bash
docker run --rm -it -e PGPASSWORD=postgres postgres:16 psql -h 6.tcp.ngrok.io -p 16313 -U postgres -d banking_db

Replace the link after -h below with the link from the previous step. The port (what comes after -p) is the portion of the URL that follows the colon. For example, in the ngrok screenshot above, the port is 16313. Note that the password is postgres, by default.

This should open a prompt with a connection to Postgres:

Next, run the following commands to see you have the orders and flow_watermarks tables, as expected, and that the orders table is being updated with new rows every 5 seconds:

Step 3: Add the Postgres database as a source in Estuary

1. Go to the Sources page in Estuary,
2. Click on New Capture.
3. Select PostgreSQL (Real time) from the list of possible connectors.
4. This should open a new page asking for some details for your Postgres database as follows: 

• Server Address: is the ngrok host url from the previous step when running ngrok tcp 5432. Don’t forget to include the port, in the format <host>:<port>
• Use the following connection details (as defined in the init.sql script):
  • Username: flow_capture
  • Password: secret
  • Database: banking_db
• Under Advanced Options, optionally specify the publication name, replication slot name, and watermark table that were defined earlier in the init.sql script.
• Click Next.

5. Under Target Collections, you should now see your tables.

6. Press Next and then Save and publish on the top right of the screen.
7. You can now view your tables on COLLECTIONS and verify that there's been some data written into it.

Step 4: Set up ClickHouse as destination 

We will use ClickHouse Cloud as a data warehouse for storing the CDC data captured from Postgres.  We will be using Estuary's native ClickHouse connector to capture changes from PostgreSQL and materialize them directly into ClickHouse with minimal configuration and operational overhead.

1. Create (or open) a ClickHouse Cloud service

Before materializing your collection to ClickHouse, you will need your Host URL.
In your ClickHouse UI, under Connect: 

2. Once you click on it, copy the host URL: 

• Note the host (looks like abc123.us-east-1.aws.clickhouse.cloud)—the native protocol port is 9440 by default.
• In ClickHouse, run the following command:

sql
CREATE DATABASE IF NOT EXISTS banking;

3. In Estuary's dashboard, go to the Destinations page, click on + New Materialization, and select the first option for Clickhouse, which runs in Real-time.

4. Fill out details in the Materialization, Endpoint, and Source Collections sections:
   1. Materialization Details: Provide a unique name for your materialization and choose a data plane (cloud provider and region)
   2. Source Collections: Link an existing capture to expose to ClickHouse
   3. Name of the ClickHouse database to materialize to.
   4. Username and password for Authentication
   5. Address: Host and port of the database (in the form of host[:port]). ClickHouse Host is the Host url from above.
      1. “<CLICKHOUSE_HOST>:9440” (Default is 9000 if SSL is disabled, 9440 if SSL is enabled)
5.  Once the connection is successful it will write the tables into Clickhouse:

Output: 

Here you can see the transactions table written into ClickHouse:

Applying and Verifying CDC Updates

To verify that CDC is working correctly, update a record in your local PostgreSQL database and then run the following command in your terminal to confirm the changes have been replicated to ClickHouse.

bash
docker exec -it banking_postgres  psql -U postgres -d banking_db

And update one customer’s information, such as the city and state:

You can see in your ClickHouse table automatically: city and state have changed.

Step 5:  Creating a Fraud Detection Dashboard in ClickHouse

Once the CDC pipeline is running, the data stored in ClickHouse can be used to power real-time analytics and dashboards. While many organizations connect ClickHouse to external business intelligence tools such as Power BI, Looker, and Tableau for visualization, ClickHouse Cloud also provides built-in dashboard capabilities.

By writing SQL queries directly in the ClickHouse SQL console, you can create charts, tables, and KPI widgets without relying on an external BI platform. Because ClickHouse is continuously receiving CDC events from PostgreSQL through Estuary, these visualizations automatically reflect the latest transactions, account updates, and customer activity in near real time.

Once the banking transaction data is continuously flowing into ClickHouse, we can create dashboards to monitor suspicious activity in real time. The dashboard below demonstrates how ClickHouse can be used to visualize key fraud detection metrics directly from CDC-powered transaction data without requiring an external BI tool.

• Suspicious Transaction Count tracks the total number of transactions flagged as suspicious.
• Suspicious Volume Count measures the total dollar value associated with suspicious or high-risk transactions.
• Declined Transactions shows the number of transactions that were rejected, which can be an early indicator of fraudulent activity.
• Suspicious Volume by Channel breaks down suspicious transaction amounts across payment channels such as card, mobile, ATM, and web transactions.
• Top Risky Merchants highlights merchant ids associated with the highest number of suspicious or high-value transactions.

Because ClickHouse is continuously receiving CDC events from PostgreSQL through Estuary, every insert, update, or status change made in the banking database is automatically reflected in these dashboard metrics and visualizations. This enables near real-time fraud monitoring without impacting the performance of the operational PostgreSQL database.

Key Benefits of This Architecture

By combining PostgreSQL, Estuary, and ClickHouse, organizations can build a scalable real-time analytics platform without compromising operational performance.

• Real-Time Analytics: Changes made in PostgreSQL are streamed to ClickHouse as they occur, enabling dashboards and reports to reflect the latest transaction activity with minimal latency.
• Reduced Load on PostgreSQL: Analytical queries are offloaded to ClickHouse, allowing PostgreSQL to focus on transactional workloads such as processing payments, account updates, and customer interactions.
• Scalable CDC Pipeline: Estuary continuously captures inserts, updates, and deletes from PostgreSQL, eliminating the need for scheduled batch ETL jobs and reducing data movement overhead.
• Improved Fraud Detection: Real-time access to transaction data makes it possible to identify suspicious activity, unusual spending patterns, and high-risk merchants as events occur.
• Separation of Operational and Analytical Workloads: PostgreSQL remains the system of record for transactional data, while ClickHouse serves as a dedicated analytics engine, improving reliability and performance across both systems.
• Built-In Visualization Options: ClickHouse can be connected to BI platforms such as Power BI, Tableau, and Looker, or used directly through ClickHouse Cloud's native dashboarding capabilities.
• Future-Proof Architecture: Because Estuary supports multiple destinations, the same CDC stream can later be extended to data warehouses, data lakes, AI applications, and event-driven systems without redesigning the pipeline.

Conclusion

In this guide, we built a real-time PostgreSQL to ClickHouse pipeline using Estuary and ClickHouse Cloud to continuously replicate transactional banking data through Change Data Capture (CDC). With only a few configuration steps, changes made in PostgreSQL—including inserts, updates, and deletes—were automatically streamed into ClickHouse, where they became immediately available for analytical queries. Using our customer transactions example, we demonstrated how CDC-powered analytics can be used to monitor transaction activity, track customer behavior, identify suspicious transactions, and power real-time fraud detection dashboards.