CODEWITHSUNDEEP
sqldatabase-designnosql
CodeTower
CodeTower

Reputation: 6333

How to store real-time measurements with high read performance

We are developing a project which needs to handle large quantities of measurements. The system will likely receive 10.000 measurements per minute for this project.

The data is simple and looks like: 

 device ID  | measurement_type |  a time stamp |  floating point value

There are 20-30 measurement_types pr. device. Each measurement is taken every 5 minute.

The write performance is not critical, but the system much be optimized for reading. If the system is implemented in sql most queries will be of the form:

select * value 
from measurements 
where 
    device_id = :id and 
    measurements_type = :typeid and 
    start_time between :start and :stop

How should such a system be designed for high performance read?

One of our ideas is to create 2 adjacent tables, one storing hour values and one storing day values. a service is then implement to aggregate the 5 minutes values into hour value and hours into day.

What about other systems than SQL-based would they make sense for fast reads?

Upvotes: 0

Views: 120

Answers (1)

Svein Fidjestøl
Svein Fidjestøl

Reputation: 3206

Given that you are going to use a SQL based system:

If you want fast reads it is important to set your indexes correctly so that you get an INDEX SEEK instead of an INDEX SCAN.

Looking at your query it seems that you would likely need an index on device_id, measurements_type and start_type, with value as an included column on that index. More information about this: Why is SQL Server not using Index for very similar datetime query?

Also, it is important that you use the same data types as your parameter values to the query so that the index is actually used for your queries. Using SQL Server as an example, this can be verified using the Show Actual Execution Plan functionality in SQL Server Management Studio.

Upvotes: 1

Related Questions