Changing a Schema in Production

-- Index without blocking writes. CONCURRENTLY cannot run inside a transaction
-- block, so most migration frameworks need this marked as non-transactional.
CREATE INDEX CONCURRENTLY orders_amount_minor_idx ON orders (amount_minor);

-- A failed CONCURRENTLY build leaves an INVALID index behind that still costs
-- writes. Check for it, then drop and retry.
SELECT c.relname FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS orders_amount_minor_idx;

-- Constraints in two steps. NOT VALID checks new and changed rows only, and
-- takes a brief lock; VALIDATE then scans existing rows without blocking writes.
ALTER TABLE orders ADD CONSTRAINT orders_amount_minor_nonneg
  CHECK (amount_minor >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_minor_nonneg;

-- NOT NULL the same way: a validated CHECK lets PostgreSQL 12+ set the column
-- NOT NULL without a full table scan.
ALTER TABLE orders ADD CONSTRAINT orders_amount_minor_present
  CHECK (amount_minor IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_minor_present;
ALTER TABLE orders ALTER COLUMN amount_minor SET NOT NULL;

-- Always bound how long a DDL statement may wait for its lock. Without this, a
-- migration queues behind one long transaction and every later query queues
-- behind the migration.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

// Moving a table to a different store is the same three phases, except the two
// writes cannot share a transaction. Order matters, and so does which failure
// you allow.
const MODE = process.env.SESSION_STORE_MODE; // old | dual | dual-verify | new

async function putSession(oldStore, newStore, session) {
  if (MODE === 'old') return oldStore.put(session);

  // MODE === 'new': the old store is gone from the write path, so return before
  // touching it. Reaching this means the mismatch rate below has been zero for
  // an agreed period.
  if (MODE === 'new') return newStore.put(session);

  // dual and dual-verify: source of truth first. If it fails, nothing is
  // written anywhere.
  await oldStore.put(session);

  try {
    await newStore.put(session);
  } catch (err) {
    // The new store is not authoritative yet, so its failure must not fail
    // the request. Count it: a rising rate here means do not cut over.
    metrics.increment('session.shadow_write_failed', { reason: err.code });
  }
}

async function getSession(oldStore, newStore, id) {
  if (MODE === 'new') return newStore.get(id);

  const authoritative = await oldStore.get(id);

  if (MODE === 'dual-verify') {
    // Shadow read: compare, report, and still return the old answer. This is
    // the evidence that justifies the cutover, and it is cheap to leave on.
    newStore
      .get(id)
      .then((shadow) => {
        const same = JSON.stringify(shadow) === JSON.stringify(authoritative);
        metrics.increment(same ? 'session.shadow_match' : 'session.shadow_mismatch');
      })
      .catch(() => metrics.increment('session.shadow_read_failed'));
  }
  return authoritative;
}