Simon Willison’s Weblog

pgroll (via) “Zero-downtime, reversible, schema migrations for Postgres”

I love this kind of thing. This one is has a really interesting design: you define your schema modifications (adding/dropping columns, creating tables etc) using a JSON DSL, then apply them using a Go binary.

When you apply a migration the tool first creates a brand new PostgreSQL schema (effectively a whole new database) which imitates your new schema design using PostgreSQL views. You can then point your applications that have been upgraded to the new schema at it, using the PostgreSQL search_path setting.

Old applications can continue talking to the previous schema design, giving you an opportunity to roll out a zero-downtime deployment of the new code.

Once your application has upgraded and the physical rows in the database have been transformed to the new schema you can run a --continue command to make the final destructive changes and drop the mechanism that simulates both schema designs at once.

# 30th January 2024, 9:27 pm / migrations, postgresql, zero-downtime

Stripe: Online migrations at scale (via) This 2017 blog entry from Jacqueline Xu at Stripe provides a very clear description of the “dual writes” pattern for applying complex data migrations without downtime: dual write to new and old tables, update the read paths, update the write paths and finally remove the now obsolete data—illustrated with an example of upgrading customers from having a single to multiple subscriptions.

# 5th November 2023, 4:06 pm / migrations, databases, stripe, zero-downtime

Database Migrations. Vadim Kravcenko provides a useful, in-depth description of the less obvious challenges of applying database migrations successfully. Vadim uses and likes Django’s migrations (as do I) but notes that running them at scale still involves a number of thorny challenges.

The biggest of these, which I’ve encountered myself multiple times, is that if you want truly zero downtime deploys you can’t guarantee that your schema migrations will be deployed at the exact same instant as changes you make to your application code.

This means all migrations need to be forward-compatible: you need to apply a schema change in a way that your existing code will continue to work error-free, then ship the related code change as a separate operation.

Vadim describes what this looks like in detail for a number of common operations: adding a field, removing a field and changing a field that has associated business logic implications. He also discusses the importance of knowing when to deploy a dual-write strategy.

# 1st October 2023, 11:55 pm / django, migrations, databases, ops, zero-downtime

Sqitch tutorial for SQLite (via) Sqitch is an interesting implementation of database migrations: it’s a command-line tool written in Perl with an interface similar to Git, providing commands to create, run, revert and track migration scripts. The scripts the selves are written as SQL in whichever database engine you are using. The tutorial for SQLite gives a good idea as to how the whole system works.

# 24th July 2022, 11:44 pm / sqlite, migrations, databases

Simple declarative schema migration for SQLite (via) This is an interesting, clearly explained approach to the database migration problem. Create a new in-memory database and apply the current schema, then run some code to compare that with the previous schema—which tables are new, and which tables have had columns added. Then apply those changes.

I’d normally be cautious of running something like this because I can think of ways it could go wrong—but SQLite backups are so quick and cheap (just copy the file) that I could see this being a relatively risk-free way to apply migrations.

# 3rd May 2022, 6:07 pm / migrations, sqlite

migra (via) This looks like a very handy tool to have around: run “migra postgresql:///a postgresql:///b” and it will detect and output the SQL alter statements needed to modify the first PostgreSQL database schema to match the second. It’s written in Python, running on top of SQLAlchemy.

# 26th February 2022, 11:23 pm / migrations, postgresql, databases

How to Create an Index in Django Without Downtime (via) Excellent advanced tutorial on Django migrations, which uses a desire to create indexes in PostgreSQL without locking the table (with CREATE INDEX CONCURRENTLY) to explain the SeparateDatabaseAndState and atomic features of Django’s migration framework.

# 11th April 2019, 3:06 pm / migrations, postgresql, django, zero-downtime

How Balanced does Database Migrations with Zero-Downtime. I’m fascinated by the idea of “pausing” traffic during a blocking site maintenance activity (like a database migration) and then un-pausing when the operation is complete—so end clients just see some of their requests taking a few seconds longer than expected. I first saw this trick described by Braintree. Balanced wrote about a neat way of doing this just using HAproxy, which lets you live reconfigure the maxconns to your backend down to zero (causing traffic to be queued up) and then bring the setting back up again a few seconds later to un-pause those requests.

# 7th November 2017, 11:36 am / highavailability, migrations, http, scaling, haproxy, zero-downtime

On Django And Migrations. South author Andrew Godwin on the plans for migrations in Django. His excellent South migration library will be split in to two parts—one handling database abstraction, dependency resolution and history tracking and the other providing autodetection and the South user interface. The former will go in to Django proper, encouraging other migration libraries to share the same core abstractions.

# 2nd June 2010, 4:27 pm / andrew-godwin, django, migrations, orm, south, recovered

Southerly Breezes. Andrew Godwin is slowly assimilating the best ideas from other Django migration systems in to South—the latest additions include ORM Freezing from Migratory and automatic change detection. Exciting stuff.

# 15th March 2009, 1:17 pm / andrew-godwin, django, south, migrations, orm, databases

Gearshift. Whoa, a full migrations library written in JavaScript for Gears (which uses SQLite for its data store).

# 15th September 2008, 2:51 pm / sqlite, gears, google-gears, javascript, migrations, gearshift

South. A brand new light-weight Django migrations tool from Andrew Godwin. On first glance, this is spookily similar to the system we’ve been putting together at GCap.

# 8th August 2008, 11:42 am / gcap, django, andrew-godwin, migrations, south

DbMigration—a schema migration tool for Django. Nice and simple tool for adding schema migrations to a Django application.

# 27th September 2007, 3:04 pm / django, orm, djangoorm, migrations, sql, python