Primary keys, time columns, and uniqueness for hypertables

Every hypertable has a partitioning column, usually time. That choice drives chunk boundaries, retention, columnstore, and which unique and primary key constraints are valid. Decide these when you model the table, not after data is loaded.

Time column

The partition column defines how rows are grouped into chunks. It is almost always:

• A timestamptz (or timestamp / date) column for event time
• An integer (smallint, int, or bigint) column for Unix epoch seconds, milliseconds, or other monotonic keys

timestamptz vs integer time

• timestamptz: Natural for real-world clocks, time zones, and human-readable queries. Prefer this for most applications.
• Integer epoch: Useful when upstream systems already emit epoch integers. If you use background jobs like retention or columnstore policies, set integer_now_func so those jobs know what “current” means.

Pick a type you can keep stable — changing the partition column later requires a migration.

Primary keys and unique constraints

TimescaleDB requires that every unique constraint or primary key includes the partitioning column. Additionally, if your hypertable uses multiple partitioning dimensions (for example, space partitioning on device_id), all partitioning columns must appear in the constraint.

This rule exists because uniqueness is enforced per chunk. Including all partitioning columns ensures that PostgreSQL can check the constraint within a single chunk without scanning the entire hypertable.

Valid examples

A sensor table where each device writes at most one row per timestamp:

CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  device_id   INT         NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION,
  PRIMARY KEY (device_id, time)
) WITH (timescaledb.hypertable);

An order events table with a surrogate ID that still includes the partition column:

CREATE TABLE order_events (
  time      TIMESTAMPTZ NOT NULL,
  order_id  BIGINT      NOT NULL,
  status    TEXT,
  UNIQUE (order_id, time)
) WITH (timescaledb.hypertable);

Invalid example

A surrogate key that does not include the partition column fails:

-- This fails because 'id' alone cannot guarantee uniqueness per chunk
CREATE TABLE bad_example (
  id   SERIAL PRIMARY KEY,
  time TIMESTAMPTZ NOT NULL,
  value DOUBLE PRECISION
) WITH (timescaledb.hypertable);

Unique constraints and columnstore

When you insert into a columnstore chunk that has a unique constraint or primary key, TimescaleDB decompresses data in memory to check for constraint violations. Hypertables without unique constraints skip this check, so inserts into columnstore chunks are faster. If your workload is append-only with no duplicates, consider whether you truly need a unique constraint.

Indexes

Unique constraints create indexes. Those indexes affect ingest cost and storage. See Hypertable indexes and Hypertables and unique indexes for tuning and edge cases.

Modeling checklist

• Partition column: One clear time or integer column used consistently in queries and policies.
• Uniqueness: Express real-world identity (sensor + time, order_id + time) in keys that include all partitioning columns.
• Nulls: TimescaleDB enforces NOT NULL on the partition column automatically. Make sure your ingest pipeline never sends null values for this column.
• Future timestamps: Far-future time values create unexpected chunks and affect retention behavior. Validate timestamps at ingest.

Learn more

• Wide, narrow, and medium tables: Choose a table layout for your time-series data.
• Create and configure a hypertable: CREATE TABLE options and configuration.
• Partition a hypertable: Time, integer, and space partitioning.
• Enforce constraints with unique indexes: Unique index rules and limitations.
• CREATE TABLE reference: Full API reference.