[ForHackernews]

Postgres has transactional DDL: you can be applying migrations in one transaction while serving live traffic from the old schema in another. By tying the schema changes directly to the application deployment it becomes harder to apply a big migration without downtime. You can't apply the migration and then cut over traffic to new app instances once the migration is complete.

[prirun]

SQLite has transactional DDL: you can start a transaction, do a bunch of create tables, copy data from old tables into the new tables. If an error occurs during this and a rollback occurs, everything will be just like it was before the transaction started. If a commit occurs, the migration succeeds and everyone sees the new schema on their next transaction.

In rollback mode, only the migration thread can be active because it's a write transaction, but I'm guessing in WAL mode, readers can continue to read during the migration, as with any other write transaction. I don't use WAL mode much because for my application (HashBackup), I don't need db concurrency.