adding-field-with-default

problem#

Adding a field with a default can cause table rewrites, which will take an ACCESS EXCLUSIVE lock on the table, blocking reads / writes while the statement is running.

In Postgres version 11 and later, adding a field with a non-VOLATILE DEFAULT will not require a table rewrite. Adding a field with a VOLATILE DEFAULT will cause a table rewrite.

solutions#

adding a non-volatile default in Postgres 11+#

note

This statement is only safe when your default is non-volatile.

ALTER TABLE "core_recipe" ADD COLUMN "foo" integer DEFAULT 10 NOT NULL;

adding a volatile default#

Add the field as nullable, then set a default, backfill, and remove nullabilty.

Instead of:

ALTER TABLE "core_recipe" ADD COLUMN "foo" integer DEFAULT 10 NOT NULL;

Use:

ALTER TABLE "core_recipe" ADD COLUMN "foo" integer;
ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET DEFAULT 10;
-- backfill column in batches
ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET NOT NULL;

We add our column as nullable, set a default for new rows, backfill our column (ideally done in batches to limit locking), and finally remove nullability.

See "How not valid constraints work" for more information on adding constraints as NOT VALID.

solution for alembic and sqlalchemy#

Instead of:

# models.py
import sqlalchemy as sa

class CoreRecipe(BaseModel):
    __tablename__ = "core_recipe"
    ...
    foo = sa.Column(sa.BigInteger, server_default="10", nullable=False)

Use:

# models.py
import sqlalchemy as sa

class CoreRecipe(BaseModel):
    __tablename__ = "core_recipe"
    ...
    foo = sa.Column(sa.BigInteger, default=10, nullable=True)

# migrations/*.py
import sqlalchemy as sa
from alembic import op

def schema_upgrades():
    op.add_column("core_recipe", sa.Column("foo", sa.BigInteger(), nullable=True))
    op.execute(
        sa.text(
            """
            ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET DEFAULT 10;
            """
        ),
    )
    # if you have the big table use batch update
    op.execute(
        sa.text("""UPDATE core_recipe SET foo = 10"""),
    )
    

def schema_downgrades():
    op.drop_column("core_recipe", "foo")