Indexes

An index is a separate, sorted structure that lets Postgres jump straight to the rows a query needs instead of reading the whole table. On a small table the difference is invisible. On a table with a few hundred thousand rows, the right index turns a query that takes seconds into one that takes a millisecond, and it lowers CPU use for every other project on your instance too.

Indexes aren't free. Each one takes disk space (which counts toward your plan's shared database size) and adds a little work to every insert and update. Add them for queries you actually run.

Index the columns you filter, join and sort on

Start with these, in roughly this order:

1. Columns used in Row Level Security policies, such as user_id or team_id. Every request through the Data API runs the policy, so an unindexed policy column slows down everything.
2. Foreign key columns. Postgres indexes the referenced primary key but not the referencing column.
3. Columns in common where, order by and join clauses.

For a bookings app:

create index bookings_user_id_idx on public.bookings (user_id);
create index bookings_venue_id_idx on public.bookings (venue_id);

Index types

GIN is covered in Full-text search and JSON and JSONB columns, GiST in Geo data with PostGIS.

Composite indexes

When a query filters on several columns, one index on all of them usually beats several single-column indexes. Order matters: put equality columns first and the range or sort column last.

-- Supports: where venue_id = $1 and starts_at >= $2 order by starts_at
create index bookings_venue_starts_idx on public.bookings (venue_id, starts_at);

This index also serves queries that filter on venue_id alone, but not queries that filter only on starts_at.

Partial indexes

If most queries look at a small slice of the table, index only that slice. It's smaller and faster to maintain.

-- Only upcoming, unconfirmed bookings are ever polled by the reminder job
create index bookings_pending_idx
  on public.bookings (starts_at)
  where status = 'pending';

The query's where clause must include status = 'pending' for Postgres to use it.

Expression indexes

Index the result of an expression when you filter on it. A common one is case-insensitive email lookup:

create index customers_email_lower_idx on public.customers (lower(email));

select * from public.customers where lower(email) = lower('[email protected]');

Unique indexes

A unique index enforces a rule and speeds up lookups at the same time. Partial unique indexes express rules like "one active subscription per user":

create unique index one_active_sub_per_user
  on public.subscriptions (user_id)
  where status = 'active';

Building indexes on a live table

A plain create index blocks writes to the table until it finishes. On a busy table, build it with concurrently instead. It takes longer but lets inserts and updates carry on:

create index concurrently bookings_created_at_idx on public.bookings (created_at);

concurrently can't run inside a transaction. That affects where you run it on SupaHost:

• Studio SQL editor: run the statement on its own, not together with other statements in the same run.
• psql over the SSH tunnel (Business and Enterprise): works as normal. See SSH access and direct Postgres.
• MCP apply_migration: every migration runs as one transaction, so concurrently fails there. Use a plain create index in migrations for small tables, and build indexes on big tables separately.

If a concurrent build fails, it leaves an invalid index behind. Find and drop it, then try again:

select indexrelid::regclass as index_name
from pg_index
where not indisvalid;

A long index build can also hit a statement timeout. See Connections and timeouts for raising it for one session.

Check that an index is used

Use explain to see the plan before and after:

explain analyze
select * from public.bookings where venue_id = 42 and starts_at >= now();

Look for Index Scan or Bitmap Index Scan on your index instead of Seq Scan. On a tiny table Postgres may still pick a sequential scan because it's cheaper; that's fine. Query performance and EXPLAIN explains how to read plans.

Find unused and oversized indexes

Postgres counts how often each index is scanned. Indexes with zero scans after a few weeks of normal traffic are candidates for removal (keep unique indexes and primary keys, which enforce rules even if never scanned):

select
  s.relname as table_name,
  s.indexrelname as index_name,
  s.idx_scan as scans,
  pg_size_pretty(pg_relation_size(s.indexrelid)) as size
from pg_stat_user_indexes s
join pg_index i on i.indexrelid = s.indexrelid
where not i.indisunique
order by s.idx_scan asc, pg_relation_size(s.indexrelid) desc;

These counters are per database, so on SupaHost they only describe the project you're connected to. Drop an index with drop index concurrently index_name; to avoid blocking writes.

Related

• Query performance and EXPLAIN
• Speeding up slow queries with indexes
• RLS performance best practices
• index row size exceeds btree maximum