High-Traffic Row Caching

In high-traffic applications with heavy search filtering and pagination (such as e-commerce product catalogs or social feeds), caching full query results as monolithic JSON blobs wastes memory and causes massive cache churn.

client.rows() implements an optimized two-tier caching pattern that separates search index ordering from entity row data.

---

The Problem with Traditional Caching

Suppose you have 10,000 products viewed through 50 different filter and sorting combinations:

[ Page 1 Sort by Price ] ──► Contains Products [ A, B, C, D, E ]
[ Page 1 Sort by Rating] ──► Contains Products [ C, A, F, B, G ]
[ Search "wireless"    ] ──► Contains Products [ A, D, H, B, K ]

In standard caching, products A and B are duplicated across dozens of separate cache keys. Updating product A's price requires invalidating or updating dozens of separate keys.

---

The Two-Tier Solution with client.rows()

Cachemate splits the query into:

1. Query ID List: cm:index:products:price_asc:page_1 -> ['id_A', 'id_B', 'id_C']
2. Individual Row Cache: cm:row:products:id_A -> { id: 'id_A', title: 'Wireless Headphones', price: 99 }

src/services/catalog.ts

import { client } from '../lib/cache';

interface Product {
  id: string;
  title: string;
  price: number;
  category: string;
}

interface PaginationMeta {
  page: number;
  perPage: number;
  total: number;
}

export async function searchCatalog(params: {
  category?: string;
  sortBy: 'price' | 'rating';
  page: number;
}) {
  return await client.rows<Product, PaginationMeta>({
    namespace: 'catalog',
    tables: ['products'],
    filters: params,
    ttlSeconds: 600, // 10 minutes

    // 1. Extract unique ID from entity
    rowId: (product) => product.id,

    // 2. Query executed when list index is missing
    getByAll: async () => {
      console.log('[DB] Executing full SQL query for catalog search');
      const { items, total } = await db.products.search({
        where: params.category ? { category: params.category } : {},
        orderBy: { [params.sortBy]: 'asc' },
        skip: (params.page - 1) * 20,
        take: 20,
      });

      return {
        rows: items,
        meta: { page: params.page, perPage: 20, total },
      };
    },

    // 3. Selective batch query executed when list is present but specific rows are missing
    getAllById: async (missingIds: string[]) => {
      console.log(`[DB] Fetching ${missingIds.length} missing row entities by ID`);
      return await db.products.findMany({
        where: { id: { in: missingIds } },
      });
    },
  });
}

---

Benefits

1. 80%+ Memory Reduction: Entity rows are stored once. Even if a product appears across 500 search queries, its data payload is stored in Redis only once.
2. Non-Blocking Background Write: The caller receives the HTTP response immediately on cache misses. Redis write operations run asynchronously in the background.
3. Resilient to Partial Eviction: If Redis evicts 1 item from memory under pressure, Cachemate calls getAllById([missingId]) to fetch only that 1 row instead of re-running the heavy 20-row join query.