Intermittent `crawl budget` exhaustion when generating large XML sitemaps for dynamic content

hey folks,

i'm hitting a wall with our 'Free XML Sitemap Generator' tool, specifically when dealing with extremely large, dynamically changing datasets. we're talking millions of URLs.

• the core issue: for smaller sites, it's a breeze. but once we push past a certain threshold (around 500k-1M URLs), we start seeing intermittent processing failures. it's not always a hard crash, sometimes it just hangs, leading to incomplete sitemaps and, i suspect, inefficient `crawl budget` usage for our users. this really impacts effective sitemap generation for our power users.
• what i've already tried:
  • implemented aggressive chunking and file splitting (sitemap index files are generated correctly).
  • optimized database queries for fetching URLs to be as lean as possible, using cursors where appropriate.
  • increased PHP memory limits, execution times, and even tried async processing with queues for the actual generation part.
  • server-side, we've scaled up resources (RAM, CPU) significantly.
• the peculiar symptom: the errors aren't always consistent. sometimes a 2M URL sitemap generates fine, other times a 1.5M URL one times out. it feels like a resource contention or a subtle race condition i'm missing. i've observed things like this in the logs:
  

  [2023-10-27 14:35:01] CRITICAL: System.OutOfMemoryException: 'Memory limit exhausted' (simulated, specific error varies)
  [2023-10-27 14:35:02] WARNING: Generation timed out for chunk X. Retrying...
  [2023-10-27 14:35:05] ERROR: Unhandled Promise Rejection: Max heap size exceeded.

• looking for: deeper architectural patterns or specific performance tuning advice for PHP/NodeJS (we're experimenting with both backends) that can handle truly massive, on-the-fly XML sitemap generation without these intermittent resource exhaustion issues. how do you guys manage this at scale without impacting `crawl budget` efficiency?

thanks in advance!