Perf: N+1 query storm when listing messages (batch enrichment) #84

New issue

Closed

opened 2026-07-08 02:40:32 +00:00 by icub3d · 0 comments

icub3d commented

2026-07-08 02:40:32 +00:00

Owner

Category: Performance — found in code review 2026-07-07.

Problem

server/src/messages/handlers.rs:90 (enrich_message) runs 3 separate queries per message (list_attachments_for_message, list_reaction_counts, get_thread_summary), awaited in a for loop (:221). A 50-message page ≈ 150 sequential round-trips. A batched list_thread_summaries(&[id]) already exists (storage/sqlite/threads.rs:91) but the list path uses the single-row variant.

Fix

Collect the page's message IDs, then run 3 batch queries: attachments WHERE message_id IN (...), reaction counts grouped by message_id, and the existing list_thread_summaries. Assemble in memory.
Add list_attachments_for_messages(&[String]) and list_reaction_counts_for_messages(&[String], viewer).

Turns ~150 queries into 3.

**Category: Performance** — found in code review 2026-07-07. ## Problem `server/src/messages/handlers.rs:90` (`enrich_message`) runs **3 separate queries per message** (`list_attachments_for_message`, `list_reaction_counts`, `get_thread_summary`), awaited in a `for` loop (`:221`). A 50-message page ≈ 150 sequential round-trips. A batched `list_thread_summaries(&[id])` already exists (`storage/sqlite/threads.rs:91`) but the list path uses the single-row variant. ## Fix - [ ] Collect the page's message IDs, then run 3 batch queries: attachments `WHERE message_id IN (...)`, reaction counts grouped by message_id, and the existing `list_thread_summaries`. Assemble in memory. - [ ] Add `list_attachments_for_messages(&[String])` and `list_reaction_counts_for_messages(&[String], viewer)`. Turns ~150 queries into 3.

icub3d added the

area:api

status:planned

type:refactor

labels

2026-07-08 02:40:32 +00:00

icub3d referenced this issue from a commit

2026-07-08 03:56:10 +00:00

perf(messages): batch list enrichment to fix N+1 queries

icub3d referenced this issue from a pull request that will close it,

2026-07-08 03:56:46 +00:00

perf(messages): batch list enrichment to fix N+1 queries #99