Migration recipes

CREATE TABLE users (
  id    bigint PRIMARY KEY,
  email text NOT NULL  -- @renamed from=email_address
);

ALTER TABLE users RENAME COLUMN email_address TO email;

-- @renamed-table from=user_accounts
CREATE TABLE users (
  id bigint PRIMARY KEY,
  email text NOT NULL
);

ALTER TABLE user_accounts RENAME TO users;

-- 50M-row table; this locks for ~6 minutes
ALTER TABLE events ALTER COLUMN tenant_id SET NOT NULL;

-- 1. Add CHECK constraint NOT VALID — instantaneous, short AccessExclusive lock
ALTER TABLE events
  ADD CONSTRAINT chk_events_tenant_id_notnull
  CHECK (tenant_id IS NOT NULL) NOT VALID;

-- 2. VALIDATE the constraint — long scan but only ShareUpdateExclusive lock (non-blocking for writes)
ALTER TABLE events VALIDATE CONSTRAINT chk_events_tenant_id_notnull;

-- 3. SET NOT NULL — fast because PG 14+ sees the proven CHECK and skips the rescan
ALTER TABLE events ALTER COLUMN tenant_id SET NOT NULL;

-- 4. DROP the helper constraint — no longer needed
ALTER TABLE events DROP CONSTRAINT chk_events_tenant_id_notnull;

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

$ pg-flux migrate apply
Error: refusing to apply: blocking hazards; pass --allow-hazards or change schema

$ pg-flux migrate apply --allow-hazards=DATA_LOSS

-- Step 1: mark the column NOT NULL → nullable, deprecate via comment
ALTER TABLE users ALTER COLUMN legacy_phone DROP NOT NULL;
COMMENT ON COLUMN users.legacy_phone IS 'deprecated, will be removed in 0.4';

-- ... time passes, application stops writing to it ...

-- Step 2: drop in a later migration (with explicit --allow-hazards=DATA_LOSS)
ALTER TABLE users DROP COLUMN legacy_phone;

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Build the unique index concurrently (no AccessExclusive)
CREATE UNIQUE INDEX CONCURRENTLY users_email_unique
  ON users (email);

-- Attach the index as the backing index for a UNIQUE constraint (catalog flip, instantaneous)
ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique;

-- Step 1: add the new column as nullable, no rewrite
ALTER TABLE events ADD COLUMN event_time_new timestamptz;

-- Step 2: backfill in batches from application code
-- (not pg-flux territory — your worker, in chunks of 10k rows)

-- Step 3: switch reads to the new column in app code, deploy, validate

-- Step 4: drop the old column (with --allow-hazards=DATA_LOSS)
ALTER TABLE events DROP COLUMN event_time;
ALTER TABLE events RENAME COLUMN event_time_new TO event_time;

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;  -- now nothing can read documents until a policy permits
CREATE POLICY documents_read ON documents FOR SELECT TO authenticated USING (...);

-- Step 1: create the policies. Policies on non-RLS tables do nothing.
CREATE POLICY documents_read ON documents
  FOR SELECT TO authenticated
  USING (owner_id = current_setting('app.user_id')::bigint);

CREATE POLICY documents_admin ON documents
  TO admin
  USING (true)
  WITH CHECK (true);

-- Step 2: enable RLS (now the policies take effect)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- before:
CREATE TYPE status AS ENUM ('draft', 'review', 'published');

-- after — rename, don't drop+add:
CREATE TYPE status AS ENUM ('draft', 'in_review', 'published');

ALTER TYPE status RENAME VALUE 'review' TO 'in_review';

-- Step 1: create a new type with the right values
CREATE TYPE status_new AS ENUM ('draft', 'published');

-- Step 2: switch every column over (this WILL fail if any row has the removed value)
ALTER TABLE posts ALTER COLUMN status TYPE status_new USING status::text::status_new;

-- Step 3: drop the old type and rename
DROP TYPE status;
ALTER TYPE status_new RENAME TO status;

CREATE TABLE orders (
  id           bigint PRIMARY KEY,
  subtotal     numeric NOT NULL,
  tax_rate     numeric NOT NULL,
  tax_amount   numeric GENERATED ALWAYS AS (subtotal * tax_rate) STORED
);

-- Step 1: add the typed columns nullable
ALTER TABLE events ADD COLUMN user_id    bigint;
ALTER TABLE events ADD COLUMN action     text;
ALTER TABLE events ADD COLUMN timestamp_ timestamptz;

-- Step 2: backfill from the JSON (in your app, in batches)
UPDATE events SET
  user_id   = (payload->>'user_id')::bigint,
  action    = payload->>'action',
  timestamp_ = (payload->>'timestamp')::timestamptz
WHERE user_id IS NULL;

-- Step 3: enforce NOT NULL with the staged pattern (see "Add NOT NULL to a huge table")
ALTER TABLE events ALTER COLUMN user_id   SET NOT NULL;
ALTER TABLE events ALTER COLUMN action    SET NOT NULL;
ALTER TABLE events ALTER COLUMN timestamp_ SET NOT NULL;

-- Step 4 (later, when the app no longer reads payload): drop with --allow-hazards=DATA_LOSS
ALTER TABLE events DROP COLUMN payload;

-- Step 1: create the new partitioned parent
CREATE TABLE events_new (
  id          bigint NOT NULL,
  occurred_at timestamptz NOT NULL,
  ...
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2025 PARTITION OF events_new FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE events_2026 PARTITION OF events_new FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Step 2: backfill in your app, in batches
-- INSERT INTO events_new SELECT * FROM events WHERE occurred_at >= '...' LIMIT 10000;

-- Step 3: when caught up, swap (locking briefly)
BEGIN;
  ALTER TABLE events RENAME TO events_old;
  ALTER TABLE events_new RENAME TO events;
COMMIT;

-- Step 4: drop the old table when verified
DROP TABLE events_old;

ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view active_users depends on column "users.email"

-- target schema must exist
CREATE SCHEMA IF NOT EXISTS app;

-- move
ALTER TABLE public.users SET SCHEMA app;

pg-flux migrate generate --schemas=public,app