PostgreSQL

How to Create an Index in PostgreSQL

Having the right indexes are critical to making your queries performant, especially when you have large amounts of data. Here’s an example of how to create an index in PostgreSQL:

create index concurrently "index_created_at_on_users"
on users using btree (created_at);

If you want to index multiple columns:

create index concurrently "index_user_id_and_time_on_events"
on events using btree (user_id, time);

Unique indexes to prevent duplicate data:

create unique index concurrently "index_stripe_event_id_on_stripe_events"
on stripe_events using btree(stripe_event_id);

Partial indexes to only index rows where a certain condition is met:

create index concurrently "index_active_users"
on users using btree(created_at) where active is true;

You can also have a unique partial index. For example, imagine if each user can only have one active credit card:

-- This will prevent any user from having more than one active credit card
create unique index concurrently "index_active_credit_cards"
on credit_cards using btree(user_id) where active is true;