I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose.
About the data
The source data which is to be stored in the data-storage is provided by physical measurement units. Each of the unit may or may not have a different subset of variables with up to 300 variables per measurement-station (e.g. fuel type, fuel consumption, speed) while the number of different signals over all stations is on the order of 1500. The expected subset of variables per station is known beforehand. However, additional sensors might be added to a station with time (schema change might be required over time). All of the stations provide data in varying rate ranging from 20Hz to 0.2Hz.
In addition there is a fair amount of meta-data available to all these measurement-stations of which we will have about 500 in the end.
The data usually comes in in batches and not as "realtime" stream. The batch sizes differ from hourly to monthly batches.
About the queries
The querying on the data is done for two main reasons, reporting and statistical analysis on data of a single measurement-station as well as cross-station comparison. About 80% of the queries are concerned with data which came in during the last 30 days. Querying is done on a daily basis therefore
SELECT load exceeds
Ideally queries like
SELECT var1, var2, ... varN FROM station_data WHERE station_id=X OR station_id=Y AND TIMESTAMP BETWEEN ... AND ...;
would be possible for ease of data access for non SQL-specialists. Moreover simple time based aggregation arithmetics should be possible (AVG, MAX, etc. pp).
Currently a highly normalized structure is used to store the data in a PostgreSQL database which grew by now to about 6TB with one table per variable. Each of the about 1500 data tables is of the form
(timestamp, station_id, value)
with indexes on
(station_id), (station_id, timestamp), (timestamp) and a unique constraint on
(station_id, timestamp, value).
This structure requires heavy outer joining (up to 300 outer joins) which make data retrieval cumbersome and computationally expensive.
So far the following considerations were made:
- While NoSQL would give the required schema flexibility, tools to ensure data integrity, access control and management of the meta-data seem to be challenging and no NoSQL experience exists in house. Further, reading comments and answers along the line of this seem to go in favour of a SQL solution for our usecase.
- Different time-base optimized databases were considered (mainly CrateDB and TimescaleDB). Both look promising with regard to their "automatic" partitioning and sharding where TimescaldeDB would be slightly favoured for it being based on PostgreSQL.
So far two different schemas were worked out which work in principle. However, both have significant drawbacks which I need to find a way around.
- EAV (anti-)pattern with one huge vertical data table with sharding on
station_idand monthly partitioning on
timestamp. While the required schema flexibility would be given, this pattern would not comply with the required ease of access as it still relies heavily on inner joins. Furthermore, type-safety for different datatypes is not ensured on db-side and access control is not possible.
- One table per
station_idwith horizontally changing schema on addition of a sensor to a specific station. This unnormalized structure is on first sight appealing from an application point of view (fast inserts, little indexing required, simple query on single station). However, querying would required dynamic SQL since the enduser might not know the table name for the specific station and cross station comparison would only be possible with extended SQL queries or client side code.
While storage capacity is of no concern, reliabiliy, uptime and speed of data retrieval is.
Which of the proposed data models would be preferred in order to meet the requirements while maintaining scalability? Suggestions for any additional schema which fits the requirements is highly welcome.