I'm rendering charts for timeseries data composed by millions of records. The charts need to be interactive and have lots of feature support so I need to downsample them. The problem I've encountered with SMA and other window-type dowsampling algorithms like LTTB is that because these data have sparse asymptotes, they're always dropped. I have to draw out a representative example because I'm unable to share any screenshots. This data isn't always in a straight line, they sometimes have other minutia that are less extreme, like sawtooths for example. Does anyone have recommendations? I started with simple downsampling, which obviously eliminated my asymptotes, before trying downsampling libraries to accomplish SMA, and LTTB, but those also elimated the asymptotes. I also tried a sort of k_means implementation, but the results weren't great and I got lost because I am relying on an older, company/internal AI model to guide me because this isn't my specialty. More about my data: All my data lives in a single Postgres table, because I need high read/write performance. The data is queried using a compound index, matching all records with certain fields. Right now I'm working on very complex materialized views to basically pull out only those records which have some kind of "delta", and this works great, but I need to do this for multiple fields, for multiple charts, and so the union between them still results in giant data structures. It also doesn't work for my "sawtooth" data because there is always a delta across 2-4 million records. Continue reading...
