Running schema changes on production databases is risky, especially on large, high-traffic tables. A common scenario: an ALTER TABLE command to add an index seems to run fine, but as it nears completion, query latency spikes and users notice slower performance. Canceling a long-running DDL job at that point isn't ideal, since it wastes all progress made and forces a restart from scratch. TiDB's pause and resume DDL feature solves this by letting administrators pause a job during peak load and resume it later, minimizing impact on production.

What is TiDB DDL Pause and Resume?

TiDB allows running DDL jobs to be paused and resumed without cancellation, which is useful when workloads fluctuate or during emergencies. In a TiDB cluster, one node acts as the "DDL owner," coordinating the workers that execute schema changes. The current owner can be identified with ADMIN SHOW DDL;, which returns the schema version, owner ID, and owner address.

How DDL Blocking Impacts Production Queries

Even though TiDB supports online DDL, operations like index creation or column-type changes still consume real resources while running. These "physical DDL operations" (which modify both metadata and underlying data) can cause increased write amplification, higher CPU usage, extra storage I/O, and higher query latency during the reorganization phase — commonly seen when creating indexes on large tables or backfilling large datasets.

Pausing a DDL Job — Step by Step

  1. Create the DDL job, e.g., CREATE INDEX idx_order_date ON orders(date);
  2. Check running jobs with ADMIN SHOW DDL JOBS; to find the job ID and its state.
  3. Pause the job using ADMIN PAUSE DDL JOBS <job_id>;, which returns a success result.
  4. Verify the pause by re-running ADMIN SHOW DDL JOBS; — the state should show as "paused."

Pausing does not cancel any currently running transactions: existing transactions continue, DDL reorganization stops, and resources are freed up for production workloads. This makes pausing safer than canceling during peak load.

 

Resuming a DDL Job - Commands and Timing


Once load decreases or during a maintenance window, resume the job with ADMIN RESUME DDL JOBS <job_id>;. Checking ADMIN SHOW DDL JOBS; afterward should show the state as "running." The job resumes from its last completed checkpoint rather than starting over. Many SaaS teams pause migrations during business hours and resume them during nightly maintenance windows, scheduled low-traffic periods, or region-specific off-peak hours to keep application performance consistent. If a resumed job runs into trouble, recommended steps include checking cluster health, verifying TiKV resource utilization, inspecting the DDL job queue, and canceling/recreating the job if necessary.

Real-World Use Cases

  • Peak traffic pause: A multi-tenant SaaS platform sees high API latency after adding an index to a large table; engineers pause the DDL job and resume it during off-peak hours instead of canceling it.
  • Emergency rollback without table locks: During index creation, an unexpected workload spike hits the cluster; pausing halts further resource consumption while the team investigates.
  • Multi-region coordination: For globally distributed deployments, DDL jobs can be paused until replication or regional traffic stabilizes, helping coordinate schema changes across regions.

Troubleshooting Common Issues

If a job ID is incorrect or the job has already completed, TiDB returns a "DDL Job Not Found" error — check ADMIN SHOW DDL JOBS; to verify. If a resumed job doesn't execute immediately, possible causes include another DDL job ahead in the queue, resource throttling, or TiKV backpressure. The same ADMIN SHOW DDL JOBS; command shows job order and state.

Best Practices

Set alerts to monitor long-running DDL job duration, always test pause/resume behavior in staging before applying it in production, and document DDL schedules so development and operations teams stay coordinated.

Conclusion

TiDB's pause/resume DDL feature gives teams control over long-running schema operations, letting them pause expensive jobs during peak workloads and resume them later without losing progress. This is especially valuable for SaaS platforms, high-traffic production systems, multi-region deployments, and large-scale data platforms.