DEV Community: Technical Turtle

The Postgres migration that locks your table at 3am, and how to catch it in review

Technical Turtle — Thu, 23 Jul 2026 07:07:44 +0000

-- looks fine in review, locks your busiest table in prod:
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- same result, no outage:
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

The top one scans every row under an ACCESS EXCLUSIVE lock, so every read and write to the table blocks until it finishes. On a big table that is a four-minute outage. The bottom one gets to the same place without ever holding that lock during a scan. A generalist reviewing the diff cannot tell them apart, because both just say "make email NOT NULL".

That is the whole problem. Whether a Postgres migration is safe has almost nothing to do with intent and everything to do with locks. A migration is dangerous when it holds a strong lock while it scans or rewrites a large table:

ACCESS EXCLUSIVE blocks reads and writes.
SHARE blocks writes.

On a ten-row table you never notice. On a hot production table it is an outage. And there is a second trap: Flyway and Liquibase wrap each migration in a transaction by default, which changes what is safe and what errors out.

Here are the changes most likely to hurt, and the safe rewrite for each.

1. CREATE INDEX without CONCURRENTLY

A plain CREATE INDEX takes a SHARE lock that blocks every write for the entire build. On a big table that is minutes of blocked writes.

-- danger
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- safe (note: cannot run inside a transaction)
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

The catch: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which is exactly how Flyway and Liquibase wrap migrations by default. A bare concurrent-index migration will error out. You have to disable the wrapping transaction for that one migration (Flyway: a script config flag; Liquibase: runInTransaction="false").

2. SET NOT NULL, directly

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL scans the whole table under ACCESS EXCLUSIVE to prove no nulls exist.

-- danger
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- safe (PG12+ skips the scan because a validated CHECK already proves it)
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;  -- scans, but only takes a SHARE UPDATE EXCLUSIVE lock (writes continue)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;      -- fast, no scan

3. ADD CHECK without NOT VALID

Same shape as above. A plain ADD CONSTRAINT ... CHECK scans the whole table under ACCESS EXCLUSIVE. Add it NOT VALID first (instant, only checks new rows), then VALIDATE CONSTRAINT in a separate step (scans under a weaker lock that lets writes continue).

4. ADD FOREIGN KEY without NOT VALID

Adding a foreign key validates every existing row while holding a write-blocking lock on both the referencing and referenced tables. Same fix: add it NOT VALID, then VALIDATE CONSTRAINT separately.

5. ADD COLUMN with a volatile DEFAULT or SERIAL

Since PG11, adding a column with a constant default is cheap (metadata only). But a volatile default has to be evaluated per row, which rewrites the whole table under ACCESS EXCLUSIVE.

-- danger: gen_random_uuid(), random(), and SERIAL are volatile -> full rewrite
ALTER TABLE events ADD COLUMN token uuid DEFAULT gen_random_uuid();

-- safe
ALTER TABLE events ADD COLUMN token uuid;            -- nullable, cheap
-- backfill in batches (see 8), then:
ALTER TABLE events ALTER COLUMN token SET DEFAULT gen_random_uuid();

A constant default like now() evaluated once, or a literal, is fine.

6. ALTER COLUMN TYPE

Changing a column type rewrites the table, with a few binary-coercible exceptions (for example varchar to text, or widening a varchar(n)). For anything else: add a new column, backfill it, swap in application code, drop the old column in a later deploy.

7. ADD PRIMARY KEY or UNIQUE, directly

Adding these builds the underlying index under ACCESS EXCLUSIVE. Build the index first, concurrently, then attach it:

CREATE UNIQUE INDEX CONCURRENTLY orders_pkey_idx ON orders (id);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_idx;

8. Backfills in the wrong order, or one giant statement

UPDATE big_table SET ... with no WHERE bound takes one long transaction, bloats the table, and can deadlock. And backfilling after you have already added the gating constraint fails. The order that works: add the column nullable, batch-backfill (by primary key ranges, committing each batch), validate, then tighten the constraint, across separate deploys.

9. Lock-taking DDL with no lock_timeout

Even a fast ALTER queues behind long-running queries, and everything queues behind it. One slow ALTER can stall all traffic to a table. Bound the wait:

SET lock_timeout = '2s';
ALTER TABLE ...;   -- if it cannot get the lock in 2s, it fails instead of stalling the app; retry

10. Destructive operations

DROP TABLE, TRUNCATE, DROP COLUMN, DROP SCHEMA CASCADE are irreversible and break rolling deploys where old app instances still reference the object. Stop referencing it in the app, deploy, then drop it in a later migration.

The pattern

Almost every safe rewrite above is the same move: never hold a strong lock while you scan or rewrite. Split the dangerous one-step change into a fast metadata change plus a separate, weakly-locked scan or a batched backfill. Do it across deploys.

I turned this into a tool

I do this review by hand often enough that I encoded it. The Postgres Migration Safety Auditor reads a Flyway or Liquibase migration (SQL, XML, YAML, JSON), parses it with libpg_query (the same parser Postgres uses, so a column named type or a dollar-quoted body is understood, not misread by a regex), and flags each hazard with the reason and, where it can justify one from a cited catalog, the safe rewrite. 36 rules, every one pointing at an official Postgres, Flyway, or Liquibase source.

It runs three ways: as a CLI, as a CI gate (machine-readable JSON, exit codes keyed to severity so it fails the build on a blocker), or as a Claude Code skill. It never connects to your database and never runs the migration, so it needs no credentials and nothing leaves your machine. Python 3, nothing to pip install (the parser is bundled per platform). One-time EUR 14.99.

The full ten-hazard cheatsheet is free on the landing page, so grab that even if the tool is not for you: https://technical-turtle.com/postgres-migration-auditor#cheatsheet

There is also a free, open-source version, pg-migration-guard, that runs these ten rules as a CLI and a GitHub Action, if you want the check in CI without the full catalog.

Most migration outages are one of these ten. Knowing them is most of the fight; automating the check across all 36 rules is the rest.

Catch the dangerous Postgres migration at the CI step

Technical Turtle — Wed, 22 Jul 2026 15:36:52 +0000

-- looks routine in review, locks your table in production:
CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Both of those statements hold a lock that blocks traffic while they scan or build against the table. On a small table you never notice. On a busy production table it is downtime, and a diff review does not catch it because the SQL looks fine.

pg-migration-guard is a free tool that reads the migration and flags these before they reach production:

$ pg-migration-guard V42__orders.sql

  WARN  IDX01  CREATE INDEX without CONCURRENTLY   [becomes a blocker on a large or busy table]
    Why:  Plain CREATE INDEX takes a lock that blocks all writes until the build finishes.
    Safe: CREATE INDEX CONCURRENTLY idx ON tbl (col);  -- outside a transaction

  WARN  NN01  SET NOT NULL scans the whole table under ACCESS EXCLUSIVE
    Safe: ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID; VALIDATE CONSTRAINT c; then SET NOT NULL;

Summary: 0 blocker, 2 warn, 0 advisory

Why migrations bite

Whether a Postgres migration is safe has almost nothing to do with intent and everything to do with locks. A migration is dangerous when it holds a strong lock while it scans or rewrites a large table. ACCESS EXCLUSIVE blocks reads and writes; SHARE blocks writes. The change that reads fine in review is the one that quietly takes that lock.

Put it in CI

The most useful place for this is the pull request, so a risky migration fails the build before anyone merges it. There is a GitHub Action:

name: migration-guard
on:
  pull_request:
    paths: ["db/migration/**.sql"]
jobs:
  guard:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.x"
      - uses: technical-turtle/pg-migration-guard@v1
        with:
          path: db/migration
          fail-on: blocker

Each finding shows up as an inline annotation on the diff, so the author sees exactly which line is the problem and what the safe rewrite is.

Or run it locally

pip install pg-migration-guard
pg-migration-guard db/migration/V42__add_index.sql

Human output by default, --format json for tooling, --fail-on {blocker,warn,advisory,none} for the exit code. Python 3.8+, no other dependencies.

What it catches

Ten of the most common migration foot-guns:

CREATE INDEX without CONCURRENTLY, and a CONCURRENTLY index op inside a transaction (which errors out)
SET NOT NULL directly (scans the whole table under a strong lock)
ADD CHECK and ADD FOREIGN KEY without NOT VALID
ADD COLUMN with a volatile DEFAULT, and ALTER COLUMN TYPE (both rewrite the table)
ADD PRIMARY KEY / UNIQUE directly
backfill ordering mistakes and unbatched UPDATE / DELETE
lock-taking DDL with no lock_timeout
destructive ops (DROP TABLE, TRUNCATE, DROP COLUMN, DROP SCHEMA)

Each finding names the hazard, links to the official Postgres docs, and prints the safe rewrite.

How it works

It parses the SQL with libpg_query, the same parser PostgreSQL itself uses, so a column named like a keyword, a schema-qualified table, or a dollar-quoted body is read correctly rather than misread by a regex. The parser is bundled as a small BSD-licensed binary and bound through the Python standard library, so there are no third-party dependencies and the tool never connects to a database or the network. It is MIT-licensed: https://github.com/technical-turtle/pg-migration-guard

Full coverage

pg-migration-guard is the free version of the Postgres Migration Safety Auditor. The paid tool checks 36 rules (not 12), each linked to an official source, and adds Liquibase XML / YAML / JSON changelog support, Postgres-version and table-size aware checks, a config file with per-rule mute controls, and a Claude Code skill: https://technical-turtle.com/postgres-migration-auditor

Run it against your migrations directory once, before your next deploy. The old migrations nobody has looked at since are usually where it finds something.

Claude Code hides your context % and usage limits. Here's how to see them.

Technical Turtle — Wed, 22 Jul 2026 11:22:02 +0000

before:  ~/code/myapp  main

after:   ctx 48% | 5h 14% ~1h20m | 7d 21% ~5d10h | opus-4.8   ~/code/myapp  main

One command, and your Claude Code statusline now shows how full your context is and how close you are to the Pro/Max limits, each with a reset countdown, in front of whatever bar you already run.

Claude Code hides both of those numbers until you slam into them: the context compacts out from under you, or you hit a usage limit mid-task, with no warning. And if you write hooks or plugins, you cannot read those numbers at all. A PreToolUse hook that wants to checkpoint before the context fills has no way to ask "how full are we". The data is not in the hook payload. Here is the fix, and the one place Claude Code will actually tell you these numbers.

Where the data actually lives

There is exactly one place Claude Code exposes this. Not the hooks API, not a plugin call, not an environment variable: the statusLine command. Every render, Claude Code pipes a JSON payload to your configured statusline program, and that payload contains the authoritative fields:

{
  "session_id": "abc123",
  "context_window": { "used_percentage": 47.2, "context_window_size": 200000 },
  "rate_limits": {
    "five_hour": { "used_percentage": 12, "resets_at": 1783359600 },
    "seven_day": { "used_percentage": 30, "resets_at": 1783368000 }
  },
  "model": { "id": "claude-opus-4-1" }
}

context_window.used_percentage is the real context fullness. rate_limits (present on Pro/Max OAuth) is your 5h and 7d usage, each with a Unix-epoch resets_at. This is the only place Claude Code shows them. So if you want the numbers, the statusline is where you have to stand.

The tool

cc-context-telemetry is a small statusLine wrapper that does two things with that payload.

One, it puts the numbers on your bar (the after line at the top). It prepends context used %, the 5h and 7d limits each with a reset countdown, and the current model to whatever your own statusline already prints. It wraps ANY statusline command, not a specific one, so you keep your existing bar and get the segment in front of it.

Two, it writes that telemetry to a per-session file your hooks can finally read. This is the part that is not obvious. Because the wrapper is standing in the one place Claude Code shows the data, it can persist it for everything else. Your hooks read it back with a one-liner.

Install

npm i -g cc-context-telemetry

Then set it as your statusLine in ~/.claude/settings.json:

{
  "statusLine": { "type": "command", "command": "cc-context-telemetry-statusline" }
}

Start a new session (settings changes take effect in a fresh one) and your bar is now ctx % | 5h % | 7d % | model with reset countdowns.

Already run a statusline you want to keep? Point the wrapper at it and the segment prepends on the same line:

{
  "statusLine": {
    "type": "command",
    "command": "cc-context-telemetry-statusline",
    "env": { "CCT_WRAP": "<your existing statusline command>" }
  }
}

Pick which segments show, and in what order, with CCT_SEGMENTS (default ctx,5h,7d,model):

CCT_SEGMENTS="ctx,5h,7d"          # drop the model
CCT_SEGMENTS="5h,7d"              # just the usage limits

For hook and plugin authors

This is the reason the project exists. In any hook, read the latest reading and act on it:

const { readTelemetry } = require('cc-context-telemetry');
const d = require('fs').readFileSync(0, 'utf8'); // hook stdin
const sessionId = JSON.parse(d).session_id;

const t = readTelemetry(sessionId);
if (t && t.fresh && t.contextPct >= 85) {
  // near the wall: checkpoint, summarize, or pause
}

readTelemetry(sessionId) returns the normalized reading (contextPct, fiveHourPct, sevenDayPct, fiveHourResetsAt, sevenDayResetsAt, model, and a fresh flag that rejects stale or non-finite values), or null if there is nothing yet. Now a plugin can throttle itself as context fills, warn before a usage window resets, or checkpoint long work before a compaction wipes the session. All of it was impossible without a bridge out of the statusline.

It has to be safe to run every render

Claude Code runs your statusLine on every render and kills that process each time to keep it bounded. That is a hostile environment for anything heavy. Two design choices keep it clean:

The per-render path is pure POSIX shell, with no Node on the hot path. It reads the payload, writes the raw file atomically, prints the segment. All JSON parsing happens later, on demand, only when a hook actually calls readTelemetry.
When you wrap an existing bar, the entry execs your command, so it BECOMES your statusline process rather than spawning a child. What Claude Code kills each render is the whole thing. Nothing outlives a render to orphan or pile up. (An earlier design that spawned Node every render did pile up under the render-kills; the exec-through wrapper does not.)

Node 18+ is needed only for the hooks API, never for the bar itself. It is cross-platform (CI-tested on Linux, macOS, and Windows via Git Bash), never throws, and never calls claude.

Accurate across your open sessions

The 5h and 7d numbers are account-wide, and Claude Code only refreshes them when a session makes an API call. So cc-context-telemetry shows the freshest reading across all your open sessions on the machine ("latest API call wins"): your sessions agree on one number instead of each showing its own stale one. Context % is per session and always current. (The reconciliation is per machine, so separate machines each track their own.)

Get it

Free and MIT-licensed, zero third-party dependencies.

It is one of the developer tools I build under Technical Turtle. If you also ship database changes and want to catch the migration that locks your production table before it does, the Postgres Migration Safety Auditor is the paid one.

If you build something with this telemetry in a hook, I would love to see it.