Storage solution for realtime time series analytics

Question

I'm designing a system which allows user to analyze their data over time. It is like a IoT database which stores a lot of information from different devices from the user. I don't think that a normal RDBMS will scale to the requirements. What database (model) will best fit my requirements below?

Scale

The system needs to handle a large number of writes. Each user will have multiple devices which will each output a measurement, the time range over which this measurement was calculated and a value. These measurements range from 10s to 1h in time span. When there is no measurement, the device does not create any data. I expect about 100.000 and 300.000 measurements across all user at a single moment in time but these will not be written to the database immediately. (Number of measurements will grow with the number of users)

Storage

I'm not sure how I should store the measurements. I could store the measurements as a time range:

  +------------+         +--------------+           +--------+
  | User       |         | Measurement  |           | Type   |
  +------------+         +--------------+           +--------+
  | * id       +-------> | * user_id    | <---------+ * id   |
  | * name     |         | * type_id    |           | * type |
  | * group_id |         | * value      |           | * unit |
  +------------+         | * start_time |           +--------+
                         | * timespan   |
                         +--------------+

Note: timespan is in seconds, could also be substituted with end_time.

Or the measurements can be stored as samples over the smallest aggregate possible (10s). When the devices store the values in the database all values are interpolated.

  +------------+         +--------------+           +--------+
  | User       |         | Measurement  |           | Type   |
  +------------+         +--------------+           +--------+
  | * id       +-------> | * user_id    | <---------+ * id   |
  | * name     |         | * type_id    |           | * type |
  | * group_id |         | * value      |           | * unit |
  +------------+         | * start_time |           +--------+
                         +--------------+

Although the time range option has the smallest storage impact, I think that the sample storage might be easier to aggregate.

Queries

The system needs to be able to handle the following types of questions:

• What were user with id X measurements of type Y in the last week grouped by day?

  What was Andy's room temperature in the last week per day?

  How many movies did April watch in the last month per week?

• What is the sum of measurements of type X in user group Y in the last month grouped by week?

  How many book have been read by the Parks department in the last year by month?

• Where do measurements of type X and Y overlap in time (and what is their value) for user with id Z in the past day grouped by hour.

  How many minutes was Donna calling while driving her car today (grouped by hour)?

I expect the last question to be the most difficult one because it needs to intersect multiple timelines to get a answer. (The measurements which are matched will be of the same unit, e.g. both time or both degrees centigrade.) This type of question might be one of the most important questions.

Most questions will be over the last days/week/month/year. There will also be questions which will be over a fixed time range.

All these queries need to be fast. (below 1 second)

Assumptions

I did some assumptions which I think will help improve the performance of the system.

• The larger the time range which was queried, the larger the aggregate will be. Querying the room temperature over the last year per hour does not make sense.
• Measurements which are a long time ago do not have a lot of significance. Querying the number of book read on 11th of January 2013 will not be of much importance.

Solutions

I've looked at some database/storage solutions. So far I have found the following databases but I'm unsure if they will fit our requirements and scale because I do not have any previous experience with working on such a scale.

• InfluxDb (Time range database)
• HBase (Columnar database)
• OpenTSDB

All solutions take a different approach to schema's and I cannot determine if these options are a good fit because I'm used to working basic RDBMS like MSSQL and MySQL.

So my question is:

Which database system will be the best solution for my problem and will this impact the schema I designed?

Answer 1

Database Choice: Given your requirements for scalability, real-time analytics, and efficient time-based queries, you should consider using a time-series database or a columnar database. Some options to explore include:

InfluxDB: InfluxDB is a popular open-source time-series database designed for handling large volumes of time-series data efficiently. It provides native support for time-based queries and aggregation, making it suitable for your use case.

Apache Cassandra: Cassandra is a distributed NoSQL database known for its scalability and high write throughput. It can handle a large number of writes and is suitable for time-series data storage.

Apache Druid (incubating): Druid is designed for real-time analytics and exploration of large datasets. It can handle high ingestion rates and is suitable for interactive queries.

Google Bigtable: If you're open to cloud solutions, Google Bigtable is a highly scalable NoSQL database that can handle large volumes of time-series data. It integrates well with other Google Cloud services.

Schema Design: Your choice of database will influence your schema design. In a time-series database like InfluxDB, you can store data with tags (similar to your user and type) and a timestamp. This allows efficient querying based on tags and time ranges.

For your data samples, you can store them as individual records, including the user ID, type ID, timestamp, and value. You can also add any additional tags or metadata that are relevant to your use case.

Here's a simplified example of a measurement table in InfluxDB:

measurement
-----------
user_id
type_id
timestamp
value

You can add tags to this table for user group, device, or any other relevant metadata.

Queries: With the right schema and database choice, you should be able to execute your desired queries efficiently. InfluxDB, for example, provides powerful query capabilities for time-series data, including aggregations and filtering by tags.

Aggregations: InfluxDB supports various aggregation functions (e.g., mean, sum, count) that you can use to answer questions like the sum of measurements of a specific type within a time range.

Grouping: You can group data by time intervals (e.g., days, weeks) or by tag values (e.g., user, user group) to answer questions like the number of measurements grouped by day or by user group.

Intersecting Timelines: Handling queries that involve intersecting timelines may require custom query logic. You can fetch data for each timeline separately and then perform the intersection and value comparison in your application code.

Scalability: The selected database should be capable of horizontal scalability to accommodate the expected growth in data volume. Ensure that you set up proper data retention policies to manage old data.

Indexing: Consider indexing on timestamp, user ID, and type ID to speed up queries.

Remember to monitor and fine-tune your database performance as your data volume increases to ensure that queries continue to execute within your desired response time.