SQLite rename-and-swap migration: bounding the final exclusive lock for a small application

Short answer

Yes, the final ALTER TABLE ... RENAME can almost always be kept under 200 ms for a 100 MB table — because the lock hold time has essentially nothing to do with table size. The rename itself is a schema-header update measured in microseconds. What determines your real window is how long the migration connection must wait for existing readers to finish, and whether a WAL checkpoint gets pulled into the same transaction. And no, a short busy_timeout cannot corrupt the schema: a timed-out rename fails cleanly with SQLITE_BUSY and leaves the database exactly as it was.

What actually determines the lock window

Separate two phases: waiting to acquire the lock, and holding it.

• Hold time: updating the schema entry and dropping the mirror triggers is a handful of page writes. Sub-millisecond at this scale, regardless of whether the table is 1 MB or 100 MB. Row data is never touched by a rename.
• Wait time: in WAL mode, readers hold read marks on snapshots. The rename needs the write lock plus schema exclusivity, so it must wait for in-flight transactions that conflict. Long analytical reads or an application transaction someone forgot to commit are the real threat to your 200 ms budget — not the table.
• Checkpoint pressure: if your batched copy left a large WAL, a checkpoint triggered near the rename (e.g., by wal_autocheckpoint or a closing connection) adds I/O. Run PRAGMA wal_checkpoint(TRUNCATE); on the migration connection before the rename so the final step starts with a nearly empty WAL.

Procedure for this case

1. Finish the batched copy and verify row counts.
2. PRAGMA wal_checkpoint(TRUNCATE);
3. On the migration connection only: PRAGMA busy_timeout = 3000; — long enough to ride out normal short readers, short enough to fail fast if something is stuck.
4. In a single transaction: rename old table away, rename new table into place, drop the mirror triggers, commit.
5. On SQLITE_BUSY, retry the whole transaction a bounded number of times, then abort the migration and alert.

Busy timeout and consistency

busy_timeout only controls how long SQLite retries before returning SQLITE_BUSY. It never interrupts a lock already held, and it never partially applies a rename. Because the rename-and-cleanup runs inside one transaction, the outcome is binary: either the whole swap is visible, or nothing changed. The only real risk is application-level — code that misreads a timeout as success. Check the return code explicitly.

Verify before the production run

On a staging copy, spawn readers with controlled transaction lengths (e.g., 50 ms, 500 ms, 5 s) and time the rename with a high-resolution clock. You should see hold time near zero and total time tracking the longest active reader. Then set busy_timeout = 100, hold a reader open, and confirm the rename returns SQLITE_BUSY while PRAGMA table_list / sqlite_master show the original schema untouched.

Remaining caveat

The 200 ms figure is an engineering target, not a documented guarantee — SQLite does not publish lock-duration bounds. If your application has any code path that can hold a read transaction open for seconds, no timeout setting will save the window; you must pause or drain those readers during the cutover. That reader behavior is the one diagnostic worth confirming before scheduling the migration.