Creating persistent real-time analytics of time-series data?

iwasaki-kenta

Hello,

I am looking to create a system which has to be able to sustain and persist approx. 1000 events per second from Phoenix channels, and compute statistics of the events over different time windows for an IoT project I’m doing.

The system for example given a bunch of temperature sensors scattered around a room would over windows of 1 minute, 1 hour, 6 hours, and per day compute the minimum mean temperatures, maximum mean temperatures, and opening/closing mean temperatures of the time windows and be able to report them to a web dashboard in real-time as events come by.

The statistics reported cannot be approximate (so techniques like average moving windows/rolling windows cannot be used for these aggregated statistics).

The events right now are aggregated onto a Postgres table, and I was thinking upon receiving the event I could recompute the means/mins/openings/closings using a Postgres aggregation query, though that would obviously be very tasking on the system as this aggregation query would have to go through all events thousands of times a second.

Another method I was thinking of was keeping a running count of the number of events (total count), and having a counter accumulating the temperature values over time on a separate Postgres table (to then compute the mean). I’m worried however that this accumulation would go past the data type limit after awhile and become a pain to handle in the database.

Timing accuracy and persistence if I were to do everything in memory (Redis, ETS) is also a major concern. I hopefully don’t want to touch a time-series database which holds continuous queries, though if anyone knows a very simple way to plug one in then I’d be very interested.

Anyone have ideas on how to architect such a system?

16 comments

/phoenix #channels #timestamps #real-time

1 4398 16

2017-12-07 08:42:44 UTC

First Post!

Show Best Posts
Show All (oldest first)
Show All (newest first)

christhekeele

The sort of system you are describing with these challenging requirements is the stuff of Enterprise in-house software with data pipelines that a lot of hardware has been thrown at, especially if you are avoiding time series databases based on the no-continuous-query criteria. Perhaps if one of your requirements was loosened you’d have more luck finding supporting software?

Just based on your light description, it sounds like a column-oriented database might be able to perform the calculations you are interested in faster than postgres, in exchange for longer insert times. Whether or not it is fast enough or worth the trade-off depends greatly on your data I suspect.

Post #1

Most Liked

entone

I highly recommend InfluxDB with a Grafana frontend

Definitely check out the continuous queries for rolling up data. I generally do 30 day retention for realtime data, 1y retention 15min rollup, and infinite retention for 1hr rollup data.

I generally use the Docker image to run influxdb as a service.

You can see my MQTT based backend here, Brood

Post #5

entone

You would probably want to check out Kapacitor

Here’s a great example of outputting a JSON result. Live leaderboard of game scores | Kapacitor Documentation

You could also have it POST to an http endpoint and multiplex the message from a cowboy handler.

Post #7

entone

You could also use a subscription and write data out to a UDP socket. Influx Query Language (InfluxQL) reference | InfluxDB OSS v1 Documentation

I think losing the robustness of InfluxDB query language would be hard.

I would imagine a system that creates several subscriptions on continuous queries tables, and pipes that data to a UDP server that pushes the data to your websocket clients.

Post #9

Last Post!

kip

ex_cldr Core Team

If you’re using Postgres for your standard DB, https://www.pipelinedb.com might be worth investigating. (I have no personal knowledge, I’ve stored this link for later investigation for my own stuff)

Post #16