Indexes and constraints
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.
The following table lists different types of indexes and their support across the two APIs.
|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|