Planning Safe Diffs with Atlas schema apply

You changed three lines in schema.sql — a new column, a new index, a tightened constraint — ran atlas schema apply, and Atlas printed a plan and asked for approval. The plan looked plausible, so you approved it, and the new index was built with a plain CREATE INDEX that blocked writes to a forty-million-row table for ninety seconds. Nothing in the plan was wrong: it was the correct DDL for the desired end state. It was simply not the DDL a zero-downtime deploy needs. This guide sets up Atlas’s declarative workflow on PostgreSQL so the plan it produces is one you would have written yourself, and so the plan that reaches production is exactly the plan someone reviewed. It applies the model from Declarative Schema Management to one concrete tool.

What Happens Inside atlas schema apply Sequence between the engineer, Atlas, the disposable dev database and the production database. Atlas loads schema.sql into the dev database to normalise it, inspects production, computes the diff, prints the plan, and on approval executes the statements against production. What Happens Inside atlas schema apply Engineer Atlas CLI Dev DB (docker) Production schema apply --env prod --dry-run execute schema.sql normalised desired state inspect catalog (read-only) diff + apply diff policy plan: CREATE INDEX CONCURRENTLY … apply reviewed plan execute statements
Production is only read until you approve; the dev database is where the desired state is compiled, so its version must match production.

Symptom / Error Signatures

These are the signs that an Atlas plan is not yet safe to run unattended against a live database:

  • The plan contains CREATE INDEX or DROP INDEX without CONCURRENTLY on a table that takes writes.
  • The plan contains ALTER TABLE ... ALTER COLUMN ... SET NOT NULL or ALTER COLUMN ... TYPE on a large table, both of which scan or rewrite under ACCESS EXCLUSIVE.
  • The plan contains DROP COLUMN or DROP TABLE you did not intend — typically because a line was deleted from schema.sql during a merge.
  • On the very first run, the plan is non-empty even though nobody changed anything: Schemas are not synced followed by statements that only change a default’s formatting or a constraint’s name.
  • The apply fails with pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which means concurrent indexes were enabled but the plan was executed in a single transaction.

Root Cause Analysis

Atlas computes the plan from two inputs and one policy. The inputs are the inspected current state and the normalised desired state; the policy is the diff block in atlas.hcl, which controls which kinds of change are emitted and how. With no policy, Atlas emits the most direct DDL for each difference — which on PostgreSQL means blocking index builds and immediate drops.

Normalisation is the second source of surprise. Atlas executes your desired schema on the --dev-url database and inspects the result, so that int becomes integer, unnamed constraints get the engine’s generated names, and now() defaults are rendered identically on both sides. If the dev database is a different PostgreSQL major version or lacks an extension that production has, the two models differ in ways that have nothing to do with your change, and the plan fills with noise that reviewers learn to ignore.

Plan problem Cause Setting that fixes it
blocking CREATE INDEX no concurrent-index policy diff { concurrent_index { create = true } }
unexpected DROP TABLE line removed from schema.sql diff { skip { drop_table = true } }
noise on first run dev DB version or extensions differ dev = "docker://postgres/<same major>/dev" + extensions in schema
plan differs at apply time production changed after review apply the saved plan; fail on mismatch
Default Plan vs Policy-Guarded Plan Two panels showing the plan Atlas generates for the same schema change. Default policy: plain CREATE INDEX, DROP COLUMN legacy_code, and SET NOT NULL. With diff policy and staged desired state: CREATE INDEX CONCURRENTLY, no drop because drops are skipped, and a CHECK constraint added as NOT VALID. Default Plan vs Policy-Guarded Plan No diff policy CREATE INDEX idx_orders_region ON orders (region) ALTER TABLE orders DROP COLUMN legacy_code ALTER TABLE orders ALTER COLUMN region SET NOT NULL write stall + data loss Policy + staged desired state CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region) drop skipped: legacy_code still read by v41 ADD CONSTRAINT region_nn CHECK (region IS NOT NULL) NOT VALID no blocking locks
The same edit to schema.sql yields a very different plan once the diff policy is in place and the NOT NULL change is staged through a validated constraint.

Immediate Mitigation

1. Stop applying interactively. Until the policy is in place, generate plans with --dry-run only and apply them by hand after review. A dry run inspects production but executes nothing.

# Shell · engineer workstation or CI · read-only credentials are sufficient for --dry-run
atlas schema apply \
  --url "postgres://readonly:***@prod-replica:5432/app?sslmode=require" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/16/dev" \
  --dry-run

2. Match the dev database to production. Use the same major version and create every extension production uses at the top of schema.sql (for example CREATE EXTENSION IF NOT EXISTS pgcrypto;). Re-run the dry run; on an unchanged schema it must now report no changes.

3. Add the diff policy. Commit an atlas.hcl with concurrent index creation and drop skipping, as shown in the parent topic’s procedure. Concurrent index statements cannot run inside a transaction, so apply them without one — recent Atlas releases expose a transaction-mode flag for this; on older versions, apply index statements as a separate step:

# Shell · deploy job · migration role
# WARNING: --tx-mode none runs each statement separately; a failure leaves earlier statements applied.
atlas schema apply --env prod \
  --url "postgres://migrator:***@prod-primary:5432/app?sslmode=require&options=-c%20lock_timeout%3D2s" \
  --tx-mode none --dry-run

4. Split what the policy cannot fix. For SET NOT NULL and type changes, change schema.sql in stages: first add a CHECK (col IS NOT NULL) NOT VALID constraint, then validate it in a hand-written statement, then add NOT NULL to the desired state — at which point PostgreSQL 12+ uses the validated constraint and skips the scan. The pattern is detailed in adding NOT NULL via a CHECK constraint.

Permanent Fix / Long-Term Pattern

The stable workflow separates planning from applying and pins every input. In the pull request, CI runs atlas schema apply --dry-run against a read-only replica and posts the plan as a comment; reviewers approve that SQL. On merge, the deploy job regenerates the plan against the primary and refuses to continue if it differs from the reviewed one — production may have changed in between, and an unreviewed statement must never slip through. Then it applies with a lock_timeout in the connection options and --tx-mode none when concurrent index statements are present.

-- PostgreSQL · desired state excerpt (schema.sql) · compiled on the dev database, never run directly
-- WARNING: adding NOT NULL here without a validated CHECK constraint makes Atlas emit a scanning SET NOT NULL.
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region     text,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT region_nn CHECK (region IS NOT NULL) NOT VALID
);
CREATE INDEX idx_orders_region ON orders (region);
-- ROLLBACK PATH: remove the constraint and index lines; review the resulting DROP plan before applying.

Keep destructive changes out of the everyday path entirely. With skip { drop_table = true } and column drops caught by linting, removing structure becomes a deliberate change with its own pull request and an explicit override — the approach in preventing destructive changes in declarative diffs. When a change needs code and schema sequenced across releases, generate a versioned file from the diff instead of applying it declaratively, as in combining declarative diffs with versioned migration files.

Plan Review to Apply, With No Gaps Pipeline. The pull request generates a dry-run plan; a gate checks the plan for blocking or destructive statements; human review approves the plan; on merge, a gate checks that the regenerated plan equals the reviewed plan; then the apply runs with lock_timeout. Plan Review to Apply, With No Gaps PR dry-run plan.sql posted lint blocking/drops? Human review approve the SQL same? plan unchanged? Apply tx-mode none, lock_timeout request staged change re-plan and re-review fail
Two gates matter most: the plan is linted before review, and the plan applied must be byte-for-byte the plan that was approved.

Verification Checklist

Frequently Asked Questions

Does atlas schema apply --dry-run take any locks on production? It only inspects the catalog with ordinary read queries, which take ACCESS SHARE locks on catalog tables for milliseconds. It does not lock your application tables and executes nothing, so it is safe to run against a replica or the primary.

Why must the dev database match production’s major version? Atlas compiles the desired schema on the dev database and compares what the engine produced there with what production reports. Different major versions render some types, defaults and generated names differently, which creates spurious differences in every plan and trains reviewers to skim.

Can Atlas rename a column without losing data? A plain diff treats a rename as a drop and an add. Perform renames through expand and contract instead: add the new column, dual-write and backfill, switch reads, then remove the old column from the desired state in a later change.

What does running the plan without a transaction change? Each planned statement commits on its own instead of inside one wrapping transaction. That is required for CREATE INDEX CONCURRENTLY, but it means a failure partway leaves earlier statements applied. Keep such plans small, and rely on the post-apply diff to show exactly what remains.

Related Articles