# Indexes

> Which Postgres index to add and when on SupaHost: B-tree, composite, partial, GIN, BRIN and expression indexes, building them without locking, and finding unused ones.

Source: https://supahost.app/docs/database/indexes · Updated 2026-11-11

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:

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

## Index types

| Type | Good for | Example |
|---|---|---|
| B-tree (default) | Equality, ranges, sorting, `like 'abc%'` | `create index on bookings (starts_at);` |
| GIN | Arrays, `jsonb` containment, full-text search | `create index on events using gin (payload jsonb_path_ops);` |
| GiST | Geometry, ranges, overlap and nearest-neighbour | `create index on venues using gist (location);` |
| BRIN | Huge append-only tables where values follow insert order (timestamps) | `create index on audit_log using brin (created_at);` |
| HNSW / IVFFlat | Vector similarity (pgvector) | See [Vector indexes](/docs/ai/vector-indexes) |

GIN is covered in [Full-text search](/docs/database/full-text-search) and [JSON and JSONB columns](/docs/database/json), GiST in [Geo data with PostGIS](/docs/database/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.

```sql
-- 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.

```sql
-- 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:

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

select * from public.customers where lower(email) = lower('Sam@Example.com');
```

## 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":

```sql
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:

```sql
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](/docs/platform/ssh).
- **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:

```sql
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](/docs/database/connections-and-timeouts) for raising it for one session.

## Check that an index is used

Use `explain` to see the plan before and after:

```sql
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](/docs/database/query-performance) 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):

```sql
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](/docs/database/query-performance)
- [Speeding up slow queries with indexes](/kb/improve-query-performance)
- [RLS performance best practices](/kb/rls-performance)
- [index row size exceeds btree maximum](/kb/index-row-size-exceeds)