DEV Community: Fan()

Why PostgreSQL ADD COLUMN NOT NULL DEFAULT now() should not pass review on sight

Fan() — Wed, 26 Aug 2026 15:51:59 +0000

Adrian Van (GitHub: Fanduzi)

Last week a change ticket landed with one statement:

ALTER TABLE users ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();

The developer said: add a column, give it a default, old rows will not be NULL. The ticket was clean. The style check passed. Someone had already booked a low-traffic window.

I stopped it. The SQL is valid. The ticket still does not say which PostgreSQL version, whether the default is STABLE or VOLATILE, or whether every old row should share the ALTER's timestamp. Get those wrong and a large heap takes a long ACCESS EXCLUSIVE lock, a WAL spike, and as much as another copy of the table on disk. It looks like an add-column. It can run like a rebuild.

Adding a column is not always cheap

PostgreSQL does not treat every ADD COLUMN the same.

A nullable column with no default is cheap on current versions: catalog only, heap barely touched. PostgreSQL 11 also made a non-volatile default cheap. The value is evaluated once, stored in the catalog (atthasmissing / attmissingval), and returned for existing rows. relfilenode does not change. The exclusive lock is short.

DEFAULT 0 is that path.

DEFAULT now() is not a constant. It is a function call. That is the first reason a reviewer should pause. The second is volatility. The ALTER TABLE notes are specific:

A non-volatile default is stored in metadata. No rewrite.
A volatile default rewrites the table and its indexes. The documented example is clock_timestamp().

now() is an alias for transaction_timestamp(). It is STABLE: one value for the whole transaction. On PostgreSQL 11 and later, NOT NULL DEFAULT now() usually takes the catalog path. clock_timestamp(), timeofday(), and random() are VOLATILE. Those still rewrite.

Before PostgreSQL 11, a default of any kind rewrote the table.

So the one-liner is three different operations:

Default	Typical PostgreSQL 11+ behavior
Constant (`0`, `'x'`)	Catalog only
`STABLE` function (`now()`)	Catalog only. Every old row reads as the ALTER's timestamp.
`VOLATILE` function (`clock_timestamp()`)	Table + index rewrite

A rewrite changes relfilenode. It holds ACCESS EXCLUSIVE for the whole build. Disk can approach 2× table size while the new heap is written. Rewriting forms of ALTER TABLE are not MVCC-safe: a concurrent transaction whose snapshot started before the rewrite can see the table as empty.

That is not a style question. It is "will production rebuild this heap, and what timestamp do the old rows get."

Why tickets still sail through

Change tickets look at grants, backups, and the window. They do not always remember that DEFAULT now() and DEFAULT 0 are not the same class of work, or that now() and clock_timestamp() differ.

A style checker is happy. Indentation is fine. now() is just a function call. Passing a linter is not an assessment of lock time.

Online schema-change tools answer how to apply a change. They do not answer whether this shape should run.

Another miss, same family: a change split into several "small" statements. On MySQL, two ALTER TABLEs against the same table can be two rebuilds. Each line looks harmless. The ticket passes them one by one. Nobody reviews the pair.

Same class of legal-but-expensive DDL

I stop these on sight too. None of them are syntax errors. All of them are expensive when the table is large.

CHECK without NOT VALID. Adding a CHECK takes ACCESS EXCLUSIVE and scans the table. Safer: ADD CONSTRAINT … NOT VALID, then VALIDATE CONSTRAINT (that validation takes SHARE UPDATE EXCLUSIVE).

ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount >= 0);

CREATE INDEX without CONCURRENTLY. The default build blocks writes. On a live table that is the wrong default.

DELETE without WHERE. Everyone already treats that as a red line. That is why the ALTER comes first in this note. A bare DELETE is hard to sneak through a ticket. DEFAULT now() is easy to click through.

What I want written on the ticket

If the table is small and the window is real, a rewrite can be fine. Write that down. Do not assume it from the SQL text.

Check relfilenode before and after on a clone if you are unsure:

SELECT relfilenode FROM pg_class WHERE oid = 'public.users'::regclass;

If it changed, the statement rewrote the heap.

Check volatility if the default is a function:

SELECT proname, provolatile
FROM pg_proc
WHERE proname IN ('now', 'clock_timestamp', 'transaction_timestamp');
-- i = immutable, s = stable, v = volatile

Then answer the semantic question. created_at on every old row equal to the time of the ALTER is usually a lie. If that is acceptable and you are on PostgreSQL 11+ with now(), the one-liner is cheap. Still write the version and the function name on the ticket.

If old rows should not share one timestamp, do not use the one-liner:

ALTER TABLE users ADD COLUMN created_at timestamptz; — catalog only.
Backfill in batches. Not one UPDATE of the whole table.
ALTER TABLE users ALTER COLUMN created_at SET NOT NULL; — scan, no rewrite. Or add NOT NULL … NOT VALID and VALIDATE CONSTRAINT.
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now(); — future inserts only.

If someone wrote clock_timestamp(), or the cluster is older than 11, plan a rewrite. Do not discover it in production.

An offline check that flags the shape

I also run the SQL text through an offline linter before the window. DeltaScope v0.490.0 (rule catalog 371: blocker 72, warning 142, notice 157) parses the statement and applies policy. It does not execute the SQL. It does not run EXPLAIN ANALYZE. Default dialect is MySQL; PostgreSQL needs --dialect postgresql. Without a database connection it does not check that the table exists.

deltascope audit \
  --dialect postgresql \
  --sql "ALTER TABLE users ADD COLUMN created_at timestamptz NOT NULL DEFAULT now()" \
  --format json

That hits ddl.pg.alter.add_column.non_null_default.rewrite.warn. Default level is warning. Verdict is review, not reject. Extra facts on the finding: not_null, has_default, default_kind (here function_call). The linter is conservative: a function-call default plus NOT NULL is enough to flag. It cannot see server version or provolatile from the text alone.

I would not default that rule to a hard fail. A 2k-row table in a maintenance window can rewrite. A few-hundred-million-row table with the same SQL is an incident. The tool marks the shape. Window and rollback stay a human call.

The same pass will also flag CHECK without NOT VALID and CREATE INDEX without CONCURRENTLY. That is all I use it for here. A reader who never installs it still has the rewrite lesson above.

Project: https://github.com/Fanduzi/DeltaScope

Site: https://deltascope.pages.dev/

License: Apache 2.0

If you do connect it to a database, use --ask-password, --password-env, or --password-file. Not --password.

Bottom line

The statement parses. The ticket is clean. You still stop and ask: which PostgreSQL version, which function, does every old row get the ALTER time, and can this table absorb a rewrite.

If the answer is fuzzy, it does not pass on sight.

Also published in Chinese: Juejin, MODB

Automated SQL Migration Review with Real CLI Output (MySQL, PostgreSQL, TiDB)

Fan() — Sat, 09 May 2026 16:58:27 +0000

Auditing MySQL ALTER TABLE Risks with a CLI (Real Output Included)

Background

I'm the author of DeltaScope, an open-source offline SQL audit tool that supports MySQL, TiDB, and PostgreSQL. This post skips the marketing and shows real SQL inputs with real audit outputs — no fabrications.

Scenario 1: An Innocent-Looking Migration File

Consider a migration file migration.sql:

-- Add columns and index to users table
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;
ALTER TABLE users ADD INDEX idx_phone (phone);

-- Clean up temp data
DELETE FROM temp_data;

Audit it:

deltascope audit --file migration.sql

Output:

Verdict: reject

- Statements: 4
- Blockers: 1
- Warnings: 1
- Notices: 0

## Statement 1
- SQL: ALTER TABLE users ADD COLUMN phone VARCHAR(20)
No findings.

## Statement 2
- SQL: ALTER TABLE users ADD COLUMN age INT DEFAULT 0
No findings.

## Statement 3
- SQL: ALTER TABLE users ADD INDEX idx_phone (phone)
No findings.

## Statement 4
- SQL: DELETE FROM temp_data

### Findings
- [blocker] dml.where.require: UPDATE and DELETE statements must include a WHERE clause
  Suggestion: add a WHERE clause that narrows the affected rows

### Impact
- estimated_ratio: 1.0000
- risk_level: high
- confidence: high
- source: shape
- reason_code: missing_where

## Global Findings
- [warning] ddl.alter.merge.mysql.require: multiple ALTER TABLE statements
  target "users" under mysql mode
  alter_count: 3, dialect: mysql
  Suggestion: merge repeated alter statements on the same table into
  a single ALTER TABLE

Two issues:

DELETE FROM temp_data has no WHERE clause — offline mode estimates ratio = 1.0 (full table), flagged as high risk
Three ALTER statements target the same table users — MySQL recommends merging them

After fixing:

ALTER TABLE users
  ADD COLUMN phone VARCHAR(20),
  ADD COLUMN age INT DEFAULT 0,
  ADD INDEX idx_phone (phone);

DELETE FROM temp_data WHERE created_at < '2026-01-01';

Re-audit:

Verdict: pass
- Statements: 2 | Blockers: 0 | Warnings: 0 | Notices: 0

Scenario 2: Changing Column Type + NULL Constraint

ALTER TABLE orders MODIFY COLUMN amount VARCHAR(100) NOT NULL;

deltascope audit --sql "ALTER TABLE orders MODIFY COLUMN amount VARCHAR(100) NOT NULL"

Verdict: reject

- [blocker] ddl.alter.modify_column.explicit_nullability_change.forbid:
  ALTER TABLE modify column explicitly changes nullability for "amount",
  which this policy forbids
  Suggestion: keep nullability unchanged for "amount" or relax the policy
  intentionally after review

This statement changes the column type from INT to VARCHAR and also changes the NULL constraint. Type changes may trigger a full table COPY in InnoDB (depending on the direction), and NULL constraint changes can silently break application logic that depends on the constraint.

The rule ID is ddl.alter.modify_column.explicit_nullability_change.forbid, default level blocker. Teams that genuinely need this change can adjust the level or add an approval flow in the config.

Scenario 3: Dropping NOT NULL

ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NULL;

deltascope audit --sql "ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NULL"

Verdict: reject

- [blocker] ddl.alter.modify_column.explicit_nullability_change.forbid:
  ALTER TABLE modify column explicitly changes nullability for "email",
  which this policy forbids
  Suggestion: keep nullability unchanged for "email" or relax the policy
  intentionally after review

Same rule as Scenario 2. After dropping NOT NULL, application code like if user.Email != "" silently breaks — NULL is not an empty string. This won't throw errors at deploy time, but behavior changes quietly, making it expensive to debug later.

Scenario 4: Dropping Primary Key

ALTER TABLE users DROP PRIMARY KEY;

deltascope audit --sql "ALTER TABLE users DROP PRIMARY KEY"

Verdict: reject

- [blocker] ddl.alter.drop_primary_key.forbid:
  ALTER TABLE drop primary key is forbidden for "primary"
  Suggestion: avoid drop primary key in this change or relax the policy intentionally

InnoDB uses the primary key as the clustered index. Dropping it forces a full table rebuild. Default policy rejects this outright. If you need to change the primary key scheme, the correct approach is to ADD the new primary key column first, then DROP the old one in a separate step.

Scenario 5: Sloppy CREATE TABLE

CREATE TABLE t1 (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100),
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This SQL executes fine, but the audit finds 8 issues:

deltascope audit --sql "CREATE TABLE t1 (id bigint unsigned NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"

Verdict: review
- Statements: 1 | Blockers: 0 | Warnings: 8

- [warning] ddl.table.comment.require: table comment is required
- [warning] ddl.table.audit_columns.require: should include a created-time
  audit column with DEFAULT CURRENT_TIMESTAMP
- [warning] ddl.table.audit_columns.require: should include an updated-time
  audit column with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- [warning] ddl.column.comment.require: column "id" must include a comment
- [warning] ddl.column.comment.require: column "name" must include a comment
- [warning] ddl.column.default.require: column "id" should define a default value
- [warning] ddl.column.default.require: column "name" should define a default value
- [warning] ddl.column.not_null.require: column "name" should be declared NOT NULL

Missing table comment, missing audit columns (created_at / updated_at), missing column comments, missing default values, name allows NULL. A "working" CREATE TABLE has 8 compliance issues — every column added later compounds the debt.

Corrected version:

CREATE TABLE t1 (
  id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  name varchar(100) NOT NULL DEFAULT '' COMMENT 'display name',
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created at',
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated at',
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='demo table';

Scenario 6: Three PostgreSQL Migration Pitfalls

PostgreSQL has a different DDL locking model than MySQL. Some risks are PG-specific. Take this migration file:

ALTER TABLE orders ALTER COLUMN amount TYPE TEXT;
ALTER TABLE orders ALTER COLUMN amount DROP NOT NULL;
CREATE INDEX idx_status ON orders (status);

deltascope audit --dialect postgresql --file pg_migration.sql

Output:

Verdict: reject
- Statements: 3 | Blockers: 1 | Warnings: 4

## Statement 1: ALTER TABLE orders ALTER COLUMN amount TYPE TEXT
- [warning] ddl.alter.set_data_type.forbid: ALTER TABLE set data type is
  forbidden for "amount"
- [warning] ddl.pg.alter.set_data_type.rewrite.warn: ALTER COLUMN "amount"
  SET DATA TYPE carries table rewrite risk on PostgreSQL
  Suggestion: Assess table size and lock impact first. For large tables,
  use a phased migration: add a shadow column with the new type, backfill
  in batches, switch application reads, then drop the old column.

## Statement 2: ALTER TABLE orders ALTER COLUMN amount DROP NOT NULL
- [blocker] ddl.alter.drop_not_null.explicit_nullability_change.forbid:
  ALTER TABLE drop not null explicitly changes nullability for "amount"
- [warning] ddl.alter.drop_not_null.forbid: ALTER TABLE drop not null is
  forbidden for "amount"

## Statement 3: CREATE INDEX idx_status ON orders (status)
- [warning] ddl.pg.create_index.concurrently.require: CREATE INDEX "idx_status"
  without CONCURRENTLY can block writes on PostgreSQL
  Suggestion: Use CREATE INDEX CONCURRENTLY to build the index without
  blocking writes. Note that CONCURRENTLY cannot run inside a transaction;
  run it as a standalone migration step.

Three issues:

Statement 1: Type change triggers table rewrite

SET DATA TYPE in PostgreSQL acquires an ACCESS EXCLUSIVE lock (blocks all reads and writes) and rewrites the entire table. DeltaScope suggests a phased migration: add a shadow column with the new type, backfill in batches, switch application reads, then drop the old column. This is a PG-only rule (ddl.pg.alter.set_data_type.rewrite.warn) — it won't fire under the MySQL dialect.

Statement 2: Dropping NOT NULL

Same risk as MySQL — application code depends on the NOT NULL constraint. But PG syntax uses ALTER COLUMN ... DROP NOT NULL instead of MySQL's MODIFY COLUMN. DeltaScope recognizes PG syntax and fires the corresponding rule.

Statement 3: CREATE INDEX without CONCURRENTLY

This is a PG-specific pitfall. A regular CREATE INDEX holds a write-blocking lock for the entire index build. CREATE INDEX CONCURRENTLY builds the index without blocking writes, but it can't run inside a transaction. DeltaScope checks for this and reminds you that CONCURRENTLY must be a standalone migration step.

One more PG-specific scenario — adding a CHECK constraint:

ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0);

deltascope audit --dialect postgresql --sql "ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0)"

Verdict: review

- [warning] ddl.pg.alter.add_check.not_valid.require: CHECK constraint
  "chk_amount" without NOT VALID validates all existing rows immediately
  on PostgreSQL
  Suggestion: Use a two-step approach:
  1) ADD CONSTRAINT ... NOT VALID to register the constraint without
     scanning existing rows.
  2) VALIDATE CONSTRAINT in a separate step — it holds only a SHARE
     UPDATE EXCLUSIVE lock.

By default, PG scans the entire table to validate the constraint at ADD CONSTRAINT time, holding an ACCESS EXCLUSIVE lock on large tables. The correct approach: first register the constraint as NOT VALID (no scan), then run VALIDATE CONSTRAINT separately (only holds SHARE UPDATE EXCLUSIVE lock, doesn't block reads or writes).

These rules (ddl.pg.alter.set_data_type.rewrite.warn, ddl.pg.create_index.concurrently.require, ddl.pg.alter.add_check.not_valid.require) are exclusive to the PostgreSQL dialect. Switch with --dialect postgresql — they won't fire under MySQL or TiDB.

Scenario 7: Dialect Differences — MySQL vs TiDB

ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

MySQL default audit:

$ deltascope audit --sql "ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL"

Verdict: pass

TiDB dialect audit:

$ deltascope audit --dialect tidb --sql "ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL"

Verdict: pass

Both pass for a single statement. But if a migration file has three ALTER statements on the same table, the MySQL dialect fires a warning (suggesting merge), while the TiDB dialect does not (because TiDB DDL is online — no table locking, no need to merge). This is what --dialect is for — different engines have different best practices, and audit rules should follow the engine.

Three dialects are currently supported: mysql (default), tidb, postgresql.

CI Integration

All of the above checks belong in CI, not in manual review. GitHub Actions config:

name: SQL Audit
on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  audit:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install DeltaScope
        run: curl -fsSL https://raw.githubusercontent.com/Fanduzi/DeltaScope/main/install.sh | sh
      - name: Audit
        run: deltascope audit --file ./migrations/ --format github-actions --fail-on warning

Three CI output formats:

Format	Flag	Use case
GitHub Actions	`--format github-actions`	PR annotations
GitLab Code Quality	`--format gitlab-codequality`	Code Quality artifact
SARIF	`--format sarif`	GitHub Code Scanning

For more accurate audits using live table structure (e.g., checking for redundant indexes), use metadata-aware mode:

deltascope audit \
  --sql "ALTER TABLE orders ADD INDEX idx_status (status)" \
  --host 127.0.0.1 --port 3306 --user root --ask-password --schema app

This connects to the database to read table statistics but never executes any DDL or DML — read-only metadata access.

JSON Output

For CI scripts that need machine-readable results:

deltascope audit --sql "ALTER TABLE orders MODIFY COLUMN amount VARCHAR(100) NOT NULL" --format json

{
  "verdict": "reject",
  "summary": {
    "statements": 1,
    "blockers": 1,
    "warnings": 0,
    "notices": 0
  },
  "statements": [
    {
      "index": 0,
      "kind": "ddl",
      "raw_sql": "ALTER TABLE orders MODIFY COLUMN amount VARCHAR(100) NOT NULL",
      "findings": [
        {
          "rule_id": "ddl.alter.modify_column.explicit_nullability_change.forbid",
          "level": "blocker",
          "message": "ALTER TABLE modify column explicitly changes nullability for \"amount\"",
          "suggestion": "keep nullability unchanged for \"amount\" or relax the policy intentionally after review",
          "metadata": {
            "action": "modify_column",
            "change_kind": "explicit_nullability_change",
            "column_name": "amount",
            "table": "orders"
          }
        }
      ]
    }
  ],
  "context": {
    "mode": "offline",
    "dialect": "mysql",
    "dialect_source": "default"
  }
}

Script logic: verdict == "reject" → block, "review" → require human acknowledgment, "pass" → allow.

Rule Configuration

151 built-in rules (run deltascope rules to list all), configurable via YAML:

# deltascope.yaml
rules:
  # Require DBA sign-off for DROP COLUMN
  ddl.alter.drop_column.forbid:
    enabled: true
    level: blocker

  # Enforce idx_ prefix on secondary indexes
  ddl.alter.add_index.secondary.prefix.require:
    enabled: true
    level: warning
    params:
      prefix: idx_

  # Teams that don't need audit columns can disable
  ddl.table.audit_columns.require:
    enabled: false

Commit the config to the repo — CI loads it automatically.

Links:

Website: https://deltascope.pages.dev
GitHub: https://github.com/Fanduzi/DeltaScope
Rule reference: https://github.com/Fanduzi/DeltaScope/blob/main/configs/deltascope.example.yaml
CI integration docs: https://github.com/Fanduzi/DeltaScope/tree/main/docs/recipe

Install:

# macOS
brew tap Fanduzi/deltascope && brew install --cask deltascope

# Linux
curl -fsSL https://raw.githubusercontent.com/Fanduzi/DeltaScope/main/install.sh | sh