[julian37]

This might work to some extent for renaming things but doesn't for any other kind of migration I can think of:

- Dropping a column doesn't work (assuming the point of dropping it is actual deletion, rather than just hiding it)

- Adding a column doesn't work either

- Changing a column's nullability, default value, or data type doesn't work

- Doesn't help with adding or changing constraints or indexes

[fabianlindfors]

With this model, migrations would be two-stepped. First the migration would be applied, creating a new schema and adding the new information to the underlying table. Once the version is old and no longer used by any clients, the migration would be "deleted", removing the schema and the underlying information.

So when dropping a column it would go like this: 1. Migration is applied. Columns is removed from the new view but remains in the backing table. 2. Migration is no longer needed. Column is removed from backing table.

This design should be extendable to cover changing data type (introduce a new column and moves values over + sync values using trigger), changing default or changing the contents of a full column.

Constraints or indices are a bit trickier, but it might work to create a new column duplicated from the old one and add constraints to that instead.

[kroolik]

Adding a column, changing column's nullability and adding/changing constraints is already zero-downtime in PG.

Not sure about default value change, but I would also say its zero-downtime as adding a column with default is zero-downtime. Haven't checked, though.

Dropping a column and changing the data type are not zero-downtime.

Renaming a column is not zero-downtime as you might have multiple readers/writes using both.

[postgressomethi]

> Adding a column, changing column's nullability and adding/changing constraints is already zero-downtime in PG.

Making a previously nullable column NOT NULL is not zero downtime. Neither are adding constraints -- except in some cases with NOT VALID, which isn't quite the same thing.

> Dropping a column and changing the data type are not zero-downtime.

Dropping a column is zero downtime.

[mikeklaas]

Adding a NULL constraint isn't zero downtime since PG scans the whole table ensuring that the constraint is not violated.