DynamoDB Timeseries Table Design

Question

Scenario: I have a few weather stations that I'm collecting data for. The data comes in roughly every 15 minutes or so. Each data packet contains several measurements like pressure, temperature, humidity, etc.

The data would be queried in multiple ways:

1. display latest values for all measurements at a station
2. display a historical chart for a single measurement (for ex. temperature)
3. other?

Proposed Tables:

STATIONS: hash-key: station-id

Contains metadata information about the stations

STATION_X_MEASUREMENT_DATA: hash-key: measurement-type, range-key: timestamp

Where X is the station ID. Each record contains the measurement value for a specific measurement type and time. Each station will have its own data table so that the data can be removed by dropping a table when a station is no longer in service.

STATION_SUMMARY: hash-key: station_id

Contains the latest/current values for all measurement types for each station

Questions:

1. Should I have two separate tables (summary and individual measurments) or should I just query the latest measurements when I want to display the summary?

2. Should I store the measurement types as individual records or combined into a single records for a specific timestamp?

3. If I were to store all measurements in a combined record with timestamp as range key, would it be worth to use minutes or seconds as the partition key? I'm afraid that would make querying more complicated.

4. Is there anything else I should change/improve? Are there better alternatives?

Answer 1

Should I have two separate tables (summary and individual measurments) or should I just query the latest measurements when I want to display the summary?

I don't see how you can have one table. In the measurement data you will have an item per measurement, while in the summary table every item will have static information about stations. If you are going to add them into a single table, are you going to duplicate summary information?

Also having two separate tables allows you to set different RCU/WCU for tables. I guess that station summary is rarely written, so you can set a low WCU, and higher a RCU, while measurement data is often written and may not be read so often. Again your settings can reflect this.

Now, do you want to have separate table for stations and stations summaries? It depends on your data and access patterns, but it is a common pattern to split heave detailed information into a separate table, and compact representation (maybe subset of fields) into a different table. It allows you to save some serious number of RCUs if you have requests like get-all-stations, since probably they don't require detailed info.

Should I store the measurement types as individual records or combined into a single records for a specific timestamp?

The only difference that I see is that you can compress several measurements into a binary blob and store it into one item. If your measurements have some repetitions (LZW algorithm?) or if data does not change one from measurement to measurement (delta encoding?). In the later case instead of writing 202, 203, 202, you can write 22, 1, -1 or something like this.

Keep in mind that an item is limited to 400KB so you can't jam a lot of data in one item.

Also keep in mind that for a single partition key you can't have more than 10GB of data, so you need to have a strategy for how you are going to handle that. Notice that this does not depend on number of items or size of individual items.

If you don't have a lot of data you may be fine having just an item per measurement. If you have a lot of data and you need to decrease AWS cost, then you probably will be better having compressed arrays of measurements

If I were to store all measurements in a combined record with timestamp as range key, would it be worth to use minutes or seconds as the partition key? I'm afraid that would make querying more complicated.

Hard to say. How many records do you have per second? Per minute? Maybe it makes sense to aggregate per hour to get better results from compression? Or maybe for a day? It depends on your data.

Is there anything else I should change/improve? Are there better alternatives?

You can have different tables for different time intervals. Newer data can have high WCU/RCU config, while older data will have low WCU (can you write in the past?) and lower RCU. Old data can be transferred to S3. Also you can use DynamoDB TTL to automatically remove old tables if you need to.