Simple Postgres to ClickHouse replication featuring MinIO
At PeerDB, we provide a fast and cost-effective way to replicate data from Postgres to Data Warehouses such as Snowflake, BigQuery, ClickHouse, and queues like Kafka, Red Panda and Google PubSub, among others.
A few months ago, we added a ClickHouse connector for Postgres Change Data Capture (CDC). Surprisingly, this connector gained substantial traction and adoption within our community. This applies to both our fully managed service (PeerDB Cloud) and our Open Source offerings. Here is a customer story from one of our customers who uses the ClickHouse connector.
The Problem
However, there was one common piece of feedback from many of our Open Source users. The ClickHouse connector required an S3 bucket as a prerequisite, which added additional overhead for users. Non-AWS users and those without immediate access to S3 could not use the ClickHouse connector. This wasn't a problem in our fully managed offering (PeerDB Cloud), as we abstracted away the S3 bucket creation from our customers.
This blog describes how we solved this problem and made it extremely easy for our users replicating data from Postgres to ClickHouse. We used MinIO, the open source S3 alternative, to stage the intermediary Avro files as part of the Change Data Capture (CDC) from Postgres to ClickHouse.
Why does the ClickHouse connector need S3?
Under the hood, PeerDB uses the Avro format for data in transit while replicating data from Postgres to Data Warehouses. Loading Avro files through Go wasn't trivial as the clickhouse-go driver didn't support Avro ingestion. Additionally, ClickHouse has native integration for loading data from S3 and is very efficient at it, as it attempts to parallelize as much work as possible, processing files in a streaming fashion. Therefore, we chose to use S3 as an intermediary storage for Avro files before importing them into ClickHouse.
This method has proven effective, allowing users to efficiently replicate data from Postgres to ClickHouse with latencies under 30 seconds and high throughput rates.
MinIO helps make the PeerDB's ClickHouse Connector Seamless
By integrating MinIO container services into our Docker Compose files for our Open Source offering, we've enabled an in-house S3-compatible storage solution that launches seamlessly with PeerDB. PeerDB uses environment variables to manage S3 bucket credentials, allowing for easy integration. Users can set these variables to match the MinIO bucket parameters, or they can plug in their own S3 bucket details. These parameters default to the packaged MinIO bucket parameters, as a result, users no longer need to provide a separate bucket for PeerDB’s ClickHouse integration, simplifying the setup process significantly.
A huge shoutout to MinIO for building a solid product that serves as an open source alternative to S3. Integrating MinIO's Docker container within PeerDB's Docker file was a one-week project. MinIO's APIs, being fully compatible with S3, allowed for seamless integration with PeerDB and ClickHouse.
Result: Even simpler Postgres to ClickHouse replication with PeerDB.
Simplifying ClickHouse Peer Creation with Optional S3 Configuration
Integrating the MinIO Docker Container in our Open Source offering eliminates the need for users to specify S3 buckets to use our ClickHouse connector. While creating the ClickHouse Peer, adding S3 information is optional, as shown in the screenshot below.
Set Up a Postgres to ClickHouse Mirror in Under a Minute
Once the Postgres and ClickHouse Peers are created, users can create MIRRORs to replicate data from Postgres to ClickHouse within a minute. See below video:
Use the MinIO Console for complete visibility into internal staging
MinIO also comes with a sleek UI that helps you manage the internal Avro files PeerDB creates as part of the replication process.
We hope you enjoyed reading the blog. If you're a ClickHouse user and wish to replicate data from Postgres to ClickHouse using PeerDB, please check out the links below or reach out to us directly!