Thank you! Huge shout out to Shane, Jacob and others on our team who helped with the graphics / design elements!
148 karma · joined May 4, 2018
(NB: Post author here).
This is interesting and I totally get at least some of the problems you're facing. I wonder if you could take some of the strategies from t-digest and modify a bit to accomplish...I'd be interested in seeing some sort of implementation of this and would love to see if we can get it into our toolkit if you do...or you can also open up a ticket for us and we'll see if we can prioritize to work on something like it ourselves.
I do think there are some interesting ways you can cut corners if you know things about the "SLO" or other sort of cutoff values, but I'd have to think more deeply about it to say more. Essentially we want a variable error rate based on the distance away from a known value, where you have little error in the values relatively close to the known value, care little if at all for fine distinctions on the low end and, once you get past some high end value you could probably bucket everything above it into a "large outliers" bucket or something too...meaning you p999 could get out of hand if you start getting lots of stuff above a threshold, but, if that starts happening, probably everything's already burning down, so might not be that useful to know it's burning down in a very specific way...
Yep! Briefly noted that in the post, but deserves re-stating! it's definitely a more complex analysis to figure out the percentage of users affected (though often more important) could be majority could also be one user who has some data scientist programmatically making hundreds of long API calls for some task...(can you tell that I ran into that? Even worse it was one of our own data scientists ;) ).
Oops, yep, that should probably be order from smallest to largest. Thanks for the correction!
Love this example. Might have to use that in a future post. Feel like a lot of us are running into a similar thing with remote work and video calls these days...
We've been meaning to add IQR as an accessor function for these, may have to go back and do it...the frequency trails [1] stuff from Brendan Gregg also goes into some of this and it's really cool as a visualization.
This is great! So fun...will have to use in the future...
Yeah, that was one of the reasons we chose it as one of the ones to implement, seemed like that was a really interesting tradeoff, we also used uddsketch[1] which provides relative error guarantees, which is pretty nifty. We thought they provided different enough tradeoffs that we wanted to implement both.
Totally can be true. In our case, we use these approximation methods that allow you to get multiple percentiles "for free" definitely need to choose the right ones for the job. (We talk a bit more about the whole approach where we do the aggregation then the accessor thing in the previous post on two-step aggregation [1]). But there are definitely times when averages/stddev and potentially the 3rd and 4th moments are more useful etc.
[1]: https://blog.timescale.com/blog/how-postgresql-aggregation-w...
I found it surprisingly difficult to explain well. Took a lot of passes and a lot more words than I was expecting. It seems like such a simple concept. I thought the post was gonna be the shortest of my recent ones, and then after really explaining it and getting lots of edits and rewriting, it was 7000 words and ...whoops! But I guess it's what I needed to explain it well (hope you thought so anyway).
It's somewhat exponential, but yeah, not necessarily, it's definitely long-tailed in some way and it sort of doesn't matter what the theoretical description is (at least in my mind) the point is these types of distributions really don't get described well by a lot of typical statistics.
Can't talk too much about SREs at the ad analytics company I mentioned, we were the backend team that wrote a lot of the backend stores / managed databases / ran the APIs and monitored this stuff a bit (and probably not all that well). It was a bit more ad hoc I guess, probably now that company is large enough they have a dedicated team for that...
Thanks for sharing! Hadn't heard of that algorithm, have seen a number of other ones out there, we chose a couple that we knew about / were requested by users. (And we are open to more user requests if folks want to use other ones! https://github.com/timescale/timescaledb-toolkit and open an issue!)
That's really nifty, wish I'd heard about it earlier. Might go back and add a link to it in the post at some point too! Very useful. Definitely know I wasn't breaking new ground or anything, but fun to see it represented so succinctly.
Std deviation definitely helps a lot, still often not as good as percentiles, was actually thinking about adding some of that in the post but it was already getting so long. It's funny how things you think are simple sometimes take the most effort to explain, definitely found that on this one.
NB: Post author here.
(NB: Post author here)
[1]: https://docs.timescale.com/api/latest/hyperfunctions/percent...
(NB: Post author here.)
A number of the things you're looking for we've done experimentally and we'll be stabilizing over the next few releases. So we'd love some feedback while we're still able to futz with the API without making breaking changes.
But the two you're asking about are, I think, going to be covered by hyperloglog (we just reimplemented the internals with HLL++) and stats_agg family of functions, which have both 1D (which will give you avg, stddev, variance, etc) and 2D (co-variance, slope, intercept, x-intercept etc as well as all the 1D functions).
Would also love issues if you think we're missing other stuff, going to be generalizing this and want to make it useful for folks.
(NB: Post author here.)
Glad you liked the GIFs! Will have to take a look at this. Thanks for sharing!
There are plans to do that, I'm not sure the exact timeline, I think it's in the design phase now.
We do already support partitionwise aggregation, and this meshes very well with that, so that you're sending much, much less data back over the network between the data node and access node when you're trying to do things like percentiles. But I agree that continuous aggregate support will be an even bigger win!
Yes! You can use any of the hyperfunction aggregates to build a continuous agg. Including sketches like the percentile approximation stuff.
With hyperloglog, it's still experimental, so you have to jump through some hoops to get it into a continuous agg as it could be dropped on next release, but once that's been stabilized it'll be very easy to use in continuous aggs. Feel free to test now, just know that it may get dropped and you'd have to re-create the continuous agg after the next release when it gets stabilized.
Interesting idea with Simpson's rule! Perhaps we'll add another method...
The other approach is very similar to the LOCF (last observation carried forward) approach, except that it uses the distance to the next sample instead of distance to the previous. I'm sure there are reasons to do the other way, but for most things that use LOCF they record when the value changes so it makes more sense to carry it forward. And it works pretty well as well. These are all also other integral approximation techniques, and which to use probably depends mostly on use case.
That's a nifty way of doing things that I didn't know as much about (definitely left out the historians bit as I didn't want to get too into the weeds) and have sometimes encountered the linear interpolation fit coming from them as well as the LOCF type fit, but didn't realize that's how they were sampling under the hood. That's pretty cool. I was mostly giving that example so people would understand why the LOCF option was available for the function as well, and one of the most common places where I've seen it is in (usually slowly changing) sensors in industrial settings that only record when they change.
On the single continuous process, yes, this is one of the most common mistakes people can make when they do this. We find it's best to model that as a change to the relational part of the data and potentially generate a new "id" for the sensor when that sort of discontinuity happens.
Yep! Though I will say a number of the hyperfunctions are optimized to work on time-series data whether or not there's a hypertable involved, but the name is meant to echo the hypertable concept. In general, hyperfunctions are meant to improve the user experience for working with time-series data inside of SQL.
I'm not 100% sure I understand exactly what you're asking, but, perhaps some more info will be useful. First off, the goal with this isn't just to find the next unique item, it's to find the set of all the unique items, so you'd need to iterate the search, which is basically what the skipscan is doing internally.
Internally, the index is using something like a binary search (it's a btree, so slightly different, IIRC) to skip to the next unique item. A lot of the work here was in teaching the planner / executor to actually do that rather than the simpler, less efficient way that PG usually does it (brute force unique over a full scan). There's some more complexity on top of that in terms of teaching it to do that over all the chunks and then combine the results (because each has a separate index) and some more complexity in terms of knowing when you can use multi-column indexes and the like, but at its most basic level, it basically is using some sort of binary search to find the next item. But perhaps I'm missing what you're asking, feel free to clarify if I can better help here...