Wide, narrow, and medium tables

When you design a PostgreSQL schema for time-series data, choosing between narrow, wide, and medium table layouts affects how you query, how easily you add new metrics, and how well your data compresses into columnstore. This page explains each layout with examples and helps you pick the right one.

Narrow table layout

A narrow table has few value columns but may include multiple metadata columns. Each metric gets its own table and data type.

-- One table per metric
CREATE TABLE cpu_usage (
  time        TIMESTAMPTZ NOT NULL,
  host_id     INT,
  usage       DOUBLE PRECISION
);

CREATE TABLE memory_usage (
  time        TIMESTAMPTZ NOT NULL,
  host_id     INT,
  bytes_used  BIGINT
);

Advantages:

• Adding a new metric means creating a new table — no schema changes to existing tables.
• Each metric has its own precise data type.
• Columns are uniform, so compression ratios are typically high.

Disadvantages:

• Many tables become hard to manage operationally.
• Querying several metrics at once requires joins or unions and more I/O.

Wide table layout

A wide table has many columns, often one per metric. Related values live in a single row, so common queries avoid joins.

CREATE TABLE host_metrics (
  time          TIMESTAMPTZ NOT NULL,
  host_id       INT,
  cpu_usage     DOUBLE PRECISION,
  mem_bytes     BIGINT,
  disk_io_read  BIGINT,
  disk_io_write BIGINT,
  net_rx_bytes  BIGINT,
  net_tx_bytes  BIGINT
);

Advantages:

• Simpler queries — all metrics for a host are in one row.
• Efficient column access, especially with columnar compression.

Disadvantages:

• New metrics require ALTER TABLE ADD COLUMN, which can be costly on large tables.
• Dropped columns do not immediately reclaim disk space.

Medium table layout

A medium layout uses one column per data type rather than one column per metric. A separate metadata column identifies which metric each row represents.

CREATE TABLE metrics (
  time          TIMESTAMPTZ NOT NULL,
  host_id       INT,
  metric_name   TEXT,
  value_double  DOUBLE PRECISION,
  value_bigint  BIGINT
);

Advantages:

• New metrics don’t require schema changes — just insert rows with a new metric_name.
• Works well for multi-tenancy where each tenant may have different metrics.

Disadvantages:

• You need a mapping from logical metric names to typed columns.
• Querying metrics of different types requires filtering and casting.

Comparison

	Narrow	Wide	Medium
Best for	Few, well-defined metrics with distinct types	Many related metrics queried together	Dynamic or unknown metrics with mixed types
Adding metrics	Create a new table	ALTER TABLE	Insert rows with a new name
Query complexity	Joins/unions across tables	Simple single-table queries	Filtering on metric_name
Schema changes	Rare per table	Frequent as metrics grow	Rare
Compression	Excellent (uniform columns)	Good (NULL-heavy columns compress well)	Good (uniform typed columns)

Which layout should you choose?

• You know all metrics up front and they rarely change → wide. You get simple queries and good compression.
• You have a small, fixed set of distinct metric types → narrow. Each table stays focused and compresses well.
• Metrics are dynamic, user-defined, or vary by tenant → medium. You avoid constant schema changes.

In practice, many systems combine layouts. For example, use a wide table for core host metrics and a medium table for custom application-level metrics.

Learn more

• Primary keys, time columns, and uniqueness: Partition column and constraint rules for hypertables.
• Schema optimization: Indexes, constraints, and other schema tools.
• Create and configure a hypertable: CREATE TABLE options including segmentby and orderby.
• Accelerate queries using indexes: How-to guide for creating and tuning indexes.
• CREATE TABLE reference: Full API reference.