Creating Compound Indexes for Multi-Field Filtering

Developers implementing offline-first state persistence frequently encounter O(n) full-table scans or DataError exceptions when chaining independent IDBIndex lookups. This degrades PWA responsiveness and violates strict latency SLAs for mobile web teams. This walkthrough is part of Indexing Strategies for Fast Queries and shows how a single compound index resolves a two-field predicate in one B-tree traversal.

Root Cause Analysis

IndexedDB’s default indexing model optimizes only single-key lookups. Without explicit schema-level compound index registration, the browser engine cannot leverage B-tree traversal for intersecting conditions. Attempting to intersect two separate indexes forces sequential cursor iteration or throws DataError when invalid key ranges are passed. Implementing robust Indexing Strategies for Fast Queries requires defining a composite keyPath during the onupgradeneeded lifecycle to enable native multi-field range resolution.

The key insight is that a compound index stores its entries sorted lexicographically across the whole tuple — first by the leading field, then by the next, and so on. That ordering is exactly what lets a single IDBKeyRange carve out “this status, priority from 3 up” as one contiguous slice of the tree. The spec guarantees this lexicographic key comparison, which is why field order in the keyPath is a correctness concern and not a stylistic one.

Step-by-Step Implementation

Step 1: Register Compound Index During Version Upgrade

Define the keyPath as an ordered array of field names in createIndex(). The array order dictates query precedence: leading fields support exact matches, while trailing fields support range queries. All schema operations must use the implicit versionchange transaction — do not call db.transaction() inside onupgradeneeded.

// Must execute inside onupgradeneeded using the implicit versionchange transaction
const request = indexedDB.open('tasks-db', 1);

request.onupgradeneeded = (event) => {
  const db = event.target.result;
  const store = db.createObjectStore('tasks', { keyPath: 'id' });

  // Compound index: [status, priority]
  // unique: false allows multiple records with identical status/priority combos
  store.createIndex('status_priority', ['status', 'priority'], {
    unique: false,
  });
};

Step 2: Construct Async Query Wrapper with Explicit Error Handling

Wrap transaction and index access in a try/catch block. Handle QuotaExceededError and InvalidStateError explicitly. Use IDBKeyRange.bound() to define precise multi-field boundaries.

async function queryCompoundIndex(db, status, minPriority) {
  return new Promise((resolve, reject) => {
    const tx = db.transaction('tasks', 'readonly');
    const store = tx.objectStore('tasks');

    // Verify index exists to prevent NotFoundError
    if (!store.indexNames.contains('status_priority')) {
      console.warn(
        'Compound index missing. Falling back to client-side filter.'
      );
      const fallbackReq = store.getAll();
      fallbackReq.onsuccess = () =>
        resolve(
          fallbackReq.result.filter(
            (r) => r.status === status && r.priority >= minPriority
          )
        );
      fallbackReq.onerror = () => reject(fallbackReq.error);
      return;
    }

    const index = store.index('status_priority');
    // Bound range: [exact_status, min_priority] to [exact_status, Infinity]
    const range = IDBKeyRange.bound([status, minPriority], [status, Infinity]);
    const request = index.openCursor(range);

    const results = [];
    request.onsuccess = (e) => {
      const cursor = e.target.result;
      if (cursor) {
        results.push(cursor.value);
        cursor.continue();
      } else {
        resolve(results);
      }
    };
    request.onerror = () => reject(request.error);
  });
}

Step 3: Execute Filtered Cursor Iteration

Pass exact match values for leading index fields and range bounds for trailing fields. Parameter alignment with the createIndex() array order is mandatory to prevent DataError.

// Executes native B-tree traversal — no client-side filtering required.
const filteredTasks = await queryCompoundIndex(db, 'active', 3);
console.log(`Retrieved ${filteredTasks.length} matching records`);

Verification & Production Safeguards

Before deploying to production, validate query performance and memory safety:

1. Latency Profiling: Wrap execution in performance.now() to measure start/end deltas. Target sub-50 ms on datasets exceeding 10k records with a well-selected compound index.
2. Range Isolation Verification: Confirm IDBKeyRange correctly isolates target records. Inspect cursor.value payloads to ensure no post-fetch .filter() calls are masking inefficient queries.
3. Memory & Callback Safety: Verify the onsuccess callback terminates cleanly when cursor evaluates to null. Persistent references to large result arrays cause memory leaks in long-lived PWA sessions; clear results arrays immediately after DOM rendering or state hydration.

You can also verify in DevTools: open the Application panel, expand IndexedDB, select the store, and confirm status_priority appears under the store’s index list. Run the query twice and compare performance.now() deltas — a correctly bound compound query stays flat as the store grows, while a getAll() fallback scales linearly with record count.

Edge Cases & Fallback Approaches

A few situations break the happy path. The table below maps each to its cause and the fix.

The most common fallback is the missing-index guard already shown in Step 2: when store.indexNames.contains('status_priority') is false, the code degrades to a full getAll() and an in-memory filter so the feature still works while the upgrade propagates. This same defensive pattern is essential for the streaming reads described in Query Optimization & Cursors, and for the page-by-page traversal in Paginating Large IndexedDB Result Sets with Cursors.

Note: Compound indexes are immutable post-creation. Schema changes require a version bump and data migration. Always test index creation in a private browsing window to bypass stale schema caches.

Frequently Asked Questions

Related

• Indexing Strategies for Fast Queries — the broader guide on index design and field ordering.
• Query Optimization & Cursors — streaming compound-index results without exhausting memory.
• Paginating Large IndexedDB Result Sets with Cursors — keyset pagination over a compound key range.
• Database Schema Migrations — recreating indexes across version upgrades.