6 ms·
How we made data aggregation on PostgreSQL better and faster
- jasfi 4y agoI've read claims/benchmarks that TimescaleDB handles inserts faster than plain PostgreSQL, but how? From what I read this is because of the performance effects of using partitions to reduce index updates, but plain PostgreSQL tables can use partitions too.
- philliphaydon 4y agoThey did a blog post about insert perf using PostgreSQL 9.# and it was the use of their partition. The same year PostgreSQL 10 dropped which added partitioning support so they revisited it and still came out on top. But they have never revisited the insert perf in 11/12/13/14. And as far as I can tell they don’t support pg13 yet. So im curious if PostgreSQL caught up or not, or if the results are even real.
- gcbirzan 4y agoTimescale does support PostgreSQL 14 just fine.
- philliphaydon 4y agoJust double checked and I stand corrected. 13/14 work fine. Thanks.
- jasfi 4y agoThanks, I guess the only way to know is to run my own benchmarks. Perhaps they have their own partitioning code which allows for faster performance.
- gcbirzan 4y agoYou should watch this talk, in particular here for the exact question you have: https://youtu.be/eQKbbCg0NqE?t=1489 https://youtu.be/eQKbbCg0NqE?t=1489
- jasfi 4y agoThe speaker compares TimescaleDB to PostgreSQL 10, so this is out of date since PostgreSQL is on v14 now.
- mritchie712 4y agoFor what it's worth to the timescale team: Whenever I see "time-series", I think "cool, but a lot of my data is not time-series, so I guess this isn't for me". What I really want is a "fast open source SQL analytics database".
- valyala 4y agoClickHouse [1] is a "fast open source (almost)SQL analytics database" you are looking for :) [1] https://clickhouse.com/ https://clickhouse.com/
- mritchie712 4y agooh trust me, I found it! We're building Luabase on it. I made this comment because Timescale compares itself to clickhouse a lot, but all the messaging around "time-series" throws me a bit. I'd prefer to use a product that's basically an analytics focused postgres, but it's unclear from all the focus on time-series if that's what Timescale is doing.
- ryanbooz 4y ago(blog author) 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.
- throwaway894345 4y agoIs time series the right answer for anything with a time dimension, or is it mostly for things where time is THE critical dimension? For example, business intelligence applications care about time, but they also care about a whole bunch of other stuff as well (I think with at least as much importance)--is timeseries the right answer for this use case?
- epgui 4y agoAnytime you’re interested in seeing how things change over time, that’s time series. It’s a very big category of use cases.
- PeterZaitsev 4y agoNice to see comparison done to the previous version of TimescaleDB rather than comparison to other vendors, which always tends to be questionable and biased
- Tostino 4y agoRight...benchmarking is hard, even for people who "know what their doing"
- PeterZaitsev 4y agoYes. And BenchMarketing is easy but does not serve your customers well
- deepsun 4y agoJust discuss your results with developers of all the products you benchmark with, prior to publishing.
- avinassh 4y agoThis comment instantly reminded me of recent QuestDB's benchmark and the rebuttal by Clickhouse. Both make a great read: 4Bn rows/sec query benchmark: ClickHouse vs. QuestDB vs. Timescale - https://news.ycombinator.com/item?id=31585563 https://news.ycombinator.com/item?id=31585563 No, QuestDB is not Faster than ClickHouse - https://news.ycombinator.com/item?id=31767858 https://news.ycombinator.com/item?id=31767858
- qoega 4y agoDid you miss previous try of TimescaleDB to show that they are better than ClickHouse https://news.ycombinator.com/item?id=28945903 https://news.ycombinator.com/item?id=28945903?
- avinassh 4y agoOh, I wasn’t implying Timescale is better than the other. That comment by GP reminded me of what happened last week
- fabian2k 4y agoMaterialized views that are updated efficiently when new rows are added or rows updated would be a really great feature to have in core Postgres. This certainly would be useful outside of timeseries data.
- djk447 4y agoNB - Timescale person here. Totally true! It's also a much harder problem :) One of the things that we try to focus on at Timescale is figuring out how we can simplify problems based on the specific needs of time-series data. Postgres has to solve things for very general cases, and sometimes that just is much harder. And then they often won't work all that well for time-series, because they're not all that optimized for them.
- Tostino 4y agoYeah, that's been one of my most wished-for features throughout the years. There is an extension providing some limited support for this, but it's far from what would be usable for any project where I have that need. Just too limited in what it supports. I need complicated queries, aggregates, etc.
- Nelkins 4y agoI think this is what you're looking for: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-released.html https://yugonagata-pgsql.blogspot.com/2022/05/pgivm-v10-rele...
- aidos 4y agoInteresting. I check back on the wiki page every couple of years to see what progress is being made. The basic idea seems to be to track the primary keys of the base tables in the incremental view and then use triggers to update those rows when source rows are updated. The meat of the project is over here for anyone that’s interested - in particular this section about the limitations is pretty interesting (and expected). https://github.com/sraoss/pg_ivm/#supported-view-definitions-and-restriction https://github.com/sraoss/pg_ivm/#supported-view-definitions...
- hokkos 4y agoWhat I really want about Timescale continuous aggregate is join and continuous aggregate from other continuous aggregate.
- ryanbooz 4y ago(NB - blog author/Timescale employee) 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 https://github.com/timescale/timescaledb/issues/1400 [2]: https://www.timescale.com/blog/introducing-hyperfunctions-new-sql-functions-to-simplify-working-with-time-series-data-in-postgresql/ https://www.timescale.com/blog/introducing-hyperfunctions-ne... [3]: https://www.timescale.com/blog/how-postgresql-aggregation-works-and-how-it-inspired-our-hyperfunctions-design-2/ https://www.timescale.com/blog/how-postgresql-aggregation-wo...
- trollied 4y agoI was expecting this to be about INSERT performance/overhead (both IO and CPU), which is the metric that matters most when dealing with the overhead of materialized views.
- ryanbooz 4y ago(NB - post author) 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.
- trollied 4y agoAppreciate the reply, thank you.
- skunkworker 4y agoWith the recent release of AlloyDB by GCP, how does timescaleDB compare with OLAP now? https://cloud.google.com/alloydb https://cloud.google.com/alloydb