← All writing

Exporting a huge product catalog without taking the site down

Moving a whole product catalog from one store to another sounds like a single job: read every product, transform it, send it. On a large catalog that single job is exactly what takes the site down. I learned this on a big Interspire store, and the fix applies to any platform.

Why the one-shot export fails

A single request that loops over every product runs into every limit at once: PHP's memory_limit, max_execution_time, the web server's timeout, and sometimes the destination's rate limits. When it dies, it usually dies without saying how far it got, so the only option is to run it again and hit the same wall.

1. Work in batches

Process a few hundred products per request instead of all of them. Page through the table by ID rather than with OFFSET, which gets slower the further you go and can skip or repeat rows if the table changes mid-run:

$stmt = $db->prepare('SELECT * FROM products WHERE id > ? ORDER BY id LIMIT 200');
$stmt->execute([$lastId]);

Each batch finishes well inside the server's limits, so the store keeps serving customers while the export runs.

2. Save a checkpoint after every batch

Store the last ID you finished, in a table or a file, after each batch. If anything stops the run, it starts again from that ID instead of from zero. A failure now costs one batch, not the whole export.

foreach ($stmt as $product) {
    exportProduct($product);
    $lastId = $product['id'];
}
saveCheckpoint('catalog_export', $lastId);

Make the write on the destination an upsert keyed by SKU, so a batch that runs twice updates products instead of duplicating them.

3. Retry, then move on

Some products take far longer than others: huge descriptions, dozens of variants, slow image downloads. Retry a failed batch a couple of times. If one product keeps failing, log it and skip it, so a single bad record can't stop the other thousands.

4. Show progress

A long job behind a screen that never changes looks broken. People refresh it or close the tab, which is the worst thing they can do mid-run. Drive the batches from the page and show a counter:

async function run() {
  let failures = 0;
  for (;;) {
    const res = await fetch('/export-batch.php', { method: 'POST' })
      .then((r) => r.json())
      .catch(() => null);
    if (!res) {
      if (++failures > 3) return showError('Export paused. Run it again to continue.');
      await new Promise((r) => setTimeout(r, 3000));
      continue; // the checkpoint means this retries the same batch
    }
    failures = 0;
    counter.textContent = `${res.done} of ${res.total} products`;
    if (res.finished) return showDone();
  }
}

The same structure works from a cron job or a queue worker if nobody needs to watch it.

The takeaway

Any job that touches a whole catalog should assume it will fail partway and be built to continue rather than restart. And when something takes time, showing progress matters almost as much as being fast.

You can read the full story in the case study. If you're facing something similar, here's how I can help.