asseeninmovies: home page — 60s in-memory cache with inflight dedup + startup warmup (count(*) on 290k movies stays slow cold but every user request hits cached data; concurrent home hits no longer stack up in pg_stat_activity)

d6451f313d2a97780e561098d43fa2c8cea5bf92 · 2026-05-12 17:25:15 -0700 · SteveStudio2

Files touched

M server.js

Diff

commit d6451f313d2a97780e561098d43fa2c8cea5bf92
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Tue May 12 17:25:15 2026 -0700

    asseeninmovies: home page — 60s in-memory cache with inflight dedup + startup warmup (count(*) on 290k movies stays slow cold but every user request hits cached data; concurrent home hits no longer stack up in pg_stat_activity)
---
 server.js | 54 +++++++++++++++++++++++++++++++++++++++---------------
 1 file changed, 39 insertions(+), 15 deletions(-)

diff --git a/server.js b/server.js
index affe258..8362f38 100644
--- a/server.js
+++ b/server.js
@@ -1223,17 +1223,17 @@ app.get('/people', async (req, res) => {
 });
 
 // Home — placeholder until tick 1 ships TMDB ingest + real UI.
-app.get('/', async (_req, res) => {
-  // Live-stats home with featured rails of recent movies + people. Read-only.
-  let stats = { movies_total: 0, people_total: 0, movies_poster: 0, people_headshot: 0, spotted_total: 0 };
-  let featuredMovies = [], featuredPeople = [], featuredSpotted = [];
-  try {
-    // Parallel via Promise.all so the home page wall time = max of the four
-    // queries instead of sum. count(*) on asim_movies/asim_people is the
-    // long-pole at ~3s cold; running the featured-rail picks alongside (not
-    // after) cuts total home-page time roughly in half. Kept ORDER BY
-    // random() since TABLESAMPLE SYSTEM had 3s startup AND produced empty
-    // samples when paired with a restrictive WHERE.
+// In-memory home-page cache. count(*) on 290k asim_movies + 61k asim_people
+// is 2-3s cold each, and concurrent home hits stack up in pg_stat_activity.
+// TTL 60s — stats don't change second-to-second, and the spotted/featured
+// rails benefit from light memoization too.
+let _homeCache = null;
+const HOME_CACHE_TTL_MS = 60_000;
+async function getHomeCache() {
+  const now = Date.now();
+  if (_homeCache && (now - _homeCache.at) < HOME_CACHE_TTL_MS) return _homeCache.data;
+  if (_homeCache && _homeCache.inflight) return _homeCache.inflight;
+  const inflight = (async () => {
     const [s, fm, fp, fs] = await Promise.all([
       pool.query(`
         SELECT
@@ -1251,10 +1251,29 @@ app.get('/', async (_req, res) => {
          WHERE s.verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')
          ORDER BY s.verified_at DESC LIMIT 6`)
     ]);
-    stats = s.rows[0];
-    featuredMovies = fm.rows;
-    featuredPeople = fp.rows;
-    featuredSpotted = fs.rows;
+    return { stats: s.rows[0], featuredMovies: fm.rows, featuredPeople: fp.rows, featuredSpotted: fs.rows };
+  })();
+  _homeCache = { at: now, inflight, data: _homeCache?.data || null };
+  try {
+    const data = await inflight;
+    _homeCache = { at: Date.now(), inflight: null, data };
+    return data;
+  } catch (e) {
+    _homeCache = { at: now, inflight: null, data: _homeCache?.data || null };
+    throw e;
+  }
+}
+
+app.get('/', async (_req, res) => {
+  // Live-stats home with featured rails of recent movies + people. Read-only.
+  let stats = { movies_total: 0, people_total: 0, movies_poster: 0, people_headshot: 0, spotted_total: 0 };
+  let featuredMovies = [], featuredPeople = [], featuredSpotted = [];
+  try {
+    const cached = await getHomeCache();
+    stats = cached.stats;
+    featuredMovies = cached.featuredMovies;
+    featuredPeople = cached.featuredPeople;
+    featuredSpotted = cached.featuredSpotted;
   } catch (e) {
     // home page should never 500; fall back to static.
   }
@@ -1392,4 +1411,9 @@ app.get('/', async (_req, res) => {
 app.listen(PORT, '127.0.0.1', async () => {
   await ensureSchema();
   console.log(`asseeninmovies → http://127.0.0.1:${PORT}`);
+  // Prime the home-page cache so the first real request doesn't wait on
+  // cold count(*) queries (2-3s each on 290k+61k rows).
+  getHomeCache()
+    .then(d => console.log(`[home-cache] warm — movies=${d.stats.movies_total} people=${d.stats.people_total} spotted=${d.stats.spotted_total}`))
+    .catch(e => console.warn(`[home-cache] warmup failed: ${e.message}`));
 });

← 25508fb asseeninmovies: home page perf — parallelize 4 queries via P · back to AsSeenInMovies · asseeninmovies: /movies + /people — parallelize page query a dbd4763 →