Performance: HNSW index loading blocks database initialization (10+ seconds for 10k records)

[Brad-Edwards]

Imported from kuzudb/kuzu#6047
Original URL: kuzudb#6047
Original author: @1amageek
Original created at: 2025-10-05T01:41:23Z

Problem

Database initialization is blocked by sequential HNSW vector index loading, causing significant delays that scale linearly with the number of indexed records.

Measured Impact:

Records	Init Time	User Experience
1,000	~2s	Noticeable
10,000	~10s	Unacceptable
100,000	~60s+	Critical

Real-world impact: iOS app with 10,000+ photo records experiences 10+ second white screen on startup.

---

Root Cause

HNSW indexes are loaded sequentially within a recovery transaction during database initialization:

// extension_manager.cpp:93-97
void ExtensionManager::autoLoadLinkedExtensions(main::ClientContext* context) {
    auto trxContext = transaction::TransactionContext::Get(*context);
    trxContext->beginRecoveryTransaction();  // ⬅️ Single-threaded lock
    loadLinkedExtensions(context, loadedExtensions);  // ⬅️ Sequential
    trxContext->commit();
}

// vector_extension.cpp:10-29
static void initHNSWEntries(main::ClientContext* context) {
    for (auto& indexEntry : catalog->getIndexEntries()) {
        if (indexEntry->getIndexType() == HNSWIndexCatalogEntry::TYPE_NAME) {
            unloadedIndex.load(context, storageManager);  // ⬅️ O(N) per index
        }
    }
}

Bottleneck: Each HNSW index loads graph structure (O(N) complexity) one at a time.

Log evidence:

[KUZU DEBUG] Checkpointer: auto-loading linked extensions
[KUZU DEBUG] IndexHolder::load() START - indexType='HNSW'
[KUZU DEBUG] IndexHolder: calling loadFunc()
    ⬆️ This takes 10+ seconds for 10k records
[KUZU DEBUG] IndexHolder::load() COMPLETE

---

Proposed Solution

Key observation: After WAL replay completes, the database is in a consistent read-only state where:

• ✅ All transactions applied (data consistent)
• ✅ No concurrent writes (initialization phase)
• ✅ HNSW indexes are independent (no shared state)
• ✅ Checkpoint data is immutable (thread-safe reads)

Therefore: Transaction locking is unnecessary, and indexes can be loaded in parallel.

Implementation Suggestion

// extension_manager.cpp - Add parallel loading path
void ExtensionManager::autoLoadLinkedExtensions(main::ClientContext* context) {
    // Check if parallel loading is safe
    if (isConsistentAfterWAL() && canLoadInParallel()) {
        loadIndexesInParallel(context);  // NEW: Parallel path
        return;
    }

    // Fallback: Sequential loading (existing behavior)
    auto trxContext = transaction::TransactionContext::Get(*context);
    trxContext->beginRecoveryTransaction();
    loadLinkedExtensions(context, loadedExtensions);
    trxContext->commit();
}

void ExtensionManager::loadIndexesInParallel(main::ClientContext* context) {
    std::vector<std::thread> workers;

    for (auto& indexEntry : getHNSWIndexes()) {
        workers.emplace_back([&]() {
            // Load without transaction lock (read-only, post-WAL)
            indexEntry.loadWithoutTransaction(context, storageManager);
        });
    }

    for (auto& worker : workers) {
        worker.join();
    }
}

Safety Analysis

Why parallel loading is safe:

1. Post-WAL consistency: Data is stable after WALReplayer::replay()
2. Read-only operations: HNSW loading only reads checkpoint data
3. Index independence: Each index operates on different table/column
4. Thread-safety: All operations are on thread-local objects

---

Expected Impact

Performance improvement (based on Amdahl's Law with 95% parallelizable work):

CPU Cores	Current	Parallel	Speedup
2	10.0s	5.5s	1.8x
4	10.0s	3.0s	3.3x
8	10.0s	1.8s	5.6x

User experience:

• Mobile apps: Sub-second startup instead of 10+ seconds
• Desktop apps: Near-instant database initialization
• Large datasets: Scales better with multi-core systems

---

Implementation Considerations

1. Backward compatibility: Add as opt-in feature with fallback to sequential loading
2. Configuration: SystemConfig::enableParallelIndexLoading (default: true)
3. Thread limit: SystemConfig::maxIndexLoadThreads (default: auto-detect cores)
4. Error handling: Graceful fallback to sequential on any thread error

---

Additional Context

• Platform: Tested on iOS (ARM64) with 10,000+ photo records
• Use case: Photo mosaic app with LAB color vector similarity search
• Current workaround: Defer GraphContainer initialization to background thread (UI still blocked)

Would appreciate feedback on this approach. Happy to provide more details or help with implementation if this aligns with Kuzu's roadmap.

[Brad-Edwards]

Imported comment from kuzudb/kuzu
Original author: @hpvd
Original created at: 2025-10-06T12:08:25Z
Original URL: kuzudb#6047 (comment)
+1 on parallelizing as much as possible.

Couldn't one, in addition to this, not also think of a concept to "lazy load" "advanced things" to make startup to basic DB usage as fast as possible?
(Similar approach as done on website loading: Delaying the loading of non-critical or non-visible content is also known as “lazy loading.” It is a common best practice that serves to improve performance and optimize page usage.)
Possibly there are other things beside HNSW Index which could be deferred...