Indexes and constraints in YugabyteDB

Indexes and constraints

Explore indexes and constraints in YSQL and YCQL

As with tables, indexes in YugabyteDB are stored in a distributed manner - that is, they are split into tablets and replicated. Updates to indexes are transactional, which means that row updates and the corresponding index updates occur as a single transaction. Similar to tables, they are stored in LSM format, as opposed to the B-tree structure used by indexes in PostgreSQL.

YugabyteDB supports most of the PostgreSQL index semantics in the YSQL API, and the YCQL API supports most of the Cassandra index semantics while incorporating other improvements.

The following table lists different types of indexes and their support across the two APIs.

Type YSQL YCQL Description
Primary key Yes Yes Unique key that identifies the row
Foreign key Yes No Link to a column in another table
Secondary index Yes Yes Index on columns other than the primary key
Unique index Yes Yes Set one or many columns to be unique
Multi-column index Yes Yes Index on multiple columns for faster scan with lesser rows
Partial index Yes Yes Indexes that apply to only some rows of the table
Covering index Yes Yes Store other columns in the index for faster retrieval
Expression index Yes No Index based on a functional operation on columns
GIN index Partial No Generalized inverted index for fast text search
GIST Index No No For spatial search. Tracked - #1337

Learn more