Indexing data

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:

You can run this command before or after you convert a regular PostgreSQL table to a hypertable.

When you create a hypertable with the create_hypertable command, a time index is created on your data. If you want to manually create a time index, you can use this command:

When you create a hypertable with the command, and you specify an optional space partition in addition to time, such as a location column, an additional index is created on the optional column and time. For example:

Best practices for indexing

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

To define an index as a UNIQUE or PRIMARY KEY index, the index must include the time column and the partitioning column, if you are using one. For example, a unique index must include at least the (time, location) columns, in addition to any other columns you want to use. Generally, time-series data uses UNIQUE indexes more rarely than relational data.