from Hacker News

Database Migrations

by bndr on 10/1/23, 10:28 AM with 67 comments

• by claytonjy on 10/1/23, 4:25 PM

  Two additional rules I don't see followed often, but made a past life of mine much easier:

  1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations

  2. never expose real tables to an application. Create an "API" schema which contains only views, functions, procedures, and only allow applications to use this schema. This gives you a layer of indirection on the DB side such that you can nearly eliminate the dance of coordinating application changes and database migrations

  You can get away without these rules for a long time, but 2 becomes particularly useful when more than one application uses the database.

• by simonw on 10/1/23, 4:15 PM

  This is really good.

  It covers one of the most common things people miss with regards to running migrations: it isn't possible to atomically deploy both the migration and the application code that uses it. This means if you want to avoid a few seconds/minutes of errors, you need to deploy the migration first in a way that doesn't break existing code, then the application change, and then often a cleanup step to complete the migration in a way that won't break.

  Knowing how to do this isn't a common skill. It's probably a good topic for an interview question for senior engineering roles.

• by tudorg on 10/1/23, 5:19 PM

  This is a fantastic article! It shows that even simple migrations (like adding or removing a column) can be quite tricky to deploy in concert with the application deployement.

  We (at Xata) have tried for a while to come up with a generic schema migration system for PostgreSQL that makes this easier. We ended up using views and temporary columns in such a way that we can provide both the "old" and the "new" schema simultaneously. Up/down triggers convert newly inserted data from old to new and the other way around. This also has the advantage the it can do rollbacks instantly by just dropping the "new" view.

  We were just planning to announce this as an open source project this week, but actually it is already public, so if you are curious: https://github.com/xataio/pgroll

• by montroser on 10/1/23, 4:49 PM

  Such is life. This perspective seems coming from an application developer who is seeing the database as an extension of the app. When you're big enough to have a database ops team, they will come with a different perspective, and most likely be highly skeptical of storing schema changes in the database.

  If your're not too cool for MySQL, check out skeema.io for a declarative, platform-agnostic approach to schema management and path to happiness.

• by victorNicollet on 10/2/23, 5:08 PM

  The pain of database migrations is what originally pushed me towards event sourcing. The database is read-only (but immediately mirrors changes applied to the event stream), and I can have multiple databases connected to the same stream, with different schemas.

  This makes schema changes easy to perform (just create a new database and let the system populate it from the events), easy to do without downtime (keep the old database available while the new database is building), and easy to roll back (keep the old database available for a while after the migration).

  The trade-off is that changing/adding event types now needs to be done carefully (first deploy code able to process the new event types, then deploy the code that can produce the new events), whereas a SQL database supports new UPDATE or INSERT without a schema change.

• by Clever321 on 10/1/23, 5:18 PM

  I really like the datomic guidelines for change, which negates a category of migration headaches: https://blog.datomic.com/2017/01/the-ten-rules-of-schema-gro...

• by physicsguy on 10/1/23, 5:02 PM

  I still think that for many cases, small to medium enterprises should consider migrations with downtime. If it’s B2B it’s relatively normal to have maintenance periods.

• by saisrirampur on 10/2/23, 2:07 AM

  Great write up! At PeerDB, we’ve been using refinery https://github.com/rust-db/refinery to handle database migrations of our catalog Postgres database from Rust. It is easy, typesafe and gets the job done. Thought this would be useful for users building apps with Rust!

• by thedudeabides5 on 10/1/23, 5:16 PM

  I think the Heisenberg uncertainty principle applies to database migrations.

  You can migrate or you can have no downtime, you cannot do both.

• by tianzhou on 10/1/23, 5:32 PM

  Nice writeup. It's one of those things not taught in school and always learn in the hard way.

  We at Bytebase also recognize this and have spent over 2 years to build a solution for team to coordinate the database migrations better.

• by cpursley on 10/1/23, 6:06 PM

  What I want are state based migrations which are smart enough to handle things which are dependent on others.

• by k__ on 10/1/23, 5:50 PM

  What if your (read) DB is just a projection?

• by gcau on 10/1/23, 4:09 PM

  Insightful. Also, your website design is very clean and nice - I like it.