We treat adding a nullable column as the safest migration there is. It needs no rewrite of the table in Postgres, it takes a lock for milliseconds, and old code carries on not knowing the column exists. Our release process runs such migrations a few minutes before the new application version rolls out, so old pods run against the new schema for a short while.
In October we added a delivery_window column to orders. Within a second of the migration finishing, the old order service pods started returning errors on order lookups, roughly three thousand of them over the next two minutes, before the error rate fell away on its own. The logs said: cached plan must not change result type.
The lookup was one of the few queries in that service written by hand, through JdbcTemplate, and it was SELECT * from orders where the order number matched. The Postgres JDBC driver switches a statement to a server side prepared statement once the same connection has run it five times, which is the default prepareThreshold.…