From fe3708eaf495613cc6e2340b821795f25811d6ed Mon Sep 17 00:00:00 2001
From: bndw <ben@bdw.to>
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/config/README.md      |  9 ++-------
 internal/config/config.go      | 16 ++++------------
 internal/config/config_test.go |  5 ++---
 internal/storage/storage.go    |  6 ++++++
 4 files changed, 14 insertions(+), 22 deletions(-)

(limited to 'internal')

diff --git a/internal/config/README.md b/internal/config/README.md
index 87d6fa1..79e1b89 100644
--- a/internal/config/README.md
+++ b/internal/config/README.md
@@ -75,11 +75,8 @@ database:
   # Path to SQLite database file
   path: "relay.db"
 
-  # Maximum number of open connections
-  max_connections: 10
-
-  # Connection max lifetime
-  max_lifetime: "1h"
+  # Note: Connection pooling is automatically configured for SQLite.
+  # SQLite uses a single connection for optimal performance.
 
 # Authentication configuration
 auth:
@@ -264,8 +261,6 @@ Server:
 
 Database:
   Path: "relay.db"
-  MaxConnections: 10
-  MaxLifetime: 1h
 
 Auth:
   Enabled: false
diff --git a/internal/config/config.go b/internal/config/config.go
index 87ca4eb..91e79f7 100644
--- a/internal/config/config.go
+++ b/internal/config/config.go
@@ -31,9 +31,9 @@ type ServerConfig struct {
 
 // DatabaseConfig holds database configuration.
 type DatabaseConfig struct {
-	Path           string        `yaml:"path"`
-	MaxConnections int           `yaml:"max_connections"`
-	MaxLifetime    time.Duration `yaml:"max_lifetime"`
+	Path string `yaml:"path"`
+	// Note: SQLite connection pooling is handled internally in the storage layer.
+	// SQLite works best with a single connection due to its single-writer architecture.
 }
 
 // AuthConfig holds authentication configuration.
@@ -106,9 +106,7 @@ func Default() *Config {
 			WriteTimeout: 30 * time.Second,
 		},
 		Database: DatabaseConfig{
-			Path:           "relay.db",
-			MaxConnections: 10,
-			MaxLifetime:    1 * time.Hour,
+			Path: "relay.db",
 		},
 		Auth: AuthConfig{
 			Enabled:         false,
@@ -239,12 +237,6 @@ func applyEnvOverrides(cfg *Config) {
 	if val := os.Getenv("MUXSTR_DATABASE_PATH"); val != "" {
 		cfg.Database.Path = val
 	}
-	if val := os.Getenv("MUXSTR_DATABASE_MAX_CONNECTIONS"); val != "" {
-		var n int
-		if _, err := fmt.Sscanf(val, "%d", &n); err == nil {
-			cfg.Database.MaxConnections = n
-		}
-	}
 
 	// Auth
 	if val := os.Getenv("MUXSTR_AUTH_ENABLED"); val != "" {
diff --git a/internal/config/config_test.go b/internal/config/config_test.go
index 50d9b67..e1df1aa 100644
--- a/internal/config/config_test.go
+++ b/internal/config/config_test.go
@@ -257,7 +257,6 @@ server:
 
 database:
   path: "test.db"
-  max_lifetime: "30m"
 
 rate_limit:
   cleanup_interval: "10m"
@@ -282,7 +281,7 @@ rate_limit:
 		t.Errorf("expected write timeout 2m, got %v", cfg.Server.WriteTimeout)
 	}
 
-	if cfg.Database.MaxLifetime != 30*time.Minute {
-		t.Errorf("expected max lifetime 30m, got %v", cfg.Database.MaxLifetime)
+	if cfg.RateLimit.CleanupInterval != 10*time.Minute {
+		t.Errorf("expected cleanup interval 10m, got %v", cfg.RateLimit.CleanupInterval)
 	}
 }
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