Time-Series Schema Design: Narrow vs Wide Tables

Scope: the one decision this page covers

This page is for developers and data engineers designing the readings table of a building, energy or industrial IoT system: BMS points such as AHU supply-air temperature and zone CO2, chiller power, sub-meters, and the asset and work-order tables of a CAFM or CMMS that sit next to them. It answers one question: should a reading be a row, or a column in a row?

It does not repeat the general storage design (asset and point register, monthly partitions, roll-ups, retention), which is in How to Store IoT Sensor Data, or measure bytes per reading, which Sizing Storage for One-Second Sensor Telemetry does for both shapes. Index design and tag cardinality have their own pages: time-series indexing and high-cardinality tags.

The data model: narrow, wide and hybrid

• Narrow (also called tall or long): (point_id, ts, value, quality). Each BMS point, meter register or sensor channel has a row in a point register that holds its asset, name, unit and expected interval. A new point is a new register row; the readings table never changes shape.
• Wide: (asset_id, ts, supply_air_temp, return_air_temp, zone_co2, fan_speed_pct). One row per device per timestamp. The schema documents the measurements and their types, and per-column constraints are possible, but every device in the table must share that column list.
• Hybrid: a wide table per equipment class whose devices report a fixed block together (for example a power meter that returns kW, kWh, power factor and three phase voltages in one poll), plus a narrow table for the long tail of BMS points. Both join to the same asset register, so a CMMS work order on a chiller can be shown next to either.

The script below creates both shapes for one AHU and loads an hour of one-minute data. Fan speed is only sampled every 15 minutes, as often happens when a point is polled at a slower rate or reported on change of value. Converting wide to narrow is an unpivot with CROSS JOIN LATERAL (VALUES ...); NULLs are simply not inserted.

SET TIME ZONE 'UTC';

-- Narrow: one row per point per timestamp
CREATE TABLE reading_narrow (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    quality   smallint NOT NULL DEFAULT 0,
    PRIMARY KEY (point_id, ts)
);

-- Wide: one row per AHU per timestamp, one column per measurement
CREATE TABLE ahu_reading_wide (
    asset_id         integer NOT NULL,
    ts               timestamptz NOT NULL,
    supply_air_temp  double precision,
    return_air_temp  double precision,
    zone_co2         double precision,
    fan_speed_pct    double precision,
    PRIMARY KEY (asset_id, ts)
);

-- One hour of AHU-01 data: three values every minute, fan speed only every 15 minutes
INSERT INTO ahu_reading_wide
SELECT 1, g.ts,
       13 + (extract(minute FROM g.ts)::int % 5) * 0.2,
       22 + (extract(minute FROM g.ts)::int % 3) * 0.1,
       450 + extract(minute FROM g.ts),
       CASE WHEN extract(minute FROM g.ts)::int % 15 = 0 THEN 80 END
FROM generate_series(timestamptz '2026-10-27 08:00+00', timestamptz '2026-10-27 08:59+00', interval '1 minute') AS g(ts);

-- Wide to narrow (unpivot): points 1-4 are AHU-01's four measurements; NULLs are not stored
INSERT INTO reading_narrow (point_id, ts, value)
SELECT v.point_id, w.ts, v.value
FROM ahu_reading_wide w
CROSS JOIN LATERAL (VALUES (1, w.supply_air_temp), (2, w.return_air_temp),
                           (3, w.zone_co2),        (4, w.fan_speed_pct)) AS v(point_id, value)
WHERE v.value IS NOT NULL;

SELECT 'narrow' AS layout, count(*) AS rows, count(value) AS stored_values FROM reading_narrow
UNION ALL
SELECT 'wide', count(*), count(supply_air_temp) + count(return_air_temp) + count(zone_co2) + count(fan_speed_pct)
FROM ahu_reading_wide;

 layout | rows | stored_values
--------+------+---------------
 narrow |  184 |           184
 wide   |   60 |           184

Decision factors

Work through these in order; in our judgement the first two usually settle it.

Storage is a consequence, not the starting point

Each row carries a header, so a wide row with several values uses fewer bytes per value than several narrow rows. The storage sizing article compares both shapes on 180,000 values, as an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly; not a production benchmark and not representative of your server); use its method on your own data rather than choosing a shape for its byte count. A wide table that is mostly NULL, or that needs a quality column per measurement, gives back much of the difference.

Sparse data: what a missing value costs

In the narrow table a missing reading is simply an absent row. In the wide table it is a NULL. PostgreSQL stores NULLs in a per-row null bitmap with one bit per column, and a NULL column takes no space in the data area. The query below, from the same run, compares the size of single rows as pg_column_size reports them (row header plus data; the 4-byte line pointer and index entries are extra):

SELECT 'narrow, one value' AS row_kind, pg_column_size(r.*) AS bytes
FROM reading_narrow r WHERE point_id = 1 AND ts = '2026-10-27 08:01+00'
UNION ALL
SELECT 'wide, four values', pg_column_size(w.*) FROM ahu_reading_wide w WHERE ts = '2026-10-27 08:00+00'
UNION ALL
SELECT 'wide, fan speed NULL', pg_column_size(w.*) FROM ahu_reading_wide w WHERE ts = '2026-10-27 08:01+00';

       row_kind       | bytes
----------------------+-------
 narrow, one value    |    50
 wide, four values    |    72
 wide, fan speed NULL |    64

Reading the result

In this run (an illustrative measurement on a small PGlite sample, PostgreSQL 18.6 compiled to 32-bit WebAssembly; not a production benchmark and not representative of your server) the row with a NULL fan speed was exactly 8 bytes shorter than the full row (64 against 72 bytes): the NULL saved its 8 bytes of data and the null bitmap added nothing. Why the bitmap cost nothing here is our inference from the documented page layout, not a documented figure: the bitmap holds one bit per data column, and user data starts at an offset that must be a multiple of MAXALIGN, so on a platform with 8-byte alignment the 23-byte header is padded to 24 bytes and a bitmap of up to eight columns fits in that padding byte. Wider tables need more bitmap bytes, which can add 8 bytes of header to rows that contain a NULL; check with pg_column_size on your own table. Within those limits, and on this illustrative run, moderate sparsity added little to row size. The real costs of a sparse wide table are elsewhere: queries and roll-ups must handle NULLs column by column, "latest value" for a slow point means searching back through rows where it is NULL, and a table where most columns are empty most of the time is a sign that the measurements do not belong together. For gap filling and last-value queries on irregular data, see querying irregularly sampled sensor data.

Moving between shapes: pivot for charts and exports

Choosing narrow storage does not force narrow output. A chart, a CSV export or an efficiency calculation that wants one column per measurement can pivot with aggregate FILTER clauses, which feed only the matching rows to each aggregate. The point list must be known when the query is written, so build it from the point register in the application or reporting layer.

SELECT ts::text,
       max(value) FILTER (WHERE point_id = 1) AS supply_air_temp,
       max(value) FILTER (WHERE point_id = 3) AS zone_co2,
       max(value) FILTER (WHERE point_id = 4) AS fan_speed_pct
FROM reading_narrow
WHERE ts >= '2026-10-27 08:00+00' AND ts < '2026-10-27 08:03+00'
GROUP BY ts ORDER BY ts;

           ts           | supply_air_temp | zone_co2 | fan_speed_pct
------------------------+-----------------+----------+---------------
 2026-10-27 08:00:00+00 |              13 |      450 |            80
 2026-10-27 08:01:00+00 |            13.2 |      451 |
 2026-10-27 08:02:00+00 |            13.4 |      452 |

Pivot needs aligned timestamps

The pivot groups by exact timestamp, which works here because the points were sampled on the same minute. Real BMS points drift by a few seconds, so group by a time bucket (for example date_bin('1 minute', ts, ...)) and decide how to pick a value inside the bucket. That is the same alignment problem a wide table hides at write time: the ingestion service has to assign one timestamp to values that were not read at exactly the same instant.

Schema changes: adding a measurement

When a filter differential-pressure sensor is added to the AHU, the narrow design inserts one row into the point register and starts writing readings. The wide design adds a column. In PostgreSQL that is cheap if done carefully: the documentation states that adding a column with no default, or with a non-volatile default, does not rewrite the table, while a volatile default (such as clock_timestamp()), a stored generated column or an identity column rewrites the whole table and its indexes. A table can have at most 1,600 columns, further limited by the row having to fit on one page.

ALTER TABLE ahu_reading_wide ADD COLUMN filter_dp_pa double precision;
SELECT count(*) AS rows, count(filter_dp_pa) AS rows_with_filter_dp FROM ahu_reading_wide;

--  rows | rows_with_filter_dp
-- ------+---------------------
--    60 |                   0

The hidden cost is downstream

The ALTER TABLE itself is quick, but every wide-table change also touches the ingestion mapping, views, roll-up jobs, exports and any warehouse tables fed from it, and needs a lock on the table while it runs. With dozens of equipment types and continuous commissioning, those changes pile up. In our view that is the strongest practical argument for narrow storage of BMS points.

Mixed value types in a narrow table

Not every point is a number. Run status is a boolean, an operating mode is text. Storing everything in double precision works for booleans (0 and 1) but not for modes. One option keeps the narrow shape and adds a typed column per kind, with a CHECK that exactly one is filled. The second insert below fails because it fills two:

CREATE TABLE reading_typed (
    point_id    integer NOT NULL,
    ts          timestamptz NOT NULL,
    num_value   double precision,
    bool_value  boolean,
    text_value  text,
    PRIMARY KEY (point_id, ts),
    CHECK (num_nonnulls(num_value, bool_value, text_value) = 1)
);
INSERT INTO reading_typed (point_id, ts, bool_value) VALUES (5, '2026-10-27 08:00+00', true);
INSERT INTO reading_typed (point_id, ts, num_value, text_value) VALUES (6, '2026-10-27 08:00+00', 1, 'auto');

ERROR:  new row for relation "reading_typed" violates check constraint "reading_typed_check"
DETAIL:  Failing row contains (6, 2026-10-27 08:00:00+00, 1, null, auto).
SQLSTATE 23514

Alternatives

• Separate tables per type (numeric readings, state changes). State points often change rarely and are queried as events ("when did the fan trip?"), so an event table can suit them better than a sampled series.
• A jsonb payload column. Easy to write, but the PostgreSQL documentation still recommends that JSON documents have a somewhat fixed structure, and notes that any update locks the whole row. Use it for the occasional extra attribute, not as the main value column.

How time-series engines map to the same choice

• InfluxDB OSS v2. A line of line protocol carries a measurement, tags and a field set, so writes look wide. The documentation defines a series key as the measurement plus tag set, and a point as the series key, one field value and a timestamp, so storage and queries work per field. Tags are indexed and fields are not; the schema guide recommends keeping data in tag and field values, not encoding it in measurement or key names.
• QuestDB. Tables are column-oriented: each partition is a directory and each column is a separate file inside it. A wide table therefore does not force a query to read every column, and the schema guide suggests covering indexes for wide tables where queries select a subset of columns. Columns can be added later; the designated timestamp and partitioning cannot be changed without recreating the table.
• TimescaleDB. A hypertable is a PostgreSQL table, so everything on this page applies unchanged; its columnstore then compresses each column separately, which is covered in time-series compression and columnar storage.

For a product-level comparison see TimescaleDB vs InfluxDB, and the InfluxDB and QuestDB explainers.

A worked design for a BMS and CMMS site

An illustrative scenario (the counts and intervals are assumptions for the example): a hospital campus has 4,000 BMS points across AHUs, chillers, boilers and fan-coil units, polled every 30 to 300 seconds or reported on change of value, plus 60 electricity meters that return a fixed block of eight registers every 15 minutes. The CMMS holds assets and work orders.

1. Asset and point register: one table of assets, keyed by the CMMS asset ID, and one of points with unit, interval and BMS object reference.
2. BMS points: narrow. Intervals differ, points are added during every fit-out, and most queries are one point over a time range. Run status and modes go in a typed narrow table or an event table.
3. Meters: wide. The eight registers always arrive together with one timestamp, the set never changes for a meter model, and energy reports use several registers at once.
4. Ingestion: the BMS gateway publishes to MQTT or Kafka; an ingestion service maps BMS object names to point_id and writes batches. The streaming layer moves and buffers readings; the database stores and serves them.
5. Analytics: a nightly job pivots selected points into wide, bucketed tables for the warehouse or lakehouse, where feature engineering and portfolio reporting want columns.

Limitations, operations and when not to use each shape

• Do not use a wide table for heterogeneous devices with optional measurements, for points that are added often, or when the column list would run to hundreds. You will end up with a mostly empty table and a schema change for every commissioning job.
• Do not use a narrow table alone when nearly every query reads the same dozen measurements of one device at the same instant and they always arrive together; pivoting on every read is then likely to cost more than storing them together (a judgement; compare plans on your own data).
• Narrow is not a generic entity-attribute-value store. Keep units, types, ranges and the asset link in the point register, and validate readings against it on ingest, or the readings table loses meaning over time. Range checks per point are covered in IoT data quality checks.
• Security: in either shape, give ingestion an insert-only role and give dashboards views rather than base tables. A view per tenant or per site is easier to reason about in the narrow shape, where the filter is on point_id through the register.
• Operations: monitor points that stop reporting (a narrow table shows them as missing rows, a wide table as NULL columns) and keep the point register under change control together with the BMS configuration.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Database Page Layout
• PostgreSQL 18 documentation: ALTER TABLE
• PostgreSQL 18 documentation: Appendix K, PostgreSQL Limits
• PostgreSQL 18 documentation: JSON Types
• PostgreSQL 18 documentation: Value Expressions (aggregate FILTER)
• InfluxDB OSS v2 documentation: InfluxDB schema design
• InfluxDB OSS v2 documentation: Data elements
• QuestDB documentation: Schema design
• QuestDB documentation: Time partitions

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.