Thread by @ryanbooz

1/ There's a question that we’ve been hearing a lot lately: how does @ClickHouseDB compare to @TimescaleDB for time series data? 🤔📊

After spending *many* weeks learning about ClickHouse, we finally have an answer for you… 👇

Read the full story: tsdb.co/clickhouse-ben…

2/ ClickHouse is an open-source columnar #OLAP database that was initially built for web analytics in Yandex Metrica. ClickHouse is known for its high insert rates, fast analytical queries, and #SQL-like dialect.

3/ Knowing this, it’s no surprise that people have been asking us how does ClickHouse compares to #PostgreSQL and TimescaleDB for time-series data💡But at Timescale, we take our comparisons very seriously… So we took some time (ahem, 3 months of testing!) before answering 🤓

4/ Developers deserve more benchmarking and less “bechmarketing”. Working on this comparison, we’ve spent hundreds of hours testing, reading documentation, and working with ClickHouse contributors 🤝

5/ Before sharing our conclusions, we’ll say this crystal clear: ClickHouse is an impressive piece of technology 🙌 ClickHouse proved to be a blazing fast database, especially in inserts and complex grouping aggregations.

6/ But in database design, nothing comes for free. ClickHouse is a great database for certain workloads, but not the best choice for others... And as a developer, the most important thing is to choose *the right tool for your workload*.

7/ Specifically, the ClickHouse architecture gives it great performance for OLAP-type workloads - but it implies significant tradeoffs that can be crucial for many use cases 👎

8/ A few examples of these tradeoffs:

➡️ Asynchronous deletes and updates
➡️ Lack of backup consistency
➡️ Non-standard SQL
➡️ No transactions
➡️ No user-defined functions
➡️ No index management beyond primary and secondary indexes
➡️ No triggers

...and there's more.

9/ #PostgreSQL is a versatile, fully reliable database with a vast ecosystem of tools 💪 and TimescaleDB optimizes it for time series. 🔥

If you need a database to power applications with many users + frequent updates/deletes, @TimescaleDB is a better choice than @ClickHouseDB.

10/ Now that we’ve set up the basics, we’re in a better place to understand our benchmark results 🎉 (By the way, our benchmarking suite is public - we welcome you to reproduce everything discussed here!) tsdb.co/github-tsbs

Keep reading for a summary of our findings 👇

11/ 👀 @ClickHouseDB vs @TimescaleDB: insert performance 👀 (I) >>

Overall, we found that ClickHouse outperforms TimescaleDB on inserts with large batch sizes…

12/ @ClickHouseDB vs @TimescaleDB: insert performance 👀 (II) >>

...but when the batch size is smaller, the results are reversed. Not only does TimescaleDB maintain steady, faster insert speeds than ClickHouse, but ClickHouse disk usage is 2.7x higher than TimescaleDB.

13/ @ClickHouseDB vs @TimescaleDB: Query performance 👀 >>

To our surprise, TimescaleDB was faster for the majority of the queries we tested - all except for complex aggregates. 🤯

14/ In a nutshell:

🏗️ @ClickHouseDB is like a bulldozer: very efficient and performant for a specific use-case. If you're doing lots of construction, get a bulldozer!

🚗 Instead, #PostgreSQL (& @TimescaleDB) is like a car: versatile, reliable, and useful for most situations.

15/ To better understand our findings, please read the entire article! 🙏 You will find not only a detailed description of our benchmarking, but also a deep dive into the ClickHouse architecture: tsdb.co/clickhouse-ben…

16/ If you want to try TimescaleDB, the easiest way is to sign up for a fully-managed Timescale Cloud instance (100% free for the first 30 days!) 👉 tsdb.co/ts-cloud-signup