Skip to content

Database tools

Safe schema changes

Tested with:PostgreSQL 16 and 18 · Databasezy API v1 · ZB-617

Make a schema change on a branch first, then let Databasezy work out what it means for the parent. The Schema changes page of a branch shows the DDL that brings the parent to the branch’s schema, what each statement locks or rewrites, and how risky that is on the parent’s tables at their current size. When it looks right, apply it to the parent in one transaction. If your team requires it, the apply waits for a team lead’s approval.

Schema changes work on PostgreSQL and TimescaleDB branches.

The comparison runs inside your database’s cell, next to both databases. The platform reads the catalogs of the branch and of its parent (in read-only transactions, as each database’s own user) and plans the change with pg-schema-diff, the same planner zb db diff uses. No row of your data is read and the control plane never connects to either database.

  • Compared schemas. Every schema of either database except PostgreSQL’s own, schemas that belong to an extension, and the platform’s schemas (auth, storage, realtime, cron, pgmq, vault, extensions, graphql, zb_*, TimescaleDB’s internals and the others the studio also protects). A schema that exists only on the branch is created.
  • Order. Statements are listed in the order they run, dependencies first.
  • One transaction. The plan never uses CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY: it is applied as one transaction, so it either applies completely or not at all.
  • Hazards. Each statement carries the planner’s own warnings, for example ACQUIRES_ACCESS_EXCLUSIVE_LOCK, INDEX_BUILD or DELETES_DATA.
  • Hash. The statements have a SHA-256 hash. Applying requires the hash you reviewed: if the branch or the parent changed in the meantime, the apply is refused (409 preview-stale) and you review the new plan.

A plan holds at most 2,000 statements or 4 MiB of DDL (422 schema-diff-too-large; compare fewer schemas). If a database is asleep after idling, the request wakes it and answers 409 instance-waking; retry after a few seconds.

Every statement is checked for the operations that lock or rewrite a table, and each finding is graded by the size of the parent’s table: its row estimate (pg_class.reltuples, what the query planner uses) and its size on disk with indexes. These estimates and sizes are the only things read about your tables.

RuleWhat it catchesSafer way
alter-type-rewriteALTER COLUMN … TYPE that rewrites the table and its indexes under an ACCESS EXCLUSIVE lock. Widening a varchar or numeric limit, varchar to text and similar binary-compatible changes are not reportedAdd a column of the new type, backfill it in batches, switch over
index-not-concurrentCREATE INDEX or REINDEX without CONCURRENTLY on an existing table: writes wait for the whole buildBuild the index with CREATE INDEX CONCURRENTLY from a SQL client first; the plan then no longer contains it
drop-index-not-concurrentDROP INDEX without CONCURRENTLYDROP INDEX CONCURRENTLY from a SQL client
not-null-without-defaultADD COLUMN … NOT NULL without a default on a table that has rows: the statement failsAdd a DEFAULT (PostgreSQL 11+ stores a constant default without a rewrite), or add the column nullable and backfill
add-column-rewriteA new column with a volatile default (gen_random_uuid(), random(), clock_timestamp(), a serial or identity) or a stored generated column: a full rewriteAdd the column plain, backfill in batches, then set the default
set-not-null-scanSET NOT NULL reads every row under an ACCESS EXCLUSIVE lockValidate a CHECK (col IS NOT NULL) constraint first, in its own transaction
constraint-validation-scanA CHECK or foreign key added without NOT VALID, or validated in the transaction that added itAdd it NOT VALID, then VALIDATE CONSTRAINT separately
constraint-index-buildADD PRIMARY KEY, UNIQUE or EXCLUDE that builds its index under lockBuild a unique index CONCURRENTLY, then ADD CONSTRAINT … USING INDEX
table-rewriteVACUUM FULL, CLUSTER, SET TABLESPACE, SET LOGGED/UNLOGGED, SET ACCESS METHODA maintenance window, or pg_repack
drops-dataDROP TABLE, DROP COLUMN, TRUNCATE, DROP SCHEMA … CASCADEStop using the object first and make sure a recent backup exists

Severity. A table with at least 1,000,000 estimated rows or 1 GiB is large and its findings are errors; from 100,000 rows or 128 MiB it is medium (warnings); smaller tables give info. A statement that fails or deletes data on a table with rows is always an error. A table whose size cannot be read is a warning. Tables the change creates are empty, so nothing that touches them is reported. Because the apply is one transaction, validating a constraint that the same transaction added still blocks writes and is reported too.

Applying runs the reviewed statements in the parent in one transaction, as the parent’s database user, with a 5-second lock timeout: a statement that would queue behind a long-running query fails at once instead of making the parent’s traffic wait behind it, and nothing is changed. Function body checks are off while the plan creates functions, as with pg_dump.

A pre-change backup comes first. One transaction protects the parent from a change that fails, but not from one that succeeds and drops a column or narrows a type. So on every paid plan (the plans that also get in-place major upgrades) an apply starts a schema apply operation instead of running at once (202 backing_up):

  1. a pre-change backup of the parent is taken with the regular backup machinery (its id is pre_change_backup_id in the answer and on the operation);
  2. when the backup has completed, the change is planned again; it runs only if it still has the hash you reviewed (applying);
  3. the operation ends completed, or failed with the reason. If the backup fails or does not complete within 6 hours, nothing is applied.

The Schema changes page follows the operation; GET …/branches/{branch_id} lists it under operations (kind schema_apply). Only one change of a parent runs at a time: a second apply, or a promote into the same parent, waits (409). If the platform loses track of an apply in the middle, it plans again after five minutes: an empty plan means the change already ran, the same plan runs it once.

Free has no pre-change backup (as for major upgrades). There, a change that can delete or rewrite data (dropped tables or columns, column type changes) is refused (422 on plan); a change that keeps every row (new tables, columns, indexes, constraints) applies at once (200) without a backup.

To apply you need:

  • credentials:reveal on the parent’s team (the statements run with the database’s own credentials, as in a writable SQL console) and a second factor completed in the last 15 minutes;
  • to type the parent’s name;
  • to acknowledge the lint’s error findings, if there are any (422 on acknowledge_findings otherwise);
  • the hash you reviewed (409 preview-stale when the plan changed).

The apply goes through the cell’s console proxy and is bounded by its 30-second statement timeout. Build large indexes or rewrite large tables from your own SQL client first, the way the lint suggests; the next diff then no longer contains those statements.

An anonymized branch is never applied: its schema carries its masking. Make the change on a regular branch. Staff support sessions cannot apply schema changes.

Every apply is written to the audit log (instance.schema_changed, with the statements’ hash and count, the pre_change_backup_id and, after an approval, the approval and the requester, never the statements’ values) and is the security event data.branch_schema_applied (with the backup’s id; high severity when the change deletes data; a failed backup or a refused apply is the same event with outcome failure). Changes to grants, policies, roles or row-level security are also authz.database_access_changed.

A team can require a lead’s approval before schema changes reach a parent. In the team’s approval policy set schema_changes: true; require_for_all: true, or the parent’s placement listed in placements, require it as well. Then Apply becomes Request approval: the request is filed as a branch.schema_apply approval with the reviewed hash, and nothing runs. A team lead or an organization admin approves it in Approvals with a second factor completed in the last 15 minutes; the requester cannot approve their own request. Approving plans the change again and, only if it still has the requested hash, starts the schema apply operation: the pre-change backup is taken at approval time and the statements run once it completed. If the branch or the parent changed, the approval is refused and the request can be denied and filed again. Pending requests expire after 7 days.

When a repository is linked to a database through the organization’s GitHub App (portal, Integrations), each pull request gets its own branch database. Databasezy then posts one comment on the pull request with the DDL and the lint findings, and edits that same comment when the branch’s schema changes (for an hour after each push, every few minutes). The comment links to the branch’s Schema changes page, where people with access review and apply it.

  • The comment never contains credentials or data: statements go through a secret scanner (tokens, keys, passwords in URLs or PASSWORD '…' are replaced), and table statistics appear only as findings.
  • In a public repository the comment only gives the counts and the link, not the DDL or table names.
  • The GitHub token used for the comment is limited to that one repository and to pull requests.
  • Pull requests from forks never get a branch database or a comment, and a merged pull request applies nothing: applying to the parent is always a person’s decision in Databasezy.
CallNeeds
GET /v1/orgs/{org}/instances/{parent}/branches/{branch}/schema-diff?schemas=instances:read; both instances running
POST /v1/orgs/{org}/instances/{parent}/branches/{branch}/schema-diff/apply {hash, confirm_name, acknowledge_findings?, schemas?}credentials:reveal on the parent’s team and a recent second factor; 202 backing_up with the schema_apply operation and pre_change_backup_id, 202 approval_required with the approval, or (Free, a change that keeps every row) 200 applied

The diff answers statements (each with hazards), findings (rule, severity, statement, table, message, hint, rows_estimate, bytes), counts, destructive, hash, the thresholds used and apply (available, reason, approval_required, approval_reasons). Every lookup is scoped to the organization in the URL: a branch of another organization, or of another parent, is a 404.