R007 · default CAUTION

UPDATE without WHERE

UPDATE without WHERE touches every row in the table, generating massive WAL and possible replication lag.

Why this is a problem

Common bug: forgetting the WHERE clause on a tenant-scoped or per-row update. Even when intentional (bulk backfill), the unbatched form blocks autovacuum and saturates replication.

Don't do this

UPDATE users SET tenant_id = 42;

Do this instead

-- Batch in 100K-row chunks, COMMIT between to yield to replication + vacuum
DO $$
DECLARE batch_size INTEGER := 100000; affected INTEGER;
BEGIN
  LOOP
    UPDATE users SET tenant_id = 42
    WHERE id IN (
      SELECT id FROM users WHERE tenant_id IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS affected = ROW_COUNT;
    EXIT WHEN affected = 0;
    COMMIT;
  END LOOP;
END $$;

References

• Strong Migrations: Backfilling data →