Comparison
pgroll vs pgfence
pgroll (Xata's zero-downtime Postgres migration executor) and pgfence (a Postgres migration safety analyzer) solve different problems. This page is mostly disambiguation: what each tool does, and why the common answer is to use both.
What pgroll is best at
pgroll is a migration executor for Postgres, built and maintained by Xata. As of v0.16.2 it is a single Go binary with no external dependencies, targeting Postgres 14 and later (including RDS and Aurora). You define a migration as a structured operation (create_table, add_column, alter_column, create_index, and more) in YAML or JSON, and pgroll carries it out using an expand or contract pattern: it creates versioned schema views over the physical tables so old and new application code can both keep working against the same table mid-migration, backfills and syncs changed columns with triggers, and lets you roll back instantly at any point before you mark the migration complete. If you want a tool that actually runs zero-downtime schema changes for you, not just tells you they would be risky, pgroll does that job well.
Important honest framing: pgroll and pgfence are not competitors. pgroll is an executor, it applies a migration to a live database. pgfence is an analyzer, it reads the SQL a migration will run and reports the lock mode and risk before anything executes. This page exists for the "pgroll vs pgfence" and "do I need both" searches, and the honest answer for most of those is: you are probably asking the wrong question, they solve adjacent problems and are commonly used together.
Feature comparison
| Feature | pgroll (v0.16.2) | pgfence (v0.8.0) |
|---|---|---|
| Category | Migration executor | Migration safety analyzer (lint + trace) |
| Language | Go | TypeScript (Node.js 20+) |
| License | Apache-2.0 | MIT |
| What it does | Applies schema changes to a live Postgres database using expand or contract: dual-schema views, backfill triggers, instant rollback | Reads migration SQL statically (and optionally traces it in Docker) and reports lock modes, risk levels, and safe rewrite recipes before anything runs |
| Migration definition format | Its own YAML/JSON operation schema, plus a raw SQL sql operation as an escape hatch | Whatever your migration tool already emits: raw .sql, TypeORM, Prisma, Knex, Kysely, Sequelize, Drizzle |
| Converts existing SQL migrations | Yes (pgroll convert translates a .sql file into pgroll's format) | N/A, reads SQL directly, nothing to convert |
| Executes migrations against a database | Yes, this is pgroll's core job | No, analyzer only, never writes to a database |
| Zero-downtime dual-schema versioning | Yes, this is pgroll's core mechanism | Out of scope, that is pgroll's job, not pgfence's |
| Static lock-mode / safety analysis of arbitrary SQL | No, explicitly disclaimed for its raw SQL escape hatch | Yes, every statement labeled with lock mode and blocked operations |
| Safe rewrite recipes in output | No | Yes (step-by-step expand or contract sequences) |
| Trace mode (live lock verification) | No | Yes (Docker, observes pg_locks) |
| DB-size-aware risk scoring | No | Yes (stats snapshot or read replica) |
| Instant rollback of an in-progress migration | Yes, before the migration is marked complete | N/A, pgfence never executes a migration |
| Postgres version support | 14+ (RDS, Aurora compatible) | Any version supported by the statements it parses, via libpg_query |
| GitHub Action / CI | Community examples | Yes (composite action, CI mode with --max-risk) |
The gap pgroll's own docs point at
pgroll's raw SQL operation exists as an "escape hatch" for anything its structured operations do not cover, and pgroll's own documentation is direct about the limit: "pgroll is unable to guarantee that raw SQL migrations are safe and will not result in application downtime." That is not a criticism of pgroll, it is an honest scoping statement, migration execution and lock-safety analysis are different problems and pgroll only claims to solve the first one. That sentence is exactly the gap pgfence is built to fill: run pgfence against the SQL going into a sql: block (or against the source migration before you run pgroll convert on it) and you get the lock mode, the risk level, and the rewrite recipe for the part pgroll itself will not vouch for.
Do you need both?
- Use pgroll if you want an executor that runs zero-downtime migrations for you, especially for column type changes, renames, and NOT NULL additions where dual-schema views let old application code keep running unmodified during the rollout.
- Use pgfence if you want a pre-merge safety net that works with whatever migration tool or raw SQL you already have, without adopting a new migration definition format or an execution runtime.
- Use both if you want pgroll's zero-downtime execution and a static safety check on the raw SQL pgroll itself declines to vouch for. In practice this looks like: pgfence runs in CI on the migration SQL before it merges, and pgroll runs in the deploy pipeline to actually apply it.
See also
- Atlas vs pgfence: another executor-shaped tool (schema-as-code and migration planning), a similar "different category" comparison to this one.
- Bytebase vs pgfence: a governance platform, closest overlap with pgfence Cloud's roadmap rather than with the free analyzer.
- pgrubic vs pgfence: a direct linter competitor, 100+ rules and auto-fix, raw SQL only.
- Eugene vs pgfence: the other lock-tracing linter, closest in spirit to pgfence's trace mode.