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

71–80 of 184 posts

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

#71

Post by akulkarni »

dmw_ng wrote: post #59

More war stories: found Timescale easier to setup (maybe just because more familiar), but raw query perf is not something you just magically get for free. Timescale requires a lot of investment in planning. In one project we had simple time range scan queries against a less-than-RAM-sized table taking tens of seconds to complete. ClickHouse has a bit more ops overhead, but requires very little in the way of pre-plann…

(TimescaleDB co-founder)

Thank you for the feedback - it is conversations like this that enable us to understand how we can continue to make TimescaleDB better.

And some of the ideas you are discussion are on our roadmap - if anyone wants to help, we are hiring :-)

https://www.timescale.com/careers

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

#72

Post by akulkarni »

ryanbooz wrote: post #70
qaq wrote: post #55

ClickHouse shines at scales that timescale has no hope of ever supporting. Hence the choice of workloads in the test. Cloudflare was ingesting 6,000,000 rows per second into 36 node (dual E5-2630) ClickHouse cluster (2018) was something like 20PB of data per year.

(post author) Those are great, impressive numbers. We certainly don't claim to be all things to all people, but the benchmark was run using single instances mostly because that is what most other benchmarks published by others have done. With a multi-node TimescaleDB cluster, ingest does literally scale to millions of rows/second and we have a numerous users achieving great numbers. One Fortune 100 company has a 25+…

Minor correction: It is actually a 40+ node cluster :-)

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

#73

Post by claytonjy »

dominotw wrote: post #66
claytonjy wrote: post #15

Earlier quoted context omitted.

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…

> Clickhouse the obvious choice for a warehouse > Clickhouse/Snowflake/Redshift. but clickhouse is very unlike the other two. when i think of a warehouse i think star schema, data modeling ect not something that hates joins.

Agreed, I wouldn't use Clickhouse for usual warehouse stuff either, mostly because I can't imagine it plays well with dbt which is a non-starter these days.

I'd still argue Clickhouse is closer to Snowflake/Redshift than anything OLTP, and their name is intentionally chosen to evoke warehouse-like scenarios.

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

#74

Post by PeterZaitsev »

ericb wrote: post #30
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.

Good question. Was it Open Source version of TimeScaleDB compared or Source Available ?

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

#75

Post by sam0x17 »

GordonS wrote: post #41
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.

We're using TimescaleDB to store log events - so each row has a timestamp, some properties, and a log message. And a lot of that is actually in a JSONB column. Not the archetypal time series use case, but TimescaleDB is still really useful. TSDB's compression means we can store a huge volume of data in a fraction of the space of a standard Postgres table. You can achieve even better compression ratios and performance…

So services like Sentry / Honeybadger / etc probably use this architecture?

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

#76

Post by ryanbooz »

PeterZaitsev wrote: post #63

Altinity folks have suggested number of Clickhouse optimizations for time series benchmark did you enable any of those ? https://altinity.com/blog/clickhouse-continues-to-crush-time...

Hello @PeterZaitsev!

Actually Altinity is the one that contributed the bits to TSBS for benchmarking ClickHouse[1], so we are using the work that they contributed (and anyone is welcome to make a PR for updates or changes). We also had a former ClickHouse engineer look at the setup to verify it matched best practices with how CH is currently designed, given the TSBS dataset.

As for the optimizations in the article you pointed to from 2019 (specifically how to query "last point" data more efficiently in ClickHouse), it uses a different table type (AggregatedMergeTree) and a materialized view to get better query response times for this query type.

We (or someone in the community) could certainly add that optimization to the benchmark, but it wouldn't be using raw data - which we didn't think was appropriate for the benchmark analysis. But if one wanted to use that optimization, then one should also use Continuous Aggregates for TimescaleDB - ie for an apples to apples comparison - which I think would also lead to similar results to what we show today.

It's actually something we've talked about adding to TSBS for TimescaleDB (as an option to turn on/off) and maybe other DBs could do the same.

[1]: https://github.com/timescale/tsbs/pull/26

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

#77

Post by benwilson-512 »

We've got a few billion rows in TSDB, pretty happy with it so far. Our workload fits the OLTP workflow more than OLAP though, we're processing / analyzing individual data points from IoT devices as they come in, and then providing various visualizations. This tends to mean that we're doing lots of fetches to relatively small subsets of the data at a time, vs trying to compute summaries of large subsets.

Compression is seriously impressive, we see ~90% compression rate on our real world datasets. Having that data right next to our regular postgres tables and being able to operate on it all transactionally definitely simplifies our application logic.

Where I see a lot of folks run into issues with TimescaleDB is that it does require that your related data models hold on to relevant timestamps. If you want to query a hypertable efficiently, you always want to be able to specify the relevant time range so that it can ignore irrelevant chunks. This may mean that you need to put data_starts_at, data_ends_at columns on various other tables in your database to make sure you always know where you find your data. This is actually just fine though, because it also means you have an easy record of those min / max values on hand and don't need to hit the hypertable at all just to go "When did I last get data for this device".

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

#78

Post by bradstewart »

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.

We capture and store energy readings at ~5second intervals, then display total energy at various time granularity by aggregating the values over minutes, hours, days, months, etc.

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

#79

Post by ryanbooz »

PeterZaitsev wrote: post #74
ericb wrote: post #30

Earlier quoted context omitted.

A licensing comparison would be a good addition.

Good question. Was it Open Source version of TimeScaleDB compared or Source Available ?

Sure. As we shared in the blog post it was tested (like other benchmarks) on dedicated EC2 instances using the freely available Community version.

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

#80

Post by PeterZaitsev »

ryanbooz wrote: post #58
arunmu wrote: post #54

What is the difference w.r.t the comparison done by Altinity of clickhouse with timescale ? Clickhouse performed better there for the same test. What gives ?

(Post author) The two big things, which we discuss at length in the post, are: - Altinity (and others) did not enable compression in TimescaleDB (which converts data into columnar storage) and provides improvement in querying historical data because it can retrieve individual columns in compressed format similar to CH - They didn't explore different batch sizes to help understand how each database is impacted at vari…

Have you from your side followed all Clickhouse best practices?

Clickhouse design in particular suggests doing ingest request approximately once per second and if you do much more than that when you use it outside of intended usage and if you need that you usually have some sort of queue between whatever produces the data and Clickhouse.

Note the ingest in small batches also can significantly affect query performance

Post reply on HN