CREATE INDEX (Transaction Per Chunk)

Create a hypertable index using a separate transaction for each chunk

CREATE INDEX ... WITH (timescaledb.transaction_per_chunk, ...);

This option extends CREATE INDEX with the ability to use a separate transaction for each it creates an index on, instead of using a single transaction for the entire . 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 , it functions as if a regular CREATE INDEX were called on that , however other s are completely unblocked.

This version of CREATE INDEX can be used as an alternative to CREATE INDEX CONCURRENTLY, which is not currently supported on s.

• Not supported for CREATE UNIQUE INDEX.

• If the operation fails partway through, indexes might not be created on all s. If this occurs, the index on the root table of the is marked as invalid. You can check this by running \d+ on the . The index still works, and is created on new s, but if you want to ensure all s have a copy of the index, drop and recreate it.

  You can also use the following query to find all invalid indexes:

  SELECT * FROM pg_index i WHERE i.indisvalid IS FALSE;
  

Samples

Create an anonymous index:

CREATE INDEX ON conditions(time, device_id)
    WITH (timescaledb.transaction_per_chunk);

Alternatively:

CREATE INDEX ON conditions USING brin(time, location)
    WITH (timescaledb.transaction_per_chunk);