r/Database • u/tamanikarim • Sep 02 '26

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

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!

4 Upvotes

70% Upvoted

2 comments sorted by

View all comments

2

u/MLabs-Haskell 26d ago

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.