Dynamic sitemap: why a sitemap goes stale and what it breaks
A static sitemap.xml is a snapshot of the catalog at build time. While the file sits in the root, the catalog lives its own life: products appear and disappear, prices change, new categories open, some URLs return 404 or a redirect.
A search bot arrives on schedule and sees a set of URLs that no longer exists, while new pages simply were not in it. The more active the store, the more the file and reality diverge.
The cost of the divergence is measurable. A new product that did not make it into an online store's sitemap waits weeks to be crawled — all that time it does not appear in search results, and the traffic that could have gone to the product page goes to competitors.
Deleted items remain in the file and pull the crawl budget onto themselves: the bot spends its crawl limit on ghost pages instead of the live assortment.
If the sitemap is generated manually or on a once-a-day schedule, the gap only grows — when exporting thousands of SKUs, a daily delay means part of the catalog is not indexed at all.
A separate problem is format limits. A single file holds a limited number of URLs, so a large catalog requires splitting into several sitemaps and an index file that links to them.
A hand-assembled set quickly loses consistency: some sitemaps are not updated, some link to outdated sections, and the search engine, receiving contradictory signals, lowers its trust in the source.
A dynamic sitemap solves this differently: URLs are generated by a query to the database at the moment the bot requests them, so the sitemap always reflects the current state of the catalog. Below we break down how this works and which bottlenecks it closes.
What generating a sitemap from a database gives you
A static sitemap file lives separately from the catalog: a product was added, a price was changed, an item was taken off sale — yet the file still holds the old set of URLs. A search bot comes to it, hits non-existent pages, and reduces the crawl frequency of the section.
Generating from a database removes this gap: the sitemap is assembled by a query to the catalog, not by hand by a content manager.
Indexing speed here is a consequence of regularity. When the sitemap is rebuilt on a schedule or on a record-change event, a new URL enters it in the same cycle, and the bot picks it up on its next crawl rather than after the next release.
The share of indexed product pages grows not because we "asked" the search engine, but because it stops spending its crawl on broken and duplicate links.
Sitemap freshness rests on selection rules, not on the editor's discipline. Only published items with non-empty stock or an available pre-order make it into the selection, duplicate pages are merged via the canonical URL, and items taken off sale leave the sitemap immediately.
Automatic updating also covers the routine: edits during a mass category rename or a change in URL structure no longer turn into a separate week-long task.
A side effect, but a tangible one, is the reduced load from manual edits. The editor focuses on content rather than reconciling URLs, and the sitemap always reflects what actually sits in the database. Let's break down how this is set up and what needs to be provided for in the catalog.
Ways to serve a sitemap: file, stream, splitting by sections
Serving a sitemap is a trade-off between data freshness, database load, and how quickly a search engine crawler traverses the catalog.
A static file is served instantly and doesn't touch the database, but it goes stale between rebuilds: a new product appears in the catalog, yet it's not in the sitemap yet.
On-demand generation always serves an up-to-date set of URLs, but every crawler pass is a database query, and on a catalog with tens of thousands of items it must fit within the allotted time.
| Option | When it fits | Limitations |
|---|---|---|
| Static file | Catalog changes rarely, up to a few thousand URLs | Requires a rebuild on every update, lags behind the data |
| On-demand generation | Frequent changes, products and prices update constantly | Database load on every crawl pass, caching needed |
| Splitting into sitemaps by section | Large catalog, tens of thousands of URLs | Requires an index file and section-splitting logic |
Sitemap limits dictate the very need for splitting: a single file holds up to 50,000 URLs and up to 50 MB, so a large catalog physically doesn't fit into one sitemap.
The sitemap is then split by catalog sections — categories, products, articles — and a root index file links to each part. The crawler traverses the sections independently, and updating one of them doesn't force a rebuild of the entire sitemap.
In practice, these options are combined: heavy and rarely changing sections are served statically, while product sections are generated on demand with application-side caching. Caching takes the load off the database, and splitting keeps each part within the limits.
Such a hybrid delivers up-to-date data without peak load on the catalog at the moment of a crawl pass.
How we build generation: from database audit to update policy
Generating a dynamic sitemap starts not with code, but with a data inventory: which tables contain products, categories, brands, and articles, how entities are related, whether there are publication flags and modification dates.
This determines whether a genuinely accessible page makes it into the map or a broken link that the search engine will drop from the index.
- Database audit. We examine the table structure, relationships, record activity indicators, and the history of deletions and drafts. We determine which entities should even be included in the map and which are closed to indexing.
- Query design. We build queries for each group of pages separately so that a heavy catalog does not slow down the map delivery. We add filters by publication status and last modification date — this provides a correct
lastmod. - Delivery setup. The map is generated on the fly or cached and served at a stable address with the correct content type. For large catalogs, we split it into parts and link them with an index file so that the search robot retrieves data in portions.
- Completeness check. We compare the number of records in the map with the number of active pages in the database, catching duplicates, empty categories, and URLs with erroneous parameters. We separately verify that closed sections have not leaked into the output.
- Update policy. The map must be rebuilt when the catalog changes, not by a forgotten manual run. We set up event-driven rebuilding and periodic reconciliation so that new products appear in the index the same day.
- Handover and monitoring. We provide the client's team with a description of the logic, control points, and the procedure for handling failures. We add an alert if the map stops updating or returns an empty response.
This approach eliminates manual map assembly and keeps the index up to date without human involvement. If the catalog grows or the database structure changes, the policy is revised in a targeted way, without rewriting the entire logic.
What the client receives after implementation
After implementing a dynamic sitemap, the team is left not with "magic in the code" but with a set of artifacts that allow the system to be maintained without the author.
Generation stops being a black box: it is clear where the URLs come from, by what rule they get into the map, and what to do when the catalog structure changes.
- Generation configuration — a file with parameters: which entities get into the map, the limit of URLs per file, splitting into multiple maps, priorities and update frequency by page type. Edits are made without an application release.
- Table relationship schema — the map by which the generator assembles URLs: products, categories, brands, filters, regional versions. It is clear which field is responsible for the SEO-friendly URL and which for the publication flag.
- URL selection rules — which records are excluded: hidden, out of stock, duplicates, stub pages, technical sections. Separately — the rules for merging and priority for pages with identical content.
- Handling large catalogs — splitting into an index map and child files so that search engine robots fetch it in parts and do not hit the limit per single file.
- Documentation — a description of the selection logic, integration points with the database, and behavior when the catalog structure changes. Enough for a new engineer to figure it out without corresponding with the author.
- Update procedure — when the map is rebuilt: on a schedule, on a publication event, or during a bulk import. Plus the course of action if generation fails.
- Monitoring and logs — a report on the number of URLs in each map, alerts on a sharp drop or growth, build history for incident analysis.
- Acceptance checklist — a set of checks: the map is served at the required address, contains only public pages, is correctly split, and is updated after a product change.
Such a set removes the main risk — dependence on a single person. The catalog structure changes, but the selection rules and the update procedure remain a point of support: an edit can be made in the configuration without rewriting the generator.
Case study: how a catalog with tens of thousands of cards stopped losing addresses
An electronics online store with a catalog of about 40,000 active cards: some items sell out within weeks, new ones arrive in batches of several hundred.
The sitemap was assembled by a script once a day — the file was generated in its entirety, stored as a single document, and was only partially processed on the search engine's side.
According to crawl logs, up to half of the product addresses were not regularly making it into the queue, and new cards waited an average of 9–12 days for the robot's first visit.
Google Search Console confirmed the picture: the share of indexed cards hovered around 62%, and "Discovered, but not indexed" was growing faster than the catalog.
Marketing edited addresses manually — up to 300 edits per month went solely toward pushing new items into the crawl.
What was changed
A dynamic sitemap was assembled: an index file plus a breakdown by catalog sections, 5,000 addresses per document, with correct lastmod values from the database.
Generation happens on an event basis — a product appeared, changed its price, or was archived — rather than on a schedule. Archived and inactive cards drop out of the map immediately, and duplicates are closed off with canonical addresses.
Metrics before and after
| Metric | Before | After |
|---|---|---|
| Share of indexed cards | 62% | 94% |
| Time for a new product to appear in the crawl | 9–12 days | 1–2 days |
| Manual address edits per month | ~300 | isolated cases |
The load was also addressed separately: generation stopped being a heavy nightly process, and the map is served from cache and updated incrementally.
Two months after launch, the share of indexed cards remains above 90% even during peaks of new batch arrivals — the crawl keeps up with the speed of catalog updates.
How a dynamic sitemap differs from a static file
A static file is a pre-assembled list of URLs that sits in the site root and is served as is. As long as the catalog structure changes once a month, it works: the file is generated at release, gets included in the build, and requires no separate service.
The problem begins where URLs appear and disappear every day — a product card is taken off sale, a category has moved, an item's URL has changed. At that moment the static list lies: it serves search engines URLs that no longer exist and stays silent about new ones.
A dynamic sitemap is assembled on the fly from the database: a query takes active entities, applies visibility rules, and serves ready-made XML on request. The key difference is a single source of truth.
A page's URL exists exactly when a record exists in the database, and no separate synchronization between the catalog and the sitemap is needed.
This also solves the volume issue: if there are more URLs than the limit per file, the generator serves an index and splits the list into parts automatically, rather than by hand at every release.
Comparing the approaches comes down to the frequency of changes. When a static file is appropriate: landing pages, corporate sites, documentation — tens to hundreds of URLs, changing rarely, generation at release is cheaper than any service.
A dynamic sitemap is mandatory where the catalog is live: an online store, a marketplace, a listings database. Here a static file either goes stale between releases or requires a manual export that still lags behind reality.
From an engineering standpoint, the difference is also in the cost of an error. A static file is easy to check by eye and commit — but it knows nothing about a product added an hour ago.
A dynamic one requires caching and protection from load, but it serves an up-to-date picture on every crawl. For a store with daily deliveries, this is not a convenience but a condition for indexing new items.
Technical concerns: load, cache, duplicates and large catalogs
A dynamic sitemap is read by search engine robots more often than it seems: during active crawling, a request arrives every few minutes, and each time it is a database query.
If the map is built "on the fly" with a heavy join of catalog tables, on a large store this turns into regular peak load on the database — especially when the robot crawls the entire map rather than in parts.
Caching removes the issue: the ready map is served from cache, and rebuilding is triggered by an event — a product was added, a price was changed, an item was unpublished. Between rebuilds, serving the map does not touch the database at all.
For catalogs with tens of thousands of items, the map is split into parts by sections or by ranges, and an index file remains at the root — the robot fetches only the changed parts, not the entire list.
Will the load on the database increase during rebuilding?
Rebuilding runs in batches and in the background, not as a single heavy query: the database returns data in portions, with a pause between portions. The peak occurs at the moment changes are published, not on every robot crawl.
If the catalog is updated en masse — for example, by a nightly synchronization — rebuilding is queued after it, not in parallel.
What to do about duplicate URLs?
Duplicates are eliminated at the query level: only the canonical URL goes into the map, while pages with sorting, filter and pagination parameters are excluded by a rule. Products accessible via multiple paths serve a single URL, and the rest are marked as canonical.
This eliminates the situation where the robot crawls the same product via a dozen URLs and dilutes the page's weight.
What about products that are out of stock?
Such items do not drop out of the map abruptly — otherwise the robot sees a mass disappearance of URLs. They remain with an unavailability flag, and are removed from the map only after the product is discontinued for good. This way the map does not "flicker" on every warehouse change.
These three concerns — load, cache and duplicates — are resolved at the design stage of the generator, not after launch.
If the map is already served directly from the database and slows down the catalog, we move it to a cache with event-based rebuilding without rewriting the site.
Let's discuss your task and come back with an estimate and a plan
The conversation doesn't start with a brief, but with your current sitemap: how it's assembled now, how many URLs are in the index, how often the catalog changes, and what happens to products that have been taken off sale.
From these answers it becomes clear whether generation from the database is needed or whether a scheduled rebuild is enough — and exactly where the connection breaks between the data and what the search engine sees.
Then we go through the standard engineering path, and at every step you see what is being done and why:
- We capture the current state: sitemap export, the share of pages returning errors, response speed under peak load, behavior when the database is unavailable.
- We agree on the sources: which tables and views we treat as the source of truth, how we mark active, hidden, and deleted items, what goes into the sitemap and what is closed from indexing.
- We design the generation: splitting into files by section, the link limit per file, caching of the finished result, invalidation when the catalog changes.
- We implement the generation on a test environment: we verify the output against the database line by line, check behavior during a mass update and when adjacent services fail.
- We roll it out to the production environment: switching without downtime, error monitoring, alerting if the sitemap stops updating or returns an empty response.
- We hand over operations: the update procedure, control points, instructions for your team — what to do in case of a failure and how to roll back to the previous version.
- Post-launch support: we monitor the share of indexed pages, respond to a growing number of excluded URLs, and refine the rules when the catalog changes.
To come back with an estimate and a plan, we need a minimum: the site address, the approximate number of products and sections, what the database runs on, and how often the assortment changes.
Describe the task within this scope — we'll look at the current sitemap, and give you the timelines for the stages and the order of work.
