One of YugabyteDB’s core missions is to deliver the most PostgreSQL-compatible distributed SQL database in the world, combining the familiar PostgreSQL experience with the horizontal scalability and resilience of a cloud-native, distributed architecture.
A big part of that mission is ensuring that PostgreSQL features you already know and love work seamlessly in a distributed environment. Transactional DDL is a shining example of this journey.
What Is Transactional DDL?
In PostgreSQL, many DDL (Data Definition Language) statements, such as CREATE, ALTER, or DROP, can be run inside transactions. This means you can bundle schema changes with data changes and have them all succeed or fail together.
With YugabyteDB 2025.1, this PostgreSQL capability is now fully realized in the distributed world, allowing schema changes to be included in multi-statement transactions, with automatic rollback of the entire transaction, schema changes and all, if anything goes wrong.
Yes, that means even if you create a table, insert rows, and alter its structure in the same transaction, you can still roll back and leave no trace, just like in PostgreSQL!
Why It Matters in a Distributed World
In a single-node database, transactional DDL is relatively straightforward. In a distributed system—where changes must be coordinated across nodes and regions—it requires deep engineering in:
• Catalog management (so all nodes see the change consistently)
• Consensus protocols (so schema changes are committed or rolled back atomically)
• Transaction coordination (so schema and data changes succeed or fail together)
By implementing Transactional DDL, YugabyteDB makes it easier to:
• Run schema migrations safely without leaving the database in an inconsistent state.
• Bundle schema and data changes together in a single atomic operation.
• Rollback schema changes as easily as you rollback data changes—no manual cleanup needed.
From “Fails” to “Works” – A Real Example
Here’s a practical example that works perfectly in YugabyteDB 2025.1, but fails in earlier versions:
BEGIN;
-- Create a new table
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
-- Insert initial data
INSERT INTO customers (name)
VALUES ('Jane'), ('Lucy');
-- Add a new column in the same transaction
ALTER TABLE customers ADD COLUMN email TEXT;
-- Update with new column values
UPDATE customers
SET email = LOWER(name) || '@yugabyte.com';
COMMIT;
In YugabyteDB 2024.2.3.1:
yugabyte=# BEGIN;
BEGIN
yugabyte=*# -- Create a new table
yugabyte=*# CREATE TABLE customers (
yugabyte(*# id BIGSERIAL PRIMARY KEY,
yugabyte(*# name TEXT NOT NULL
yugabyte(*# );
CREATE TABLE
yugabyte=*# -- Insert initial data
yugabyte=*# INSERT INTO customers (name)
yugabyte-*# VALUES ('Jane'), ('Lucy');
INSERT 0 2
yugabyte=*# -- Add a new column in the same transaction
yugabyte=*# ALTER TABLE customers ADD COLUMN email TEXT;
ALTER TABLE
yugabyte=*# -- Update with new column values
yugabyte=*# UPDATE customers
yugabyte-*# SET email = LOWER(name) || '@yugabyte.com';
ERROR: could not serialize access due to concurrent update (query layer retry isn't possible because this is not the first command in the transaction. Consider using READ COMMITTED isolation level.)
DETAIL: Transaction aborted: 0e537228-87d8-408e-8cbc-861c2b65527b
yugabyte=!# COMMIT;
ROLLBACK
yugabyte=# SELECT * FROM customers;
id | name | email
----+------+-------
(0 rows)
In YugabyteDB 2025.1:
yugabyte=# BEGIN;
BEGIN
yugabyte=*# -- Create a new table
yugabyte=*# CREATE TABLE customers (
yugabyte(*# id BIGSERIAL PRIMARY KEY,
yugabyte(*# name TEXT NOT NULL
yugabyte(*# );
CREATE TABLE
yugabyte=*# -- Insert initial data
yugabyte=*# INSERT INTO customers (name)
yugabyte-*# VALUES ('Jane'), ('Lucy');
INSERT 0 2
yugabyte=*# -- Add a new column in the same transaction
yugabyte=*# ALTER TABLE customers ADD COLUMN email TEXT;
ALTER TABLE
yugabyte=*# -- Update with new column values
yugabyte=*# UPDATE customers
yugabyte-*# SET email = LOWER(name) || '@yugabyte.com';
UPDATE 2
yugabyte=*# COMMIT;
COMMIT
yugabyte=# SELECT * FROM customers;
id | name | email
----+------+-------------------
2 | Lucy | lucy@yugabyte.com
1 | Jane | jane@yugabyte.com
(2 rows)
Rollback in Action
In YugabyteDB 2025.1, you can even test rollback with Transactional DDL:
BEGIN;
CREATE TABLE test_txn_ddl (id INT PRIMARY KEY, val TEXT);
INSERT INTO test_txn_ddl VALUES (1, 'first');
-- Oops! A constraint violation to trigger rollback
INSERT INTO test_txn_ddl VALUES (1, 'duplicate');
ROLLBACK;
-- Verify the table never existed
\d test_txn_ddl
In YugabyteDB 2025.1:
yugabyte=# BEGIN;
BEGIN
yugabyte=*# CREATE TABLE test_txn_ddl (id INT PRIMARY KEY, val TEXT);
CREATE TABLE
yugabyte=*# INSERT INTO test_txn_ddl VALUES (1, 'first');
INSERT 0 1
yugabyte=*# -- Oops! A constraint violation to trigger rollback
yugabyte=*# INSERT INTO test_txn_ddl VALUES (1, 'duplicate');
ERROR: duplicate key value violates unique constraint "test_txn_ddl_pkey"
yugabyte=!# ROLLBACK;
ROLLBACK
yugabyte=# -- Verify the table never existed
yugabyte=# \d test_txn_ddl
Did not find any relation named "test_txn_ddl".
This is a game-changer for schema management in distributed environments… no leftover tables, no half-applied migrations, no manual cleanup.
Familiar PostgreSQL Behavior, Distributed by Design
One of the reasons developers love PostgreSQL is its predictability. Bringing that predictability into a distributed SQL database is a non-trivial challenge, but it’s central to YugabyteDB’s design philosophy.
Transactional DDL is part of a broader set of features that align YugabyteDB with PostgreSQL behavior:
• Full ACID transactions, even across multiple shards and regions.
• Rich SQL and PL/pgSQL support.
• A powerful catalog layer that supports both OLTP and analytics-style workloads.
By continuing to close the compatibility gap, YugabyteDB allows organizations to modernize their infrastructure without rewriting their SQL.
Have Fun!
