Over the last year, we’ve noticed a strong pattern in customers who’ve migrated their analytics workloads to ClickHouse Cloud: After self-hosted ClickHouse, PostgreSQL is the most common source of migrations. ClickPipes made data replication and migrations easy for these use-cases. However, we found that users still face significant challenges migrating queries and application code from PostgreSQL to ClickHouse. To address this, a few months ago we started looking into ways to simplify and reduce the time required to migrate analytical queries from PostgreSQL to ClickHouse.
Today, we’re pleased to release pg_clickhouse v0.1.0, an Apache 2-licensed PostgreSQL extension to transparently execute analytics queries on ClickHouse directly from PostgreSQL.
Download pg_clickhouse from:
Or kick the tires by spinning up a Docker instance:
docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass -d ghcr.io/clickhouse/pg_clickhouse:18
Consider starting with the tutorial, or watch Sai run through the tutorial in the video below.
Consider the common case where an organization builds an application backed by Postgres, including not only business data and transaction processing, but logging and metrics, as well. As the product grows, user traffic and data volume exponentially increase. As a result, the analytical queries powering both real-time customer-facing features and observability systems begin to slow down.
Developers frequently mitigate these issues by using PostgreSQL read replicas, a provisional solution, at best. Eventually, they look to move the workload to a specialized analytics database such as ClickHouse. ClickPipes powers rapid data migration, but what to do with the existing PostgreSQL queries against that data, often created by SQL libraries or ORMs?
The time-consuming part isn’t moving data; ClickPipes has that bit nailed. It’s rewriting months or years of analytics SQL baked into dashboards, ORMs, and cron jobs.
We imagined a PostgreSQL extension to reduce the need to migrate those queries at all, where one could follow the data migration with the workload migration simply by pointing those queries at a new Postgres database or schema.
... continue reading