fly-apps / safe-ecto-migrations

Guide to Safe Ecto Migrations

Safe Ecto Migrations

A non-exhaustive guide on common migration recipes and how to avoid trouble.

• Adding an index
• Adding a reference or foreign key
• Adding a column with a default value
• Changing the type of a column
• Removing a column
• Renaming a column
• Renaming a table
• Adding a check constraint
• Setting NOT NULL on an existing column
• Adding a JSON column
• Squashing migrations

Read more about safe migrations at Fly.io Phoenix Files where we dive into how to safely backfill data and go through Ecto Migration options.

---

Adding an index

Creating an index will block both reads and writes in Postgres.

MySQL is concurrent by default since 5.6 unless using SPATIAL or FULLTEXT indexes (which then it blocks reads and writes).

BAD ❌

def change do
  create index("posts", [:slug])

  # This obtains a ShareLock on "posts" which will block writes to the table
end

GOOD ✅

With Postgres, instead create the index concurrently which does not block reads. There are two options:

Option 1

Configure the Repo to use advisory locks for locking migrations while running. Advisory locks are application-controlled database-level locks, and EctoSQL since v3.9.0 provides an option to use this type of lock. This is the safest option as it avoids the trade-off in Option 2.

Disable the DDL transaction in the migration to avoid a database transaction which is not compatible with CONCURRENTLY database operations.

# in config/config.exs
config MyApp.Repo, migration_lock: :pg_advisory_lock

# in the migration
@disable_ddl_transaction true

def change do
  create index("posts", [:slug], concurrently: true)
end

If you're using Phoenix and PhoenixEcto, you will likely appreciate disabling the migration lock in the CheckRepoStatus plug during dev to avoid hitting and waiting on the advisory lock with concurrent web processes. You can do this by adding migration_lock: false to the CheckRepoStatus plug in your MyAppWeb.Endpoint.

Option 2

Disable the DDL transaction and the migration lock for the migration. By default, EctoSQL with Postgres will run migrations with a DDL transaction and a migration lock which also (by default) uses another transaction. You must disable both of these database transactions to use CONCURRENTLY. However, disabling the migration lock will allow competing nodes to try to run the same migration at the same time (eg, in a multi-node Kubernetes environment that runs migrations before startup). Therefore, some nodes may fail startup for a variety of reasons.

@disable_ddl_transaction true
@disable_migration_lock true

def change do
  create index("posts", [:slug], concurrently: true)
end

For either option chosen, the migration may still take a while to run, but reads and updates to rows will continue to work. For example, for 100,000,000 rows it took 165 seconds to add run the migration, but SELECTS and UPDATES could occur while it was running.

Do not have other changes in the same migration; only create the index concurrently and separate other changes to later migrations.

---

Adding a reference or foreign key

Adding a foreign key blocks writes on both tables.

BAD ❌

def change do
  alter table("posts") do
    add :group_id, references("groups")
    # Obtains a ShareRowExclusiveLock which blocks writes on both tables
  end
end

GOOD ✅

In the first migration

def change do
  alter table("posts") do
    add :group_id, references("groups", validate: false)
    # Obtains a ShareRowExclusiveLock which blocks writes on both tables.
  end
end

In the second migration

def change do
  execute "ALTER TABLE posts VALIDATE CONSTRAINT group_id_fkey", ""
  # Obtains a ShareUpdateExclusiveLock which doesn't block reads or writes
end

These migrations can be in the same deployment, but make sure they are separate migrations.

Note on empty tables: when the table creating the referenced column is empty, you may be able to create the column and validate at the same time since the time difference would be milliseconds which may not be noticeable, no matter if you have 1 million or 100 million records in the referenced table.

Note on populated tables: the biggest difference depends on your scale. For 1 million records in both tables, you may lock writes to both tables when creating the column for milliseconds (you should benchmark for yourself) which could be acceptable for you. However, once your table has 100+ million records, the difference becomes seconds which is more likely to be felt and cause timeouts. The differentiating metric is the time that both tables are locked from writes. Therefore, err on the side of safety and separate constraint validation from referenced column creation when there is any data in the table.

---

Adding a column with a default value

Adding a column with a default value to an existing table may cause the table to be rewritten. During this time, reads and writes are blocked in Postgres, and writes are blocked in MySQL and MariaDB.

BAD ❌

Note: This becomes safe in:

• Postgres 11+
• MySQL 8.0.12+
• MariaDB 10.3.2+

def change do
  alter table("comments") do
    add :approved, :boolean, default: false
    # This took 10 minutes for 100 million rows with no fkeys,

    # Obtained an AccessExclusiveLock on the table, which blocks reads and
    # writes.
  end
end

GOOD ✅

Add the column first, then alter it to include the default.

First migration:

def change do
  alter table("comments") do
    add :approved, :boolean
    # This took 0.27 milliseconds for 100 million rows with no fkeys,
  end
end

Second migration:

def change do
  alter table("comments") do
    modify :approved, :boolean, default: false
    # This took 0.28 milliseconds for 100 million rows with no fkeys,
  end
end

Schema change to read the new column:

schema "comments" do
+ field :approved, :boolean, default: false
end

---

Changing the type of a column

Changing the type of a column may cause the table to be rewritten. During this time, reads and writes are blocked in Postgres, and writes are blocked in MySQL and MariaDB.

BAD ❌

Safe in Postgres:

• increasing length on varchar or removing the limit
• changing varchar to text
• changing text to varchar with no length limit
• Postgres 9.2+ - increasing precision (NOTE: not scale) of decimal or numeric columns. eg, increasing 8,2 to 10,2 is safe. Increasing 8,2 to 8,4 is not safe.
• Postgres 9.2+ - changing decimal or numeric to be unconstrained
• Postgres 12+ - changing timestamp to timestamptz when session TZ is UTC

Safe in MySQL/MariaDB:

• increasing length of varchar from < 255 up to 255.
• increasing length of varchar from > 255 up to max.

def change do
  alter table("posts") do
    modify :my_column, :boolean, from: :text
  end
end

GOOD ✅

Take a phased approach:

1. Create a new column
2. In application code, write to both columns
3. Backfill data from old column to new column
4. In application code, move reads from old column to the new column
5. In application code, remove old column from Ecto schemas.
6. Drop the old column.

---

Removing a column

If Ecto is still configured to read a column in any running instances of the application, then queries will fail when loading data into your structs. This can happen in multi-node deployments or if you start the application before running migrations.

BAD ❌

# Without a code change to the Ecto Schema

def change do
  alter table("posts") do
    remove :no_longer_needed_column
  end
end

GOOD ✅

Safety can be assured if the application code is first updated to remove references to the column so it's no longer loaded or queried. Then, the column can safely be removed from the table.

1. Deploy code change to remove references to the field.
2. Deploy migration change to remove the column.

First deployment:

# First deploy, in the Ecto schema

defmodule MyApp.Post do
  schema "posts" do
-   column :no_longer_needed_column, :text
  end
end

Second deployment:

def change do
  alter table("posts") do
    remove :no_longer_needed_column
  end
end

---

Renaming a column

Ask yourself: "Do I really need to rename a column?". Probably not, but if you must, read on and be aware it requires time and effort.

If Ecto is configured to read a column in any running instances of the application, then queries will fail when loading data into your structs. This can happen in multi-node deployments or if you start the application before running migrations.

There is a shortcut: Don't rename the database column, and instead rename the schema's field name and configure it to point to the database column.

BAD ❌

# In your schema
schema "posts" do
  field :summary, :text
end


# In your migration
def change do
  rename table("posts"), :title, to: :summary
end

The time between your migration running and your application getting the new code may encounter trouble.

GOOD ✅

Strategy 1

Rename the field in the schema only, and configure it to point to the database column and keep the database column the same. Ensure all calling code relying on the old field name is also updated to reference the new field name.

defmodule MyApp.MySchema do
  use Ecto.Schema

  schema "weather" do
    field :temp_lo, :integer
    field :temp_hi, :integer
    field :precipitation, :float, source: :prcp
    field :city, :string

    timestamps(type: :naive_datetime_usec)
  end
end

## Update references in other parts of the codebase:
   my_schema = Repo.get(MySchema, "my_id")
-  my_schema.prcp
+  my_schema.precipitation

Strategy 2

Take a phased approach:

1. Create a new column
2. In application code, write to both columns
3. Backfill data from old column to new column
4. In application code, move reads from old column to the new column
5. In application code, remove old column from Ecto schemas.
6. Drop the old column.

---

Renaming a table

Ask yourself: "Do I really need to rename a table?". Probably not, but if you must, read on and be aware it requires time and effort.

If Ecto is still configured to read a table in any running instances of the application, then queries will fail when loading data into your structs. This can happen in multi-node deployments or if you start the application before running migrations.

There is a shortcut: rename the schema only, and do not change the underlying database table name.

BAD ❌

def change do
  rename table("posts"), to: table("articles")
end

GOOD ✅

Strategy 1

Rename the schema only and all calling code, and don’t rename the table:

- defmodule MyApp.Weather do
+ defmodule MyApp.Forecast do
  use Ecto.Schema

  schema "weather" do
    field :temp_lo, :integer
    field :temp_hi, :integer
    field :precipitation, :float, source: :prcp
    field :city, :string

    timestamps(type: :naive_datetime_usec)
  end
end

# and in calling code:
- weather = MyApp.Repo.get(MyApp.Weather, “my_id”)
+ forecast = MyApp.Repo.get(MyApp.Forecast, “my_id”)

Strategy 2

Take a phased approach:

1. Create the new table. This should include creating new constraints (checks and foreign keys) that mimic behavior of the old table.
2. In application code, write to both tables, continuing to read from the old table.
3. Backfill data from old table to new table
4. In application code, move reads from old table to the new table
5. In application code, remove the old table from Ecto schemas.
6. Drop the old table.

---

Adding a check constraint

Adding a check constraint blocks reads and writes to the table in Postgres, and blocks writes in MySQL/MariaDB while every row is checked.

BAD ❌

def change do
  create constraint("products", :price_must_be_positive, check: "price > 0")
  # Creating the constraint with validate: true (the default when unspecified)
  # will perform a full table scan and acquires a lock preventing updates
end

GOOD ✅

There are two operations occurring:

1. Creating a new constraint for new or updating records
2. Validating the new constraint for existing records

If these commands are happening at the same time, it obtains a lock on the table as it validates the entire table and fully scans the table. To avoid this full table scan, we can separate the operations.

In one migration:

def change do
  create constraint("products", :price_must_be_positive, check: "price > 0", validate: false)
  # Setting validate: false will prevent a full table scan, and therefore
  # commits immediately.
end

In the next migration:

def change do
  execute "ALTER TABLE products VALIDATE CONSTRAINT price_must_be_positive", ""
  # Acquires SHARE UPDATE EXCLUSIVE lock, which allows updates to continue
end

These can be in the same deployment, but ensure there are 2 separate migrations.

---

Setting NOT NULL on an existing column

Setting NOT NULL on an existing column blocks reads and writes while every row is checked. Just like the Adding a check constraint scenario, there are two operations occurring:

1. Creating a new constraint for new or updating records
2. Validating the new constraint for existing records

To avoid the full table scan, we can separate these two operations.

BAD ❌

def change do
  alter table("products") do
    modify :active, :boolean, null: false
  end
end

GOOD ✅

Add a check constraint without validating it, backfill data to satiate the constraint and then validate it. This will be functionally equivalent.

In the first migration:

# Deployment 1
def change do
  create constraint("products", :active_not_null, check: "active IS NOT NULL", validate: false)
end

This will enforce the constraint in all new rows, but not care about existing rows until that row is updated.

You'll likely need a data migration at this point to ensure that the constraint is satisfied.

Then, in the next deployment's migration, we'll enforce the constraint on all rows:

# Deployment 2
def change do
  execute "ALTER TABLE products VALIDATE CONSTRAINT active_not_null", ""
end

If you're using Postgres 12+, you can add the NOT NULL to the column after validating the constraint. From the Postgres 12 docs:

SET NOT NULL may only be applied to a column provided none of the records in the table contain a NULL value for the column. Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint is found which proves no NULL can exist, then the table scan is skipped.

However we cannot use modify/3 as it will include updating the column type as well unnecessarily, causing Postgres to rewrite the table. For more information, see this example.

# **Postgres 12+ only**

def change do
  execute "ALTER TABLE products VALIDATE CONSTRAINT active_not_null",
          ""

  execute "ALTER TABLE products ALTER COLUMN active SET NOT NULL",
          "ALTER TABLE products ALTER COLUMN active DROP NOT NULL"

  drop constraint("products", :active_not_null)
end

If your constraint fails, then you should consider backfilling data first to cover the gaps in your desired data integrity, then revisit validating the constraint.

---

Adding a JSON column

In Postgres, there is no equality operator for the json column type, which can cause errors for existing SELECT DISTINCT queries in your application.

BAD ❌

def change do
  alter table("posts") do
    add :extra_data, :json
  end
end

GOOD ✅

Use jsonb instead. Some say it’s like “json” but “better.”

def change do
  alter table("posts") do
    add :extra_data, :jsonb
  end
end

---

Squashing Migrations

If you have a long list of migrations, sometimes it can take a while to migrate each of those files every time the project is reset or spun up by a new developer. Thankfully, Ecto comes with mix tasks to dump and load a database structure which will represent the state of the database up to a certain point in time, not including content.

• mix ecto.dump
• mix ecto.load

Schema dumping and loading is only supported by external binaries pg_dump and mysqldump, which are used by the Postgres, MyXQL, and MySQL Ecto adapters (not supported in MSSQL adapter).

For example:

20210101000000 - First Migration
20210201000000 - Second Migration
20210701000000 - Third Migration <-- we are here now. run `mix ecto.dump`

We can "squash" the migrations up to the current day which will effectively fast-forward migrations to that structure. The Ecto Migrator will detect that the database is already migrated to the third migration, and so it begins there and migrates forward.

Let's add a new migration:

20210101000000 - First Migration
20210201000000 - Second Migration
20210701000000 - Third Migration <-- `structure.sql` represents up to here
20210801000000 - New Migration <-- This is where migrations will begin

The new migration will still run, but the first-through-third migrations will not need to be run since the structure already represents the changes applied by those migrations. At this point, you can safely delete the first, second, and third migration files or keep them for historical auditing.

Let's make this work:

1. Run mix ecto.dump which will dump the current structure into priv/repo/structure.sql by default. Check the mix task for more options.
2. During project setup with an empty database, run mix ecto.load to load structure.sql.
3. Run mix ecto.migrate to run any additional migrations created after the structure was dumped.

To simplify these actions into one command, we can leverage mix aliases:

# mix.exs

defp aliases do
  [
    "ecto.reset": ["ecto.drop", "ecto.setup"],
    "ecto.setup": ["ecto.load", "ecto.migrate"],
    # ...
  ]
end

Now you can run mix ecto.setup and it will load the database structure and run remaining migrations. Or, run mix ecto.reset and it will drop and run setup. Of course, you can continue running mix ecto.migrate as you create them.

---

Credits

Created and written by David Bernheisel with recipes heavily inspired from Andrew Kane and his library strong_migrations.

PostgreSQL at Scale by James Coleman

Strong Migrations by Andrew Kane

Adding a NOT NULL CONSTRAINT on PG Faster with Minimal Locking by Christophe Escobar

Postgres Runtime Configuration

Automatic and Manual Ecto Migrations by Wojtek Mach

Special thanks for sponsorship:

• Fly.io

Special thanks for these reviewers:

• Steve Bussey
• Stephane Robino
• Dennis Beatty
• Wojtek Mach
• Mark Ericksen
• And all the contributors

Reference Material

Postgres Lock Conflicts

	Current Lock →
Requested Lock ↓	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

• SELECT acquires a ACCESS SHARE lock
• SELECT FOR UPDATE acquires a ROW SHARE lock
• UPDATE, DELETE, and INSERT will acquire a ROW EXCLUSIVE lock
• CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT acquires SHARE UPDATE EXCLUSIVE
• CREATE INDEX acquires SHARE lock

Knowing this, let's re-think the above table:

	Current Operation →
Blocks Operation ↓	SELECT	SELECT FOR UPDATE	UPDATE DELETE INSERT	CREATE INDEX CONCURRENTLY VALIDATE CONSTRAINT	CREATE INDEX	SHARE ROW EXCLUSIVE	EXCLUSIVE	ALTER TABLE DROP TABLE TRUNCATE REINDEX CLUSTER VACUUM FULL
SELECT								X
SELECT FOR UPDATE							X	X
UPDATE DELETE INSERT					X	X	X	X
CREATE INDEX CONCURRENTLY VALIDATE CONSTRAINT				X	X	X	X	X
CREATE INDEX			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ALTER TABLE DROP TABLE TRUNCATE REINDEX CLUSTER VACUUM FULL	X	X	X	X	X	X	X	X

safe-ecto-migrations's Issues

Verify concurrent index table locks

It looks like the lock obtained in Postgres when creating an index concurrently SHARE UPDATE EXCLUSIVE should allow writes ROW EXCLUSIVE, but the guide currently implies that writes are blocked.

With Postgres, instead create the index concurrently which does not block reads. There are two options:

Postgres docs:

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.
Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).

ROW EXCLUSIVE (RowExclusiveLock)
Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.
The commands UPDATE, DELETE, INSERT, and MERGE acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

Adding non-immutable default value to existing table is not safe

https://www.postgresql.org/docs/current/sql-altertable.html

Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. However, indexes must always be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one. For example, if the collation for a column has been changed, an index rebuild is always required because the new sort order might be different. However, in the absence of a collation change, a column can be changed from text to varchar (or vice versa) without rebuilding the indexes because these data types sort identically. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

(emphasis mine)

The Safe Ecto Migrations note about adding a new column with a default implies that it becomes totally safe in Postgres 11+, but that's not entirely true because of the highlighted caveat above.

We should add a note to call this out.

Advisory lock for migration transactions

Hello,
Not sure if this is the best place to ask but I will shoot it anyway ;).

I'm Rails developer learning Elixir and I have a question about safe Ecto migrations. Is it possible to use advisory lock instead of share update exclusive on schema_migrations table (like Rails does it) when creating a migration record?

I'm asking because (as fair as I understand) advisory lock would allow to disable ddl transaction for the migration itself (i.e. adding index concurrently) but it would still make sure that only one node can execute it (in multi node setup).

Add hint to tune down `:migration_advisory_lock_retry_interval_ms` in `dev` environment when using `:pg_advisory_lock`

In the "Adding an index" section it is advised to configure the Repo to use advisory locks with :pg_advisory_lock. This can cause long phoenix request delays (5s 😮) in phoenix dev environment. This is due to Phoenix.Ecto.CheckRepoStatus calling Ecto.Migrator.migrations/1, which runs under a migration lock. When “Ecto cannot obtain the lock due to another instance occupying the lock, Ecto will wait for 5 seconds”. In dev environment, with CheckRepoStatus plug, you just need two web requests to hit your phoenix server at the very same time to get into that situation. We stumbled into it with a JS app that, after load, fired two simultaneous GraphQL requests. One of them would take 5s.

I already asked in Elixir Forum whether it is necessary that this convenience plug runs under a migration lock.

We worked around the problem by tuning down the :migration_advisory_lock_retry_interval_ms setting from its default value of 5000 ms to 10 ms (just in dev). Unless there are better options, maybe you can add that hint to the guide?

Recommend `:text` instead of `:string`

I know this but I need to find the right sources to cite and run my own benchmarks.

Here's a wonderful start: https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

It's not so much a caution of "don't do this" but more of a "steer in better direction with no trade-offs".

Docs on setting NON NULL safely on PG12+ may not be safe due to ecto-generated inclusion of `ALTER COLUMN ... TYPE ...`

For the recipe to set NON NULL on an existing column, I think there's a bug with one of the statements that will cause locking for extended periods of time in PG12. I have no tried to reproduce on any newer versions of postgres.

When it gets to the following step in the docs:

  alter table("products") do
    modify :active, :boolean, null: false
  end

Ecto generates the following SQL under the hood:

ALTER TABLE "products" ALTER COLUMN "active" TYPE boolean, ALTER COLUMN "active" SET NOT NULL;

Even though the type is the same as the existing column type, which should in itself be a no-op or metadata-only change, I believe it's causing postgres to not think that it can optimize the ALTER; it sees that additional work might be needed to be done at the same time and decides not to take the constraint-check shortcut route, thus triggering the table scan again while locked. At least that was my experience on PG 12.14.

Until ecto is smart enough to know that the column type is the same, and strips out the ALTER COLUMN ... TYPE ..., it might be dangerous to run an ecto-generated ALTER TABLE to perform this step vs. handcrafted SQL. Even then, you'd have to be on a certain version of ecto.

Steps to Reproduce

Assuming a table called "foo" with a lot of data and a column called "a" of type text with a UNIQUE constraint:

(I'm using 50 million rows, but locally on a fast NVMe drive w/ enough RAM to fully cache the data set, so single-digit second responses will be larger on an active production environment)

I'm running PG 12.2 for these timings, but we also experienced the issue on PG 12.14.

Creating and validating the constraint. Note the 3.3 seconds to do a full table scan:

testdb =# ALTER TABLE "foo" ADD CONSTRAINT "a_not_null" CHECK (a IS NOT NULL) NOT VALID;
ALTER TABLE
Time: 6.648 ms

testdb=# ALTER TABLE foo VALIDATE CONSTRAINT a_not_null;
ALTER TABLE
Time: 3365.122 ms (00:03.365)

Converting the column to non-null using SQL produced by mix ecto.migrate --log-migrations-sql. Note the inclusion of ALTER COLUMN "a" TYPE text and the time it takes to run being similar to the full table scan:

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" TYPE text, ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 3425.920 ms (00:03.426)

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.708 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" TYPE text, ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 3370.575 ms (00:03.371)

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.634 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" TYPE text, ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 3418.631 ms (00:03.419)

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.586 ms

Setting the NOT NULL without the type. Note response times in the single-digit milliseconds:

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 1.475 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.428 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 6.562 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.685 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" SET NOT NULL;
ALTER TABLE
Time: 6.757 ms

testdb =# ALTER TABLE "foo" ALTER COLUMN "a" DROP NOT NULL;
ALTER TABLE
Time: 6.445 ms

Example Table Setup

The following table setup was used to reproduce the issue:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE foo (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    a text,
    b text,
    c text,
    d text,
    e text,
    f text,
    g text,
    h text,
    i text,
    j text,
    k text,
    l text,
    m text,
    n text,
    o text,
    p text,
    q text,
    r text,
    s text,
    t text,
    u text,
    v text,
    w text,
    x text,
    y text,
    z text,
    CONSTRAINT a_uniq_idx UNIQUE(a)
);

INSERT INTO foo (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z)
  SELECT
    uuid_generate_v4(),
    concat('b-', i::text),
    concat('c-', i::text),
    concat('d-', i::text),
    concat('e-', i::text),
    concat('f-', i::text),
    concat('g-', i::text),
    concat('h-', i::text),
    concat('i-', i::text),
    concat('j-', i::text),
    concat('k-', i::text),
    concat('l-', i::text),
    concat('m-', i::text),
    concat('n-', i::text),
    concat('o-', i::text),
    concat('p-', i::text),
    concat('q-', i::text),
    concat('r-', i::text),
    concat('s-', i::text),
    concat('t-', i::text),
    concat('u-', i::text),
    concat('v-', i::text),
    concat('w-', i::text),
    concat('x-', i::text),
    concat('y-', i::text),
    concat('z-', i::text)
  FROM generate_series(1, 5000000) AS gs(i);

Benchmark creating foreign key references for empty table column

https://github.com/fly-apps/safe-ecto-migrations#adding-a-reference-or-foreign-key

If the reference is on a brand new and empty table, will the referenced table w/ millions of records be locked for a meaningful amount of time?

Backfill data page - question on "next_page = results |> Enum.reverse() |> List.first()"

Hi, in https://fly.io/phoenix-files/backfilling-data/#batching-deterministic-data - The code in getting the next_page id, this seems to imply that Repo.update_all would return the results in an ordered list, and in reversed order.

Would that be true? I wonder if you'd need to do some sorting on the result to get the tail of the result?