How We Generate 100+ Product Feeds From 300k SKUs Without Hitting the Database
The article details how the team at a PrestaShop-based e-commerce company generates over 100 product feeds (10 feed types × 4 languages × 3 shops) for 300,000 SKUs without querying the database during feed generation. The naive approach—loading each product from PrestaShop, computing price, checking availability, and formatting—required ~80 database queries per product, resulting in hours of generation time per feed due to network latency and clustered database overhead. The author tried a 'proper' engineering approach (likely caching or query optimization) but found it failed. Instead, they built a solution that dumps all necessary product data (prices, stock, attributes, etc.) into memory using a single batch query, then processes feeds in PHP without further database hits. This reduced generation time from hours to minutes. The key insight is that PrestaShop's runtime computations (price rules, group discounts, tax rules, stock management) are complex and cannot be easily replicated in raw SQL, so preloading all data into memory avoids repeated database calls.
Reduces feed generation time from hours to minutes, enabling faster updates and lower database load.