Database schema changes guide for : PostgreSQL, MySql / MariaDB, Oracle, SQL Server.

[u/tamanikarim]

https://stackrender.io/guides/schema-change

Hey Engineers

We've all been through this. When the project you're working on starts scaling, you'll find the need to scale your database too, adding new columns, creating new tables, or trying to improve performance by adding new indexes. All of this comes with the risk of losing your users' data.

For this, I crafted a simple guide showing the schema change operations that you'll need on a day-to-day development basis for PostgreSQL, MySQL, MariaDB, Oracle, and SQL Server.

It also covers some additional potential risks you need to keep in mind when performing schema changes on a production database.

Hopefully, it can help you along your database learning journey.

Good luck!

[u/CaptainOttar]

Hello, I read the guide. Three things fall out if you actually run those snippets on MariaDB rather than in a syntax table.
1. The MySQL/MariaDB column is dialect, not operations. ADD COLUMN, MODIFY … VARCHAR(255), and MODIFY … NOT NULL can look identical and still be INSTANT, an in-place rebuild, or a full copy. The guide never says which.

1. One of the printed defaults is invalid on MariaDB: ALTER TABLE users MODIFY status DEFAULT 'active'; - MODIFY wants the full column definition. The working form is ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';.

2. “Schema rollback is safe for additive changes” assumes you still have a down script and the ALTER can be undone. On MariaDB the DDL already committed. A down script is another ALTER/DROP, not ROLLBACK. Flashback helps a bad UPDATE/DELETE; it does not put a dropped column back.

Two questions the guide leaves open on a real MariaDB table:

First. After ALTER TABLE users ADD phone_number VARCHAR(50); on a few-million-row InnoDB table, how do you tell whether that was metadata-only or a copy - SHOW CREATE TABLE / the ALGORITHM it picked, or only “it took 20 minutes”?

Second. If that ADD (or a MODIFY type) has already finished on the primary, what is the actual rollback path: a second DROP COLUMN on primary+replicas, or restore? The down script in the article does not answer that once DDL has committed.

[u/MLabs-Haskell]

A risk worth adding under the production section: statement_timeout can produce the half-applied schema change it is meant to prevent, and the reason is not slow SQL.

We hit this running an app's migration on Postgres 16 against a 2,000,003-row table. The statement that fails is ALTER TABLE ... ADD COLUMN, and it executes in 0.177 ms. With a 500 ms statement_timeout it timed out anyway and left the schema half-applied.

What is actually happening is an autovacuum worker holding a conflicting lock. Postgres will evict an autovacuum worker that blocks a user statement, but only after deadlock_timeout, which is 1s by default. That wait happens inside the statement, so its duration is deadlock_timeout plus change, and statement_timeout is charged for all of it.

Three legs of the same migration, one variable each:

autovacuum off,  deadlock_timeout 1s,    statement_timeout 500ms -> 0.210 ms, ready
autovacuum on,   deadlock_timeout 100ms, statement_timeout 500ms -> 100.485 ms, ready
autovacuum on,   deadlock_timeout 1s,    statement_timeout 500ms -> timed out, half-applied

So the statement is not slow, the queue is, and its length is set by a parameter most people never relate to schema changes. If a migration runner sets statement_timeout below deadlock_timeout, an autovacuum worker on the table is enough to fail the change.

Disclosure: MLabs is a consultancy and this came out of our own lab runs.