Skip to main content

Profiling Migrations, Queries, and Database Code

The --profile flag of atlas migrate test and atlas schema test profiles what the tests run: the migration files, and the queries, functions, procedures, and triggers of the schema. The tests supply the data and the workload, and Atlas measures what each statement does to the database. The report shows the locks a statement takes and for how long, the tables it rewrites, and the disk, log, and temporary space it needs. It flags the statements that would block or slow down production, and says what to do instead.

Preview Feature

Profiling is in preview and open to a subset of Teams and Enterprise customers. To get access, reach out through your Atlas support channel or message us on Intercom.

Running a Profile​

Add --profile to the test command. Atlas runs the tests on the dev database as usual and prints a profile after the result of each test case.

atlas migrate test --dev-url "docker://postgres/18/dev" --profile .

The numbers depend on the rows the test works on. See Profiling with Data to load them.

To profile against a dev database that outlives a single run, start it with atlas tool docker --profile. The container is configured for profiling:

export DEV_URL=$(atlas tool docker --url "docker://postgres/18/dev" --name profile-db --profile)
atlas migrate test --dev-url "$DEV_URL" --profile .

When you're done, stop the container with atlas tool docker kill --name profile-db.

Profiling with Data​

A profile measures the rows the test holds. On an empty table, every lock is short and no statement scans or rewrites much, so load the tables before the statements you want to measure. A dataset loads them in one command:

  • A synthetic dataset generates rows from the schema, at any size.
  • An rds_snapshot dataset loads a snapshot export of a real RDS instance. Point Atlas at the S3 bucket, or another location, that holds the export directory.
  • A csv dataset loads a directory of CSV files, one per table.

The rows of an rds_snapshot or csv dataset can be sampled with foreign keys intact, and their sensitive columns masked.

The following migration adds a column to the orders table and fills it in one statement:

migrations/20261104093012_add_orders_digest.sql
ALTER TABLE orders ADD COLUMN digest text;
UPDATE orders SET digest = md5(note);

The test migrates to the version before it, generates 2,000,000 orders, and then applies the new version over them:

migrate.test.hcl
dataset "synthetic" "orders" {
  table "public.orders" {
    rows = 2000000
  }
}

test "migrate" "20261104093012" {
  # Migrate to one version before the profiled version.
  migrate {
    to = "20261103101500"
  }
  # Generate the rows.
  dataset {
    use = dataset.synthetic.orders
  }
  # Apply the profiled version over the generated rows.
  migrate {
    to = "20261104093012"
  }
}
atlas migrate test --dev-url "docker://postgres/18/dev" --profile .

The profile of the migration over the 2,000,000 rows prints the following. The output is abridged:

migrate#3 20261104093012                9.84s
  rows     public.orders                2,000,000 updated
  read     public.orders                2,000,000 rows, 1 sequential scan
  size     public.orders                185.3MB -> 432.0MB (+246.7MB)
  log      721.3MB written, 721.3MB while the statements ran
  ! lock public.orders: takes AccessExclusiveLock for 9.842s, 9.806s of it after the statement, blocking reads and writes (20261104093012_add_orders_digest.sql:1)

Lines that start with ! are findings: statements that would block or slow down production. In the full report, each one is followed by why it matters and a safer way to write the statement.

The cost of a statement depends on the size of the table it runs on, so reading the SQL rarely shows it. A profile measures it during the test run, before the change is merged.

Coding agents can read the report too. It applies to any code that runs on the database: a migration generated with Atlas, a hand-written query, or a function, trigger, or procedure. After writing it, an agent runs the tests with --profile, measures the impact of the change on the database, and fixes the statements the report flags before it proposes the change.

Datasets are in preview. See Testing with Data for the dataset kinds, sampling, and masking. Without a dataset, seed the rows with an exec command, as in testing data migrations.

Supported Databases​

Profiling is supported on PostgreSQL, MySQL, MariaDB, and SQL Server.

On a dev database that Atlas runs in Docker, the report also includes the CPU, memory, and I/O of the database server.

Next Steps​

Testing with Data

Load an RDS snapshot export, CSV files, or generated rows into a test, sampled and masked

Testing Migrations

Seed a version, migrate past it, and assert on the data the migrations produce

Testing Schemas

Write schema tests with SQL assertions over functions, views, triggers, and queries