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 use case the choice is clear.
Thanks for the feedback. Without knowing your situation, one of the things we show in the blog post is that TimescaleDB compression often changes the game on those kinds of queries (data is transformed to columnar storage when you compress). You don't mention if you did that or not, but it's something we've seen/noticed in every other benchmark at this point - that folks don't enable it for the benchmark.
And second point of the article is that you have lots of options for whatever works in your specific situation. But, make sure you're using the chosen database features before counting it out. :-)
From what I can tell it comes down to execution engine differences. TimeScale, even with compressed tables, uses a row by row execution engine architecturally resembling IE6 era JS engines. ClickHouse uses a batched and vectorized execution engine utilizing SIMD. Difference is one to two orders of magnitude of throughput in terms raw number of rows per core pushed through the execution engine.
Postgres/TimeScale could certainly also implement a similar model of execution, but to call it an undertaking would be an understatement considering the breadth and extensibility of features that the execution engine would need to support. To my knowledge no one is seriously working on this outside of limited capability hacks like vops or PG-Strom extensions.
You do a great job summarizing some of the benefits of ClickHouse we mentioned in the post, including the vectorized engine!
That said, I'm not sure I'd refer to PostgreSQL/TimescaleDB engine architecture as resembling IE6 JS support. Obviously YMMV, but every release of PG and TimescaleDB bring new advancements to query optimizations for the architecture they are designed for, which was the focus of the post.
I'm personally still impressed, after 20+ years of working with SQL, relational databases, when any optimization engine can use statistics to find the "best" plan among (potentially) thousands in a few ms. Maybe I'm too easily impressed. :-D
So while the query processor might still be "row-by-row", each "row" it processes actually corresponds to a column segment for which parallelization/vectorization is possible. And because these column segments are TOASTed, the row itself are just pointers, and you only need to read in those compressed column segments that you are actually SELECTing.
Anyway, might have known this, just wanted to clarify. Thanks for discussion!
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?
More info: https://blog.timescale.com/blog/building-columnar-compressio...
- Gorilla compression for floats
- Delta-of-delta + Simple-8b with run-length encoding compression for timestamps and other integer-like types
- Whole-row dictionary compression for columns with a few repeating values (+ LZ compression on top)
- LZ-based array compression for all other types
This means within even the same table, different columns will be compressed using different algorithms based on their type (or inferred entropy).
More information for those interests:
- General TimescaleDB compression post: https://blog.timescale.com/blog/building-columnar-compressio...
- Deep dive on compression algorithms it employs: https://blog.timescale.com/blog/time-series-compression-algo...
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 internal-facing reports I would expect to eventually outgrow that and reach for Clickhouse/Snowflake/Redshift.
I’ve been thinking of doing exactly that. What are your concerns?
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.)
Clickhouse (and other systems with the same basic architecture, like elastic search and, shudder, mongodb) work very well on happy path. They are not advertising themselves as ACID.
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.
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.
Rows inserted into the buffer table are then flushed to the normal/base table when one of the limits (defined when the buffer table is created) is reached (limits are max rows, max bytes, max time since the last flush), or when you drop the buffer table.
I'm using it and it works (performance difference can be huge compared to perform single inserts directly into a real/normal table), but be careful - the flushed rows don't give a guarantee of which row is flushed in which sequence, so using a buffer table is a very bad idea if your base table is something which relies on the correct sequences of rows that it receives.
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.
I'd still argue Clickhouse is closer to Snowflake/Redshift than anything OLTP, and their name is intentionally chosen to evoke warehouse-like scenarios.
Having used Redshift, Snowflake and CH for similar workloads, I’d much prefer ClickHouse to the other 2.
Snowflake is hideously expensive for the subpar perf it offers in my experience and Redshift is mediocre at best in general.
Howdy! We provided all of those details in the post and you're welcome to join us next week when we live-stream our setup and test!
https://blog.timescale.com/blog/what-is-clickhouse-how-does-...