PostgreSQL+Elixir Phoenix环境下低停机Ecto数据库迁移方案咨询

Hey there! Let's walk through a safe, low-downtime approach to add a column with a default value to your ~1000-row table using Ecto for your Phoenix app. This method avoids long table locks and keeps your site available throughout the process.

Key Background: PostgreSQL's Behavior #

First, a quick context note: PostgreSQL 11+ doesn’t rewrite all rows when you add a column with a constant default value. Instead, it stores the default as metadata and returns it dynamically for existing rows on read. This makes the initial DDL operation nearly instant. For older PostgreSQL versions (<11), we’ll still use a phased approach to minimize lock time.

Step-by-Step Implementation #

1. Add the Column (Nullable with Default) #

Start by creating a migration that adds the column as nullable with your desired default value. This avoids an immediate full-table rewrite—critical for low downtime.

defmodule MyApp.Repo.Migrations.AddNewColumnToMyTable do
  use Ecto.Migration

  def change do
    # Replace :new_column, :my_table, :string, and default value with your actual details
    alter table(:my_table) do
      add :new_column, :string, default: "default_value", null: true
    end
  end
end

Run this with mix ecto.migrate—it’ll complete in milliseconds even for 1000 rows, since no row-level updates happen yet.

2. Backfill Existing Rows (Optional but Recommended) #

While PostgreSQL returns the default value for existing rows on read, if you plan to add a NOT NULL constraint later, you’ll need to backfill the column for all existing rows. Do this asynchronously in batches to avoid locking the entire table.

You can create a Mix task for this, or use a background job (like Oban) if you have one set up:

defmodule Mix.Tasks.MyApp.BackfillNewColumn do
  use Mix.Task
  import Ecto.Query
  alias MyApp.Repo
  alias MyApp.MyTable

  @batch_size 100

  def run(_args) do
    Mix.Task.run("app.start")

    from(t in MyTable, where: is_nil(t.new_column))
    |> Repo.stream(max_rows: @batch_size)
    |> Enum.each(fn row ->
      row
      |> MyTable.changeset(%{new_column: "default_value"})
      |> Repo.update!()
    end)

    IO.puts("Backfill complete!")
  end
end

Run this with mix my_app.backfill_new_column. For 1000 rows, this takes just a few seconds, and batched operations won’t block other database traffic.

3. Add NOT NULL Constraint (If Needed) #

Once all rows have the column filled, you can safely add the NOT NULL constraint. This is instant because PostgreSQL doesn’t need to recheck every row (we already backfilled them):

defmodule MyApp.Repo.Migrations.MakeNewColumnNotNull do
  use Ecto.Migration

  def change do
    alter table(:my_table) do
      modify :new_column, :string, default: "default_value", null: false
    end
  end
end

Run this with mix ecto.migrate—again, it’ll finish in milliseconds.

Expected Migration Timings #

For your ~1000-row table:

• Step 1: < 1 second (instant DDL in PostgreSQL 11+; even older versions finish quickly for this row count)
• Step 2: 2-5 seconds (batched updates with minimal locking)
• Step 3: < 1 second (instant constraint addition)

Total downtime risk is effectively zero, as each step is non-blocking or completes in milliseconds.

Additional Tips #

• If you don’t need the column to be NOT NULL, skip steps 2 and 3—the default value will work for existing rows without any backfill.
• Always test migrations in a staging environment first to match your exact PostgreSQL version and table characteristics.

内容的提问来源于stack exchange，提问作者relentless