Back to changelog
New
•3 minute read

Profile Expectations: Fail Tests on Locks, Rewrites, and Row Counts

Schema and migration tests can set bounds on what a command does to the database with expect "profile" blocks, and fail when a migration locks, rewrites, or changes more than allowed.

The --profile report shows what each command of a test does to the database. An expect "profile" block checks those numbers in the test itself. Atlas measures the command, even without --profile, and fails the test at the command's line when a bound does not hold.

Example

The online set below allows no table rewrites and at most five seconds of blocked reads or writes. It is defined once at the top level, and each migration test applies it with use:

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 two tested versions add an index and change a column type:

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

The synthetic 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.

Metrics

Each metric block bounds one metric with eq, ne, lt, le, gt, or ge, and labels such as table narrow the values it checks:

MetricMeasures
db.rowsRows inserted, updated, deleted, or read.
db.table.rewritesNumber of times a table is rewritten into new storage.
db.table.copyDisk space the new copy of a rewritten table takes until the rewrite ends.
db.lock.timeTime a lock blocks reads or 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.
db.log.writtenWAL, redo log, or binary log written.
db.statement.timeTime the statements ran.

A bound that Atlas cannot verify fails the test too, so a passing test means every bound held.

Notes

  • Expectations work on exec, catch, and script commands of schema and migration tests, and on migrate commands of migration tests.
  • Expectations are supported on PostgreSQL, MySQL 8.0.28 and later, and SQL Server.
  • 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.

Next Steps

Read the Profiling guide to write expectations, select values with labels, and see which bounds Atlas cannot verify.

featuretestingprofilingmigrate testschema testdatasetsatlas pro