Recently, while browsing my feed on LinkedIn I came across a post dealing with PostgreSQL transactional DDL. Although I couldn’t find an exact documentation reference in the PostgreSQL docs (the closest I found is a reference to the transaction model in general) it is safe to say that you can wrap DDL statements into a BEGIN … COMMIT (or ROLLBACK) block, making them atomic.

This sounds like a great feature and something I would love to explore more. Transactional DDL potentially does away with many headaches you have as a database developer:

• No more need for explicit rollback scripts
• In the absence of rollback scripts, you don’t have to scramble at god awful hours to design a “fix-foward” strategy
• Greatly reduced risk of rolling out changes

Having spent my entire career working with databases ranging from MySQL, PostgreSQL, DB2, and Oracle, I know this concept must come with a tradeoff. DDL operations typically come with pretty strong locking associated, or else things can go very wrong for the application. I wanted to know

• if transactional DDL really is, transactional
• what impact it has on ongoing operations
• how PostgreSQL compares with Oracle AI Database 26ai when it comes to online schema changes
• … without getting lost in detail 😬

Narrator: he got lost in detail and had to scratch the comparison with Oracle AI Database 26ai (or defer to a later blog post)

The first test I ran involved the postgres:18-alpine image. It turns out that transactional DDL is indeed transactional, and my limited testing on a single user system showed that it worked well.

But what about something closer to the real-world systems I supported as a production DBA? Negotiating downtime windows isn’t something we should do in 2026 (and shouldn’t have done for a while …), the better a database engine fares when it comes to online schema changes, the more suitable it is for production use.

Since I’m by no means a PostgreSQL expert, I left the task of fiddling with the fine details to my coding agent. In a nutshell, it

• Created a React application as a load generator
• Wrote a node-express backend connecting to the database and performing DML statements
• Provided me with a transactional DDL script modifying the table accessed by the application

The load generator looked a lot better than anything I could have come up with.

As you can see it’s pretty straight forward: set the number of concurrent sessions, a think time, and add the option to start and stop the workload. Add a chart covering the number of transactions per second and that’s all I needed. Under the covers the frontend calls a node-express app doing the database work. When starting, the postgres-18:alpine image mounts a directory containing a SQL script, and executes it once the database is available. To keep everything simple, a single. table is created and populated with 1000 rows. The express code creates a connection pool, and handles the REST calls. There really is only one of these worth mentioning, and it

• grabs a session from the pool
• selects a random ID from a table
• updates the table by modifying the last updated at column

You can see this in action, alongside an explanation of the app, in this short video:

As you can see in the video, the application chucks away happily until the DDL transaction starts and modifies the table accessed by the application. At this point, the number of transactions per second drops to zero, until the DDL transaction is either committed or rolled back.

I asked my AI agent to explain this to me and it pointed a few things out that are worth mentioning.

• In-place testing: this is tricky to do with non-interactive deployments you typically use in CI/CD pipelines. No other session “sees” your uncommitted DDL statement(s). Testing must happen before the commit/rollback option is invoked.
• Blocking DDL statements: many DDL commands such as ALTER TABLE are blocking; you see this in the video. Remediation often includes setting lock_timeout, statement_timeout, idle_in_transaction_session_timeout since it’s better to fail early than to create a massive queue of “hanging” transactions but the concept remains risky business on a busy system.
• Concurrent index rebuilds don’t work in a transaction: this has to do with the way indexes are built in multiple transactions of their own.
• Work on external systems cannot be undone: should to perform more than DDL you must be careful not to trigger activities on external systems like sending email, send messages to Kafka, etc.
• Sequence values aren’t reset: this is a minor nuisance should you be testing your changes within the DDL transaction but can have an impact on systems where gaps in sequence numbers aren’t allowed.
• Increased WAL generation and need to VACUUM: keep an eye on these, too, as they might affect your deployment and you might see table and index bloat.

All-in-all I’m not entirely convinced that transactional DDL solves the online schema change problem, in particular on a busy system. The excellent Database Reliability Engineering book by Charity Majors and Lane Campell explains a potentially better way to deal with changes to the database:

1. Expand the schema in a backward-compatible way.
2. Deploy code that handles old and new forms.
3. Backfill or synchronize data.
4. Switch reads and writes.
5. Remove the old schema in a later deployment.

This to me looks a lot more straight forward. I’d also lean towards the use of pg_repack which to my eye looks like the better solution than transactional DDL.

Feel free to let me know if anything in this post isn’t correct, I’m happy to learn more about other database management systems than the one I know best. I’ll try and write another post (or a series) about online schema changes in Oracle AI Database. Until then I’d like to plug the corresponding tech brief from Oracle on the subject: Implementing DevOps Principles with Oracle Database.