---
title: "Change large tables in small batches · Agent Wranglers"
url: https://agentwranglers.se/playbook/scaling/growing-the-database/small-batches
updated: 2026-10-21
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Change large tables in small batches

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers). Updated 2026-10-21.

- ID: SCL-27
- Level: Required
- Chapter: [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database)
- Rule: Update, delete or copy many rows in batches of at most 10,000, each in its own short transaction, with a pause between them. Never touch millions of rows in one statement.
- Risk: The app down or unusable
- How: One huge `UPDATE` holds locks for minutes, bloats the table and leaves replicas far behind. Walk the table by primary key, as [Page through long lists by key](https://agentwranglers.se/playbook/data/queries-and-indexes/keyset-pagination) does, and commit each batch. Pause while replica lag is over its limit. Save the last key, so the job resumes there after a crash. Backfills for [Expand first and contract later](https://agentwranglers.se/playbook/data/migrations/expand-and-contract) and archive moves use this rule.
- Check: Every data migration and job that changes rows uses batches of 10,000 rows or fewer per transaction. Run the backfill on a production-sized copy. No request waits longer than the lock timeout, and replica lag stays under its limit.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### JavaScript example

```js
const BATCH_SIZE = 5000;
const PAUSE_MS = 200;

export async function backfillCurrency(db) {
  let lastId = (await db.oneOrNone('SELECT last_id FROM backfills WHERE name = $1', ['currency']))?.last_id ?? 0;
  for (;;) {
    const rows = await db.any(
      `UPDATE orders SET currency = 'EUR' WHERE id IN (
         SELECT id FROM orders WHERE id > $1 AND currency IS NULL ORDER BY id LIMIT $2)
       RETURNING id`, [lastId, BATCH_SIZE]);
    if (rows.length === 0) return;
    lastId = Math.max(...rows.map(row => row.id));
    await db.none('INSERT INTO backfills (name, last_id) VALUES ($1, $2) ON CONFLICT (name) DO UPDATE SET last_id = $2', ['currency', lastId]);
    await new Promise(resolve => setTimeout(resolve, PAUSE_MS));
  }
}
```

### Python example

```python
import time

BATCH_SIZE = 5000
PAUSE_SECONDS = 0.2

def backfill_currency(conn) -> None:
    row = conn.execute("SELECT last_id FROM backfills WHERE name = 'currency'").fetchone()
    last_id = row[0] if row else 0
    while True:
        with conn.transaction():
            ids = [r[0] for r in conn.execute(
                """UPDATE orders SET currency = 'EUR' WHERE id IN (
                     SELECT id FROM orders WHERE id > %s AND currency IS NULL ORDER BY id LIMIT %s)
                   RETURNING id""", (last_id, BATCH_SIZE))]
            if not ids:
                return
            last_id = max(ids)
            conn.execute(
                "INSERT INTO backfills (name, last_id) VALUES ('currency', %s) "
                "ON CONFLICT (name) DO UPDATE SET last_id = EXCLUDED.last_id", (last_id,))
        time.sleep(PAUSE_SECONDS)
```

## Related rules

- [Spread scheduled work across the hour](https://agentwranglers.se/playbook/scaling/background-jobs-and-queues/spread-scheduled-jobs) in Scaling
- [Move finished rows out of hot tables](https://agentwranglers.se/playbook/scaling/growing-the-database/archive-old-rows) in Scaling
- [Page through long lists by key](https://agentwranglers.se/playbook/data/queries-and-indexes/keyset-pagination) in Data
- [Expand first and contract later](https://agentwranglers.se/playbook/data/migrations/expand-and-contract) in Data

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/scaling/growing-the-database/small-batches">Agent Wranglers scaling playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Move finished rows out of hot tables](https://agentwranglers.se/playbook/scaling/growing-the-database/archive-old-rows)
- Next: [Splitting one database across servers too early](https://agentwranglers.se/playbook/scaling/growing-the-database/early-sharding)
- [Growing the database](https://agentwranglers.se/playbook/scaling/growing-the-database)