How to Store IoT Sensor Data in a Database

The problem and the reader

You have devices that report numbers on a schedule: a BMS exposing AHU supply-air temperature and zone CO2 every minute, chiller power meters, electricity sub-meters every 15 minutes, environmental sensors in plant rooms. You need to keep the readings, show trends and latest values, produce reports, feed alarms and maintenance rules, and delete old data on a schedule without taking the system down.

This guide is for developers and data engineers building that store in SQL. It uses plain PostgreSQL so that every statement can be run anywhere; the same design carries over to TimescaleDB, which automates the partitioning step, and the ideas apply to other engines. For what a time-series database is and how the pipeline fits together, start with the time-series overview.

Data model: asset, point, timestamp, value, quality

Model three things separately:

• Asset: the equipment (AHU-01, CH-01, a meter), with site, type and the ID your CAFM or CMMS uses for it.
• Point: one measured quantity on an asset (supply_air_temp, zone_co2, power_kw), with unit and expected interval. In BMS terms this is the point or object; in MQTT terms it often maps to a topic.
• Reading: one value of one point at one timestamp, with a quality code (0 = good; other values for estimated, out of range, communication failure, manual entry).

Keep names and units out of the readings table. A renamed BMS point then becomes an update to one row in point, not to millions of readings, and the readings stay small. Store time as timestamptz (8 bytes, microsecond resolution) and treat it as the measurement time from the device or gateway, not the insert time.

Use double precision (8 bytes) for values unless you know the range and precision fit real (4 bytes, about 6 significant digits). Avoid numeric for raw readings: the PostgreSQL documentation describes calculations on it as very slow compared with integer and floating-point types, and sensors do not deliver exact decimals anyway.

CREATE TABLE asset (
    asset_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    site_code   text NOT NULL,
    asset_tag   text NOT NULL UNIQUE,
    asset_type  text NOT NULL
);

CREATE TABLE point (
    point_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    asset_id    integer NOT NULL REFERENCES asset (asset_id),
    point_name  text NOT NULL,
    unit        text NOT NULL,
    interval_s  integer NOT NULL,
    UNIQUE (asset_id, point_name)
);

INSERT INTO asset (site_code, asset_tag, asset_type) VALUES
    ('HQ', 'AHU-01', 'air handling unit'),
    ('HQ', 'CH-01',  'chiller');

INSERT INTO point (asset_id, point_name, unit, interval_s) VALUES
    (1, 'supply_air_temp', 'degC', 60),
    (1, 'zone_co2',        'ppm',  60),
    (2, 'power_kw',        'kW',   60);

Partition the readings table by time

PostgreSQL declarative partitioning splits one logical table into child tables by a key range. For readings, partition by ts. The primary key must include the partition key, so (point_id, ts) works and also serves the main query pattern.

This example uses monthly partitions for one-minute building data; at higher rates, smaller partitions (for example daily) keep each partition and its indexes smaller. Treat both as illustrative starting points rather than rules: the choice depends on rows per partition, how far back typical queries reach and the retention step you want. The PostgreSQL documentation warns in both directions: too few partitions leave indexes large, while too many lengthen query planning and raise memory use. It notes the planner handles up to a few thousand partitions fairly well when queries prune all but a few.

CREATE TABLE reading (
    point_id    integer NOT NULL,
    ts          timestamptz NOT NULL,
    value       double precision,
    quality     smallint NOT NULL DEFAULT 0,
    PRIMARY KEY (point_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE reading_2026_09 PARTITION OF reading
    FOR VALUES FROM ('2026-09-01 00:00+00') TO ('2026-10-01 00:00+00');
CREATE TABLE reading_2026_10 PARTITION OF reading
    FOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');
CREATE TABLE reading_2026_11 PARTITION OF reading
    FOR VALUES FROM ('2026-11-01 00:00+00') TO ('2026-12-01 00:00+00');

CREATE INDEX reading_ts_brin ON reading USING brin (ts);

INSERT INTO reading (point_id, ts, value)
SELECT p.point_id,
       g.ts,
       CASE p.point_id WHEN 1 THEN 13 + (extract(minute FROM g.ts) % 5) * 0.2
                       WHEN 2 THEN 450 + extract(minute FROM g.ts)
                       ELSE 120 + extract(minute FROM g.ts) / 2 END
FROM point p
CROSS JOIN generate_series('2026-09-30 22:00+00'::timestamptz,
                           '2026-10-01 01:59+00'::timestamptz,
                           interval '1 minute') AS g(ts);

SELECT tableoid::regclass AS partition, count(*) AS rows
FROM reading GROUP BY 1 ORDER BY 1;

    partition    | rows
-----------------+------
 reading_2026_09 |  360
 reading_2026_10 |  360

Partition pruning: the query plan touches one partition

A query with a plain range predicate on ts lets the planner skip partitions that cannot contain matching rows. The plan below, from the same run, reads only the October partition, through its primary-key index:

EXPLAIN (COSTS OFF)
SELECT ts, value FROM reading
WHERE point_id = 1
  AND ts >= '2026-10-01 00:00+00' AND ts < '2026-10-01 02:00+00';

--  Index Scan using reading_2026_10_pkey on reading_2026_10 reading
--    Index Cond: ((point_id = 1) AND (ts >= '2026-10-01 00:00:00+00'::timestamp with time zone)
--                 AND (ts < '2026-10-01 02:00:00+00'::timestamp with time zone))

Create partitions ahead of time

Without a matching partition, an insert fails. This is the error from the same run when a December reading arrived with no December partition:

INSERT INTO reading (point_id, ts, value) VALUES (1, '2026-12-05 08:00+00', 13.4);

ERROR:  no partition of relation "reading" found for row
DETAIL:  Partition key of the failing row contains (ts) = (2026-12-05 08:00:00+00).
SQLSTATE 23514

Indexes for sensor readings

Start with the primary key (point_id, ts). A multicolumn B-tree is most efficient when the query constrains the leading column, so it serves "point X between A and B" and "latest value of point X" directly. Put point_id first, not ts: dashboards ask for a few points over a range far more often than for all points at one instant.

For queries across all points in a time window (a site-wide report for yesterday), a BRIN index on ts is a small, cheap option. BRIN stores a summary per block range and works when the column correlates with physical order, which is true for append-mostly readings arriving roughly in time order. It becomes less useful if a large share of data arrives very late or is loaded out of order.

Partition pruning does not need an index on the partition key; it is driven by the partition bounds. Every extra index costs write work on each insert, so add indexes only for query patterns you actually run. For background on how B-tree indexes work, see SQL indexes explained for beginners.

Raw and aggregated tables

Most reports do not need one-minute resolution after the first few weeks. Keep a rolled-up table with the average, minimum, maximum and number of samples per point per bucket. Storing the sample count lets you spot gaps and re-weight averages later. Exclude bad-quality readings from the roll-up, and make the job idempotent with ON CONFLICT ... DO UPDATE so it can be re-run for a window when late data arrives.

CREATE TABLE reading_15m (
    point_id    integer NOT NULL,
    bucket      timestamptz NOT NULL,
    avg_value   double precision,
    min_value   double precision,
    max_value   double precision,
    samples     integer NOT NULL,
    PRIMARY KEY (point_id, bucket)
);

INSERT INTO reading_15m
SELECT point_id,
       date_bin(interval '15 minutes', ts, timestamptz '2000-01-01 00:00+00') AS bucket,
       avg(value), min(value), max(value), count(*)
FROM reading
WHERE quality = 0
  AND ts >= '2026-10-01 00:00+00' AND ts < '2026-10-01 01:00+00'
GROUP BY 1, 2
ON CONFLICT (point_id, bucket) DO UPDATE
SET avg_value = EXCLUDED.avg_value, min_value = EXCLUDED.min_value,
    max_value = EXCLUDED.max_value, samples = EXCLUDED.samples;

SELECT p.point_name, r.bucket, round(r.avg_value::numeric, 2) AS avg_value, r.samples
FROM reading_15m r JOIN point p USING (point_id)
WHERE p.point_name = 'supply_air_temp'
ORDER BY r.bucket;

   point_name    |         bucket         | avg_value | samples
-----------------+------------------------+-----------+---------
 supply_air_temp | 2026-10-01 00:00:00+00 |     13.40 |      15
 supply_air_temp | 2026-10-01 00:15:00+00 |     13.40 |      15
 supply_air_temp | 2026-10-01 00:30:00+00 |     13.40 |      15
 supply_air_temp | 2026-10-01 00:45:00+00 |     13.40 |      15

Materialized views or tables

A PostgreSQL materialized view can hold the same roll-up, but REFRESH MATERIALIZED VIEW recomputes the whole view each time. For a roll-up that grows every 15 minutes, our editorial view is that a table refreshed one window at a time is often more practical. TimescaleDB's continuous aggregates automate incremental refresh; Continuous aggregates vs materialized views compares the approaches and sets out the recommended plain PostgreSQL schedule: recompute a bounded recent window on every run, plus a periodic reconciliation.

Retention: drop partitions, not rows

Retention for raw readings becomes a partition operation. The PostgreSQL documentation notes that dropping a partition, or detaching it, is far faster than a bulk DELETE and avoids the vacuum work a large delete creates. Detaching first lets you archive the table (for example with pg_dump or an export to a data lake) before dropping it.

ALTER TABLE reading DETACH PARTITION reading_2026_09;
DROP TABLE reading_2026_09;

SELECT tableoid::regclass AS partition, count(*) AS rows
FROM reading GROUP BY 1 ORDER BY 1;
--     partition    | rows
-- -----------------+------
--  reading_2026_10 |  360

Sizing: a worked estimate

The figures below are an estimate from stated assumptions, not a measurement, and not a universal per-row size. The assumptions: the column types shown, 8-byte alignment as on most 64-bit servers, an assumed index size, a campus of 5,000 points, the intervals stated and the retention periods from the illustrative plan above. Real size depends on alignment, free space, indexes, dead rows from updates, WAL, replicas and backups, so check with pg_total_relation_size() on your own data once you have loaded a representative sample. The storage sizing guide shows that method on a small PGlite sample, where the same narrow layout took 60.3 table bytes and 33.1 index bytes per row; that is an illustrative measurement (PostgreSQL 18.6 compiled to 32-bit WebAssembly), not a production benchmark and not representative of your server.

Bytes per raw row (estimate). The PostgreSQL page layout documentation gives a fixed row header of 23 bytes and a 4-byte item pointer per row. The columns are integer (4 bytes), timestamptz (8), double precision (8) and smallint (2). With alignment padding (the header rounds to 24 bytes; the timestamp aligns to 8 bytes, adding 4 bytes after point_id) the row is about 24 + 26 = 50 bytes, rounded up to 56, plus the 4-byte pointer: about 60 bytes per row in the table. Assume about 30 bytes per row for the (point_id, ts) primary-key index (an assumption for this estimate), giving about 90 bytes per reading.

Bytes per roll-up row (estimate). Two keys, three doubles and an integer count: about 72 bytes plus a 4-byte pointer, and an index entry of about 30 bytes: about 106 bytes per bucket.

What the estimate tells you

On these assumptions, one-minute sampling produces tens of gigabytes of raw data and roll-ups, a volume many single PostgreSQL servers can hold; whether yours can depends on its disks, the WAL, replica and backup copies, and headroom. At one-second sampling across every point, the estimate grows sixty-fold, and uncompressed row storage can become the dominant cost. That is where it becomes worth evaluating columnar compression (TimescaleDB, QuestDB, InfluxDB and others) or sampling only selected points at a high rate, by measuring a sample of your own data. Reordering columns so the 8-byte ones come first removes the 4 bytes of padding per row in this layout; small at one row, noticeable at billions.

Ingestion: batches, duplicates and late data

• Batch writes. The PostgreSQL documentation recommends COPY for loading many rows, or several INSERTs in one transaction, because committing each row separately does a lot of work per row. An ingestion service that collects readings for a second or two and writes them together avoids that per-row commit work, which one transaction per message would repeat for every reading.
• Duplicates. MQTT QoS 1 and Kafka consumers that restart can both deliver a reading twice. With the (point_id, ts) primary key, INSERT ... ON CONFLICT (point_id, ts) DO NOTHING keeps the first copy; this ran without error in the example above. Without it, a duplicate raises a unique-violation error (SQLSTATE 23505).
• Late data. A gateway that reconnects after an outage sends old readings. They land in older partitions, so do not drop a partition the moment it falls outside the window; keep a grace period, and re-run the roll-up for affected windows (the upsert above makes a re-run idempotent; it was run in a single session only, and the recommended schedule for re-runs is covered in late-arriving and out-of-order data).
• Buffering. Kafka keeps events durably for a configured retention period and consumers can re-read them, so it is a useful buffer when the database is down for maintenance; an MQTT broker is not a long-term buffer.

Security, operations and when this design is not enough

Security. Give the ingestion service a role that can only insert into the readings table (and create partitions, if it does that job). Dashboards read through a read-only role or views. Store credentials outside code and use TLS between gateway, broker and database.

Operations. Monitor ingestion lag, rows rejected, the existence of next month's partition and disk usage. Back up metadata tables and roll-ups as carefully as raw data: losing the point table makes raw readings unreadable. To try the SQL on this page without installing anything, use the PostgreSQL playground; to run a local server, see PostgreSQL in Docker.

When not to use this design. If you sample many points at one second or faster, need years of raw retention, or need compression to keep storage affordable, consider a time-series extension or engine; see TimescaleDB vs PostgreSQL and the time-series database selection guide. If your devices report irregular JSON payloads with many changing fields, decide which fields become points before storing them; a jsonb column of everything is easy to write and hard to query. For what drives speed once the data is in, read Time-Series Database Performance.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Table Partitioning
• PostgreSQL 18 documentation: BRIN Indexes
• PostgreSQL 18 documentation: Multicolumn Indexes
• PostgreSQL 18 documentation: Populating a Database
• PostgreSQL 18 documentation: INSERT
• PostgreSQL 18 documentation: Date/Time Types
• PostgreSQL 18 documentation: Numeric Types
• PostgreSQL 18 documentation: Database Page Layout
• Apache Kafka: Introduction
• OASIS MQTT Version 5.0 specification

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.