/schema

Index Strategy Guide

Purpose: Use this file when selecting index type, composite order, or monitoring strategy.

Contents:

1. Index type selection
2. Composite index rules
3. Partial and covering indexes
4. Monitoring queries

Index Type Selection

Composite Index Rules

• Order columns as Equality -> Range -> Sort.
• Respect the leftmost-prefix rule.
• Prefer the actual predicate order over abstract “important columns”.

Example:

WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY name

Preferred index:

(status, created_at, name)

Partial And Covering Indexes

CREATE INDEX idx_active_users
  ON users(email)
  WHERE deleted_at IS NULL;

CREATE INDEX idx_covering_users
  ON users(status)
  INCLUDE (name, email);

Monitoring Queries

-- Unused indexes (PostgreSQL)
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE NOT indisunique AND idx_scan < 50;

-- Missing-index hints
SELECT relname, seq_scan - idx_scan AS too_much_seq
FROM pg_stat_user_tables
WHERE seq_scan - idx_scan > 100;

pgvector Index Selection

Use this table when adding a vector similarity index to a table with vector columns.

-- IVFFlat: choose lists = sqrt(row_count), e.g. 100 for 10k rows
CREATE INDEX idx_embeddings_ivfflat
  ON document_embeddings
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- HNSW: better recall, higher memory cost
CREATE INDEX idx_embeddings_hnsw
  ON document_embeddings
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Operator class selection:

• vector_cosine_ops — normalized embeddings (OpenAI, Cohere, most transformers)
• vector_l2_ops — unnormalized embeddings, Euclidean distance
• vector_ip_ops — inner product (dot product similarity)