Storage Tier and Database Consistency for a Bursty Ingest Pipeline
An IoT company ingests telemetry from 20,000 sensors. Each sensor sends one 2 KB message every 10 seconds, producing roughly 345 million messages per day. Messages arrive out of order and duplicates occur when sensors retry. Downstream analytics need to query the last 90 days of data by sensor ID and time range, and a compliance team needs immutable 7-year retention of raw payloads. The current design writes each message directly into a relational database, which is now at 85% disk capacity and showing write latency spikes. Budget is tight and the team has one database administrator.
1. Redesign the storage architecture, choosing storage and database services for the hot analytics path and the cold compliance path. Justify each choice against volume, query pattern, and retention.
Read the worked answer
Volume check. 345M messages/day × 2 KB ≈ 690 GB/day raw, ~63 TB over 90 days, and ~1.8 PB over 7 years before compression. A single relational database is the wrong tool for that write rate and volume, and one DBA cannot operate a sharded cluster.
Hot path (90 days, query by sensor ID + time range):
- Land raw messages in object storage as the durable buffer, partitioned by date and hour. Object storage absorbs the write rate cheaply and gives replayability.
- Process into a time-series-optimized analytical store (columnar/wide-column or a managed time-series database) keyed by sensor ID and timestamp. Time-range scans by sensor are exactly this store's access pattern, and columnar compression cuts the 63 TB substantially.
- Deduplicate on ingest using a deterministic key (sensor ID + sequence number or message ID) so retries are idempotent. Out-of-order arrival is handled by writing with event-time timestamps and letting queries sort, rather than assuming arrival order.
Cold path (7-year immutable retention):
- Use object storage with immutability controls (retention lock / write-once policy) and lifecycle rules to transition older partitions to an archive tier. This meets the compliance requirement at the lowest cost and requires no DBA effort.
Why not keep it relational: write amplification, index maintenance at 4,000 messages/second, and disk-capacity ceilings. The relational database can remain for sensor metadata and configuration - small, transactional, and well within one DBA's scope.
2. The team proposes using the analytical store as the single source of truth and skipping object storage to save cost. Evaluate this proposal.
Read the worked answer
Reject it. Three independent reasons:
- Durability and replay. If the analytical store is the only copy and an ingest bug writes bad data (or a schema change corrupts a partition), there is nothing to replay from. Object storage as the immutable landing zone is the recovery mechanism. Skipping it trades a small storage cost for the loss of the only rollback path.
- Compliance. The 7-year immutable retention requirement is a legal control. An analytical store with mutable partitions and TTL-based expiry does not demonstrate immutability; object storage with retention lock does. The requirement cannot be satisfied by the analytical store regardless of cost.
- Cost is not actually saved. The analytical store is optimized for query, not for cheap bulk retention - storing 7 years there would cost far more than archive-tier object storage. The proposal inverts the economics.
What to do instead: keep object storage as the system of record, treat the analytical store as a derived, rebuildable serving layer, and document the rebuild procedure. Set lifecycle rules so hot object-storage partitions expire after the analytics store has ingested them, retaining only what compliance requires. This keeps cost low while preserving replay and immutability.
The judgement: "single source of truth" is about which copy you can rebuild from, not which copy is queried most.