I am writing an application for viewing and management of sensor data. I can have unlimited number of sensors, and each sensors makes one reading every minutes and records the values as (time, value, sensor_id, location_id, [a bunch of other doubles]).
As an example, I might have 1000 sensors and collect data every minute for each one of them, which ends up generating 525,600,000 rows after a year. Multiple users (say up to 20) can plot the data of any time period, zoom in and out in any range, and add annotations to the data of a sensor at a time. Users can also modify certain data points and I need to keep track of the raw data and modified one.
I'm not sure how the database for application like this should look like! Should it be just one table SensorData, with indices for time and sensor_id and location_id? Should I partition this single table based on sensor_id? should I save the data in files for each sensor each day (say .csv files) and load them into a temp table upon request? How should I manage annotations?
I have not decided on a DBMS yet (maybe MySQL or PostgreSQL). But my intention is to get an insight about data management in applications like this in general.
Looking for an answer I came across this thread. While it is not entirely the same as my case, it answers many of my questions; such as is using a relational database a reasonable way of doing this (to which the answer is "Yes"), and what to do about partitioning, maintenance, archiving, etc.