Skip to content

06-indexing-strategy.md

Latest commit

 

History

History
200 lines (138 loc) · 8.36 KB

06-indexing-strategy.md

File metadata and controls

200 lines (138 loc) · 8.36 KB
Raw

6. Indexing Strategy

What an Index Is and What It Costs

An index is a separate data structure that PostgreSQL maintains alongside a table to speed up queries. Without an index, finding all orders for a user requires reading every row in the orders table (a sequential scan). With an index on user_id, PostgreSQL can jump directly to the relevant rows.

The cost is real: every insert, update, or delete on the table requires updating all associated indexes. An over-indexed table has slow writes. An under-indexed table has slow reads. The goal is to index what you actually query, not to preemptively index everything.

PostgreSQL's default index type is a B-tree (Balanced Tree), which is appropriate for most use cases: equality queries (WHERE id = ?), range queries (WHERE created_at > ?), sorting (ORDER BY), and prefix matching (WHERE name LIKE 'foo%').

What to Index: The Rules

Always Index

Primary keys — PostgreSQL creates a unique index on the primary key automatically.

Foreign keys — PostgreSQL does not create indexes on foreign key columns automatically, unlike MySQL. This is a common source of slow queries in PostgreSQL. If orders.user_id references users.id, add an index on orders.user_id.

model Order {
  userId String @map("user_id")

  user User @relation(fields: [userId], references: [id])

  @@index([userId])  // Required — PostgreSQL does not add this automatically
}

Failing to index foreign keys means that queries like "find all orders for this user" perform a sequential scan of the entire orders table. On a small dataset this is fast. On a table with a million rows, it is not.

Columns used in WHERE clauses on large tables — If a query filters by a column frequently and the table has more than a few thousand rows, that column needs an index.

Columns used in JOIN conditions — If you join orders to users on orders.user_id = users.id, index orders.user_id.

Consider Indexing

Columns used in ORDER BY — If you frequently sort by a column, an index can eliminate a sort operation. This is especially valuable when combined with LIMIT (pagination), where PostgreSQL can read the first N rows from the index without scanning the full table.

Columns used in GROUP BY — Index group-by columns when you aggregate over large datasets.

Columns in unique constraints — Unique constraints already create an index. Do not add a separate index on a column with a unique constraint.

Do Not Index

Low-cardinality columns — A column with only a few distinct values (a boolean is_active, an OrderStatus enum with 5 values) is often not worth indexing. When a query matches a large fraction of rows, PostgreSQL's query planner will often choose a sequential scan over an index scan because the overhead of following index pointers to each row exceeds the cost of reading the table sequentially. Rule of thumb: if a query matches more than roughly 10-15% of rows, the index may not be used.

Columns that are almost never queried — An index that is never used still costs write performance.

Every column as a default — Index what you measure, not what you guess.

Composite Indexes

A composite index covers multiple columns. It is used for queries that filter on those columns together and can also be used for queries that filter on a prefix of the indexed columns.

model Order {
  userId String @map("user_id")
  status OrderStatus

  @@index([userId, status])
}

This index supports:

  • WHERE user_id = ? — the prefix is used
  • WHERE user_id = ? AND status = ? — both columns used
  • ORDER BY user_id, status — used for sorting

This index does NOT support:

  • WHERE status = ? alone — the index cannot be used because user_id (the leading column) is not in the filter

The order of columns in a composite index matters. Put the most selective column first, or put the column that appears alone in queries first.

Partial Indexes

A partial index indexes only rows that match a condition. They are smaller, faster to scan, and can handle uniqueness constraints that only apply under certain conditions.

-- Only one active subscription per user
CREATE UNIQUE INDEX users_one_active_subscription
ON subscriptions (user_id)
WHERE status = 'active';

-- Index only non-deleted orders (if soft delete is used)
CREATE INDEX orders_user_active
ON orders (user_id, created_at DESC)
WHERE deleted_at IS NULL;

-- Index only unverified users (for a background job that processes them)
CREATE INDEX users_unverified
ON users (created_at)
WHERE is_verified = false;

Partial indexes are a powerful tool in PostgreSQL that are not available in all databases. Use them when most queries include a consistent filter (like deleted_at IS NULL) — they avoid indexing the rows you never query.

In Prisma, partial indexes require raw SQL in a migration:

-- prisma/migrations/TIMESTAMP_add_partial_index/migration.sql
CREATE UNIQUE INDEX users_one_active_subscription
ON subscriptions (user_id)
WHERE status = 'active';

GIN Indexes for Full-Text Search

Full-text search in PostgreSQL uses a different index type: GIN (Generalized Inverted Index). A GIN index on a tsvector column allows efficient text search.

-- Add a generated tsvector column and index it
ALTER TABLE products
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
) STORED;

CREATE INDEX products_search ON products USING GIN (search_vector);

Querying:

SELECT id, name
FROM products
WHERE search_vector @@ plainto_tsquery('english', 'wireless headphones')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'wireless headphones')) DESC;

This approach provides solid search functionality without a dedicated search engine. It handles stemming, stop words, and ranking. For most applications that need search, this is sufficient before reaching for Elasticsearch or Typesense.

Checking Index Usage with EXPLAIN ANALYZE

The only reliable way to know whether an index is being used is to look at the query plan. PostgreSQL's EXPLAIN ANALYZE executes a query and shows the plan with actual timing:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000'
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Key things to look for in the output:

  • Index Scan using orders_user_id_status_idx — the index is being used. Good.
  • Seq Scan on orders — sequential scan. May mean the index is missing, or the query planner decided the index was not worth using.
  • cost=0.00..8.29 rows=20 — estimated cost. The actual cost appears after actual time=....
  • Filter rows being removed by the scan — if many rows are scanned and most filtered out, an index is likely needed.
-- Find the slowest queries in your database (requires pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Finding Unused Indexes

Indexes that are never used should be dropped. They waste storage and slow down writes.

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,      -- Number of times the index was used
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Run this on a production database after a few weeks of traffic. Indexes with zero scans are candidates for removal, but verify by checking if the query patterns they support are exercised regularly.

Sources