Struggling with Eloquent ORM performance for dynamic XML sitemap generation in large Laravel apps

hey everyone,

we're currently deep into developing a robust, dynamic XML sitemap generator for a pretty large-scale Laravel application. this isn't just a small blog, it's a platform with tens of thousands of records across various Eloquent models, and the goal is critical: real-time sitemap updates for optimal SEO. we need that fresh content indexed fast.

the core problem we're hitting is significant performance degradation and outright memory exhaustion when iterating through multiple Eloquent ORM models to build the sitemap. honestly, it's becoming unscalable past a certain data threshold, which is a major roadblock for our Laravel performance optimization efforts.

• Attempts & Failures:
• we've used chunk() and chunkById() extensively, which helps, but the overhead of hydrating many Eloquent models still accumulates, even in chunks. it just shifts the problem a bit.
• tried disabling model events and timestamp updates during the sitemap generation process, hoping to shave off some milliseconds, but the impact was minimal on the larger memory issues.
• implemented eager loading (with()) where applicable, which is a no-brainer for N+1, but certain models require complex relationships or custom query scopes that make it tricky to apply uniformly or efficiently without fetching too much.
• we've considered going down the raw DB query route, but losing the Eloquent flexibility for URL generation, especially with localized routes and dynamic slugs, is really undesirable and adds a ton of boilerplate.

the observed behavior is pretty consistent: the process times out or hits memory limits, especially when including models with soft deletes or complex, deeply nested relationships. we often see something like this in the logs:

[2023-10-27 10:34:12] local.ERROR: Allowed memory size of 268435456 bytes exhausted (tried to allocate 12345678 bytes) in /path/to/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Collection.php on line 123

this error often appears when attempting to collect or map a large number of model instances, particularly when building the final collection before serialization.

so, my specific question is: what are the most effective, battle-tested strategies for generating large, dynamic sitemaps using Eloquent ORM without consistently hitting memory limits or excessive execution times? are there specific Eloquent patterns, helper packages, or even architectural shifts for this exact scenario that you'd recommend? any advanced Laravel performance optimization tips for this specific use case would be super helpful.

anyone faced this before with high-volume Laravel applications and found a robust solution?