Indexes and constraints in YugabyteDB YSQL

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.

The following table lists different types of indexes and their support in YSQL.

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

Learn more

{{<index/block>}}

{{<index/item title="Primary keys" body="Explore the use of primary keys in YSQL." href="primary-key-ysql/" icon="fa-thin fa-bars">}}

{{<index/item title="Foreign keys" body="Explore the use of foreign keys associated with primary keys in YSQL." href="foreign-key-ysql/" icon="fa-thin fa-list-ul">}}

{{<index/item title="Secondary and multi-column indexes" body="Explore indexes to optimize your database performance with examples." href="secondary-indexes-ysql/" icon="fa-thin fa-list-ol">}}

{{<index/item title="Unique indexes" body="Explore unique indexes in YSQL with examples." href="unique-index-ysql/" icon="fa-thin fa-bars-staggered">}}

{{<index/item title="Partial indexes" body="Explore partial indexes in YSQL with examples." href="partial-index-ysql/" icon="fa-thin fa-list-check">}}

{{<index/item title="Expression indexes" body="Explore Expression indexes in YSQL with examples." href="expression-index-ysql/" icon="fa-thin fa-percent">}}

{{<index/item title="Covering indexes" body="Explore Covering indexes in YSQL with examples." href="covering-index-ysql/" icon="fa-thin fa-table-list">}}

{{<index/item title="GIN indexes" body="Use GIN indexes in YSQL to run efficient queries." href="gin/" icon="fa-thin fa-folder-tree">}}

{{<index/item title="Other constraints" body="Explore CHECK, UNIQUE, and NOT NULL constraints to optimize your database performance." href="other-constraints/" icon="/images/section_icons/develop/learn.png">}}

{{<index/item title="Index backfill" body="Understand how you can create indexes without affecting ongoing queries." href="index-backfill/" icon="/images/section_icons/develop/learn.png">}}

{{</index/block>}}