Scaling Schema Changes

I frequently get asked how Disqus deals with schema changes. It’s a fair question, since we operate a fairly large amount of servers, but I also tend to think the answer is somewhat obvious. So let’s start with the problem of schema changes at scale (in PostgreSQL).

Generally you have some table, let’s call it a profile (since people seem to enjoy changing those). Well today, a new service has launched called Twooter, and we want to denormalize the user’s Twooter name into their profile. To do this we need to add a new field, twooter_username.

DDL First

The first thing we have to realize, is that everyone will not have twooter_username. Now even if that weren’t true, it needs to be to maintain compatibility, and efficiency. For us, this means that all additions must be made as NULLable columns. This means that the old code can stay in place whether the schema change has been made or not, and more importantly, NULLable ALTERs are much quicker in Postgres.

It’s very important that the schema change is made before the application’s new version is deployed. Ideally you want to do the change as soon as the schema is finalized. I’ll talk more a bit about the reasons for that later.

Application Changes

The second thing we need to concern ourselves with is our application logic. As I said before you must do the DDL before deploying your code changes. For us, this means all DDL happens in a branch, and can be merged once the change is completed. I also mentioned that additions must be NULLable, which not only means we can do the schema change before updating our application, but we also ensure forwards and backwards compatibility.

In addition to waiting for the schema change to complete before deploying your application, some changes may require several other steps along the release process. As an example, maybe we already had twooter_username stored in a different table, and we were literally just moving it to optimize our data access. This happens with a two things:

  • A write-through cache in the application to ensure new data is stored.
  • A backfill operation to ensure old data is stored (this also must be idempotent).

Once we’ve taken care of the above steps, only then can we actually utilize read operations on this new data. What this generally means is multi-step process to add a new data pattern:

  1. Perform DDL.
  2. Deploy write-through cache code.
  3. Run backfill operation.
  4. Run sanity checks (verify the data is correct, and exists).
  5. Deploy code which utilizes new data.

DDL on a Cluster

I’ve mostly been talking about how we scale the application side (read: code) for our DDL changes, but it’s also important to note how we do no-downtime schema changes. For this there are two important concepts we utilize: platform-wide read-only mode, and enough capacity to remove a node from the cluster. The last part is important: enough capacity to remove a node from the cluster.

Now let’s say this twooter_username is going to be added to a table which is so large, that even a fast NULLable ALTER cannot be run in production. In this case we’re actually going to need to swap out our master PG node to ensure we don’t hit any downtime, or slowness while making these changes. This is where read-only mode comes into play. It looks something like this:

  1. Take a slave out of the pool.
  2. Run DDL on slave.
  3. Put it back into the pool.
  4. (repeat on all slaves)
  5. Turn on read-only.
  6. Promote a slave to master.
  7. (repeat DDL operation on former-master)

And that’s all there is to it. I’d be curious to hear if anyone else is doing things differently.

Read full article at “David Cramer’s Blog”

Leave a comment