Online migrations

The applier auto-rewrites ops that would block writes on large tables into their online variants. Cross the threshold and the planner promotes safe → online-required, executes a non-blocking variant, and reports progress through the CLI + boot log.

#Threshold

The threshold defaults to 50,000 rows per table. Override it with the --online-after <n> flag on voltro db plan / voltro db apply, or the VOLTRO_ONLINE_THRESHOLD env var (the flag wins). At or above n rows an index/column change is planned as an ONLINE (non-blocking) operation:

voltro db plan --online-after 10000       # treat tables ≥ 10k rows as online
VOLTRO_ONLINE_THRESHOLD=0 voltro db apply # every change online (most conservative)

The threshold check runs at plan time. The applier counts rows with a fast EXPLAIN-derived estimate (Postgres reltuples, MSSQL sys.dm_db_partition_stats.row_count, MySQL information_schema.tables.table_rows); exact counts only happen during the actual op if needed.

#ADD INDEX → online via CONCURRENTLY

export const posts = table('posts', {
  ...,
  authorId: reference(() => users),
}).index('byAuthor', ['authorId'])

On a 1M-row posts table, the plan output marks the index add as online:

◷ CREATE INDEX byAuthor ON posts(authorId)  # online — postgres CONCURRENTLY

Per-dialect mechanism:

• Postgres: CREATE INDEX CONCURRENTLY — no table lock, can read + write during build. ~3× slower than blocking + can't run inside a transaction. The applier moves it OUT of the main migration tx and runs it as its own step.
• MySQL 8 / MariaDB 10.6+: ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE LOCK=NONE. Slightly weaker guarantee than postgres (briefly takes a metadata lock at start + end) but unblocks DML throughout.
• MSSQL 2019+: CREATE INDEX ... WITH (ONLINE = ON). Enterprise Edition required for ONLINE=ON; Standard Edition falls back to blocking + the applier emits a loud warning.
• SQLite: no-op. SQLite locks anyway + the framework targets single-process deploys.

If the CONCURRENTLY build fails partway through (out of disk, killed by an admin, network blip), Postgres leaves the index in an INVALID state. The next plan run detects it + reissues the build:

◷ DROP INDEX byAuthor (invalid from prior partial build) + recreate

#Batched backfill with progress

A backfill on > threshold rows gets rewritten to batched:

⊕ ALTER TABLE posts ADD COLUMN slug text
  → backfill (batched, 1000 rows/batch, 50ms sleep): est. 50,000 rows, ~12s
⊕ ALTER TABLE posts ALTER COLUMN slug SET NOT NULL

Per-batch progress shows up in voltro logs --tail 50 while the apply runs:

backfill posts.slug: 12,000 / 50,000 (24.0%) — ETA 8s
backfill posts.slug: 24,000 / 50,000 (48.0%) — ETA 5s
backfill posts.slug: 36,000 / 50,000 (72.0%) — ETA 3s
backfill posts.slug: completed — 50,000 rows in 11.8s

Inside the framework the batched form is:

UPDATE posts
SET slug = LOWER(REPLACE(title, ' ', '-'))
WHERE id > $cursor AND slug IS NULL
ORDER BY id
LIMIT 1000

$cursor is the last batch's max id. The query plan is index-only on the primary key, so it stays fast as the table grows. WHERE slug IS NULL lets the same batch resume after a crash without double-updating completed rows.

Override the batching parameters per-column when you know more than the planner does:

slug: text().backfill(sql`lower(replace(title, ' ', '-'))`, {
  batchSize: 5000,    // big batches for cheap CPU-only updates
  sleepMs:   0,       // no breathing room needed
}),

embedding: text().backfill(async (row) => embed(row.title), {
  batchSize: 100,     // small batches — each row is an HTTP call
  sleepMs:   200,     // throttle external API quota
}),

#Shadow-column rewrite

For type changes on a large table that can't be done via ALTER COLUMN TYPE (Postgres requires a rewrite + table lock; MySQL likewise for non-trivial converts), the applier auto-emits a shadow-column pattern:

1. ADD COLUMN <col>_new <newType> nullable
2. Backfill batched: UPDATE ... SET <col>_new = (<col>::<newType>) in chunks
3. SET NOT NULL on <col>_new (if applicable)
4. Atomic swap inside a brief metadata lock: rename <col> → <col>_old, rename <col>_new → <col>, drop <col>_old

The atomic swap is the only blocking step + holds the lock for ~1 ms. Apps that pin connections might see a brief query error during the swap; pooled connections re-issue + succeed on the second attempt.

The plan output flags this clearly:

◷ ALTER TABLE users ALTER COLUMN created_at TYPE timestamptz  # shadow-column swap (4 steps: add → backfill → swap → drop)

Schema dependencies: indexes + FKs referencing the column are recreated against the new column in the same swap step.

#Cross-dialect quirks

Mechanism	Postgres	MySQL 8+	MariaDB 10.6+	MSSQL 2019+ Ent	SQLite
Online index build	CONCURRENTLY	INPLACE LOCK=NONE	INPLACE LOCK=NONE	WITH (ONLINE = ON)	no-op
Resumable failed index	INVALID state + recreate	partial drop + retry	partial drop + retry	requires DBA intervention	no-op
Batched backfill	identical	identical	identical	identical	identical (single-process)
Shadow-column type change	yes	yes	yes	yes	no (table rewrite from multi-dialect)

After a large backfill you'll usually want fresh planner statistics on the affected table. The applier does NOT emit ANALYZE / UPDATE STATISTICS for you — run it yourself once the apply completes (e.g. ANALYZE posts; on postgres) if the post-backfill query plans look stale.

#What CONCURRENTLY doesn't help with

Online migrations are about writes during apply. They don't help with:

• Reads during apply — never blocked by either path
• Long-running transactions that hold a lock the apply needs — the apply waits regardless
• Replication lag — the new index propagates to replicas at their own pace

For a multi-hour migration on a 100M-row table, the right pattern often isn't "make it online" but "split into many small applies + apply during low-traffic windows". The framework's planner-based system lets you express that as multiple deploys, each with a small focused plan, instead of one monolithic migration. The prod pipeline page covers the deploy-cadence side.