# Better database migrations in Postgres

DevFeed: [Better database migrations in Postgres](<https://devfeed.tech/articles/better-database-migrations-in-postgres-41199.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2017/09/10/better-postgres-migrations/>)

Author: Map

Published: 2017-09-10T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Database](<https://devfeed.tech/topics/database.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Development](<https://devfeed.tech/topics/development.md>)

Tags: [database](<https://devfeed.tech/tags/database.md>), [downtime](<https://devfeed.tech/tags/downtime.md>), [migrations](<https://devfeed.tech/tags/migrations.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [production](<https://devfeed.tech/tags/production.md>)

## AI overview

This article explains how Postgres schema migrations can affect production as databases grow. It focuses on adding columns, constraints, default values, and indexes, and recommends staged migrations with gradual backfilling to reduce downtime.

## Source excerpt

As your database grows and scales there are some operations that you need to take more care of than you did when you were just starting. When working with your application in your dev environment you may not be fully aware of the cost of some operations until you run them against production. And at some point most of us have been guilty of it, running some migration that starts at 5 minutes, then 15 minutes in it's still running, and suddenly production traffic is impacted. There are two operations that tend to happen quite frequently, each with some straightforward approaches to mitigate having any noticable amount of downtime. Let's look at each of the operations, how they work and then how you can approach them in a safer way. Adding new columns Adding a new column is actually quite cheap in Postgres. When you do this it updates its underlying tracking of the columns that exist-which is almost instant. The part that becomes expensive is when you have some constraint against the column. A constraint could be a primary or foreign key, or some uniqueness constraint. Here Postgres has to scan through all the records in the table to ensure that it's not being violated. Adding some constraint such as not null does happen some, but is not the most common cause. The most common reason for slowness of adding a new column is that most frameworks make it very simple for you to set a default value for the new column. It's one thing to do this for all new records, but when you do this when an existing table it means the database has to read all the records and re-write them with the new default value attached. This isn't so bad for a table with a few hundred records, but for a few hundred million run it then go get yourself coffee, or lunch, or a 5 course meal because you'll be waiting for a while. In short, not null and setting a default value (on creation) of your new column will cause you pain. The solution is to not do those things. But, what if you want to have a default