Best approach for on-the-fly sitemap generation on large SaaS?

We're running a rapidly growing SaaS platform with millions of user-generated content pages, and our current sitemap strategy is buckling under the pressure. We need a robust, scalable solution for dynamic sitemap generation.

Our main pain point is that static sitemaps are simply unmanageable. With content being added and updated constantly, regenerating them manually or even via daily cron jobs leads to significant staleness and misses newly indexed content. We're hitting XML file size and URL limits regularly, requiring complex sitemap index management that's prone to errors.

• What we've tried:
• Initially, simple static XML files generated by a script, but updates were too slow.
• Moved to a cron-based system generating sitemaps from the database every few hours, but this became a significant performance bottleneck during generation, especially for our largest content types.
• Explored caching generated sitemap files, but ensuring cache invalidation for millions of URLs efficiently without race conditions or serving stale data is proving complex.
• Specific technical hurdles we're facing:
• Performance Impact: Generating a sitemap for millions of URLs (across multiple sitemap index files) can bring our primary database to its knees, impacting user experience.
• Memory & CPU: In-memory generation for such large datasets often exceeds available resources on our web servers.
• `lastmod` & `changefreq` accuracy: Dynamically determining `lastmod` for millions of pages, especially when child content updates affect parent pages, is challenging without excessive database queries.
• Scalability: How do we ensure this system scales linearly with content growth without requiring constant re-architecting?
• Error Handling: What's the best way to handle generation failures or timeouts gracefully for search engine crawlers?

We're looking for architectural patterns, specific technologies, or best practices that can handle an on-the-fly sitemap for a high-volume, dynamic SaaS platform. Should we offload generation entirely to a dedicated service? How do others manage the database load during these operations? What are the most efficient ways to serve these massive sitemap index files without impacting server performance?

Eagerly awaiting insights from those who've tackled similar large-scale on-the-fly sitemap challenges!