From fe3708eaf495613cc6e2340b821795f25811d6ed Mon Sep 17 00:00:00 2001 From: bndw Date: Sat, 14 Feb 2026 09:44:55 -0800 Subject: fix: optimize SQLite connection pooling for single-writer architecture Remove misleading max_connections config option and properly configure SQLite connection pooling in the storage layer. Changes: - Set MaxOpenConns(1) for optimal SQLite performance - Set MaxIdleConns(1) to keep connection alive - Set ConnMaxLifetime(0) to never close connection - Remove max_connections and max_lifetime from DatabaseConfig - Update docs to clarify SQLite's single-writer architecture Rationale: SQLite is an embedded database with a single-writer lock. Multiple connections cause lock contention and reduce performance. WAL mode allows concurrent reads from the same connection, making connection pooling unnecessary and counterproductive. This change makes the configuration clearer and ensures optimal SQLite performance by using a single long-lived connection. --- internal/storage/storage.go | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'internal/storage/storage.go') diff --git a/internal/storage/storage.go b/internal/storage/storage.go index d00d7bf..9ef9956 100644 --- a/internal/storage/storage.go +++ b/internal/storage/storage.go @@ -22,6 +22,12 @@ func New(dbPath string) (*Storage, error) { return nil, fmt.Errorf("failed to open database: %w", err) } + // Configure connection pool for SQLite + // SQLite works best with a single connection due to single-writer lock + db.SetMaxOpenConns(1) // Single connection (SQLite is single-writer) + db.SetMaxIdleConns(1) // Keep connection alive + db.SetConnMaxLifetime(0) // Never close the connection + // Configure SQLite for optimal performance pragmas := []string{ "PRAGMA journal_mode=WAL", // Write-Ahead Logging for concurrency -- cgit v1.2.3