advancedTop 15 Database Design Questions
Design a time-series sensor data database for 10,000 IoT sensors reporting every second.
sensors(id, name, location, type, created_at) readings(sensor_id FK, recorded_at TIMESTAMPTZ NOT NULL, value DECIMAL(10,4), quality INT) Partition by RANGE(recorded_at) — daily or weekly partitions BRIN index on recorded_at (excellent for time-series: tiny index, chronologically ordered data) B-Tree index on (sensor_id, recorded_at DESC) for per-sensor queries TimescaleDB extension: hypertables wi
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response