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. Postgres had cached a plan for that prepared statement, and the plan returned the columns orders had at the time. Adding a column changed what SELECT * meant, so the result shape of the cached plan no longer matched, and Postgres refuses to silently hand back a different row shape on a prepared statement. It raises that error instead.

The driver notices the error and prepares the statement again the next time it is used on that connection. So every pooled connection, in every pod, failed once for every SELECT * query it had prepared, and when the failure happened inside a transaction it took the rest of that transaction down too. Forty pods with thirty connections each made the two minutes.

The migration was backwards compatible for any query that names its columns. It was not for a query that asks for all of them.

We rewrote the hand written queries to list their columns, and a static check in CI now rejects SELECT * in application SQL. The connection string sets autosave to conservative, which tells the driver to retry a statement once after this specific failure, so a schema change cannot surface it to a customer again. Our migration review checklist now includes a line we had never needed: which running queries depend on the shape of this table.

– Sergey Shinder