Choosing a Telemetry Store for a Water Utility with Audit and Join Requirements
A municipal water utility is integrating 12,000 pressure and flow sensors. About 2,000 critical sensors sample every 5 seconds and the rest every minute, producing roughly 1.5 billion readings per month. Operations need dashboards joining live pressure with asset age, pipe material, and open work orders; finance needs monthly billing extracts; regulators require 5 years of raw data and 10 years of one-minute aggregates with an auditable chain of custody. The team has strong SQL skills and two PostgreSQL administrators, but little NoSQL experience. One proposal uses InfluxDB for telemetry plus PostgreSQL for assets, synchronized by a custom job; another uses PostgreSQL with TimescaleDB. The MQTT broker delivers at least once, so duplicates occur during gateway reconnects. The cluster budget covers three nodes with 8 TB of SSD each. A vendor claims the time-series database will make every dashboard query under 200 ms, including joins to work orders. The integration lead asks you to choose a storage design and justify it.
1. Choose between the two storage designs and justify the decision using query patterns, team skills, retention, and operational risk. Address the vendor's 200 ms claim.
Read the worked answer
Choose PostgreSQL with TimescaleDB for the core, and do not run a second primary telemetry store unless a measured query proves it necessary. About 70% of the dashboard value comes from joins between readings and asset/work-order tables; TimescaleDB keeps those in one transactional system with SQL, foreign keys, and one backup/restore process. The team already has two PostgreSQL administrators, so operational risk is lower. InfluxDB plus a custom sync introduces dual-write consistency, a second retention policy, and a reconciliation job; the vendor's 200 ms claim covers only single-series queries, not joins. Size: roughly 567 rows per second average, about 1.5 billion rows per month; five years of raw data is on the order of 90 billion rows, so use native compression, time-based chunk intervals, and partition by sensor class. Keep raw data for 5 years and one-minute continuous aggregates for 10 years. Use three nodes with streaming replication and regular restore tests. If a specific high-cardinality dashboard later fails, add a read cache or a dedicated rollup, not a second source of truth.
2. Propose the schema, retention, and aggregation design, including how to satisfy the regulator's chain-of-custody requirement.
Read the worked answer
Use a narrow readings hypertable with columns time, sensor_id, value, quality, and ingest_id, and a small integer foreign key to a sensor registry; avoid wide tables per site. Enable TimescaleDB compression after about 7 days, segment by sensor_id, and order by time. Create one-minute and one-hour continuous aggregates with refresh policies; retain raw data for 5 years, one-minute aggregates for 10 years, and drop older chunks automatically. Generate billing extracts from a validated, versioned view and store each extract with a hash and an approval record. Audit: make reading and command tables append-only, forbid in-place updates, and maintain periodic hash chains or signed manifests for the regulator's chain of custody. Do not downsample raw data before 5 years; regulators may ask for the original sample. Run retention jobs off-peak, monitor chunk sizes, and test a restore that answers a query from three years ago.
3. The broker delivers at least once and gateways reconnect frequently. Explain how you would deduplicate without damaging the audit trail, and what you would monitor.
Read the worked answer
At-least-once delivery means the same reading can arrive with a new broker message ID after reconnect. Add a deterministic dedupe key: sensor_id + sample_timestamp + a device sequence number when available; store it in a unique index on the raw table or in a staging table and use INSERT ... ON CONFLICT DO NOTHING. If the device lacks a sequence, use sensor_id + timestamp + value hash, but accept that legitimate repeated values at the same timestamp are rare and should be flagged. Keep ingest_id for lineage, not as the dedupe key. For audit, never delete duplicates in place; record rejected duplicates in an ingest audit table with a reason. Dashboards should read from continuous aggregates that already deduplicate, not from raw. Monitor duplicate rate by gateway; a spike usually means a reconnect loop or a misconfigured client, which is an operational finding, not just data noise. Also monitor ingest lag and staging-table growth.