Accelerate queries using indexes

Indexes shrink scan work for point lookups and selective filters. TimescaleDB uses two complementary mechanisms:

• Regular PostgreSQL indexes on the rowstore — B-tree indexes on recent, non-columnstore chunks, configured the same way as on a regular PostgreSQL table.
• Sparse indexes on the columnstore — bloom and minmax summaries per columnstore batch that let the engine skip batches that can’t match a query. See Sparse indexes on the columnstore.

For background on how the columnstore stores compressed data, see Compression methods.

Indexes on the rowstore

You can create an index on any combination of columns. To define an index as UNIQUE or PRIMARY KEY, it must include the partitioning column (this is usually the time column).

When you create a hypertable, set the datatype for the time column as timestamptz and not timestamp. For more information, see PostgreSQL timestamp best practices.

Note

While it is possible to add an index that does not include the time column, doing so results in very slow ingest speeds. For time-series data, indexing on the time column allows one index to be created per chunk.

Choose effective index columns

Consider a simple example with temperatures collected from two locations named office and garage:

An index on (location, time DESC) is organized like this:

garage-0940
garage-0930
garage-0920
garage-0910
office-0930
office-0920
office-0910

An index on (time DESC, location) is organized like this:

0940-garage
0930-garage
0930-office
0920-garage
0920-office
0910-garage
0910-office

A good rule of thumb with indexes is to think in layers. Start by choosing the columns that you typically want to run equality operators on, such as location = garage. Then finish by choosing columns you want to use range operators on, such as time > 0930.

Composite indexes

As a more complex example, imagine you have a number of devices tracking 1,000 different retail stores. You have 100 devices per store, and 5 different types of devices. All of these devices report metrics as float values, and you decide to store all the metrics in the same table, like this:

CREATE TABLE devices (
     time timestamptz,
     device_id int,
     device_type int,
     store_id int,
     value float
);

When you create this table, an index is automatically generated on the time column, making it faster to query your data based on time.

If you want to query your data on something other than time, you can create different indexes. For example, you might want to query data from the last month for just a given device_id. Or you could query all data for a single store_id for the last three months.

You want to keep the index on time so that you can quickly filter for a given time range, and add another index on device_id and store_id. This creates a composite index. A composite index on (store_id, device_id, time) orders by store_id first. Each unique store_id, will then be sorted by device_id in order. And each entry with the same store_id and device_id are then ordered by time. To create this index, use this command:

CREATE INDEX ON devices (store_id, device_id, time DESC);

When you have this composite index on your hypertable, you can run a range of different queries. Here are some examples:

SELECT * FROM devices WHERE store_id = x

This queries the portion of the list with a specific store_id. The index is effective for this query, but could be a bit bloated; an index on just store_id would probably be more efficient.

SELECT * FROM devices WHERE store_id = x AND time > 10

This query is not effective, because it would need to scan multiple sections of the list. This is because the part of the list that contains data for time > 10 for one device would be located in a different section than for a different device. In this case, consider building an index on (store_id, time) instead.

SELECT * FROM devices WHERE device_id = M AND time > 10

The index in the example is useless for this query, because the data for device M is located in a completely different section of the list for each store_id.

SELECT * FROM devices WHERE store_id = M AND device_id = M AND time > 10

This is an effective query for this index. It narrows down the list to a very specific portion.

Create indexes

You can create an index using the CREATE INDEX command. For example, to create an index that sorts first by location, then by time, in descending order:

CREATE INDEX ON conditions (location, time DESC);

Tips

Monitor index creation progress on a hypertable through PostgreSQL‘s pg_stat_progress_create_index view. Since 2.27.0, TimescaleDB reports per-chunk progress so you can track long-running builds.

If you do not want to lock the entire hypertable for the duration of the build, use CREATE INDEX … WITH (timescaledb.transaction_per_chunk), which creates the index in a separate transaction per chunk. Other operations on the hypertable can run concurrently between chunk transactions.

Default indexes

When you create a hypertable with CREATE TABLE, TimescaleDB creates a time index automatically. If you want to create the time index manually, run:

CREATE INDEX ON conditions (time DESC);

You can also create an additional index on another column and time. For example:

CREATE INDEX ON conditions (location, time DESC);

To opt out of default index creation, set tsdb.create_default_indexes to false when you create the hypertable:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  device      TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
) WITH (
  tsdb.hypertable,
  tsdb.create_default_indexes=false
);

When you create a hypertable using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk_interval, defined through after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert chunks in a hypertable to the columnstore.

Best practices

If you have sparse data, with columns that are often NULL, you can add a clause to the index, saying WHERE column IS NOT NULL. This prevents the index from indexing NULL data, which can lead to a more compact and efficient index. For example:

CREATE INDEX ON conditions (time DESC, humidity)
  WHERE humidity IS NOT NULL;

Time-series data uses UNIQUE indexes more rarely than relational data. When you do need one, the index must include the partitioning column. For example, on a hypertable partitioned only on time, a UNIQUE index on (time, location) is valid; a UNIQUE index on (location) alone is not.

Sparse indexes on the columnstore

Once chunks are converted to the columnstore, regular B-tree indexes don’t apply. Instead, TimescaleDB maintains sparse indexes — small per-batch summaries that let the engine skip batches that can’t match the query before reading or moving them back to the rowstore.

Two index types are available:

• bloom(<column>, ...) — probabilistic filter for equality predicates. Lets the engine answer “could this batch contain column = X?” without inspecting the batch’s compressed data. Accepts one or more columns; a composite bloom tests combined equality predicates across the listed columns.
• minmax(<column>) — stores per-batch min and max for the column. Lets the engine answer range and equality predicates by comparing the predicate against the batch’s bounds.

Sparse indexes accelerate SELECT, UPSERT, UPDATE, and DELETE on columnstore data. For DML, when a batch can’t contain matching rows, TimescaleDB skips it without moving it back to the rowstore for checking — this is what delivers the headline speedups (more than 2× for upserts, up to 160× for UPDATE/DELETE with selective bloom matches).

Default sparse indexes

By default (timescaledb.auto_sparse_indexes), TimescaleDB picks suitable sparse indexes automatically based on the hypertable‘s existing B-tree indexes and column data types. Setting tsdb.orderby always creates an implicit minmax index on the orderby column.

Configure sparse indexes

Configure sparse indexes when you CREATE TABLE, and change them at any time with ALTER TABLE. Use tsdb.sparse_index with a comma-separated list:

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id INT NOT NULL,
  region TEXT,
  cpu DOUBLE PRECISION,
  PRIMARY KEY (time, device_id)
) WITH (
  tsdb.hypertable,
  tsdb.orderby = 'time DESC',
  tsdb.segmentby = 'region',
  tsdb.sparse_index = 'bloom(device_id), minmax(cpu)'
);

Restrictions

• Bloom on tsdb.segmentby columns: not allowed, single-column or composite. A bloom whose column list includes any segmentby column is rejected.
• Single-column bloom on a tsdb.orderby column: not allowed. The orderby column gets an implicit minmax automatically; use that for range filters. A composite bloom(orderby_col, other_col) that includes the orderby column is allowed.
• Minmax on tsdb.segmentby columns: not allowed.
• Minmax on a tsdb.orderby column: redundant — an implicit minmax is already added; configuring one explicitly has the same effect.
• One single-column sparse index per column: you cannot have two indexes on the same single column (bloom(region), minmax(region) is rejected). Composite indexes are not affected by this rule (bloom(region, dev), bloom(dev) is valid).

Composite bloom filters

When a query filters on multiple columns that have bloom indexes, TimescaleDB automatically combines the per-column bloom checks at query time — no extra configuration needed.

For known multi-column query patterns, you can also configure an explicit composite bloom by listing the columns inside a single bloom() call. This stores one combined bloom per batch, which can be tighter than separate per-column blooms:

ALTER TABLE metrics SET (
  timescaledb.sparse_index = 'bloom(region, device)'
);

You can mix single-column and composite bloom indexes on the same hypertable. When multiple bloom indexes apply to a query, the planner picks the most-selective one.

Verify with EXPLAIN

EXPLAIN ANALYZE exposes counters for sparse-index pruning. For SELECT, UPDATE, and DELETE:

• Compressed batches filtered — batches skipped by sparse indexes without moving them back to the rowstore.
• Batches filtered after decompression — batches that had to be processed before being filtered out.

For UPSERT, additional bloom-specific counters:

• Batches checked by bloom, Batches pruned by bloom, Batches without bloom, Batches bloom false positives.

Use these to confirm bloom and minmax indexes are doing useful work for your workload. If Compressed batches filtered stays at zero on selective queries, your sparse indexes are not on the right columns.