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

151–160 of 184 posts

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

#151

Post by david38 »

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.

Clickhouse is nothing like snowflake. A Postgres user can be useful in Clickhouse in minutes, not even close for snowflake.

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

#152

Post by hodgesrm »

akulkarni wrote: post #148
csdvrx wrote: post #146

Earlier quoted context omitted.

> This is also a funny statement, because TimescaleDB is built on PostgreSQL. I know, but doing timeseries with postgres is "cool", not standard, not boring. I'd even say "risky". > We actually take great pride in being a "boring" option No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more…

> No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used This is not true at all, and we explain why in the post: 1. TimescaleDB's reliability is PostgreSQL's reliability. ClickHouse has a lot of advantages, but "more reliable than PostgreSQL" is not one of them. From the post: P…

You are right about transactional differences between ClickHouse and PostgreSQL but you are comparing apples and oranges. ClickHouse prioritizes speed, efficiency, and scale over consistency. These are reasonable choices, especially in the largely append-only use cases which dominate analytics.

1. I've seen relatively few messed up source tables and mat views over thousands of support cases. When they happen they can be bad for some use cases like financial analytics. They simply aren't very common. And for use cases like observability or log management it just doesn't matter to have a few lost or duplicated blocks over huge datasets.

2. ClickHouse overall is eventually consistent. There are generally differences between replicas when load is active, yet it causes relatively few practical problems in most applications as they load balance queries over replicas. Serialization is expensive and simply not very highly valued here.

3. ClickHouse uses other mechanisms than ACID transactions to ensure consistency. One good example is discarding duplicate blocks on insert into replicated tables. If there's any doubt whether an insert succeeded, you can just insert the block again. ClickHouse checks the hash and discards it. This is incredibly efficient and works without requiring expensive referential integrity (e.g., unique indexes).

4. It's just about always possible to get ClickHouse to boot even when you have corrupt underlying data (e.g., due to file system problems). I don't know how you define reliability but at least in this sense ClickHouse is extremely robust. I've never seen a server fail to start, though you might need a bit of surgery beforehand.

5. ClickHouse doesn't have transactional DDL. What it does have is features like altering tables to add new columns in a fraction of a second without locking regardless of the size of the dataset. Its behavior is close to NoSQL in this regard.

I could go on, but I think these points illustrate that ClickHouse has a different set of design choices for different problems. I would never use it for eCommerce, but it's great for analytics.

Disclaimer: I work on ClickHouse at Altinity.

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

#153

Post by RhodesianHunter »

encoderer wrote: post #67
dreyfan wrote: post #65

Earlier quoted context omitted.

https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.

Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level. In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.

On a project I worked on we found the sweet spot to be 20k-60k rows per insert.

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

#154

Post by RhodesianHunter »

willvarfar wrote: post #84
encoderer wrote: post #64

Earlier quoted context omitted.

Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.

(Remember that clickhouse is not reliable. It doesn’t pretend to be. Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too. Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)

I have not found this to be the case. Like any system you need to take precautions (replicas and sharding) to ensure no data loss, but I didn't find that to be challenging. In what way have you found ClickHouse particularly risky in this way?

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

#155

Post by assface »

mfreed wrote: post #144
nojvek wrote: post #136

I’m surprised Timescale hasn’t given a comparison with SingleStoreDB. I’ve found SingleStore column scans at parity with ClickHouse in speed. At same time SingleStore uses a hybrid skip-list, columnstore data structure in their universal storage (which is default table format). So you have high throughput transactions, as well as insanely fast aggregate scans. Usually in column stores, they are great at append, not s…

Beyond being closed source, SingleStoreDB's License explicitly prohibits benchmarking: https://www.singlestore.com/assets/contracts/singlestore-fre... 2. Restrictions. You acknowledge that the Software, and its structure, organization, and source code, constitute SingleStore’s and its suppliers’ valuable trade secrets, and the Software is subject to the following restrictions. Specifically, Customer shall not, and sh…

Just ignore it.

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

#157

Post by RhodesianHunter »

GordonS wrote: post #115
sam0x17 wrote: post #75

Earlier quoted context omitted.

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

I think most online logging SaaS services actually use ElasticSearch.

I know that at least two of the top ten use ClickHouse.

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

#158

Post by RhodesianHunter »

chalcolithic wrote: post #91

Could putting RedPanda/Kafka in front of ClickHouse make it insert benchmark winner? Of course it means operational expenses but I wonder if this route is worth exploring?

Yes that is exactly how you would get the best performance out of ClickHouse.

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

#159

Post by nhoughto »

mfreed wrote: post #133
ants_a wrote: post #132

Earlier quoted context omitted.

The optimization engine is of course great (despite occasionally missing hard), but I am not referring to it. I am referring to the way that PostgreSQL executes query plans, the way rows are pulled up the execution tree, is very similar to first iterations JavaScript engines - a tree based interpreter. Picking out columns from rows and evaluating expressions used to work the same until PG11, where we got a bytecode b…

With TimescaleDB compression, 1000 rows of uncompressed data are compressed into column segments, moved to external TOAST pages, and then pointers to these column segments are stored in the table's "row" (along with other statistics, including some common aggregates). So while the query processor might still be "row-by-row", each "row" it processes actually corresponds to a column segment for which parallelization/ve…

yeah very interesting, i was wondering how timescale pushed postgres more towards columnar without rewriting a bunch of postgres itself.

My understanding of TOAST is that it itself is just a bunch of rows in a toast table that split the compressed "row" or in this case "1000 rows of 1 column" across as many rows as required to store the data whilst remaining within the postgres page size limits (normally 8kb).

With the often quoted postgres per row overhead of 23 bytes~ which you would have to pay for each TOAST row as well, does this not add up and eat into your storage efficiencies? or does compression work so well that the 23 bytes x N rows (1 row pointing to toast + N toast rows) required to store the "row" isn't important?

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

#160

Post by akulkarni »

hodgesrm wrote: post #152
akulkarni wrote: post #148

Earlier quoted context omitted.

> No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used This is not true at all, and we explain why in the post: 1. TimescaleDB's reliability is PostgreSQL's reliability. ClickHouse has a lot of advantages, but "more reliable than PostgreSQL" is not one of them. From the post: P…

You are right about transactional differences between ClickHouse and PostgreSQL but you are comparing apples and oranges. ClickHouse prioritizes speed, efficiency, and scale over consistency. These are reasonable choices, especially in the largely append-only use cases which dominate analytics. 1. I've seen relatively few messed up source tables and mat views over thousands of support cases. When they happen they can…

   I could go on, but I think these points illustrate that ClickHouse has a different set of design choices for different problems. I would never use it for eCommerce, but it's great for analytics.
I agree with this. You are poking at a straw man.

My reply was in response to this comment by the OP:

> No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used

TimescaleDB - which some don't realize - inherits all of the reliability of PostgreSQL, i.e., the 20+ years of usage and tuning (and broad tooling ecosystem).

What I was disproving was the statement that TimescaleDB/PostgreSQL was somehow riskier than ClickHouse.

ClickHouse is impressive, but deployments are still far behind that of PostgreSQL. ClickHouse is also younger and less mature than PostgreSQL.

I can see that you are the CEO of Altinity. Nice to meet you. I'm the CEO of Timescale. I think it's important that we strive for transparency in our industry, which includes admitting our own product's shortcomings, and to accept valid criticism.

We've done that many times in this HN thread (and in the blog post). I think we would have had a more productive discussion in this HN thread if ClickHouse developers were also as transparent with ClickHouse's shortcomings.

I'm happy to continue this conversation offline if you'd like. The database market is large, the journey is long, and in many ways companies like ours are fellow travelers. ajay (at) timescale (dot) com

Post reply on HN