identity-service deploy rolled back: new requests and decisions failed in the EU for 9 minutes

Summary

Today's identity-service deploy carried a migration for request bundles that built an index on access_requests without CONCURRENTLY and added a foreign key in the same step. In the EU, with 3.4 million rows, writes to access_requests waited behind it: nobody could make a request or decide one from 15.12 to 15.21. We rolled back by hand. US and UK had deployed first and were fine (small tables).

Impact

EU clients only. About 140 requests and decisions failed with an error and had to be done again. Sign-in was not affected.

Timeline (Tuesday 14 July)

• 15.10 Deploy reaches the EU.
• 15.11 Migration add_bundle_id_to_access_requests starts.
• 15.12 5xx alarm on the requests endpoints.
• 15.14 Oskar sees lock waits on access_requests.
• 15.16 Decision to roll back. Marek cancels the migration's query.
• 15.17 Rollback started by hand.
• 15.21 Requests and decisions succeed again.

Cause

A migration written the quick way on a large table. CI doesn't check migrations; the pipeline doesn't roll back by itself.

Follow-ups

• Migrations on large tables never take a lock that stops writes: ENG-1548
• The pipeline rolls a region back when its smoke tests fail: ENG-1549
• The bundles migration redone safely: ENG-1506