Optimizing SurrealDB Queries with Field Indexing on JSON Documents

Slow JSON Lookups? Indexing Solves Performance Bottlenecks in SurrealDB

When using SurrealDB for flexible, JSON-based data modeling, simple field filtering on large datasets can quickly degrade performance. The useful takeaway is that defining explicit indexes on frequently queried JSON fields is the most direct way to ensure fast, scalable lookups without sacrificing schema flexibility.

Native JSON Storage vs. Relational Rigidity

SurrealDB excels because it supports native JSON document storage. This avoids the need for rigid, predefined relational schemas, allowing developers to evolve data structures easily. However, this flexibility means the database cannot automatically optimize every query; performance hinges on proper indexing.

Implementing Field Indexes

Indexes tell the database where to find data quickly, rather than scanning every document (a full table scan). You define these using the `DEFINE INDEX` command.

Worked Example: Indexing a User Profile Field

Assume you store user data where the `profile` field is a JSON object containing a searchable `status` (e.g., 'active', 'pending').

DEFINE INDEX user_status_idx ON user (profile.status);

Where to run: SurrealQL CLI or API endpoint.

Permissions: Requires database write/schema modification privileges.

Expected Check: The command should execute without syntax errors, and subsequent queries filtering on profile.status should execute significantly faster than unindexed queries on a large dataset.

Query Performance Comparison

Consider a dataset of 100,000 users. A query like SELECT * FROM user WHERE profile.status = 'active' will be near-instantaneous when user_status_idx is present, whereas it could take several seconds without it.

Trade-off: Write Overhead

The primary limitation of indexing is write overhead. Every time a document is inserted, updated, or deleted, the index structure must also be maintained. Over-indexing (indexing every field) will slow down writes without providing commensurate query benefits.

Practical Check: Run a benchmark query against 100,000 records both with and without the index defined. Monitor the execution time difference.

Limitation: SurrealDB does not enforce data integrity on the JSON structure; application-level validation remains necessary to prevent queries against null or unexpected field types.

Actionable Step

Identify the top three fields used in WHERE clauses across your application. Define specific indexes for those fields rather than relying on full-document scans. This balances flexibility with necessary query performance.