After completing this chapter, you will be able to:
Time-series databases are purpose-built for storing and querying timestamped data–the dominant pattern in IoT sensor telemetry. They achieve 10-100x better performance than generic SQL databases for time-series workloads through specialized optimizations.
| Feature | Time-Series DB | Generic SQL |
|---|---|---|
| Write throughput | 100K-1M/sec | 10K-50K/sec |
| Time-range queries | Optimized (partition pruning) | Full table scan |
| Compression | 90-95% automatic | Manual, limited |
| Retention policies | Built-in | Custom scripts |
| Continuous aggregates | Native | Materialized views |
Use this calculator to estimate storage requirements for your IoT deployment:
TimescaleDB extends PostgreSQL with hypertables–automatically partitioned tables optimized for time-series data.
| Feature | Standard Table | Hypertable |
|---|---|---|
| Query recent data | Full table scan | Scan 1 chunk |
| Delete old data | Slow DELETE | Instant DROP chunk |
| Parallel processing | Limited | Multi-chunk concurrent |
| Index management | One large index | Per-chunk indexes |
-- Main sensor readings table
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
device_id VARCHAR(50) NOT NULL,
value DOUBLE PRECISION,
quality SMALLINT DEFAULT 100
);
-- Convert to hypertable with 1-day chunks
SELECT create_hypertable('sensor_readings', 'time',
chunk_time_interval => INTERVAL '1 day');
-- Index for common queries
CREATE INDEX ON sensor_readings (device_id, time DESC);-- Enable compression
ALTER TABLE sensor_readings SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device_id'
);
-- Compress data older than 7 days
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');Compression results: 90-95% storage reduction for typical sensor data.
-- Automatically drop chunks older than 90 days
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');Why it’s fast: Dropping entire chunks is a metadata operation (milliseconds), not row-by-row deletion (hours for millions of rows).
Pre-compute common aggregations for dashboard queries:
-- Create hourly summary
CREATE MATERIALIZED VIEW hourly_summary
WITH (timescaledb.continuous) AS
SELECT
device_id,
time_bucket('1 hour', time) AS hour,
AVG(value) AS avg_value,
MIN(value) AS min_value,
MAX(value) AS max_value,
COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY device_id, hour;
-- Auto-refresh every 30 minutes
SELECT add_continuous_aggregate_policy('hourly_summary',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '30 minutes');Performance impact: Dashboard queries go from 45 seconds (500M rows) to 10ms (hourly aggregates).
The Mistake: Creating composite indexes on (device_id, timestamp) without considering that each unique device creates a separate index entry per time interval, leading to index bloat that exceeds the data itself.
Why It Happens: Developers apply relational database indexing patterns to time-series data. In a traditional database, indexing a user_id column with 100,000 users creates 100,000 index entries. But in time-series, indexing (device_id, timestamp) for 100,000 devices with 1-second resolution creates 100,000 x 86,400 = 8.64 billion entries per day.
The Fix: Use time-partitioned tables with partition-local indexes:
-- PostgreSQL/TimescaleDB: Partition by time, index within partitions
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
device_id TEXT NOT NULL,
value DOUBLE PRECISION
);
-- TimescaleDB automatically partitions by time
SELECT create_hypertable('sensor_readings', 'time',
chunk_time_interval => INTERVAL '1 day');
-- Index is created per-chunk, not globally
CREATE INDEX ON sensor_readings (device_id, time DESC);
-- Each daily chunk has ~8.64M rows for 100K devices at 1Hz
-- Index per chunk: ~200 MB vs global index: ~73 GB for 1 yearCardinality calculation: For 100,000 devices at 1 Hz: - Daily rows: 100,000 x 86,400 = 8.64 billion rows/year - Global B-tree index: ~8 bytes/entry x 8.64B = 69 GB index overhead - Chunked index (365 daily chunks): 365 x 200 MB = 73 GB, but queries only scan relevant chunks
The Mistake: Using TIMESTAMP instead of TIMESTAMPTZ (timestamp with timezone), causing silent data corruption when devices or servers change timezones, or when DST transitions occur.
Why It Happens: TIMESTAMP (without timezone) is shorter to type and appears simpler. Developers assume “we’ll always use UTC” but don’t enforce it at the schema level.
The Fix: Always use timezone-aware timestamp types and enforce UTC:
-- WRONG: Timezone-naive timestamp
CREATE TABLE sensor_data (
time TIMESTAMP NOT NULL, -- Ambiguous!
device_id TEXT,
value DOUBLE PRECISION
);
-- CORRECT: Timezone-aware timestamp
CREATE TABLE sensor_data (
time TIMESTAMPTZ NOT NULL, -- Stores UTC, displays in session TZ
device_id TEXT,
value DOUBLE PRECISION
);
-- Enforce UTC on insert (PostgreSQL)
ALTER DATABASE iot_db SET timezone TO 'UTC';Storage difference: Both TIMESTAMP and TIMESTAMPTZ use 8 bytes in PostgreSQL–there’s no storage penalty for timezone awareness.
Continue with Data Quality Monitoring to learn how to validate and monitor data quality in IoT database systems.
Related Chapters: - Database Selection Framework - Choosing the right database - CAP Theorem - Distributed systems trade-offs - Sharding Strategies - Horizontal scaling