Indexing

📇 Secondary Indexes & Performance

Creating Indexes

No dedicated REST endpoint

There is currently no dedicated REST endpoint for creating indexes. A SchemaOp::CreateIndex variant and apply-style logic exist in stackhouse/src/api/dashboard.rs (intended to power a future schema-editing dashboard UI), but that module is never mounted into the Axum router in stackhouse/src/main.rs — it's dead code today, not a callable API. To create an index right now, use the raw SQL endpoint (subject to its own gating — see Querying and API Reference) or a versioned schema migration.

curl -X POST http://localhost:3000/v1/sql/query \
  -H "Content-Type: application/json" \
  -d '{"query": "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users (email)"}'

Index Types

Primary Index

Auto-created on id column. Unique, auto-incrementing. Fast lookups.

Secondary Index

User-created. Can be unique or non-unique. Speeds up queries on the indexed column.

Vector Index

For similarity search — HNSW, built and served by an external Qdrant instance (not Stackhouse's own storage engine). See Vector Search.

When to Create Indexes

✅ Frequently queried columns
✅ Join columns
✅ Filter/order by columns
❌ Low-cardinality columns (e.g., boolean)
❌ Columns updated frequently

Performance Impact

Approach	Complexity
Without index	O(n) full scan
With index	O(log n) index lookup + O(1) row fetch

Query speedup: 10-100x for large datasets

Next: Vector Search