Database Design for IoT Application

To optimize your database for fast reads, especially for “latest value” queries, there are several strategies and design techniques you can adopt to handle the scale and query performance issues as the number of devices and measurements grows. Here are some key approaches that can significantly improve query performance:

1. Separate Tables for Latest and Historical Data

This is one of the most effective techniques for your scenario. You can create two separate tables:

• MeasurementLatest: Stores only the latest measurement for each device. This table will have a much smaller size compared to the full historical data.
• MeasurementHistory: Stores historical measurements, where older data is stored and used only for the less frequent “daily snapshot” queries.

Why this helps:

• Latest Value Queries (95%): The MeasurementLatest table will always have a single row per device, making the “latest value” queries extremely fast, since it’s a small table with minimal rows to search.
• Historical Queries (5%): For infrequent queries like “daily snapshots,” you can still query the MeasurementHistory table without impacting the performance of more frequent “latest value” queries.