Choosing Storage for Rapidly Growing Sensor Data
An agricultural technology company collects readings from 8,000 field sensors every 30 seconds, producing about 700 GB per month of small JSON records. Data older than 90 days is read only a few times per year for trend analysis, but must be retained for seven years. The current design writes each reading to a relational database, and query latency has climbed from 40 ms to 2.5 seconds as the table passed two billion rows. The team wants fast dashboards on the last 30 days of data and cheap long-term retention for the rest, with the ability to run occasional large analytical queries over the full history.
1. Diagnose why the relational database is failing and propose a storage split.
Read the worked answer
The database is doing two jobs with opposite requirements: high-frequency small writes plus low-latency recent reads, and cheap multi-year retention with occasional scans. At 700 GB per month and two billion rows, the single table makes every index large and every dashboard query compete with ingestion. The fix is to split hot and cold paths.
Send incoming readings to a time-series-optimized store or a partitioned table for the last 30 days to serve dashboards. Archive older records to object storage in a columnar format, partitioned by date, so trend queries scan only relevant partitions. Keep the relational database for metadata such as sensor registry and calibration, not raw readings. This aligns cost with access frequency and restores dashboard latency.
2. Recommend a lifecycle approach for the seven-year retention requirement and justify the trade-offs.
Read the worked answer
Write raw records to object storage as the durable source of truth, then apply a lifecycle policy: keep the most recent 90 days in a standard tier for occasional reads, transition months 4 through 12 to an infrequent-access tier, and move older partitions to an archival tier. Because trend analysis happens only a few times per year, archival retrieval latency and fees are acceptable, but the team must budget for retrieval cost on the rare full-history query and restore objects before analysis.
The trade-off is that archival tiers are not instantly queryable, so the team should keep a small derived summary table of monthly aggregates in a fast tier for common questions. That way routine trends avoid retrieval entirely, and the expensive path is reserved for genuinely deep analysis. Document the restore procedure so an analyst is not surprised mid-query.
3. The team proposes keeping all seven years in the relational database for simplicity. Argue against it.
Read the worked answer
Simplicity that produces 2.5-second dashboard latency is not simple for users. Keeping all history in the database means paying for high-performance storage and compute sized for the largest table, while most of that data is read a few times per year. It also couples retention risk to a single system: a migration or upgrade must handle two billion rows and growing.
A split design is more work up front but cheaper and more resilient. The object store provides durable, low-cost retention with independent lifecycle rules, and the hot store stays small and fast. The occasional analytical query can run against archived partitions without touching production dashboards. The team should invest once in a documented archive-and-query workflow rather than continuously paying to keep cold data hot.