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 INSERTs, and other operations 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 to CREATE 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 is marked as invalid (this can be seen by running \d+ on the hypertable). The index still works, and is 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);Found an issue on this page?
Report an issue!