November 3, 20267 min read

Ptah or Flyway OSS for Versioned PostgreSQL Migrations?

Run the same PostgreSQL change with Flyway OSS and Ptah, then check migration history and a live schema change.

Nathan BrooksTechnical Writer

Ptah helps you plan, review, and apply database migrations. Try in your browser

An application already deploys reviewed SQL migrations. For its next PostgreSQL change, the team must decide whether migration files alone remain the source of truth or a desired schema should also guide generation and checks against the live database. Both Flyway OSS and Ptah apply handwritten versioned SQL. Ptah can also derive a reviewed migration from that desired schema.

Choose Ptah when the team wants that schema model, generated SQL, shadow verification, and live comparison in one workflow. Keep a functioning Flyway deployment when changing its automation and recorded history costs more than the added workflow is worth. Ptah’s read-only schema comparison can still serve a Flyway-managed database with a suitable scope and release-matched schema file. Ptah can also run handwritten SQL without a shadow database.

The Flyway command tiers matter to this comparison. The measured edition is OSS, while paid editions provide some of the same operations through different workflows. Ptah’s versioned migration workflow supports both handwritten and generated SQL.

Operation	Flyway 13.12.0	Ptah 0.17.0
Apply reviewed versioned SQL	OSS: `migrate`, with history and `validate`	`migrations up`, with revision history and `ptah.sum` integrity
Generate SQL from schema differences	Enterprise: `diff` and `generate`	`migrations generate`, using a disposable shadow database
Preview pending execution	Teams: `check -dryrun`	`migrations up --dry-run`
Check live schema drift	Enterprise: `check -drift`	`schema compare --exit-code`
Run a down migration	Teams: `undo`	`migrations down`, using reviewed down SQL

Reviewing the SQL file before deployment is possible in either workflow. It is separate from a dedicated dry run. The commands in the table have different inputs and guarantees; their edition placement does not claim feature parity.

We ran one small change with handwritten Flyway SQL and Ptah-generated SQL. The exercise checks the resulting table and shows the distinct integrity and drift checks. It does not measure deployment performance.

The run used Flyway OSS 13.12.0, Ptah 0.17.0, and PostgreSQL 18.6. The runnable inputs and full output are beside this post.

Start from the same table

Both disposable databases began with this first migration. We reused the table and four sample rows from our Flyway handoff guide:

CREATE TABLE public.products (
    id bigint PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    stock integer NOT NULL DEFAULT 0,
    archived boolean NOT NULL DEFAULT false
);

Flyway applied V1 with -target=1. Ptah applied the same SQL from its own version 1 file with --limit 1. We then inserted the same four product rows into each database. The commands and seed file are in the example directory.

Flyway kept flyway_schema_history in a separate flyway schema for this experiment. The application table stayed in public. This is a configuration choice made before the first migration, not a step to perform on an existing database. Flyway’s default-schema setting determines where the history table lives.

Write the change with Flyway

The Flyway path starts with a reviewed V2 SQL file:

ALTER TABLE public.products
    ADD COLUMN reorder_level integer NOT NULL DEFAULT 5;

PostgreSQL can add a column with a constant default without updating each existing row. The ALTER TABLE still takes an ACCESS EXCLUSIVE lock. That behavior comes from PostgreSQL’s column-addition rules and lock rules, not from either migration runner.

With the connection settings recorded in the example, info showed V2 as pending. migrate applied the file; validate checked the available files against Flyway’s recorded history:

flyway -locations=filesystem:flyway/sql info
flyway -locations=filesystem:flyway/sql migrate
flyway -locations=filesystem:flyway/sql validate

The apply reported:

Current version of schema "flyway": 1
Migrating schema "flyway" to version "2 - reorder level"
Successfully applied 1 migration to schema "flyway", now at version v2 (execution time 00:01.426s)

The team wrote the SQL and reviewed it before migrate. Flyway supplied ordering, execution, and history validation. Its validate command checks migration metadata and SQL checksums against that history.

Generate the change with Ptah

The Ptah path starts with the desired table definition, including the new column:

CREATE TABLE public.products (
    id bigint PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    stock integer NOT NULL DEFAULT 0,
    archived boolean NOT NULL DEFAULT false,
    reorder_level integer NOT NULL DEFAULT 5
);

Ptah compared this file with its version 1 database. The separate shadow database was disposable: generation reset it to replay the existing history and verify the new migration, including a down-and-up replay. We inspected both generated files, sealed the directory, previewed the pending version, and then applied it:

ptah migrations generate --db-url "$PTAH_DB_URL" --schema-file schema.sql --schemas public --migrations-dir ptah/migrations --name reorder_level --shadow-db "$PTAH_SHADOW_URL"
ptah migrations hash --dir ptah/migrations
ptah migrations up --db-url "$PTAH_DB_URL" --migrations-dir ptah/migrations --verify-sum --dry-run
ptah migrations up --db-url "$PTAH_DB_URL" --migrations-dir ptah/migrations --verify-sum

The generated up file contained this statement:

ALTER TABLE "public"."products" ADD COLUMN "reorder_level" integer NOT NULL DEFAULT 5;

The dry run showed that exact SQL. Apply recorded the new version:

✅ Migrations completed successfully!
Database is now at version: 1793683584

The generated down file drops the column with CASCADE and warns about lost data and dependent objects. Its successful shadow replay proves that it ran on the disposable fixture. The down SQL still needs review before anyone uses it to recover a real deployment.

Here the team edits schema.sql as its desired state. Reviewers approve the generated up and down SQL before deployment. The shadow database checks that the migration pair can replay on this fixture. Maintaining the schema file and disposable shadow database adds work to the generation path; Ptah’s handwritten migration path needs neither.

Both databases ended with identical product-column records and these four rows. Ptah’s schema comparison also reported no difference from the desired file:

id,sku,stock,archived,reorder_level
1,KIT-42,12,f,5
2,CABLE-7,3,f,5
3,ADAPTER-2,0,f,5
4,OLD-KIT,8,t,5

Check a live change separately from migration history

We added one column outside either migration directory, on the Flyway-managed database:

ALTER TABLE public.products ADD COLUMN manual_note text;

Flyway validate still succeeded. Its migration files and history still agreed. We then ran Ptah’s read-only comparison against the same database:

flyway -locations=filesystem:flyway/sql validate
ptah schema compare --db-url "$FLYWAY_DB_URL" --schema-file schema.sql --schemas public --exit-code

Ptah returned exit code 1 and named the changed table:

Differences detected (1 category):
  tables_modified (1): public.products

The full comparison also proposed DROP COLUMN "manual_note" CASCADE as reconciliation SQL. We did not apply it. A detected difference calls for investigating who added the column and what depends on it. A final query found the extra column and all four product rows still present. Flyway’s check -drift command is in Enterprise; the measured validate result describes the OSS command used here.

We also changed copies of the already applied V2 files. Flyway validate rejected its copy with a checksum mismatch (exit 1). Ptah migrations status --verify-sum rejected its copy because it no longer matched ptah.sum (exit 2). The Flyway result and Ptah result make the distinction plain: Flyway compared the file with its database history, while Ptah compared the directory with ptah.sum. Neither check inspected the live table.

We then changed the SQL in another copy of Ptah’s applied V2 file from DEFAULT 5 to DEFAULT 6 and rehashed that copy. Directory validation passed. migrations status --verify-sum reported a modified applied migration but exited 0; adding --exit-code made it exit 1. The recorded output shows the database’s original checksum and the copy’s new checksum. The database history was not changed.

After deployment, use ptah migrations status --verify-sum --exit-code to gate on the applied release’s directory and history. Pending migrations also make that command return 1, so a pre-deployment job must account for the planned pending version; migrations validate can check the directory before deployment. Run live comparison as a separate check with a schema file that matches the deployment stage. Before deployment, compare the database with the schema of the release currently installed. After deployment, compare it with the schema of the release just applied. Comparing a V1 database with a proposed V2 schema would report the intended change as a difference.

The read-only Flyway sidecar in this experiment scopes Ptah to public and keeps Flyway’s history table in a separate flyway schema. An existing installation with flyway_schema_history in public needs an explicit exclusion before using this recipe; moving its history table is not part of the check.

Adoption and recovery boundaries

Ptah also runs handwritten migrations in order and records applied versions. Handwritten SQL alone is no reason to keep Flyway. A new project can choose Ptah for that workflow, then add schema generation and live comparison when they serve its process. An existing Flyway project can keep its runner and add only the read-only comparison. That does not import Flyway’s migration history.

Ptah v0.17.0 is MIT-licensed and pre-GA. Plan CLI upgrade checks when adopting it. The generated down file would discard this column’s values; neither tool’s rollback command substitutes for an application recovery plan.

This comparison covers PostgreSQL 18.6 and the named releases. Check the Flyway database matrix and Ptah support matrix for other targets. If the team later chooses to transfer its existing Flyway history to Ptah, our verified handoff guide covers that separate decision and its repeatable-migration boundary.

Put Ptah to work

Example files

← All articles