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.
How the diff is made
Section titled “How the diff is made”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 CONCURRENTLYorDROP 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_BUILDorDELETES_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.
The lock-risk lint
Section titled “The lock-risk lint”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.
| Rule | What it catches | Safer way |
|---|---|---|
alter-type-rewrite | ALTER 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 reported | Add a column of the new type, backfill it in batches, switch over |
index-not-concurrent | CREATE INDEX or REINDEX without CONCURRENTLY on an existing table: writes wait for the whole build | Build the index with CREATE INDEX CONCURRENTLY from a SQL client first; the plan then no longer contains it |
drop-index-not-concurrent | DROP INDEX without CONCURRENTLY | DROP INDEX CONCURRENTLY from a SQL client |
not-null-without-default | ADD COLUMN … NOT NULL without a default on a table that has rows: the statement fails | Add a DEFAULT (PostgreSQL 11+ stores a constant default without a rewrite), or add the column nullable and backfill |
add-column-rewrite | A new column with a volatile default (gen_random_uuid(), random(), clock_timestamp(), a serial or identity) or a stored generated column: a full rewrite | Add the column plain, backfill in batches, then set the default |
set-not-null-scan | SET NOT NULL reads every row under an ACCESS EXCLUSIVE lock | Validate a CHECK (col IS NOT NULL) constraint first, in its own transaction |
constraint-validation-scan | A CHECK or foreign key added without NOT VALID, or validated in the transaction that added it | Add it NOT VALID, then VALIDATE CONSTRAINT separately |
constraint-index-build | ADD PRIMARY KEY, UNIQUE or EXCLUDE that builds its index under lock | Build a unique index CONCURRENTLY, then ADD CONSTRAINT … USING INDEX |
table-rewrite | VACUUM FULL, CLUSTER, SET TABLESPACE, SET LOGGED/UNLOGGED, SET ACCESS METHOD | A maintenance window, or pg_repack |
drops-data | DROP TABLE, DROP COLUMN, TRUNCATE, DROP SCHEMA … CASCADE | Stop 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.
Apply to the parent
Section titled “Apply to the parent”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):
- a pre-change backup of the parent is taken with the regular backup machinery (its id is
pre_change_backup_idin the answer and on the operation); - when the backup has completed, the change is planned again; it runs only if it still has the hash you reviewed
(
applying); - the operation ends
completed, orfailedwith 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:revealon 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_findingsotherwise); - the hash you reviewed (409
preview-stalewhen 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.
Approvals
Section titled “Approvals”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.
On the pull request
Section titled “On the pull request”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.
With the API
Section titled “With the API”| Call | Needs |
|---|---|
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.