Ingest IoT telemetry into a time-series database with downsampling and retention policies

Steps

1. Choose a time-series database appropriate for your scale: InfluxDB OSS or Cloud for moderate workloads, TimescaleDB (PostgreSQL extension) for SQL familiarity, or Apache IoTDB for high-throughput industrial scenarios
2. Write telemetry from the MQTT broker or stream processor to the DB using its native line protocol or REST API; for InfluxDB use the /api/v2/write endpoint with line protocol format: measurement,tag1=v1 field1=1.0 <nanosecond-timestamp>
3. Define a downsampling task or continuous aggregate: in InfluxDB use a Flux task that runs every 5 minutes to calculate mean, min, max of raw data and write to a separate measurement; in TimescaleDB use CREATE MATERIALIZED VIEW with WITH (timescaledb.continuous)
4. Set a retention policy on the raw data bucket to expire high-resolution data after a shorter window (e.g., 7 days) while retaining downsampled hourly or daily aggregates indefinitely or for a longer period
5. Index on device ID (tag in InfluxDB, indexed column in TimescaleDB) to ensure per-device queries do not perform full table scans; partition time-series tables by time range to keep query performance stable as data volume grows
6. Monitor write throughput, cardinality (number of unique tag value combinations), and query latency; high cardinality (millions of unique tag sets) is the most common cause of memory exhaustion in time-series DBs