DEV Community

Cover image for Postgres vs. MySQL: DDL Transaction Difference
Ayra Jett for Bytebase

Posted on • Originally published at bytebase.com

1 1 1 1 1

Postgres vs. MySQL: DDL Transaction Difference

Database schema changes are critical operations that require careful planning and execution. The ability to perform these changes safely and reliably is a key consideration when choosing a database management system. In this post, we'll compare how PostgreSQL 17 and MySQL 8 handle Data Definition Language (DDL) transactions, with a focus on atomicity and rollback capabilities.

Understanding DDL Transactions

Before diving into the comparison, let's clarify what we mean by DDL transactions. DDL statements can be grouped together and either committed as a unit or rolled back entirely if something goes wrong.

There are two important concepts to distinguish:

  1. Transactional DDL: The ability to include DDL statements within a multi-statement transaction block, with the option to commit or roll back all statements together.

  2. Atomic DDL: The guarantee that individual DDL statements are atomic (all-or-nothing), but not necessarily supporting their inclusion in multi-statement transactions.

PostgreSQL 17: True Transactional DDL

In PostgreSQL 17, DDL operations are fully transactional, meaning:

  • DDL statements can be included in transaction blocks alongside DML statements
  • Multiple DDL operations can be committed or rolled back as a single unit
  • Savepoints can be used within transactions that include DDL statements
  • If a transaction fails, all DDL changes are rolled back, leaving the database in its original state

There are only a few exceptions to this rule: operations on databases and tablespaces themselves (such as CREATE DATABASE or DROP TABLESPACE) cannot be rolled back. However, all other catalog operations are reversible.

MySQL 8: Atomic DDL

Prior to MySQL 8, DDL operations in MySQL were not atomic at all. If a DDL statement failed partway through execution—such as an ALTER TABLE operation that was adding multiple columns or indexes—the database could be left in an inconsistent, partially-modified state.

MySQL 8 introduced a feature called Atomic DDL which represents a significant improvement over previous versions but differs fundamentally from PostgreSQL's approach.

In MySQL 8, DDL statements are atomic at the statement level, meaning:

  • Individual DDL statements are either fully completed or fully rolled back
  • DDL statements implicitly commit any active transaction before execution
  • DDL statements cannot be part of a multi-statement transaction that can be rolled back
  • Crash recovery ensures statement-level atomicity

MySQL's atomic DDL is implemented through a special internal DDL_LOG table in InnoDB that tracks the creation of files and structures during DDL execution. This log is used at commit/rollback time to clean up properly, ensuring that no orphaned files or index trees remain after a crash.

It's important to note that atomic DDL is only supported in MySQL 8 InnoDB storage engine. For tables using other storage engines, partial updates may still occur.

Examples

To better understand these differences, let's run some DDLs that demonstrate how DDL transactions behave in both systems.

PostgreSQL 17

Our 1st example makes all DDL operations wrap in a transaction. When we execute the ROLLBACK command, all tables and indexes are removed as if they were never created.

-- Start a transaction block
BEGIN;

-- Create a simple table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Add an index
CREATE INDEX idx_username ON users(username);

-- Oops! We made a mistake and want to roll back all changes
ROLLBACK;

-- Verify that the table was not created
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = 'users';
-- Should return no rows, as the transaction was rolled back
Enter fullscreen mode Exit fullscreen mode

The 2nd example demonstrates how PostgreSQL allows partial rollbacks using SAVEPOINT, giving developers fine-grained control over schema changes.

BEGIN;

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Create a savepoint
SAVEPOINT after_users_table;

-- Alter the table to add a column
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- Oops! We only want to roll back the column addition
ROLLBACK TO after_users_table;

-- Add a different column instead
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE;

-- Commit the transaction
COMMIT;
Enter fullscreen mode Exit fullscreen mode

MySQL 8

In this MySQL example, despite wrapping the DDL statements in a transaction block, the ROLLBACK command does not undo the table creation. This is because each DDL statement implicitly commits the transaction before executing, making it impossible to roll back multiple DDL statements as a unit.

-- Try to use a transaction block (note: this won't work as expected for DDL)
START TRANSACTION;

-- Create a simple table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Add an index
CREATE INDEX idx_username ON users(username);

-- Try to roll back all changes (won't work for DDL statements)
ROLLBACK;

-- Verify that the table was created despite the ROLLBACK
SHOW TABLES;
-- Will show 'users' table
Enter fullscreen mode Exit fullscreen mode

However, MySQL 8 does provide statement-level atomicity:

-- This will either create all users or none
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1',
           'user2'@'localhost' IDENTIFIED BY 'password2';

-- This will either drop all tables or none
DROP TABLE IF EXISTS table1, table2, table3;
Enter fullscreen mode Exit fullscreen mode

Conclusion

Let's examine the key differences between PostgreSQL 17 and MySQL 8 in how they handle DDL transactions:

Feature PostgreSQL 17 MySQL 8
DDL Transaction Support Full transactional DDL Atomic DDL (statement-level only)
Multi-statement DDL Transactions Yes No
DDL Rollback in Transaction Yes No (implicit commit)
Atomicity of Single DDL Statement Yes Yes (InnoDB only)
Crash Recovery Full transaction recovery Statement-level recovery
Savepoints with DDL Supported Not supported
Exceptions Database and tablespace operations Database directory removal not part of atomic operation
Implementation Mechanism Write-Ahead Log (WAL) DDL_LOG in InnoDB

PostgreSQL's transactional DDL provides a higher level of safety for complex schema migrations. By wrapping multiple related changes in a transaction, administrators can ensure that the database remains in a consistent state, even if something goes wrong during the migration process. In contrast, MySQL 8 introduces atomic DDL at the statement level, meaning each individual DDL statement is executed as an all-or-nothing operation. While this is a notable improvement over earlier versions of MySQL, it still does not support multi-statement DDL transactions.

References

  1. PostgreSQL Wiki: Transactional DDL in PostgreSQL: A Competitive Analysis

  2. MySQL Documentation: Atomic Data Definition Statement Support

  3. MySQL Blog: Atomic DDL in MySQL 8.0

  4. MySQL Documentation: Statements That Cannot Be Rolled Back

Warp.dev image

The best coding agent. Backed by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (1)

Collapse
 
hammglad profile image
Hamm Gladius

This is a really clear and informative comparison of how PostgreSQL 17 and MySQL 8 handle DDL transactions. I appreciate how you broke down not just the key differences in atomicity and transaction support, but also provided concrete examples to illustrate the behaviors in both systems. Your explanation of transactional DDL in PostgreSQL and atomic DDL in MySQL helps clarify an area that can be confusing for many developers and DBAs. The summary table at the end is especially handy for quickly referencing the main distinctions. Overall, this post is a great resource for anyone weighing which database system might better suit their needs, especially for environments where reliable and flexible schema changes are important. Thanks for putting this together!

👋 Kindness is contagious

Delve into a trove of insights in this thoughtful post, celebrated by the welcoming DEV Community. Programmers of every stripe are invited to share their viewpoints and enrich our collective expertise.

A simple “thank you” can brighten someone’s day—drop yours in the comments below!

On DEV, exchanging knowledge lightens our path and forges deeper connections. Found this valuable? A quick note of gratitude to the author can make all the difference.

Get Started