DEV Community: Munteanu Flavius-Ioan

I built a tool that runs your Postgres migrations before they hit production

Munteanu Flavius-Ioan — Sat, 14 Mar 2026 17:02:57 +0000

Last month I shipped pgfence, a CLI that tells you what lock modes your Postgres migrations take and how to rewrite them safely. It works by parsing your SQL and looking up each DDL statement in a lock mode table.

The feedback I kept getting: "How do I know your lookup table is right?"

Fair question. So I built trace mode.

What trace mode does

pgfence trace migrations/add-verified.sql

pgfence - Trace Report (PostgreSQL 17, Docker)

  migrations/add-verified.sql  [HIGH]

  #  Statement                          Lock Mode         Blocks  Risk    Verified    Duration
  1  ALTER TABLE users ADD COLUMN       ACCESS EXCLUSIVE  R + W   HIGH    Confirmed   2ms
     email_verified BOOLEAN NOT NULL
  2  CREATE INDEX idx ON users(email)   SHARE             W       MEDIUM  Confirmed   1ms

  Trace-Only Findings:
  ! Table rewrite detected on "users" (relfilenode changed)

  === Coverage ===
  Analyzed: 2 statements | Verified: 2/2 | Mismatches: 0 | Trace-only: 1
  Docker: postgres:17-alpine | Container lifetime: 4.2s

Every statement gets a verification status:

Confirmed: pgfence's static prediction matched real Postgres behavior
Mismatch: the prediction was wrong. You see what actually happened.
Trace-only: something Postgres did that static analysis can't predict (table rewrites, implicit locks on sequences)

How it works

pgfence runs static analysis first (all the normal rules and safe rewrite recipes)
Pulls postgres:17-alpine and starts a container on a random 127.0.0.1 port
Each statement executes inside BEGIN/COMMIT so locks are held when we snapshot
After each statement, diffs: pg_locks (lock modes), pg_class.relfilenode (table rewrites), pg_attribute (column changes), pg_constraint (validation state), pg_index (index validity)
Static predictions are matched against trace observations
Container is deleted

The whole thing takes 3-5 seconds for a typical migration file. The container is ephemeral: random password, 127.0.0.1 only, cleaned up even on SIGINT/SIGTERM.

The CONCURRENTLY problem

CREATE INDEX CONCURRENTLY is the most common safe migration pattern. It's also the hardest to trace.

The reason: Postgres rejects CONCURRENTLY inside a transaction. If you wrap everything in BEGIN/COMMIT to hold locks for snapshotting, CONCURRENTLY statements fail.

Eugene (a Rust-based tool with a similar trace feature) solves this by wrapping everything in a transaction and rolling back. CONCURRENTLY statements are just skipped.

pgfence takes a different approach. Since the Docker container is disposable, there's no need for transactions on CONCURRENTLY statements. Instead, pgfence opens a second "observer" connection that polls pg_locks every 50ms while the main connection executes the statement. The observer captures every lock mode that was held during execution.

This means pgfence is the only tool that can verify the lock behavior of CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, and DETACH PARTITION CONCURRENTLY.

What trace mode catches that static analysis can't

Table rewrites. When Postgres changes a column type, it sometimes rewrites the entire table (new relfilenode). Static analysis can detect known rewrite patterns, but trace mode sees the actual rewrite happen by diffing pg_class.relfilenode before and after.

Implicit locks. Adding a column to a table with a serial column implicitly locks the sequence. Adding a foreign key locks the referenced table. These cascade effects are visible in pg_locks but not in the DDL syntax.

Version-specific behavior. ALTER TYPE ADD VALUE takes EXCLUSIVE on PG12+ but ACCESS EXCLUSIVE on PG11. ADD COLUMN ... DEFAULT is instant on PG11+ but rewrites the table on PG10. Trace mode tests against the exact version you specify.

CI integration

pgfence trace --ci --max-risk medium migrations/*.sql

Exit code 1 if any check exceeds the risk threshold, any mismatch between prediction and reality, or any execution error. Mismatches are CI failures because a mismatch means the static analysis is wrong for that statement.

Try it

npm install -D @flvmnt/pgfence
pgfence trace migrations/*.sql

Requires Docker. Works with raw SQL, TypeORM, Prisma, Knex, Drizzle, and Sequelize migrations.

If you find a mismatch between pgfence's static analysis and what trace mode observes, please open an issue. Every mismatch report helps make the static analysis more accurate.

GitHub | Website | npm

The ALTER TABLE that took down our API for 6 minutes

Munteanu Flavius-Ioan — Wed, 25 Feb 2026 18:40:33 +0000

Last Tuesday at 2:47 PM, we deployed a migration that looked completely innocent:

ALTER TABLE users ADD COLUMN email_verified boolean NOT NULL;
CREATE INDEX idx_users_email ON users(email);

Two statements. Nothing exotic. The kind of thing you've written a hundred times.

At 2:48 PM, every API endpoint that touched the users table started timing out. Our health checks went red. PagerDuty fired. Customers couldn't log in.

At 2:54 PM — six minutes later — the migration finished and everything recovered on its own.

The root cause wasn't a bug. It was a lock.

What actually happened

Every DDL statement in Postgres acquires a lock on the table it modifies. The lock type depends on the statement:

Statement	Lock Mode	What it blocks
`ADD COLUMN ... NOT NULL` (no DEFAULT)	ACCESS EXCLUSIVE	Everything — reads, writes, DDL
`CREATE INDEX` (no CONCURRENTLY)	SHARE	All writes
`SELECT`	ACCESS SHARE	Nothing

ACCESS EXCLUSIVE is the nuclear option. It blocks every other operation on the table — including simple SELECT queries — until the DDL completes.

On a small table, this takes milliseconds. On our users table with 8 million rows, the ADD COLUMN ... NOT NULL without a default forces Postgres to rewrite the entire table while holding that lock. Every query stacks up in the lock queue. The queue backs up into connection pool exhaustion. The pool exhaustion cascades to every service.

Six minutes of downtime from a two-line migration.

The fix we never should have needed

The safe way to add a NOT NULL column is a multi-step expand/contract pattern:

-- Step 1: Add the column as nullable (instant, no rewrite)
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified boolean;

-- Step 2: Backfill out-of-band in batches (not in a migration)
-- UPDATE users SET email_verified = false WHERE email_verified IS NULL LIMIT 1000;

-- Step 3: Add the constraint without validating (brief lock)
ALTER TABLE users ADD CONSTRAINT chk_nn
  CHECK (email_verified IS NOT NULL) NOT VALID;

-- Step 4: Validate (reads table but doesn't block writes)
ALTER TABLE users VALIDATE CONSTRAINT chk_nn;

-- Step 5: Now it's safe to enforce
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_nn;

And that CREATE INDEX? Should have been:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

CONCURRENTLY takes longer but only requires a SHARE UPDATE EXCLUSIVE lock — it allows reads and writes to continue.

Every experienced Postgres DBA knows these patterns. The problem is that migrations are written by application developers who don't. And code review doesn't catch lock modes — they're not visible in the SQL.

pgfence: catch it before your users do

We built pgfence to make these problems visible before they reach production. It's a CLI that analyzes your migration files and reports exactly what each statement locks, what it blocks, and how to fix it.

$ npx @flvmnt/pgfence analyze migrations/add_verified.sql

Output:

migrations/add_verified.sql  [HIGH]
Lock: ACCESS EXCLUSIVE | Blocks: reads+writes+DDL

#  Statement                                        Lock Mode         Blocks           Risk
1  ALTER TABLE users ADD COLUMN email_verified ...   ACCESS EXCLUSIVE  reads,writes,DDL HIGH
2  CREATE INDEX idx_users_email ON users(email)      SHARE             writes,DDL       MEDIUM

Safe Rewrite Recipe:
  add-column-not-null-no-default: Add nullable column, backfill, then add NOT NULL constraint

    ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified boolean;
    -- Backfill out-of-band in batches
    ALTER TABLE users ADD CONSTRAINT chk_nn CHECK (email_verified IS NOT NULL) NOT VALID;
    ALTER TABLE users VALIDATE CONSTRAINT chk_nn;
    ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
    ALTER TABLE users DROP CONSTRAINT chk_nn;

Policy Violations:
  ERROR  Missing SET lock_timeout — lock queue death spiral risk
  → Add SET lock_timeout = '2s'; at the start of the migration

Analyzed: 2 statements | Unanalyzable: 0 | Coverage: 100%

It tells you:

What lock mode each statement takes
What it blocks (reads, writes, DDL)
The risk level (LOW / MEDIUM / HIGH / CRITICAL)
The safe rewrite — the exact SQL to use instead
Policy violations — missing lock_timeout, CONCURRENTLY inside a transaction, etc.

How it works

pgfence doesn't use regex to guess at SQL patterns. It uses libpg_query — PostgreSQL's actual parser, compiled to a C library and exposed via Node.js bindings. The same parser that Postgres itself uses to understand your SQL.

This means it handles edge cases that regex-based tools miss:

-- pgfence correctly identifies this as safe (PG11+ metadata-only):
ALTER TABLE t ADD COLUMN status text DEFAULT 'active';

-- And this as dangerous (volatile default forces rewrite):
ALTER TABLE t ADD COLUMN created_at timestamptz DEFAULT now();

38 checks across the full DDL surface

Not just ADD COLUMN and CREATE INDEX. pgfence covers:

Critical: DROP TABLE, TRUNCATE, REINDEX SCHEMA/DATABASE
High: ADD FOREIGN KEY without NOT VALID, ADD UNIQUE without concurrent index, VACUUM FULL, ATTACH PARTITION, REFRESH MATERIALIZED VIEW
Medium: SET NOT NULL without pre-validated constraint, ALTER TYPE ADD VALUE on PG < 12, CREATE/DROP TRIGGER
Low: Safe patterns it recognizes and doesn't flag — ADD COLUMN DEFAULT <constant> on PG11+, ADD UNIQUE USING INDEX, DETACH PARTITION CONCURRENTLY

Works with your ORM

pgfence extracts SQL from ORM migration files:

# TypeORM
pgfence analyze --format typeorm src/migrations/*.ts

# Knex
pgfence analyze --format knex migrations/*.ts

# Prisma (analyzes the generated SQL files)
pgfence analyze prisma/migrations/**/migration.sql

# Plain SQL
pgfence analyze migrations/*.sql

It handles queryRunner.query() calls in TypeORM, knex.raw() and builder chains in Knex, queryInterface calls in Sequelize, and Drizzle migrations.

One line in CI

# .github/workflows/migration-check.yml
- name: Check migrations
  run: npx @flvmnt/pgfence analyze --ci --max-risk medium migrations/*.sql

Exit code 1 on HIGH risk or above. The build fails before the migration reaches production.

Optional: table-size-aware scoring

A CREATE INDEX on a 500-row lookup table is fine. The same statement on a 50M-row events table is a production incident. pgfence can adjust risk levels based on table sizes:

# Export table stats from your read replica (you control the connection)
pgfence extract-stats --db-url postgres://readonly@replica/mydb > pgfence-stats.json

# Analyze with size awareness
pgfence analyze --stats-file pgfence-stats.json migrations/*.sql

pgfence never asks for database credentials directly. The stats export runs in your CI environment using your existing secrets.

Why not Eugene / Squawk / strong_migrations?

Those are excellent tools. Here's what's different:

	pgfence	Eugene	Squawk	strong_migrations
Language	TypeScript	Rust	Rust	Ruby
ORM extraction	TypeORM, Knex, Prisma, Sequelize, Drizzle	SQL only	SQL only	Rails only
Safe rewrite output	Full SQL recipes	Warnings only	Warnings only	Suggestions
Table-size scoring	Yes (via stats snapshot)	No	No	No
Lock mode mapping	All 8 PG lock modes	Yes	Partial	No

If you're a Rails shop, strong_migrations is the right choice. If you're in the Node/TypeScript ecosystem with TypeORM or Knex or Prisma — pgfence was built for you.

Get started

npm install -D @flvmnt/pgfence

Analyze your existing migrations:

npx pgfence analyze migrations/*.sql

You'll probably find at least one statement you didn't know was dangerous.

GitHub · Docs · npm

How we stopped ORM migrations from taking down our Postgres database

Munteanu Flavius-Ioan — Sun, 22 Feb 2026 17:00:39 +0000

If you've ever run a database migration that applied a seemingly minor change, like ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT false, only to watch your API response times spike and connection pools exhaust, you've met the Postgres ACCESS EXCLUSIVE lock.

Modern ORMs like TypeORM, Sequelize, Prisma, and Drizzle hide the underlying Postgres locking mechanics. When developers can't see the DDL statements their ORMs are running, they can't optimize them.

The Lock Queue Death Spiral

Postgres requires strict locks for schema changes. When a migration runs an ALTER TABLE command, it normally requires an ACCESS EXCLUSIVE lock.
If there's currently a 30-second reporting query running on that table, the migration has to wait in line.

While the migration is waiting, every other incoming production query behind it is also forced to wait. Suddenly, your app is down.

Introducing `pgfence`

To solve this, I built pgfence, a source-available TypeScript CLI designed specifically for the Node.js ecosystem. It's the first tool in this space built natively for Node. The alternatives (strong_migrations, Eugene, Squawk) are Ruby or Rust, which creates real friction if your stack is TypeScript.

Unlike other linters, pgfence parses the Abstract Syntax Trees (ASTs) of your ORM's migration files (supporting .ts files from TypeORM, Knex, Sequelize, and Prisma). It statically extracts the SQL and evaluates the risk before you ever merge to main.

1. Predicting Lock Modes

pgfence matches every DDL command to the Postgres lock matrix. It prints out exactly what locks are being grabbed and whether they will block Reads, Writes, or both.

2. Enforcing Timeout Policies

If you don't explicitly declare SET lock_timeout = '2s' inside a migration, a stuck migration will bring down your app. pgfence scans your migrations and fails CI if timeouts are omitted.

3. Giving you the fix

pgfence provides "Safe Rewrite Recipes." If you try to add a column with a volatile default, it gives you the exact 3-step zero-downtime expand/contract migration to use instead.

Try it out

You can integrate pgfence into your GitHub Actions today. It's free and open-source.

npx pgfence analyze migrations/*.sql

Website: pgfence.com
GitHub: flvmnt/pgfence

Your NestJS Idempotency Layer is Probably Broken

Munteanu Flavius-Ioan — Wed, 18 Feb 2026 22:40:46 +0000

Most idempotency implementations in NestJS apps look the same: hash the key, check Redis, return cached response or proceed. 40 lines, done.

I'm building a booking platform with NestJS - concurrent mutations against shared resources, money involved. While writing the idempotency layer I kept finding edge cases that the simple version doesn't handle. There are at least five, and most of them are exploitable.

The version everyone ships

@Injectable()
export class IdempotencyInterceptor implements NestInterceptor {
  constructor(private readonly redis: Redis) {}

  async intercept(context: ExecutionContext, next: CallHandler) {
    const req = context.switchToHttp().getRequest();
    const key = req.headers['idempotency-key'];
    if (!key) return next.handle();

    const cached = await this.redis.get(`idem:${key}`);
    if (cached) return of(JSON.parse(cached));

    const response = await lastValueFrom(next.handle());
    await this.redis.set(`idem:${key}`, JSON.stringify(response), 'EX', 86400);
    return of(response);
  }
}

It has at least five holes.

Keys aren't scoped

The raw idempotency key goes straight into Redis. So:

User A sends Idempotency-Key: abc123 to POST /orders
User B sends Idempotency-Key: abc123 to POST /payments
User B gets User A's cached order response

Unlikely? Sure. But "unlikely" in security means "will happen at scale, probably during an incident when you're already stressed."

Keys need to be scoped to the actor and the endpoint:

function buildRedisKey(actorId: string, method: string, path: string, idempotencyKey: string): string {
  const keyHash = createHash('sha256').update(idempotencyKey).digest('hex');
  return `idempotency:${actorId}:${method}:${path}:${keyHash}`;
}

The idempotency key gets hashed before going into the Redis key. Not for secrecy - it prevents injection of Redis key separators (:) in user-supplied values and normalizes key length.

Payload switching

User sends Idempotency-Key: order-1 with { item: "book", quantity: 1 }. Gets a 201. Sends the same key with { item: "laptop", quantity: 100 }. The naive version replays the cached 201. The user sees "order confirmed" for a laptop that was never actually ordered.

The scarier version: an attacker probes with a throwaway payload, waits for the cache to expire, then replays the key with an expensive one.

Hash the body, reject mismatches.

function hashBody(body: unknown): string {
  const stable = JSON.stringify(body, Object.keys(body ?? {}).sort());
  return createHash('sha256').update(stable).digest('hex');
}

// On cache hit:
const cached = JSON.parse(await redis.get(redisKey));
if (cached.bodyHash !== hashBody(req.body)) {
  // Extend TTL - don't let them wait it out
  await redis.expire(redisKey, 86400);
  throw new ConflictException({
    code: 'IDEMPOTENCY_CONFLICT',
    message: 'This idempotency key was already used with a different request body.',
  });
}

The TTL extension on conflict matters. Without it, the attacker just waits for the entry to expire and tries again.

The concurrent first-use race

Two identical requests arrive within the same millisecond. Both check Redis. Both miss. Both execute the mutation. Double booking.

Your database constraints might save you (unique indexes, optimistic locking), but "might" isn't a word I want near payment processing.

const lockKey = `${redisKey}:lock`;
const acquired = await redis.set(lockKey, '1', 'EX', 5, 'NX');

if (!acquired) {
  await sleep(100);
  const cached = await redis.get(redisKey);
  if (cached) return replay(cached);
  // First request still running or crashed. Let this one through -
  // the DB-level constraints are the final safety net.
}

SET NX is atomic. Only one request wins. The loser waits 100ms, checks if the winner cached a result, and either replays it or proceeds (because the winner might have crashed, and the 5-second lock TTL ensures we don't deadlock).

This is defense in depth: the idempotency layer catches the common case, and the database catches the rest. Neither alone is enough.

Not all errors should be cached

The naive version caches every response for 24 hours. Including 503s, 429s, and 408s.

So your server has a brief Redis hiccup, returns a 503 to one request, and now every retry of that idempotency key for the next 24 hours replays "Service Unavailable." The user literally cannot complete their action.

Different status codes need different treatment:

function getTtlForStatus(statusCode: number): number | null {
  if (statusCode >= 200 && statusCode < 300) return 14400; // 4h - it worked, cache it

  if (statusCode >= 400 && statusCode < 500) {
    if (statusCode === 409) return 2;          // Brief dampener, not a wall
    if ([429, 408, 423].includes(statusCode)) return null; // Retryable, don't cache
    return 14400;                              // 400, 422 etc - retrying won't fix bad input
  }

  if (statusCode >= 500) {
    if (statusCode === 503) return null;       // "Try again" means let them try again
    return 10;                                 // Brief thundering-herd protection
  }

  return null;
}

The 409 with a 2-second TTL is worth explaining: when two requests race and the loser gets a DB conflict, you want a tiny dampener to absorb the immediate retry storm, but nothing longer. The client needs to be able to recover.

Success preservation

This one is subtle.

Request A and Request B arrive simultaneously, same key, same body. A gets the lock, runs the mutation, gets 201, starts writing to Redis. B's lock attempt timed out, it ran anyway, got a 409 from the database (unique constraint), and writes its result to Redis.

If B's write lands after A's, the cache now holds a 409 for a mutation that succeeded. Every future replay tells the client their operation failed. They retry with a new idempotency key, and now you have a duplicate.

The rule is simple: never let an error overwrite a success.

async function cacheResponse(redisKey: string, statusCode: number, body: unknown, bodyHash: string) {
  const ttl = getTtlForStatus(statusCode);
  if (ttl === null) return;

  const isSuccess = statusCode >= 200 && statusCode < 300;

  if (!isSuccess) {
    const existing = await redis.get(redisKey);
    if (existing) {
      const parsed = JSON.parse(existing);
      if (parsed.statusCode >= 200 && parsed.statusCode < 300) {
        return; // A 201 is already cached. Don't touch it.
      }
    }
  }

  await redis.set(redisKey, JSON.stringify({ statusCode, body, bodyHash }), 'EX', ttl);
}

Cardinality attacks

Someone writes a script that sends a million requests with unique idempotency keys. The mutations all fail validation, but each one creates a Redis cache entry that sits there for 4 hours. Your Redis instance fills up.

Per-actor quota, bucketed by time window:

async function checkQuota(actorId: string): Promise<boolean> {
  const bucket = Math.floor(Date.now() / 60000);
  const quotaKey = `idem-quota:${actorId}:${bucket}`;

  const count = await redis.incr(quotaKey);
  if (count === 1) {
    await redis.expire(quotaKey, 120); // 2x window for clock skew
  }

  return count <= 30;
}

INCR + EXPIRE instead of SETEX because INCR atomically creates the key if it doesn't exist and increments it. SETEX would reset the counter on every call. The 120-second TTL (double the 60-second window) ensures the key outlives its bucket.

30 unique keys per minute per user is generous for legitimate use and devastating for an attacker trying to fill your Redis.

A few more things worth getting right

Skip unauthenticated requests. Without an actor ID you can't scope keys. An "anonymous" bucket keyed by IP sounds reasonable until you remember that anyone behind the same NAT shares an IP - one user's cached response replays for another. Just skip idempotency for unauthenticated endpoints.

Hash multipart uploads carefully. A 50MB file upload with the same idempotency key is almost certainly a retry. But hashing 50MB on every request is expensive. Hash only the non-file fields.

Rate limiter integration. If a request is a cache hit (idempotent replay), don't count it against rate limits. The user isn't making a new request. They're recovering from a dropped connection. Penalizing retries is punishing correct client behavior.

Redis going down. If your idempotency layer throws 500s when Redis is unavailable, a Redis blip takes down the entire API. Skip idempotency and let requests through instead. You lose duplicate protection for a few seconds. You don't lose your entire service.

The full implementation is around 500 lines. Most of it is boring, defensive code - which is probably why so many production systems are still running the 40-line version.

DEV Community: Munteanu Flavius-Ioan

I built a tool that runs your Postgres migrations before they hit production

What trace mode does

How it works

The CONCURRENTLY problem

What trace mode catches that static analysis can't

CI integration

Try it

The ALTER TABLE that took down our API for 6 minutes

What actually happened

The fix we never should have needed

pgfence: catch it before your users do

How it works

38 checks across the full DDL surface

Works with your ORM

One line in CI

Optional: table-size-aware scoring

Why not Eugene / Squawk / strong_migrations?

Get started

How we stopped ORM migrations from taking down our Postgres database

The Lock Queue Death Spiral

Introducing pgfence

1. Predicting Lock Modes

2. Enforcing Timeout Policies

3. Giving you the fix

Try it out

Your NestJS Idempotency Layer is Probably Broken

The version everyone ships

Keys aren't scoped

Payload switching

The concurrent first-use race

Not all errors should be cached

Success preservation

Cardinality attacks

A few more things worth getting right

Introducing `pgfence`