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.

Profile Expectations​

A profile reports what a command does to the database. An expect "profile" block sets bounds on those numbers, such as how long a table may stay locked, and fails the test at the command's line when a bound does not hold. A change that would block production then fails in the test run, before it is merged.

Add the block to an exec, catch, or script command of a schema or migration test, or to a migrate command of a migration test. To reuse a set of expectations, define it at the top level and apply it with use. A command can add its own metric blocks on top of the set.

For example, the online set below allows no table rewrites and at most five seconds of blocked reads or writes, and two migration tests apply it:

migrate.test.hcl
dataset "synthetic" "orders" {
batch = 5000
table "users" {
rows = 20000
total = 2000000
column "id" {
value = row.index
}
}
table "orders" {
rows = 200000
total = 50000000
column "user_id" {
value = row.index % 20000
}
column "status" {
value = format("s%d", row.index % 4)
}
}
}

# Online migrations rewrite no table,
# and block reads or writes for at most five seconds.
expect "profile" "online" {
metric "db.table.rewrites" {
eq = 0
}
metric "db.lock.time" {
le = "5s"
}
}

test "migrate" "index" {
migrate {
to = "1"
}
dataset {
use = dataset.synthetic.orders
}
migrate {
to = "2"
}
migrate {
to = "3"
expect "profile" {
use = [expect.profile.online]
}
}
}

test "migrate" "retype" {
migrate {
to = "1"
}
dataset {
use = dataset.synthetic.orders
}
migrate {
to = "5"
}
migrate {
to = "6"
expect "profile" {
use = [expect.profile.online]
metric "db.table.copy" {
table = "orders"
le = "1GB"
}
}
}
}

The earlier versions create the users and orders tables. The two tested versions add an index and change a column type:

migrations/3_status_index.sql
CREATE INDEX orders_status ON orders (status);
migrations/6_retype.sql
ALTER TABLE orders ALTER COLUMN amount TYPE bigint;

The dataset generates 200,000 rows of orders, and its total sets the 50,000,000 rows the table holds in production. Run the tests:

atlas migrate test --dev-url "docker://postgres/18/dev" .
-- FAIL: index (1.73s)
migrate.test.hcl:43:
Error: db.lock.time: public.orders (ShareLock, blocks writes): ~19.3s at 50,000,000 rows, expected less than or equal to 5s (3_status_index.sql:1)
-- FAIL: retype (2.61s)
migrate.test.hcl:61:
Error: db.table.rewrites: public.orders: 1, expected equal to 0 (6_retype.sql:1)
db.lock.time: public.orders (AccessExclusiveLock, blocks reads): ~48.6s at 50,000,000 rows, expected less than or equal to 5s (6_retype.sql:1)
db.table.copy: public.orders: ~5.7GB at 50,000,000 rows, expected less than or equal to 1GB (6_retype.sql:1)

Both tests fail, and each error points to the statement that broke the bound. The tests ran over 200,000 rows in under three seconds, while the lock times and the copy size are reported for the 50,000,000 rows of production.

Without --profile, Atlas measures only the commands that have an expect block, and prints no report. With --profile, it measures and reports every command, and checks the expect blocks as well.

Metrics​

A metric block bounds one metric with one or more of eq, ne, lt, le, gt, and ge. Labels, such as table, narrow the values it checks. Without labels, it checks every value of the metric, for example the lock time of every table.

MetricMeasuresLabels
db.rowsRows inserted, updated, deleted, or read.table, operation (INSERT, UPDATE, DELETE, READ)
db.table.rewritesNumber of times a table is rewritten into new storage.table
db.table.copyDisk space the new copy of a rewritten table takes until the rewrite ends.table
db.lock.timeTime a lock blocks reads or writes.table, mode or blocks (READS, WRITES)
db.cpu.timeCPU time of the statements.
db.memory.usageMost memory a single statement held.
db.ioBytes the statements read from or wrote to disk.direction (READ, WRITE)
db.log.writtenWAL, redo log, or binary log written.
db.statement.timeTime the statements ran.
  • table matches tables by name, with * and ? wildcards. Write <schema>.<table> to match in one schema. A label that matches a partitioned table also matches its partitions.
  • mode selects a lock mode, written as the report or the database names it, for example AccessExclusiveLock or ACCESS EXCLUSIVE on PostgreSQL. blocks selects locks by what they block, READS or WRITES, and works the same on every database. Set one of the two, not both.
  • Durations and sizes need a unit, such as "5s" or "1GB". A bare 0 needs none.

An expect block needs at least one metric, set in the block or added by use. test "plan" and test "script" cases do not support expect blocks.

Unverified values​

A test also fails when Atlas cannot verify a bound, and the error reports the value as not verified. To bound CPU, memory, and I/O, run the dev database in Docker, with a docker:// dev URL or atlas tool docker --profile. MySQL does not report the I/O of a statement, so on MySQL an upper bound on db.io always fails as not verified.

Expectations are supported on PostgreSQL, MySQL 8.0.28 and later, and SQL Server.

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​