Strategies for efficient sitemap optimization on deep, dynamic sites?

Following up on the memory exhaustion issues we discussed for large XML sitemap generation, we've largely mitigated the direct memory errors by chunking and optimizing the generation process. This was a crucial step.

However, the underlying challenge remains: our site is incredibly deep and dynamic, with millions of URLs. Even with chunking, performing direct database queries to fetch all relevant URLs for a full sitemap rebuild is becoming a significant performance bottleneck. It's not about memory crashing anymore, but about the sheer time it takes and the load it puts on our primary database during generation. We're talking hours, not minutes, for a full refresh, which is unsustainable.

I'm looking for more advanced architectural strategies for truly efficient sitemap optimization on such a scale. Beyond simple pagination or increasing server specs, what are some robust approaches for generating sitemaps for deep, dynamic sites with millions of URLs without excessive database load or multi-hour generation times? Are there patterns for distributed generation, intelligent caching, or incremental update mechanisms that don't involve a full re-scan every time?