perf: Offload blocking SQLite calls to a dedicated thread pool

[mpiton]

Description

The SQLite cache performs blocking I/O operations directly on the async tokio runtime. This can block worker threads, preventing them from making progress on other concurrent tasks, leading to increased latency.

Rationale

Functions like SqliteCache::get and SqliteCache::insert use the synchronous rusqlite library. When called from an async context, these functions can block a tokio worker thread for the duration of the database query. Wrapping these calls in tokio::task::spawn_blocking moves the work to a dedicated thread pool for blocking tasks, keeping the async event loop responsive.

Current Metric

Synchronous database queries block async threads, potentially causing latency under load.

Expected Improvement

Improved LSP server responsiveness, especially during operations that trigger database access (e.g., fetching cached version info). Reduced tail latency for API endpoints.

Implementation

1. Change the methods in the ReadCache and WriteCache impls for SqliteCache to be async.
2. Inside each method, wrap the blocking database call (e.g., conn.query_row(...)) in a tokio::task::spawn_blocking closure.
3. Update the call sites to await the async cache methods.

Trade-offs

Slight overhead from moving tasks between thread pools. Code becomes more complex due to the introduction of async and spawn_blocking.

Affected Files

• dependi-lsp/src/cache/sqlite.rs

Estimated Effort

Medium

---

Source: ideation/performance_optimizations_ideas.json — perf-002