IoT Database Design:
Time-Series Database with TimescaleDB & PostgreSQL

Every sensor, every gateway, every connected device in an IoT deployment generates a relentless stream of timestamped measurements. Temperature readings, vibration levels, power consumption, GPS coordinates — these data points arrive by the millions every day. A single fleet of 10,000 sensors reporting every 30 seconds produces over 28 million rows per day. Traditional relational databases, designed for transactional workloads with frequent updates and point lookups, begin to buckle under this kind of append-heavy, time-oriented load.

After building and operating IoT backends that collectively ingest billions of time-series data points per year, we have found that PostgreSQL with the TimescaleDB extension offers the best balance of performance, developer ergonomics, and operational simplicity for the vast majority of IoT use cases. This article walks through the design decisions, table schemas, and configuration patterns that make it work at production scale. For the messaging layer that feeds this database, see our MQTT production infrastructure guide.

The IoT Time-Series Database Challenge

IoT time-series data has a fundamentally different access pattern than conventional OLTP data:

• High write throughput: Thousands to millions of inserts per second, almost all of them appends.
• Append-heavy: Data is written once and rarely modified. Updates are the exception, not the rule.
• Time-range queries: Reads are almost always queries over a time window rather than point lookups on a primary key.
• Ordered by time: New data arrives in chronological order, and queries scan recent time windows.
• Data lifecycle: Recent data is hot, older data is cold. Retention policies matter.

A standard PostgreSQL table without tuning will struggle at around 10,000–50,000 inserts per second due to index maintenance overhead on every write. TimescaleDB solves this by transparently partitioning data into chunks and optimizing the storage engine for time-ordered appends.

Sensor Data Database Table Design: Narrow vs. Medium-Width

The first decision is how to structure your measurement table. In IoT, you generally have two options:

Narrow Format (EAV-style)

CREATE TABLE sensor_reading (
    ts          TIMESTAMPTZ       NOT NULL,
    sensor_id   INT               NOT NULL,
    metric      TEXT              NOT NULL,
    value       DOUBLE PRECISION  NOT NULL
);

Each row stores a single metric. If a sensor reports temperature, humidity, and pressure simultaneously, that is three rows. This format is flexible — adding new metrics requires no schema changes — but queries joining multiple metrics for the same timestamp become verbose.

Medium-Width Format

CREATE TABLE sensor_reading (
    ts            TIMESTAMPTZ       NOT NULL,
    sensor_id     INT               NOT NULL,
    temperature   DOUBLE PRECISION,
    humidity      DOUBLE PRECISION,
    pressure      DOUBLE PRECISION,
    battery_v     DOUBLE PRECISION
);

One row per sensor per reporting interval. NULLs for unused metrics. This format is simpler to query and typically more space-efficient when most metrics are present in each reading. We recommend the medium-width format for production IoT workloads where the set of metrics is known in advance.

TimescaleDB IoT Partitioning with Hypertables

TimescaleDB's core abstraction is the hypertable — a PostgreSQL table that is automatically partitioned (chunked) by time (and optionally by space). To the application and to SQL queries, it looks and behaves exactly like a regular table.

-- Convert sensor_reading into a hypertable
SELECT create_hypertable(
    'sensor_reading',
    'ts',
    chunk_time_interval => INTERVAL '1 day'
);

The chunk_time_interval controls how much time each chunk covers. A good starting point is 1 day for high-ingest workloads or 1 week for moderate ingest. Each chunk is a standalone PostgreSQL table with its own indexes, so inserts into the current chunk do not contend with chunks being queried for historical data. This dramatically reduces write amplification and keeps autovacuum manageable.

Indexing Strategy for IoT Telemetry

Index design is where most IoT time-series databases go wrong. A naive approach is to index every column, but that destroys write throughput. Instead, target the exact query patterns:

-- Primary access pattern: all readings for a sensor over a time range
CREATE INDEX idx_sensor_ts
    ON sensor_reading (sensor_id, ts DESC);

-- Secondary: reverse-chronological scan across all sensors (dashboards)
CREATE INDEX idx_ts_desc
    ON sensor_reading (ts DESC);

-- Optional: partial index for recent data only
CREATE INDEX idx_recent_sensor
    ON sensor_reading (sensor_id, ts DESC)
    WHERE ts > NOW() - INTERVAL '7 days';

The composite index on (sensor_id, ts DESC) is the workhorse. TimescaleDB automatically creates this index on each chunk independently, so index maintenance is parallelized across chunks. The partial index on recent data is useful when your dashboards primarily query the last 7 days — it keeps the index small and fast.

TimescaleDB Continuous Aggregates for IoT Analytics

Querying raw sensor readings for dashboard visualizations at scale is unnecessarily expensive. A dashboard showing the average temperature over the last 24 hours does not need 86,400 individual readings — it needs 1,440 one-minute averages (or fewer).

-- 1-minute rollup
CREATE MATERIALIZED VIEW sensor_reading_1m
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', ts) AS bucket,
    sensor_id,
    AVG(temperature)  AS avg_temp,
    MAX(temperature)  AS max_temp,
    MIN(temperature)  AS min_temp,
    AVG(humidity)     AS avg_humidity
FROM sensor_reading
GROUP BY bucket, sensor_id;

-- 1-hour rollup from the 1-minute view
CREATE MATERIALIZED VIEW sensor_reading_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', bucket) AS bucket,
    sensor_id,
    AVG(avg_temp)   AS avg_temp,
    MAX(max_temp)   AS max_temp,
    MIN(min_temp)   AS min_temp
FROM sensor_reading_1m
GROUP BY bucket, sensor_id;

-- Configure refresh policy
SELECT add_continuous_aggregate_policy('sensor_reading_1m',
    start_offset => INTERVAL '3 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 minute'
);

Continuous aggregates are incrementally maintained — only new or changed data is re-aggregated on each refresh, not the entire dataset. The end_offset parameter leaves a 1-hour window of raw data unaggregated to handle late-arriving data from devices with intermittent connectivity.

IoT Data Retention and Tiered Storage

Raw sensor data older than 90 days is rarely queried at the individual-reading level, but aggregated data remains useful for trend analysis. TimescaleDB's drop_chunks() makes retention trivial:

-- Drop raw data older than 90 days
SELECT drop_chunks(
    'sensor_reading',
    older_than => INTERVAL '90 days'
);

-- Automate with a cron job or pg_cron
SELECT add_job(
    'drop_chunks_job',
    INTERVAL '1 day',
    $$ SELECT drop_chunks('sensor_reading', older_than => INTERVAL '90 days') $$
);

We recommend a three-tier retention policy:

• Hot tier (0–7 days): Full-resolution raw data on fast SSDs.
• Warm tier (7–90 days): Raw data compressed (see below), on standard storage.
• Cold tier (90–365 days): Only 1-hour and 1-day continuous aggregates retained.

TimescaleDB Compression for Sensor Data

TimescaleDB's native compression uses columnar storage for chunks that are no longer being written to. For IoT data, compression ratios typically exceed 90%, reducing 100 GB of sensor data to under 10 GB:

ALTER TABLE sensor_reading SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'ts DESC'
);

-- Compress chunks older than 7 days
SELECT add_compression_policy('sensor_reading', INTERVAL '7 days');

The compress_segmentby parameter determines how rows are grouped for compression. sensor_id is a good choice because queries always filter by sensor. The compress_orderby setting enables efficient time-range pruning on compressed data without decompressing the entire chunk.

Queries on compressed data are slower than uncompressed (roughly 2–5x), but for historical analytics this is an acceptable trade-off when storage costs drop by an order of magnitude.

PostgreSQL IoT Telemetry Connection Pooling

IoT backends often handle thousands of concurrent device connections, each sending data. Opening a PostgreSQL connection per device is not viable — PostgreSQL's connection-per-fork model hits memory limits around a few hundred active connections. PgBouncer in transaction-pooling mode solves this:

-- pgbouncer.ini excerpt
[databases]
iot = host=localhost port=5432 dbname=iot

[pgbouncer]
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000

With transaction pooling, each device connection is held only for the duration of a single INSERT transaction, then returned to the pool. Fifty backend connections can comfortably handle thousands of concurrent device connections.

Combined with prepared statements and batch inserts, you can sustain over 100,000 inserts per second on modest hardware:

-- Batch insert for efficiency
INSERT INTO sensor_reading (ts, sensor_id, temperature, humidity)
SELECT unnest($1::TIMESTAMPTZ[]),
       unnest($2::INT[]),
       unnest($3::DOUBLE PRECISION[]),
       unnest($4::DOUBLE PRECISION[]);

Time-Series Gap-Filling and Interpolation

IoT devices frequently miss reporting intervals due to network issues, power cycles, or sleep schedules. Dashboards that skip missing intervals produce misleading visualizations. TimescaleDB's time_bucket_gapfill() function handles this natively:

SELECT
    time_bucket_gapfill('1 minute', ts) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    LOCF(AVG(temperature)) AS filled_temp
FROM sensor_reading
WHERE ts BETWEEN '2026-10-01' AND '2026-10-02'
  AND sensor_id = 42
GROUP BY bucket, sensor_id
ORDER BY bucket;

LOCF() (Last Observation Carried Forward) fills gaps with the most recent known value. TimescaleDB also provides interpolate() for linear interpolation between surrounding points — useful for continuous metrics like temperature where a linear approximation is more accurate than repeating the last value.

Real-World IoT Database Design Schema

Here is a production-ready schema that combines all the patterns discussed above:

CREATE TABLE sensor_reading (
    ts              TIMESTAMPTZ       NOT NULL,
    sensor_id       INT               NOT NULL,
    temperature     DOUBLE PRECISION,
    humidity        DOUBLE PRECISION,
    pressure        DOUBLE PRECISION,
    battery_v       DOUBLE PRECISION,
    rssi            INT,
    tags            JSONB
);

-- Convert to hypertable (1-day chunks)
SELECT create_hypertable('sensor_reading', 'ts',
    chunk_time_interval => INTERVAL '1 day');

-- Indexes
CREATE INDEX idx_sensor_ts
    ON sensor_reading (sensor_id, ts DESC);
CREATE INDEX idx_ts_desc
    ON sensor_reading (ts DESC);

-- Continuous aggregates
CREATE MATERIALIZED VIEW sensor_reading_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       sensor_id,
       AVG(temperature) AS avg_temp,
       MAX(temperature) AS max_temp,
       AVG(humidity)    AS avg_humidity
FROM sensor_reading
GROUP BY bucket, sensor_id;

-- Compression for chunks older than 7 days
ALTER TABLE sensor_reading SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'ts DESC'
);
SELECT add_compression_policy('sensor_reading', INTERVAL '7 days');

-- Auto-drop raw data after 90 days
SELECT add_retention_policy('sensor_reading', INTERVAL '90 days');

When Not to Use TimescaleDB for IoT

TimescaleDB is an excellent choice for most IoT backends, but it is not universal. Consider alternatives when:

• Sub-millisecond point lookups: If your workload needs to retrieve a specific reading at a specific nanosecond for thousands of sensors simultaneously, a key-value store like ScyllaDB or Redis may be faster.
• High-update workloads: TimescaleDB optimizes for append. If you frequently update individual readings, the chunk-based architecture can lead to write amplification. Consider a row-oriented store.
• High cardinality tags at extreme scale: If every reading has hundreds of unique tag combinations and you need instant ad-hoc filtering, a columnar store like ClickHouse may suit better.

IoT Database Design Conclusion

PostgreSQL with TimescaleDB is the right choice for the vast majority of IoT backends. You get the full power of SQL — JOINs, window functions, CTEs, JSONB — combined with a storage engine optimized for time-ordered appends, automatic partitioning, built-in compression, and hands-off data lifecycle management. Your development team already knows PostgreSQL. Your ORM, your BI tools, your monitoring stack all work without modification.

Time-series data is the backbone of IoT, and PostgreSQL + TimescaleDB handles it better than anything else at this point in the maturity curve. For combining time-series analytics with edge intelligence, read our TinyML on ESP32 guide.