7 ms·

Database Migrations

79 points by bndr 3y ago

victorNicollet 3y ago
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.
- gregw2 3y ago
  This assumes your stream has the full event history … or that history/state is irrelevant, correct? If not, did you leave out a ‘copy history from source to target’ step?
  - victorNicollet 3y ago
    Indeed, the event stream contains the full history of all events that have ever been produced during the lifetime of the application.
gcau 3y ago
Insightful. Also, your website design is very clean and nice - I like it.
- izoow 3y ago
  Very hard to read on mobile though. I either have to scroll left to right for every line, or zoom out and have very small text. Seems like the content div has a min-width or something that prevents the text from wrapping on a narrow screen.
simonw 3y ago
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.
- perrygeo 3y ago
  Great interview topic, I agree. Candidates should be able to identify any SQL DDL that might break an app, then decompose it into three safe steps as you've described. It's a core skill for working with an RDBMS but rarely explicitly taught.
  - btown 3y ago
    Something as simple as a field rename can result in downtime done naively, and showing a candidate a badly named field and asking them what they’d do to fix it can be quite illuminating!
claytonjy 3y ago
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.
- klysm 3y ago
  I’d really like to implement 2. but it’s quite difficult to make the switch to that approach when you already have a ton of tables.
  - tudorg 3y ago
    If you are using Postgres, I think you can create a view for each table and put all the views in a schema, then you switch the app all at once by using `SET search_path TO`.
    - claytonjy 3y ago
      You can, but I'd argue if your API schema is just 1-to-1 views for the underlying tables, you're not getting much value from this approach.
      - spott 3y ago
        Until you change the underlying tables, or the api schema. At which point you don’t need to change the other.
        claytonjy 3y ago
montroser 3y ago
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.
- klysm 3y ago
  Your comment is written like you have a undisclosed stake in skeema.io
  - evanelias 3y ago
    Founder of skeema.io here. GP is a fan and does not have a stake in the company. Skeema is used by several hundred companies, including GitHub, Twilio, and Etsy. We have a lot of fans in the MySQL community, and just because someone enjoys the product does not mean they’re a shill.
    - klysm 3y ago
      Thanks for the clarification, and I wasn’t suggesting he was a shill - it just sounded like it and typically such claims and prefixed with a disclaimer on HN
  - montroser 3y ago
    Nope, it just made my life better and I want others who feel the pain in TFA to know there are options.
    - evanelias 3y ago
      Thank you for spreading the word, I greatly appreciate it. Building a bootstrapped product/business in the MySQL space has been quite challenging. Most newer entrants in the schema management space are VC-funded, and went wide instead of deep in terms of the range of supported DBs. I personally believe in deep expert-level coverage of a specific DB, resulting in better functionality and a safer schema management toolchain. However it does make word-of-mouth more challenging, since MySQL is unpopular here. So often these blog posts don’t mention Skeema, since blog authors coming from other DBs haven’t ever encountered it.
physicsguy 3y ago
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.
- klysm 3y ago
  Strong agree, you can usually find some time when the application doesn’t have to be available. It’s so much easier to just shut the service layer down, take a snapshot, migrate, and bring the services back.
thedudeabides5 3y ago
I think the Heisenberg uncertainty principle applies to database migrations. You can migrate or you can have no downtime, you cannot do both.
- klysm 3y ago
  Effectively zero downtime migrations are certainly possible, but they are very complex to implement and require a couple layers of indirection. Rarely worth the cost imo.
Clever321 3y ago
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-growth.html https://blog.datomic.com/2017/01/the-ten-rules-of-schema-gro...
tudorg 3y ago
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 https://github.com/xataio/pgroll
tianzhou 3y ago
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.
k__ 3y ago
What if your (read) DB is just a projection?
cpursley 3y ago
What I want are state based migrations which are smart enough to handle things which are dependent on others.
saisrirampur 3y ago
Great write up! At PeerDB, we’ve been using refinery https://github.com/rust-db/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!

79 points by bndr 3y ago

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.

gregw2 3y ago
This assumes your stream has the full event history … or that history/state is irrelevant, correct? If not, did you leave out a ‘copy history from source to target’ step?
- victorNicollet 3y ago
  Indeed, the event stream contains the full history of all events that have ever been produced during the lifetime of the application.

gcau 3y ago

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

izoow 3y ago
Very hard to read on mobile though. I either have to scroll left to right for every line, or zoom out and have very small text. Seems like the content div has a min-width or something that prevents the text from wrapping on a narrow screen.

simonw 3y ago

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.

perrygeo 3y ago
Great interview topic, I agree. Candidates should be able to identify any SQL DDL that might break an app, then decompose it into three safe steps as you've described. It's a core skill for working with an RDBMS but rarely explicitly taught.
- btown 3y ago
  Something as simple as a field rename can result in downtime done naively, and showing a candidate a badly named field and asking them what they’d do to fix it can be quite illuminating!

claytonjy 3y ago

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.

klysm 3y ago
I’d really like to implement 2. but it’s quite difficult to make the switch to that approach when you already have a ton of tables.
- tudorg 3y ago
  If you are using Postgres, I think you can create a view for each table and put all the views in a schema, then you switch the app all at once by using `SET search_path TO`.
  - claytonjy 3y ago
    You can, but I'd argue if your API schema is just 1-to-1 views for the underlying tables, you're not getting much value from this approach.
    - spott 3y ago
      Until you change the underlying tables, or the api schema. At which point you don’t need to change the other.
      - claytonjy 3y ago

montroser 3y ago

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.

klysm 3y ago
Your comment is written like you have a undisclosed stake in skeema.io
- evanelias 3y ago
  Founder of skeema.io here. GP is a fan and does not have a stake in the company. Skeema is used by several hundred companies, including GitHub, Twilio, and Etsy. We have a lot of fans in the MySQL community, and just because someone enjoys the product does not mean they’re a shill.
  - klysm 3y ago
    Thanks for the clarification, and I wasn’t suggesting he was a shill - it just sounded like it and typically such claims and prefixed with a disclaimer on HN
- montroser 3y ago
  Nope, it just made my life better and I want others who feel the pain in TFA to know there are options.
  - evanelias 3y ago
    Thank you for spreading the word, I greatly appreciate it. Building a bootstrapped product/business in the MySQL space has been quite challenging. Most newer entrants in the schema management space are VC-funded, and went wide instead of deep in terms of the range of supported DBs. I personally believe in deep expert-level coverage of a specific DB, resulting in better functionality and a safer schema management toolchain. However it does make word-of-mouth more challenging, since MySQL is unpopular here. So often these blog posts don’t mention Skeema, since blog authors coming from other DBs haven’t ever encountered it.

physicsguy 3y ago

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.

klysm 3y ago
Strong agree, you can usually find some time when the application doesn’t have to be available. It’s so much easier to just shut the service layer down, take a snapshot, migrate, and bring the services back.

thedudeabides5 3y ago

I think the Heisenberg uncertainty principle applies to database migrations. You can migrate or you can have no downtime, you cannot do both.

klysm 3y ago
Effectively zero downtime migrations are certainly possible, but they are very complex to implement and require a couple layers of indirection. Rarely worth the cost imo.

Clever321 3y ago

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-growth.html https://blog.datomic.com/2017/01/the-ten-rules-of-schema-gro...

tudorg 3y ago

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 https://github.com/xataio/pgroll

tianzhou 3y ago

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.

k__ 3y ago

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

cpursley 3y ago

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

saisrirampur 3y ago

Great write up! At PeerDB, we’ve been using refinery https://github.com/rust-db/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!