1

Here is an example:

You create a new column called note on the payments table on v1.1.0 (green) of your application. However, you notice a bug. You roll your application back to v1.0.0 (blue). When v1.0.0 creates a new payment, the database will throw an error because the note field is missing. However, if the note was nullable or had a database default, this would not happen.

Thus, am I correct in thinking that as long as "blue" (v1.0.0) could be rolled back to (i.e. in one release behind), every column must be added with either a default or nullable value? I assume that in the next release, say v1.0.2, you could then add a default value without risk?

1 Answer 1

0

If you're unable to rollback schema migrations, then yes, your schema must be backwards-compatible to whatever application version(s) you want to allow rollbacks to. Your policy only works if your application never assumes column count and order, i.e. never uses SELECT * directly on tables and always specifies the columns you are providing data for in any INSERT statement.

This policy will paint you into a corner fairly quickly. Think about what happens if you roll out a change, roll it back, then want to roll forward again. Your new column is now inconsistent, and you will have to spend time making it consistent again before the new code can go live.

(Dropping/nullable-ing a column is a fast action on modern PostgreSQL. On PG11 and later, adding a default value is also fast.)

2
  • So @dwhitemv is your recommendation to add a default or nullable for each column? I think that is what you are saying, but I do not want to misinterpret! Or are you saying it makes more sense to simply drop the column when rolling back? Oct 26, 2022 at 11:10
  • Drop the column.
    – dwhitemv
    Oct 26, 2022 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.