Indexes & nested data

Declare a secondary index

Creation and backfill validate against the actual collection before atomic publication. A failed unique build leaves no committed index. Named index metadata is returned by the collections endpoint. An equivalent existing definition with the same ordered fields, uniqueness and sparse policy is rejected before another build. Different field orders and policies remain distinct.

{
  "requestId": "projects-workspace-status-index-v1",
  "operations": [
    {
      "op": "create_index",
      "collection": "projects",
      "name": "by_workspace_status",
      "fields": [
        "workspace",
        "status"
      ],
      "unique": false,
      "sparse": false
    }
  ]
}

Nested and expanded keys

Index paths can traverse objects and explicitly expand arrays, for example passkeys[*].credentialId. Repeated array values within one record count once. Compound expanded paths form independent Cartesian products and do not assert same-array-element matches.

Expansion is bounded to 4,096 traversal steps and keys per record/index. Current admission permits 64 named indexes per collection, 250,000 members per index and 4,000,000 members across indexes including constraints. Unlimited nesting or expansion is not claimed.

Choose uniqueness and null semantics

Nonunique index writes perform no uniqueness count. Changes to unique memberships are checked through the common delta path. Deleting a record removes its primary, secondary and TTL memberships atomically.

Use indexes without losing matches

Dense scalar composite indexes can serve leading equality prefixes and the next scalar range after a complete equality prefix. Sparse or expanded indexes cannot safely serve every prefix query because omitted records may still match. The planner retains a complete candidate path and verifies original predicates.

SELECT _key, score FROM scores
WHERE tenant = :tenant AND score >= :low AND score < :high
LIMIT 100

Order a compound index after equality

An index on (tenant, score) can serve this filter and order through one bounded lookup. The leading tenant equality is retained in the plan, so SORT needs only the remaining score column. Ascending and descending queries use the same index, with ascending primary-key ties.

Fixed leading columns may be omitted or included in index order with either direction. Every varying column must remain in index order with one direction; an omitted varying suffix cannot preserve ties. Scalar coverage must be proven by maintained index metadata. Sparse prefix/range paths, expanded arrays and incomplete coverage retain the existing sort path.

Residual filters run before OFFSET and LIMIT. The planner keeps its chosen selective index and does not switch to another ordering index without a cost decision. This functional path is implemented; its dedicated FZ performance comparison remains pending.

SELECT score FROM scores
WHERE tenant = :tenant AND score >= :low AND score < :high
SORT score DESC, _key ASC
LIMIT 20 OFFSET 10

Expire records with a TTL policy

TTL timestamps are unsigned Unix seconds. Missing/null values do not expire. TTL is asynchronous deletion; records retain their unique values until deletion commits. Authorization must check expiry itself. Embedded hosts schedule db.expire(limit); standalone maintenance schedules bounded sweeps.

{
  "requestId": "sessions-expiry-v1",
  "operations": [
    {
      "op": "create_ttl_index",
      "collection": "sessions",
      "name": "expiry",
      "field": "expiresAtSec",
      "expireAfter": 0
    }
  ]
}