CREATE INDEX (Transaction Per Chunk)
CREATE INDEX ... WITH (timescaledb.transaction_per_chunk, ...);
This option extends CREATE INDEX
with the ability to use a separate transaction for each chunk it creates an index on, instead of using a single transaction for the entire hypertable. This allows INSERT
s, and other operations to to be performed concurrently during most of the duration of the CREATE INDEX
command. While the index is being created on an individual chunk, it functions as if a regular CREATE INDEX
were called on that chunk, however other chunks are completely un-blocked.
TIP:This version of
CREATE INDEX
can be used as an alternative toCREATE INDEX CONCURRENTLY
, which is not currently supported on hypertables.WARNING:If the operation fails partway through, indexes may not be created on all hypertable chunks. If this occurs, the index on the root table of the hypertable will be marked as invalid (this can be seen by running
\d+
on the hypertable). The index will still work, and will be created on new chunks, but if you wish to ensure all chunks have a copy of the index, drop and recreate it.
Sample Usage
Anonymous index
CREATE INDEX ON conditions(time, device_id) WITH (timescaledb.transaction_per_chunk);
Other index methods
CREATE INDEX ON conditions(time, location) USING brin
WITH (timescaledb.transaction_per_chunk);