Postgres indexes in practice: three you will actually use

Postgres has many index types. In day-to-day application work, three of them do almost all of the heavy lifting.

B-tree: the default

Equality and range queries on scalar columns. If you filter or sort by it, a B-tree is probably right.

create index orders_customer_created_idx on orders (customer_id, created_at desc);

Column order matters: put the equality column first and the range or sort column second.

GIN: for documents and search

Full-text search, jsonb containment and arrays. This site's course search uses a generated tsvector column with a GIN index.

Partial indexes: index only what you query

create index jobs_pending_idx on jobs (run_at) where status = 'pending';

Smaller, faster and cheaper to maintain when most rows are never queried by that path.

Want the full picture? See the Postgres for App Developers course.