commit 569eb827d6e7b45e8a482bacc42d1e03c4122b6d
parent c91d4f26eb82f291337bcf6c896e864e41d62799
Author: Silas Brack <silasbrack@gmail.com>
Date: Fri, 12 Jun 2026 23:28:32 +0200
perf: separate reader pool and writer connection
Reads (20 methods) use a pool of 10 connections that never
contend with writes. Writes (9 methods) use a single dedicated
connection behind its own Mutex. In WAL mode, readers never block
on the writer and vice versa.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
| M | src/database.rs | | | 142 | +++++++++++++++++++++++++++++++++++++++++-------------------------------------- |
1 file changed, 74 insertions(+), 68 deletions(-)
diff --git a/src/database.rs b/src/database.rs
@@ -104,7 +104,7 @@ pub struct StoryMeta {
// Database
// ==========================================================================
-const POOL_SIZE: usize = 10;
+const READ_POOL_SIZE: usize = 10;
const PRAGMAS: &str = "\
PRAGMA journal_mode=WAL;\
PRAGMA synchronous=NORMAL;\
@@ -117,33 +117,42 @@ const PRAGMAS: &str = "\
pub struct Database {
path: String,
- conns: Mutex<Vec<Connection>>,
+ readers: Mutex<Vec<Connection>>,
+ writer: Mutex<Connection>,
}
-/// RAII guard that returns a connection to the pool on drop.
-pub struct PooledConn<'a> {
+/// RAII guard that returns a read connection to the pool on drop.
+pub struct ReadConn<'a> {
conn: Option<Connection>,
- pool: &'a Database,
+ db: &'a Database,
}
-impl<'a> std::ops::Deref for PooledConn<'a> {
+impl<'a> std::ops::Deref for ReadConn<'a> {
type Target = Connection;
fn deref(&self) -> &Connection {
self.conn.as_ref().unwrap()
}
}
-impl<'a> Drop for PooledConn<'a> {
+impl<'a> Drop for ReadConn<'a> {
fn drop(&mut self) {
if let Some(conn) = self.conn.take() {
- let mut conns = self.pool.conns.lock().unwrap();
- if conns.len() < POOL_SIZE {
- conns.push(conn);
+ let mut readers = self.db.readers.lock().unwrap();
+ if readers.len() < READ_POOL_SIZE {
+ readers.push(conn);
}
}
}
}
+fn open_conn(path: &str) -> Result<Connection, AppError> {
+ let c = Connection::open(path)
+ .map_err(|e| AppError::Database(format!("Failed to open db: {e}")))?;
+ c.execute_batch(PRAGMAS)
+ .map_err(|e| AppError::Database(format!("Failed to set PRAGMAs: {e}")))?;
+ Ok(c)
+}
+
impl Database {
pub fn new(path: &str) -> Result<Self, AppError> {
if let Some(parent) = std::path::Path::new(path).parent() {
@@ -151,49 +160,43 @@ impl Database {
.map_err(|e| AppError::Database(format!("Failed to create db directory: {e}")))?;
}
- // Run migrations
- let conn = Connection::open(path)
- .map_err(|e| AppError::Database(format!("Failed to open db: {e}")))?;
- conn.execute_batch(PRAGMAS)
- .map_err(|e| AppError::Database(format!("Failed to set PRAGMAs: {e}")))?;
- crate::migrations::run(&conn).map_err(AppError::Database)?;
-
- // Pre-fill pool
- let mut conns = Vec::with_capacity(POOL_SIZE);
- conns.push(conn);
- for _ in 1..POOL_SIZE {
- let c = Connection::open(path)
- .map_err(|e| AppError::Database(format!("Failed to open db: {e}")))?;
- c.execute_batch(PRAGMAS)
- .map_err(|e| AppError::Database(format!("Failed to set PRAGMAs: {e}")))?;
- conns.push(c);
+ // Run migrations on the writer connection
+ let writer = open_conn(path)?;
+ crate::migrations::run(&writer).map_err(AppError::Database)?;
+
+ // Pre-fill read pool
+ let mut readers = Vec::with_capacity(READ_POOL_SIZE);
+ for _ in 0..READ_POOL_SIZE {
+ readers.push(open_conn(path)?);
}
Ok(Self {
path: path.to_string(),
- conns: Mutex::new(conns),
+ readers: Mutex::new(readers),
+ writer: Mutex::new(writer),
})
}
- fn get_conn(&self) -> Result<PooledConn<'_>, AppError> {
+ /// Get a read-only connection from the pool.
+ fn read_conn(&self) -> Result<ReadConn<'_>, AppError> {
let conn = {
- let mut conns = self.conns.lock().unwrap();
- conns.pop()
+ let mut readers = self.readers.lock().unwrap();
+ readers.pop()
};
let conn = match conn {
Some(c) => c,
- None => {
- // Pool exhausted, open a new connection
- let c = Connection::open(&self.path)
- .map_err(|e| AppError::Database(format!("Failed to open db: {e}")))?;
- c.execute_batch(PRAGMAS)
- .map_err(|e| AppError::Database(format!("Failed to set PRAGMAs: {e}")))?;
- c
- }
+ None => open_conn(&self.path)?,
};
- Ok(PooledConn { conn: Some(conn), pool: self })
+ Ok(ReadConn { conn: Some(conn), db: self })
+ }
+
+ /// Get the exclusive writer connection.
+ fn write_conn(&self) -> Result<std::sync::MutexGuard<'_, Connection>, AppError> {
+ self.writer
+ .lock()
+ .map_err(|e| AppError::Database(format!("Writer lock poisoned: {e}")))
}
// ======================================================================
@@ -218,7 +221,7 @@ impl Database {
}
pub fn get_stories_new(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM story ORDER BY published DESC LIMIT ?1 OFFSET ?2"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -232,7 +235,7 @@ impl Database {
}
pub fn get_stories_top(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM story ORDER BY score DESC, published DESC LIMIT ?1 OFFSET ?2"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -247,7 +250,7 @@ impl Database {
pub fn get_stories_hot(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let fetch_limit = (limit + offset) * 2;
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM story ORDER BY published DESC LIMIT ?1"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -268,7 +271,7 @@ impl Database {
}
pub fn get_story(&self, id: i64) -> Result<Option<Story>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT * FROM story WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -291,7 +294,7 @@ impl Database {
domain: Option<&str>,
language: &str,
) -> Result<Story, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
let published = chrono::Utc::now().format("%Y-%m-%d %H:%M:%S").to_string();
conn.execute(
@@ -301,6 +304,7 @@ impl Database {
).map_err(|e| AppError::Database(e.to_string()))?;
let id = conn.last_insert_rowid();
+ drop(conn);
self.get_story(id)?.ok_or_else(|| AppError::Database("Failed to retrieve created story".to_string()))
}
@@ -312,7 +316,7 @@ impl Database {
text: &str,
domain: Option<&str>,
) -> Result<(), AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
conn.execute(
"UPDATE story SET title = ?1, url = ?2, text = ?3, domain = ?4 WHERE id = ?5",
params![title, url, text, domain, id],
@@ -321,7 +325,7 @@ impl Database {
}
pub fn get_stories_by_tag(&self, category_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT s.* FROM story s
JOIN story_category sc ON sc.news_item_id = s.id
@@ -339,7 +343,7 @@ impl Database {
}
pub fn get_stories_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM story WHERE created_by = ?1 ORDER BY published DESC LIMIT ?2 OFFSET ?3"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -353,7 +357,7 @@ impl Database {
}
pub fn search_stories(&self, query: &str, limit: usize) -> Result<Vec<SearchResult>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT s.id, s.title, s.text, s.score, s.comment_count
FROM story_fts fts
@@ -399,7 +403,7 @@ impl Database {
}
pub fn get_comments_for_story(&self, story_id: i64) -> Result<Vec<Comment>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM comment WHERE story_id = ?1 ORDER BY path"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -419,7 +423,7 @@ impl Database {
parent_id: Option<i64>,
text: &str,
) -> Result<Comment, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
// Calculate path and depth
let (path, depth) = match parent_id {
@@ -469,11 +473,12 @@ impl Database {
).map_err(|e| AppError::Database(e.to_string()))?;
let id = conn.last_insert_rowid();
+ drop(conn);
self.get_comment(id)?.ok_or_else(|| AppError::Database("Failed to retrieve created comment".to_string()))
}
pub fn get_comment(&self, id: i64) -> Result<Option<Comment>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT * FROM comment WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -488,7 +493,7 @@ impl Database {
}
pub fn get_comments_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Comment>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT * FROM comment WHERE created_by = ?1 ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -507,7 +512,7 @@ impl Database {
/// Toggle vote: INSERT if not exists, DELETE if exists. Returns true if now voted.
pub fn toggle_vote(&self, user_id: i64, target_type: i64, target_id: i64) -> Result<bool, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
// Try to insert
let result = conn.execute(
@@ -536,7 +541,7 @@ impl Database {
return Ok(vec![]);
}
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
// Build placeholders for IN clause
let placeholders: Vec<String> = target_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 3)).collect();
@@ -569,7 +574,7 @@ impl Database {
// ======================================================================
pub fn get_categories(&self, limit: usize) -> Result<Vec<Category>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT id, name FROM category LIMIT ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -587,7 +592,7 @@ impl Database {
}
pub fn get_categories_for_story(&self, story_id: i64) -> Result<Vec<Category>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare(
"SELECT c.id, c.name FROM category c
JOIN story_category sc ON sc.category_id = c.id
@@ -608,7 +613,7 @@ impl Database {
}
pub fn add_tag_to_story(&self, story_id: i64, category_id: i64) -> Result<(), AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
conn.execute(
"INSERT INTO story_category (news_item_id, category_id) VALUES (?1, ?2)",
params![story_id, category_id],
@@ -617,7 +622,7 @@ impl Database {
}
pub fn remove_all_tags_from_story(&self, story_id: i64) -> Result<(), AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
conn.execute(
"DELETE FROM story_category WHERE news_item_id = ?1",
params![story_id],
@@ -645,7 +650,7 @@ impl Database {
}
pub fn get_user_by_id(&self, id: i64) -> Result<Option<User>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT * FROM user WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -660,7 +665,7 @@ impl Database {
}
pub fn get_user_by_email(&self, email: &str) -> Result<Option<User>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT * FROM user WHERE email = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -675,7 +680,7 @@ impl Database {
}
pub fn get_user_by_username(&self, username: &str) -> Result<Option<User>, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let mut stmt = conn.prepare("SELECT * FROM user WHERE username = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
@@ -690,18 +695,19 @@ impl Database {
}
pub fn create_user(&self, email: &str, password_hash: &str, username: &str, verification_token: Option<&str>) -> Result<User, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
conn.execute(
"INSERT INTO user (email, password_hash, username, verification_token) VALUES (?1, ?2, ?3, ?4)",
params![email, password_hash, username, verification_token],
).map_err(|e| AppError::Database(e.to_string()))?;
let id = conn.last_insert_rowid();
+ drop(conn);
self.get_user_by_id(id)?.ok_or_else(|| AppError::Database("Failed to retrieve created user".to_string()))
}
pub fn verify_email_token(&self, token: &str) -> Result<bool, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
let rows_affected = conn.execute(
"UPDATE user SET email_verified = 1, verification_token = NULL WHERE verification_token = ?1",
params![token],
@@ -710,7 +716,7 @@ impl Database {
}
pub fn update_verification_token(&self, user_id: i64, token: &str) -> Result<(), AppError> {
- let conn = self.get_conn()?;
+ let conn = self.write_conn()?;
conn.execute(
"UPDATE user SET verification_token = ?1 WHERE id = ?2",
params![token, user_id],
@@ -727,7 +733,7 @@ impl Database {
return Ok(vec![]);
}
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
// Fetch author usernames
let placeholders: Vec<String> = story_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 1)).collect();
@@ -793,7 +799,7 @@ impl Database {
return Ok(HashMap::new());
}
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
let placeholders: Vec<String> = user_ids.iter().enumerate().map(|(i, _)| format!("?{}", i + 1)).collect();
let sql = format!(
"SELECT id, username FROM user WHERE id IN ({})",
@@ -820,14 +826,14 @@ impl Database {
/// Get the score of a story
pub fn get_story_score(&self, id: i64) -> Result<i64, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
conn.query_row("SELECT score FROM story WHERE id = ?1", params![id], |row| row.get(0))
.map_err(|e| AppError::Database(e.to_string()))
}
/// Get the score of a comment
pub fn get_comment_score(&self, id: i64) -> Result<i64, AppError> {
- let conn = self.get_conn()?;
+ let conn = self.read_conn()?;
conn.query_row("SELECT score FROM comment WHERE id = ?1", params![id], |row| row.get(0))
.map_err(|e| AppError::Database(e.to_string()))
}
