How do we make the top customers endpoint fast?

src/server.ts⋯ } +// ---------------------------------------------------------------------------+// In-memory response cache for GET /stats/top-customers+// TTL is 60 seconds by default; tests may override via the exported constant.+// ---------------------------------------------------------------------------++export const CACHE_TTL_MS = 60_000;++interface CacheEntry {+  value: TopCustomer[];+  expiresAt: number;+}++// One cache map per module load. Each buildServer() call shares this cache so+// that concurrent requests collapse onto the same in-flight query (see the+// inflight map below) and hot restarts pick up immediately.+const cache = new Map<number, CacheEntry>();+const inflight = new Map<number, Promise<TopCustomer[]>>();++/** Exposed so tests can invalidate entries without waiting for real TTL. */+export function bustCache(): void {+  cache.clear();+}++async function cachedTopCustomers(db: PGlite, limit: number): Promise<TopCustomer[]> {+  const now = Date.now();+  const entry = cache.get(limit);+  if (entry && now < entry.expiresAt) {+    return entry.value;+  }++  // Collapse concurrent requests for the same limit into one DB query.+  let pending = inflight.get(limit);+  if (!pending) {+    pending = topCustomers(db, limit).then((rows) => {+      cache.set(limit, { value: rows, expiresAt: Date.now() + CACHE_TTL_MS });+      inflight.delete(limit);+      return rows;+    }).catch((err) => {+      inflight.delete(limit);+      throw err;+    });+    inflight.set(limit, pending);+  }++  return pending;+}+ function send(res: ServerResponse, status: number, body: unknown): void {   res.writeHead(status, { "content-type": "application/json" });⋯       if (req.method === "GET" && url.pathname === "/stats/top-customers") {         const limit = Math.min(Math.max(Number(url.searchParams.get("limit") ?? 10), 1), 100);-        return send(res, 200, await topCustomers(db, limit));+        return send(res, 200, await cachedTopCustomers(db, limit));       }

src/db.ts⋯     ANALYZE;   `);++  // Materialized view: precomputed top-customers ranking (no time filter —+  // the view itself stores the rolling-30-day aggregation so every refresh+  // re-evaluates the window cheaply against a fresh snapshot).+  await db.exec(`+    CREATE MATERIALIZED VIEW mv_top_customers AS+    SELECT c.id AS customer_id,+           c.name,+           sum(o.amount_cents)::int AS revenue_cents,+           count(*)::int           AS orders+    FROM orders o+    JOIN customers c ON c.id = o.customer_id+    WHERE o.status = 'paid'+      AND o.created_at > now() - interval '30 days'+    GROUP BY c.id, c.name+    ORDER BY revenue_cents DESC;++    CREATE UNIQUE INDEX ON mv_top_customers (customer_id);+  `);+   return db; }src/index.ts⋯ import { openDb } from "./db.ts"; import { buildServer } from "./server.ts";+import { startRefresh } from "./matview.ts";  const port = Number(process.env.PORT ?? 3000); const started = Date.now(); const db = await openDb();+startRefresh(db); buildServer(db).listen(port, () => {   console.log(`orders-api listening on ${port} (seeded in ${Date.now() - started} ms)`);src/matview.ts⋯+import type { PGlite } from "@electric-sql/pglite";++const REFRESH_INTERVAL_MS = 5_000;++/**+ * Starts a background interval that refreshes mv_top_customers every 5 s.+ * Returns a stop function that clears the interval (call it in tests/shutdown).+ *+ * CONCURRENTLY is used so reads are never blocked during refresh.+ * It requires the unique index on customer_id that openDb() creates.+ */+export function startRefresh(db: PGlite): () => void {+  const timer = setInterval(() => {+    db.exec("REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_customers").catch(+      (err) => console.error("[matview] refresh error:", err),+    );+  }, REFRESH_INTERVAL_MS);++  // Let Node exit even if the interval is still pending (belt-and-suspenders).+  if (typeof timer.unref === "function") timer.unref();++  return () => clearInterval(timer);+}src/server.ts⋯ export async function topCustomers(db: PGlite, limit: number): Promise<TopCustomer[]> {   const { rows } = await db.query<TopCustomer>(-    `SELECT c.id AS customer_id,-            c.name,-            sum(o.amount_cents)::int AS revenue_cents,-            count(*)::int AS orders-     FROM orders o-     JOIN customers c ON c.id = o.customer_id-     WHERE o.status = 'paid'-       AND o.created_at > now() - interval '30 days'-     GROUP BY c.id, c.name+    `SELECT customer_id, name, revenue_cents, orders+     FROM mv_top_customers      ORDER BY revenue_cents DESC      LIMIT $1`,

src/db.ts⋯     FROM generate_series(1, ${ORDERS}); +    -- Partial covering index for the top-customers query.+    -- The WHERE clause limits the index to 'paid' rows only (~75% of the+    -- table, but the only rows the query ever touches).+    -- Including customer_id and amount_cents means Postgres can satisfy the+    -- entire GROUP BY / SUM from the index without visiting the heap at all+    -- (index-only scan).  created_at is the leading column so the 30-day+    -- range filter is a cheap B-tree range scan rather than a full index scan.+    CREATE INDEX orders_paid_created_covering_idx+      ON orders (created_at, customer_id, amount_cents)+      WHERE status = 'paid';+     ANALYZE;   `);src/server.ts⋯ // endpoint the Settle demo debates how to speed up. export async function topCustomers(db: PGlite, limit: number): Promise<TopCustomer[]> {+  // Aggregate on orders first using only the columns covered by the partial+  // index (status = 'paid' is the index predicate; created_at, customer_id,+  // amount_cents are index columns), then join to customers for the name.+  // This lets Postgres use an index-only scan for the heavy aggregation step+  // and avoids dragging the customers table into the hash/sort before LIMIT.   const { rows } = await db.query<TopCustomer>(-    `SELECT c.id AS customer_id,+    `SELECT agg.customer_id,             c.name,-            sum(o.amount_cents)::int AS revenue_cents,-            count(*)::int AS orders-     FROM orders o-     JOIN customers c ON c.id = o.customer_id-     WHERE o.status = 'paid'-       AND o.created_at > now() - interval '30 days'-     GROUP BY c.id, c.name-     ORDER BY revenue_cents DESC-     LIMIT $1`,+            agg.revenue_cents,+            agg.orders+     FROM (+       SELECT customer_id,+              sum(amount_cents)::int AS revenue_cents,+              count(*)::int          AS orders+       FROM orders+       WHERE status = 'paid'+         AND created_at > now() - interval '30 days'+       GROUP BY customer_id+       ORDER BY revenue_cents DESC+       LIMIT $1+     ) agg+     JOIN customers c ON c.id = agg.customer_id+     ORDER BY agg.revenue_cents DESC`,     [limit],   );