Wide and narrow format

Overview

Time-series data can be stored in two different table formats: wide and narrow. Understanding the difference between these formats is crucial for writing efficient queries and choosing the right storage strategy for your data.

Wide table format

Structure

A wide table stores each field as a separate column, with tags also as columns. Each row represents a single timestamp with all measurements taken at that time.

Example:

time	Tag 1	Tag 2	Field 1	Field 2	Field 3
2020-01-01 00:00:00	TagValue1	TagValue2		15
2020-01-01 00:00:01	TagValue1	TagValue2	1
2020-01-01 00:00:02	TagValue1	TagValue2		20
2020-01-01 00:00:03	TagValue1	TagValue2			3.14

Characteristics

• Column per field: Each field has its own column
• Fixed schema: Adding new fields requires schema changes (adding columns)
• Sparse data: If a field doesn't have a value at a timestamp, the cell is NULL/empty
• Easy to query: Simple SELECT statements for specific fields

Databases using wide format

• InfluxDB (all measurements)
• TimescaleDB Wide (configured for wide format)

When to use

• You have a fixed set of fields that don't change often
• You need fast access to specific fields
• You want simple, readable queries
• You're working with structured, predictable data

Narrow table format

Structure

A narrow table uses a fixed schema with a name column to identify the field and separate value columns for different data types. Each row represents a single field measurement at a specific time.

Example:

time	gateway_id	gateway	source	name	identifier	value	value_str	value_json	tags
2020-01-01 00:00:00				Field 2		15			{ "Tag 1": "TagValue1", "Tag 2": "TagValue2"}
2020-01-01 00:00:01				Field 1		1			{ "Tag 1": "TagValue1", "Tag 2": "TagValue2"}
2020-01-01 00:00:02				Field 2		20			{ "Tag 1": "TagValue1", "Tag 2": "TagValue2"}
2020-01-01 00:00:03				Field 3		3.14			{ "Tag 1": "TagValue1", "Tag 2": "TagValue2"}

Characteristics

• Row per measurement: Each field value creates a new row
• Flexible schema: Adding new fields doesn't require schema changes
• Dense data: No NULL values for missing fields (they simply don't have rows)
• More complex queries: Requires filtering by name column or pivoting

Key columns

• name: Identifies which field this row represents
• value: Numeric values (float/integer)
• value_str: String/text values
• value_json: JSON object values
• tags: JSON object containing all tags for this measurement

Databases using narrow format

• TimescaleDB Narrow (configured for narrow format)

When to use

• You have dynamic fields that change frequently
• You need to store fields with different data types efficiently
• You want flexibility to add new fields without schema migrations
• You're working with semi-structured or evolving data models

Comparison

Aspect	Wide Table	Narrow Table
Schema changes	Requires column additions	No changes needed for new fields
Query complexity	Simple for single fields	More complex, requires filtering by name
Storage	Sparse (many NULLs if data varies)	Dense (no NULLs, only existing data)
Readability	Very readable	Requires pivoting to read naturally
Performance	Fast for specific field queries	Fast for dynamic field queries
Use case	Fixed, predictable data structures	Dynamic, evolving data structures
Field types	One type per column	Multiple value columns for different types

Converting narrow to wide format

When working with narrow tables, you may want to convert them to wide format for easier querying or visualization. This is especially useful when you need to:

• Compare multiple fields side-by-side
• Perform calculations across different fields
• Create visualizations that expect wide format data

Basic pivot query

To transform a narrow table into a wide table format, you can use SQL aggregation with CASE statements to pivot the data:

SELECT
  time,
  MAX(CASE WHEN name = 'Field 1' THEN value END) AS "Field 1",
  MAX(CASE WHEN name = 'Field 2' THEN value END) AS "Field 2",
  MAX(CASE WHEN name = 'Field 3' THEN value END) AS "Field 3",
  tags->>'Tag 1' AS "Tag 1",
  tags->>'Tag 2' AS "Tag 2"
FROM "DatabaseName"."Measurement"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
GROUP BY time, tags
ORDER BY time

How this query works:

• Pivots fields: MAX(CASE WHEN name = 'Field 1' THEN value END) creates a column for each field
• Extracts tags: tags->>'Tag 1' extracts tag values from the JSON tags column using the ->> operator
• Groups data: GROUP BY time, tags combines all fields from the same timestamp
• Filters time: Filters data to the last 15 minutes using standard SQL interval syntax

Using a CTE (Common Table Expression)

If you need to perform additional operations on the pivoted data, you can use a Common Table Expression (CTE). This allows you to reference the wide table result in subsequent queries without repeating the pivot logic.

Simple CTE example:

WITH wide_data AS (
  SELECT
    time,
    MAX(CASE WHEN name = 'Field 1' THEN value END) AS "Field 1",
    MAX(CASE WHEN name = 'Field 2' THEN value END) AS "Field 2",
    MAX(CASE WHEN name = 'Field 3' THEN value END) AS "Field 3",
    tags->>'Tag 1' AS "Tag 1",
    tags->>'Tag 2' AS "Tag 2"
  FROM "DatabaseName"."Measurement"
  WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
  GROUP BY time, tags
)
SELECT *
FROM wide_data
ORDER BY time

Advanced CTE with calculations:

You can then add additional filtering, calculations, or aggregations after the CTE:

WITH wide_data AS (
  SELECT
    time,
    MAX(CASE WHEN name = 'Field 1' THEN value END) AS "Field 1",
    MAX(CASE WHEN name = 'Field 2' THEN value END) AS "Field 2",
    MAX(CASE WHEN name = 'Field 3' THEN value END) AS "Field 3",
    tags->>'Tag 1' AS "Tag 1",
    tags->>'Tag 2' AS "Tag 2"
  FROM "DatabaseName"."Measurement"
  WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
  GROUP BY time, tags
)
SELECT
  time,
  "Field 1",
  "Field 2",
  "Field 3",
  ("Field 1" + "Field 2") AS "Sum of Fields"
FROM wide_data
WHERE "Tag 1" = 'TagValue1'
ORDER BY time

Benefits of using a CTE:

• Readability: Makes complex queries more readable by breaking them into logical steps
• Reusability: Allows you to reference the same pivoted data multiple times without repeating the pivot logic
• Maintainability: Easier to add additional filters or calculations on the wide table format
• Performance: The database can optimize the CTE execution plan