sql 60 lines · 3 tabs

SQL migration safety: add column nullable, backfill, then constrain

Shared by codesnips Jan 2026

3 tabs

-- Step 1: add the column nullable, no default.
-- Catalog-only change in Postgres 11+, returns instantly.
ALTER TABLE orders
  ADD COLUMN currency text;

-- Optional: keep the lock attempt bounded so a long-running
-- query can't make this migration hang indefinitely.
SET lock_timeout = '3s';

COMMENT ON COLUMN orders.currency IS
  'ISO 4217 code; nullable until backfill (03_constrain) completes';

-- Step 2: backfill NULL rows in small, committed batches.
-- Run outside a single wrapping transaction so COMMIT works.
DO $$
DECLARE
  batch_size int := 5000;
  updated    int;
BEGIN
  LOOP
    WITH todo AS (
      SELECT id
      FROM orders
      WHERE currency IS NULL
      ORDER BY id
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    )
    UPDATE orders o
    SET currency = 'USD'
    FROM todo
    WHERE o.id = todo.id;

    GET DIAGNOSTICS updated = ROW_COUNT;
    RAISE NOTICE 'backfilled % rows', updated;

    EXIT WHEN updated = 0;

    COMMIT;              -- release locks between batches
    PERFORM pg_sleep(0.1);  -- yield to production traffic
  END LOOP;
END $$;

-- Step 3: enforce NOT NULL without a long ACCESS EXCLUSIVE scan.

-- 3a: add the rule NOT VALID -> checks only new writes, no table scan.
ALTER TABLE orders
  ADD CONSTRAINT orders_currency_not_null
  CHECK (currency IS NOT NULL) NOT VALID;

-- 3b: validate existing rows under SHARE UPDATE EXCLUSIVE
-- (reads and writes stay unblocked).
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_currency_not_null;

-- 3c: on PG 12+, a validated CHECK lets SET NOT NULL skip its
-- own full scan; then the redundant CHECK can be dropped.
ALTER TABLE orders
  ALTER COLUMN currency SET NOT NULL;

ALTER TABLE orders
  DROP CONSTRAINT orders_currency_not_null;

3 files · sql Explain with highlit

Adding a NOT NULL column to a large, busy table in one statement is a classic way to cause an outage. In PostgreSQL, ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... can rewrite the whole table (older versions) and, more importantly, adding the constraint requires validating every existing row while holding an ACCESS EXCLUSIVE lock that blocks reads and writes. The safe pattern splits the change into three independently deployable steps: add the column nullable, backfill data in small batches, then add the constraint without a long lock.

The first tab, 01_add_column.sql, adds currency as a plain nullable text column. A bare ADD COLUMN with no default and no NOT NULL is a catalog-only change in modern Postgres, so it takes a brief lock and returns almost instantly regardless of table size. New writes from the application start populating the column immediately (the app is deployed to set it), while historical rows stay NULL for now.

The second tab, 02_backfill.sql, fills those NULL rows in bounded chunks. The loop uses a CTE that selects up to batch_size ids WHERE currency IS NULL, FOR UPDATE SKIP LOCKED so concurrent transactions never block each other, and updates only that slice. GET DIAGNOSTICS updated = ROW_COUNT drives the exit condition, and COMMIT inside the DO-style procedure keeps each batch's locks short-lived. Sleeping briefly between batches yields headroom to production traffic and lets autovacuum keep up. This is idempotent: re-running only touches rows still NULL.

The third tab, 03_constrain.sql, adds the guarantee cheaply. Rather than SET NOT NULL directly, it first adds a CHECK (currency IS NOT NULL) NOT VALID constraint, which skips scanning existing rows and only enforces the rule on new writes. A separate VALIDATE CONSTRAINT then scans the table under a weaker SHARE UPDATE EXCLUSIVE lock that permits concurrent reads and writes. On Postgres 12+, an equivalent validated CHECK even lets SET NOT NULL skip its own full scan.

The trade-off is more steps and a window where the column is nullable, so application code must tolerate NULL until step three completes. The payoff is that no single statement takes a table-wide blocking lock, making the whole change safe under load.

Related snips

typescript

export interface RetryOptions {
  retries: number;
  baseMs: number;
  maxMs: number;
  signal?: AbortSignal;
  onRetry?: (attempt: number, delay: number, err: unknown) => void;

Exponential backoff with jitter for retries

typescript reliability retry

by codesnips 2 tabs

package dbutil

import (
  "context"

  "github.com/jackc/pgconn"

Retry Postgres serialization failures with bounded attempts

go postgres transactions

by Leah Thompson 1 tab

ruby

require "csv"

class PeopleCsvStream
  include Enumerable

  HEADERS = %w[id full_name email signed_up_at plan].freeze

Resilient CSV Export as a Streamed Response

rails performance streaming

by codesnips 3 tabs

rust

use crossbeam::channel::unbounded;
use std::thread;

fn main() {
    let (tx, rx) = unbounded();

Crossbeam for advanced concurrent data structures

rust concurrency lock-free

by Marcus Chen 1 tab

javascript

// Creating a Promise
const myPromise = new Promise((resolve, reject) => {
  const success = true;

  setTimeout(() => {
    if (success) {

Promises and async/await patterns for asynchronous JavaScript

javascript promises async-await

by Alex Chang 1 tab

rust

use std::sync::mpsc;
use std::thread;

fn main() {
    let (tx, rx) = mpsc::channel();

Channels (mpsc) for message passing between threads

rust concurrency channels

by Marcus Chen 1 tab

Share this code

Here's the card — post it anywhere.

SQL migration safety: add column nullable, backfill, then constrain — share card

Link copied