Bringing PostgreSQL towards zero downtime migration with Python

Using an SQL database offers a bunch of advantages; first of all its maturity and that it is understood by almost every software developer. But it has at least one main disadvantage. As the data is structured, if you want to modify the structure, for example on a long-running project, you need a migration and therefore almost for sure, a downtime.

When you have to make a migration, to modify the structure of data for a small amount of records, it is so fast that it never gets problematic. But if you think to modify the structure of tables containing millions or billions of records, the time required to simply apply the structural change is problematic.

Here are some changes we are working on at orderbird to go towards zero downtime migrations using some of the latest improvements of PostgreSQL 9.4, mainly logical replication and mixing in a little magic of some python scripting with psycopg.

Slide also available on Scribd: https://www.scribd.com/doc/272917622/EuroPython-2015-Rigal-Bringing-Postgres-Towards-Zero-downtime-Migration