Challenges with SQL data retrieval for IoT sensor aggregation using SQLite on embedded devices
Could someone explain Could someone explain I'm stuck on something that should probably be simple. I've been struggling with this for a few days now and could really use some help. Currently developing an IoT application where multiple sensors feed data into an SQLite database running on an embedded device. The goal is to aggregate sensor readings to analyze trends over time. I've set up a simple database schema, which looks like this: ```sql CREATE TABLE sensor_data ( id INTEGER PRIMARY KEY, sensor_id TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, value REAL ); ``` To retrieve aggregated data, I tried executing the following SQL query to get the average values per sensor for the last 24 hours: ```sql SELECT sensor_id, AVG(value) as avg_value FROM sensor_data WHERE timestamp >= DATETIME('now', '-1 day') GROUP BY sensor_id; ``` The query performs well with a small dataset, but as the number of records grows, the performance deteriorates significantly. After profiling the application, I found that querying a large amount of historical data causes delays. Additionally, I noticed that the disk space is limited, which further complicates data retention strategies. To mitigate the performance issues, I implemented an index on the `timestamp` column: ```sql CREATE INDEX idx_timestamp ON sensor_data(timestamp); ``` Despite this, the performance still isn't optimal. I’ve considered batch processing the data and storing aggregated results in a separate table to reduce load. However, implementing this requires careful management of real-time data flow to prevent data loss. Looking for advice on best practices for managing SQL queries in SQLite for embedded systems, especially in the context of optimizing read operations and efficiently aggregating data without overwhelming the limited device resources. Any insights on alternative data storage solutions or indexing strategies would also be appreciated. My development environment is Windows. I'd really appreciate any guidance on this. For context: I'm using Sql on macOS. Any help would be greatly appreciated! This is happening in both development and production on Ubuntu 22.04. Is there a better approach? I'm developing on Ubuntu 20.04 with Sql. What's the best practice here?