Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

blog.timescale.com

Post reply on HN
Print view View original

11–20 of 184 posts

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#11

Post by ryanbooz »

brightball wrote: post #8

That’s a really thorough comparison. Much more detailed than I expected. From what I see, the trade off in disk space usage would point me toward Timescale for most of my workloads. The insert performance tradeoff just wouldn’t justify the difference for me.

(Post author)

Thanks for the compliment! It's becoming a habit with us and benchmarks. We just really want to dig in and understand what's going on and why things work the way they do. ;-)

There really are so many nuances and as we tried to say a number of times, ClickHouse is really great at what it does well. But it's still OLAP at heart (which precludes OLTP features many apps take for granted) and after enabling TimescaleDB compression, the query story isn't as cut and dry. We don't claim that TimescaleDB is the fastest in all circumstances, or that it absolutely has to be for every workload. Features and versatility play a major part in the decision.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#12

Post by AdamProut »

eatonphil wrote: post #2

I was surprised to see that ClickHouse and ElasticSearch have the same number of contributors. That's pretty astounding given how much older and more prominent ElasticSearch has been. https://github.com/ClickHouse/ClickHouse/graphs/contributors https://github.com/elastic/elasticsearch/graphs/contributors Edit: I was very off. The Github contributor graph does not show all actual contributors. ElasticSearch has somewh…

Clickhouse is by far the leading open source columnar SQL data warehouse at this point. We have had strong open source operational SQL DBs for many years (MySQL, Postgres), but no open source systems that mirrored closed source MPP columnstore until clickhouse. Its interesting that it took "this long" for a strong open source SQL DW to emerge.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#13

Post by eatonphil »

rohitnair wrote: post #10
eatonphil wrote: post #2

I was surprised to see that ClickHouse and ElasticSearch have the same number of contributors. That's pretty astounding given how much older and more prominent ElasticSearch has been. https://github.com/ClickHouse/ClickHouse/graphs/contributors https://github.com/elastic/elasticsearch/graphs/contributors Edit: I was very off. The Github contributor graph does not show all actual contributors. ElasticSearch has somewh…

As per the landing pages of the projects, ES has 1.6k contributors whereas ClickHouse has 803. The contributors page likely only lists the top contributors to keep the page load time manageable.

That makes much more sense. Thanks for pointing that out.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#14

Post by hodgesrm »

eatonphil wrote: post #2

I was surprised to see that ClickHouse and ElasticSearch have the same number of contributors. That's pretty astounding given how much older and more prominent ElasticSearch has been. https://github.com/ClickHouse/ClickHouse/graphs/contributors https://github.com/elastic/elasticsearch/graphs/contributors Edit: I was very off. The Github contributor graph does not show all actual contributors. ElasticSearch has somewh…

ClickHouse now has more unique contributors with merged PRs on an annual basis. The lines crossed early this year, or even late last year.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#15

Post by claytonjy »

andrejserafim wrote: post #4

Our anecdata: we store telemetry per thing. After loading a month worth of data - timescaldb as hosted by their cloud ran a difference aggregation in seconds. Clickhouse routinely did it in 20 millis. Simple avg, etc were better, but always clickhouse was an order of magnitude faster than timescale. We didn't invest a whole bunch into optimization other than trying some indexing strategies in timescaledb. So for our…

Was this for your primary source-of-truth, or more of a downstream data warehouse, or something else?

I'm struggling to imagine a case where these are the two things being considered; Timescale is the obvious choice for a primary database, Clickhouse the obvious choice for a warehouse. I wouldn't let my user-facing app write to Clickhouse, and while I could potentially get away with a read-only Timescale replica for internal-facing reports I would expect to eventually outgrow that and reach for Clickhouse/Snowflake/Redshift.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#16

Post by eatonphil »

hodgesrm wrote: post #14
eatonphil wrote: post #2

I was surprised to see that ClickHouse and ElasticSearch have the same number of contributors. That's pretty astounding given how much older and more prominent ElasticSearch has been. https://github.com/ClickHouse/ClickHouse/graphs/contributors https://github.com/elastic/elasticsearch/graphs/contributors Edit: I was very off. The Github contributor graph does not show all actual contributors. ElasticSearch has somewh…

ClickHouse now has more unique contributors with merged PRs on an annual basis. The lines crossed early this year, or even late last year.

Thanks! Could you point me at something concrete. :)

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#20

Post by ryanbooz »

rkwasny wrote: post #17

There is some creative engineering going here :) have a look: https://github.com/timescale/tsbs/blob/master/scripts/load/l... vs https://github.com/timescale/tsbs/blob/master/scripts/load/l...

(Post author)

I'm not sure why you think that's creative engineering. What you're pointing to is the depth of available configuration that the contributors to TSBS have exposed for each database. It's totally open source and anyone is welcome to add more configuration and options! I believe (although not totally sure) that Altinity and ClickHouse folks added their code a few years ago - at least it wasn't anyone on the Timescale team.

That said, we didn't actually use those scripts to run our tests. Please join us next Wednesday (10AM ET/4PM CET) to see how we set the databases up and ran the benchmarks. We'd be delighted to have you try it on your own too!

Post reply on HN