Zero-downtime Postgres migrations - the hard parts

Talk video: http://www.thedotpost.com/2015/06/chris-sinjakli-zero-downtime-postgresql-migrations

As a payments company, GoCardless can't take unexpected downtime lightly. Back in January, we were hit by a 15 second API outage during a Postgres schema change. We thought we had all the bases covered, but obviously we'd missed something.

In the talk I briefly introduce the problem of zero-downtime schema changes, what happened to us, and give a few tips on how to avoid it happening to you.

The talk is a condensed version of this blog post: https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts/

Presented at dotScale 2015

Transcript

1. Zero-downtime Postgres migrations - the hard parts @ChrisSinjo
2. Apps change frequently
3. Many apps can’t take downtime
4. The easy parts
5. - Multiple app versions - Fast DDL statements
6. Fine, until it’s not
7. We took downtime :(
8. Let’s talk about queues
9. -- Slow query (AccessShare) SELECT DISTINCT(customer_id) FROM payments; -- Forces this to queue (AccessExclusive) ALTER TABLE payments ADD COLUMN refunded boolean; -- Which blocks these (AccessShare) SELECT * FROM payments WHERE id = 123;
10. - Kill slow queries - Set lock_timeout low - Enable log_lock_waits
11. We blogged about this
 two weeks ago I’ll tweet the link from @ChrisSinjo