EchoQuest v0.19.10: Database & Caching
Bundles 0–4 optimized the server loop, network protocol, and client rendering. Bundle 3 (numbered by priority, not sequence) targets the database layer — the N+1 query patterns, missing indexes, and uncached high-frequency reads that were generating hundreds of unnecessary queries per second.
Achievement Perks Cache (Biggest Win)
Every time a player attacks an enemy, the server calls getCharacterPerks() to look up completed achievement bonuses — damage vs. specific monsters, crit bonus, defense bonus, and more. This function was called at 5 different points in the combat pipeline, adding up to roughly 50 database queries per second per active player in combat. With 10 players fighting, that’s 500 queries/sec on a single function.
The fix is a simple in-memory cache with a 60-second TTL. Perks rarely change (only when an achievement completes), so caching is safe. Invalidation hooks in incrementProgress(), checkLevelAchievements(), and checkCoinAchievements() ensure the cache is cleared immediately when a perk-granting achievement is completed. This single change is the largest query reduction in the entire performance plan.
SpawnZone N+1 → Single JOIN
When a player joins a map, the server loads all active spawn zones for that map (1 query), then loops through each zone to load its monster assignments (N queries). A map with 10 spawn zones meant 11 queries. This is the classic N+1 pattern. The fix replaces the loop with a single LEFT JOIN across spawn_zones, spawn_zone_monsters, and monsters, then groups the flat result rows back into nested objects in JavaScript. 11 queries become 1.
Inventory Save Batch INSERT
The auto-save system fires every ~30 seconds and persists each player’s inventory. The old code deleted all inventory rows (1 query), then inserted each item individually in a loop (N queries). A player with 30 items meant 31 queries every 30 seconds. The fix builds a single multi-row INSERT with parameterized placeholders: INSERT INTO ... VALUES ($1,$2,$3,$4), ($5,$6,$7,$8), .... Same transaction safety, 31 queries become 2.
Quest Objectives Batch
Loading active quests fetched the quest list (1 query), then looped each quest to get its objectives (N queries). The fix collects all quest IDs and fetches objectives in a single WHERE quest_id = ANY($1) query, then groups them by quest ID in a Map. 6 queries become 2.
Crafting Material Counts
Opening a crafting station was the worst N×M pattern: for each recipe, for each material, the server queried the player’s inventory count individually. 10 recipes with 3 materials each meant 30 queries just to show the crafting UI. A new getInventoryItemCounts() function collects all unique material slugs across all recipes, queries them in a single GROUP BY, and returns a Map. The crafting service looks up counts from the Map instead of hitting the database. 30 queries become 1.
Recipe Materials Batch
Loading recipes for a profession fetched the recipe list, then looped each recipe to load its materials. The fix uses the same ANY() batch pattern as quests. This only fires on cache miss (recipes have a 5-minute TTL), but when it does fire, 11 queries become 2.
NPC Shop Items Cache
Shop inventories are read-heavy and rarely change (only on buy/sell with stock limits). A 5-minute TTL cache with deep copy on read prevents stock mutations from leaking into cached data. Cache invalidation hooks after buy and sell transactions ensure consistency. Repeat shop opens are now instant.
Performance Indexes
Three indexes were added to support the optimized queries: a B-tree on characters.current_map for map join lookups, a composite on character_inventory(character_id, item_id) for the batch material count query, and one on character_achievements(character_id, status) for the perk cache query. All created with IF NOT EXISTS for idempotent migration.
Impact
The combined effect targets total database query volume. Combat goes from ~50 QPS per player to ~1 per 60 seconds for perk lookups alone. Map joins, auto-saves, quest logs, crafting stations, and shops all see 80%+ query reductions. This completes Bundle 3 of the 9-bundle performance plan.
Try the EchoQuest Demo — free in your browser. Full game coming to Steam.