The team at Tembo keeps working quickly to enable many different use cases with Postgres and extensions. Pretty fun example of how capable Postgres is for various tasks.
117 karma · joined September 24, 2020
In the example you gave, I'd assume that you wouldn't run a query over billions of transactions to do a sum. (obviously indexes would be part of reducing this number at query time). I would think you'd probably want to aggregate the sum per hour/day of all addresses and then decide at query-time if you need to sum all transactions for all time or within a specific range. Whenever you need to constrain the query based on time, you're still using the data like time-series, even if the final result doesn't have a date on it. And whenever you're doing the same aggregate queries over and over, that's where Continuous Aggregates can help!
For example, using the (transaction??) timestamp to efficiently store the data in time-based partitions (TimescaleDB chunks) unlocks all kinds of other functionality. You can create continuous aggregates to keep that historical aggregate data up-to-date (even if you need to eventually drop or archive some of the raw transaction data). With 2.7, you can create indexes on the views in ways you couldn't before which speeds up queries even more. Chunks can be compressed (often 93%+!!) and make historical queries faster while saving you money.
So in that sense, time is the component that helps unlock features - when time is an essential component of the raw data, but the query-time analytics don't have to specifically be about time. PostgreSQL and TimescaleDB work together to efficiently use indexes and features like partition pruning to provide the performance you need.
BTW, I'm not sure if you saw the post and tutorial we just released last week showing how to analyze transactions on the Bitcoin Blockchain or not. [1][2] Similar use-case and not all tied to time-based queries only. There are also other companies currently indexing other blockchains (Solana for instance) that have had really great success with TimescaleDB (and it gets even better with TimescaleDB 2.7!)
Thanks!
[1]: https://www.timescale.com/blog/analyzing-the-bitcoin-blockch...
[2]: https://docs.timescale.com/timescaledb/latest/tutorials/anal...
Great definition! Having worked for years on both energy and IoT applications, the argument here is that your "monthly sales data" is likely being aggregated from your time-series data (sales transactions over time). If you store the transaction data in a database like TimescaleDB, then continuous aggregates provide the straightforward method for keeping that aggregated, monthly sales data up-to-date. :-D!
In a sense, it is. Continuous aggregates only have to materialize the most recent bucket of time, not the entire materialized view as you have to in PostgreSQL. That's honestly hard to demonstrate and quantify in a blog post like this because it's something that you notice over time. If you have to refresh the PG materialized view every hour (to get the recent hour of data) and it takes 2 minutes - a year from now it's probably going to take 3-4 minutes (maybe more)... and a lot of valuable CPU/IO to boot.
With continuous aggregates, TimescaleDB is only materializing the last hour - and updating anything that's changed in previous buckets within the refresh window.
One thing we're improving as we move forward in documentation and other areas is explaining why doing joins (and things like window functions) is difficult in continuous aggregates and not the current focus. Honestly, it's part of the reason most databases haven't tackled this problem before.
Once you add in joins or things that might refer to data outside of the refresh window (LAG values for example), things get really complicated. For instance, if you join to a dimension table and a piece of metadata changes, does that change now need to be updated and reflected back in all of this historical aggregate data that's outside of the automatic refresh policy? Same with a window function - if data within a window hasn't changed but data that *might* be hit because of the window function reference does change, continuous aggregates would have to know about that for each query and track those changes too.
I'm not saying it's impossible or that it won't be solved someday, but the functionality with continuous aggregates that keeps the aggregate data updated automatically (without losing any history) *and* being able to perform fast joins on the finalized data is a very useful step that's not available anywhere else within the Postgres ecosystem.
RE: CAGG on top of a CAGG - you're certainly not the only person to request this[1] () and we understand that. Part of this is because of what I discussed above (tracking changes across multiple tables), although having finalized data might make this more possible in the future.
That said (!!!), the cool thing is that we already *have* begun to solve this problem with hyperfunction aggregates and 2-step aggregation, something I showed in the blog post. So, if your dataset can benefit from one of the hyperfunction aggregates that we currently provide, there are lots of cool things you can do with it, including rollups into bigger buckets without creating a second continuous aggregate! If you haven't checked them out, please do! [2][3]
[1]: https://github.com/timescale/timescaledb/issues/1400 [2]: https://www.timescale.com/blog/introducing-hyperfunctions-ne... [3]: https://www.timescale.com/blog/how-postgresql-aggregation-wo...
Thanks for the feedback! Out of curiosity, if the data you're trying to analyze doesn't have time as one of the critical components, what kind of data is it?
Always helpful to learn a bit more.
Great observations around how popular and useful it is for systems to have some level of Postgres line compatibility! Thanks for doing the writeup!
One thing that's not totally clear in the comparison table is that most of these implementations are just that, databases providing an abstraction through the Postgres wire protocol over a different database architecture/parser & solution.
TimescaleDB, however, is the only one listed that is built directly on Postgres as an extension. There is no abstraction layer over some other implementation of the query parser or storage layer.
When it comes to application maintenance and developer expectations at query time, not all of these are apples-to-apples comparisons, as some of the other comments have noted.
We have a few users that have larger multi-node clusters setup this way (one at 40+ nodes so far) and happy with the offering overall. Obviously YMMV depending on requirements/IT support.
Thanks for the great, thoughtful feedback. We (Timescale) couldn't agree more that there is a lot to love about ClickHouse, especially where it truly excels.
Information like this is helpful for others currently in the "choose the right tool" part of the job and to the developers of the product. I can't imagine how different all of our offerings will look in a few more years! :-)
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
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.
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
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.
The code that's currently used by TSBS was submitted by Altinity, a heavy supporter of ClickHouse in the U.S., but TSBS is open source and anyone is welcome to contribute and make the process/test better!
What do you mean by "migrating old data"? Don't want to make assumptions before answering further.
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
As for ingest queueing, TSBS does not queue results. We agree, and tell most users that they should queue and batch insert in larger numbers. Not every app is designed that way and so we wanted to understand what that would look like.
But CH did amazingly well regardless of that with batches above 1k-2k and lived up to it's name as a really fast database for ingest!
- The code that TSBS uses was contributed by Altinity[1]. If there is a better setup, please feel free to submit a PR. As stated elsewhere, we did have a former CH engineer review and even updated ClickHouse to the newest version __yesterday__ based on his suggestion to ensure we had the best numbers. (and some queries did improve after upgrading, which are the numbers we presented)
- It seems like you read the article (great job - it was long!!), so I'm sure you understand that we were trying to answer performance and feature questions at a deeper level than almost any benchmark we've seen to date. Many just show a few graphs and walk away. We fully acknowledged that smaller batches are not recommended by CH, but something many (normally OLTP) users would probably have. It matters and nobody (that we know of) has shown those numbers before. And in our test, larger batch sizes do work well, but not to some great magnitude in this one server setup. Did 10k or 20k rows maybe go a little faster for CH? Sometimes yes, sometimes negligible. The illustration was that we literally spent months and hundreds of benchmark cycles trying to understand the nuances.
I think we're pretty clear in the post that CH is a great database for the intended cases, but it has shortcomings just like TimescaleDB does and we tried to faithfully explore each side.
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.
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+ TimescaleDB cluster backing their network monitoring stack and loves it for their use case.
At some point, when we can, I'm sure we'll start to do more with multi-node benchmarking too to give some input to the conversation.
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 various batch sizes.
Howdy! All of the details about our TSBS settings in the performance section of the docs. Also, we'll be streaming a sample benchmark of the two databases next Wednesday at 10AM ET/4PM CET.
https://blog.timescale.com/blog/what-is-clickhouse-how-does-...
twitch.tv/timescaledb
Remember, TSBS is open-source and we've had some great contributions from many teams/databases. :-)
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-...
This is a great post to give you some talking points:
https://blog.timescale.com/blog/what-the-heck-is-time-series...
I also love this recent one we did with some non-standard time-series data that the NFL provided! Really fun working on that data set.
https://blog.timescale.com/blog/hacking-nfl-data-with-postgr...