simple-web-app

commit c91d4f26eb82f291337bcf6c896e864e41d62799
parent 638106b9a532703c50af06c3954d16679f056ba1
Author: Silas Brack <silasbrack@gmail.com>
Date:   Fri, 12 Jun 2026 23:20:37 +0200

perf: add SQLite performance PRAGMAs, merge feed db_query calls

Added synchronous=NORMAL (safe with WAL, skips unnecessary fsync),
mmap_size=256MB, temp_store=MEMORY.

Merged the two sequential spawn_blocking calls in the feed handler
into one, halving thread spawn + mutex lock overhead per request.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Diffstat:
M
src/database.rs
 | 
10
+++++++++-
M
src/handlers/feed.rs
 | 
18
+++++++-----------

2 files changed, 16 insertions(+), 12 deletions(-)
diff --git a/src/database.rs b/src/database.rs
@@ -105,7 +105,15 @@ pub struct StoryMeta {
 // ==========================================================================
 
 const POOL_SIZE: usize = 10;
-const PRAGMAS: &str = "PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000; PRAGMA cache_size=-64000;";
+const PRAGMAS: &str = "\
+    PRAGMA journal_mode=WAL;\
+    PRAGMA synchronous=NORMAL;\
+    PRAGMA foreign_keys=ON;\
+    PRAGMA busy_timeout=5000;\
+    PRAGMA cache_size=-64000;\
+    PRAGMA mmap_size=268435456;\
+    PRAGMA temp_store=MEMORY;\
+";
 
 pub struct Database {
     path: String,
diff --git a/src/handlers/feed.rs b/src/handlers/feed.rs
@@ -46,10 +46,10 @@ async fn fetch_feed(
     let tag_id = query.tag;
     let page = query.page;
 
-    let (stories, categories) = state.db_query(move |db| {
+    let (stories, categories, voted_ids, meta) = state.db_query(move |db| {
         let categories = db.get_categories(20)?;
 
-        let stories = match tag_id {
+        let all_stories = match tag_id {
             Some(tid) => db.get_stories_by_tag(tid, PAGE_LIMIT + 1, offset)?,
             None => match sort {
                 FeedSort::Hot => db.get_stories_hot(PAGE_LIMIT + 1, offset)?,
@@ -58,15 +58,9 @@ async fn fetch_feed(
             },
         };
 
-        Ok((stories, categories))
-    }).await?;
-
-    let reached_end = stories.len() <= PAGE_LIMIT;
-    let stories: Vec<_> = stories.into_iter().take(PAGE_LIMIT).collect();
+        let stories: Vec<_> = all_stories.into_iter().take(PAGE_LIMIT).collect();
+        let story_ids: Vec<i64> = stories.iter().map(|s| s.id).collect();
 
-    let story_ids: Vec<i64> = stories.iter().map(|s| s.id).collect();
-
-    let (voted_ids, meta) = state.db_query(move |db| {
         let voted_ids = if let Some(uid) = user_id {
             db.get_user_votes(uid, TARGET_TYPE_STORY, &story_ids)
                 .unwrap_or_default()
@@ -76,9 +70,11 @@ async fn fetch_feed(
 
         let meta = db.enrich_stories(&story_ids).unwrap_or_default();
 
-        Ok((voted_ids, meta))
+        Ok((stories, categories, voted_ids, meta))
     }).await?;
 
+    let reached_end = stories.len() <= PAGE_LIMIT;
+
     let mut enriched_stories = Vec::with_capacity(stories.len());
     let base_rank = page * PAGE_LIMIT;