> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reliantlabs.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Measure migration locks and table rewrites before production

> See which Postgres locks, table rewrites and full scans each pending migration causes, on a disposable database, before the migration reaches production.

By the end of this guide you will know, for each statement in your pending migrations, which locks it took, whether it rewrote a table, and whether it scanned every row while blocking writes. `reliant forge db analyze` applies the migrations to a disposable Postgres, so you find a table-locking migration on your machine instead of during a deploy.

The [migration lint](https://docs.reliantlabs.io/guides/forge/zero-downtime-postgres-migrations.md) reads SQL text. `db analyze` runs it: each file in its own transaction, the way forge's migrator applies it, then reads what Postgres reports.

## Before you begin

* A Forge project with a database, in a git repository. With no repository and no `--base`, the command exits 2:

  ```text theme={null}
  db analyze could not analyze: cannot find the merge-base with the remote default branch (no git repository, no default branch, or a shallow clone): pass --base <rev>
  ```

* Forge v1.7.0 or later (`reliant forge --version` inside the project). Nothing else: the default target is an embedded Postgres, with no docker.

**1. Choose the migrations to analyze**

`db analyze` migrates a scratch database with the migrations that already exist at a base revision, then analyzes the ones you added or changed since. By default the base is the merge-base with your remote's default branch, which is right on a feature branch. To analyze your uncommitted migrations, commit everything else and pass `--base HEAD`.

**2. Analyze a migration that locks the table**

Two common mistakes, in two new migration files: a column with a volatile default, and an index built without `CONCURRENTLY`.

```sql db/migrations/20261120070327_add_task_due_at.up.sql theme={null}
ALTER TABLE tasks ADD COLUMN due_at TIMESTAMPTZ DEFAULT clock_timestamp();
```

```sql db/migrations/20261120070328_index_tasks_owner.up.sql theme={null}
CREATE INDEX tasks_owner_idx ON tasks (owner);
```

```bash terminal theme={null}
reliant forge db analyze --base HEAD
```

```text theme={null}
forge db analyze — scratch target localhost:44559/forge_test_… (postgres 18.3), base HEAD@24e27eafee1f

db/migrations/20261120070327_add_task_due_at.up.sql
  line 12: ALTER TABLE tasks ADD COLUMN due_at TIMESTAMPTZ DEFAULT clock_timestamp(…
    3.7ms
    locks:    tasks ShareLock, tasks AccessExclusiveLock, tasks_pkey AccessExclusiveLock
    rewrote:  tasks (table)
    rewrote:  tasks_pkey (index)
    scanned:  tasks (2 seq scan(s), 0 rows)

db/migrations/20261120070328_index_tasks_owner.up.sql
  line 12: CREATE INDEX tasks_owner_idx ON tasks (owner)
    0.9ms
    locks:    tasks ShareLock
    scanned:  tasks (1 seq scan(s), 0 rows)

findings:
  warning …add_task_due_at.up.sql:12 [db-analyze-table-rewrite] rewrote tasks: every row is copied to new storage, and every index rebuilt, under ACCESS EXCLUSIVE
  warning …add_task_due_at.up.sql:12 [db-analyze-access-exclusive-lock] held ACCESS EXCLUSIVE on tasks, which blocks every read and write of it, while the statement rewrote it
  warning …index_tasks_owner.up.sql:12 [db-analyze-blocking-index-build] built index tasks_owner_idx on tasks without CONCURRENTLY: the build holds SHARE (or stronger), which blocks every write to tasks until it finishes
      warn: [db-analyze-blocking-index-build] is new in v1.7.0 and gates from v1.7.1; gate now with lint.rules: {db-analyze-blocking-index-build: error}

2 migration(s) analyzed, 2 statement(s) executed
```

Each finding also prints a `fix:` line and, while the rule is new, a `warn:` line; most are trimmed here. The scratch tables are empty, so the timings are not what production will see. The locks and rewrites are.

**3. Read the findings**

| Rule | Fires when |
| - | - |
| `db-analyze-table-rewrite` | A table that existed before the migration, or its indexes, got new storage |
| `db-analyze-access-exclusive-lock` | `ACCESS EXCLUSIVE`, which blocks reads and writes, was held across a scan or rewrite |
| `db-analyze-validation-scan` | A full scan ran under a lock that blocks writes |
| `db-analyze-blocking-index-build` | An index was built without `CONCURRENTLY` |
| `db-analyze-statement-timeout` | A statement exceeded `statement_timeout` |

New rules warn for one release before they gate, depending on the `forge_version` your project pins ([lint rollout](https://docs.reliantlabs.io/forge/linting/rollout.md)). To gate on them now, grade them in `forge.yaml`:

```yaml forge.yaml theme={null}
lint:
    rules:
        db-analyze-table-rewrite: error
        db-analyze-access-exclusive-lock: error
```

With that, the same run ends `db analyze: 2 finding(s) gate` and exits 1. Exit 0 means nothing gating; exit 2 means it could not analyze.

**4. Fix the migrations**

Add the column with no default, then set the default for new rows. Give the concurrent index build a migration file of its own, because it cannot run inside a transaction:

```sql db/migrations/20261120070327_add_task_due_at.up.sql theme={null}
ALTER TABLE tasks ADD COLUMN due_at TIMESTAMPTZ;
ALTER TABLE tasks ALTER COLUMN due_at SET DEFAULT now();
```

```sql db/migrations/20261120070328_index_tasks_owner.up.sql theme={null}
CREATE INDEX CONCURRENTLY tasks_owner_idx ON tasks (owner);
```

**5. Verify the locks are brief**

```bash terminal theme={null}
reliant forge db analyze --base HEAD
```

```text theme={null}
db/migrations/20261120070327_add_task_due_at.up.sql
  line 12: ALTER TABLE tasks ADD COLUMN due_at TIMESTAMPTZ
    0.6ms
    locks:    tasks AccessExclusiveLock
  line 13: ALTER TABLE tasks ALTER COLUMN due_at SET DEFAULT now()
    0.5ms

db/migrations/20261120070328_index_tasks_owner.up.sql
  line 12: CREATE INDEX CONCURRENTLY tasks_owner_idx ON tasks (owner)
    0.9ms
    locks:    tasks ShareUpdateExclusiveLock (known from the statement's kind)
    scanned:  tasks (2 seq scan(s), 0 rows)

2 migration(s) analyzed, 3 statement(s) executed
```

No findings and exit 0. `ADD COLUMN` still takes `ACCESS EXCLUSIVE`, but with no rewrite and no scan it holds it only for a catalog change. `SHARE UPDATE EXCLUSIVE` blocks neither reads nor writes.

**6. Make a column NOT NULL without a blocking scan**

`SET NOT NULL` scans every row under `ACCESS EXCLUSIVE`, and `db analyze` reports `db-analyze-access-exclusive-lock` and `db-analyze-validation-scan` for it. Spread it over three migrations, so Postgres can skip the scan:

```sql db/migrations/20261120063317_backfill_task_owner.up.sql theme={null}
UPDATE tasks SET owner = 'unassigned' WHERE owner IS NULL;
ALTER TABLE tasks ADD CONSTRAINT tasks_owner_not_null CHECK (owner IS NOT NULL) NOT VALID;
```

```sql db/migrations/20261120063318_validate_task_owner.up.sql theme={null}
ALTER TABLE tasks VALIDATE CONSTRAINT tasks_owner_not_null;
```

```sql db/migrations/20261120063319_require_task_owner.up.sql theme={null}
-- forge:allow-unsafe-not-null — tasks_owner_not_null, validated by the previous migration, proves no row is null
ALTER TABLE tasks ALTER COLUMN owner SET NOT NULL;
ALTER TABLE tasks DROP CONSTRAINT tasks_owner_not_null;
```

`db analyze` shows the validation under `ShareUpdateExclusiveLock` and the `SET NOT NULL` with no scan, and reports no findings. The pragma is there because `forge lint --migration-safety` checks each file alone and would otherwise fail the third file with `set-not-null-without-backfill`.

**7. Use the JSON in CI**

`--json` prints a `forge.db-analyze/v1` document with the same exit code. Each statement carries `locks`, `rewrites` and `scans`; `findings` and `summary` close it. One finding, without its `fix` and `note` fields:

```bash terminal theme={null}
reliant forge db analyze --base HEAD --json
```

```json theme={null}
{
  "rule": "db-analyze-table-rewrite",
  "severity": "warning",
  "file": "db/migrations/20261120070327_add_task_due_at.up.sql",
  "line": 12,
  "relation": "tasks",
  "message": "rewrote tasks: every row is copied to new storage, and every index rebuilt, under ACCESS EXCLUSIVE"
}
```

The summary of that run was `{"migrations_scanned": 2, "statements_executed": 2, "queries_scanned": 0, "skipped": []}`.

## Analyze against a copy of real data

For real row counts and timings, use a snapshot you built, such as an anonymized copy of production. Mark it with `ALTER DATABASE <db> SET forge.analyze_target = 'on'`, then run `FORGE_ANALYZE_DATABASE_URL=postgres://... reliant forge db analyze --target dsn`. Forge only reads the target: it migrates a copy and drops it. The role needs `CREATEDB` and ownership of the database, and a non-development environment's declared database is refused.

## What this does not do

* **It does not change your migrations.** It reports and prints a fix; you edit the file.
* **Empty tables by default.** The scratch target has no rows, so it shows what a statement locks and rewrites, not how long that takes on your data. Use `--target dsn` for that.
* **No rollback.** If a bad migration ships, recovery is a new forward migration. On [managed Postgres](https://docs.reliantlabs.io/hosting/managed-postgres.md) each migration statement runs with a 5-second `lock_timeout`, retried with backoff, so a migration that cannot get its lock fails the deploy instead of queueing behind traffic.

## Next steps

> [card] [Zero-downtime migrations](https://docs.reliantlabs.io/guides/forge/zero-downtime-postgres-migrations.md)
>
> Declare compatibility, expand then contract, and lint each migration.

> [card] [db analyze reference](https://docs.reliantlabs.io/reference/cli.md#reliant-forge-db-analyze)
>
> Every flag of `reliant forge db analyze`.

> [card] [Migration lint rules](https://docs.reliantlabs.io/forge/linting/rules-migrations.md)
>
> The text-level rules `forge lint --migration-safety` runs.

> [card] [Lint rollout](https://docs.reliantlabs.io/forge/linting/rollout.md)
>
> How new rules warn before they gate, and how to opt in early.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.