Change large tables in small batches

How to do it

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 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 and archive moves use this rule.

Examples

JavaScript example

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

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)

How to 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.

Related rules

• Spread scheduled work across the hour in Scaling
• Move finished rows out of hot tables in Scaling
• Page through long lists by key in Data
• Expand first and contract later in Data