Migrate Data in Batches with a Cursor
Process records in batches of 1000 using a cursor so a 10 million row migration does not blow up memory or lock the table.
Task
Apply DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop so the schema change is safe, reversible, and does not lock the table during peak traffic.
When to use this
A migration deploys DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop during peak hours, takes a 40-second lock, and causes a connection pool exhaustion.
Prerequisites
- A database with migration tooling configured
- A staging environment mirroring production schema
Steps
1. Configure DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop in the project configuration file with the recommended defaults.
DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop
Expected output: The configuration is accepted and DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop runs without errors on the first invocation.
2. Run the check locally to verify DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop produces the expected result before pushing.
pnpm test
Expected output: Local run matches CI behavior; no surprises after push.
3. Add the gate to the CI pipeline so DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop blocks merge on failure.
- name: Run add data migration batch cursor
run: pnpm test
Expected output: Pull requests with failing DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loop are blocked from merging.
Troubleshooting
Problem
The check passes locally but fails in CI
Solution
Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.
Problem
Configuration is rejected with a schema error
Solution
Check the tool version matches the config schema; older versions may not support newer fields.
Limitations
- The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
- Configuration drift is possible if someone edits settings outside version control.
Make migrations safe
Start by measuring the problem. Process records in batches of 1000 using a cursor so a 10 million row migration does not blow up memory or lock the table. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; – process, commit, loop against the current codebase and inspect the output before wiring it into CI.
DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; -- process, commit, loopVerify and roll back
Once the gate is in place, make failures actionable. A failing DECLARE cur CURSOR FOR SELECT id FROM old_table; FETCH 1000 FROM cur; – process, commit, loop should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.