Performance Optimization

There are two separate problems here, one on the server and one in the browser. Both show up in the code and the page description, but neither is measured yet. I'd measure first and confirm which one dominates LCP.

What to measure first

1. Network waterfall in DevTools, throttled to mobile. Look at three numbers:

   • TTFB and download time for /api/products.
   • The JSON payload size.
   • The total image bytes, and when the LCP element's request starts and finishes.

   This tells you whether LCP is gated by the API, the images, or both. The API call has to finish before React can render any <img>, so the delays add up in sequence.

2. The LCP element in the Lighthouse or Performance trace. Check which image it is, and look at the breakdown into TTFB, resource load delay, resource load duration and render delay.

3. Postgres query count and timing for one request. Turn on log_min_duration_statement = 0 temporarily, or use pg_stat_statements. Also run EXPLAIN ANALYZE on the individual queries. With 1,200 products, I expect about 2,401 queries per request.

Likely bottlenecks

Backend: N+1 queries. The endpoint runs 1 query for products, then 1 for the category and 1 for the reviews of each product, one after another with await. Even at about 0.4 ms each, that is around a second of sequential round trips. This matches the p95 of 1.2 s. The endpoint also has these problems:

• SELECT * with no pagination returns all 1,200 products in one response.
• It sends every individual review rating, when the list page probably only needs an average and a count.
• The queries are built by string concatenation. The values are integer IDs from your own database, so this is probably not exploitable today, but it is a bad habit and should use parameters.
• Check that reviews.product_id has an index. It may already, and the plan will tell you.

Frontend: images and DOM size. 1,200 full 2400px JPEGs is probably several to tens of MB on a phone. That is most likely the main reason LCP is 4.8 s. Rendering 1,200 cards also costs main-thread time, and it will hurt INP.

What I'd change

Change one thing at a time and re-measure after each, as in the sequence below.

API (do first, it's cheap and the cause is clear):

app.get('/api/products', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 24, 100);
  const offset = Math.max(parseInt(req.query.offset) || 0, 0);
  const { rows } = await db.query(`
    SELECT p.id, p.name, p.price, p.image_url,
           c.name AS category,
           COALESCE(r.avg_rating, 0) AS avg_rating,
           COALESCE(r.review_count, 0) AS review_count
    FROM products p
    LEFT JOIN categories c ON c.id = p.category_id
    LEFT JOIN LATERAL (
      SELECT AVG(rating)::float AS avg_rating, COUNT(*)::int AS review_count
      FROM reviews WHERE product_id = p.id
    ) r ON true
    ORDER BY p.id
    LIMIT $1 OFFSET $2`, [limit, offset]);
  res.json(rows);
});

This turns about 2,400 queries into 1, selects only the columns the list needs, adds pagination with a stable order, and parameterizes the inputs. Make sure reviews(product_id) is indexed. If the aggregate is still slow, store avg_rating and review_count on products or in a materialized view. Keyset pagination is better than OFFSET for deep pages. Add HTTP caching headers or a short TTL cache only if the data is read far more often than it changes, and key the cache on the query parameters.

Front end (probably the biggest LCP win):

• Show about 24 products per page with "load more" or infinite scroll, or virtualize the list. This is the largest single reduction in DOM size, bytes and requests.
• Serve resized images: generate 320, 640 and 960px variants in AVIF or WebP at build time or through an image CDN. Use srcset and sizes so a phone downloads a 15–60 KB image instead of a multi-hundred-KB one.
• Give every <img> a width and height to avoid layout shift. Use loading="lazy" and decoding="async" on everything below the fold. Give the actual LCP image fetchpriority="high" and don't lazy-load it.
• Consider server-rendering or prefetching the first page, so the LCP image doesn't have to wait for the JS bundle and then the API call.

Verifying

Re-measure the same way as the baseline (same throttling, same data, repeated runs). Compare against run-to-run noise, and revert anything that doesn't clearly move the numbers. Then add a Lighthouse CI budget and field monitoring of LCP p75 with web-vitals.

My expectation, which the measurements need to confirm, is that the query fix takes the p95 from 1.2 s to a few tens of milliseconds. The image and pagination work should bring LCP into the "good" range (≤ 2.5 s). If the waterfall shows something different, such as a slow TTFB from somewhere other than the queries, follow that instead.