Skip to content

migration-safety-with-pgfence.mdx

Latest commit

 

History

History
212 lines (150 loc) · 8.01 KB

migration-safety-with-pgfence.mdx

File metadata and controls

212 lines (150 loc) · 8.01 KB
title Analyze migration safety with pgfence
slug migration-safety-with-pgfence

import Section from "@mdx/Section.astro"; import Prerequisites from "@mdx/Prerequisites.astro"; import Callout from "@mdx/Callout.astro"; import Npm from "@mdx/Npm.astro"; import Steps from "@mdx/Steps.astro";

- Get started with [PostgreSQL](/docs/get-started-postgresql) - [Drizzle Kit](/docs/kit-overview) - [Drizzle migrations](/docs/kit-overview#running-migrations)

When you run drizzle-kit generate, Drizzle creates plain SQL migration files in your drizzle/ folder. Before applying those migrations to production, you can use pgfence to analyze them for dangerous lock patterns and get safe rewrite suggestions.

pgfence is a Postgres migration safety CLI that reads your SQL files and reports:

  • Lock modes each statement acquires (e.g. ACCESS EXCLUSIVE, SHARE)
  • Risk levels (LOW, MEDIUM, HIGH, CRITICAL)
  • Safe rewrite recipes when a dangerous pattern is detected

This helps you catch migrations that could block reads or writes on busy tables before they ever reach production.

Install pgfence

@flvmnt/pgfence -D

The workflow

The recommended workflow with Drizzle and pgfence is straightforward: generate, analyze, migrate.

Generate your migration

After making schema changes, generate the SQL migration as usual:

npx drizzle-kit generate

This creates a new .sql file inside your drizzle/ migrations folder.

Analyze the migration with pgfence

Run pgfence against the generated SQL file:

npx --yes @flvmnt/pgfence@0.2.3 analyze drizzle/*.sql

pgfence parses each SQL statement using PostgreSQL's actual parser and checks it against known dangerous patterns.

Review the output and apply

If pgfence reports no issues, you can safely apply the migration:

npx drizzle-kit migrate

If pgfence flags a dangerous pattern, review the safe rewrite recipe it provides and adjust your migration accordingly.

Understanding pgfence output

Let's say you have a Drizzle schema change that adds a NOT NULL column with a default value to an existing table:

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age').notNull().default(0), // new column
});

After running drizzle-kit generate, the SQL migration might look like this:

ALTER TABLE "users" ADD COLUMN "age" integer NOT NULL DEFAULT 0;

Running npx --yes @flvmnt/pgfence@0.2.3 analyze drizzle/0001_add_age.sql produces output like:

```bash copy npx --yes @flvmnt/pgfence@0.2.3 analyze drizzle/0001_add_age.sql ``` 
pgfence v0.2.3 — Postgres migration safety analysis

drizzle/0001_add_age.sql
┌─────────────────────────────────────────────────────────────┐
│  ADD COLUMN with NOT NULL + DEFAULT                         │
│  Lock: ACCESS EXCLUSIVE  Risk: LOW (PG11+ instant)          │
│  Table: users                                               │
│  Note: Safe on PostgreSQL 11+ (metadata-only operation)     │
└─────────────────────────────────────────────────────────────┘

Analyzed 1 SQL statement. 0 issues found.
Adding a column with a constant `DEFAULT` value is instant (metadata-only) on PostgreSQL 11 and later. On older versions, Postgres rewrites the entire table. pgfence is aware of this distinction and adjusts the risk level accordingly. You can specify `--pg-version 10` to see the older behavior.

When pgfence catches a dangerous pattern

Consider a migration that creates a non-concurrent index:

CREATE INDEX idx_users_name ON users (name);

pgfence flags this because CREATE INDEX without CONCURRENTLY acquires a SHARE lock, which blocks all writes to the table for the duration of the index build:

drizzle/0002_add_index.sql
┌─────────────────────────────────────────────────────────────┐
│  CREATE INDEX (non-concurrent)                              │
│  Lock: SHARE  Risk: MEDIUM                                  │
│  Table: users                                               │
│                                                             │
│  Safe rewrite:                                              │
│  CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_name      │
│    ON users (name);                                         │
└─────────────────────────────────────────────────────────────┘
`CREATE INDEX CONCURRENTLY` cannot run inside a transaction. If your migration runner wraps statements in a transaction, you'll need to run this statement separately. pgfence detects and warns about this case too.

Common patterns pgfence detects

Here are the most common patterns pgfence checks for in Drizzle-generated migrations:

Pattern Lock mode Risk Safe alternative
ADD COLUMN ... NOT NULL (no default) ACCESS EXCLUSIVE HIGH Add nullable, backfill, then set NOT NULL
CREATE INDEX (non-concurrent) SHARE MEDIUM CREATE INDEX CONCURRENTLY
ALTER COLUMN TYPE ACCESS EXCLUSIVE HIGH Expand/contract pattern
ADD CONSTRAINT ... FOREIGN KEY ACCESS EXCLUSIVE HIGH NOT VALID + VALIDATE CONSTRAINT
ADD CONSTRAINT ... UNIQUE ACCESS EXCLUSIVE HIGH Build concurrent unique index, then USING INDEX
DROP TABLE ACCESS EXCLUSIVE CRITICAL Separate release

CI integration with GitHub Actions

You can add pgfence to your CI pipeline so every pull request that includes migration changes gets automatically analyzed. Here's a GitHub Actions workflow:

name: Migration safety check

on:
  pull_request:
    paths:
      - 'drizzle/**'

jobs:
  pgfence:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: 20

      - run: npm install

      - name: Analyze migrations
        run: npx --yes @flvmnt/pgfence@0.2.3 analyze --ci --max-risk medium --output github drizzle/*.sql

The --ci flag makes pgfence exit with code 1 when any finding exceeds the --max-risk threshold. The --output github flag formats the output as a markdown summary suitable for GitHub PR comments.

For more accurate risk assessment, pgfence can factor in table sizes. Generate a stats snapshot from your read replica with `pgfence extract-stats` and pass it via `--stats-file pgfence-stats.json`. Tables with over 1 million rows automatically escalate risk levels. See the [pgfence docs](https://pgfence.com) for details.

Output formats

pgfence supports multiple output formats that you can choose with the --output flag:

# Default CLI table output
npx --yes @flvmnt/pgfence@0.2.3 analyze drizzle/*.sql

# Machine-readable JSON
npx --yes @flvmnt/pgfence@0.2.3 analyze --output json drizzle/*.sql

# GitHub PR comment markdown
npx --yes @flvmnt/pgfence@0.2.3 analyze --output github drizzle/*.sql

Further reading