Narrow tables

One row per entity, time and metric, with the metric’s name in one column and its value in another: the long or tall format.

How it works

A narrow table stores each measurement as its own row: (time, device_id, metric, value). A reading of CPU, memory and disk becomes three rows instead of one. The schema never changes, so a new metric is just a new value in the metric column, and devices or tenants can each send different metrics into the same table. The costs show up elsewhere. There is one row per value, so the table has several times more rows, each repeating the time and device. The single value column has one type, usually a double, so text, booleans and exact integers either get their own tables or are cast. And any question about several metrics at once needs a pivot (a conditional aggregate or a join per metric) to turn rows back into columns. Metric systems are built this way: Prometheus identifies each time series by a metric name plus labels and stores float64 samples, and OpenTelemetry’s timeseries model does the same with a metric name and attributes. Some guides, TigerData’s among them, also call a table per metric narrow: the same idea with the metric name moved into the table name. Stretched to any attribute of any entity, the pattern becomes entity-attribute-value (EAV).

Think of it as

A logbook where every line is one reading: “09:00, pump 3, pressure, 4.2 bar”. A new kind of reading fits on the next line, but comparing pressure and temperature at 09:00 means finding two lines and copying them side by side.

Reach for it when

Metrics are dynamic, user-defined, or differ per device or tenant
Most queries read one metric at a time, such as a chart or an alert per series
Readings arrive one at a time, so a wide row would be mostly NULL
Data comes from Prometheus, OpenTelemetry or other pipelines that already send one value per series

Think twice when

Most reads combine many metrics of the same moment, so nearly every query pivots
Values have mixed types (numbers, text, booleans) that you want stored natively
You need per-metric constraints, such as NOT NULL or a CHECK on a range

A real example

An IoT platform lets each customer define their own sensor fields. Readings land in one readings(time, device_id, metric, value) table: a weather station sends temp, humidity and wind_speed, a freezer sends only temp, and next month’s soil sensor adds moisture with no migration. A device sending three metrics every minute writes about 4,300 rows a day instead of 1,440. The per-metric chart filters on metric = 'temp'; the station dashboard, which shows all three side by side, runs a pivot, and an index on (device_id, metric, time) lets each series be read in order.

The same readings, long and wide#

Four rows of a narrow table: two metrics from one device over two minutes.

time	device_id	metric	value
09:00	7	temp	21.4
09:00	7	humidity	57.1
09:01	7	temp	21.5
09:01	7	humidity	56.8

Pivoted by metric, the same data is two wide rows: (09:00, 7, 21.4, 57.1) and (09:01, 7, 21.5, 56.8). The FILTER query in the snippet above does that pivot in SQL.

Where narrow shows up#

Prometheus

metrics

Every time series is a metric name plus labels, and each sample is a timestamp and a float64. A new metric or label value is a new series, never a schema change.

OpenTelemetry in ClickHouse

metrics

The collector’s ClickHouse exporter keeps one table per metric type (gauge, sum, histogram and so on). Each row holds MetricName, an Attributes map and one Value column, sorted by service, metric name and time.

Data frames

analysis

pandas’ melt() unpivots a wide DataFrame so every measured column ends up in one variable column and one value column; pivot() turns them back into columns. R’s tidyr does the same with pivot_longer() and pivot_wider().

In code · sql

CREATE TABLE readings (
  time      timestamptz NOT NULL,
  device_id int         NOT NULL,
  metric    text        NOT NULL,
  value     double precision
);

-- a new metric needs no migration
INSERT INTO readings VALUES
  ('2026-10-06 09:00', 7, 'temp',     21.4),
  ('2026-10-06 09:00', 7, 'humidity', 57.1),
  ('2026-10-06 09:00', 7, 'moisture', 0.31);

-- reading several metrics together needs a pivot
SELECT time,
       avg(value) FILTER (WHERE metric = 'temp')     AS temp,
       avg(value) FILTER (WHERE metric = 'humidity') AS humidity
FROM readings
WHERE device_id = 7 AND time > now() - interval '1 hour'
GROUP BY time
ORDER BY time;

Often confused with

Wide tables Hybrid (medium) tables Entity-attribute-value (EAV)Time-series databases Wide, hybrid or narrow tables?

Where it came from2005–2024

Statisticians named the shape long before databases did. Survey and panel data is kept either one row per subject (wide) or one row per subject per measurement (long), and analysts convert between the two all the time. Hadley Wickham’s reshape package for R, first released in 2005, called the wide-to-long step “melting”; his 2007 paper framed it as separating identifying variables from measured ones, and pandas uses the same word, melt().

Wickham’s 2014 “Tidy Data” paper added a warning that still applies to narrow tables. Storing variable names in a column, such as an element column holding tmax and tmin, is one of its classic kinds of messy data, fixed by pivoting wider. Long is the right shape when a column holds values of one variable, and an awkward one when it holds the names of many. tidyr 1.0 (2019) renamed the tools pivot_longer() and pivot_wider().

Monitoring made narrow the default for metrics. Prometheus, begun at SoundCloud in 2012, identifies every series by a metric name and labels and stores each sample as a float64, so a new metric is just a new series. OpenTelemetry’s metrics data model describes the same timeseries shape, and its ClickHouse exporter writes one row per data point with the metric name, an attributes map and a single value. Timescale’s 2024 schema guide called narrow the layout that changes least and compresses well, because its columns are uniform.

Why it’s built this way

Put the metric name in the data, not the schema: New metrics, devices and tenants arrive without migrations, which matters when the people defining metrics are customers or developers who will never run ALTER TABLE.
One value type per table: A single value column has one type. Prometheus stores every sample as a float64; other designs add a table per type or a JSONB value column and cast on the way out.
Pivot at read time: Combining metrics costs a GROUP BY with conditional aggregates, or a join per metric. That is cheap for a few metrics over a short range and grows with every metric added to the query.

Timeline

2005
First release of Wickham’s reshape package for R
2007
The reshape paper names the wide-to-long step “melting”
2012
Prometheus starts at SoundCloud: one series per metric name and labels
2014
“Tidy Data” warns against variable names stored in a column
2019
tidyr 1.0 adds pivot_longer() and pivot_wider()
2024
Timescale guide compares narrow, medium and wide layouts

Sources9

Improve this page on GitHub