SQLite WAL Reader/Writer Pool Pattern

SQLite WAL Reader/Writer Pool Pattern

Problem

A single Mutex<Connection> for all database access serializes every read and write. When background operations (embedding indexing, sync, checkpoints) write frequently, interactive reads (search, page load) queue behind them, causing perceptible UI latency.

Symptoms:

• Search queries slow during background embedding indexing
• SQLITE_BUSY errors when concurrent operations compete for the single connection
• UI freezes while waiting for write lock release

Investigation

Why WAL Alone Isn’t Enough

Enabling WAL (Write-Ahead Logging) allows concurrent readers while a writer is active — but only if they use separate connections. A single Mutex<Connection> still serializes all access regardless of journal mode.

Steps Tried

1. Single connection with WAL — still serialized by the Mutex
2. Two connections (reader + writer) — worked but limited concurrency
3. Writer + N-reader pool — optimal for read-heavy workloads

Root Cause

SQLite allows exactly one writer at a time but unlimited concurrent readers in WAL mode. To exploit this, the application needs separate connection objects for reads vs. writes.

Solution

Refactor from single connection to writer + reader pool:

pub struct WorkspaceDatabase {
    writer: Mutex<Connection>,
    readers: Vec<Mutex<Connection>>,
    reader_index: AtomicUsize,  // Round-robin selector
}

const READER_POOL_SIZE: usize = 4;

Connection Configuration

Every connection (reader and writer) gets the same pragmas:

fn configure_connection(conn: &Connection) -> rusqlite::Result<()> {
    conn.pragma_update(None, "journal_mode", "WAL")?;
    conn.pragma_update(None, "synchronous", "NORMAL")?;
    conn.pragma_update(None, "cache_size", -2000)?;   // 2MB per connection
    conn.pragma_update(None, "busy_timeout", 5000)?;   // 5s retry on BUSY
    Ok(())
}

Why these values:

• WAL — enables concurrent reads during writes
• synchronous = NORMAL — safe with WAL (fsync on checkpoint, not every commit)
• cache_size = -2000 — 2MB cache; 5 connections = ~10MB total
• busy_timeout = 5000 — retry for 5s before returning SQLITE_BUSY (critical for write contention between the embedding pipeline and user saves)

Access Methods

impl WorkspaceDatabase {
    /// Read-only access via round-robin reader pool
    pub fn with_reader<F, T, E>(&self, f: F) -> Result<T, E>
    where F: FnOnce(&Connection) -> Result<T, E>
    {
        let idx = self.reader_index.fetch_add(1, Ordering::Relaxed)
            % self.readers.len();
        let conn = self.readers[idx].lock().unwrap();
        f(&conn)
    }

    /// Exclusive write access via single writer connection
    pub fn with_writer<F, T, E>(&self, f: F) -> Result<T, E>
    where F: FnOnce(&Connection) -> Result<T, E>
    {
        let conn = self.writer.lock().unwrap();
        f(&conn)
    }

    /// Transaction on writer connection
    pub fn with_transaction<F, T>(&self, f: F) -> Result<T, rusqlite::Error>
    where F: FnOnce(&Transaction) -> Result<T, rusqlite::Error>
    {
        let mut conn = self.writer.lock().unwrap();
        let tx = conn.transaction()?;
        let result = f(&tx)?;
        tx.commit()?;
        Ok(result)
    }
}

Round-robin uses Ordering::Relaxed — exact distribution doesn’t matter; we only need atomicity, not memory ordering. Two threads occasionally hitting the same reader is harmless.

In-Memory Testing

Named in-memory databases with shared cache allow readers and writer to share the same data:

fn open_in_memory() -> WorkspaceDatabase {
    static COUNTER: AtomicUsize = AtomicUsize::new(0);
    let id = COUNTER.fetch_add(1, Ordering::Relaxed);
    let uri = format!("file:test_db_{}?mode=memory&cache=shared", id);

    let writer = Connection::open_with_flags(&uri,
        OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE
        | OpenFlags::SQLITE_OPEN_URI | OpenFlags::SQLITE_OPEN_SHARED_CACHE)?;

    let readers = (0..READER_POOL_SIZE).map(|_| {
        Connection::open_with_flags(&uri,
            OpenFlags::SQLITE_OPEN_READ_ONLY
            | OpenFlags::SQLITE_OPEN_URI | OpenFlags::SQLITE_OPEN_SHARED_CACHE)
    }).collect();

    WorkspaceDatabase { writer, readers, reader_index: AtomicUsize::new(0) }
}

Key: Unique names per test (COUNTER) prevent cross-test interference in parallel test execution.

Migration Path

Introduce with_connection() as a temporary bridge that routes to with_reader():

/// Deprecated: use with_reader() or with_writer() explicitly
pub fn with_connection<F, T, E>(&self, f: F) -> Result<T, E> { self.with_reader(f) }

Then convert callsites one module at a time. Repository methods that only SELECT use with_reader(); those that INSERT/UPDATE/DELETE use with_writer() or with_transaction().

Prevention

Decision Guide

Use	Method
SELECT queries	with_reader()
Single INSERT/UPDATE/DELETE	with_writer()
Multiple writes that must be atomic	with_transaction()
Batch operations (embedding upsert)	with_transaction()

Pool Sizing

• 4 readers is a reasonable default for desktop apps
• More readers = more file descriptors + memory, diminishing returns past CPU cores
• Writer is always exactly 1 (SQLite limitation)

Warning Signs

• SQLITE_BUSY errors in logs — increase busy_timeout or investigate long-held write locks
• Slow reads during writes — verify readers are separate connections, not sharing the writer
• Memory growth — check cache_size per connection, multiply by pool size

References

• Commit: 0a68a65 — feat(sqlite): split WorkspaceDatabase into reader pool + writer (INK-178)
• SQLite WAL documentation: https://sqlite.org/wal.html
• File: crates/infrastructure/sqlite/src/connection.rs