PostgreSQL Schema Version Control: Methods and Tools

Staging and production disagree about a column, and the only record of how they got that way is a folder of SQL scripts plus somebody's memory of which ones ran where. Putting the schema itself under Git ends that. DbSchema keeps the design in a .dbs XML model file you commit next to the application code, compares any two versions of it, and generates the DDL that turns one version into the other.

What database version control is, and why PostgreSQL needs it

Version control for a database means one artifact in the repository that describes the structure, plus a history that says when each table, column and constraint arrived. Two things follow from having it. Drift between environments becomes visible, because the difference between an environment and the committed structure is a comparison anyone can run. And a schema change reaches production through a pull request, reviewed by the same people who review the code that depends on it.

PostgreSQL helps with the deployment half. All statements after a BEGIN command are executed in a single transaction until an explicit COMMIT or ROLLBACK is given[1], and the statements that create and alter tables are among them, so a deployment that fails halfway leaves the schema as it was. What PostgreSQL does not keep is the history: the catalog holds the current structure and nothing about the structures before it.

DbSchema fills that half with the state-based approach. The .dbs model file describes the structure you want, Git holds every version of that file, and the script that deploys a change is generated from the difference between two of them instead of being written by hand. Saving the model file and comparing two schemas are Pro features.

Methods of database version control

Two approaches to that history are in use, and they differ in what sits in the repository. State-based version control keeps one file that describes the structure you want, and a comparison against a live database produces the statements that close the gap. Migration-based version control keeps an ordered set of SQL scripts, one per change, each running once and in sequence, such as 001_init.sql followed by 002_add_index.sql. The choice decides what a reviewer reads in a pull request, a structural diff or a script, and schema migration tools divide along the same line.

DbSchema is state-based and hands you the script at the end, so a change is reviewed twice: once as a structural diff in the model file, and once as the statements that will run.

PostgreSQL schema debt and the locks behind it

A change that is harmless on a table of a thousand rows can stop an application on a table of a hundred million, and the difference is the lock. Many forms of ALTER INDEX and ALTER TABLE acquire an ACCESS EXCLUSIVE lock[2], which conflicts with locks of all modes[2], so every SELECT, INSERT, UPDATE and DELETE against that table queues until the statement is done. After one deployment like that, the temptation is to stop refactoring: you add columns and never remove or narrow one, and that is the schema debt somebody inherits later.

The examples below run on PostgreSQL 18 against these two tables:

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  email       varchar(255) NOT NULL
);

CREATE TABLE orders (
  order_id    integer,
  customer_id integer NOT NULL,
  placed_on   date NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

Ask for the lock with a deadline. Setting lock_timeout aborts any statement that waits longer than the specified amount of time while attempting to acquire a lock[3], so a deployment that cannot get the lock gives up in seconds instead of collecting every query behind it:

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN status varchar(20);
COMMIT;

When the lock does not arrive within three seconds, the statement is cancelled and the transaction rolls back:

ERROR:  canceling statement due to lock timeout

Give a new column a constant default rather than a volatile one. The two statements below differ only in that default:

ALTER TABLE orders ADD COLUMN imported_at timestamptz DEFAULT '2026-01-01 00:00:00+00';

ALTER TABLE orders ADD COLUMN imported_at timestamptz DEFAULT clock_timestamp();

The first adds a column with a constant default value, which does not require each row of the table to be updated when the ALTER TABLE statement is executed[4]. The second uses a volatile DEFAULT, clock_timestamp(), which causes the entire table and its indexes to be rewritten[5].

Add a foreign key in two steps. ALTER TABLE normally scans the table to verify that all existing rows satisfy the new constraint, the NOT VALID option skips that scan, and VALIDATE CONSTRAINT checks the older rows afterwards[5]:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customers;

The index behind a new unique constraint can go on while writes continue. With CONCURRENTLY, PostgreSQL builds the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table, whereas a standard index build locks out writes until it is done[6]:

CREATE UNIQUE INDEX CONCURRENTLY idx_customers_email ON customers (email);

Versioning PostgreSQL schemas in Git via XML

Between sessions the design lives in the .dbs design model file, which is XML, and that is what makes a schema change reviewable. The orders table above is a few lines of it:

<table name="orders" row_count="0" spec="">
  <column name="order_id" type="integer" length="32" mandatory="y" />
  <column name="customer_id" type="integer" length="32" mandatory="y" />
  <column name="placed_on" type="date" length="0" mandatory="y" />
  <index name="pk_orders" unique="PRIMARY_KEY">
    <column name="order_id" />
  </index>
</table>

Add the status column in the DbSchema diagram, save the model, and the commit carries one added line:

   <column name="placed_on" type="date" length="0" mandatory="y" />
+  <column name="status" type="varchar" length="20" />
   <index name="pk_orders" unique="PRIMARY_KEY">

Nothing has reached PostgreSQL at this point. Editing the diagram changes the model in memory, saving writes the .dbs file, and committing puts that file in Git. The database is still the one you started the day with. Because the file holds structure and not rows, no customer data enters the repository either.

The loop does not leave DbSchema. Open the Model menu and choose Git — Collaborative Design. The Git dialog clones the repository, then stages, commits, pushes and pulls the model file. Creating a branch and stashing work you are not ready to commit happen in the same dialog. That dialog and saving the model file belong to the Pro edition. After a pull, Compare with Current opens the Synchronization Dialog on what a colleague pushed, so you read their change as structure rather than as XML. What a database client puts in the repository differs from one client to the next, which is the subject of the comparison of database design tools with Git integration.

The migration script between two model versions

A generated script needs two states to compare. One of them is the model in front of you. The other is either the database you are deploying to, or an earlier version of the model itself.

Against a database, open Schema → Compare Model with Database in DbSchema. The diff view lists the differences, table by table and column by column. For each difference you choose to update the model, push the change to the database, or skip it. Schema → Synchronize Model with Database then generates the SQL for what you chose. The Sync Dialog is where you edit those statements before clicking Execute, which is the moment the database changes.

Against an earlier version, open the two .dbs files at the same time and synchronize between them, which is how a release branch is compared with main without a database in the middle.

The example has collected three differences by now: the status column added in the model, the foreign key from orders to customers, and the unique index on the email column. The script that closes them is three statements:

ALTER TABLE orders ADD COLUMN status varchar(20);

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

CREATE UNIQUE INDEX idx_customers_email ON customers (email);

The Sync Dialog is also where the safeguards from the previous section go in. Edit the ADD CONSTRAINT statement so that it ends in NOT VALID, take the index out of the script and build it with CONCURRENTLY on its own, and only then click Execute. If a statement has to come out afterwards, rolling back a database schema change covers what PostgreSQL undoes inside a transaction and what it does not.

Strategies for zero-downtime deployments

A change that removes a column, or narrows one, breaks the running application the moment it lands, because the deployed code is still writing the old shape. Expand and contract splits that change into steps which are each backwards compatible:

1. Add the new column, table or nullable constraint, and leave the old structure in place.
2. Deploy application code that writes to both the old and the new structure.
3. Backfill the existing rows in batches.
4. Drop the old structure once no running instance reads it.

Each of those steps is a version of the model and a commit of its own. Working offline in the design model is how all four get planned before the first one runs, since a change made while disconnected goes to the model file and nowhere else.

One step of that plan can deploy as a single unit, because DDL runs inside the surrounding transaction[1]. Either both statements below land or neither does:

BEGIN;
ALTER TABLE orders ADD COLUMN status varchar(20);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (customer_id) NOT VALID;
COMMIT;

The index build is the exception: a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot[6]. It runs on its own, after the transaction has committed.

Best practices for versioning a PostgreSQL schema

The whole loop in DbSchema, from a database nobody versioned to a schema that ships through pull requests:

1. Connect to your PostgreSQL instance and reverse-engineer it, so DbSchema reads the catalog into the design model and draws the diagram.
2. Save the model as a .dbs file inside the Git repository that holds the application code, and commit it as the baseline before you change anything.
3. Make the change in the diagram, the same place you would design a new PostgreSQL schema, where it goes into the model and not into the database.
4. Commit the changed model file, so a reviewer reads the structural diff in the pull request.
5. Open Schema → Synchronize Model with Database against staging, read the generated statements, and execute them. Run the same pass against production once the change has landed on staging.
6. Export the schema as HTML5 documentation from Diagram → Export HTML5 or PDF Documentation, for the people who never open DbSchema.

Download DbSchema at https://dbschema.com/download.html, reverse-engineer the database you deploy to, and commit its model file before the next change goes anywhere near it. Connecting, reverse-engineering and the diagrams are the free Community Edition. The four steps that make the schema versionable, saving the .dbs file, the Git dialog, schema synchronization and the HTML5 documentation, are Pro, which the same download runs for 15 days.

Frequently asked questions

What are the three types of version control?

Local systems keep the revisions in one working copy on a single machine. Centralized systems keep the authoritative history on one shared server, as Subversion and CVS do, and distributed systems give every clone the full history, as Git and Mercurial do. Schema versioning rides on the distributed type, because the schema artifact belongs in the same repository as the application code.

What is a version example?

Version 1.0 of the model has orders(order_id, customer_id, placed_on). You add a status column in DbSchema, save the model and commit it, and the file in Git is version 1.1. Comparing the two produces ALTER TABLE orders ADD COLUMN status varchar(20), which is the deployable artifact for that version.

What is the most common version control system?

Git, which is why DbSchema speaks it directly: the Git dialog clones, stages, commits, pushes and pulls the .dbs model file, so the schema history sits in the same repository and the same pull request as the code that queries it.

Can I version a PostgreSQL schema without paying for a tool?

The free Community Edition connects to PostgreSQL, reverse-engineers the schema and draws the interactive diagrams, which shows you what is in the database today. Saving that model to a .dbs file, comparing two versions of it and generating the migration script are Pro features, and those are the steps that turn the schema into something Git can hold. The installer runs Pro as a 15-day trial, so the whole loop can be tried on your own database first.

Does this workflow apply to databases other than PostgreSQL?

The .dbs model and the comparison workflow are not PostgreSQL-specific, and DbSchema connects over JDBC to 70+ SQL and NoSQL databases, each with the same commit, compare and deploy loop. This article uses PostgreSQL because its DDL runs inside the surrounding transaction, so a failed migration leaves nothing half-applied, which engines without transactional DDL cannot offer.

Sources

1. postgresql.org
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org
6. postgresql.org