Hacker News Reader

Comment by whitepoplar | Hacker News Reader

Haven't used any of these yet, but how does ClickHouse compare to Postgres extensions like TimescaleDB and Citus (which recently launched a columnar feature)? I remember reading in the ClickHouse docs some time ago that it does not have DELETE functionality. Does this pose any problems with GDPR and data deletion requests?

View on HN

I benchmarked ClickHouse vs. Timescale, Citus, Greenplum and Elasticsearch for a real-time analytics application. With a couple hours learning for each (although I’ve used Postgres extensively and so Postgres-backed databases had a bit of an advantage), ClickHouse’s performance was easily an order of magnitude or two better than anything except ES. ES had its own downsides with respect to the queries we could run (which is why we were leaving ES in the first place).

What did you end up going with?

ClickHouse deployed to EKS with Clickhouse operator

I know of a database that claims to perform even faster than that one, It is commercial, though. For very massive data, in a time-series setup basically.

https://www.hydrolix.io/

From docs it seems they use forked ClickHouse code.

As far as I understand, they use a part as a frontend, not as a full engine for everything.

Cloudflare’s analytics have been powered by Clickhouse for a long time. And I was an early investor in Timescale. They’re both excellent products.

Yeah, I was a little disappointed because I was rooting for Timescale myself. And, maybe if I had spent more time optimizing, I could have made Timescale work but, between our experiments and Cloudflare’s blog post about migrating from Citus to ClickHouse, ClickHouse seemed like the one most likely to hold up for our production workloads.

In a nutshell, my extremely subjective and biased take on it:

* Citus has a great clustering story, and a small data warehousing story, afaik no timeseries story;

* TimescaleDB has a great timeseries story, and an average data warehousing story;

* Clickhouse has a great data warehousing story, an average timeseries story, and a bit meh clustering story (YMMV).

(Disclaimer: I work for a competitor)

[Timescale co-founder]

This is a really great comparison. I might borrow it in the future :-)

But yes, if you have classic OLAP-style queries (e.g., queries that need to touch every database row), Clickhouse is likely the better option.

For anything time-series related, and/or if you like/love Postgres, that is where TimescaleDB shines. (But please make sure you turn on compression!)

TimescaleDB also has a good clustering story, which is also improving over time. [0][1]

[0] https://news.ycombinator.com/item?id=23272992

[1] https://news.ycombinator.com/item?id=24931994

I like how you always chime in with time series stuff. Just by watching you interact here, boost my confidence on your product

Thanks for the kind words. Our first company value is, "Help first." :-)

I can vouch for this. Ajay replies to my emails on a weekend. I'm nobody, not even a customer. He doesn't need to do that at all. I imagine he does it because he's a genuinely good and helpful type of person.

Thanks! It's not just me, it's what we all try to do at Timescale :-)

I thought Pinot and Druid are direct competitors

> Disclaimer: I work for a competitor

What competitor btw? I tried to open a link from your profile but it does not work.

It appears the competitor is QuasarDB

Excellent comment archeology, this is correct. :)

actually grabbed it from Keybase -> Googled name -> LinkedIn :)

ClickHouse wins on licensing--Apache.

The TimeScale licensing approach, the way it is written, perhaps accidentally, has lots of hidden landmines. The TimeScale license slants toward cloud giant defense to the extent that normal use is perilous.

For example, timescale can be used for normal data (postgres) as well, so any rules seem to apply to all your data in the database. The free license only usable available if:

the customer is prohibited, either contractually or technically, from defining, redefining, or modifying the database schema or other structural aspects of database objects, such as through use of the Timescale Data Definition Interfaces, in a Timescale Database utilized by such Value Added Products or Services.

My read is that if you let a customer do anything that adds a custom field, or table, or database, or trigger, or anything that is "structural" (even in the regular relational stuff) anywhere in your database (metrics or not), you are in violation. There doesn't seem to be a distinction about whether this is "direct" control or not, or whether a setting indirectly adds a trigger. I don't want to be in a courtroom debating whether a new metric is a "structural change!"

Now, none of that might be the intent of the license, but you have to go by what it says, not intentions.

The sad part of that is, I, and I'm sure many folks, have no interest in starting a database company, but we can't rally timescale because of legal risk. Looks awesome otherwise, though.

[Timescale co-founder here]

Hi Eric, thanks for taking a close look at our license.

I'd like to dispel some misconceptions:

The core of TimescaleDB is Apache2. Advanced features are under the Timescale License.

Regarding this:

  the customer is prohibited, either contractually or technically, from defining, redefining, or modifying the database schema or other structural aspects of database objects, such as through use of the Timescale Data Definition Interfaces, in a Timescale Database utilized by such Value Added Products or Services.

  My read is that if you let a customer do anything that adds a custom field, or table, or database, or trigger, or anything that is "structural" (even in the regular relational stuff) anywhere in your database (metrics or not), you are in violation. There doesn't seem to be a distinction about whether this is "direct" control or not, or whether a setting indirectly adds a trigger. I don't want to be in a courtroom debating whether a new metric is a "structural change!"

That's not correct, and we took pains to clarify that in the license:

  3.5 "Timescale Data Definition Interfaces" means SQL commands and other interfaces of the Timescale Software that can be used to define or modify the database schema and other structural aspects of database objects in a Timescale Database, including Data Definition Language (DDL) commands such as CREATE, DROP, ALTER, TRUNCATE, COMMENT, and RENAME. [0]

Strictly speaking, if you provide Data Definition Interfaces (DDL) to customers via a SaaS service (ie you are running a TimescaleDBaaS - which applies to < 0.000001% of all possible users) you are in violation of the license. But otherwise you are fine.

If you are looking for more votes of confidence, today there are literally millions of active TimescaleDB instances, including by large companies like Walmart, Comcast, IBM, Cisco, Electronic Arts, Bosch, Samsung, and many many smaller ones. [2]

If you have any other questions, I'm happy to answer them here, or offline (ajay at timescale dot com).

[1] https://www.timescale.com/legal/licenses#section-3-5-timesca...

[2] https://www.timescale.com/

Are you sure?

The phrasing "such as" in "such as through use of the Timescale Data Definition Interfaces" looks to me like it can be interpreted as saying "Including but not limited to"

Yes :-)

It was a little cumbersome to list every DDL SQL command, which why it uses that language. But that is the intent.

If you have a specific question, happy to answer it here (or offline)

Also, we used this language deliberately to provide more clarity. DDL vs DML is a pretty clear line to most developers who use TimescaleDB (vs some other companies who use language like, "you can't compete with us" etc).

Right, I'm onboard with your stated intent, and the lovely database, and even sympathetic on the cloud provider threat.

My interpretation doesn't hinge on the DDL/DML question. If what you said is your intent, the legal language used is wrong and you should fix it. Consider this a bug report. Here's a source!

https://www.law.cornell.edu/definitions/uscode.php?width=840...

In order for the license to be usable, you need to be limitative here.

Thanks for the feedback, will pass along :-)

The issue isn't the DDL vs. DML.

The issue is the 'such as', which reads to me as indicating that providing an API endpoint that can add fields would also be a way of letting the customer modify the database schema and therefore covered.

Which means that building a SaaS app backed by timescale that has any level of customisation exposed to the user appears to be prohibited.

This seems a rather stronger level of prohibition than stopping people directly competing with you, and would suggest that if it's intended it would help to make it more explicit, and if it isn't then an explicit statement of that would be worth adding.

Practically speaking, there are already 1000s+ SaaS apps built on TimescaleDB (and using the Timescale License features)

But I appreciate the feedback on how we could make our language clearer. Will share with the team!

I mean, that’s cool, but maybe a bit problematic if the language of the license means they’re all in violation of it.

I understand from your perspective this whole discussion probably looks silly, because you know what’s in your mind.

There are just too many stories of this blowing up in someone’s face (mostly due to legal, not due to actual enforcement) :/

Right, and we're subject to what's in his mind, which we can't know, and what's in his acquirer's mind, which even he can't know!

The issue is likely something like this:

a) Oracle buys TimescaleDB

b) Oracle sues any of 1000+ SaaS apps that have decent revenue and they can identify

c) The other 900 suddenly have massive due diligence issues even if not sued

So this is a very relevant question and I have been trying to figure out if I need to migrate off timescaledb asap coincidentally over the last 2 weeks (We're pre-production anyway, so it's the time to do so!). Doing so has been super low priority, but since you're here .... :)

If I have a table that records timeseries data and then another table that has a customer-provided extensible set of metadata where a customer can define columns and other related tabular data, would that violate the license? The customer doesn't have a direct, like, psql level of access but the API intentionally provides a very similar level of interaction.

Does this qualify as providing Data Definition Interfaces? If none of those additional columns and such appear on tables set up as timescale tables does that make any difference?

One clarification to the above discussion -- about whether these restrictions also get applied to "even the regular relational stuff" -- which I think is relevant to you (and parent):

The Timescale License only covers the TimescaleDB code. Postgres code continues to be covered by the OSS PostgreSQL License [0].

So putting aside the question about API vs. psql level (again, the Timescale License was drafted to enabled this for "Value Added Services", e.g., where "such value-added products or services are not primarily database storage or operations products or services"), this license wouldn't apply for non-Timescale code.

[Timescale co-founder here]

[0] https://github.com/timescale/timescaledb/blob/master/NOTICE

Not the parent, but great to hear that clarification! By the way, doubt I would complain if Prometheus didn't look impressive. :-)

One other bug report on the license language front, this language could be construed to prohibit uses like Prometheus--unless there's a definition of operations products I missed (possible).

> are not primarily database storage or operations products

Suggested edit:

> are not primarily database storage or *database operations products*

Oh, English parsing =)

Yes, database is a modifier to both storage and operations: database (storage or operations) products.

It's a good edit; will keep in mind.

(For good reason, we generally just don't like to "update" the text of the license too much, even for minor nits.)

Thanks!

Oh sorry, said Prometheus above, meant TimeScale is impressive!

Understood. If you see the other thread regarding the "such as" language, there is a serious edit that you can batch-up and repair to reflect your intentions with this one.

The "such as" language, retains your right to sue anyone for license violations if their API allows any customer action that causes structure changes indirectly, via the DDL, even under the hood (materialized views, too, presumably). That's way way more use cases than just repackaging TSDB as a service. That's a landmine, which when people compare and choose databases, they'd just assume avoid, even if otherwise comfortable with a cloud-protective license. Making this clearer and less onerous probably will probably pay for itself with a wider top-of-funnel for the product with more people more confident in the license.

The "We Clarified It In a Thread on Hacker News Public License" is probably not as ideal as updating the places that need clarification. :-P

Ok thank you!

I appreciate from your POV this probably is silly but for us it’s very helpful to have explicit clarity since investors are questioning and demand certainty. I once had to rewrite https handshakes because a lawyer thought export compliance laws would be violated, so hopefully you understand my trepidation :-)

Just keep these comments handy in case you ever need them in court :P

> ClickHouse wins on licensing--Apache

How so? An end user should prefer a database under a license that protect the developer and users from cloudification/proprietization/SaaS

On top of that, Yandex requires a very aggressive CLA to be signed by contributors: https://yandex.ru/legal/cla/?lang=en

This worries me and makes me wonder if they are going for the open-source-only-by-name model.

[Please reply instead of giving silent dowvotes.]

We don't require Yandex CLA:

> As an alternative, you can provide DCO instead of CLA. You can find the text of DCO here: https://developercertificate.org/ It is enough to read and copy it verbatim to your pull request.

> If you don't agree with the CLA and don't want to provide DCO, you still can open a pull request to provide your contributions.

https://github.com/ClickHouse/ClickHouse/blob/master/CONTRIB...

Anyway, Yandex CLA will be removed in the upcoming days (it should be already removed).

ClickHouse competes with OLAP storages like Druid or Pinot.

I don't know about ClickHouse but the other 2 uses bitmap indexes to make storing petabytes of data affordable.

Row oriented databases would struggle to compete against ClickHouse. They are easily an order of magnitude slower.

ClickHouse uses skip indexes. They basically answer the question "is the value I'm seeking not in the block."

For example, there are a couple varieties of Bloom filters, which allow you to test for presence of string sequences in blocks. This allows ClickHouse to skip reading and uncompressing blocks (actually called granules) unnecessarily.

Sentry.io settled on Clickhouse for error and transaction data after reviewing several options including Citus and Elastic. We've been happy with both the performance and how well it scales from Open Source installs to our SaaS clusters.

There are many independent comparisons of ClickHouse vs TimescaleDB:

By Splitbee: https://github.com/ClickHouse/ClickHouse/issues/22398#issuec... By GitLab: https://github.com/ClickHouse/ClickHouse/issues/22398#issuec... And others: https://github.com/ClickHouse/ClickHouse/issues/22398#issuec... https://github.com/ClickHouse/ClickHouse/issues/22398#issuec...

If you'll find more, please post it there.

TimescaleDB can work pretty fine in time series scenario but does not shine on analytical queries. For most of time series queries, it is below ClickHouse in terms of performance but for small (point) queries it can be better.

The main advantage of TimescaleDB is that it better integrates with Postgres (for obvious reasons).

There are also many comparisons of ClickHouse vs Citus. The most notable is here: https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

ClickHouse can do batch DELETE operations for data cleanup. https://clickhouse.com/docs/en/sql-reference/statements/alte... It is not for frequent single-record deletions, but it can fulfill the needs for data cleanup, retention, GDPR requirements.

Also you can tune TTL rules in ClickHouse, per table or per columns (say, replace all IP addresses to zero after three months).

[Timescale DevRel here]

@zX41ZdbW@ - Thanks for pointing out the various benchmarks that have been run by other companies between Clickhouse and TimescaleDB using TSBS[1]. As we mentioned, we'll dig deeper into a similar benchmark with much more detail than any of those examples in an upcoming blog post.

One notable omission on all of the benchmarks that we've seen is that none of them enable TimescaleDB compression (which also transforms row-oriented data into a columnar-type format). In our detailed benchmarking, queries on compressed columnar data in Timescale outperformed Clickhouse in most queries, particularly as cardinality increases, often by 5x or more. And with compression of 90% or more, storage is often comparable. (Again, blog post coming soon - we are just making sure our results are accurate before rushing to publish.)

The beauty of TimescaleDB columnar compression model is that it allows the user to decide when their workload can benefit from deep/narrow queries of data that doesn't change often (although it can still be modified just like regular row data), verses shallow/wide queries for things like inserting data and near-time queries.

It's a hybrid model that provides a lot of flexibility for users AND significantly improves the performance of historical queries. So yes, we do agree that columnar storage is a huge performance win for many types of queries.

And of course, with TimescaleDB, one also gets all of the benefits of PostgreSQL and its vibrant ecosystem.

Can't wait to share the details in the coming weeks!

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

Thank you! Looking forward for a blog post. We need more references for comparison to optimize ClickHouse performance.

(although it can still be modified just like regular row data)

But it can't be updated or deleted, so what do you mean by this?

That's a great catch @xdanger and you're right, my comment wasn't accurate. Honestly I rewrote the response a few times and this part wasn't cleaned up which is totally on me.

The overall concept that I was intending to highlight is that you can benefit from both row & columnar store in TimescaleDB. Chunks that are not yet compressed (row store data) can be modified (INSERT/UPDATE/DELETE) as usual and it's transactional - so you're assured it's been completed.

As of TimescaleDB 2.3, compressed chunks (columnar) do allow INSERTS but UPDATES/DELETES on compressed chunks are not yet supported natively. You _can_ decompress any chunk and modify the data (again, transactionally) as needed and recompress.

I have a related question, in case anyone knows: We want to store typical analytics data somewhere (currently in BigQuery) to analyze with Looker. Things like "CI run started", "CI run finished" and then calculate analytics over average CI runtimes.

Which database would be a good fit for this? There isn't too much data, maybe tens of thousands of rows eventually. Would Timescale be a good fit? I'd prefer that, due to existing familiarity with Postgres, but if ClickHouse is better, that's good too.

Postgres has much more featureful query language, and at tens of thousands of rows the performance difference is irrelevant. The story becomes different when answering a query has to touch millions of records and the answer is needed in milliseconds.

Thanks!

Why move off BigQuery?

If BigQuery is good, there's no reason to. I just assumed a time-series database would be better suited to the workload.

ClickHouse can delete rows but work as batch/async operations: https://clickhouse.com/docs/en/faq/operations/delete-old-dat...

Correct + wanted to mention that "lightweight/point-deletes" might come as a new feature.

Initial discussion: https://github.com/ClickHouse/ClickHouse/issues/19627

Being implemented: https://github.com/ClickHouse/ClickHouse/pull/24755

> I remember reading in the ClickHouse docs some time ago that it does not have DELETE functionality. Does this pose any problems with GDPR and data deletion requests?

Clickhouse has ALTER ... DELETE and ALTER ... UPDATE functionality now! (and TTLs)

(Timescale DevRel here)

We've recently been working through a detailed benchmark of TimescaleDB and Clickhouse. The DELETE/UPDATE question has been an intriguing story to follow - and I honestly hadn't considered the GDPR angle.

ATM, Clickhouse is still OLAP focused and their MergeTree implementation does not allow direct DELETE (or UPDATE) of any data. All DELETE/UPDATE requests are applied asynchronously by (essentially) re-writing/merging the table data (it's referred to as a "mutation") without whatever data was referenced in the DELETE/UPDATE. [1]

[1]: https://clickhouse.com/docs/en/sql-reference/statements/alte...

You are correct, the proper way to do deletions in ClickHouse is to use partitions, and drop partitions. That is probably good enough for most analytical use cases, but YMMV.

We are using Clickhouse combined with GDPR's Data Deletion Requests. We store the user-ids in a separate system, and run the ALTER/DELETE statements once per week. Works pretty smooth, though I would prefer some more automation within Clickhouse for them.

Data for in-active users gets deleted because our clickhouse retention policy is lower than the in-active-user timeout

This question is becoming critical right now, as nonrecoverable deletes are required within 30 days for both GDPR and CCPA.

Most products do the asynchronous rewrite, especially if they're based on immutable storage. That's fine, but it should be tested to verify that it's not triggering on every delete, for example, and that it's resource-efficient.

The other option is to use row level encryption and throw away the keys when a user requests a delete.

ClickHouse does allow delete and update operations. They are just asynchronous functions.

I use them every now and then, but I prefer working with partition strategies when I have to these programmatically.

> I remember reading in the ClickHouse docs some time ago that it does not have DELETE functionality. Does this pose any problems with GDPR and data deletion requests?

Altinity is fixing this. The project is called Lightweight Delete and it's for exactly the GDPR reason cited. The idea is that there will be a SQL DELETE command that causes rows to disappear instantly. What actually will happen is that they will be marked as deleted, then garbage collected on the next merge.

Disclaimer: I work for Altinity.

Reply on news.ycombinator.com