cubic
Try cubic for free

Batch Large Backfills and Deletes in PostgreSQL

Flags single UPDATE or DELETE statements over millions of PostgreSQL rows in migrations and deploy steps, and batch loops that never commit.

Catch this in your code

What it flags

Flaggedmigrations/20260918_orders_region.sql
BEGIN;

SET LOCAL lock_timeout = '3s';

ALTER TABLE orders ADD COLUMN region text;

-- Updates every row in one transaction while the ALTER TABLE lock is held
UPDATE orders o
SET region = c.region
FROM customers c
WHERE c.id = o.customer_id;

COMMIT;
Passesscripts/backfill_orders_region.sql
-- Run with psql after the migration that adds orders.region, outside a transaction
DO $$
DECLARE
  batch_size constant bigint := 5000;
  next_id bigint;
  max_id bigint;
BEGIN
  SELECT min(id), max(id) INTO next_id, max_id FROM orders;

  WHILE next_id <= max_id LOOP
    UPDATE orders o
    SET region = c.region
    FROM customers c
    WHERE c.id = o.customer_id
      AND o.id >= next_id
      AND o.id < next_id + batch_size
      AND o.region IS NULL;

    next_id := next_id + batch_size;
    COMMIT;
    PERFORM pg_sleep(0.05);
  END LOOP;
END
$$;

Why it matters

A single UPDATE over millions of rows writes a new version of each row and holds each row lock until commit, so application writes to those rows wait for the whole statement. The old row versions cannot be vacuumed until the transaction ends, the long transaction keeps vacuum from removing dead rows elsewhere in the database, and the burst of WAL can make replicas fall behind. If the statement fails near the end, all of its work rolls back. On a development database the same statement finishes at once, so nothing in review or CI hints at the problem.

Agent instructions

cubic checks every pull request against these instructions. Paste them into AGENTS.md or Cursor rules and your AI coding tool follows them while it writes code.

AGENTS.md

Add this section to AGENTS.md at the repository root. Codex, Cursor, and other AI coding tools read it.

## Batch Large Backfills and Deletes in PostgreSQL

Applies to `**/*.sql`, `**/migrations/**`, `**/migrate/**`.

Review PostgreSQL migrations, deploy scripts, and one-off data scripts for UPDATE and DELETE statements that touch millions of rows in one transaction.

Flag changes that:
- Run a single `UPDATE` or `DELETE` over most or all of a large table, such as a backfill with no `WHERE` clause or a retention delete on `created_at < now() - interval '90 days'`, in a migration or deploy step.
- Backfill in the same transaction as `ALTER TABLE` on that table, so the ACCESS EXCLUSIVE lock stays held until the whole backfill commits.
- Batch inside one transaction, such as a PL/pgSQL loop in a function (functions cannot `COMMIT`) or a loop that never commits, which keeps every row lock and old row version until the end.
- Page through batches with `OFFSET`, or with a filter like `WHERE col IS NULL LIMIT n` that no index supports, so each batch scans more rows than the last. Walk the primary key in ranges instead.
- Cannot resume after a failure: no condition that skips finished rows and no saved progress, so a rerun repeats non-idempotent changes such as `SET amount_cents = amount_cents * 100`.

Do not flag:
- Statements bounded to a small, known set of rows, such as a `WHERE id IN (...)` list or a small lookup table.
- Data changes on tables created earlier in the same migration.
- Backfills handed to a background job that already processes rows in committed batches.

Checks files matching **/*.sql, **/migrations/**, **/migrate/**.

More PostgreSQL agents

See all PostgreSQL code review standards

Run this agent on every pull request

cubic reviews every pull request against the agents you turn on and comments on the lines that break them.

Catch this in your code