How We Scaled Next.js Dynamic Sitemaps to 150k+ Pages for Enterprise SEOIn my years of scaling high-traffic programmatic web platforms, few things have triggered as many late-night alerts as dynamic sitemap failures. When our product catalog grew from 10,000 pages to over 150,000, our basic build-time static sitemap generation strategy ground to a halt. Builds were timing out, database connections were saturating, and Googlebot began encountering 504 Gateway Timeouts when attempting to fetch our index files.Sitemaps are the structural blueprint of your site. If search crawlers cannot read your sitemaps efficiently, they stop indexing new pages, killing organic visibility. In this article, I will walk you through the exact production architecture I built using Next.js App Router, Redis caching, and dynamic chunking to serve lightning-fast sitemaps under 150ms.The Core Bottlenecks of Large-Scale SitemapsBefore diving into the code, it is vital to understand the constraints we must design around. Google imposes a strict physical limit of 50,000 URLs or 50MB (uncompressed) per individual sitemap file. If you exceed either threshold, your sitemap is rejected.To support more than 50,000 URLs, you must use a Sitemap Index File. This is an index of nested sitemaps. For our architecture, we needed a nested structure like this:/sitemap.xml (The root index pointing to individual sitemap chunks)/sitemaps/products-1.xml/sitemaps/products-2.xml/sitemaps/categories.xmlGenerating these dynamic indexes on-the-fly presents a massive load problem. Fetching 150k records from a relational database, mapping them to XML strings, and serializing them over HTTP can easily spike server memory and exhaust database connection pools.Step 1: Architecting the Sitemap Index RouterOur first objective is to build the parent sitemap index. This file tells search engine bots where to find the child chunks. In Next.js App Router, we use a custom Route Handler targeting app/sitemap.xml/route.ts to control the XML headers and dynamically compute the number of chunks we need to list.Here is how I implemented the dynamic index coordinator:import { NextResponse } from 'next/server';
const BATCH_SIZE = 40000; // Leaving a buffer under the 50k limit
const BASE_URL = 'https://example.com';
async function getTotalProductCount(): Promise<number> {
// Query database count quickly
const response = await fetch(${process.env.INTERNAL_API_URL}/api/products/count, {
next: { revalidate: 3600 } // Cache count lookup for an hour
});
const data = await response.json();
return data.count;
}
export async function GET() {
const totalProducts = await getTotalProductCount();
const chunkCount = Math.ceil(totalProducts / BATCH_SIZE);
let sitemapIndexXML = <?xml version="1.0" encoding="UTF-8"?>\n;
sitemapIndexXML += <sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n;
// Add static routes sitemap
sitemapIndexXML += <sitemap>\n <loc>${BASE_URL}/sitemaps/static.xml</loc>\n </sitemap>\n;
// Add dynamic product sitemap chunks
for (let i = 0; i < chunkCount; i++) {
sitemapIndexXML += <sitemap>\n <loc>${BASE_URL}/sitemaps/products-${i}.xml</loc>\n </sitemap>\n;
}
sitemapIndexXML += </sitemapindex>;
return new NextResponse(sitemapIndexXML, {
headers: {
'Content-Type': 'application/xml',
'Cache-Control': 'public, max-age=3600, s-maxage=86400, stale-while-revalidate=600'
}
});
}In this code block, we calculate the number of virtual pages required to segment our catalog. Instead of pulling all records, we perform a lightweight COUNT query. The root index outputs the list of nested XML URLs, set with an aggressive caching header to prevent repeated database hits.Step 2: Creating the Dynamic Dynamic Chunk HandlerWith our sitemap index set up, we now need to capture the incoming requests for each individual chunk (e.g., /sitemaps/products-1.xml). In Next.js, we can utilize dynamic route catch-all parameters to map these incoming chunk requests cleanly.We create a route handler at app/sitemaps/[chunk]/route.ts. This handler parses the index parameter, executes a paginated database query, and streams the raw XML format back to the client.import { NextRequest, NextResponse } from 'next/server';
const BATCH_SIZE = 40000;
const BASE_URL = 'https://example.com';
interface ProductSubset {
slug: string;
updatedAt: string;
}
async function getProductBatch(pageIndex: number): Promise<ProductSubset[]> {
const offset = pageIndex * BATCH_SIZE;
const response = await fetch(
${process.env.INTERNAL_API_URL}/api/products?limit=${BATCH_SIZE}&offset=${offset},
{ next: { revalidate: 1800 } }
);
if (!response.ok) return [];
return response.json();
}
export async function GET(request: NextRequest, { params }: { params: { chunk: string } }) {
const { chunk } = params;
const pageMatch = chunk.match(/products-(\d+)\.xml/);
if (!pageMatch) {
return new NextResponse('Sitemap Not Found', { status: 404 });
}
const pageIndex = parseInt(pageMatch[1], 10);
const products = await getProductBatch(pageIndex);
let sitemapXML = <?xml version="1.0" encoding="UTF-8"?>\n;
sitemapXML += <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n;
for (const product of products) {
const safeDate = new Date(product.updatedAt).toISOString();
sitemapXML += <url>\n;
sitemapXML += <loc>${BASE_URL}/products/${product.slug}</loc>\n;
sitemapXML += <lastmod>${safeDate}</lastmod>\n;
sitemapXML += <changefreq>weekly</changefreq>\n;
sitemapXML += <priority>0.8</priority>\n;
sitemapXML += </url>\n;
}
sitemapXML += </urlset>;
return new NextResponse(sitemapXML, {
headers: {
'Content-Type': 'application/xml',
'Cache-Control': 'public, max-age=7200, s-maxage=86400, stale-while-revalidate=1200'
}
});
}Using this modular chunk approach, we completely isolate database pressure. We fetch exactly 40,000 dynamic URLs at a time, preventing Node.js process out-of-memory errors and maintaining high availability.Step 3: Optimizing Database Fetching via CursorsMany engineers overlook database performance when building programmatic SEO pipelines. Using offset pagination (LIMIT 40000 OFFSET 80000) causes databases like PostgreSQL or MySQL to read and discard all previous rows before reaching the desired offset. This makes fetching higher page indexes progressively slower.To combat this, we transitioned to Keyset Pagination or sequential cursor-based fetching for our backend API. Rather than relying on simple dynamic offsets, our API leverages indexed date ranges or numerical primary keys:-- Slow query as index increases
SELECT slug, updated_at FROM products LIMIT 40000 OFFSET 120000;
-- Fast query using indexed keyranges
SELECT slug, updated_at FROM products WHERE id > 120000 ORDER BY id ASC LIMIT 40000;Adding an database composite index on (id, updated_at) ensures queries execute instantly, keeping sitemap response generation times within acceptable latency tolerances.Step 4: Layering Redis for Bulletproof Edge CachingEven with pagination optimized, Googlebot and other crawlers like Bingbot scan your site concurrently, downloading multiple sitemaps simultaneously. If they request the same sitemap chunks simultaneously, you will experience database spikes. To prevent this, implement a Redis Cache Layer directly in front of the Next.js API router.By reading directly from memory (Redis) instead of performing slow relational DB fetches, response times plunge from 1.5 seconds to under 40 milliseconds.Here is an operational pattern using Upstash or standard Redis inside the Next.js router:import { Redis } from '@upstash/redis';
const redis = new Redis({
url: process.env.REDIS_URL || '',
token: process.env.REDIS_TOKEN || ''
});
async function getCachedSitemap(chunkId: string): Promise<string | null> {
try {
return await redis.get(sitemap:${chunkId});
} catch (error) {
console.error('Redis cache lookup failed', error);
return null;
}
}
async function setCachedSitemap(chunkId: string, data: string): Promise<void> {
try {
// Cache sitemaps for 12 hours
await redis.set(sitemap:${chunkId}, data, { ex: 43200 });
} catch (error) {
console.error('Redis cache save failed', error);
}
}During a sitemap execution request, the endpoint instantly checks the cache. If a cache miss occurs, the backend queries the database, formats the XML, writes it async to Redis, and returns the response. This pattern completely decouples external crawl intensity from your application db.Step 5: Testing and Submitting to Google Search ConsoleOnce you deploy your dynamic chunked configuration, test the XML structures directly before committing changes. You can easily validate structural integrity using free command-line tools like xmllint:curl -s https://example.com/sitemap.xml | xmllint --noout -If there are missing closing tags, unescaped characters (like naked & symbols), or invalid schemas, the tool outputs detailed syntax errors. Ensure special characters in your database slugs (such as &, <, >, ", ') are correctly escaped or encoded before generating output.After automated verification completes, submit only the parent index file (https://example.com/sitemap.xml) inside Google Search Console. Googlebot automatically crawls the index file, extracts the individual child dynamic sitemaps, and processes the child dynamic items asynchronously over the following days.Frequently Asked Questions (FAQs)Frequently Asked Questions (FAQs)1. Can I use compression on large dynamic sitemaps?Yes, but you rarely need to configure it manually. Cloud providers and CDN edge proxies like Cloudflare or AWS CloudFront compress XML payloads using gzip or Brotli by default if the client requests it. Ensure your custom Route Handler serves a valid Content-Type: application/xml header, which signals the edge proxies to apply compressions safely.2. How often should I invalidate the cached sitemaps?For most directories and e-commerce websites, regenerating your dynamic sitemaps once every 12 to 24 hours is optimal. Googlebot does not crawl your sitemap constantly; it usually crawls them once a day or once a week depending on your site's crawl budget. Using a stale-while-revalidate header guarantees search bots get served instant responses while updates process in the background.3. What happens if there is an unescaped character in my slug XML?If an XML sitemap contains invalid characters (e.g., raw ampersands inside URL parameters), search crawlers throw parsing errors and abort reading the entire file. Always run your slug and URL generations through an encoder like encodeURIComponent(), or escape special characters manually using predefined entities like &amp; before sending the response string.4. Should I list static pages in dynamic chunked sitemaps?No. Keep your dynamic collections separate from your high-value static system pages (like Homepage, Contact, About, Terms). I find it best to route all static links to an independent static.xml sitemap file. This keeps your clean content isolated and simplifies troubleshooting when debug processes highlight indexing errors.
