`lock_timeout` and `statement_timeout` in migrations

Capsule overview

You already know how to run non-blocking DDL operations (capsule 04). But even "safe" operations can hang in production from external causes: another transaction took the lock your migration needs, an analytical query has been using the table for hours, a maintenance job got stuck. With no timeout configured, your migration waits indefinitely — and the deploy stays blocked, the new pods don't come up, the rollout hangs.

This capsule teaches you the most important defensive reflex of production migrations: opening every dangerous migration with SET lock_timeout and SET statement_timeout so it fails fast instead of hanging. A migration that fails can roll back automatically and retry later; a hung migration paralyzes the deploy and demands manual intervention at 3am.

By the end you'll have internalized the "always set timeouts at the start" pattern, you're going to understand when lock_timeout and statement_timeout apply (they aren't the same), when you need SET LOCAL statement_timeout = '0' for long backfills, and you're going to have a migration template to copy as a starting point for any DDL in production.

Mental model: the migration's seatbelt

When you drive a car, you put on the seatbelt even though you know you're almost never going to crash. The probability of a crash is low, but the cost of not having it when it happens is enormous. Setting lock_timeout in a migration is exactly that: most of the time it won't fire, but when it does it saves you hours of incident.

Without lock_timeout, a migration that can't take a lock waits indefinitely. It goes into the queue and waits its turn. If the transaction holding the lock is hung (a dev forgot to COMMIT in psql, an analytical transaction took a READ lock for 6 hours, an undetected deadlock), your migration waits 6 hours. Meanwhile:

• The deploy stays at "running migration..."
• The new pods don't get promoted to serving traffic.
• The rollout doesn't advance.
• If you configured the right alerts, your pager goes off.

With lock_timeout = '5s', the same migration:

• Tries to take the lock.
• If it doesn't take it in 5 seconds, it fails with ERROR: canceling statement due to lock timeout.
• The deploy reports an error and rolls back automatically.
• Your pipeline marks the deploy as failed.
• You investigate what had the lock, resolve it, retry.

The operational difference is enormous: minutes of troubleshooting vs hours of incident. That's the seatbelt.

The critical distinction: lock_timeout vs statement_timeout

They're two different parameters covering two different scenarios. Confusing them is one of the most common mistakes.

lock_timeout: the maximum time waiting for a lock

SET lock_timeout = '5s';

It applies when your query is waiting for another query to release a lock. If you wait more than 5 seconds to take the lock, it fails with:

ERROR: canceling statement due to lock timeout

When it matters: every DDL operation that takes a lock can end up waiting if another transaction already has it. Specifically, operations that take ACCESS EXCLUSIVE (ALTER TABLE, DROP TABLE, etc.) are sensitive because they conflict with anything else.

statement_timeout: the maximum total execution time

SET statement_timeout = '30s';

It applies to the total time the query is running, including the time waiting for a lock. If the query (waiting + executing) takes more than 30 seconds total, it fails with:

ERROR: canceling statement due to statement timeout

When it matters: queries that can run correctly but take a long time. For example, a large backfill, a VALIDATE CONSTRAINT over an enormous table, a CREATE INDEX that gets slow from bloat.

Comparison table

Set both in dangerous migrations

SET lock_timeout = '5s';        -- Don't wait more than 5s to take a lock
SET statement_timeout = '60s';  -- Total execution no more than 60s

Combined:

• If in 5s it doesn't take the lock → it fails.
• If it takes the lock fast but the operation takes more than 60s → it fails.
• If it takes the lock in 3s and finishes in 50s → it runs correctly.

The canonical pattern for every dangerous migration

Every migration touching tables with production traffic has to open by setting both timeouts. It's the pattern you're going to repeat until it becomes the default.

# alembic/versions/XXX_some_dangerous_migration.py
"""add column XYZ to tasks

Revision ID: XXX
Revises: previous
"""
from alembic import op


revision = "XXX"
down_revision = "previous"


def upgrade():
    # THE CANONICAL PATTERN: timeouts at the start
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET statement_timeout = '60s'")

    # The actual DDL operation
    op.add_column("tasks", sa.Column("xyz", sa.Integer(), nullable=True))


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET statement_timeout = '60s'")
    op.drop_column("tasks", "xyz")

Suggested default values:

• lock_timeout = '5s' for normal DDL operations. If it doesn't take the lock in 5s, something odd is happening — better to fail and diagnose.
• statement_timeout = '60s' for DDL operations you know should be fast (ADD COLUMN nullable, DROP COLUMN). If it takes longer, something's wrong.

Operations where the defaults do NOT apply:

• Long backfills (a batched UPDATE totaling minutes): you need SET LOCAL statement_timeout = '0' (no limit). We cover this in the next section.
• CREATE INDEX CONCURRENTLY on a very large table: it can legitimately take minutes. Consider statement_timeout = '600s' (10 min) or disabling it.
• VALIDATE CONSTRAINT on a very large table: similar, it can take minutes.

For those cases, the pattern is:

def upgrade():
    op.execute("SET lock_timeout = '5s'")           # Still critical
    op.execute("SET LOCAL statement_timeout = '0'")  # Disable the total timeout for this migration

    # An operation that can take minutes
    with op.get_context().autocommit_block():
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_priority "
            "ON tasks (priority)"
        )

SET LOCAL applies only to the current transaction. When the migration finishes, it goes back to the global statement_timeout (configured by the DBA, typically 30s or none).

The special case: long backfills

Backfills (batched UPDATEs that populate a new column) are the scenario where statement_timeout can break you.

Why it's problematic: most production DBs have a global statement_timeout configured by the DBA (typically 30s) to keep badly-written production queries from hanging the system. Your backfill that legitimately takes 10 minutes runs into that limit and dies at 30s, leaving the backfill incomplete.

The incorrect solution: modifying the global statement_timeout. That would affect every production query, opening the door to undetected slow queries.

The correct solution: use SET LOCAL statement_timeout = '0' at the start of the backfill. This disables the timeout only for the migration's current transaction, without affecting production queries.

def upgrade():
    # For long backfills: disable statement_timeout locally.
    # Do NOT use SET (without LOCAL) — that would affect other connections in the pool.
    op.execute("SET LOCAL statement_timeout = '0'")

    # Keep lock_timeout — the batch's individual queries have to fail fast
    # if they find an active lock, not wait for hours.
    op.execute("SET lock_timeout = '5s'")

    connection = op.get_bind()
    BATCH_SIZE = 10_000
    SLEEP_SEC = 0.1

    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE priority IS NULL")
    ).scalar() or 0

    batch_min = 0
    while batch_min <= max_id:
        connection.execute(
            sa.text(
                "UPDATE tasks SET priority = 0 "
                "WHERE id BETWEEN :a AND :b AND priority IS NULL"
            ),
            {"a": batch_min, "b": batch_min + BATCH_SIZE - 1},
        )
        batch_min += BATCH_SIZE
        time.sleep(SLEEP_SEC)

The mental pattern:

• Fast, targeted DDL operations: a short lock_timeout, a short statement_timeout.
• Backfills or legitimately long operations: a short lock_timeout (still important), statement_timeout = '0' LOCAL.
• In no case modify these parameters globally from a migration.

The gotcha: SET vs SET LOCAL in migrations

This connects with what you saw in module 4 about RLS and PgBouncer.

SET parameter = value: affects the whole session (the current connection). When the transaction ends, the setting persists as long as the connection exists.

SET LOCAL parameter = value: affects only the current transaction. When the transaction ends (COMMIT or ROLLBACK), the setting gets cleared automatically.

Which one to use in migrations?

In Alembic's context, both work for your purpose because:

• Alembic opens a connection, runs the migration, closes the connection.
• If you use SET, the setting persists on that connection until it closes. But the connection closes at the end of the migration.
• If you use SET LOCAL, the setting persists only during the migration's transaction. But that's the whole relevant duration.

Recommendation: use SET LOCAL for statement_timeout (because it's the standard pattern and it teaches safe reflexes that also apply to application code). Use SET or SET LOCAL interchangeably for lock_timeout (both work).

If your Alembic is configured with transaction_per_migration=False or your pipeline reuses connections in some exotic way, always use SET LOCAL to avoid leaks between migrations.

Practical cases: timeouts by operation type

Here's a reference table you can copy into your team's runbook:

When to deviate from the defaults:

• If your DB has frequent long analytical transactions, consider lock_timeout = '10s' to give some margin, but ideally coordinate with the analytics team so their queries don't block.
• If your app has very low traffic, you can use longer timeouts with little risk. But it's better to keep the defaults to build the reflex.

Why does this matter in real work?

1. It's what separates a "failed deploy" from a "hung deploy." A failed deploy is manageable noise: the pipeline goes red, retry. A hung deploy is an incident: pods in an intermediate state, rollouts stopped, someone has to go in by hand. Setting timeouts turns the second scenario into the first.

2. It saves you the "the migration has been running for 2 hours" scenario. Without lock_timeout, the migration can wait for a lock indefinitely. Eventually somebody notices, tries to cancel, can't (because the cancellation also waits for the lock), and ends up doing pg_cancel_backend or pg_terminate_backend. With lock_timeout, that scenario doesn't even exist.

3. It's a reflex you build once. You don't have to think about it every migration: you add it as "always the first line" and it becomes automatic. Like putting on the seatbelt when you get in the car.

4. It's one of the first patterns a new dev notices when they join your team. If every migration opens with SET lock_timeout = '5s', that's a visible signal of a serious team. The new dev is going to copy it in their migrations without anyone explaining it. Team culture built with code.

Traps and common mistakes

Mistake 1 (operational): not setting any timeout

Symptom: a migration in production sits waiting for a lock for hours. The deploy hangs. Eventually somebody kills it manually.

Why it happens: the dev wrote the migration with a direct op.add_column(...) without thinking about scenarios where somebody else has the lock. In development it always works because there's no concurrent traffic.

How to tell: review any migration touching a production table. Does it have SET lock_timeout at the start? If not, it's vulnerable.

How to fix it: add op.execute("SET lock_timeout = '5s'") as the first line of upgrade() and downgrade(). Consider it non-negotiable.

Mistake 2 (conceptual): confusing lock_timeout with statement_timeout

Symptom: a dev sets statement_timeout = '5s' thinking it's going to fail fast if it can't take the lock. But the migration legitimately takes 8 seconds (the operation + the lock) and dies from the statement_timeout when it already had the lock and was executing.

Why it happens: both parameters have "timeout" in the name and sound similar. But they do different things.

How to tell: lock_timeout applies before the query starts executing (while it waits for a lock). statement_timeout applies to the total time (waiting + executing). If you want "fail fast if it doesn't take the lock," that's lock_timeout. If you want "fail if the query takes more than X total," that's statement_timeout.

How to fix it: set both with appropriate values. A short lock_timeout always. A statement_timeout adapted to the operation (short for targeted DDL, long or disabled for backfills).

Mistake 3 (critical operational): modifying the global statement_timeout from a migration

Symptom: a dev does ALTER DATABASE myapp SET statement_timeout = '0' from a migration so their backfill doesn't die. Afterward, every production query in the app can run indefinitely — badly-written slow queries (e.g. a JOIN with no index) hang connections for hours.

Why it happens: the dev confused "I need this migration to take longer" with "I need to relax the global timeout." They found ALTER DATABASE and applied it.

How to tell: any ALTER DATABASE ... SET ... or ALTER ROLE ... SET ... in a migration is suspicious. Those commands change persistent config, they don't apply only to the migration.

How to fix it: use SET LOCAL for migration-specific timeouts. If you need to change the DB's global config, do it as a conscious DBA decision, not as a side effect of a migration.

Mistake 4 (conceptual): not understanding that SET LOCAL statement_timeout = '0' is safe

Symptom: a dev is afraid to put statement_timeout = '0' (no limit) in their backfill. They leave it at 30s and the backfill dies at 30s.

Why it happens: "no limit" sounds dangerous. Intuition says "always put some limit just in case."

How to tell: SET LOCAL statement_timeout = '0' applies only to your migration's current transaction. When it finishes, it goes back to the global. It doesn't affect other queries.

How to fix it: use SET LOCAL statement_timeout = '0' with confidence for legitimately long operations (backfills, VALIDATE CONSTRAINT on an enormous table, CREATE INDEX CONCURRENTLY on a table with 100M rows). The scope is bounded.

Mistake 5 (operational): overly generous timeouts

Symptom: a dev puts lock_timeout = '60s' "just in case." A migration sits waiting for a lock for 60 seconds, during which the deploy also waits. If this happens frequently, that's 1 minute lost per deploy.

Why it happens: intuition says "more time = safer." But more time waiting for a lock = more time of a slow deploy.

How to tell: is there a concrete reason to wait that long? If the migration should take the lock in milliseconds when there's no contention, waiting 60s only applies when something's wrong.

How to fix it: keep lock_timeout short (5s). If it's blocked more than 5s, there's a concrete problem to diagnose — waiting longer doesn't fix it, it just delays the diagnosis.

Exercises

Exercise 1: classify appropriate timeouts by operation type

For each operation, indicate the timeouts you'd set at the start of the migration and why:

1. op.add_column("tasks", sa.Column("notes", sa.Text(), nullable=True))
2. op.execute("ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL") (with no NOT VALID trick, a 1M-row table)
3. A batched backfill UPDATE over 50M rows, estimated total ~30 minutes
4. op.execute("CREATE INDEX CONCURRENTLY ... ON tasks (priority)") over a 100M-row table
5. op.drop_index("idx_tasks_old", "tasks") (an unused index)

op.execute("SET lock_timeout = '5s'")
op.execute("SET statement_timeout = '30s'")

op.execute("SET lock_timeout = '5s'")
op.execute("SET statement_timeout = '120s'")

op.execute("SET lock_timeout = '5s'")
op.execute("SET LOCAL statement_timeout = '0'")

op.execute("SET lock_timeout = '5s'")
op.execute("SET LOCAL statement_timeout = '0'")
# Then: with op.get_context().autocommit_block(): ...

op.execute("SET lock_timeout = '5s'")
op.execute("SET statement_timeout = '30s'")

Exercise 2: detect and fix a migration with no timeouts

This migration caused a production incident where the deploy hung for 2 hours. Identify the problems and rewrite it.

# alembic/versions/incident_migration.py
def upgrade():
    # Backfill priority
    op.execute("UPDATE tasks SET priority = 0 WHERE priority IS NULL")

    # Add an index on priority
    op.create_index("idx_tasks_priority", "tasks", ["priority"])

    # Set NOT NULL
    op.alter_column("tasks", "priority", nullable=False)


def downgrade():
    op.alter_column("tasks", "priority", nullable=True)
    op.drop_index("idx_tasks_priority", "tasks")
    op.execute("UPDATE tasks SET priority = NULL")

# Migration 1: expand (add the column)
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET statement_timeout = '30s'")
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=True))


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET statement_timeout = '30s'")
    op.drop_column("tasks", "priority")


# Migration 2: a batched backfill
import time

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '0'")  # The backfill can take a while

    connection = op.get_bind()
    BATCH_SIZE = 10_000
    SLEEP_SEC = 0.1

    max_id = connection.execute(
        sa.text("SELECT COALESCE(MAX(id), 0) FROM tasks WHERE priority IS NULL")
    ).scalar() or 0

    batch_min = 0
    while batch_min <= max_id:
        connection.execute(
            sa.text(
                "UPDATE tasks SET priority = 0 "
                "WHERE id BETWEEN :a AND :b AND priority IS NULL"
            ),
            {"a": batch_min, "b": batch_min + BATCH_SIZE - 1},
        )
        batch_min += BATCH_SIZE
        time.sleep(SLEEP_SEC)


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '0'")
    op.execute("UPDATE tasks SET priority = NULL")


# Migration 3: contract (SET NOT NULL with the trick)
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET statement_timeout = '120s'")  # The VALIDATE can take a while

    # The NOT VALID + VALIDATE trick (capsule 03)
    op.execute(
        "ALTER TABLE tasks ADD CONSTRAINT tasks_priority_check "
        "CHECK (priority IS NOT NULL) NOT VALID"
    )
    op.execute("ALTER TABLE tasks VALIDATE CONSTRAINT tasks_priority_check")
    op.alter_column("tasks", "priority", nullable=False)
    op.execute("ALTER TABLE tasks DROP CONSTRAINT tasks_priority_check")


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    op.alter_column("tasks", "priority", nullable=True)


# Migration 4 (separate): add the index CONCURRENTLY
def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("SET LOCAL statement_timeout = '0'")  # The CREATE INDEX can take a while

    with op.get_context().autocommit_block():
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_priority "
            "ON tasks (priority)"
        )


def downgrade():
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_tasks_priority")

Exercise 3: implement a reusable migration template

Create a helper function or snippet that automatically generates a migration with the appropriate timeouts according to the operation type. The idea is to reduce "I forgot to set timeouts" mistakes.

# alembic/templates/safe_migration_helpers.py
"""Helpers for creating safe migrations with appropriate timeouts.

Import in each migration:
    from alembic.templates.safe_migration_helpers import (
        set_safe_timeouts,
        set_long_running_timeouts,
    )
"""
from alembic import op


def set_safe_timeouts(
    lock_timeout_sec: int = 5,
    statement_timeout_sec: int = 30,
) -> None:
    """Sets conservative timeouts for targeted DDL operations.

    Use in: ADD COLUMN, DROP COLUMN, RENAME, metadata-only constraint alters.

    Args:
        lock_timeout_sec: the maximum time waiting for a lock (default 5s).
        statement_timeout_sec: the maximum total execution time (default 30s).
    """
    op.execute(f"SET lock_timeout = '{lock_timeout_sec}s'")
    op.execute(f"SET LOCAL statement_timeout = '{statement_timeout_sec}s'")


def set_long_running_timeouts(lock_timeout_sec: int = 5) -> None:
    """Sets timeouts for legitimately long operations.

    Use in: batched backfills, CREATE INDEX CONCURRENTLY,
            VALIDATE CONSTRAINT on a large table.

    The statement_timeout gets disabled (LOCAL) because the operation
    can legitimately take minutes. The lock_timeout stays
    so it fails fast if it hits contention.

    Args:
        lock_timeout_sec: the maximum time waiting for a lock (default 5s).
    """
    op.execute(f"SET lock_timeout = '{lock_timeout_sec}s'")
    op.execute("SET LOCAL statement_timeout = '0'")


def set_concurrent_index_timeouts() -> None:
    """A specific alias for CREATE INDEX CONCURRENTLY."""
    set_long_running_timeouts()

# alembic/versions/00X_add_priority_column.py
from alembic import op
import sqlalchemy as sa
from alembic.templates.safe_migration_helpers import set_safe_timeouts


revision = "00X_add_priority"
down_revision = "previous"


def upgrade():
    set_safe_timeouts()  # 5s lock, 30s statement
    op.add_column("tasks", sa.Column("priority", sa.Integer(), nullable=True))


def downgrade():
    set_safe_timeouts()
    op.drop_column("tasks", "priority")

# alembic/versions/00Y_backfill_priority.py
from alembic import op
import sqlalchemy as sa
from alembic.templates.safe_migration_helpers import set_long_running_timeouts


def upgrade():
    set_long_running_timeouts()  # 5s lock, no statement timeout

    connection = op.get_bind()
    BATCH_SIZE = 10_000
    # ... the backfill loop

# scripts/check_migrations_use_helpers.py
"""A pre-commit hook: verifies Alembic migrations include a timeout setup."""
import sys
from pathlib import Path
import re

MIGRATIONS_DIR = Path("alembic/versions")
TIMEOUT_PATTERNS = [
    r"set_safe_timeouts\(",
    r"set_long_running_timeouts\(",
    r"set_concurrent_index_timeouts\(",
    r"SET\s+lock_timeout",  # Also allows the raw pattern
]


def check_file(path: Path) -> list[str]:
    content = path.read_text()
    if "def upgrade" not in content:
        return []  # Not a migration

    has_timeout = any(re.search(p, content, re.IGNORECASE) for p in TIMEOUT_PATTERNS)
    if not has_timeout:
        return [f"{path}: missing timeout setup in upgrade()"]
    return []


def main() -> int:
    errors = []
    for migration_file in MIGRATIONS_DIR.glob("*.py"):
        errors.extend(check_file(migration_file))

    if errors:
        print("ERROR: migrations with no timeouts configured:", file=sys.stderr)
        for e in errors:
            print(f"  - {e}", file=sys.stderr)
        return 1

    print(f"OK: every migration has timeouts configured")
    return 0


if __name__ == "__main__":
    sys.exit(main())

- repo: local
  hooks:
    - id: check-migration-timeouts
      name: Verify Alembic migrations have timeouts
      entry: python scripts/check_migrations_use_helpers.py
      language: system
      pass_filenames: false
      files: ^alembic/versions/.*\.py$

Exercise 4: simulate a lock_timeout locally

On your local DB, demonstrate that lock_timeout works correctly. Steps:

1. Session A: run BEGIN; LOCK TABLE migration_test IN ACCESS EXCLUSIVE MODE;. Do NOT commit or rollback.
2. Session B: run an operation that needs a lock, first with no lock_timeout (you're going to see it hang), then with lock_timeout = '3s' (you're going to see it fail with an error).
3. Clean up: in session A, run ROLLBACK;.

Document the outputs of each step.

postgres=# BEGIN;
BEGIN
postgres=# LOCK TABLE migration_test IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
-- The transaction is now active with the lock. Do NOT COMMIT yet.

postgres=# ALTER TABLE migration_test ADD COLUMN test_col INTEGER NULL;
-- Here the session hangs indefinitely waiting for the lock.
-- Ctrl+C to cancel manually:
^C
ERROR:  canceling statement due to user request

postgres=# SET lock_timeout = '3s';
SET
postgres=# ALTER TABLE migration_test ADD COLUMN test_col INTEGER NULL;
-- After 3 seconds:
ERROR:  canceling statement due to lock timeout

postgres=# SELECT
    pg_class.relname,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.pid,
    pg_stat_activity.query
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_class.relname = 'migration_test';

 relname        |        mode         | granted | pid  |              query
----------------+---------------------+---------+------+------------------------------------------
 migration_test | ACCESS EXCLUSIVE    | t       | 1234 | LOCK TABLE migration_test IN ACCESS...
 migration_test | ACCESS EXCLUSIVE    | f       | 5678 | ALTER TABLE migration_test ADD COLUMN...

postgres=# ROLLBACK;
ROLLBACK

postgres=# SET statement_timeout = '3s';
SET
postgres=# ALTER TABLE migration_test ADD COLUMN test_col INTEGER NULL;
-- After 3 seconds:
ERROR:  canceling statement due to statement timeout

Summary and next step

In this capsule you learned:

• lock_timeout and statement_timeout are different. The first is the time waiting for a lock; the second is the total time (waiting + executing).
• The canonical pattern of every dangerous migration opens with SET lock_timeout = '5s' and SET statement_timeout = '30s' (or '0' LOCAL for long operations).
• A migration with no lock_timeout can hang the deploy for hours. The migration's seatbelt.
• For backfills and legitimately long operations, use SET LOCAL statement_timeout = '0' to disable the timeout only in that transaction, without affecting production queries.
• Never modify the global statement_timeout from a migration. The scope has to be LOCAL.
• SET LOCAL vs SET: both work in the Alembic context, but SET LOCAL is the safe pattern that teaches the right reflexes.
• A helper template reduces mistakes and makes the pattern visible in code review.

Before moving on you should be able to:

• Set appropriate lock_timeout and statement_timeout in any migration according to its operation type.
• Distinguish when to use short timeouts (targeted DDL) vs long ones vs '0' (backfills).
• Diagnose and empirically demonstrate with two psql sessions that lock_timeout works.
• Recognize in code review the migrations that forget to set timeouts.
• Create a template or helper that reduces boilerplate and prevents mistakes.

Next capsule — Alembic in production: safe patterns. You now have the individual techniques (expand-contract, CONCURRENTLY, timeouts). Now you're going to see how they integrate into a complete production workflow: separating the code deploy from the migration deploy, migration idempotence so they survive retries, configuring env.py for multi-environment production, and the runbook you need at 3am when a migration hangs in production and you have to decide between killing it or waiting. It's the capsule that closes "techniques" and opens "production operation."

Resources

1. PostgreSQL — Runtime configuration: lock_timeout — the parameter's official documentation.
2. PostgreSQL — Runtime configuration: statement_timeout — the official documentation.
3. GitLab — lock_retries pattern — the pattern's most sophisticated version: retrying to take a lock with exponential backoff.
4. PostgreSQL — SET LOCAL vs SET — the differences and when to use each one.
5. Sebastian Insausti — Optimizing PostgreSQL: lock_timeout — an operational analysis of the parameters.
6. PlanetScale — Schema migration safety — how the lock problem gets tackled in modern schema migration platforms.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: Alembic in production — safe patterns, idempotence, and the hung-migration runbook.