Add a NOT NULL column to a large table without locking it

system

You are a database engineer. Reply with SQL and brief comments only — no essay.

user

Table `orders` has ~50 million rows and takes writes continuously.

Add a column `status text NOT NULL DEFAULT 'pending'` and backfill existing
rows, without holding a long lock or taking the service down. Postgres 15.

Write the migration.

Checked for the constructs that make this safe, and for absence of the one that makes it dangerous. Several correct spellings exist, so exact text is not required.

• Adds the column and the constraint in separate steps rather than one ALTER
• Backfills in batches rather than a single unbounded UPDATE
• Uses NOT VALID then VALIDATE CONSTRAINT, or an equivalent non-blocking path
• Does not issue a bare `ALTER TABLE ... ADD COLUMN ... NOT NULL` with backfill in one step
• Any index is created CONCURRENTLY

Rules, in order

adds_the_column
  must match  /alter\s+table\s+["`]?orders["`]?\s+add\s+column/i
  The migration must actually add the column.

batched_backfill
  must match  /(limit\s+\d|loop|batch|where[^;]*\bid\b[^;]*<)/i
  A single unbounded UPDATE over 50M rows holds locks and bloats WAL. The backfill must be bounded.

non_blocking_constraint
  must match  /(not\s+valid|validate\s+constraint)/i
  Adding NOT NULL directly forces a full table scan under an exclusive lock. NOT VALID followed by VALIDATE CONSTRAINT avoids it.

no_blocking_not_null_add
  must NOT match  /add\s+column[^;]*\bnot\s+null\b/i
  Adding the column NOT NULL in one statement is precisely the migration that takes the service down.

concurrent_index_if_any
  must NOT match  /create\s+index(?!\s+concurrently)/i
  A non-concurrent CREATE INDEX blocks writes for the duration.

A rubric can fail in two directions that reading it will not reveal: it accepts everything, so every model scores 1 and the probe measures nothing; or it rejects everything, so every model looks bad at a task that is fine. These fixtures are run through the real scorer by npm run probes:check and by the test suite. The correct answer must score 1, and every wrong answer must not.

-- 1. Add the column, nullable, with a default (instant in PG 11+)
ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';

-- 2. Backfill in batches so no single transaction is long-lived
DO $$
DECLARE
  done int;
BEGIN
  LOOP
    UPDATE orders SET status = 'pending'
    WHERE status IS NULL AND id < 1000000
    LIMIT 10000;
    GET DIAGNOSTICS done = ROW_COUNT;
    EXIT WHEN done = 0;
    COMMIT;
  END LOOP;
END $$;

-- 3. Add the constraint without a blocking full scan
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';

ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';
UPDATE orders SET status = 'pending' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';
UPDATE orders SET status = 'pending' WHERE status IS NULL LIMIT 10000;
ALTER TABLE orders ADD CONSTRAINT c CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT c;
CREATE INDEX idx_orders_status ON orders(status);

You should add the column as nullable first, then backfill in batches, then add the constraint as NOT VALID and validate it separately.

v2 · current

v2 — raised maxTokens by 1000 to leave room for model reasoning. Reasoning models spend output tokens thinking before they write; the v1 budget was sized for the visible answer alone, which starved them mid-thought and produced a truncated response the scorer read as a wrong answer. Found by the first live call, 10 Aug 2026: gpt-5-mini used 64 reasoning tokens to write one word. The prompt and rubric are unchanged — only the budget.

maxTokens

1900

temperature

0

curl https://openrouter.ai/api/v1/chat/completions \
  -H "Authorization: Bearer $OPENROUTER_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
  "model": "anthropic/claude-opus-5",
  "messages": [
    {
      "role": "system",
      "content": "You are a database engineer. Reply with SQL and brief comments only — no essay."
    },
    {
      "role": "user",
      "content": "Table `orders` has ~50 million rows and takes writes continuously.\n\nAdd a column `status text NOT NULL DEFAULT '\''pending'\''` and backfill existing\nrows, without holding a long lock or taking the service down. Postgres 15.\n\nWrite the migration."
    }
  ],
  "max_tokens": 1900,
  "temperature": 0
}'