I’ve also been using PostgreSQL for about six years now. But using it and understanding its internals are different things. I knew how to write queries and stand up a server. I didn’t have a clear picture of what Postgres was actually doing under the hood, or why it made the design choices it made. So I decided to dig in. And I found that every explanation out there falls into one of two buckets: either it’s a “Postgres vs MySQL” comparison written by someone who clearly picked a side, or it’s a Postgres tutorial that starts from “what is a table.” Neither is useful when you already know what a clustered index is and you want to understand why Postgres doesn’t have one.

This post is what I wish existed when I started: PostgreSQL internals explained from the perspective of someone who already knows MySQL. Every concept mapped to its InnoDB equivalent. No fanboy energy. Just the architecture.

MVCC: Two philosophies, same goal

Both InnoDB and PostgreSQL implement MVCC. Readers don’t block writers, writers don’t block readers. Same goal. Completely different implementations.

What you already know from InnoDB:

When you run an UPDATE, InnoDB modifies the row in-place in the buffer pool page. The previous version gets copied to the undo log (rollback segment). The modified page is marked dirty and eventually flushed to the tablespace by a checkpoint or background flushing. The redo log guarantees durability before the flush happens. If another transaction needs the old version, InnoDB follows the pointer chain in the undo log until it finds the right one. When no active transaction needs those old versions anymore, the purge thread cleans them up quietly in the background.

The key insight: the table (the clustered index) always contains only the latest version. History lives elsewhere.

How PostgreSQL does it:

Postgres does the opposite. When you run an UPDATE, the original row stays untouched. Postgres writes a brand new copy of the entire row into the same table. The old row gets metadata that says “I was killed by transaction Y.” The new row gets metadata that says “I was born in transaction Y.”

Every row (Postgres calls them “tuples”) carries two internal fields: xmin (the transaction that created it) and xmax (the transaction that deleted or updated it). A live row has an xmin but no effective xmax. A dead row has both.

So the table itself is a graveyard of versions. Current rows, dead rows, all mixed together in the same physical structure. When a transaction needs to read data, it checks each tuple’s xmin and xmax against its own snapshot to decide: “can I see this version?”

Think of it this way. In InnoDB, the undo log is an archive in the basement. The table is the office, and it always has the current document on the desk. In Postgres, there is no basement. Every version of every document, current and historical, is piled on the same desk. Each one has a sticky note that says when it was valid.

The heap: why there’s no clustered index

In InnoDB, the primary key index is the table. The B+Tree leaf nodes contain the actual row data, physically sorted by the primary key. This is the clustered index. When you do a SELECT by PK, InnoDB walks the tree and lands directly on the data. A secondary index stores the PK value as a pointer, so a secondary index lookup means: find the PK in the secondary index, then look up the PK in the clustered index to get the row.

Postgres doesn’t have this concept. The table data lives in a structure called the heap. It’s a file where tuples are written in whatever order they arrive, page by page. Pages are 8KB (vs InnoDB’s 16KB). No sorting by any key. No tree structure. Just pages with tuple slots, filled wherever there’s room.

When you create any index in Postgres, including the primary key index, it’s a separate B-Tree that points to the physical location of the tuple in the heap. The pointer is a ctid (a page number and slot offset, like “page 42, slot 7”). Every index in Postgres is essentially what InnoDB would call a secondary index, except instead of pointing to a PK value, it points to a physical address.

The consequences for operations you already understand:

Range scans on the PK. In InnoDB, a range scan on the PK is a sequential read through contiguous leaf pages. Fast, because the data is physically ordered. In Postgres, even if you scan the PK index in order, each tuple you find could be anywhere in the heap. You’re doing random I/O into the heap for each row. Postgres has a “bitmap heap scan” optimization that collects all the heap addresses first and then reads them in physical order, but it’s still fundamentally different from InnoDB’s clustered access pattern.

UPDATEs and indexes. In InnoDB, when you update a non-indexed column, only the clustered index page changes. Secondary indexes still point to the same PK, which still points to the same row (now updated in place). In Postgres, because an UPDATE creates a new tuple at a new physical location, every index on that table needs a new entry pointing to the new location. This is expensive. Postgres mitigates this with HOT (Heap-Only Tuple) updates: if the new tuple fits in the same heap page and you didn’t change any indexed column, Postgres can skip the index updates and chain the old tuple to the new one within the page. But the moment you change an indexed column or the page is full, you pay the full cost.

The CLUSTER command. Postgres has a CLUSTER command that physically reorders the heap to match an index, once. It doesn’t maintain the order over time. After a few thousand inserts and updates, the heap is scattered again. It’s not a clustered index. It’s a one-time defragmentation.

VACUUM: the price of keeping history in the table

In InnoDB, the purge thread runs automatically in the background. It cleans up undo log entries that no active transaction needs. You almost never think about it. The worst case is when a very long-running transaction holds back the purge (the history list length grows), but even then the problem is contained to the undo tablespace.

In Postgres, the equivalent is VACUUM. Because dead tuples live in the table itself, someone has to come along and mark that space as reusable. That someone is VACUUM (or autovacuum, the background process that triggers it automatically).

VACUUM scans the table, identifies tuples that are invisible to all current transactions, and marks their space as available for new inserts. It doesn’t return the space to the OS by default. The table file stays the same size, but the free space inside it gets reused. This is why Postgres tables can “bloat”: if VACUUM can’t keep up, or if something prevents it from cleaning (like a long-running transaction holding back the visibility horizon), dead tuples accumulate and the table grows beyond what the live data needs.

The closest InnoDB analogy is imagining that the undo log didn’t exist as a separate structure, and instead old row versions accumulated directly inside the .ibd file. And then the purge thread had to walk the entire file to find and reclaim the dead ones. That’s basically what VACUUM does.

VACUUM FULL is the nuclear option. It rewrites the entire table into a new file, copying only live tuples. Reclaims all the space. But it takes an ACCESS EXCLUSIVE lock, which means nothing can read or write the table while it runs. On a busy production table, this is rarely an option.

There’s a parameter called idle_in_transaction_session_timeout that you’ll want to know about. A session that starts a transaction and then idles holds back the visibility horizon for the entire database, preventing VACUUM from cleaning any tuples created after that transaction started. It’s the Postgres equivalent of a long-running transaction inflating the history list length in InnoDB, except the consequences are worse because the bloat is inside every table.

WAL and logical decoding: the Postgres binlog

The WAL (Write-Ahead Log) is the Postgres equivalent of the redo log and binlog combined. Every change is written to WAL before it’s applied to the data files. It handles crash recovery (like the redo log) and replication (like the binlog).

For CDC and change data capture, what matters is logical decoding. This is the mechanism that reads WAL records and converts them into logical change events (INSERT, UPDATE, DELETE with row data). You consume these events through a replication slot with an output plugin (pgoutput, wal2json, test_decoding).

The replication slot tells Postgres: “don’t discard WAL segments past this point, I have a consumer that hasn’t caught up yet.” Same concept as MySQL retaining binary logs until all replicas have consumed them.

REPLICA IDENTITY: the Postgres binlog_row_image

In MySQL, binlog_row_image controls what data gets written to the binlog for each row event. FULL writes all columns before and after. MINIMAL writes only what’s needed to identify and apply the change.

Postgres has REPLICA IDENTITY, which is set per table:

• DEFAULT: Uses the primary key columns as the row identifier. For UPDATEs, you get all new column values plus only the PK as the “old key.” For DELETEs, you get only the PK of the deleted row.
• FULL: The entire old row is included. All columns, before and after. This is what you need if you want complete before/after images for CDC.
• USING INDEX: Uses a specific unique index as the identifier instead of the PK.
• NOTHING: No row identification. UPDATEs and DELETEs can’t be replicated.

The comparison to binlog_row_image is not exact. binlog_row_image controls what gets logged for all purposes. REPLICA IDENTITY is specifically about how the subscriber (or CDC consumer) identifies which row was affected. But the practical effect for building a change stream is similar.

TOAST: the large-value problem

Here’s something that has no direct parallel in the binlog world. Postgres pages are 8KB. A tuple can’t span pages. So when a column value exceeds roughly 2KB, Postgres moves it to a separate TOAST table (The Oversized-Attribute Storage Technique) and leaves a pointer in the main tuple.

Think of it like InnoDB’s overflow pages for BLOB/TEXT columns with DYNAMIC row format, where large values get stored off-page.

The CDC implication: when you do an UPDATE that doesn’t touch a TOASTed column, Postgres doesn’t include that column’s value in the WAL event. Your CDC consumer gets a placeholder that means “unchanged.” With REPLICA IDENTITY FULL, the old values including TOAST are included, but at the cost of more WAL volume and I/O.

In MySQL with binlog_row_image=FULL, every column is always included regardless of size. No special handling needed. One less thing to worry about.

Replication: why the standby can’t have its own MVCC

This is where the architectural difference between MySQL and Postgres replication becomes most visible.

Physical replication

Postgres streaming replication works by shipping WAL records to a standby server, which applies them byte by byte. The standby’s data files become a physical copy of the primary. When a WAL record says “write these bytes to page 42 of table X”, the standby does exactly that.

If you enable hot_standby = on, you can run read queries against the standby. Those queries need MVCC to get a consistent snapshot. Here’s the problem: the standby doesn’t have its own MVCC machinery. It doesn’t generate its own tuple versions. It just replays what the primary did.

In MySQL, a replica applies relay log events using InnoDB. InnoDB on the replica generates its own undo log, manages its own buffer pool, handles its own MVCC. The replica is a sovereign database engine that happens to receive instructions from the primary. The purge thread on the primary can clean up whatever it wants, and the replica doesn’t care because it has its own undo log for its own read queries.

In Postgres, the standby has no independent version management. When a read query on the standby needs to see a tuple that was alive at time T, it relies on that tuple still existing in the heap. But if the primary ran VACUUM and removed that tuple, and the WAL replay applied that VACUUM to the standby, the tuple is gone. The query fails with a replication conflict.

Postgres resolves this in one of three ways:

1. Cancel the query on the standby (the default).
2. Delay WAL replay to give standby queries time to finish (max_standby_streaming_delay).
3. Have the standby report its oldest needed xmin to the primary with hot_standby_feedback = on, so the primary delays VACUUM for those tuples.

Option 3 sounds good until you realize it means slow queries on the standby can cause bloat on the primary. A read query 3,000 miles away can prevent VACUUM from running on your primary server. In MySQL, this problem literally cannot exist because the replica’s MVCC is independent.

Logical replication

Postgres also has logical replication, which decodes the WAL into logical changes (INSERT, UPDATE, DELETE) and sends them to a subscriber. This is conceptually closer to MySQL’s row-based replication. The subscriber has its own tables, its own heap, its own MVCC. It applies the changes as regular SQL operations.

A logical replication slot retains WAL segments until the consumer catches up. If the consumer is down or lagging, WAL accumulates on disk. The slot also holds a catalog_xmin that prevents VACUUM from cleaning dead tuples in the system catalog tables (pg_class, pg_attribute, etc.) because the logical decoder needs the old catalog state to correctly decode WAL records from the past.

Important distinction: a logical slot’s catalog_xmin only blocks VACUUM on system catalog tables, not on your user tables. Your regular tables get vacuumed normally. This is different from physical replication slots (with hot_standby_feedback), which can block VACUUM on user tables too.

An abandoned logical replication slot causes two problems: unbounded WAL accumulation on disk, and bloat in the system catalogs. In extreme cases, the catalog bloat can become severe enough to threaten transaction ID wraparound. Always monitor your replication slots. Always drop the ones you’re not using.

The operational lens

Let me be direct about what I think after studying this.

MySQL’s architecture makes the DBA’s operational life easier in several specific ways. The purge thread is invisible. The clustered index eliminates a whole class of random I/O problems. The binlog is self-contained and doesn’t need to reference the data dictionary to decode events from the past. Replication is logically independent on each node.

Postgres gives the developer more features out of the box. Richer type system, better JSON support, extensions like PostGIS, more standard SQL compliance. For the person writing queries and designing schemas, Postgres often feels more complete.

The trade-off is operational complexity. VACUUM tuning is a real discipline. Bloat monitoring requires active attention. Replication conflicts on hot standbys are a problem MySQL DBAs have never had to think about. TOAST adds a layer of complexity to CDC that doesn’t exist with the MySQL binlog.

Neither system is better. They made different architectural bets. Understanding both makes you better at each one, because you stop taking your database’s design choices for granted and start seeing them as trade-offs that could have gone the other way.

I’m a MySQL DBA by trade. This is what I found when I stopped using PostgreSQL on autopilot and started reading its source of truth.

This is not a developer. Not exactly. Not a project manager either. And definitely not a prompt engineer (that term needs to retire already). The Claude Code Engineer is the person who sits between the backlog and production, and their main tool is not an IDE. It’s an agentic coding assistant.

The loop

Here’s what a Claude Code Engineer does, every day:

1. Picks up issues. From GitHub, Jira, Linear, wherever. They tell Claude Code to read the issue and come up with a plan.
2. Reviews the plan. They don’t approve blindly. They read the plan, understand the trade-offs, and leave a comment on the ticket explaining why they approved, changed, or rejected it. This means the engineer needs to understand the code and the business.
3. Enforces documentation. Every issue must produce or update documentation. No exceptions.
4. Enforces testing. Unit tests, integration tests, regression tests, e2e. The engineer decides what coverage the change needs and makes sure Claude Code generates it.
5. Runs the tests and does QA. They run the full test suite, review the output, and do manual QA. Last checkpoint before merge.
6. Manages the git workflow. Worktrees, pull requests, clean commit history. The engineer handles the merge when everything is green.

That’s the loop. Issue, plan, review, docs, tests, QA, merge. Every day. Multiple issues in parallel.

Why QA is the main skill

If I had to rank the skills needed for this role: QA first, product second, dev third.

Claude Code can write code. It can also write tests, but it will write the tests that pass, not the tests that should pass. The difference between those two is the whole point of quality assurance.

The Claude Code Engineer needs to think about edge cases the LLM didn’t consider and how the change interacts with what already exists. They need to be the person who says “what happens when the input is null and the user is unauthenticated and the database is in read-only mode?” and then makes Claude Code write that test.

This is not a junior role. This is someone who has broken enough things in production to know where things break.

Why git worktrees, not just branches

Quick detour: a git repo only has one working directory. If Claude Code is halfway through a feature, you can’t switch branches and start another task. You’re stuck waiting.

Worktrees fix this. Multiple working directories, each on its own branch, all sharing the same repo history. No cloning the repo five times. Claude Code has built-in worktree support: claude --worktree feature-auth and you’re running. The team at incident.io wrote about their experience running four or five Claude agents at the same time this way. It’s what makes the Claude Code Engineer role possible: one person coordinating multiple parallel AI sessions, each working on a different issue.

The Issue Architect

Someone has to create those issues. The Issue Architect has a product/infrastructure/architecture profile. They think in milestones and epics. They break down big initiatives into small, well-scoped issues that can be worked on independently and in parallel.

Why parallel? Because if the issues are well-structured, there’s no reason you can’t have multiple worktrees running at the same time. The bottleneck is no longer “how fast can we type code.” The bottleneck is “how well did we define the work.”

The Issue Architect’s value is in knowing how to split things up. How to break a large feature into independent pieces that don’t create merge conflicts and can be tested on their own. That skill becomes 10x more valuable when your execution speed goes way up.

The Toolsmith

The Claude Code Toolsmith builds custom plugins, skills, CLAUDE.md files, and hooks that put the team’s knowledge into Claude Code’s workflow: code review checklists, database safety rules, changelog validation, naming conventions, QA templates built from real production incidents.

They make sure that when Claude Code writes code for your company, it writes it the way your company writes code. Not generic code. Not “best practices from Stack Overflow” code. Your code.

What about the people who aren’t developers?

I’m a DBA. I don’t write great code. I know databases, a lot, and some adjacent stuff: devops, linux, monitoring. So when I look at these roles, I ask myself: where do I fit?

AI can write a migration. It can generate an ALTER TABLE that looks perfectly fine. But it has no idea that your table has 500 million rows and that ALTER will lock it for 20 minutes during peak traffic. It doesn’t know that your replication topology means that DDL needs to run through pt-online-schema-change, not as a direct statement. It doesn’t know that ON DELETE CASCADE on that particular table will silently wipe half your data if someone deletes the wrong parent row. The DBA knows that.

In this model, the specialist becomes the most important kind of Toolsmith. The DBA builds the skill that flags DDL on tables over 10M rows. The SRE builds the hook that validates resource limits. The security engineer builds the check that scans for hardcoded secrets.

And yes, I see the irony. I’m building dbsafe that does exactly this: it checks DDL and DML for dangerous patterns so that developers (and AI) don’t blow up the database. I’m literally teaching the machine everything I know. At some point, someone will say “if the tool catches all the problems, why do we need the DBA?”

It’s uncomfortable. But Terraform exists and infrastructure engineers didn’t disappear. Kubernetes exists and SREs didn’t disappear. The tool automates the execution, but someone still has to decide what to execute and why.

dbsafe started by detecting DDL algorithms and warning about table locks. Then I had to add topology awareness for PXC clusters. Then foreign key impact analysis. Then Aurora and RDS support. Each one was a new guardrail that only existed because production taught me a new lesson.

And then there’s work no tool covers. Migrating a fleet of MySQL master-slave clusters to Percona XtraDB Cluster with ProxySQL: months of planning, fixing tables with no primary key, building monitoring dashboards, writing failover runbooks, dealing with the 15 things that go wrong that you didn’t plan for. Could Claude Code help with parts of that? Probably yes. Could it plan and execute the whole thing? No. Half of it is judgment calls that depend on knowing your specific infrastructure.

The tool catches what you taught it to catch. It doesn’t catch what you haven’t seen yet. I’d rather be the person who built the guardrail than the person who gets called at 3 AM because nobody did.

The elephant in the room: what about juniors?

Salesforce announced it would halt junior hiring for 2025, saying AI made them productive enough. Then they reversed course when the strategy failed. That reversal matters.

Still, the landscape is not great. A Stanford/ADP study found developers aged 22-25 lost nearly 20% of their jobs since late 2022. SignalFire shows new grad hiring at the top 15 tech companies is down over 50% from pre-pandemic levels. LeadDev found 54% of engineering leaders expect junior hiring to go down because of AI tools.

But that logic has a problem: if you don’t hire juniors today, you won’t have seniors tomorrow. As the Stack Overflow blog put it: “if you don’t hire junior developers, you’ll someday never have senior developers.”

The Claude Code Engineer role is a new entry point. Instead of “write this CRUD endpoint,” it becomes “review this CRUD endpoint that Claude Code wrote, check the edge cases, make sure the docs match.” Different skill. Learnable. And I’d say it teaches engineering judgment faster than writing boilerplate ever did.

The other elephant: what about seniors?

“If a mid-level person can review AI output, why are we paying senior salaries?”

Some companies will try this. They will fire seniors, hire cheaper people to “operate” AI tools, and call it efficiency. It will blow up.

The Claude Code Engineer reviews the output. But someone has to define the architecture, build the skills and plugins (the Toolsmith), break down the roadmap into good issues (the Issue Architect), and make the call when the AI proposes something that breaks a constraint only 10 years of production experience would catch. That’s the senior.

The senior’s value goes up in this model. Their architecture decisions shape every feature that every Claude Code Engineer ships. Their output is no longer measured in lines of code. It’s measured in how many bad decisions they prevented the AI from making.

Companies that cut seniors will ship fast and break things. Companies that keep seniors and give them leverage will ship fast and break less.

What this looks like in practice

Imagine a team of five:

• 1 Issue Architect who breaks down the roadmap into well-scoped parallel issues
• 2 Claude Code Engineers who each run 3-5 parallel worktrees, reviewing plans, enforcing quality, and merging
• 1 Toolsmith who builds the internal skills and safety checks
• 1 senior engineer who handles system design, incident response, performance tuning, security reviews

That team of five ships what used to require fifteen. Not because the other ten got fired, but because the work changed shape.

The inevitable objection

“But what if Claude Code gets good enough that you don’t need the Engineer?”

Maybe. Eventually. But the answer to “will we still need someone to verify the work?” has always been yes. We automated deployments and still need SREs. We automated testing and still need QA engineers. We automated monitoring and still need on-call rotations.

The tools change. The responsibility doesn’t.

The backlog is infinite. It always has been.

Aurora MySQL uses a shared-storage architecture. The Aurora overview describes reader instances as connecting “to the same storage volume as the primary DB instance” rather than replaying binlog events. This architecture means gh-ost requires a complex cross-cluster replication setup and specific parameter changes to function on Aurora, rather than the straightforward single-cluster operation you get on standard MySQL.

dbsafe detects Aurora automatically and steers you toward pt-osc, which uses DML triggers and standard SQL rather than binlog streaming. No cross-cluster setup required.

This is part of the dbsafe series. Introducing dbsafe covers installation and full feature overview. When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL covers which operations trigger a full table rebuild, the context that makes tool selection critical. Foreign Keys and Schema Changes covers another case where gh-ost is excluded.

Connecting with TLS

Cloud MySQL endpoints typically require encrypted connections. dbsafe supports TLS natively via the --tls flag:

dbsafe plan \
  -H my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
  -u dbsafe_ro \
  --tls required \
  -d myapp \
  "ALTER TABLE orders ADD COLUMN fulfillment_id VARCHAR(50)"

The --tls flag accepts five modes:

For AWS environments, required is the minimum. For strict certificate verification (recommended for production), use custom with the --tls-ca flag pointing to the AWS RDS CA bundle:

dbsafe plan \
  -H my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
  -u dbsafe_ro \
  --tls custom \
  --tls-ca /path/to/aws-rds-global-bundle.pem \
  -d myapp \
  "ALTER TABLE orders ADD COLUMN fulfillment_id VARCHAR(50)"

The --tls-ca flag loads the CA certificate file and verifies the server’s certificate chain against it. This is the equivalent of MySQL’s --ssl-ca option. AWS publishes a global CA bundle that covers all commercial AWS regions. Download it once and reference it in your dbsafe config profile.

Aurora Auto-Detection

When dbsafe connects to a MySQL instance, it checks the basedir system variable. Aurora instances have a distinctive basedir that contains the Aurora version string:

SELECT @@version, @@basedir;
-- @@version = 8.0.28
-- @@basedir = /rdsdbbin/oscar-8.0.mysql_aurora.3.04.0.0.32961.0/

Note that @@version returns the MySQL compatibility version (8.0.28), not the Aurora version. The Aurora version string only appears in basedir. dbsafe parses it and extracts:

• Flavor: aurora-mysql (detected from the mysql_aurora substring in basedir)
• Aurora version: 3.04.0 (extracted from the basedir path)
• Effective MySQL version: 8.0.28 (from @@version), used for algorithm detection (INSTANT DDL eligibility, etc.)

This distinction matters because Aurora’s MySQL compatibility is not always the latest patch. According to the Aurora MySQL version mapping, Aurora 3.04.0 is compatible with MySQL 8.0.28. If a DDL feature was introduced in MySQL 8.0.29 (like INSTANT ADD COLUMN at any position), dbsafe correctly reports that the feature is unavailable on this Aurora version, even though the base MySQL 8.0 branch supports it.

Here’s dbsafe plan against an Aurora Writer for a COPY-algorithm operation:

DBSAFE_PASSWORD=mypassword dbsafe plan \
  -H my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com \
  -u dbsafe_ro \
  --tls required \
  -d myapp \
  "ALTER TABLE orders MODIFY COLUMN status VARCHAR(100)"

The output shows the Aurora flavor, the effective MySQL version used for algorithm detection, and the topology. For a COPY operation, gh-ost is excluded and pt-osc is recommended, with the reason displayed inline.

Aurora Writer vs Reader Detection

The Aurora MySQL best practices documentation recommends checking the innodb_read_only global variable to determine whether you are connected to a writer or reader instance:

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

The variable is OFF on the writer and ON on reader instances. dbsafe queries this variable and reports the topology:

• Aurora Writer: the instance accepting writes. This is where DDL should run.
• Aurora Reader: a read-only instance. DDL executed here will fail.

When dbsafe detects an Aurora Reader, it surfaces a warning: run your schema change on the Writer endpoint instead. This catches a common mistake, connecting to the reader endpoint (the -ro suffix in the DNS name) when you meant to use the cluster endpoint.

# Connecting to the reader endpoint by mistake
DBSAFE_PASSWORD=mypassword dbsafe plan \
  -H my-cluster.cluster-ro-abc123.us-east-1.rds.amazonaws.com \
  -u dbsafe_ro \
  --tls required \
  -d myapp \
  "ALTER TABLE orders ADD COLUMN priority INT"

The analysis still runs. You see the algorithm, risk, and table metadata, but the warning makes it clear that executing this DDL here would fail. The Reader endpoint is for SELECT queries and read-only analysis, not schema changes.

Why gh-ost Needs Special Handling on Aurora

gh-ost’s architecture depends on binlog streaming to capture row changes. As the gh-ost docs describe, it “pretends to be a MySQL replica: it connects to the MySQL server and begins requesting for binlog events as though it were a real replication server.” When the shadow table is caught up, it performs a cut-over swap.

On traditional MySQL replication, this works because the binlog is the authoritative source of changes. Replicas apply binlog events to stay in sync. gh-ost taps into the same stream.

On Aurora, the architecture is different. The Aurora overview describes reader instances as connecting “to the same storage volume as the primary DB instance.” The Aurora replication documentation notes that updates “are visible to all Aurora Replicas with minimal replica lag, usually much less than 100 milliseconds after the primary instance has written an update.” This replication happens through the shared storage layer, not through binlog replay.

The gh-ost RDS documentation describes the specific obstacles on Aurora:

1. Binlog filtering: Aurora enables aurora_enable_repl_bin_log_filtering by default. The gh-ost docs explain the consequence: “gh-ost waits for an entry in the binlog to proceed but this entry will never end up in the binlog because it gets filtered out.” You must set this parameter to 0 before running gh-ost and restore it to 1 afterward.

2. Master detection: gh-ost detects it is running on the master even when connected to a reader endpoint, because all Aurora instances share the same storage. The workaround requires setting up a separate Aurora cluster configured as a binlog replica, following the Aurora cross-cluster replication documentation.

3. Preflight requirements: the gh-ost RDS docs list a preflight checklist including a secondary cluster, consistent parameters, verified replication status, and backup retention exceeding 1 day.

This is not a simple “add two flags” situation like RDS standalone. It requires provisioning infrastructure (a second Aurora cluster), changing Aurora parameters, and managing cross-cluster replication state.

dbsafe detects aurora-mysql as the flavor and excludes gh-ost from the tool recommendation. pt-osc, which creates DML triggers on the original table and uses standard SQL INSERT/UPDATE/DELETE statements to populate the shadow table (as described in the pt-osc documentation: “the tool creates triggers on the original table to update the corresponding rows in the new table”), operates on Aurora without any special infrastructure because those SQL statements go through Aurora’s storage layer like any other application write.

RDS Standalone Detection

For non-Aurora RDS instances (standard MySQL on RDS), dbsafe performs best-effort detection by checking the basedir system variable. RDS instances have basedir set to a path containing rdsdbbin:

-- On RDS:
SELECT @@basedir;
-- /rdsdbbin/mysql-8.0.45.R3/

When dbsafe detects this pattern, it sets a cloud-managed flag. The implications for schema changes:

• No SSH access: you cannot install or run gh-ost on the RDS host itself. gh-ost must run from an external host with --allow-on-master.
• No SUPER privilege: RDS does not grant SUPER. The gh-ost requirements document that you can use --assume-rbr to avoid the STOP SLAVE/START SLAVE operations that require SUPER, as long as your replication is already in binlog_format=ROW.
• pt-osc works without extra configuration: it connects as a regular MySQL client and uses DML triggers. No special flags needed for RDS.

Here’s dbsafe plan against an RDS standalone instance:

DBSAFE_PASSWORD=mypassword dbsafe plan \
  -H mydb.abc123.us-east-1.rds.amazonaws.com \
  -u dbsafe_ro \
  --tls required \
  -d myapp \
  "ALTER TABLE orders MODIFY COLUMN status VARCHAR(100)"

Unlike Aurora, gh-ost is not excluded on RDS standalone. The gh-ost RDS documentation confirms that “gh-ost has been updated to work with Amazon RDS.” dbsafe shows both gh-ost and pt-osc commands, with the gh-ost command including the additional flags needed for RDS.

Configuration Profiles for Cloud

Typing --tls required --tls-ca /path/to/cert.pem on every command is tedious. dbsafe supports configuration profiles in ~/.dbsafe/config.yaml that store connection defaults:

connections:
  aurora-prod:
    host: my-cluster.cluster-abc123.us-east-1.rds.amazonaws.com
    port: 3306
    user: dbsafe_ro
    database: myapp
    tls: required
    tls_ca: /path/to/aws-rds-global-bundle.pem

  rds-staging:
    host: staging.abc123.us-east-1.rds.amazonaws.com
    port: 3306
    user: dbsafe_ro
    database: myapp_staging
    tls: required

defaults:
  format: text

With this config, your daily workflow becomes:

# Analyze against Aurora production (TLS and CA handled by the profile)
DBSAFE_PASSWORD=mypassword dbsafe plan --connection aurora-prod \
  "ALTER TABLE orders ADD COLUMN fulfillment_id VARCHAR(50)"

# Same analysis against RDS staging
DBSAFE_PASSWORD=mypassword dbsafe plan --connection rds-staging \
  "ALTER TABLE orders ADD COLUMN fulfillment_id VARCHAR(50)"

The password is passed via the DBSAFE_PASSWORD environment variable, never stored in the config file. For CI/CD pipelines, pull it from your secrets manager (AWS Secrets Manager, Vault, etc.) into the environment before invoking dbsafe.

Practical Workflow for Cloud Schema Changes

1. Set up a config profile for each environment (production Aurora, staging RDS, etc.) with TLS and CA certificate paths. Do this once.

2. Run dbsafe plan against the production Writer endpoint. Verify the topology shows Aurora Writer (not Reader) and the effective MySQL version matches your expectations.

3. Check the algorithm. INSTANT operations are safe on Aurora. They complete in milliseconds just like on standalone MySQL. The shared storage architecture doesn’t affect metadata-only changes.

4. For COPY operations, use pt-osc. gh-ost is excluded on Aurora because the cross-cluster setup it requires is impractical for routine schema changes. dbsafe generates the pt-osc command pre-populated with your connection parameters. If the table has triggers, --preserve-triggers is included automatically.

5. For RDS standalone COPY operations, choose your tool. Both gh-ost and pt-osc work, but gh-ost requires --allow-on-master and --assume-rbr. dbsafe includes these flags in the generated command. pt-osc works without extra configuration.

6. Never run DDL on a Reader endpoint. dbsafe warns you, but the best practice is to always use the cluster endpoint (which routes to the Writer) rather than a specific instance endpoint.

7. For CI/CD gates, use dbsafe plan --format json with the cloud profile:

RESULT=$(DBSAFE_PASSWORD="$DB_PASSWORD" dbsafe plan \
  --connection aurora-prod \
  --format json \
  "ALTER TABLE orders ADD COLUMN fulfillment_id VARCHAR(50)")

ALGORITHM=$(echo "$RESULT" | jq -r '.algorithm')
if [ "$ALGORITHM" != "INSTANT" ]; then
  echo "Non-INSTANT DDL on Aurora: requires pt-osc migration plan"
  exit 1
fi

Summary

1. TLS is standard for cloud MySQL. dbsafe supports five TLS modes via --tls, with --tls-ca for custom CA certificate verification against the AWS RDS CA bundle. Use required at minimum; custom with the CA bundle for full certificate verification.

2. Aurora auto-detection parses the basedir variable (which contains mysql_aurora and the Aurora version) to identify the flavor. The effective MySQL version comes from @@version. The Aurora version mapping determines INSTANT DDL eligibility based on the effective MySQL version, not the Aurora release number.

3. Aurora Writer/Reader topology is detected via the innodb_read_only variable, as recommended by AWS. Reader instances get a warning: DDL must run on the Writer. This catches the common mistake of connecting to the -ro endpoint.

4. gh-ost requires a complex setup on Aurora. The gh-ost RDS documentation documents three obstacles: default binlog filtering that blocks gh-ost events, master detection on all endpoints due to shared storage, and the need for a separate cross-cluster replication target. dbsafe excludes gh-ost on Aurora and recommends pt-osc, which works with standard SQL and requires no special infrastructure.

5. RDS standalone detection is best-effort via the basedir variable. gh-ost works on RDS but requires --allow-on-master and --assume-rbr. dbsafe includes these in the generated command. pt-osc works without extra configuration.

6. Configuration profiles in ~/.dbsafe/config.yaml store host, TLS, and CA paths per environment. Passwords go in DBSAFE_PASSWORD, never in the config file.

References

AWS Documentation:

• Amazon Aurora Overview
• Aurora Replication
• Aurora MySQL Version Mapping
• Aurora MySQL Best Practices
• Using SSL/TLS with RDS
• RDS CA Certificate Bundle

Tools:

• dbsafe, GitHub Repository
• pt-online-schema-change, Percona Toolkit
• gh-ost, GitHub’s Online Schema Migration Tool
• gh-ost Triggerless Design
• gh-ost on RDS and Aurora
• gh-ost Requirements and Limitations

Related Posts:

• Introducing dbsafe: Know Before You ALTER
• Zero-Downtime Schema Changes with INSTANT DDL
• When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL
• Foreign Keys and Schema Changes: The Constraint You Didn’t Plan For

That’s like saying a restaurant “stores food.”

Technically true. Completely misses the point.

A restaurant has to cook fast, serve many tables at once, and not poison anyone. Fail any one of those three and it doesn’t matter how good the kitchen looks. A database has the same problem — except the stakes are your production system at 2am.

A few years ago I gave a talk at Percona Live in Denver where I tried to answer this properly. Not from a features list. Not from a vendor slide deck. From first principles: what does a database have to do?

Three things. Everything else — every configuration parameter, every architecture decision, every incident you’ve ever fought — falls into one of them.

Execute Queries

A restaurant has one core job: take an order and bring food to the table. Fast, correct, and for as many tables as possible simultaneously.

A database has the same job. Answer questions about data. Record changes. As fast as possible, as many as possible, without corrupting anything in the process.

That last part is the one that gets sacrificed first when you’re optimizing for speed. InnoDB’s entire machinery — the buffer pool, the redo log, the doublewrite buffer — exists to make sure “fast” and “correct” happen at the same time. ACID isn’t a marketing term. It’s the contract the database makes with every query it executes.

The tension is real. Disabling foreign_key_checks before a bulk load makes the operation faster. It also removes a correctness guarantee while it’s disabled. That tradeoff isn’t inherently wrong — but you can only make it deliberately if you understand what you’re trading. If you’re curious about the hidden consequences of foreign keys, I covered one particularly dangerous scenario in the ON DELETE CASCADE blind spot in MySQL’s binary log.

When a query is slow, the reflex is to reach for indexes. Sometimes that’s right. But a query can also be slow because lock contention is serializing execution, because the working set stopped fitting in the buffer pool, or because something upstream is flooding the connection pool. Same symptom, completely different root causes, completely different solutions. Knowing the responsibility narrows the search. Understanding InnoDB semaphore contention is one way to tell lock contention apart from other causes.

Relationships

No database is an island.

Think of it like a person who has three very different kinds of relationships in their life — and does a bad job with any one of them at their own peril.

With users, the relationship is trust and boundaries. Who gets in, what they can see, what they can touch. MySQL’s account model — hosts, privileges, roles — is the entire machinery for this. When someone asks why the application can’t just run as root, this is why. The database has a responsibility to protect data from people and systems that shouldn’t have it. That responsibility doesn’t disappear because setting it up is inconvenient.

With other databases, the relationship is coordination. A replica trusts that the primary is sending it a faithful copy of reality. A PXC node trusts that the other nodes in the cluster will agree on the same writes. When wsrep_local_recv_queue starts climbing, the cluster is telling you a relationship is under stress — one node can’t keep up with what the others are sending. It’s a relationship problem before it’s a performance problem. Treating it as a performance problem first is how you end up chasing the wrong metric.

With dev and ops teams, the relationship is communication. Logs, status variables, Performance Schema — this is how the database talks. When you skip configuring the slow query log because it adds overhead, you’re choosing silence. You’ll regret that choice during the next incident, when you’re flying blind trying to reconstruct what happened. Tools like PMM Query Analytics exist precisely to bridge this communication gap.

A database that executes queries correctly but can’t communicate its state, can’t cooperate with peers, and can’t enforce who has access — is a ticking clock.

Survive

This is the one nobody talks about at conferences, and it’s the one that kills you.

A database doesn’t run in the cloud. It runs on a machine. A machine with a CPU that can be saturated, memory that can be exhausted, and a disk that fills up and then — not slowly degrades, but stops. Full disk doesn’t slow MySQL down. It stops it cold.

Think of it like a tenant who has to know the rules of the building they live in. The landlord — the OS — controls memory allocation, file descriptors, I/O scheduling. The tenant can push their luck, but only so far before the landlord intervenes. An OOM kill at 3am is the landlord evicting a tenant who was using more than their share.

innodb_buffer_pool_size is the most important negotiation a MySQL server has with its host machine. Too low and you’re leaving performance on the table. Too high on a box running other processes and you’re gambling that the OS won’t reclaim that memory mid-write. That configuration parameter isn’t a performance knob. It’s a survival decision.

Disk is more insidious. A table that grows 100MB per day doesn’t look dangerous today. In six months it’s 18GB. The database won’t warn you. It will just stop one day. The monitoring that watches disk growth trends and alerts before the cliff — that’s not operational overhead. That’s the database fulfilling its responsibility to survive the physical world it lives in. Setting up smart alerting with dynamic thresholds is how you catch these slow-moving threats.

Backups live here too. A database that can’t be recovered after a failure didn’t survive. Full stop.

Why This Framework Matters

These three categories won’t tell you how to fix anything. They’re not a checklist. What they give you is a way to locate a problem before you start solving it — and that matters more than most people admit.

Replica falling behind? Three possible zip codes:

• Execute Queries — the primary is running queries so heavy that the replica can’t replay them fast enough
• Relationships — the network between primary and replica can’t carry the replication stream
• Survive — the replica’s disk I/O is the bottleneck

Same symptom. Three completely different tools. If you go straight to tuning queries when the real problem is disk throughput on the replica, you will waste hours.

The framework doesn’t solve the problem. It tells you which drawer to open first.

Every decision you make as a DBA is in service of one of these three things. Execute queries correctly and fast. Manage relationships with users, peers, and teams. Survive the physical constraints of the machine it runs on.

That’s the whole job.

I first presented this framework at Percona Live in Denver. The talk was aimed at DBAs, but I’ve always believed that database fundamentals should be explainable to anyone — and that explaining them clearly forces a deeper understanding than talking only to specialists.

Foreign key constraints surface in two places during schema changes: in the metadata locks MySQL acquires for the duration of the DDL, and in the tool selection available to you. Understanding both before you run is exactly what dbsafe plan is for.

This is the fifth post in the dbsafe series. Introducing dbsafe covers installation and the full feature overview. When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL covers the operations that require a full table rebuild — the context you need before this post.

What dbsafe Detects

When analyzing a table, dbsafe queries information_schema.KEY_COLUMN_USAGE and information_schema.REFERENTIAL_CONSTRAINTS to surface every FK relationship involving the target table. The output shows the referenced tables and columns that the target table depends on.

Here’s dbsafe plan on order_items, a child table with a FK pointing to orders:

dbsafe plan -H 127.0.0.1 -P 23306 -u dbsafe -d demo \
  "ALTER TABLE order_items ADD COLUMN note_text VARCHAR(500)"

The FK refs line in the output shows the count and the referenced tables and columns — orders.id and products.id. At a glance you can see which parent tables this child depends on. Surfacing this requires querying information_schema separately; dbsafe includes it automatically in every analysis.

FK metadata appears regardless of the DDL algorithm. Even an INSTANT operation like ADD COLUMN surfaces the FK relationships, so you understand the full constraint context before changing anything.

Metadata Locks Extend to Related Tables

The MySQL documentation on metadata locking states:

“Metadata locks are extended, as necessary, to tables related by a foreign key constraint to prevent conflicting DML and DDL operations from executing concurrently on the related tables. When updating a parent table, a metadata lock is taken on the child table while updating foreign key metadata. Foreign key metadata is owned by the child table.”

The FOREIGN KEY Constraints reference adds the lock type detail:

“If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.”

In practice:

• ALTER TABLE on the child table acquires a metadata lock on the parent table for the duration of the DDL. Any transaction on the parent that hasn’t committed must complete first. New transactions that need to modify the parent must wait.
• ALTER TABLE on the parent table works in reverse: the lock protects the child table’s FK references from being invalidated while the parent schema is changing.

The Online DDL Operations reference documents the specific wait conditions that arise from CASCADE and SET NULL rules:

“An ALTER TABLE on the child table could wait for another transaction to commit, if a change to the parent table causes associated changes in the child table through an ON UPDATE or ON DELETE clause using the CASCADE or SET NULL parameters.”

And in the other direction:

“In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any FOREIGN KEY clauses, it could wait for the ALTER TABLE to complete if an INSERT, UPDATE, or DELETE statement causes an ON UPDATE or ON DELETE action in the child table.”

The practical consequence: an ALTER TABLE on order_items can block or be blocked by concurrent DML on orders, and vice versa — even though you’re only changing one table. On high-write systems this lock interaction can cause visible application latency during the DDL window. dbsafe’s FK section makes this non-obvious relationship explicit before you schedule the operation.

The COPY Algorithm on FK-Constrained Tables

When the DDL algorithm is COPY — a full table rebuild — the FK relationship adds a validation layer to every row written into the shadow table.

InnoDB enforces FK constraints on every write operation. With foreign_key_checks set to ON (the default), every row inserted into the shadow table during the COPY phase is subject to the same referential validity check that applies to any INSERT against the table. The Online DDL Operations documentation captures the dependency between FK enforcement and the algorithm available:

“The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.”

This is stated in the context of ADD FOREIGN KEY, but it reflects the general principle: FK enforcement and COPY algorithm are coupled. On a child table with millions of rows and a FK pointing to a large parent, the COPY phase performs a referential validity check alongside every row copy.

The FOREIGN KEY Constraints reference describes what disabling foreign_key_checks actually bypasses:

“When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions: [recreating a dropped table must still conform to FK definitions referencing it; incorrectly formed FK definitions still return errors; dropping an index required by a FK constraint requires removing the FK first].”

Disabling foreign_key_checks removes referential integrity enforcement for the duration of the operation. On large tables where the validation overhead is measurable, it’s a tradeoff: faster rebuild, no constraint protection if the operation is interrupted or if concurrent DML inserts during the rebuild window.

gh-ost Does Not Support FK-Constrained Tables

This is where the FK impact is most operationally significant: gh-ost refuses to operate on tables involved in FK relationships by default.

The gh-ost requirements and limitations documentation is explicit:

“Foreign key constraints are not supported. They may be supported in the future, to some extent.”

The restriction applies in both directions:

• Child-side: a table that has a FOREIGN KEY ... REFERENCES clause — like order_items with its FK to orders
• Parent-side: a table that is referenced by other tables’ FK constraints — like orders referenced by order_items

Both cause gh-ost to abort. Two escape hatches exist: --skip-foreign-key-checks (bypasses the detection check entirely, with a logged warning) and --discard-foreign-keys (allows child-side FK tables if you accept that the FK constraint will be dropped on the ghost table and not recreated). Neither is appropriate for production use when referential integrity must be maintained.

The operational result: any table involved in a FK relationship requires pt-osc. dbsafe detects FK constraints and routes the tool recommendation accordingly.

Here’s dbsafe plan on the parent orders table, which is referenced by order_items:

dbsafe plan -H 127.0.0.1 -P 23306 -u dbsafe -d demo \
  "ALTER TABLE orders MODIFY COLUMN total_amount DECIMAL(14,4)"

The output surfaces two reasons gh-ost is excluded: the orders table has triggers (trg_orders_after_update, trg_orders_after_delete) — covered in the COPY algorithm post — and it is referenced by FK constraints in child tables. Either condition alone is sufficient to exclude gh-ost. pt-osc is the only viable tool.

pt-osc and –alter-foreign-keys-method

When pt-osc alters a parent table, the rename swap at the end creates a problem: child tables still have FK constraints that reference the original table name. After the rename, those constraints point to a non-existent table.

The pt-online-schema-change documentation describes three modes for --alter-foreign-keys-method:

rebuild_constraints — the safe default:

“This method uses ALTER TABLE to drop and re-add foreign key constraints that reference the new table. This is the preferred technique, unless one or more of the ‘child’ tables is so large that the ALTER would take too long.”

Note from the pt-osc docs: “Due to a limitation in MySQL, foreign keys will not have the same names after the ALTER that they did prior to it.” The names get an underscore prefix. If your application or monitoring refers to FK constraint names explicitly, update those references after the migration.

drop_swap — faster, but riskier:

“Disable foreign key checks (FOREIGN_KEY_CHECKS=0), then drop the original table before renaming the new table into its place. This is different from the normal method of swapping the old and new table, which uses an atomic RENAME that is undetectable to client applications.”

Between the drop and the rename, the original table does not exist. Queries against it fail during that window. If the rename fails, the original table is gone. The pt-osc docs describe this as riskier for exactly these two reasons.

none — requires manual follow-up:

“This method is like drop_swap without the ‘swap’. Any foreign keys that referenced the original table will now reference a nonexistent table.”

This is explicitly for administrators who intend to handle FK re-pointing manually. The resulting state after the migration is broken until that manual step is performed.

When dbsafe generates a pt-osc command for a parent table COPY operation, rebuild_constraints is the right default unless the child tables are so large that the secondary ALTER TABLE to drop/recreate FKs falls outside your maintenance window. In that case, drop_swap trades a brief availability gap for faster completion — a deliberate tradeoff, not a safe shortcut.

A Note on ON DELETE CASCADE During the Migration Window

If the parent table has ON DELETE CASCADE relationships to child tables, there is an additional consideration during the migration window. When pt-osc runs:

1. pt-osc installs AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the original table to capture DML and replay it on the ghost table.
2. When a row is deleted from orders, the trigger fires and pt-osc replicates the delete to the ghost table — correctly.
3. But the ON DELETE CASCADE to order_items fires inside the InnoDB storage engine, below the SQL layer, and therefore below pt-osc’s triggers. The cascaded deletions on order_items are invisible to pt-osc’s change tracking on orders.

This is the same architectural limitation documented in ON DELETE CASCADE: The Foreign Key Change MySQL Doesn’t Log. In the context of a parent table migration, pt-osc’s tracking of orders is complete; it’s the shadow order_items data that may drift if CASCADE fires during the copy phase.

Whether this matters depends on your schema. If you’re only migrating orders and order_items is not being migrated simultaneously, the drift affects data in order_items (the real table) — not the shadow table being built for orders. In most cases this is informational. But if you’re orchestrating coordinated schema changes across both tables at once, sequencing and the migration window need careful planning.

Practical Workflow for FK-Constrained Tables

1. Run dbsafe plan on every table in the FK relationship, not just the one you’re altering. The metadata lock extension means the parent table’s DDL window affects child table DML and vice versa. Knowing both sides before scheduling is necessary for accurate impact assessment.

2. Accept that gh-ost is unavailable for any table with FK relationships. pt-osc is the tool. This is not a dbsafe preference — it reflects gh-ost’s documented limitation.

3. Check the algorithm. If INSTANT, the FK impact is limited to the MDL extension discussed above. If COPY, FK constraint validation adds overhead to the row copy phase. The larger the tables involved, the more significant the overhead.

4. Review --alter-foreign-keys-method for parent table changes. The rebuild_constraints default is safe. Before using drop_swap, verify you can tolerate the brief availability gap. Never use none unless you have a manual FK repair step ready to run immediately after the migration completes.

5. For type changes that cascade through FK columns — such as migrating INT primary keys to BIGINT — each table in the FK chain requires its own migration. The child FK column type must match the parent PK type. Plan each migration separately, verify referential integrity between steps, and do not attempt both simultaneously.

Summary

1. dbsafe surfaces FK relationships automatically from information_schema.KEY_COLUMN_USAGE and information_schema.REFERENTIAL_CONSTRAINTS. The FK section appears in every analysis regardless of DDL algorithm.

2. MySQL extends metadata locks to FK-related tables for the duration of the DDL. The metadata locking documentation and the Online DDL Operations reference document specific wait conditions for CASCADE and SET NULL rules — wait conditions that affect both the child table DDL and the parent table DDL.

3. COPY algorithm + FK constraints means InnoDB applies its standard FK enforcement to every row written into the shadow table during the rebuild. Disabling foreign_key_checks removes that overhead but also removes referential integrity protection for the duration.

4. gh-ost refuses FK-constrained tables by default — both parent-side and child-side. The gh-ost documentation is explicit: “Foreign key constraints are not supported.” pt-osc is required for any table in a FK relationship.

5. For parent table migrations with pt-osc, --alter-foreign-keys-method controls how child FK references are updated after the rename swap. rebuild_constraints is the safe default. drop_swap is faster but introduces a brief window where the original table does not exist. none leaves child FKs pointing to a non-existent table until manually repaired.

References

MySQL Official Documentation:

• Metadata Locking — MySQL 8.0
• FOREIGN KEY Constraints — MySQL 8.0
• Online DDL Operations — MySQL 8.0
• INFORMATION_SCHEMA KEY_COLUMN_USAGE Table — MySQL 8.0
• INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table — MySQL 8.0

Tools:

• pt-online-schema-change — Percona Toolkit
• gh-ost Requirements and Limitations
• dbsafe — GitHub Repository

Related Posts:

• Introducing dbsafe: Know Before You ALTER
• When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL
• ON DELETE CASCADE: The Foreign Key Change MySQL Doesn’t Log

Mostly yes. But there’s one case where MySQL is completely silent: foreign key cascades.

And the silence isn’t just in the binlog. It’s in the audit plugin and in your triggers too. Three blind spots, same root cause.

What ROW + FULL actually captures

When you use row-based logging, the server records the effect of every DML operation — not the statement itself. The MySQL docs on stored program binary logging are clear about this:

For stored procedures, the CALL statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation.

The same applies to triggers and events. The code doesn’t appear in the binlog — the row changes do. That’s good. With binlog_row_image=FULL, each row event contains every column of the row, before and after the change.

So for direct DML, stored procedures, triggers, stored functions, prepared statements, LOAD DATA INFILE, and Event Scheduler events — you’re fully covered.

Foreign key cascades are the exception.

The cascade blind spot

When you delete a row from a parent table and InnoDB cascades that delete to child rows, only the parent deletion appears in the binlog. The child table deletions are nowhere to be found.

This is documented as a known limitation in the MySQL 8.0 Reference Manual, section 15.1.20.5.

Here’s what the binlog actually shows for a cascaded delete:

| binlog.000003 | 354 | Table_map    | table_id: 90 (orders)      |
| binlog.000003 | 410 | Delete_rows  | table_id: 90               |
| binlog.000003 | 459 | Xid          | COMMIT                     |

If orders has a child table order_items with ON DELETE CASCADE, every deletion in order_items is missing from that output. The binlog tells you the parent row is gone, and nothing else.

The reason is architectural. InnoDB handles cascade enforcement entirely inside the storage engine. When InnoDB processes a DELETE on the parent table, it finds the matching child rows and removes them internally — without ever surfacing those operations back to the SQL layer. The binary log is written by the SQL layer. If the SQL layer doesn’t see it, the binlog doesn’t record it.

This behavior is the same whether you’re using statement-based or row-based replication. Changing binlog_format doesn’t help.

And it gets worse: triggers don’t fire either

Before reaching for triggers as a workaround, there’s a second problem. Triggers on the child table also don’t fire when rows are deleted by a cascade.

The MySQL 8.0 Reference Manual section on CREATE TRIGGER states this directly:

Cascaded foreign key actions do not activate triggers.

The same sentence appears in the FOREIGN KEY Constraints section and has been consistent across every MySQL version from 5.5 to 8.4. It was first reported as bug #11472 back in 2005 and remains open as of MySQL 8.x.

So if you had a AFTER DELETE trigger on order_items to write to an audit table — it won’t fire when the cascade removes rows. It will only fire if rows are deleted directly with an explicit DELETE statement.

And the audit plugin too

The MySQL Enterprise Audit plugin, the Percona Audit Log plugin, and MariaDB-based audit plugins all intercept events at the SQL layer — the same layer the binlog is written at. Since cascade operations never reach the SQL layer, they are invisible to every audit plugin as well.

The complete picture in MySQL 8.x:

The first five are confirmed blind spots — all operate at or above the SQL layer, and the cascade never reaches them.

The last one is more nuanced. table_io_waits_summary_by_table instruments the wait/io/table/sql/handler instrument, which hooks into the handler API (ha_delete_row, ha_write_row, etc.) at a lower level than statements. In theory, if InnoDB called ha_delete_row for each cascaded row, Performance Schema would count it. But in practice, InnoDB’s cascade implementation calls internal row deletion functions directly, bypassing the standard handler interface that Performance Schema instruments. The architectural expectation is that this is also a blind spot, but I haven’t found explicit documentation confirming it for this specific table — and it’s worth verifying empirically in your environment before relying on it for anything compliance-related.

The only layer with complete visibility into cascade operations is InnoDB itself — in the undo log and the tablespace pages.

Verifying it yourself

You can confirm this in minutes. Set up a simple parent/child relationship and watch the binlog:

CREATE TABLE orders (
  id INT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO orders VALUES (1);
INSERT INTO order_items VALUES (1, 1), (2, 1), (3, 1);

-- Now delete the parent
DELETE FROM orders WHERE id = 1;

Then check what made it into the binlog:

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000001

You’ll see one Delete_rows event for orders. Nothing for order_items, even though those three rows are gone.

The workaround: move the cascade to the SQL layer

The only clean solution in MySQL 8.x is to stop letting InnoDB handle the cascade and do it explicitly in the SQL layer instead. You keep the foreign key for referential integrity, but change the action to RESTRICT, and add a BEFORE DELETE trigger on the parent that performs the deletion on children explicitly:

-- Step 1: Change the FK to RESTRICT (no cascade)
ALTER TABLE order_items
  DROP FOREIGN KEY order_items_ibfk_1,
  ADD CONSTRAINT order_items_ibfk_1
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT;

-- Step 2: Add a trigger on the parent that does the deletion
DELIMITER //
CREATE TRIGGER before_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  DELETE FROM order_items WHERE order_id = OLD.id;
END //
DELIMITER ;

Now when you delete from orders, the trigger fires, issues an explicit DELETE on order_items, and that statement goes through the full SQL layer: it appears in the binlog, it fires any AFTER DELETE triggers on order_items, and the audit plugin captures it.

This comes with a tradeoff. InnoDB’s native cascade is highly optimized — it uses a depth-first search on the FK index and deletes child rows in a single internal pass. When you replace it with a trigger-based DELETE, that statement goes through the full SQL engine execution path. For tables with millions of child rows per parent, this can be measurably slower. Test it against your actual data volumes before deploying.

Data forensics when you can’t prevent it

If cascades already happened and you need to reconstruct what was deleted from child tables, it’s possible — but only if you have a backup predating the event.

The cascade is deterministic. Given the parent row’s primary key and the FK definition, you know exactly which child rows InnoDB would have removed. The process:

1. Parse the binlog to extract the PK values of deleted parent rows and their timestamps
2. Restore your last backup to a separate instance
3. Apply the binlog up to just before the delete event
4. Query the child tables filtering by the FK column — those results are exactly what the cascade removed
5. Repeat recursively if you have multi-level FK chains

If you don’t have a backup, the only path is low-level InnoDB recovery: undrop-for-innodb or manual tablespace page parsing. That’s a different category of work entirely and not guaranteed to succeed.

MySQL 9.6 finally fixes this

MySQL 9.6, released January 2026, addresses the root cause. Oracle moved foreign key enforcement and cascade execution from the InnoDB storage engine to the SQL engine layer. Now when a cascade fires, the SQL engine generates discrete DML statements for the child table operations, and those statements are logged to the binlog normally.

The same DELETE FROM orders WHERE id = 1 that previously produced one binlog event now produces events for both the parent deletion and all child table deletions — and audit plugins and triggers on child tables finally see them too.

For MySQL 8.x and Percona Server 8.x, this fix is not available. The architectural change is deep enough that a backport to 8.4 LTS would be a major engineering effort, and there’s no indication Percona plans to do it.

If you’re running Percona XtraDB Cluster 8.x and this gap matters for your compliance or CDC requirements, the trigger-based workaround described above is your best option today.

Summary

In MySQL 8.x, ON DELETE CASCADE and ON UPDATE CASCADE with InnoDB are handled entirely inside the storage engine, invisible to every observability layer that operates at or above the SQL layer:

• The binlog only records the parent table operation
• Audit plugins miss the child table changes entirely
• Triggers on child tables do not fire

The trigger-based workaround — replacing cascade FKs with RESTRICT and doing explicit child deletions in a BEFORE DELETE trigger on the parent — is the only way to get full visibility on MySQL 8.x. It’s more verbose and potentially slower at scale, but it makes the cascade operations first-class citizens in your binlog and audit logs.

MySQL 9.6 solves this at the architecture level. Until you get there, document the gap explicitly and design around it.

References:

• MySQL 8.0 Docs: FOREIGN KEY Constraints §15.1.20.5
• MySQL 8.0 Docs: CREATE TRIGGER §15.1.22
• MySQL 8.0 Docs: Stored Program Binary Logging §27.7
• MySQL Bug #32506: Foreign key cascades do not appear when binlog_format = ‘ROW’
• MySQL Bug #11472: Triggers not executed following foreign key updates/deletes
• Oracle MySQL Blog: No More Hidden Changes: How MySQL 9.6 Transforms Foreign Key Management

Do you have the rollback SQL written down?

Every schema change has a mirror operation. ADD COLUMN → DROP COLUMN. ADD INDEX → DROP INDEX. dbsafe generates that rollback SQL automatically as part of every plan output. Before you touch production, you already know the undo operation — and whether it’s actually reversible at all.

Related: This is part of the dbsafe series. Zero-Downtime Schema Changes with INSTANT DDL covers metadata-only operations that take milliseconds. When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL covers the dangerous end — full table rebuilds, exclusive locks, and when to use gh-ost or pt-osc.

The Two Kinds of Rollback

dbsafe’s rollback plan section covers two fundamentally different scenarios:

Structural rollbacks — operations where the inverse SQL is mechanically clean. Adding a column can be undone by dropping it. Adding an index can be undone by dropping it. These are reversible at the SQL level.

Data destruction — operations where there is no SQL rollback. DROP COLUMN permanently destroys the values stored in that column. A DROP COLUMN → ADD COLUMN pair restores the column definition but not the data. dbsafe surfaces a warning rather than a misleading rollback command.

Knowing which scenario you’re in — before you run the change — is what makes the difference between a prepared runbook and an incident.

ADD COLUMN: Clean Rollback

The simplest rollback case. Adding a column has a clean mirror: drop the same column. The schema returns to its original state.

dbsafe plan "ALTER TABLE orders ADD COLUMN fulfillment_notes TEXT"

dbsafe identifies this as INSTANT — a metadata-only change, no table rebuild, milliseconds on any table size. The rollback plan at the bottom of the report shows exactly what to run to undo it:

ALTER TABLE orders DROP COLUMN fulfillment_notes;

On MySQL 8.0.29+, that rollback is also INSTANT. The column is marked as dropped in the InnoDB data dictionary and the change commits in milliseconds — no rebuild, no rows touched.

One important caveat: the structural rollback only works cleanly if no data has been written to the column yet. If the application started populating fulfillment_notes between the ALTER TABLE and the rollback, that data disappears when the column is dropped. The SQL is correct, but it’s a schema rollback, not a data rollback. Backups cover the data; the rollback SQL covers the structure.

ADD INDEX: Clean Rollback

Index additions are even cleaner to roll back. ADD INDEX → DROP INDEX, with zero data loss. Indexes are derived structures built from existing row data — dropping one never removes a single byte of actual stored data.

dbsafe plan "ALTER TABLE orders ADD INDEX idx_total_amount (total_amount)"

The rollback plan shows:

ALTER TABLE orders DROP INDEX idx_total_amount;

The forward operation is INPLACE — MySQL reads all rows to build the index structure without creating a shadow copy of the table. The rollback is faster: dropping an index is a metadata change that doesn’t touch row data at all. If you realize the new index is causing query planner issues or unexpected lock contention, the DROP INDEX rollback is safe to run immediately under load.

DROP COLUMN: Data Is Gone

This is where the rollback plan becomes a warning.

dbsafe plan "ALTER TABLE orders DROP COLUMN fulfillment_notes"

dbsafe does not generate ADD COLUMN fulfillment_notes TEXT as the rollback. That SQL would restore the column definition but not the data stored in it across all existing rows. A misleading rollback is worse than no rollback — it creates the false impression that you can recover something you can’t.

Instead, the rollback section surfaces a clear warning: this operation permanently destroys data. After the commit, the only recovery path is restoring from a backup.

Before running any DROP COLUMN on a production table:

1. Confirm the column is genuinely unused — check application code, ORM models, stored procedures, and views
2. Verify you have a recent backup and you know how to restore from it
3. Consider a deprecation buffer: rename the column to _col_deprecated_YYYYMMDD and wait two full deployment cycles before dropping. If something breaks, you rename it back in milliseconds
4. On MySQL 8.0.29+, the physical bytes stay on disk until the next rebuild (OPTIMIZE TABLE or a COPY-algorithm ALTER) — but they’re completely inaccessible to MySQL and cannot be queried

The rename strategy is underused. It costs nothing operationally — a RENAME COLUMN is INSTANT on MySQL 8.0.28+ — and it buys you weeks of confidence before the irreversible step.

MySQL 8.0.29+: INSTANT Drop

On MySQL 8.0.29 or newer, DROP COLUMN executes as INSTANT DDL. The column is marked as dropped in the InnoDB data dictionary and the change commits in milliseconds, regardless of table size (with limited exceptions: ROW_FORMAT=COMPRESSED tables, tables with a FULLTEXT index, and tables that have exhausted the 64 INSTANT row-version limit).

The data destruction caveat is unchanged. INSTANT execution means the structural change is fast — it doesn’t mean the data is preserved or recoverable. What it does change for the rollback picture:

• No long-running ALTER to interrupt — with the COPY algorithm, the data-copy phase can take minutes or hours, giving you a window to kill the query before the final table swap. With INSTANT, the commit is atomic and immediate. There is no window to intervene after you press Enter.
• Physical bytes remain until the next rebuild — the column data is invisible to MySQL and cannot be accessed via SQL. A full table rebuild — via OPTIMIZE TABLE, ALTER TABLE ... FORCE, or ALTER TABLE ... ENGINE=InnoDB — is the only way to reclaim the disk space.

dbsafe surfaces a note when the DROP COLUMN will execute as INSTANT, so you can set correct expectations for both timing and the permanence of the change.

Rollback Plans in Practice

The rollback section is designed for one specific use case: runbook preparation.

When you’re preparing a change for a production deployment window, capture both the forward SQL and the rollback SQL in your runbook before the window opens. During the change, if something goes wrong, you paste from a document you reviewed hours earlier — not SQL you’re writing under pressure while the on-call pager fires.

The workflow:

1. Write your ALTER TABLE statement
2. Run dbsafe plan against your production server or a production-scale replica
3. Copy the rollback SQL into your runbook alongside the forward operation
4. Note the rollback category: structural (reversible) or destructive (backup required)
5. Execute the forward change during the window
6. If rollback needed: paste and run the rollback SQL immediately

For COPY-algorithm operations executed via gh-ost or pt-online-schema-change, the rollback window is wider. Both tools maintain the original table until the final cutover: gh-ost keeps the _orders_gho shadow table, pt-osc keeps _orders_new. Aborting before cutover leaves the original table completely untouched — the rollback is just stopping the tool. After cutover, you’re in the same position as a direct ALTER, and the dbsafe rollback SQL applies.

Related: After a schema change rollback, monitor for InnoDB mutex contention — especially if the change involved a heavily-written table. See Contention in MySQL InnoDB for how to read the SEMAPHORES section of SHOW ENGINE INNODB STATUS and interpret elevated wait counts after DDL.

Summary

1. dbsafe generates rollback SQL automatically for every ALTER TABLE plan. It’s included in the standard output, no extra flags needed.
2. ADD COLUMN and ADD INDEX have clean structural rollbacks — drop the same column or index. No data loss, and the rollback operation is often faster than the forward operation.
3. DROP COLUMN is irreversible — dbsafe shows a data loss warning instead of misleading rollback SQL. The only real recovery is a backup.
4. MySQL 8.0.29+ makes DROP COLUMN INSTANT — milliseconds, but the data destruction is still permanent. No window to intervene after commit.
5. Capture rollback SQL in your runbook before the change window — not while the incident is happening.

Happy (safe) schema changes!

References

MySQL Official Documentation:

• ALTER TABLE Statement — MySQL 8.0
• Online DDL Operations — MySQL 8.0
• InnoDB and Online DDL — MySQL 8.0

Tools:

• dbsafe — GitHub Repository
• gh-ost — GitHub’s Online Schema Migration Tool
• pt-online-schema-change — Percona Toolkit

Related Posts:

• Introducing dbsafe: Know Before You ALTER
• Zero-Downtime Schema Changes with INSTANT DDL
• When MySQL Rebuilds Your Table: Understanding COPY Algorithm DDL
• Contention in MySQL InnoDB

This is the COPY algorithm: the most disruptive class of ALTER TABLE operations. Unlike the INSTANT DDL operations covered in the previous post, COPY operations touch every row, block DML for the duration, and can take hours on large tables — or days.

This post covers exactly which operations trigger COPY, what physically happens at the InnoDB level, how table size translates to actual risk, and how dbsafe detects the algorithm and generates the mitigation commands you need.

Related: This is the third post in the dbsafe series. Introducing dbsafe covers installation and the full analysis capabilities. Zero-Downtime Schema Changes with INSTANT DDL covers the safe alternative — the metadata-only operations that don’t touch rows at all.

The Problem: ALTER TABLE as a Full Table Rebuild

When MySQL uses the COPY algorithm, it doesn’t modify your table in place. It executes a sequence of physical operations that are equivalent to recreating the table from scratch:

-- What MySQL does internally during a COPY algorithm ALTER TABLE:

-- Step 1: Create a new shadow table with the modified structure
CREATE TABLE orders_new LIKE orders;
ALTER TABLE orders_new MODIFY COLUMN status VARCHAR(100);

-- Step 2: Copy every row from the original to the shadow table
INSERT INTO orders_new SELECT * FROM orders;

-- Step 3: Acquire an exclusive lock and drop the original
LOCK TABLES orders WRITE;
DROP TABLE orders;

-- Step 4: Rename the shadow table to the original name
RENAME TABLE orders_new TO orders;
UNLOCK TABLES;

No rows are skipped. The operation is as expensive as it looks — a full sequential read of every row, a full write into a new table, and a metadata swap under an exclusive lock.

Four risk factors compound this:

1. Shared lock — the table is locked for writes (DML) but reads (SELECT) continue under LOCK=SHARED during the copy. Applications cannot insert, update, or delete rows, but queries can still read.
2. Disk space doubles — the new table must exist alongside the original until the rename. A 200GB table requires 200GB of free disk space during the operation.
3. Replication lag — replicas must independently re-execute the full ALTER TABLE, creating lag proportional to table size and I/O throughput.
4. Duration scales with row count — 10 million rows takes roughly 10× longer than 1 million rows. There’s no shortcut.

INPLACE operations are better — they often avoid the row-by-row copy — but can still require an internal data rebuild and may hold metadata locks. INSTANT is the only truly lock-free path, and it only applies to a specific set of operations.

The Orders Table

The examples below use the same orders table introduced in the INSTANT DDL post: 21 columns, 7 indexes, and a foreign key to customers. It’s a realistic production schema where algorithm choices have real consequences.

Refer to the INSTANT DDL post for the full CREATE TABLE statement. The column names used in the examples below — status VARCHAR(20), total_amount DECIMAL(12,2), and the mixed-charset string columns — are all part of that schema.

MODIFY COLUMN: Expanding a VARCHAR

The single most common accidental COPY trigger. A product manager asks for longer status values, so you expand status VARCHAR(20) to VARCHAR(100). Same column, same data type, just a bigger limit — surely that’s instant?

dbsafe plan "ALTER TABLE orders MODIFY COLUMN status VARCHAR(100)"

dbsafe reports Algorithm: COPY, Lock: SHARED, and a Dangerous risk assessment. The analysis includes disk space required, an explanation of why gh-ost cannot be used (the table has triggers), and a ready-to-run pt-online-schema-change command with --preserve-triggers.

The reason this triggers COPY comes down to how InnoDB stores variable-length columns. MySQL’s row format encodes the length of each VARCHAR value using 1 or 2 bytes depending on the declared maximum:

• 1-byte length prefix: VARCHAR where max_chars × bytes_per_char ≤ 255
• 2-byte length prefix: VARCHAR where max_chars × bytes_per_char > 255

Crossing that 255-byte boundary forces an on-disk format change that requires rewriting every row — triggering COPY.

The charset is the critical variable. With latin1 (1 byte/char), VARCHAR(20) = 20 bytes and VARCHAR(100) = 100 bytes — both stay under 255, so the expansion is INPLACE. With utf8mb4 (up to 4 bytes/char), VARCHAR(20) = 80 bytes and VARCHAR(100) = 400 bytes — crossing the boundary triggers COPY. The orders table uses utf8mb4, which is why dbsafe reports COPY here.

The practical rule: any MODIFY COLUMN that changes size, type, nullability, or charset will use COPY or INPLACE — never INSTANT.

CHANGE COLUMN: Rename with Type Change

CHANGE COLUMN is the syntax for renaming a column while optionally changing its type. You want to rename total_amount to amount and change the precision from DECIMAL(12,2) to DECIMAL(14,4) to track fractional amounts more precisely:

dbsafe plan "ALTER TABLE orders CHANGE COLUMN total_amount amount DECIMAL(14,4)"

The type change from DECIMAL(12,2) to DECIMAL(14,4) forces COPY. Changing decimal scale (the digits after the decimal point) modifies the internal binary encoding of every stored value — so every row must be rewritten.

All DECIMAL precision changes require ALGORITHM=COPY — there are no INSTANT or INPLACE exceptions like VARCHAR has (MySQL docs). When in doubt, run dbsafe plan against your actual MySQL version before assuming the algorithm.

Tip: If you only need to rename the column — without changing the type — use RENAME COLUMN instead of CHANGE COLUMN. RENAME COLUMN is available from MySQL 8.0.3+; it executes as INSTANT DDL from MySQL 8.0.28+ (INPLACE on earlier 8.0.x versions). Either way: no full row copy, milliseconds on any table size.

-- INSTANT on MySQL 8.0.3+ (rename only, no type change)
ALTER TABLE orders RENAME COLUMN total_amount TO amount;

-- COPY (scale change forces full rebuild)
ALTER TABLE orders CHANGE COLUMN total_amount amount DECIMAL(14,4);

Character Set Conversion

Converting a table’s character set triggers a full table rebuild. The algorithm MySQL uses — COPY or INPLACE — depends on whether the table has indexes on character columns. Per WL#11605, collation changes on indexed columns cannot be performed inplace. If any VARCHAR, CHAR, or TEXT column involved in the conversion is part of an index, MySQL falls back to ALGORITHM=COPY.

Even when INPLACE is possible (tables with no indexes on string columns), CONVERT TO CHARACTER SET does not permit concurrent DML — the table is blocked for writes during the entire rebuild. This is a critical distinction from ALTER TABLE ... CHARACTER SET = ... (which only changes the table default without converting existing columns), which does allow concurrent DML under INPLACE.

For the orders table, five indexes reference VARCHAR columns (uq_order_number, idx_status, idx_payment_method, idx_status_created), so this conversion uses COPY. When you run:

dbsafe plan "ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

Every string column in the table must be re-encoded. A utf8mb3 character that uses up to 3 bytes may require up to 4 bytes in utf8mb4. While utf8mb3 is a strict subset of utf8mb4 (all valid utf8mb3 byte sequences are valid utf8mb4), the change in maximum bytes-per-character affects column metadata, index key lengths, and the VARCHAR length-prefix calculations — which is why the table must be rebuilt. MySQL cannot predict which characters in your data need expansion, so it has to rewrite every string value in every row.

Two additional risks specific to charset conversions:

• Table size can grow — if your data contains characters above ASCII (accented letters, emojis, non-Latin scripts), utf8mb4 encoding uses more bytes than latin1. A 100GB table can become 120GB post-conversion.
• Index prefix limits — utf8mb4 uses up to 4 bytes per character. A VARCHAR(255) with utf8mb4 requires 1020 bytes for a full-column index, which exceeds InnoDB’s 767-byte index prefix limit on the COMPACT row format. If you have indexes on long string columns, the conversion may fail unless you use the DYNAMIC or COMPRESSED row format (the default since MySQL 5.7.9).

For large tables with mixed-charset data, dbsafe’s estimated duration and disk space requirements are the numbers to review carefully before scheduling the operation.

When dbsafe Recommends gh-ost or pt-online-schema-change

When dbsafe detects a COPY algorithm operation, it doesn’t just warn you — it generates the exact migration tool command to use. The goal is zero-downtime: instead of taking an exclusive lock while copying rows, gh-ost and pt-osc copy rows in the background while the table remains fully writable.

You already saw the full output in the MODIFY COLUMN screenshot above. The bottom of the dbsafe report includes a complete pt-online-schema-change command with your server’s connection parameters, the table name, and the --alter flag pre-populated with your statement. You copy, review, and run it.

For the orders table in our demo, dbsafe recommended pt-osc — not gh-ost — because orders has two triggers (trg_orders_after_update, trg_orders_after_delete). gh-ost explicitly does not support tables with existing triggers — this is a documented hard limitation. gh-ost is triggerless by design: it captures row changes via binlog streaming rather than installing triggers. But when the source table has its own triggers, those triggers fire during gh-ost’s row copy and can produce unexpected side effects (double-firing, inconsistent data). gh-ost refuses to run in this case. dbsafe detects triggers via information_schema.TRIGGERS and switches the recommendation to pt-osc automatically.

The full decision matrix for which tool dbsafe recommends:

• Table has triggers → pt-online-schema-change — gh-ost cannot operate on tables with existing triggers (known limitation). dbsafe checks for triggers first and routes to pt-osc, which handles triggers correctly via --preserve-triggers.

• Galera/PXC cluster → pt-online-schema-change — gh-ost has known incompatibilities with Galera/PXC due to differences in how DDL and locking interact with writeset replication. pt-osc uses standard SQL DML that replicates correctly through wsrep. dbsafe detects the cluster topology and switches the recommendation automatically.

• Amazon Aurora → pt-online-schema-change — gh-ost requires additional configuration and workarounds to run against Aurora (--allow-on-master, binary log configuration). pt-osc works correctly without extra configuration.

• Standalone MySQL or async replication, no triggers → gh-ost (default) — gh-ost uses binlog streaming rather than triggers, making it pausable, throttleable, and safer for high-write environments. It’s the preferred tool when no triggers or cluster topology prevents it.

A future post will cover the full decision matrix for gh-ost vs pt-osc in detail, including throttling configuration, chunk sizing, and monitoring during execution.

Risk Assessment: Table Size Matters

dbsafe factors table size into its risk assessment and estimated duration. The rough sizing guide:

These estimates assume a reasonably loaded server with local SSD storage. NFS-mounted data directories, high concurrent write load, and row formats with large BLOBs all increase duration significantly. A < 100 MB table under heavy write load can be more disruptive than a 1 GB table on a quiet replica.

dbsafe’s duration estimate comes from the table’s current row count and average row size (read from information_schema.TABLES), so it reflects your actual data, not a generic estimate.

Practical Workflow for COPY Operations

The workflow when you suspect or confirm a COPY operation:

1. Write your ALTER TABLE statement — start with what you actually need
2. Run dbsafe plan against your production server or a replica with production-scale data
3. Confirm the algorithm — if COPY, do not execute natively on a large table
4. Check the table size — dbsafe shows current size; compare against the sizing guide above
5. Review the generated command — gh-ost or pt-osc, pre-populated by dbsafe
6. Test on staging — run the full migration tool command on a staging server with a production-size dataset
7. Execute with gh-ost or pt-osc — during a lower-traffic window, with throttling configured
8. Verify after completion — check row counts, spot-check data, confirm replication is caught up

Related: Heavy DDL operations — even when run via gh-ost — can cause InnoDB mutex and semaphore contention. See Contention in MySQL InnoDB for how to detect contention using SHOW ENGINE INNODB STATUS during and after schema operations. The SEMAPHORES section will show elevated waits if the operation is stressing the buffer pool or lock subsystem.

For automated pipelines, dbsafe plan --format json lets you extract the algorithm and generated command programmatically. If the algorithm is not INSTANT, the pipeline can halt and present the migration command for human review before any production change proceeds.

Summary

1. COPY algorithm means a full table duplicate — MySQL creates a new table, copies every row, then swaps. Disk space doubles temporarily. DML (writes) is blocked throughout under LOCK=SHARED, but reads can continue.
2. The most common COPY triggers are MODIFY COLUMN (any size or type change that crosses the VARCHAR length-prefix boundary or changes binary encoding), CHANGE COLUMN with a type change, and dropping a primary key without replacement. Charset conversions (CONVERT TO CHARACTER SET) use COPY when the table has indexes on character columns — which is the common case for production tables. Even when INPLACE is possible, concurrent DML is not permitted. In either case, the physical work is a full row-by-row rebuild.
3. Risk scales with table size — a COPY on a 500GB table takes hours; a COPY on a 50MB table takes seconds. dbsafe estimates duration from your actual row count and row size.
4. dbsafe detects the algorithm and generates the mitigation command — gh-ost for standalone and async replication, pt-osc for triggered tables, Galera/PXC clusters, and Aurora.
5. Always run dbsafe plan before any production schema change — especially for operations that look innocent, like expanding a VARCHAR or renaming a column with a type change.

Happy (safe) schema changes!

References

MySQL Official Documentation:

• ALTER TABLE Statement — MySQL 8.0
• Online DDL Operations — MySQL 8.0
• InnoDB and Online DDL — MySQL 8.0
• InnoDB Row Formats — MySQL 8.0
• Character Set Conversion — MySQL 8.0
• RENAME COLUMN — MySQL 8.0

Tools:

• dbsafe — GitHub Repository
• gh-ost — GitHub’s Online Schema Migration Tool
• gh-ost Triggerless Design
• pt-online-schema-change — Percona Toolkit

Related Posts:

• Introducing dbsafe: Know Before You ALTER
• Zero-Downtime Schema Changes with INSTANT DDL
• Contention in MySQL InnoDB

This post covers exactly which operations qualify for INSTANT execution, how MySQL 8.0.29 extended the feature significantly, and how to use dbsafe to verify the algorithm before you ever touch production.

Related: New to dbsafe? The Introducing dbsafe post covers installation, configuration, and the full range of analysis capabilities including DML, topology detection, and CI/CD integration.

The Problem: Schema Changes That Don’t Need to Be Painful

MySQL’s ALTER TABLE has three execution algorithms, and they have very different performance profiles:

-- COPY: New table created, all rows copied, original dropped. Hours on large tables.
ALTER TABLE orders MODIFY COLUMN status VARCHAR(30), ALGORITHM=COPY;

-- INPLACE: Modified in-place without full row copy, but often still rebuilds data on disk.
ALTER TABLE orders ADD INDEX idx_tracking (tracking_number), ALGORITHM=INPLACE;

-- INSTANT: Metadata-only change. No rows touched. Milliseconds regardless of table size.
ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT;

The key question is: does this specific operation need to touch data at all? Adding a column with a default of NULL doesn’t require reading or writing a single row — the column simply doesn’t exist yet in the physical data. MySQL can record that fact in the InnoDB data dictionary and be done.

The problem is knowing which operations qualify. That’s where the confusion (and production incidents) happen.

What Is INSTANT DDL?

INSTANT DDL was introduced in MySQL 8.0.12 for trailing ADD COLUMN. Instead of rebuilding the table’s physical storage, MySQL makes a metadata-only change: it updates the InnoDB data dictionary to record the new column definition, without touching any of the actual row data.

The key properties:

• No table rebuild — physical row data is not copied or reorganized
• Brief metadata lock only — reads and writes proceed normally; a brief exclusive metadata lock is taken during the commit phase
• Instant execution — completes in milliseconds regardless of table size (500 rows or 500 million)
• Metadata-only — only the InnoDB data dictionary is modified

MySQL 8.0.29 extended INSTANT DDL significantly, adding support for adding columns at any position (not just trailing) and for dropping columns entirely — operations that previously always required a full rebuild.

How dbsafe Detects INSTANT Operations

dbsafe connects to your MySQL server, checks the version, reads the table’s column structure, and maps your specific ALTER TABLE statement to the algorithm MySQL will use. No guessing, no reading documentation — it tells you directly.

For the examples below, create a self-contained test table. The orders table is a realistic schema with 21 columns, 7 indexes, and a foreign key — the kind of table where DDL decisions actually matter.

CREATE DATABASE IF NOT EXISTS dbsafe_demo;
USE dbsafe_demo;

-- Minimal customers table (required for the FK constraint)
CREATE TABLE customers (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- Production-like orders table: ~3.9M rows, 21 columns, 7 indexes, 1 FK
CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_number VARCHAR(30) NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  tracking_number VARCHAR(100) DEFAULT NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  shipping_amount DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT '0.00',
  shipping_address_id INT UNSIGNED DEFAULT NULL,
  billing_address_id INT UNSIGNED DEFAULT NULL,
  payment_method VARCHAR(50) DEFAULT NULL,
  payment_status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
  shipped_at DATETIME DEFAULT NULL,
  delivered_at DATETIME DEFAULT NULL,
  cancelled_at DATETIME DEFAULT NULL,
  cancel_reason VARCHAR(255) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(512) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_order_number (order_number),
  KEY idx_customer_id (customer_id),
  KEY idx_status (status),
  KEY idx_tracking_number (tracking_number),
  KEY idx_payment_status (payment_status),
  KEY idx_created_at (created_at),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSTANT ADD COLUMN (MySQL 8.0.12+)

The original INSTANT capability: adding a column at the end of the table (trailing position). This works on any MySQL 8.0.12+ server.

Customer service wants a free-text annotations field on orders. A notes TEXT column has no default value and no NOT NULL constraint — it’s the textbook INSTANT case.

dbsafe plan "ALTER TABLE orders ADD COLUMN notes TEXT"

dbsafe reports Algorithm: INSTANT, Locking: NONE, and Risk: SAFE. The analysis also shows that no rows will be touched, and the operation will complete in milliseconds regardless of table size.

This is the most straightforward INSTANT case: a nullable column added at the end, nothing in the physical data needs to change.

INSTANT ADD COLUMN at Any Position (MySQL 8.0.29+)

Before 8.0.29, if you wanted to add a column somewhere other than the last position — using AFTER column_name or FIRST — MySQL fell back to INPLACE, which could still require rebuilding the data on disk.

On 8.0.29+, column position no longer matters:

You want to add a currency VARCHAR(3) column right next to the monetary columns for readability. Using AFTER total_amount places it in the middle of the column list — something that would have forced a rebuild on 8.0.28 and earlier.

dbsafe plan "ALTER TABLE orders ADD COLUMN currency VARCHAR(3) DEFAULT 'USD' AFTER total_amount"

The same statement on MySQL 8.0.28 would produce Algorithm: INPLACE — a full rebuild. On 8.0.29+, it’s INSTANT.

Tip: If your MySQL version is 8.0.28 or earlier, AFTER column_name clauses will not be INSTANT. dbsafe detects your server version and shows you exactly what algorithm your server will use — not what the latest version supports.

This version boundary matters in practice. If your staging server is on 8.0.30 but production is on 8.0.27, dbsafe will give you different answers when run against each server. Always run it against the target server.

INSTANT DROP COLUMN (MySQL 8.0.29+)

Before 8.0.29, DROP COLUMN always rebuilt the table. Every row had to be rewritten without that column’s data. On large tables this was as disruptive as any other COPY or INPLACE rebuild.

Starting with 8.0.29, dropping a column is also metadata-only:

The user_agent column was added years ago for fraud detection, but your new fraud system pulls that data from a separate audit log. You want to drop it for GDPR data minimization — a 512-byte VARCHAR across 3.9M rows is meaningful storage.

dbsafe plan "ALTER TABLE orders DROP COLUMN user_agent"

Internally, InnoDB marks the column as dropped in the data dictionary. The physical data remains on disk — the rows still contain the column’s bytes — until the next time a full table rebuild occurs (such as an OPTIMIZE TABLE or a COPY-algorithm ALTER).

Tip: Disk space is not immediately reclaimed after an INSTANT DROP COLUMN. The column’s data stays in the row format on disk until the next rebuild. This is expected behavior — the column is simply invisible to MySQL. If you need to reclaim the space, run OPTIMIZE TABLE orders at a maintenance window.

When INSTANT DDL Won’t Work

Not every ALTER TABLE qualifies for INSTANT. Some operations that look simple still require INPLACE or COPY because they actually need to touch or reorganize row data.

Expanding a VARCHAR column is a good example. Whether MySQL can do it in-place depends on whether the change crosses the 255-byte length-prefix boundary: VARCHAR values up to 255 bytes use a 1-byte length prefix, while values of 256 bytes or more use a 2-byte prefix. When the extension stays within the same boundary, MySQL only updates metadata. When it crosses, every row must be rewritten.

The orders table uses utf8mb3 (3 bytes per character), so the byte math matters:

• VARCHAR(30) × 3 = 90 bytes → 1-byte length prefix
• VARCHAR(50) × 3 = 150 bytes → 1-byte length prefix
• VARCHAR(255) × 3 = 765 bytes → 2-byte length prefix

Extending order_number from VARCHAR(30) to VARCHAR(50) stays within the 1-byte prefix range — both are under 255 bytes. MySQL handles this as an INPLACE, metadata-only change:

dbsafe plan "ALTER TABLE orders MODIFY COLUMN order_number VARCHAR(50)"

But extending to VARCHAR(255) crosses the boundary — from 90 bytes (1-byte prefix) to 765 bytes (2-byte prefix). MySQL must rewrite every row to change the length prefix, forcing a full COPY rebuild:

dbsafe plan "ALTER TABLE orders MODIFY COLUMN order_number VARCHAR(255)"

Tip: The boundary depends on your character set. With utf8mb4 (4 bytes/char), VARCHAR(64) is already 256 bytes — past the threshold. With latin1 (1 byte/char), you can extend up to VARCHAR(255) in-place. Always check the byte length, not the character count.

Other common operations that won’t be INSTANT:

• Changing column data type (INT → BIGINT, VARCHAR → TEXT) → COPY
• Adding an index → INPLACE (reads all rows to build the index)
• Changing NULL to NOT NULL → INPLACE or COPY (needs to validate existing rows)
• Dropping the primary key without a replacement → COPY (entire clustered index must be rebuilt); adding or replacing a PK → INPLACE (with rebuild, but faster than COPY)

For these operations, you need a different approach: gh-ost, pt-online-schema-change, or a carefully planned maintenance window. The MySQL Online DDL Operations reference has the full matrix of what’s possible.

Version Matrix

Practical Workflow

The workflow for any production schema change:

1. Write your ALTER TABLE statement
2. Run dbsafe plan against your production server (or a replica with production data)
3. Check the algorithm: INSTANT → proceed; INPLACE/COPY → evaluate alternatives
4. If INSTANT: execute directly during business hours, no maintenance window needed
5. If INPLACE or COPY: consider gh-ost for zero-downtime, or pt-osc, or schedule a maintenance window

For CI/CD pipelines, dbsafe plan --format json lets you gate deployments on the algorithm:

RESULT=$(dbsafe plan --format json "ALTER TABLE orders ADD COLUMN fulfillment_id INT")

ALGORITHM=$(echo "$RESULT" | jq -r '.algorithm')
RISK=$(echo "$RESULT" | jq -r '.risk')

if [ "$ALGORITHM" != "INSTANT" ] || [ "$RISK" != "SAFE" ]; then
  echo "Schema change is not INSTANT/SAFE — blocking deployment"
  echo "Algorithm: $ALGORITHM, Risk: $RISK"
  exit 1
fi

echo "Schema change is safe to run — proceeding"
mysql -e "ALTER TABLE orders ADD COLUMN fulfillment_id INT"

This pattern catches dangerous migrations before they reach production. The pipeline fails fast, and the developer sees exactly why: the algorithm, the locking, the risk level.

Related: Heavy schema change traffic can cause InnoDB mutex contention. See Contention in MySQL InnoDB for how to detect contention using SHOW ENGINE INNODB STATUS during and after schema operations.

For Galera/PXC clusters, the stakes are higher: even an INSTANT DDL in TOI mode blocks all cluster nodes for the duration. dbsafe detects cluster topology and adjusts its risk assessment accordingly. For cluster-specific testing patterns, see How to Test ProxySQL Read/Write Split with sysbench for context on how cluster load behaves during DDL.

Summary

1. INSTANT DDL modifies only the InnoDB data dictionary — no row copies, no table rebuild, only a brief exclusive metadata lock during the commit phase, milliseconds regardless of table size.
2. MySQL 8.0.12 introduced INSTANT ADD COLUMN for trailing positions only.
3. MySQL 8.0.29 extended INSTANT to ADD COLUMN at any position and DROP COLUMN — two of the most common DBA operations.
4. Not every ALTER qualifies: data type changes, index additions, and NULL → NOT NULL changes still rebuild.
5. Use dbsafe plan before every production schema change to confirm the algorithm against your specific MySQL version, table structure, and cluster topology.

Happy schema changes!

References

MySQL Official Documentation:

• ALTER TABLE Statement — MySQL 8.0
• Online DDL Operations — MySQL 8.0
• InnoDB and Online DDL — MySQL 8.0
• InnoDB Data Dictionary — MySQL 8.0

MySQL Blog Posts:

• MySQL 8.0: InnoDB now supports Instant ADD COLUMN
• MySQL 8.0 INSTANT ADD and DROP Column(s)

Tools:

• dbsafe — GitHub Repository
• gh-ost — GitHub’s Online Schema Migration Tool
• pt-online-schema-change — Percona Toolkit

Related Posts:

• Introducing dbsafe: Know Before You ALTER
• Contention in MySQL InnoDB
• How to Test ProxySQL Read/Write Split with sysbench

The problem with MySQL’s ALTER TABLE is that you don’t know what algorithm it will use, what locks it will take, or how long it will run until you actually execute it. By then, if you guessed wrong, your application is already timing out.

The Problem: ALTER TABLE is a Black Box

When planning a schema change, these are the questions you need answered:

• Will it use INSTANT, INPLACE, or COPY algorithm?
• What locks will it take? Can the table still handle reads/writes?
• How long will it take on a table with 500 million rows?
• Will it work differently on MySQL 8.0.12 vs 8.0.29?
• What about my Galera cluster? Will it block all nodes in TOI mode?
• Can I roll it back if something goes wrong?
• Should I use gh-ost or pt-online-schema-change instead?

You can test on staging, but staging never has production-scale data. You can read the documentation, but you still need to mentally map your MySQL version, your table structure, and your specific ALTER syntax to figure out what will happen.

There should be a tool that just tells you.

Enter dbsafe

dbsafe is a command-line tool that connects to your MySQL server, analyzes your DDL or DML statement without running it, and tells you exactly what will happen.

It’s read-only analysis. It doesn’t modify anything. It just tells you what MySQL would do if you ran that statement.

The Same Statement, Different Outcomes

Here’s what makes schema changes tricky: similar-looking statements can behave completely differently.

The first one is instant, no locks, safe for production. The second one rebuilds the entire table with an exclusive lock (COPY algorithm creates a new table and copies all rows). You need to know which is which before you run it.

Topology Detection

If you’re running Percona XtraDB Cluster, dbsafe detects it and adjusts its analysis:

In TOI mode, DDL locks the entire cluster for the duration of the operation - all nodes are blocked from accepting writes. Cluster detection uses wsrep status variables. For large tables, use pt-online-schema-change or RSU method.

The same ALTER that’s safe on standalone MySQL can block your entire cluster for minutes in TOI (Total Order Isolation) mode. dbsafe detects:

• Galera/PXC clusters (wsrep status variables)

Related: For load-testing PXC clusters with ProxySQL read/write split, see How to Test ProxySQL Read/Write Split with sysbench.

• MySQL Group Replication (performance_schema.replication_group_members)
• Async replication topologies (SHOW REPLICA STATUS)
• Semi-sync replication

And adjusts its risk assessment and recommendations accordingly.

DML Analysis

dbsafe also analyzes DELETE and UPDATE statements:

It uses EXPLAIN to estimate affected rows, checks for triggers, and generates a chunked execution script for large operations. The script uses LIMIT with SLEEP() between batches to avoid replication lag and long-running transactions.

Version-Specific Features

MySQL 8.0.12 introduced INSTANT ADD COLUMN for trailing positions. MySQL 8.0.29 extended it to any position and added INSTANT DROP COLUMN. dbsafe detects your MySQL version and tells you what’s supported.

Same statement, different behavior depending on version. You need to know what your specific MySQL version supports.

Features

Read-only analysis - Connects to your database, reads metadata, never modifies data

Topology detection - Detects Galera/PXC, Group Replication, async replication, adjusts recommendations

Version-aware - Knows feature differences between MySQL 8.0.12, 8.0.29, 8.4 LTS, and Percona variants

Rollback plans - Generates undo SQL for every DDL operation

DML analysis - Analyzes DELETE/UPDATE statements, generates chunked execution scripts

Multiple output formats - Text (colored), Plain (no colors), JSON (for automation), Markdown

Table metadata - Shows table size, row count, indexes, foreign keys, triggers

Tool recommendations - Tells you when to use gh-ost, pt-online-schema-change, or native MySQL

CI/CD integration - JSON output, exit codes for pipeline automation

Requirements

• MySQL 8.0.x or 8.4 LTS (including Percona Server variants, XtraDB Cluster, Group Replication)
• MySQL 5.7 and MariaDB are NOT supported
• Read-only MySQL user with SELECT, PROCESS, and REPLICATION CLIENT privileges

Installation

# Linux x86_64
VERSION=0.2.0
curl -L https://github.com/nethalo/dbsafe/releases/download/v${VERSION}/dbsafe_${VERSION}_linux_amd64.tar.gz | tar xz
sudo mv dbsafe /usr/local/bin/

# macOS Apple Silicon
VERSION=0.2.0
curl -L https://github.com/nethalo/dbsafe/releases/download/v${VERSION}/dbsafe_${VERSION}_darwin_arm64.tar.gz | tar xz
sudo mv dbsafe /usr/local/bin/

# Create MySQL user for dbsafe (read-only)
mysql -u root -p << 'SQL'
CREATE USER 'dbsafe'@'%' IDENTIFIED BY 'your_password';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'dbsafe'@'%';
SQL

# Setup configuration
dbsafe config init

# Test connection
dbsafe connect

Quick Start

Create a test database:

CREATE DATABASE dbsafe_demo;
USE dbsafe_demo;
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  price DECIMAL(10,2)
) ENGINE=InnoDB;

Test a safe change (add -p to be prompted for password):

dbsafe plan "ALTER TABLE products ADD COLUMN description TEXT"

Output shows INSTANT algorithm, no locks, safe for production.

Test a dangerous change:

dbsafe plan "ALTER TABLE products MODIFY COLUMN name VARCHAR(255)"

Output shows COPY algorithm, exclusive locks, recommendation to use gh-ost or pt-online-schema-change.

Note: Connection parameters come from ~/.dbsafe/config.yaml. Use the -p flag if you need to enter your password interactively (recommended - don’t store passwords in config files).

Use Cases

Planning production schema changes - Analyze before you run, know the impact

Reviewing migration scripts - Add to CI/CD pipeline to catch dangerous changes early

Galera/PXC cluster operations - Understand TOI blocking behavior before it happens

Large table operations - Get realistic time estimates, decide between native MySQL and gh-ost

DML safety - Analyze bulk DELETE/UPDATE, get chunked execution scripts

How It Works

dbsafe connects to your MySQL server (read-only) and performs the following analysis:

1. SQL Parsing - Uses Vitess sqlparser to parse and understand your DDL/DML statement
2. Topology Detection - Queries wsrep_* variables (Galera/PXC), performance_schema.replication_group_members (Group Replication), or runs SHOW REPLICA STATUS (async replication)
3. Metadata Collection - Gathers table size, row count, indexes, foreign keys, triggers from information_schema
4. Version Detection - Checks MySQL version to determine available INSTANT DDL features
5. Algorithm Determination - Maps your operation + MySQL version to execution algorithm (INSTANT/INPLACE/COPY)
6. Impact Estimation - Calculates estimated duration, lock requirements, and replication impact
7. Recommendations - Suggests gh-ost/pt-osc for COPY operations on large tables, chunked scripts for bulk DML

All analysis is read-only. No test runs, no locks taken, no data modified.

Output Formats

Text (default) - Colored output for terminal use

Plain - No colors, for log files and CI/CD

JSON - Machine-readable for automation:

dbsafe plan --format json "ALTER TABLE users ADD COLUMN email VARCHAR(255)" > analysis.json

Markdown - For documentation

Configuration

The dbsafe config init command creates ~/.dbsafe/config.yaml interactively. You can manually edit it for multiple environments:

connections:
  default:
    host: localhost
    port: 3306
    user: dbsafe
    database: myapp

  production:
    host: prod.example.com
    port: 3306
    user: dbsafe_ro
    database: production

defaults:
  chunk_size: 10000
  format: text

Important: Never store passwords in the config file. Use the -p flag when running commands to enter the password interactively.

View current configuration:

dbsafe config show

Then run analysis using the default connection:

dbsafe plan "ALTER TABLE users ADD COLUMN region VARCHAR(50)"

Links

• GitHub Repository
• Report Issues

References

MySQL Official Documentation:

• ALTER TABLE Statement - MySQL 8.0
• Online DDL Operations - MySQL 8.0
• InnoDB Storage Engine - MySQL 8.0
• InnoDB and Online DDL - MySQL 8.0
• INFORMATION_SCHEMA Tables - MySQL 8.0
• Privileges Provided by MySQL
• EXPLAIN Statement - MySQL 8.0
• SHOW REPLICA STATUS - MySQL 8.0

MySQL Blog Posts:

• MySQL 8.0: InnoDB now supports Instant ADD COLUMN
• MySQL 8.0 INSTANT ADD and DROP Column(s)

MySQL Group Replication:

• performance_schema.replication_group_members Table
• Monitoring Group Replication - MySQL 8.0

Percona XtraDB Cluster Documentation:

• Total Order Isolation (TOI)
• Rolling Schema Upgrade (RSU)
• Index of wsrep Status Variables
• Online Schema Upgrade

Percona Toolkit:

• pt-online-schema-change

Third-Party Tools:

• gh-ost - GitHub’s Online Schema Migration Tool
• Vitess sqlparser