DDL Behavior¶

Transactional DDL¶

PostgreSQL DDL is transactional. If a migration file contains multiple statements and one fails, all prior DDL in that file is rolled back. This makes PostgreSQL the safest backend for automated migration runs.

Operations that cannot run inside a transaction block:

CREATE INDEX CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY
REINDEX DATABASE / REINDEX INDEX CONCURRENTLY
ALTER TABLE DETACH PARTITION CONCURRENTLY (PG 16+)
VACUUM / ANALYZE (maintenance commands)

Index Creation¶

DBWarden defaults to CREATE INDEX CONCURRENTLY to avoid table locking. Pass --no-concurrent when the migration must run inside a transaction block (PostgreSQL requires CONCURRENTLY outside a transaction).

CONCURRENTLY Behaviour¶

Requires more total time (two table scans)
Allows concurrent reads and writes during index build
If it fails, an invalid index is left behind (clean up with DROP INDEX CONCURRENTLY)
Not supported for CREATE INDEX ON ... with partitioned tables (PG 14+ added partial support)

Column Type Changes¶

Emits ALTER TABLE t ALTER COLUMN c TYPE newtype with a commented-out -- USING col::newtype line. Pass --postgres-auto-using to emit an active USING clause. Without the flag, uncomment and verify the USING expression before running the migration against production.

USING Clause Examples¶

-- String to enum conversion
ALTER TABLE users ALTER COLUMN status TYPE user_status USING status::user_status;

-- Text to boolean
ALTER TABLE users ALTER COLUMN active TYPE boolean USING active::boolean;

-- JSONB extraction
ALTER TABLE users ALTER COLUMN metadata TYPE text USING metadata->>'name';

Table Rewrite Behaviour¶

ALTER COLUMN TYPE rewrites the entire table (ACCESS EXCLUSIVE lock). Other operations that rewrite the table:

Operation	Rewrite?	Notes
`ALTER COLUMN TYPE`	Yes	Full table rewrite
`SET STORAGE`	Yes	Rewrites column data
`SET COMPRESSION`	Yes	Rewrites column data (PG 14+)
`SET TABLESPACE`	Yes	Moves entire table
`ALTER COLUMN SET/DROP NOT NULL`	No	Metadata only
`ALTER COLUMN SET/DROP DEFAULT`	No	Metadata only
`ADD COLUMN` (no default)	No	Metadata only
`ADD COLUMN` (volatile default)	Yes	Rewrites table
`ALTER TABLE SET (fillfactor)`	No	Metadata only
`DROP COLUMN`	No	Metadata only (mark as dropped)
`SET UNLOGGED`	Yes	Writes all data to WAL

Safe Type Change¶

The --safe-type-change flag generates a multi-step strategy: 1. Add a temporary column with the new type 2. Emit a -- comment with an UPDATE statement template 3. Emit a verification comment 4. After manual verification, drop the old column and rename the temporary column

Lock Levels¶

Operation	Lock Mode	Concurrent Access
`ALTER TABLE t ALTER COLUMN c TYPE`	`ACCESS EXCLUSIVE`	Blocks all reads and writes
`ALTER TABLE t ADD COLUMN c`	`ACCESS EXCLUSIVE` (brief)	Very short lock
`ALTER TABLE t DROP COLUMN c`	`ACCESS EXCLUSIVE`	Table rewrite avoided, metadata only
`CREATE INDEX` (non-concurrent)	`ACCESS EXCLUSIVE`	Blocks all access
`CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`	Allows reads and writes
`DROP INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`	Allows reads and writes
`ALTER TABLE t SET (fillfactor)`	`ACCESS EXCLUSIVE`	Brief metadata change
`VALIDATE CONSTRAINT`	`SHARE UPDATE EXCLUSIVE`	Allows reads and writes
`REFRESH MATERIALIZED VIEW CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`	Allows reads and writes

Generated Columns¶

Adding a generated column via ALTER TABLE is not supported by PostgreSQL. DBWarden emits a comment placeholder noting this limitation. Dropping the generation expression (ALTER COLUMN c DROP EXPRESSION) produces real DDL.

PostgreSQL supports GENERATED ALWAYS AS (expr) STORED only. Virtual generated columns are not supported.

Auto-increment Lifecycle¶

DBWarden supports toggling auto-increment on integer primary key columns:

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    class Meta(PGTableMeta):
        id = ColumnMeta(autoincrement=True)

To explicitly disable auto-increment:

id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=False)

Change	Generated SQL
Adding autoincrement	`CREATE SEQUENCE users_id_seq` + `ALTER COLUMN id SET DEFAULT nextval(...)` + `ALTER SEQUENCE ... OWNED BY`
Removing autoincrement	`ALTER COLUMN id DROP DEFAULT` + `DROP SEQUENCE IF EXISTS users_id_seq`

GENERATED ALWAYS vs GENERATED BY DEFAULT¶

Mode	Behaviour
`GENERATED ALWAYS AS IDENTITY`	User cannot provide a value; `INSERT` with explicit value fails
`GENERATED BY DEFAULT AS IDENTITY`	User can provide a value; auto-generation is skipped when value is supplied

Override for ALWAYS: INSERT OVERRIDING SYSTEM VALUE.

Detection from live databases¶

DBWarden detects autoincrement by: 1. SERIAL/BIGSERIAL column types 2. SQLAlchemy's .autoincrement attribute 3. nextval(...) default patterns

Type mapping¶

Condition	Resulting Type
`autoincrement=True` (default)	`SERIAL` / `BIGSERIAL`
`autoincrement=False`	`INTEGER` / `BIGINT`
`autoincrement=None` (unspecified)	`SERIAL` / `BIGSERIAL` (backward compatible)

DDL Limitations¶

Adding generated column: Not supported via ALTER TABLE; requires table recreation
CONCURRENTLY in transaction: CREATE INDEX CONCURRENTLY, REFRESH MATERIALIZED VIEW CONCURRENTLY, and similar operations cannot run inside a transaction block
Volatile default on ADD COLUMN: ALTER TABLE t ADD COLUMN c type DEFAULT random() rewrites the table because each row needs a distinct default value
Dropping column with dependencies: Requires CASCADE if views, FKs, or other objects reference the column
Removing enum values: PostgreSQL does not support ALTER TYPE ... DROP VALUE; enum values can only be added, renamed, or the type recreated with CASCADE
Composite type modification: No ALTER TYPE for composite types; must drop and recreate with CASCADE
Constraint rename: Must use ALTER TABLE t RENAME CONSTRAINT old TO new (no ALTER CONSTRAINT rename)
SET STORAGE after table creation: Rewrites the column data; cannot be done without a table lock