Forem: Peter Y

How We Generate 100+ Product Feeds From 300k SKUs Without Hitting the Database

Peter Y — Fri, 15 May 2026 11:24:17 +0000

Generating product feeds (Google Shopping, Facebook, marketplaces) is a boring problem until your catalog has 300,000 SKUs. Then it becomes a nightmare.

The naive approach — load each product from PrestaShop, compute its price, check availability, format the output — hits the database with ~80 queries per product. Multiply that by 300k products, add network latency to a clustered database, and you're looking at hours of generation time. Per feed. And we have over a hundred feeds: 10 different feed types × 4 languages × 3 shops.

We tried the "proper" engineering approach first. It failed. Then I built something dumb and fast that actually works.

Why Feeds Are Hard in PrestaShop

You can't just dump product data with SQL queries. I mean, you technically can, but you'll regret it.

PrestaShop computes a lot of things at runtime. Product price depends on specific price rules, group discounts, cart rules, tax rules, country settings, and a dozen admin toggles. Availability depends on stock management mode, pack stock type, combination stock, and warehouse config. Even something simple like "product name" goes through language layers and shop context.

Reproducing all of that in raw SQL is reverse-engineering the entire PrestaShop business logic layer. Someone might pull it off, but it'll be fragile, it won't respect admin settings, and it'll break on every PrestaShop update.

So you're stuck loading products through PrestaShop's own objects. And that means PHP, and that means queries. Lots of them.

The "Proper" Solution That Failed

We hired a specialist to build a proper feed generation pipeline. His architecture:

Message broker for events
Enrichment service on Symfony
Separate PrestaShop instance for data hydration
Event-driven pipeline with object serialization

Five months later: nothing shipped. Not one working feed. The architecture was theoretically sound but practically impossible to finish. We let him go.

The Insight

Here's what I realized. We already have a process that loads every product through PrestaShop's full business logic: our product update cron.

In our setup, product changes arrive from an external source via a Redis queue. A cron job runs every 30 seconds, picks up SKUs that changed, and performs a full product update in PrestaShop. After the update, all the expensive stuff is already computed — prices, stock, categories, attributes, descriptions, tax rules. It's all sitting right there in PHP memory.

What if, at that exact moment, we just... grabbed it?

And it gets better. At that same point in the code we're already updating the Elasticsearch search index for the storefront — because the data is the same. So we're already building a product object for search. Adding a second write for feed data is almost free.

The Solution: Capture Once, Feed Forever

During the product update cycle, after all business logic has executed, we collect every field that any feed might ever need into a single associative array. Price, stock, description, categories, attributes, images, EAN, weight, shipping — everything.

Then we:

json_encode it
gzencode it (compress)
base64_encode it (for safe storage)
Write it to Elasticsearch as one document per SKU

The Elasticsearch index is dead simple: SKU as the ID, compressed payload, a timestamp, and an "indexed" flag. One document per product. ~4KB per document compressed. The whole index for 300k products is about 4GB.

No extra database queries. No separate pipeline. The data piggybacks on a process that was already running.

Feed Generation: Just Scroll and Write

When it's time to generate feeds, the process is trivial:

Open an Elasticsearch scroll query over the entire index
For each document: decompress → json_decode → you have all product data
Write to whatever format the feed needs (CSV, XML, JSON)

The key trick: we write all languages and all shops in a single pass. One scroll through 300k documents, and we're writing to all output files simultaneously. No need to iterate the catalog multiple times.

Numbers

216,000 active SKUs
One feed (all languages, all shops): 6 minutes
All feeds combined: ~35 minutes
Write speed: ~500 SKUs/second
Database load during feed generation: zero

For comparison: the naive approach (load each product via PrestaShop objects) would need ~80 queries per SKU. That's 24 million queries for one feed. On a clustered database with network latency — we're talking hours per feed.

Adding New Feeds Takes Minutes

This is the part I'm most happy with.

The compressed JSON in Elasticsearch contains a superset of all fields any feed could need. When a manager says "we need a new feed for marketplace X," I don't build a new data pipeline. I just:

Look at what fields the marketplace requires
Check if they're in the universal object (they usually are)
Write a simple transformer: read field A, format it as column B
Add it to the feed generation cron

A new feed type takes maybe an hour. Most of that is reading the marketplace's spec.

If a required field isn't in the universal object yet — I add it in the product update step, wait for one full update cycle, and it's available everywhere.

Why This Works (And What It's Actually Called)

After building this, I looked up whether the approach has a name. Turns out it's a combination of two well-known patterns — I just didn't know that when I built it.

Materialized View — a precomputed, stored result of a complex query, optimized for reading. That's exactly what our compressed JSON in Elasticsearch is: a materialized view of PrestaShop's business logic output. The classic version lives in a database (PostgreSQL has them built-in, for example). Ours lives in Elasticsearch because the "query" isn't SQL — it's the result of PHP-level computations that can't be expressed in SQL at all.

Event-Carried State Transfer — a pattern from event-driven architecture where instead of telling consumers "something changed, go fetch the data yourself," you send the full state along with the event. That's exactly what we do: when a product updates, we don't just flag it for later processing. We capture the complete product snapshot right there and store it. Feed generators never need to go back to the source.

The twist is that both patterns are usually discussed in the context of microservices and distributed systems. Nobody talks about applying them inside a PHP monolith to solve a feed generation problem. But that's what works.

The insight isn't architectural theory. It's practical: don't go get data when you can grab it while it's already in your hands.

Trade-offs

It's not perfect.

Feed data is only as fresh as the last product update cycle. If a product updates at 10:00 and feeds generate at 10:30, there's a 30-minute gap. For our B2B use case this is fine. For a flash-sale store it might not be.

The universal object can get bloated. Ours has maybe 40-50 fields per product. Not terrible, but it needs occasional cleanup.

You need the product update pipeline to begin with. If your products are edited manually in PrestaShop admin — this approach doesn't apply directly. You'd need to hook into PrestaShop's save events instead.

TL;DR

Don't generate feeds by loading products from the database. At scale, it's impossibly slow.
Don't build a separate data pipeline. It's months of work and probably won't ship.
Capture product data during your existing update process, when all business logic has already executed.
Store it compressed in Elasticsearch. One document per SKU, ~4KB each.
Generate feeds by scrolling Elasticsearch and writing files. Zero database load, 500 SKUs/second.
New feed types take an hour to add, not weeks.

Sometimes the best architecture is no architecture. Just write stuff down when you already have it.

Tags: prestashop, ecommerce, elasticsearch, performance

PrestaShop Behind a Load Balancer: What Breaks and How to Fix It

Peter Y — Fri, 08 May 2026 20:23:27 +0000

PrestaShop Behind a Load Balancer: What Breaks and How to Fix It

PrestaShop on a single server is fine. PrestaShop behind a load balancer with auto-scaling nodes — that's where the fun begins. The framework was never built for this, and it shows.

I've been running a B2B store on PrestaShop for several years now. ~300k SKUs, hundreds of orders a day, auto-scaling on GCP. Here's what went wrong and how we dealt with it.

800 Queries Per Product Page. Yes, Really.

A single product page in PrestaShop fires about 800 SQL queries. A category listing — around 2,000. Not a bug. That's just PrestaShop being PrestaShop.

On one server you stick Redis in front of it and forget about it. On multiple nodes — not so simple.

Cache Desync Between Nodes

PrestaShop caches query results locally. Redis, Memcached, whatever — it's per-node. So each node builds its own cache.

What happens:

Node A caches a product at 10:00
Node B caches it at 10:02
Admin updates the price at 10:05
Both nodes keep serving stale data with different prices

Obvious idea: shared Redis. Problem: network latency on every hit. At 800 queries per page, even 1ms extra per query gives you almost a second of overhead. Your "fast cache" is now slower than just hitting the database.

How We Actually Solved It

We didn't fight the architecture. We went around it.

Sticky sessions via Cloudflare. Visitor lands on a node, stays on that node for the session. No desync from the user's point of view.

Local Redis on each node, TTL 10 minutes. Fast, simple, no network hop. We talked to management: "product descriptions might be 10 minutes behind." They said fine. For a B2B catalog that doesn't change every minute, this is a non-issue.

Table blacklist. This is the part that actually matters. We built a list of tables that must never be cached — any SQL query touching these tables goes straight to the database.

The blacklist breaks down like this:

Carts and sessions — cart, cart_product, customer, guest, address. Changes on every click.

Orders and payments — orders, order_detail, order_history, payment gateway tables. You don't want stale financial data. Ever.

Stock — stock_available. Showing wrong availability is worse than a slow page.

Analytics — connections, page_viewed, log. Write-heavy, would trash the cache constantly anyway.

Third-party modules — anything that deals with orders, wishlists, promotions, discounts. Every time you install a new module, you check its tables and add them to the blacklist if needed. This never ends.

Configuration — configuration, configuration_lang. Admin changes a setting — it needs to work immediately.

The rule is dead simple: if the table changes more often than once per 10 minutes, it goes on the blacklist.

What It Gave Us

Second hit on a warm cache:

Product page: 800 → 200–300 queries (about 65% less)
Category listing: 2,000 → 600 queries (about 70% less)

No distributed cache. No invalidation logic. No latency overhead. Just sticky sessions, local Redis, and a blacklist.

Deploying Code When There's No Single Server

On one box it's git pull && clear cache. With N nodes the questions pile up. How does code get everywhere? What about shared files? What about PrestaShop's class cache?

Shared Files on NFS

Some stuff has to be shared — images, documents, uploads. An admin goes into backoffice, uploads a banner — that image lands on one node. Others don't see it. So we mount certain directories from NFS:

Product images and module media (banners, sliders, brand logos)
Documents, downloads, invoices
Upload directory
Logs

Everything else — PHP, vendor, compiled assets — lives on each node's local disk. You do not want to serve PHP from NFS. Trust me.

The Deploy Machine

We have a dedicated deploy box. It shares the NFS mount with web nodes. Deploy looks like this:

SSH into deploy machine
git pull
npm run build (compiles Bootstrap and friends)
rsync to all web nodes, excluding NFS directories
Cache dirs get wiped during rsync — nodes rebuild class maps on next request

The NFS directories are symlinked on each node — /img points to NFS, everything else is local. Rsync skips the symlinked paths.

What this gives you: code hits all nodes within seconds, PHP serves from local disk (fast), shared files are always in sync, and cache resets automatically. Good enough.

Modules: The Worst Part

This is where PrestaShop really punishes you for having infrastructure.

When you click "Install" on a module, it can:

Drop files into /override/, overriding core classes
Run arbitrary SQL — create tables, alter schema, insert rows
Copy assets all over the filesystem

No migrations. No rollback. It's a side-effect machine.

On multiple nodes this is a nightmare. You can't just commit a module and deploy — it needs its install hooks. You can't install via admin panel on prod — it only runs on one node.

Module Surgery

Every marketplace module goes through prep before it gets anywhere near production.

Strip the overrides. Modules come with an /override/ folder. We empty it. A developer manually reviews what the module wanted to override and merges it into the project's own /override/ directory by hand. Resolves conflicts, makes it a normal commit. Reviewable, reversible.

Why? Because if two modules try to override the same core method, you get a silent race condition. On a single server you might not notice. On multiple nodes with different cache states — good luck debugging that.

Review the install() method. Read what SQL it runs. Know what it'll do to your database before it does it.

Deploy the code first. Commit the cleaned module, git pull on deploy machine, rsync to all nodes. At this point every node has the module files but the module isn't installed yet.

Then hit Install in the admin panel. Since we stripped the overrides, the installer only does its database work — creates tables, registers hooks. Doesn't matter which node handles the request, the DB is shared and the code is already everywhere.

Rsync once more. PrestaShop rebuilds its class index after install. One more rsync pushes that to all nodes.

Tedious? Yes. But it works every time. And on production I'll take boring and reliable over clever and fragile.

Why Not Just Ditch PrestaShop?

Fair question after all of the above.

Because a €100 module from the marketplace replaces months of custom development. Mollie integration is free. Need gift cards? Wishlists? Complex discount rules? Someone already built it, battle-tested it on real stores, and sells it for the price of a dinner.

Try getting that on a custom headless build. You'll burn through your budget before you ship anything.

PrestaShop's value was never its architecture — the architecture is showing its age. The value is the ecosystem. For a small or mid-size business, solving commerce problems with off-the-shelf modules at these prices is hard to beat.

Yes, you pay for it with operational complexity when you scale. Everything in this article is that tax. But you're handling it with a small team and a sane budget, while companies on "enterprise" platforms throw 10x the money at consultants and ship slower.

That's the trade-off. If your team can handle the rough edges — and now you have a playbook — PrestaShop still makes a lot of sense.

TL;DR

PrestaShop on multiple nodes breaks in three places: cache, deployment, and modules.
Cache: sticky sessions + local Redis + table blacklist. 800 queries → 300. No distributed cache needed.
Deploy: dedicated deploy machine, rsync to nodes, NFS for shared files only. PHP always on local disk.
Modules: strip overrides, review SQL, deploy code first, install via admin second. Every time. No shortcuts.
PrestaShop's ecosystem is worth the operational pain — if you know how to manage it.

Struggling with a similar setup? Tell me.

Tags: prestashop, ecommerce, devops, scaling