Hypertable indexes

Indexes on hypertables work the same as on regular PostgreSQL tables, with a few TimescaleDB-specific behaviors.

Default indexes

When you create a hypertable, TimescaleDB automatically creates a B-tree index on the partition column (time descending). If the hypertable has a space dimension, a second composite index on (space column, time descending) is also created. You can disable this with timescaledb.create_default_indexes = false if you plan to add custom indexes after loading data.

Indexes speed up SELECT queries but slow down INSERT operations and increase storage. Drop any indexes you don’t actively use.

Supported index types

hypertables support all PostgreSQL index types:

• B-tree — the default. Best for equality and range filters on time, device IDs, and other scalar columns.
• Hash — useful for equality-only lookups.
• GIN — for JSONB, arrays, and full-text search columns.
• GiST — for geometric, range, and full-text search data.
• BRIN — block range indexes, useful for very large append-only tables.

Unique indexes and primary keys

Any UNIQUE or PRIMARY KEY index must include the partition column. This is a TimescaleDB requirement because uniqueness is enforced per chunk. See Primary keys, time columns, and uniqueness for details.

Per-chunk index builds

Use the timescaledb.transaction_per_chunk option when creating indexes to build per chunk in separate transactions, reducing lock contention:

CREATE INDEX idx_device_time ON sensor_data (device_id, time DESC)
  WITH (timescaledb.transaction_per_chunk = true);

Columnstore and indexes

PostgreSQL indexes (including B-tree) are kept on chunks converted to columnstore and continue to work for queries. TimescaleDB also uses batch-level metadata (min/max values) to skip irrelevant batches during scans. For best columnstore query performance, tune segmenting and ordering in addition to indexes. See Improve query and upsert performance for details.

Learn more

• Accelerate queries using indexes: How-to guide for creating and tuning indexes.
• Improve query and upsert performance: Segmenting and ordering columnstore data.
• Enforce constraints with unique indexes: Unique index rules and limitations.
• Primary keys, time columns, and uniqueness: Partition column requirements for unique constraints.
• CREATE INDEX reference: Full API reference including transaction_per_chunk.