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

21–30 of 184 posts

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

#21

Post by yumraj »

carlotasoto wrote: post #6
cercatrova wrote: post #5

Has anyone else been seeing an influx of timescale.com articles? I count around 10 in the last month.

(Timescale Team member here) We've been working really hard on our launches / releases this month! We called it "Always Be Launching" - we've been aiming for releasing multiple things per week during October :)

That sounds great.

However, as a DB where users may store critical data, should you really be "Always be launching"? That sounds a little like FB's "move fast and break things". There's a reason why some of the mission critical open source technologies move slowly.

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

#22

Post by hodgesrm »

eatonphil wrote: post #16
hodgesrm wrote: post #14

Earlier quoted context omitted.

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. :)

Go to one of the several public ClickHouse endpoints and run this query:

  -- Elastic vs CH in a single table. 
  SELECT toYear(created_at) Year, 
         uniqIf(creator_user_login, repo_name in ('elastic/elasticsearch')) "Elastic", 
         uniqIf(creator_user_login, repo_name in ('yandex/ClickHouse', 'ClickHouse/ClickHouse')) "CLickHouse" 
  FROM github_events 
  WHERE event_type = 'PullRequestEvent'
  AND merged = 1
  AND repo_name in ('yandex/ClickHouse', 'ClickHouse/ClickHouse', 'elastic/elasticsearch')
  GROUP BY Year ORDER BY Year
You can access the ClickHouse web UI for this dataset here: https://github.demo.trial.altinity.cloud:8443/play?user=demo. The password is "demo" (type it in the left side.) This is the Altinity.Cloud copy of Alexey Milovidov's excellent github_events dataset.

When I run this query I get the following numbers.

  Year|Elastic|CLickHouse|
  ----|-------|----------|
  2015|    191|         0|
  2016|    299|        40|
  2017|    296|        85|
  2018|    284|       142|
  2019|    341|       232|
  2020|    339|       300|
  2021|    243|       294|
Just speculation on my part but the drop Elastic contributors may be a side effect of the licensing change.

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

#24

Post by joshxyz »

sam0x17 wrote: post #18

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.

- https://clickhouse.com/docs/en/faq/use-cases/time-series/

- https://clickhouse.com/docs/en/faq/general/olap/

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

#25

Post by LoriP »

sam0x17 wrote: post #18

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.

Here's some posts from users on the Timescale blog https://blog.timescale.com/tag/dev-q-a/

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

#26

Post by rkwasny »

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...

Also this queries are different? order by "time" vs order by "created_at"

https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405...

https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405...

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

#27

Post by rkwasny »

ryanbooz wrote: post #20
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 t…

Ah so the tests you have used are not the ones in https://github.com/timescale/tsbs ?

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

#28

Post by hodgesrm »

didip wrote: post #19

Question, which helm chart is the best to install ClickHouse these days?

Don't use helm. The ClickHouse Kubernetes Operator is the way to go. Here's the project: https://github.com/Altinity/clickhouse-operator

This is generally true for most databases these days. Use an operator if it's available. Helm can't handle the dynamic management required to run databases properly.

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

#29

Post by GordonS »

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…

How many data points were those aggregations being computed over? How much memory does your Postgres server have, and are you using SSD storage (with associated postgres config tweaks)?

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

#30

Post by ericb »

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.

A licensing comparison would be a good addition.
Post reply on HN