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

121–130 of 184 posts

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

#121

Post by ryanbooz »

qaq wrote: post #94
ryanbooz wrote: post #70

Earlier quoted context omitted.

(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+…

and then it will come down to spec of nodes actual fields and so on etc. Also batch size obviously plays a big role here as CH is optimized for very large batch sizes and benchmark is not really using that kind of batch size. BTW. I am not involved with CH but any kind of vendor benchmarking their wears will always select params that will make their offering look good

Sure, these tests were not using really large batch sizes because of the other benchmarks we were trying to replicate (but with more detail). Honestly, for this single instance setup, we saw improvement in CH when we went from (say) 5k, 10k, or 20k batches. But it was a few percentage points at a time, not a magnitude different. I'm sure things changes with a cluster setup too, that just wasn't the focus of this post.

Interestingly, we were just testing a multi-node TimescaleDB cluster the other day and found that 75k rows/batch was the optimal size as nodes increased.

So you're completely correct. I tried to be very clear that we were not intentionally "cooking the books" and there's surely other optimizations we could have made. Most of the suggestions so far, however, require further setup of CH features that haven't been used in other benchmarks, so we tried to over communicate our strategy and process.

We also fully acknowledged in the post that an siloed "insert", wait, then "query" test is not real world. But, it's the current way TSBS has been used and other DB engines have come along and used the methodology for now. Maybe that process will change in time to come with other contributions.

BTW, we'll discuss some of this next week during the live-stream and the video will be available after.

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

#122

Post by Upitor »

Would either of these database systems be proper for a case where you have a mix of large measurement data and small reference/master data that you need to join, filter, etc. ?Example:

SELECT r.country, m.time, SUM(m.measurement) FROM measurement_table AS m INNER JOIN refence_table AS r ON m.device_id = r.device_id

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

#123

Post by ryanbooz »

PeterZaitsev wrote: post #111
mfreed wrote: post #105

Earlier quoted context omitted.

Yep! The blog post includes data and graphs from both large (5000-15,000 rows / batch) and small (100-500 rows / batch) sizes. Please see the section "Insert Performance". Thanks! https://blog.timescale.com/blog/what-is-clickhouse-how-does-...

1) This is also small batch size. If you're inserting 500.000 rows/sec 5000 rows is not particularly large batch size 2) I see different graphs for ingest but not for queries. The data layout will depend on the batch size, unless of course you did OPTIMIZE before running queries

1) you're absolutely right. 5k rows isn't "large". We also mentioned that we did hundreds of tests often going between 5k and 15k rows/batch. The overall ingest/query cycle didn't change dramatically in any of these. That is, 5k rows was within a few percentage of 10k rows. Interestingly, the benchmarks that Altinity has, only used 10k rows/sec (which we also did, it just didn't have any major impact in the grand scheme of things).

2) We did not specifically call OPTIMIZE before running queries. Again, learning from the leaders at Altinity and their published benchmarks, I don't see any references that they did either, and neither does the TSBS code appear to call it after ingest.

Happy to try both of these during our live stream next week to demonstrate and learn!

Altinity benchmark (10k rows/batch mention): https://altinity.com/blog/clickhouse-for-time-series

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

#124

Post by smarx007 »

CodesInChaos wrote: post #114

Can somebody recommend a database suitable for an event sourced application: * One series of events per user * Each series grows at about 10 events/minute while the user is active * Fancy queries are not required, typically a user's event series is consumed in order to update aggregate state for that user * Either used online, adding events one at a time and needing to immediately update the aggregate state * Used of…

10 events/minute - I would guess SQLite/DuckDB can fit the bill for many years?

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

#125

Post by jedberg »

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.

At Netflix all of our monitoring was in a time series database so we could get real time insights into pretty much anything we were monitoring (which was most everything).

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

#126

Post by [deleted] »

smarx007 wrote: post #124
CodesInChaos wrote: post #114

Can somebody recommend a database suitable for an event sourced application: * One series of events per user * Each series grows at about 10 events/minute while the user is active * Fancy queries are not required, typically a user's event series is consumed in order to update aggregate state for that user * Either used online, adding events one at a time and needing to immediately update the aggregate state * Used of…

10 events/minute - I would guess SQLite/DuckDB can fit the bill for many years?

[deleted]

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

#127

Post by ryanbooz »

Upitor wrote: post #122

Would either of these database systems be proper for a case where you have a mix of large measurement data and small reference/master data that you need to join, filter, etc. ?Example: SELECT r.country, m.time, SUM(m.measurement) FROM measurement_table AS m INNER JOIN refence_table AS r ON m.device_id = r.device_id

In it's current form/state, ClickHouse is not optimized for typical JOIN-type queries, a point we make in the post. You would have to re-write your statement to get better performance. The other main point is that all data is "immutable", so if your reference data needs to be updated, it would still need to go through some kind of asynchronous transform process to ensure you're getting the correct values at query time.

TimescaleDB is PostgreSQL, so it can easily handle this kind of join aggregate like you would expect. If "m.measurement" was compressed, historical queries with a time predicate would likely be faster than uncompressed state.

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

#128

Post by CodesInChaos »

smarx007 wrote: post #124
CodesInChaos wrote: post #114

Can somebody recommend a database suitable for an event sourced application: * One series of events per user * Each series grows at about 10 events/minute while the user is active * Fancy queries are not required, typically a user's event series is consumed in order to update aggregate state for that user * Either used online, adding events one at a time and needing to immediately update the aggregate state * Used of…

10 events/minute - I would guess SQLite/DuckDB can fit the bill for many years?

I don't think performance is the issue, but I'd like to keep the storage small/cheap via compression, since this is a hobby project. (though admittedly it's probably premature optimization)

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

#129

Post by hodgesrm »

claytonjy wrote: post #73
dominotw wrote: post #66

Earlier quoted context omitted.

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

Is your comment on ClickHouse and DBT based on using the DBT ClickHouse plugin? [0] If so I would be very interested in understanding what you or others see as deficiencies.

[0] https://github.com/silentsokolov/dbt-clickhouse

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

#130

Post by ryanbooz »

CodesInChaos wrote: post #114

Can somebody recommend a database suitable for an event sourced application: * One series of events per user * Each series grows at about 10 events/minute while the user is active * Fancy queries are not required, typically a user's event series is consumed in order to update aggregate state for that user * Either used online, adding events one at a time and needing to immediately update the aggregate state * Used of…

Looks like you edited this with some more detail, so I'll answer higher.

Compression in TimescaleDB used to mean all compressed data was immutable and the table schema couldn't be altered. Since TimescaleDB 2.1, and 2.3 that has changed in a few ways.

- Schema can now have columns added or renamed - Compressed chunks can now have rows inserted into them (partially compressed, the background job will fully compress when it runs)

Row deletion is not possible yet, but I've personally been having some internal conversations around ways to do exactly as you're suggesting in the near-term; deleting rows based on a "segmentby" column. I have some testing to do... but my focus has been taken up by a certain, 33-minute long read, blog post.

Feel free to join our Slack and DM me if you want to talk about it further.

slack.timescale.com

Post reply on HN