ClickHouse and PostgreSQL
Are your analytical queries in Postgres hitting performance limitations, scalability challenges, or becoming operationally complex? Discover why our customers are relying on ClickHouse to power these analytics use cases while freeing Postgres to do what it does best.
Why do developers choose ClickHouse?
PostgreSQL Performance Limitations
Developers report challenges with query run time and inflated disk sizes as big drains on the performance of their analytical queries running on Postgres.
ClickHouse is the fastest and most resource-efficient database for analytics.
Whether you’re aggregating large volumes of data in real-time, interactively slicing and dicing on the fly, or powering customer-facing applications, ClickHouse is blazingly fast, overcoming the performance limitations often seen with Postgres.
OONI, for example, were able to reduce their analytical query times from 20 minutes in PostgreSQL to milliseconds in ClickHouse while halving their storage requirements.
PostgreSQL Scalability Challenges
Scalability is an important consideration for any database system, including PostgreSQL. While PostgreSQL is known for its robustness and performance, it does face scalability challenges.
ClickHouse is designed to efficiently manage huge volumes of data.
ClickHouse scales both vertically and horizontally, ensuring the effective utilization of available resources. ClickHouse is relied on at scale by companies all over the world to process and store hundreds of petabytes of compressed data.
Cloudflare’s old data pipeline conceived in 2014, centered around scaling out Postgres for their analytics. “The pipeline had served us and our customers well over the years, but began to split at the seams.” They needed to find a scalable solution built for tomorrow.
PostgreSQL Operational Complexity
Optimizing query execution plans, indexing strategies, and database configuration parameters requires expertise and ongoing monitoring to address performance bottlenecks.
ClickHouse is purpose-built for real-time, large-volume, data analytics.
With ClickHouse, there is no need for the operational complexity that exists when retrofitting another system for these real-time workloads. With ClickHouse, real-time just works.
MUX’s old Postgres-based pipelines required manual oversight. “If too many jobs that landed on the same Postgres shard happened to run at the same time, the cluster could grind to a halt. This required manually stopping all of the other jobs and babysitting them one-at-a-time until they all completed.” ClickHouse solved these issues and more.
“With all of this in mind, we started by keeping the [ClickHouse] architecture as simple as possible: just dump all of the data into one table called ‘views’. No Airflow. No aggregation. No stored procedures. Any bucketing logic would be written in the SQL queries at read-time and computed on the fly. To our surprise, this worked extremely well.”
What developers say about ClickHouse and PostgreSQL
“We were using Postgres and Postgres just wasn't scaling for those nice dashboards. In order to get these nice dashboards, you need to do all these aggregation calls. Aggregations were taking more than 30 seconds and things were just timing out.”
“We realized we had so much data in Postgres, which was taking forever to process, so we started moving it to ClickHouse. It is instant in ClickHouse vs forever in Postgres.”
“Eventually, [our] all-purpose Postgres database was tasked to store millions of rows of data. It was obvious Postgres couldn't handle the scale necessary for an analytics platform like PostHog. At first, the team tried a ton of hack-y and wacky solutions in attempts to get Postgres to work. Turns out, that wasn’t sustainable (who would’ve thought!). Eventually, PostHog migrated client data to ClickHouse. Boom!”
“It became clear early on that the original architecture using Postgres to store analytics data could not handle the platform’s future growth.”
“We still use PG [Postgres] for non-royalty data such as customer account data and metadata. Fortunately, ClickHouse makes it relatively easy to connect to PG databases.”
“The [ClickHouse] table engines for integrations are great features that allow for direct connections to other relational stores like MySQL or Postgres.”
“Another useful feature of ClickHouse is that it can be easily connected to Postgres, allowing us to use it in our observability queries … We can join the user and tenant IDs in our spans to the actual account names and account status in the Postgres database.”
“We scaled [Postgres] out to a fleet of machines but were burdened with a suite of problems that throwing hardware at just could not resolve. We needed a way to reduce infrastructural work whenever a new dimension of data was discovered, not a way to scale the current dataset.”