web dev//SQL//index

An index is a data structure that makes queries faster, like the index at the back of a book. Without it, the database scans every row (a full table scan); with it, the database jumps straight to the matching rows.

An index is a data structure that makes queries faster, like the index at the back of a book. Without it, the database scans every row (a full table scan); with it, the database jumps straight to the matching rows.

CREATE INDEX idx_name ON table (column) creates a standard B-tree index, for equality and range queries.

CREATE INDEX ... USING gin (column gin_trgm_ops) creates a GIN index, for fuzzy matching with pg_trgm.

Indexes speed up reads but slow down writes, because the index must be updated on every insert or update. At small scale it does not matter; at large scale, indexing is selective, limited to the columns queried frequently.

In Supabase, indexes are created in the SQL Editor via DDL.