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:
dataset "synthetic" "orders" {batch = 5000table "users" {rows = 20000total = 2000000column "id" {value = row.index}}table "orders" {rows = 200000total = 50000000column "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:
CREATE INDEX orders_status ON orders (status);
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:
| Metric | Measures |
|---|---|
| db.rows | Rows inserted, updated, deleted, or read. |
| db.table.rewrites | Number of times a table is rewritten into new storage. |
| db.table.copy | Disk space the new copy of a rewritten table takes until the rewrite ends. |
| db.lock.time | Time a lock blocks reads or writes. |
| db.cpu.time | CPU time of the statements. |
| db.memory.usage | Most memory a single statement held. |
| db.io | Bytes the statements read from or wrote to disk. |
| db.log.written | WAL, redo log, or binary log written. |
| db.statement.time | Time 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.