3 ms·

The first version worked that way, and I struggled with that architecture for the first ~3 years, but I moved away from it for two reasons: - Unpredictability

The first version worked that way, and I struggled with that architecture for the first ~3 years, but I moved away from it for two reasons: - Unpredictability of required data attributes; It turns out there's an effectively infinite number of different metrics that users may want to monitor, and different numbers of each type of metric on any given monitoring site. E.g., users may want to monitor anywhere between 1 and 30 soil moisture readings (different depths from different probes). This year, seven years into the project, we started supporting sap flow and dendrometer (stem width) readings. I didn't know these existed even two years ago. Next year we'll likely support new types of data that we've never seen before. So it's impractical to add new columns for each data type. - Performance: It's just too slow to write a database row for each reading (sometimes data is uploaded in batches of 1000+ samples), then query and transform at display time. It's so much faster - both computationally and in the writing of the code - to just put everything into a big hash object and store it in Redis. But when I look at competitors' products and see the limitations they have in their app's data display performance and their ability to support different kinds of input devices, I can tell they seem to be stuck with these issues, likely due to being beholden to SQL tables for their primary data storage.

RedShift1 6y ago
Interesting, thanks for your reply. How do you deal with timezones? In our case, for our default dashboard view, we could pre-aggregate everything per hour but often daily, weekly, monthly denominations are needed, which is where the timezone comes into play. Do you pre-aggregate every possible timezone or just update the views for each user individually?
- tomhoward 6y ago
  Cheers! The time series data is stored as arrays of two-item arrays: [UTC-timestamp-in-ms, value]. We have some aggregated and calculated displays by day/week/month/etc, and these are updated/cached when new data is uploaded, which normally only takes a few seconds or less for each view. Data is shown to end-users in the timezone where the monitoring site is located, which normally is moot as the user looking at the data is located in the same timezone, but where they are not, the timezone is clearly identified on the graph/table.

RedShift1 6y ago

Interesting, thanks for your reply. How do you deal with timezones? In our case, for our default dashboard view, we could pre-aggregate everything per hour but often daily, weekly, monthly denominations are needed, which is where the timezone comes into play. Do you pre-aggregate every possible timezone or just update the views for each user individually?

tomhoward 6y ago
Cheers! The time series data is stored as arrays of two-item arrays: [UTC-timestamp-in-ms, value]. We have some aggregated and calculated displays by day/week/month/etc, and these are updated/cached when new data is uploaded, which normally only takes a few seconds or less for each view. Data is shown to end-users in the timezone where the monitoring site is located, which normally is moot as the user looking at the data is located in the same timezone, but where they are not, the timezone is clearly identified on the graph/table.