Indexing Deliberately

Postgres has more index types than SQLite, and B-tree is still the answer almost always. It serves equality, ranges and ordering.

The others are for specific jobs: GIN for full text search and for looking inside JSONB or arrays, GiST for geometric and range types, BRIN for very large tables whose rows are naturally ordered, such as append-only logs by timestamp.

Two Postgres capabilities worth knowing.

A partial index covers only some rows: WHERE deleted_at IS NULL. Smaller, faster, and exactly right when the query always filters that way.

A covering index includes extra columns so the query can be answered from the index alone without touching the table.

Creating an index locks the table unless you use CREATE INDEX CONCURRENTLY, which does not. On a production table with traffic, that distinction is the difference between a routine change and an outage.