Drop constraints before drop column or table

[tbicr]

When column or table dropped it also drop with cascade related constraints as foreign keys, index constraints and regular indexes.

Assume that next operations take some time:

• drop table - physically drop table files
• drop index or index constraint - physically drop index files

Lets consider possible scenarios:

• table1 has FK to table2 and we drop any of this tables

  • as cascade operation perform all suboperations will be handled in transaction
  • when we drop FK then EXCLUSIVE LOCK taken for both tables until transaction will be finished
  • we start drop table that do physical operation and take some time on huge table
  • as result any query to other table will wait until EXCLUSIVE LOCK will be released and can be clause of downtime
  • dropping FK before cascade remove locking second table and prevent donwtime

• table1 has FK to table2 and we drop any column in this relation

  • same logic as in drop table with FK case

• table has index or index constraint on column we drop

  • as cascade operation perform all suboperations will be handled in transaction
  • when we drop column it also drop indexes and index constraints that can take time on huge table
  • as result any query to this table will wait until EXCLUSIVE LOCK will be released and can be clause of downtime
  • dropping indexes and index constraints before cascade remove or decrease EXCLUSIVE locking and prevent or minimize donwtime

See also https://github.com/tbicr/django-pg-zero-downtime-migrations/issues/25