MySQL Schema Change at Lunch, Not 2 A.M.

You need to add an index to a billion-row orders table. In traditional MySQL, that means scheduling a maintenance window, spinning up external tools like gh-ost or pt-online-schema-change, throttling writes, and praying nothing fails at 2 A.M. Your SRE team loses sleep. Your customers lose access. And if something goes wrong, rollback becomes a choose-your-own-adventure nightmare.

There's a better way. TiDB's online DDL runs schema changes in three phases while keeping reads and writes flowing. Internally, it progresses through multiple online DDL states during the change, so you can ship database migrations during business hours without toolchain choreography, lock drama, or SLA violations. This playbook shows storage leads and SREs how to execute MySQL online schema change operations safely during normal work hours using TiDB's built-in capabilities.

Who This Playbook Is For: Storage Leads & SREs

Storage team leads own the database platform and are responsible for schema governance, migration playbooks, and platform safety rails. They must balance velocity with reliability as application teams push schema changes weekly or even daily.

Site Reliability Engineers (SREs) are measured on uptime, incident frequency, and clean rollouts under load. For them, every schema change is a potential incident. Traditional MySQL schema operations create tension between these groups because the tools and processes required are complex, fragile, and interrupt service.

Their shared pain points include:

• Coordinating midnight maintenance windows across global teams.
• Babysitting long-running DDL tools that can fail midway.
• Fielding customer complaints about degraded performance during "online" schema changes.
• Building rollback playbooks for every possible failure mode.

TiDB addresses these challenges with built-in MySQL online DDL that runs in three phases to keep reads and writes flowing, while internally progressing through multiple online DDL states during the change. Schema changes happen during lunch, not midnight, and the entire operation is controlled through standard SQL, with no external tool orchestration required.

What We're Solving Today: Schema Changes Hurt at Scale

Traditional ALTER TABLE statements lock tables or throttle writes during execution. Even sophisticated online DDL tools introduce jitter, connection retries, and pause-resume logic that affects application performance. On tables with billions of rows, table rewrites can take minutes to hours. Running these operations during business hours risks order failures, slow page loads, and broken reports, which is why teams resort to the dreaded 2 A.M. maintenance window.

Why Locks & Rewrites Break SLAs

MySQL's traditional DDL implementation uses table copying or in-place algorithms that hold metadata locks, rebuild indexes, or reorganize data. During these operations:

• Metadata locks block concurrent DML (inserts, updates, deletes) until the DDL completes.
• Table rewrites consume massive amounts of IO and CPU, degrading performance for all queries.
• Replication lag spikes as replicas struggle to keep up with DDL changes.
• Connection storms occur when applications retry queries that timeout during locks.

The result: p95 and p99 latencies spike, error rates climb, and customer-facing services degrade. Even "online" DDL operations in MySQL 5.7+ and 8.0+ can cause substantial performance impact on large, busy tables.

Tool Chaining vs. Embedded Capabilities

To work around MySQL's DDL limitations, many teams adopt external tools like gh-ost or pt-online-schema-change. These tools work by creating shadow tables, copying data in chunks, and then swapping tables at the end. While this approach avoids full table locks, it introduces new operational complexity:

• Installing, configuring, and maintaining separate toolchains.
• Writing automation to handle tool failures, retries, and rollbacks.
• Managing additional load from shadow table writes and data reconciliation.
• Coordinating cut-over timing to minimize application disruption.
• Cleaning up triggers, shadow tables, and artifacts after completion.

This external toolchain approach transforms what should be a simple database operation into a multi-phase engineering project. TiDB eliminates this complexity by embedding online DDL capabilities directly into the database engine.

DIY Route: External Change Toolchain (Without TiDB)

Off-Hours Windows, Throttlers, Shadow Tables

Running MySQL add index online operations without TiDB requires a multi-tool approach that most teams have battle-tested through painful experience. Here's what the typical workflow looks like:

Plan windows: Coordinate cross-team communications, freeze application deployments, and book low-traffic time slots (typically 2-4 A.M. local time). This requires alignment across engineering, product, and support teams.

Choose & wire tools: Select between gh-ost and pt-online-schema-change based on your MySQL version and workload patterns. Configure CDC streams to monitor replication lag. Set up throttlers to protect p99 latency. Write failover runbooks for every conceivable edge case.

Warm replicas & rehearse: Copy production load to staging. Pre-seed indexes on test tables. Simulate replication lag scenarios. Tune chunk sizes and throttle parameters through trial and error.

# Example pt-online-schema-change command: MySQL add index online
pt-online-schema-change \
  --alter "ADD INDEX idx_created_at (created_at)" \
  --execute \
  h=prod-db-01,D=orders,t=order_items \
  --max-lag=2 \
  --chunk-size=1000 \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --chunk-time=0.5

Run & babysit: Monitor the tool's progress for hours. Throttle aggressively to protect p99 latency. Chase deadlocks and lock wait timeouts. Re-try failed chunks. Handle failover edge cases when replicas fall behind. Stay awake through the entire operation.

Audit & clean up: After the cut-over completes, remove triggers and shadow tables. Reconcile any data drift between the original and new table. Run ANALYZE TABLE to update statistics. File incident postmortems documenting what went wrong and what you'll do differently next time.

Multi-Hop Ops and Consistency Jitter

The external toolchain approach introduces consistency challenges that don't exist with native online DDL:

• Trigger overhead: Tools create triggers to capture changes during the copy phase, adding latency to every write.
• Cut-over coordination: The final table swap requires careful timing to minimize data loss or duplication.
• Replication drift: Shadow tables and triggers can cause replicas to fall behind, requiring manual intervention.
• Resource contention: The tool's chunk-based copying competes with application queries for IO and CPU.

Each of these factors increases the risk of incidents, extends maintenance windows, and adds to SRE on-call burden.

TiDB Route: Embedded, In-Database Online DDL

Three-Phase Build with Live Traffic

TiDB's online DDL runs schema changes through three phases, without blocking reads or writes, and internally it progresses through multiple online DDL states during the change. The entire operation happens in-database with no external tools required:

Phase 1 - Prepare: TiDB validates the DDL statement and creates internal metadata structures. This phase typically completes in seconds.

Phase 2 - Build: The database builds new indexes or restructures data in the background while serving live traffic. The TiDB scheduler smooths IO and CPU usage to prevent p99 spikes. Application queries continue uninterrupted.

Phase 3 - Commit: After the background work completes, TiDB atomically commits the schema change with minimal coordination overhead.

-- MySQL ALTER TABLE without downtime: add index during business hours
ALTER TABLE orders ADD INDEX idx_created_at (created_at);

-- Monitor DDL progress
SELECT
    DB_NAME,
    TABLE_NAME,
    JOB_TYPE,
    STATE,
    ROW_COUNT,
    START_TIME
FROM information_schema.DDL_JOBS
WHERE STATE = 'running';

-- Monitor DDL progress (friendlier view)
ADMIN SHOW DDL JOBS;

Keep Reads/Writes Flowing—No App Changes

Unlike external schema change tools that require application-level coordination, TiDB's online DDL is completely transparent to applications. There are no connection pool changes, no retry logic to implement, and no special handling for in-flight transactions.

The benefits extend beyond simplicity:

• Predictable performance: The scheduler prevents DDL operations from degrading query performance.
• Automatic rollback: If a DDL fails mid-execution, TiDB automatically rolls back without leaving artifacts.
• Single operational surface: Same RBAC, backups, observability, and autoscaling as the rest of TiDB.
• Daytime deployment: Ship schema changes during normal business hours with confidence.

-- Online index creation: type change and index in a single operation
ALTER TABLE ledger
    MODIFY COLUMN amount DECIMAL(20,4),
    ADD INDEX idx_txn_date (transaction_date);

-- Check table structure immediately
SHOW CREATE TABLE ledger;

The outcome is predictable, SLO-friendly schema changes that fit normal change windows without special tooling or off-hours coordination.

Labor Showdown: External vs. Embedded Approach

Let's compare the engineering effort, risk, and time required to add an index to a 1 billion-row table using external tools versus TiDB's embedded online DDL.

Bottom line: External toolchains require multi-tool choreography, off-hours staffing, and complex rollback gymnastics. TiDB delivers one SQL statement, daytime rollout, and standard rollback via DDL control, reducing engineering hours by 70% and eliminating overnight incidents.

Implementation Flight Plan

Follow this step-by-step plan to execute schema changes safely in TiDB during normal business hours.

Step 1: Readiness

Pick a candidate table for your first online DDL operation. Confirm that backups are current and that you have defined SLO guardrails for acceptable p95 and p99 latency during the change.

-- Verify backup status
SHOW BACKUPS;

-- Check current table statistics
ANALYZE TABLE orders;
SHOW STATS_META WHERE table_name = 'orders';

Step 2: Dry-Run

Run a staging load test with production-like cardinality and data skew. Execute the DDL statement in staging and monitor the impact on query performance.

-- Staging environment test
ALTER TABLE orders_staging ADD INDEX idx_created_at (created_at);

-- Monitor query performance during DDL (SQL-level view)
-- Note: statements_summary does not expose p95 latency. Use PD Dashboard/Grafana for p95/p99.
SELECT
    digest_text,
    avg_latency / 1000000 AS avg_ms
FROM information_schema.statements_summary
WHERE schema_name = 'staging'
ORDER BY avg_ms DESC
LIMIT 20;

Step 3: Execute

Run the DDL in production during normal business hours. Monitor DDL progress using the jobs table and watch p95/p99 latencies to confirm they remain within SLO.

-- Execute DDL in production
ALTER TABLE orders ADD INDEX idx_created_at (created_at);

-- Monitor DDL progress (raw view)
SELECT
    JOB_TYPE,
    STATE,
    ROW_COUNT,
    START_TIME,
    TIMESTAMPDIFF(MINUTE, START_TIME, NOW()) AS elapsed_minutes
FROM information_schema.DDL_JOBS
WHERE STATE = 'running';

-- Monitor DDL progress (friendlier view)
ADMIN SHOW DDL JOBS;

-- Watch latency percentiles (p95/p99)
-- Note: statements_summary does not expose p95/p99 latency. Use PD Dashboard/Grafana for p95/p99.
-- You can still monitor average latency here as a SQL-level signal:
SELECT
    digest_text,
    avg_latency / 1000000 AS avg_ms
FROM information_schema.statements_summary
WHERE schema_name = 'production'
ORDER BY avg_ms DESC
LIMIT 10;

Step 4: Validate

After DDL completes, compare query plans to confirm the new index is being used. Hit key workloads to verify performance improvements. Remove any compensating controls you added before the change.

-- Verify index was created
SHOW CREATE TABLE orders;

-- Compare query plans
EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';

-- Test key queries
SELECT COUNT(*) FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01';

Step 5: Document

Codify the runbook and add alerts for future DDL operations. Document timing, performance impact, and any lessons learned. Add this pattern to your team's golden path for database migration zero downtime.

Day-2 Ops Best Practices

After your first successful daytime DDL operation, establish these practices to make online schema changes routine.

Observability: DDL Progress Metrics, Top SQL, Heatmaps

Monitor DDL progress using TiDB's built-in observability tools. The DDL jobs table shows real-time status, row counts, and elapsed time. Top SQL identifies queries affected by schema changes. Heatmaps reveal IO patterns during background index builds.

-- Real-time DDL monitoring query
SELECT
    DB_NAME,
    TABLE_NAME,
    JOB_TYPE,
    STATE,
    ROW_COUNT,
    START_TIME,
    TIMESTAMPDIFF(MINUTE, START_TIME, NOW()) AS running_minutes
FROM information_schema.DDL_JOBS
WHERE STATE IN ('running', 'queueing')
ORDER BY START_TIME DESC;

-- Identify slow queries during DDL
SELECT
    digest_text,
    exec_count,
    avg_latency / 1000000 AS avg_ms,
    max_latency / 1000000 AS max_ms
FROM information_schema.statements_summary
WHERE max_latency > 1000000  -- queries slower than 1s
ORDER BY avg_ms DESC
LIMIT 20;

Guardrails: Change Budgets, Canary DDL, SLO-Aware Throttling

When you run many DDLs at once, they can queue. That means you need to establish change budgets that limit how many large DDLs run at the same time. Use shadow tables for canary testing of complex schema changes. Configure SLO-aware throttling to automatically slow DDL operations if query latency exceeds thresholds.

-- Create canary table for testing
CREATE TABLE orders_canary LIKE orders;

-- Test DDL on canary first
ALTER TABLE orders_canary ADD COLUMN priority INT DEFAULT 0;

-- After validation, apply to production
ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0;

Resilience: Backup Verification, Rollback Patterns

Before any significant schema change, verify that backups include the table structure and data. Test restoration procedures to confirm recovery time objectives. Document rollback patterns for different DDL types.

-- Verify backup coverage before DDL
SELECT
    table_schema,
    table_name,
    create_time,
    update_time
FROM information_schema.tables
WHERE table_schema = 'production'
  AND table_name = 'orders';

-- Rollback pattern for failed DDL
-- TiDB handles automatic rollback on failure,
-- but you can also explicitly drop unwanted changes
ALTER TABLE orders DROP INDEX idx_created_at;

Proof in Production

Real teams running mission-critical workloads have eliminated 2 A.M. schema changes using TiDB's online DDL capabilities. Here's what that looks like in practice.

Plaid: From Six Months of Upgrade Pain to One Week with Zero Downtime

Plaid, a financial services technology company processing billions of financial transactions, faced severe operational pain with Amazon Aurora MySQL. Online schema changes required heavy-handed workarounds, where teams would add entirely new tables instead of modifying existing ones to avoid service interruptions. On tables ranging from 2-10+ TB, schema modifications were architectural nightmares.

The numbers told a brutal story: Plaid spent two engineering years annually architecting around Aurora's limitations. Major version upgrades consumed six months of engineering effort and still required tens of minutes of downtime per cluster. For a company committed to 24/7 uptime in financial services, this was unsustainable.

Outcomes

• Upgraded six production clusters in one week with zero downtime, instead of spending months on each major upgrade cycle.
• Cut maintenance effort by 96%, reclaiming engineering time previously spent architecting around Aurora's limits.
• Ran routine schema modifications during business hours, even on 5+ TB tables with tens to hundreds of billions of rows.
• Reduced paging and operational anxiety, so teams felt safe making routine DDL changes.

Pinterest: Petabyte-Scale Operations Without Midnight Incidents

Pinterest operates one of the world's largest visual discovery platforms, serving over 500 million monthly users with recommendation engines, shopping catalogs, and graph databases. Their legacy HBase infrastructure spanned more than 50 production clusters hosting 9,000+ virtual machines with 6+ PBs of data on disk. While HBase handled scale, it lacked critical capabilities: no secondary indexes, no transaction support, and schema changes required extensive planning and coordination.

The operational burden was crushing. Every schema evolution meant coordinating across dozens of clusters. Adding indexes required building entire secondary indexing services. The infrastructure cost was high, the complexity was growing, and the team knew they couldn't serve the next 3-5 years of business needs without fundamental change.

Outcomes

• Consolidated 50+ clusters while adding capabilities the legacy stack could not deliver.
• Eliminated the need for application-level indexing services by using TiDB's native secondary indexes and ACID transactions.
• Used online DDL to ship schema changes without coordinating multi-cluster maintenance windows.
• Reduced infrastructure costs by 80%+, while improving operational safety and velocity.

Next Steps

If you're ready to stop coordinating midnight maintenance windows and start shipping schema changes at lunch, with proof that billion-row tables won't lock or degrade SLAs, these next steps will get you started quickly.