5

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 INSERT load.

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).

Current situation

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.

Research

So far the following considerations were made:

DB Technology

  1. 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.
  2. 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.

Data Model/Schema

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.

  1. EAV (anti-)pattern with one huge vertical data table with sharding on station_id and 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.
  2. One table per station_id with 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.

General considerations

While storage capacity is of no concern, reliabiliy, uptime and speed of data retrieval is.

Question

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.

Thank you.

  • 1
    Very nice first question! Plus one for making the effort to ask a detailled first question that had a lot of effort involved. – Vérace Jan 15 at 10:25
  • 1
    Maybe consider a 3rd data-model: PostgreSQL (and thus TimescaleDB) supports JSON column types. So you could have one JSON field per table to store many or all variables of each measurement station. – TmTron Jan 16 at 7:55
  • 1
    oh - and keep in mind that the max. no of columns in PostgreSQL is 250-1600 SO reference – TmTron Jan 16 at 7:56
  • @TmTron: I considered this kind of "wide-table-approach" along the lines of this. It holds both in terms of flexibility as well as in keeping up the relational character needed for providing the meta data. However, typecasting for WHERE clauses may become cumbersome. I edited the question to – K. Hueck Jan 16 at 8:47
  • welcome such 3rd data-model options. – K. Hueck Jan 16 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.