xataio

RUN BRANCHABLE POSTGRESQL DATABASES ON KUBERNETES! 🐘 Xataio XataGUI for managing Windows apps on Linux, handling Wine setup, virtual desktops, and DirectX/DXVK installations. xataio/xata t.co

現在の仕様をコードで表す──AIコーディングを支えるIRとグラフ ! 本記事は、dev.toに公開した"Code as a Model of the Current Spec: Declarative Models, IRs, and Graphs for AI Coding"を日本語向けに一部再構成したものです。 コーディングエージェントによる個々の変更が妥当でも、システム全体の整合性が保たれるとは限りません。独自の開発ハーネスや例えばDrizzleなどを使う中で、マイグレーションが積み重なる場面などに、この課題を感じています。 エージェントの能力は、この一年でも向上していると感じます。それでも、変更時に守るべき条件をリポジトリから読み取れるようにすることは、設計上の課題として残ります。 そこで去年から取り組んでいるのが、「現在の仕様をできる限りコードで表し、そこから導出した中間表現（IR）やグラフで変更を分析する設計」です。差分のテキストだけでなく、システムの構造と意味に基づいて変更を検討できる構成を目指しています。 データベースとインフラのモデル、検証の仕組みはすでに実装し、中規模以上のリポジトリで評価を進めています。UI・APIのモデルや、領域をまたぐ移行フェーズの組み合わせは、今後取り組む部分です。 本記事では「アカウントの無効化後に、新たな課金処理を開始しない」というルールを例に説明します。 個々の変更が妥当でも、移行中に問題は起きる アカウントの状態を表すis_deactivatedを、statusとdeactivated_atへ移行するとします。 既存データの変換規則は、次のように定めます。 旧値 status deactivated_at true disabled null（過去の無効化時刻が不明） false enabled null 新たな無効化では、その時刻を記録します。課金可否はstatusで判断します。deactivated_atがnullだから有効と解釈すると、過去に無効化したアカウントを誤って有効として扱ってしまいます。 移行中は、新旧のアプリケーションが混在します。旧アプリがis_deactivatedだけを更新し、新しい課金ワーカーがstatusだけを読むと、無効化を見落とす可能性があります。それぞれの実装が単独では妥当でも、組み合わせると業務ルールを破ります。 旧列の削除時には、キューに残っている旧ジョブも問題になります。エラー処理によっては安全に失敗しますが、欠けた情報を誤って解釈するかもしれません。また、外部へ送信した決済は、スキーマを戻すだけでは取り消せません。 変更後のスキーマだけでなく、アプリケーションやワークフローのバージョン、移行段階、待機中の処理を含めて整合性を確認する必要があります。 現在の仕様と、変更の意図を分ける こうした変更を扱うには、まずリポジトリの構造から、現在の業務概念、責務、制約、依存関係を読み取れることが重要です。 課金の判断は、汎用ユーティリティに埋め込むより、課金処理の境界に名前のあるポリシーとして置くほうが明確です。ワークフローは状態と遷移を明示し、認可は適用箇所と責務をそろえることで、振る舞いを追いやすくなります。 名前、型、モジュール境界、契約、制約、状態遷移で仕様を表します。コメントには、それらでは表しきれない設計の理由、例外の必要性、外部システムへの仮定を残します。 目指すのは、実装と別に仕様書を増やすことではなく、実装の構造自体にできる限り仕様を持たせることです。人の意図をすべてコードにできるわけではありませんが、エージェントが推測する部分は減らせます。 一方、現在の仕様だけでは、変更の意図を復元できません。 Drizzleは、宣言的なスキーマと差分マイグレーションを組み合わせています。generateはスキーマのスナップショットを比較してSQLを生成しますが、列の削除と追加がリネームなのか、別の概念への置き換えなのかは、構造の差分だけでは決まりません。必要に応じたリネームの確認は、その不足する情報を開発者が補うものです。 バックフィルにも、既存データをどう解釈するかという規則が必要です。先ほどの「無効化済みだが、時刻は不明」という変換も、その一つです。 こうしたリネームの意図、データの変換規則、切り替え条件を、ここでは「変更の意味」と呼びます。現在の振る舞いはコードに、最終状態から復元できない変更の意味はマイグレーションや保存した計画に残します。 履歴を保ちながら、現在を理解するために履歴全体を読み解く必要のない構成を目指します。 宣言を、分析できる形にする 各領域で明示したい情報は異なります。 領域 宣言する情報 読み取れるようにしたいこと UI 表示状態、入力規則、操作 利用者が何を理解し、実行できるか API 入出力、認可、エラー 操作が提供する契約 ワークフロー 状態、イベント、ガード、再試行、補償処理 許される実行経路と復旧経路 データベース スキーマ、制約、アクセス規則 保存する事実と、拒否する不整合 インフラ リソース、権限、接続関係 実行環境とその境界 これらの宣言は、相互に整合している必要があります。ボタンの無効化はAPIの認可を保証しません。APIの応答はキュー内の処理の完了を意味せず、データベースの制約だけでは外部への決済送信を防げません。 読みやすいソースコードは、エージェントの理解を助けます。IRは、その構造や性質をツールが分析するために使います。想定しているのは、次のような領域別のIRです。 データベースのオブジェクトと制約を表すスキーマIR インフラのリソースと依存関係を表す構成IR ワークフローの状態と遷移を表す状態機械IR APIの契約とUIの表示・操作を表すモデル データベースのロック、インフラのリソース置換、ワークフローの到達可能性には、それぞれ異なる分析が必要です。領域固有の意味を保ち、単一の汎用モデルへの変換で必要な情報を失わないようにします。 領域間は、コード上の契約、参照、依存関係を使って結び付けます。導出できない関係は、該当するコードの近くに明示します。別の仕様レジストリを前提とする構成ではありません。 なお、IRは状態空間そのものではありません。状態、制約、遷移を記述し、分析するための表現です。 業務ルールも、名前のあるポリシー、それを呼び出すガード、待機中の処理に対する無効化の意味を定めたプロパティテストなどで表します。これにより、関連する経路がポリシーを使っているか、変更で依存関係が壊れないかを調べられます。 データベース構造との一致は独立した検証手段で確認し、業務上の振る舞いは実行可能なルールとテストで確認します。ただし、どちらも、そのルールが人の意図に合っていることまでは保証しません。初期定義や実質的な変更には、業務上の判断が必要です。 既存の例として、Terraformには機械可読なplan・state JSONや、検証条件を記述する仕組みがあります。 依存グラフと操作DAGを区別する IRからは、用途の異なるグラフを構成できます。 依存グラフは、どのビューや制約が、どの定義に依存しているかを表します。相互参照する外部キーのような循環もあり得ます。 操作DAGは、操作の前後関係を表す有向非巡回グラフです。たとえば、列の追加、バックフィル、検証、読み取り先の切り替えという順序を表します。 表現 確認する内容 宣言的なソースコード 何が存在すべきで、どのルールが明示されているか 領域別IR どの構造や意味をツールが分析できるか 依存グラフ 変更がどこに影響するか 操作DAG どの操作を先に実行する必要があるか 検証・観測結果 何を実際に確認、または強制できたか 操作の順序が正しくても、業務上の意味が保たれるとは限りません。それでも、この区別によって、エージェントはSQLの列だけでなく、計画を説明し検証するための構造を扱えるようになります。 並行実行の正しさには、操作の契約が必要 課金の例では、まず「開始」を定義します。 ここでは、特定の課金処理に対する実行許可をデータベースに記録し、コミットした時点を開始とします。無効化は、アカウントの無効状態をコミットした時点です。 この定義では、無効化前に許可された処理が、その後に決済事業者へ送信されることはあります。無効化後の外部リクエストをすべて禁止するルールではありません。 単に有効状態を確認するだけでは、このルールを守れません。ワーカーが有効状態を読んだ後、別のトランザクションがアカウントを無効化し、その後にワーカーが課金を進める可能性があるためです。PostgreSQLのRead Committedにおける通常のSELECTでは、この競合を防げません（参考）。 対策の一例は、課金許可と無効化で同じアカウント行をロックする方式です。 課金側は、ロック取得後に有効状態を確認し、一意な操作キーを付けて実行許可を記録します。無効化側も同じ行をロックしてから状態を更新します。どちらもコミットまでロックを保持し、すべての許可経路でこの手順を守ります。 既存の操作キーによる再試行は、コミット済みの許可を別の経路から再開します。新規許可を作ったり、新規許可向けの適格性判定をやり直したりはしません。外部送信にはコミット済みの許可だけを使い、再試行と冪等性は別途定めます。 操作 事前条件 実行中に維持する条件 事後条件 新規の課金許可 アカウント行をロック済みで、有効状態である 状態確認から許可のコミットまでロックを保持 対象操作の実行許可が永続化されている アカウントの無効化 同じアカウント行をロック済みである 無効状態のコミットまでロックを保持 無効状態が続く間、新規許可が作成されない 外部への決済送信 コミット済みの許可がある 再試行でも同じ操作を識別 外部の結果を記録するか、結果不明として扱う 静的解析では、この手順を迂回する許可経路を調べます。実行トレースでは、無効化のコミット後、無効状態が続く間に新規許可がコミットされていないかを確認します。 トレースの検証には、単なるログの時刻ではなく、アカウント単位の順序を確実に復元できる記録が必要です。また、確認できるのは観測した実行であり、すべての実行可能性ではありません。 移行の各段階で、守る条件を明示する 冒頭のスキーマ変更でも、課金の契約を保つ必要があります。バックフィルの規則に加え、新旧アプリケーションの混在や、旧ジョブの実行を考慮します。 移行段階 確認する条件 新しい構造の追加（Expand） 既存の読み書きとの互換性を保つ バックフィルと同期 新旧の表現が同じ意味を保つ 切り替え（Cutover） 並行更新を含め、新しい読み取り先に正しいデータが届く 旧処理の完了・更新 待機中のジョブも正しく実行できる 古い構造の削除（Contract） 引き続きサポートする処理が旧構造を必要としない 実現方法には二重書き込み、トリガー、互換コードなどがあります。計画には、採用する仕組みの前提と実行順序を残します。 pgrollは、バージョン別のスキーマビューとトリガーで新旧スキーマの共存を支えます。Temporal Worker Versioningはワークフロー実行とワーカーのバージョンを扱い、Atlas migration lintはマイグレーションの安全性を分析します。 これらは参考になる先行例です。そのうえで、各領域の条件をどう結び付けるかが、ここで考えている課題です。 領域別のIRを、薄い参照解決層で結ぶ 構成は、宣言を領域別のモデルへ変換する部分と、コードに表れた関係でモデルを結ぶ部分に分けます。 各領域のアダプターは、宣言を解析して型付きIRに変換します。IRのノードにはソース位置を保持し、診断結果から修正対象へ戻れるようにします。参照先は、プログラム上のシンボルや成果物として解決します。 この図は、各領域のモデルと、検証すべき手順の関係を示しています。線で結んだこと自体が、実装による保証を意味するわけではありません。外部決済への点線には、外部作用に関する契約が必要です。 参照解決では、確定した参照、保守的に列挙した候補、明示的なライブラリ契約、未解決の参照を区別します。候補を確定した呼び出しとして扱わず、情報がないことを安全の根拠にしません。 依存グラフは影響分析に役立ちますが、時間的な正しさには追加の情報が必要です。トランザクション境界、コミット時点、同期対象、外部作用の順序を、関連コードの近くで表現します。 構成要素 責務 領域アダプター 領域固有の意味、ソース位置、解析上の限界を保持する リンカー 参照先と成果物のバージョンを解決する 領域アナライザー 領域内の性質と、その成立に必要な前提を確認する コンポーザー 各領域の保証がほかの領域の前提を満たすか、操作同士が干渉しないかを確認する 課金の例では、コンポーザーが、同じロックを使っているか、ロック中に状態を確認しているか、許可のコミット後に外部送信しているかを確認します。別の経路から状態変更や許可の作成ができるなら、その影響も調べます。 モデルから確認できない条件は「不明」とします。参照を結ぶリンカーは薄く保ち、並行実行の分析は別の責務として扱います。 変更計画に、旧成果物と実行環境の情報を含める 計画には、三つの入力が必要です。 変更後のモデル 引き続きサポートする旧成果物のモデル どの処理が実行・再開し得るかを示す観測情報やデプロイ上の取り決め 旧ワーカーのソースを削除しても、その成果物が実行可能なら依存関係は残ります。ワークフローのバージョンも含め、対象ビルドのIRとマニフェストを保存します。 一方、実行環境の観測情報は宣言と分け、取得元と取得時刻を保持します。 コンポーザーは、領域ごとの計画に、領域をまたぐ順序や干渉の条件を加えます。 旧列を削除する前には、少なくとも次の二点を確認します。 旧列に依存する処理を新たに受け付けないこと 受け付け済みの処理が、旧列への依存を残したまま実行・再開されないこと 対象には、実行中・遅延中のジョブ、再試行、デッドレターの再実行、停止中のワークフローも含まれます。 一案は、旧バージョンの処理を継続的に受け付けない仕組みを設け、旧処理を投入する側を停止し、既存の処理をすべて完了・廃止・更新してから削除することです。スケジュール、再実行、再開にも同じ制限を適用し、削除後も旧成果物が戻らないようにします。 実行前には、この制限が有効であることと、旧処理が残っていないことを確認します。キューが一時的に空であるだけでは、競合を防げません。実行環境がこの仕組みを強制できない場合、削除の前提は「不明」のままです。 検証結果を、確認した計画に結び付ける 検証結果には、対象の計画、ソースのリビジョン、入力モデルのダイジェスト、解析器のバージョンを含む検証条件を結び付けます。この一式のフィンガープリントによって、何を確認した結果なのかを識別します。 ただし、フィンガープリントは実行環境を固定しません。観測時刻を保持し、実行直前にも必要な前提を再確認します。観測結果は検証や判断の材料にしますが、正とするソースコードを自動的に書き換えるものではありません。 エージェントには、影響するソース位置、依存経路、候補となる操作、満たせなかった条件、未確認の項目を返します。 たとえばaccounts.is_deactivatedを削除すると、保存済みの旧ワーカーIRとの照合で参照先の欠落を検出できます。現在のチェックアウトから旧ソースが消えていても、確認できます。 以下は構想上の診断例であり、実際のハーネス出力ではありません。 操作・条件 根拠 解析結果 accounts.is_deactivatedの削除 保存済みのbilling-worker:v1モデルがsrc/workers/billing-v1.ts:42で参照 違反：サポート対象の読み取り処理と非互換 旧構造の削除段階へ進む 旧列に依存する処理の新規受付を止めていること 不明：制限が有効である証拠が不足 旧構造の削除段階へ進む 受付済みの旧処理が実行・再開されないこと 不明：旧処理の解消を確認する証拠が不足 この例では、削除を保留します。解析は違反と証拠不足を区別し、実行可否は破壊的操作のポリシーで決めます。例外を認める場合も、それは明示的な方針判断であり、検証済みという意味ではありません。 「検証済み」の範囲を明確にする データベースの制約は、適用範囲内の不正な状態を拒否します。ガードは、その経路を通る操作を制御します。テストは実行したケースを確認し、監視は計測できる事象を検出します。 これらを一括して「検証済み」と呼ぶと、保証の違いが見えなくなります。結果には、何を、どの証拠と前提に基づき、どの計画やコードのバージョンについて確認したかを示す必要があります。 また、IRだけを唯一の正解判定基準にはできません。生成と検証が同じ情報を落とす変換に依存していれば、両者が一致しても、同じ振る舞いを見落としている可能性があります。 実際にデータベースのハーネスでは、生成と検証の双方で情報が欠落し、収束確認が一致を報告する問題がありました。独立したPostgreSQLベースの構造検証を加えることで、この見落としを減らしました。 設定に存在していても、実際の制御につながっていない宣言も見つかりました。宣言と実装の対応を確認し、意図的に不具合を入れた実装で検出能力を試すことが有効でした。宣言やテストが存在するだけでは、仕組みが機能しているとは判断できません。 実際の挙動と宣言がずれた場合は、実装を直すのか、設計を改めるのか、範囲を限定した例外として扱うのかを判断します。本番の緊急修正が妥当な場合でも、観測した挙動をそのまま仕様に置き換えることはしません。 AIコーディングへの効果は、まだまだこれから データベースとインフラのモデル、範囲を明示した検証、計画に結び付けた検証結果は、すでに実装しています。一方、UI・APIのモデルや領域をまたぐ移行フェーズの組み合わせを含む全体構成は、今後の方向性です。これまでの実装や運用上の経験だけで、AIコーディングへの一般的な効果が示されたわけではありません。 仮説は、コードに意味を明示し、そこから導出したモデルで分析することで、エージェントが読み解く文脈や推測する前提を減らせるのではないか、というものです。 評価では、意図的なルール違反、互換性の落とし穴、領域をまたぐ依存関係を含む変更タスクを用意します。ソースコード、型検査、lint、テスト、既存のマイグレーション解析を備えた構成と、そこに領域横断の解析を加えた構成を比較します。 モデルとトークン数・実行時間などの上限をそろえて繰り返し実行し、開発・調整に使っていないリポジトリやタスクでも確認します。宣言構造の変更とツール追加の効果は分けて評価します。対象はIR単体ではなく、これらを含む分析システムです。 指標は、問題の検出率、誤検知、実装の正しさ、修正回数、保守コストです。解析器自身の検出件数を正解とせず、独立した期待結果に照らして判断します。 宣言の数を増やすことが目的ではありません。現在の仕様をコードから読み取れ、変更後の状態と移行過程の両方を分析できること。その仕組みが、エージェントによる振る舞いの理解と維持に役立つかを基準に、実装と評価を進めています。

agent ⭐ 1.1k An open-source AI assistant that answers questions and helps with PostgreSQL. 🔗 xataio/agent #opensource t.co

Your Agent's RENAME COLUMN Breaks Every Old Pod. Expand-Contract in 4 Steps - Book: Database Playbook - Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go - My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools - Me: xgabriel.com | GitHub --- You ask a coding agent to rename users.name to full_name. It updates the model, fixes every query, and writes a migration with one line in it: ALTER TABLE users RENAME COLUMN name TO full_name;. The diff is tidy and the tests pass, because the tests run against one version of the code. Production runs two. A Kubernetes Deployment rolls out by scaling the new ReplicaSet up while it scales the old one down, and both maxUnavailable and maxSurge default to 25%. For the length of the rollout, old Pods and new Pods serve traffic side by side. Your migration runs once, just before or somewhere inside that window. Every old Pod still alive after the rename sends SELECT name ... to a table that no longer has a name column. Why one RENAME breaks the rollout There are two separate failures packed into that one line. The first is the code-schema mismatch. Run the rename before the new code and the old Pods fail. Ship the code first and the new Pods fail until the migration lands. No ordering makes both versions happy, which is why the strong_migrations README puts it bluntly: "Renaming a column that's in use will cause errors in your application." The Squawk linter has a renaming-column rule for the same reason. The second is the lock. A rename touches no data; the Postgres docs say "there is no effect on the stored data." But ALTER TABLE takes an ACCESS EXCLUSIVE lock "unless explicitly noted," and that mode conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. If a long transaction is holding the table, the rename waits. While it waits, as Xata's write-up on the lock queue explains, every later statement that needs a lock on that table queues behind it. The table is blocked for reads and writes until the rename finishes or gives up. The fix is old. Danilo Sato described it as Parallel Change in 2014: expand the interface to support old and new, migrate the clients, then contract back to only the new. For a column rename in Postgres, that is four steps. !Old and new application servers connected to one database, with the old servers' cables cut During a rolling deploy, two versions of your code share one schema. The lab: one table, two app versions Start a throwaway Postgres 18 and seed a table: docker run -d --name pg -e POSTGRES_PASSWORD=pw postgres:18 # wait a few seconds for it to accept connections docker exec -i pg psql -U postgres <<'SQL' CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL ); INSERT INTO users (name) SELECT 'user ' || g FROM generate_series(1, 200000) g; SQL The "app" is two files, one per image version. Each does what a request handler would do: write a row, read it back. -- old_app.sql: what the old image runs INSERT INTO users (name) VALUES ('Old Pod Writer'); SELECT id, name FROM users ORDER BY id DESC LIMIT 1; -- new_app.sql: what the new image runs INSERT INTO users (full_name) VALUES ('New Pod Writer'); SELECT id, full_name FROM users ORDER BY id DESC LIMIT 1; Run either one with docker exec -i pg psql -U postgres < old_app.sql. Right now old_app.sql works and new_app.sql fails. Apply the agent's rename and the result flips: old_app.sql fails with a column-does-not-exist error. That flip is your rolling deploy. Rename the column back to name before you go on. You can also watch the lock queue. Open three terminals with docker exec -it pg psql -U postgres: 1. Terminal A: BEGIN; SELECT count(*) FROM users; and leave the transaction open. 2. Terminal B: ALTER TABLE users RENAME COLUMN name TO full_name; hangs, waiting for A. 3. Terminal C: SELECT id FROM users LIMIT 1; hangs too, queued behind B. Cancel B with Ctrl-C and C returns at once. Now run SET lock_timeout = '2s'; in B and try the rename again. B gives up after two seconds with a lock-timeout error instead of holding everyone hostage. lock_timeout "applies separately to each lock acquisition attempt," and it is the setting every migration below starts with. Roll back A when you're done. Step 1: expand without a long lock The first deploy is a migration only. No application code changes. It adds the new column and a trigger that keeps the two columns in sync, whichever one a writer touches. -- 001_expand.sql (deploy 1, app code unchanged) SET lock_timeout = '2s'; SET statement_timeout = '10s'; BEGIN; ALTER TABLE users ADD COLUMN full_name text; CREATE FUNCTION users_sync_name() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'INSERT' THEN NEW.full_name := coalesce(NEW.full_name, NEW.name); NEW.name := coalesce(NEW.name, NEW.full_name); ELSIF NEW.name IS DISTINCT FROM OLD.name THEN NEW.full_name := NEW.name; ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN NEW.name := NEW.full_name; END IF; RETURN NEW; END $$; CREATE TRIGGER users_sync_name BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION users_sync_name(); COMMIT; Apply it with docker exec -i pg psql -U postgres -v ON_ERROR_STOP=1 < 001_expand.sql. If the lock wait times out, psql exits with status 3, the transaction rolls back, and you run it again: two seconds lost, instead of every query on the table stuck behind your ALTER. Three details make this safe. Adding a column with no default gives existing rows NULL, and the ALTER TABLE page says "in neither case is a rewrite of the table required," so the exclusive lock is held only briefly. The trigger is a BEFORE row trigger, so it fills name before the NOT NULL check runs, which is what lets new_app.sql insert without naming the old column. And the UPDATE branch copies in one direction only, from whichever column changed, so a writer on either version never has its value overwritten by the other column. Run both app files now. Both succeed, and the last two rows have name and full_name filled. Old and new code can live together, which is the whole point of the expand step. Step 2: backfill in batches that give up fast The 200,000 seed rows still have full_name empty. Don't fix that with one UPDATE users SET full_name = name. On a big table that is one long transaction holding row locks on every row, and strong_migrations names the three keys to doing it safely: "batching, throttling, and running it outside a transaction." One batch, keyed on the primary key so each batch starts where the last one stopped: -- backfill.sql: one batch of 1,000 rows SET lock_timeout = '2s'; SET statement_timeout = '30s'; WITH batch AS ( SELECT id FROM users WHERE id > :last ORDER BY id LIMIT 1000 ), upd AS ( UPDATE users u SET full_name = u.name FROM batch b WHERE u.id = b.id AND u.full_name IS NULL ) SELECT coalesce(max(id), 0) FROM batch; The upd step has no RETURNING and nothing reads it, and it still runs: Postgres executes data-modifying statements in WITH "exactly once, and always to completion." The query prints the last id it handled, or 0 when nothing is left. A short loop drives it. psql runs in autocommit mode, so each batch is its own transaction: #!/usr/bin/env bash # backfill.sh: run after deploy 1, before deploy 2 last=0 while :; do next=$(docker exec -i pg psql -U postgres -qtA \ -v ON_ERROR_STOP=1 -v last="$last" < backfill.sql) \ || { echo "batch failed, retrying"; sleep 1; continue; } [ "$next" -eq 0 ] && break echo "backfilled through id $next" last=$next sleep 0.2 done Size the batch so each one finishes far below the 30-second statement_timeout. When a batch hits a timeout, it rolls back on its own and the loop retries the same range. Then check that nothing drifted: SELECT count(*) FROM users WHERE full_name IS DISTINCT FROM name; That count has to be zero before you go on. !An accordion that widens in the middle and narrows again at the end Expand, migrate, contract: the schema gets wider before it gets narrower. Steps 3 and 4: switch, then contract Step 3 is a normal code deploy (deploy 2): the image that runs new_app.sql, reading and writing only full_name. During that rollout, old Pods still write name and the trigger copies it across. New Pods write full_name and the trigger copies it back, so an old Pod reading name still sees rows the new Pods wrote. Until step 4, rolling back to the old image loses nothing. If the column should be NOT NULL, Postgres 18 lets you add that without a long lock. The ALTER TABLE docs allow NOT VALID on not-null constraints, and validation runs as a separate statement: SET lock_timeout = '2s'; SET statement_timeout = '15min'; ALTER TABLE users ADD CONSTRAINT users_full_name_nn NOT NULL full_name NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn; The statement_timeout is longer than the 10 seconds used elsewhere because VALIDATE scans the whole table. It only takes a SHARE UPDATE EXCLUSIVE lock while it does, so reads and writes keep going. Step 4 waits until no released code reads name and you are past the point where you would roll back. Then it removes the old column in its own deploy: -- 002_contract.sql (deploy 3, nothing reads name) SET lock_timeout = '2s'; SET statement_timeout = '10s'; BEGIN; DROP TRIGGER users_sync_name ON users; DROP FUNCTION users_sync_name(); ALTER TABLE users DROP COLUMN name; COMMIT; DROP COLUMN is quick too. Per the same page, Postgres "does not physically remove the column"; it makes the column invisible to SQL, and the space comes back on the next table rewrite. One catch: some ORMs cache the column list at boot. strong_migrations notes that Active Record "caches database columns at runtime," so on Rails you add the column to ignored_columns and deploy that before you run the drop. The rule to give your agent The agent wrote the one-liner because nothing told it about the second version of the code. Tell it. Claude Code loads path-scoped rules from .claude/rules/ when it works on matching files, so this file only shows up when the agent touches a migration: --- paths: - "migrations/**/*.sql" --- # Postgres migration rules - Never RENAME COLUMN or RENAME TABLE on a table the running app reads. Propose expand-contract instead. - Expand migration: ADD COLUMN (nullable, no volatile default) plus a BEFORE INSERT OR UPDATE trigger that keeps the old and new columns in sync. - Backfill is a separate batched script keyed on the primary key, never part of the migration. - Every migration file starts with SET lock_timeout and SET statement_timeout. - DROP COLUMN goes in its own migration, in a later deploy, after no released code reads the column. - When asked for a rename, write out the 4-step plan and put only step 1 in this change. Other coding agents in the repo read AGENTS.md, so copy the bullets there too. Claude Code keeps loading .claude/rules/ either way, and the same docs page covers when it reads AGENTS.md at all. A rule file is advice. The docs say so directly: Claude "treats them as context, not enforced configuration." So back it with a check that does not care who wrote the SQL. Run squawk 'migrations/*.sql' in CI, or the squawk-action on pull requests. Its renaming-column rule catches the one-liner, and require-lock-timeout and require-statement-timeout catch a migration that forgot its timeouts. It will also flag the drops in step 4, which is fine: that is the one migration a human should read before it ships. If you want the expand-contract bookkeeping automated, pgroll does the add-column, backfill and sync-trigger work for you and serves old and new schema versions through views at the same time. Knowing the four steps by hand is still what lets you review what it, or your agent, is about to run. --- If this was useful The four steps here are what a schema change costs once a store has live traffic and more than one version of your code talking to it. I wrote the Database Playbook for the decision that comes before all of this: which store to pick for a system, knowing you will be changing its schema under real load for years. If this post made you look harder at your own migrations, the book is the wider version of that look. 

pgroll serves the old and new Postgres schema side by side during a migration, so rolling back is flipping a switch, not writing a second migration under pressure. Single Go binary, 6.6k stars. xataio/pgroll

Code as a Model of the Current Spec: Declarative Models, IRs, and Graphs for AI Coding --- title: "Code as a Model of the Current Spec: Declarative Models, IRs, and Graphs for AI Coding" published: true description: "Make system intent readable in code, then use domain-specific IRs and graphs to analyze cross-domain changes and migration safety for AI coding." tags: harnessengineering, agenticai, dsl, architecture cover_image: dev-to-uploads.s3.us-east-2.amazonaws.com --- I am putting this design direction into practice and evaluating it in medium-sized and larger repositories. Database and infrastructure models, along with verification mechanisms, are already implemented in our tooling. UI/API models and cross-domain composition of migration phases are the next direction. I currently use a custom harness for development, including database changes. I also use Drizzle in some projects. Across both approaches, I keep coming back to the same friction: a coding agent can produce a locally reasonable change without giving enough attention to the shape of the system as a whole. Accumulating migrations is one place where that becomes visible. My impression is that newer agents are much better at this than they were a year ago. But the architectural question remains. How do we make the repository express the system well enough that an agent can understand what a change must preserve? My target is simple to state: Make code the best available model of the current specification. Derive intermediate representations from it so changes can be analyzed, rather than judged from text alone. That applies to UI, API, workflow, database, and infrastructure together. Consider a rule as simple as “after account deactivation, no new billing operation may begin.” Preserving it during a schema change can require agreement across all five domains. I will use that example throughout. The repository should explain the system as it is When an agent opens a repository, it should be able to discover the current business concepts, responsibilities, constraints, and dependencies from its structure. A billing decision hidden inside a generic utility function is harder to understand than a named policy at the billing boundary. A workflow assembled from scattered callbacks is harder to inspect than explicit states and transitions. A permission enforced inconsistently across handlers is harder to reason about than a clear authorization boundary. The ideal is not to maintain another specification document alongside the implementation. It is to make the implementation's structure carry as much of the specification as possible. Names, types, module boundaries, contracts, constraints, and transitions all contribute. Comments should explain the reasons that those structures cannot express: why a boundary exists, why an exception is necessary, or which external assumption a decision relies on. This does not mean code can recover every human intention. It means we should reduce the amount of intention that an agent has to guess. Current state and change history have different jobs Drizzle already combines a declarative schema with incremental migrations. Its generate command builds a schema snapshot and compares it with the previous snapshot to generate SQL. Declarative state and migrations are therefore compatible, not competing approaches. See the Drizzle documentation. The current schema tells us what structure we want. A change also needs to explain how existing data should reach that structure. Two snapshots cannot fully determine that meaning. A removed column and an added column might represent a rename, a replacement, or a change in the underlying concept. Drizzle’s documented generation flow prompts the developer for renames when necessary. The prompt supplies information the structural diff cannot decide. A backfill similarly requires a rule for interpreting existing rows. I call this change meaning: information that cannot be recovered from the final state, including rename intent, backfill rules, and cutover conditions. It belongs in the change artifact—the migration or saved plan—while current behavior belongs in the source code. The repository should make the current specification legible, while the change should retain the meaning that cannot be reconstructed from the final state. History still matters. It should not be the only way to understand the present. Declare the system across its boundaries Declarative code makes important relationships available for inspection. Each domain has different things to declare. | Domain | What to express | What should become legible | | --- | --- | --- | | UI | View states, input rules, available actions | What users can understand and do | | API | Inputs, outputs, authorization, errors | The contract an operation offers | | Workflow | States, events, guards, retries, compensation | Allowed execution and recovery paths | | Database | Schema, constraints, access rules | Stored facts and rejected inconsistencies | | Infrastructure | Resources, permissions, connections | The execution environment and its boundaries | These declarations must agree. A disabled button does not enforce authorization. An API response does not prove a queued operation has completed. A database constraint does not establish that an external payment request was never sent. The architectural value comes from expressing the relationships between these boundaries clearly enough to inspect them. IR makes the declarations analyzable Readable source helps an agent understand the system. An intermediate representation, or IR, helps tools compute properties of that system. For this design, I would derive domain-specific IRs from the source: - A schema IR for database objects and constraints. - A configuration IR for infrastructure resources and dependencies. - A machine IR for workflow states and transitions. - Contract and view models for API and UI relationships. I would keep each domain's semantics intact. Database locks, infrastructure replacement, and workflow reachability need different analyses. Flattening them into one universal schema risks losing the details that make those analyses useful. Cross-domain analysis can follow contracts, references, and dependencies already expressed in code. Where a relationship cannot be derived, it needs an explicit representation close to the relevant code. It does not require a separate specification registry. An IR is not the state space itself. It is a representation from which we can describe and analyze states, constraints, and transitions. The business rule needs an explicit expression in code as well. For billing, that might be a named eligibility policy, a guard that invokes it, and a property test defining what deactivation means for pending work. An analyzer can then check whether relevant execution paths use that policy and whether a change invalidates its dependencies. An independent structural oracle checks fidelity to the database; the executable rule and its tests provide a different criterion for business behavior. Neither establishes that the chosen rule matches human intent: its initial definition and substantive changes still require domain judgment. Terraform provides an existing example of machine-readable change information: plan and state JSON. It also supports validation conditions. Graphs expose dependencies; DAGs constrain change order In a database, a schema IR can support a dependency graph: which views, constraints, or other objects depend on which definitions? A migration planner can also construct a directed acyclic graph of operations. Adding a column may precede a backfill; the backfill may precede validation; validation may precede switching readers. These are different graphs. Database dependencies can contain cycles, such as two tables with foreign keys referencing each other. An executable operation DAG represents precedence constraints, and a valid ordering still does not prove that the migration preserves business meaning. The useful progression is: | Representation | Question it helps answer | | --- | --- | | Declarative source | What should exist, and what rules are visible? | | Domain IR | What structure and semantics can tools inspect? | | Dependency graph | What depends on this change? | | Operation DAG | What must happen before what? | | Checks and observations | Which properties were actually tested or enforced? | This gives an agent more than a sequence of SQL statements. It gives the agent a basis for explaining and checking a plan. The difficult case is the transition Return to the billing rule: after an account is deactivated, no new billing operation may begin. For this example, begin means committing a durable authorization to perform a particular billing operation in the database. Deactivation means committing the account's disabled state. An operation authorized before deactivation may still be sent to the payment provider afterward. That is a deliberate rule, not a guarantee that no external request occurs after deactivation. A guard alone is insufficient. A worker can read an enabled account, another transaction can deactivate it, and the worker can then send a payment request. A normal PostgreSQL Read Committed SELECT does not protect that interval; see the transaction isolation documentation. A possible protocol serializes both operations on the same account row. In a billing transaction, acquire the row lock, read eligibility, and insert a durable authorization with a unique operation key before committing. The deactivation transaction takes the same lock before changing eligibility. All authorization paths must follow this protocol. A retry with an existing operation key resumes the committed authorization through a separate path, without creating a new one or reapplying the new-authorization eligibility condition. It must not create a fresh authorization after deactivation. External dispatch occurs only for committed authorizations and needs its own retry and idempotency contract. This identifies the properties an analyzer would need to model: | Operation | Preconditions | Conditions maintained during execution | Postconditions | | --- | --- | --- | --- | | Create a new billing authorization | Account lock acquired; account enabled | Lock held through eligibility check and authorization commit | Durable authorization exists for this operation | | Deactivate account | Same account lock acquired | Lock held through disabling commit | No later authorization may be created while disabled | | Dispatch payment | Committed authorization exists | Retry identifies the same operation | External result recorded, or outcome remains unknown | A trace checker can look for an authorization commit after a deactivation commit, while the account remains disabled. It needs records that reliably reconstruct order for that account, not merely application-log timestamps; its finding applies to observed executions, not all possible executions. Static analysis can flag an authorization path that bypasses the shared protocol. Now migrate the boolean is_deactivated to a representation that separates account status from deactivation time: status and deactivated_at. A true legacy value becomes status = disabled with deactivated_at = null, meaning disabled with an unknown historical time. A false value becomes status = enabled with no deactivation time. New deactivations record a timestamp. Eligibility depends on status; treating a null timestamp as enabled would incorrectly re-enable legacy accounts. These backfill rules belong in the change artifact. During the transition, an old application may write only is_deactivated while a new billing worker reads only status. Both versions can be individually plausible, yet their combination can bill a deactivated account. Later, removing the old column may break old jobs still waiting in the queue. Depending on error handling, they might fail safely or interpret missing information incorrectly. Sending a payment request introduces an external side effect that cannot be undone merely by rolling back the schema. The plan therefore needs to consider application versions, workflow versions, schema phases, and queued work together. | Phase | Example condition to check | | --- | --- | | Expand | Existing readers and writers remain compatible | | Backfill and synchronization | Both representations preserve the intended meaning | | Cutover | New readers receive correct data, including concurrent updates | | Drain or upgrade old work | Queued jobs retain valid execution semantics | | Contract | No remaining supported execution requires the removed structure | The exact mechanism might be dual writes, a trigger, compatibility code, or another protocol. The important point is to make its assumptions and ordering visible. Existing tools address parts of this problem. pgroll uses versioned schema views and triggers to support concurrent schema versions during PostgreSQL migrations. Temporal Worker Versioning addresses the relationship between workflow executions and worker versions. Atlas migration lint analyzes migration safety. These are useful precedents; connecting the obligations across domains remains the architectural task. A concrete architecture: domain compilers and a thin linking layer The architecture follows two paths: compile declarations into domain models, then connect those models through relationships already expressed in code. Each domain adapter parses and resolves its declarations into a typed IR. IR nodes retain source locations so a diagnostic can point back to the code an agent needs to change. References resolve to program symbols and artifacts. flowchart TD U["View and Contract IR: deactivateAccount"] --> D["Deactivate: lock account, update status, commit"] N["Machine IR: create new authorization"] --> C["Eligibility check under the same account lock"] C --> A["Schema IR: committed billing authorization"] D --> S["Schema IR: account status and deactivation time"] S -->|read under lock| C A --> P["Machine IR: dispatch committed authorization"] R["Retry: existing operation key"] -->|resume existing authorization| P F["Config IR: worker and queue"] -->|hosts| P P -.-> X["External payment service"] The graph connects domain-owned models while making the protocol visible: new authorization checks eligibility under the same account lock used by deactivation, and dispatch consumes a committed authorization. A retry resumes that authorization rather than creating another. These edges identify obligations for the analyzers; drawing them does not prove they are enforced. The dotted payment edge requires an external-effect contract. Reference resolution distinguishes definite edges, conservative sets of possible targets, explicit library contracts, and unresolved references. Possible edges are useful for conservative analysis but must not be presented as confirmed calls. Missing information is not evidence of safety. The graph supports impact analysis. Temporal correctness needs an additional contract layer, close to the relevant code: transaction boundaries, commit points, synchronization resources, and the ordering of external effects. | Component | Responsibility | | --- | --- | | Domain adapter | Preserve domain semantics, source locations, and analysis limits | | Linker | Resolve references and artifact versions | | Domain analyzer | Evaluate local properties and report the assumptions they require | | Composer | Connect guarantees to assumptions and check interference between operations | For billing, the composer must establish that both paths use the same account lock, that eligibility is read under that lock, and that authorization commits before external dispatch. If another path can change eligibility or create authorization outside the protocol, the local guarantee does not compose. Where the available model cannot establish these facts, the result is unknown. Thin reference linking remains thin; concurrency analysis is a separate responsibility. From IRs to a change plan and runtime evidence Planning needs three distinct inputs: the candidate model, models of old artifacts still supported, and evidence or deployment contracts identifying what can run or resume. Retain generated IRs and manifests for supported builds, including workflow versions. Deleting an old worker from the current checkout does not erase its dependencies while that artifact can still execute. Observations remain separate from declarations and carry their origin and acquisition time. These generated models retain the semantics of supported builds. flowchart TD P["Plan: operation DAG and phase conditions"] --> V["Verify: compatibility, ordering and gaps"] V --> G["Gate: bind evidence to plan and recheck prerequisites"] G --> E["Execute: domain operations"] E --> O["Observe: actual state and execution traces"] O --> A["Adjudicate: repair, accept or bound an exception"] A -.-> P The composer adds cross-domain precedence and interference conditions to local plans. Before removing the old field, two conditions must hold: no new work requiring it can be admitted, and no accepted work can execute or resume with that dependency. Depending on the runtime, this includes running jobs, delayed jobs, retries, dead-letter replay, and suspended workflows. One proposed protocol installs a durable version-admission fence, stops old producers, and retires or upgrades every old dependent execution. Scheduling, replay, and resume paths must obey the same fence. The fence remains in force through contract and prevents old artifacts from returning afterward. A gate checks the fence and retirement evidence before removal; rechecking queue depth alone cannot close the race. If the runtime cannot enforce this protocol, the prerequisite remains unknown. Evidence needs a reproducible verification identity: the exact plan, source revision, input-model digests, and verification conditions, including the analyzer version. A fingerprint over that bundle binds a result to what was actually checked. It does not freeze the environment: observations also carry their acquisition time, and execution gates recheck relevant runtime assumptions. Observations feed verification and adjudication; they do not automatically rewrite the authoritative source. An agent-facing interface would expose affected source locations, dependency paths, candidate operations, failed conditions, and unknowns. That gives the agent actionable evidence for a code change without asking it to reconstruct the entire architecture from logs. For example, a declaration change that removes accounts.is_deactivated would remove that field from the candidate Schema IR. Resolving the retained IR of the supported v1 worker against the candidate schema would produce a missing dependency—even if its source was deleted from the checkout. The contract-phase check separately needs evidence for admission fencing and retirement of old dependent executions. An illustrative diagnostic can be presented as a table. This is a proposed result, not captured output from the harness. | Proposed operation | Evidence or condition | Analysis result | | --- | --- | --- | | Remove accounts.is_deactivated | Retained billing-worker:v1 model reads the field at src/workers/billing-v1.ts:42 | Violated: incompatible supported reader | | Enter contract phase | New admission of old dependent work is fenced | Unknown: enforcement evidence missing | | Enter contract phase | Accepted old dependent work cannot execute or resume | Unknown: retirement evidence missing | Execution decision: block removal. The analysis distinguishes violations from missing evidence; the destructive-operation policy determines the decision. An exception would be an explicit policy decision, not a verified result. Verification needs a scope A database constraint, a workflow guard, a test, and an alert do different jobs. A constraint rejects invalid states within its scope. A guard controls operations that pass through it. Tests check the cases they execute. Monitoring detects what its instrumentation can observe. Calling all of these “verified” hides important differences. A result should state what property was checked, against which evidence, under which assumptions, and for which plan or code version. An IR also cannot serve as its own sole oracle. If generation and validation share the same lossy transformation, they can agree while omitting the same behavior. We encountered this in the database harness: a convergence check could report agreement when generation and verification symmetrically dropped information. A separate PostgreSQL-backed structural oracle helped reduce that blind spot. The lesson was specific: agreement after a shared transformation was weaker evidence than it appeared. We also found declarations that existed in configuration but were not wired into enforcement. A declared policy can give an agent false confidence when its implementation does nothing. Checks for declaration-to-enforcement mismatches, together with deliberately faulty implementations, test whether the mechanism is actually connected. Neither an impressive declaration nor a linked test is enough on its own. When actual behavior diverges from the declared model, the response requires judgment: restore the intended behavior, accept a corrected design, or record a bounded exception. A production hotfix may be valid, but observed behavior should not silently redefine the specification. The claim about AI is still a hypothesis The database and infrastructure models, scoped verification, and plan-bound evidence have concrete implementations in our tooling. The full architecture above—including UI/API models and cross-domain phase composition—is the direction I am working toward. Existing mechanisms and operational lessons do not yet establish a general benefit for AI coding. My hypothesis is that code with explicit semantics, combined with derived analysis, reduces the context an agent must reconstruct and the assumptions it must invent. Use matched change tasks with seeded violations, compatibility traps, and cross-domain dependencies. Compare a strong baseline—source, type checking, lint, tests, and existing migration analysis—with the same setup plus derived cross-domain analysis. Keep the model and budget fixed, repeat runs, and use held-out repositories and tasks. Separate changes to declaration structure from added tooling; this evaluates the analysis system, not IR in isolation. Measure detection, false positives, correct implementations, repair iterations, and maintenance cost. Judge both groups against independent expected outcomes, rather than the analyzer's own finding count. Counting declarations alone would miss the point. The model must help an agent understand and preserve behavior. Make the current specification readable in code. Make its structure analyzable through IRs and graphs. Then use that analysis to check both the destination and the path of a change. That is the design direction I want for large systems built with coding agents.

Migrating a Large Table Without Locking Production GoCardless once took around 15 seconds of unexpected API downtime from a planned migration. The tables being changed were empty. The statement was fast. It didn't matter, because the migration needed a lock on a heavily used parent table, a long-running read was already holding a conflicting lock, and every API query that arrived after that queued up behind the migration until clients timed out. They wrote it up in Zero-downtime Postgres migrations: the hard parts. That story is the best correction I know to the way this topic usually gets framed. "Tables too big to lock" suggests the danger is size: a billion rows, a rewrite that takes forty minutes. Size is a real problem, but it's the second one. The first is that any ALTER TABLE that has to wait for its lock can freeze a table of any size, including an empty one, for as long as it waits. So there are two separate things that go wrong, and they need separate fixes: 1. The wait. Your DDL can't get its lock, and it blocks everyone queued behind it while it waits. Table size is irrelevant. 2. The hold. Your DDL gets the lock and then does work proportional to table size while holding it: a full rewrite, or a full scan to prove a constraint. This is where size matters. A safe migration handles both, in that order. The target is PostgreSQL, and every lock level below comes straight from the official docs. The lock queue, precisely Postgres has eight table-level lock modes. The one that matters most here is ACCESS EXCLUSIVE, and the explicit locking docs are blunt about it: it conflicts with every other mode, and "only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement." It's also the default for ALTER TABLE. The ALTER TABLE docs say "an ACCESS EXCLUSIVE lock is acquired unless explicitly noted," and most subcommands aren't noted. Here's the part people miss. A plain SELECT takes ACCESS SHARE, which only conflicts with ACCESS EXCLUSIVE. So you'd think a stream of reads can't be blocked by anything except a DDL that's actually holding its lock. Wrong. When a backend requests a lock, Postgres checks it against locks that are held and against locks already waiting in the queue. A waiting ACCESS EXCLUSIVE request conflicts with everything, so new readers line up behind it. You can reproduce it with three psql sessions: -- Session A: an innocent analytics query, or just a forgotten open transaction BEGIN; SELECT count(*) FROM orders; -- holds ACCESS SHARE on orders until COMMIT/ROLLBACK -- (transaction left open) -- Session B: the migration ALTER TABLE orders ADD COLUMN note text; -- needs ACCESS EXCLUSIVE, waits for A -- Session C: normal app traffic SELECT * FROM orders WHERE id = 42; -- ACCESS SHARE, but queues behind B's waiting request Session C doesn't conflict with Session A at all. It's stuck because of B, and B is stuck because of A. Every request your app sends to orders joins C in the line. The ALTER TABLE itself is a catalog-only change that would finish in milliseconds. It never gets the chance. If you've read what SELECT ... FOR UPDATE actually blocks, this is the flip side. Row locks almost never block plain reads. A queued table lock blocks all of them. !Diagram of the Postgres lock queue on orders: Session A holds ACCESS SHARE, Session B's ALTER TABLE waits for ACCESS EXCLUSIVE, and every later SELECT, INSERT and UPDATE is blocked behind B even though it does not conflict with A Rule zero: never wait for a lock without a deadline The fix for the wait problem is one setting. lock_timeout aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock." The default is zero, which means wait forever, which means your migration is allowed to hold the whole table hostage for as long as the slowest open transaction lives. Set it on every migration session: SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN note text; -- ERROR: canceling statement due to lock timeout Failing is the point. A migration that errors out after two seconds cost you two seconds of queued traffic. A migration that waits until an idle-in-transaction session finally closes costs you however long that is. Xata's write-up on schema changes and the lock queue puts it as "values of less than 2 seconds are common." Pick the number your p99 latency budget can absorb, not a round one. Then retry, because the lock will usually be free a moment later. Nikolay Samokhvalov's lock_timeout and retries post on postgres.ai uses a very short timeout plus capped exponential backoff with jitter, all inside a DO block. A trimmed version of that pattern: DO $$ DECLARE attempt int; BEGIN PERFORM set_config('lock_timeout', '100ms', false); FOR attempt IN 1..20 LOOP BEGIN ALTER TABLE orders ADD COLUMN note text; RETURN; -- success EXCEPTION WHEN lock_not_available THEN -- full jitter: sleep a random slice of an exponentially growing window, capped at 10s PERFORM pg_sleep(random() * least(10, 0.01 * 2 ^ attempt)); END; END LOOP; RAISE EXCEPTION 'could not acquire lock on orders after 20 attempts'; END $$; The lock_not_available condition is what lock_timeout raises, so this catches exactly the "couldn't get the lock" failure and nothing else. Most migration frameworks have their own version: GitLab's migration helpers wrap risky statements in with_lock_retries, and their avoiding downtime guide notes that "without lock retries, this lock can time out and require a manual retry." Two related settings are worth knowing. idle_in_transaction_session_timeout kills sessions that sit idle inside an open transaction, which is the classic Session A. And since Postgres 17 there's transaction_timeout, which caps total transaction duration. The docs recommend against setting transaction_timeout globally in postgresql.conf, so treat it as a per-role or per-session tool. When a migration keeps timing out, find out who's in the way instead of raising the timeout: SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, now() - xact_start AS xact_age, left(query, 80) AS query FROM pg_stat_activity WHERE datname = current_database() AND xact_start IS NOT NULL ORDER BY xact_start; The oldest xact_age at the top is usually your answer. Very often it's a BI tool, a psql session someone left open, or a worker that begins a transaction and then makes an HTTP call. Know which DDL is actually cheap With the wait under control, the question becomes what happens once you have the lock. A lot of ALTER TABLE forms take ACCESS EXCLUSIVE but only touch the catalog, so they hold it for milliseconds. Others rewrite the entire table and every index while holding it. The difference is invisible in the SQL. Here's the map, from the current (Postgres 18) docs: | Operation | Lock | Work under the lock | |---|---|---| | ADD COLUMN nullable, no default | ACCESS EXCLUSIVE | Catalog only | | ADD COLUMN ... DEFAULT <non-volatile> | ACCESS EXCLUSIVE | Catalog only since Postgres 11 | | ADD COLUMN ... DEFAULT clock_timestamp() (volatile) | ACCESS EXCLUSIVE | Full table and index rewrite | | DROP COLUMN | ACCESS EXCLUSIVE | Catalog only, space not reclaimed | | ALTER COLUMN TYPE varchar(n) to larger n, or to text | ACCESS EXCLUSIVE | No rewrite (binary coercible) | | ALTER COLUMN TYPE int to bigint | ACCESS EXCLUSIVE | Full table and index rewrite | | SET NOT NULL | ACCESS EXCLUSIVE | Full scan, unless a valid CHECK proves it | | ADD CONSTRAINT ... CHECK | ACCESS EXCLUSIVE | Full scan (skip with NOT VALID) | | ADD FOREIGN KEY | SHARE ROW EXCLUSIVE on both tables | Full scan (skip with NOT VALID) | | VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE | Full scan, but reads and writes continue | | CREATE INDEX | SHARE | Full build, writes blocked | | CREATE INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE | Two scans, reads and writes continue | | RENAME COLUMN | ACCESS EXCLUSIVE | Catalog only, but breaks every query using the old name | A few rows deserve a sentence each. Fast defaults. Before Postgres 11, ADD COLUMN ... DEFAULT 'x' rewrote every row. Since 11, the default is evaluated once and stored in pg_attribute.attmissingval; existing rows get it on read. The docs describe this as "very fast even on large tables." The catch is the word non-volatile. now() is fine because it's stable within a transaction. clock_timestamp() or gen_random_uuid() as a default is volatile, and the docs say that "will cause the entire table and its indexes to be rewritten." Same for adding a stored generated column or an identity column. Type changes. The docs spell out the exception: no rewrite when the old type is "binary coercible to the new type" and USING doesn't change the contents. Widening varchar(50) to varchar(200) has been rewrite-free since Postgres 9.2. int to bigint is not binary coercible, so it rewrites. On a big table that's the forty-minute lock from the top of this post, and it gets its own section below. DROP COLUMN is cheap for the database and dangerous for the app. The column becomes invisible immediately; the bytes stay on disk until rows get rewritten. The real risk is any running app instance that still selects that column by name, which is why GitLab's process spreads a column drop across three releases, starting with ignore_column in the model. Foreign keys got cheaper, not free. GoCardless's outage involved adding a foreign key, which back then took ACCESS EXCLUSIVE on both tables. Postgres 9.5 reduced the lock level, and today ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on the table and on the referenced table. That still conflicts with ROW EXCLUSIVE, the lock every INSERT, UPDATE, and DELETE takes. So a queued FK addition won't freeze reads anymore, but it will freeze writes on both tables. Still needs lock_timeout. Constraints: add now, prove later Adding a CHECK or foreign key normally scans the whole table under the lock to prove existing rows comply. NOT VALID splits that in two: SET lock_timeout = '2s'; -- Step 1: brief lock, no scan. New writes are checked from this moment on. ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID; -- Step 2: the scan, under a lock that allows reads and writes. ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk; The docs explain why step 2 can be relaxed: "the validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock." For a foreign key, it also takes ROW SHARE on the referenced table. There's a bonus the docs point out: if the table already has violating rows, the NOT VALID constraint stops new ones while you clean up the old ones, and VALIDATE succeeds when you're done. SET NOT NULL is the one that trips people up, because it has no NOT VALID clause of its own before Postgres 18. The workaround uses a detail that landed in Postgres 12: SET NOT NULL skips its full scan "if a valid CHECK constraint exists ... which proves no NULL can exist." ALTER TABLE orders ADD CONSTRAINT orders_region_not_null CHECK (region IS NOT NULL) NOT VALID; -- brief ACCESS EXCLUSIVE, no scan ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null; -- scan, SHARE UPDATE EXCLUSIVE ALTER TABLE orders ALTER COLUMN region SET NOT NULL; -- brief ACCESS EXCLUSIVE, scan skipped ALTER TABLE orders DROP CONSTRAINT orders_region_not_null; -- redundant now On Postgres 18 you can skip the CHECK detour. The 18 release notes add NOT VALID for not-null constraints, so this works directly: ALTER TABLE orders ADD CONSTRAINT orders_region_not_null NOT NULL region NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null; Indexes: CONCURRENTLY, and what it doesn't tell you A plain CREATE INDEX takes a SHARE lock. Reads keep working; every INSERT, UPDATE, and DELETE blocks until the build finishes. On a large table that's the whole outage, just quieter. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, which allows writes. The CREATE INDEX docs list the price. It does "two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate." Three consequences bite in practice: - It can't run in a transaction block. Most migration frameworks wrap each migration in a transaction by default, so the statement fails. Rails has disable_ddl_transaction!, Django has atomic = False on the migration class. You need the escape hatch, and you need to know you're now running without a rollback safety net. - It waits on old transactions too. The long-running transaction that ruins your ALTER TABLE will also stall a concurrent build. The build doesn't block your traffic while it waits, but it won't finish either. - Failure leaves debris. A deadlock or uniqueness violation mid-build leaves an index marked INVALID. The docs say it's "ignored for querying purposes" but "will still consume update overhead." You have to drop it and try again (or REINDEX INDEX CONCURRENTLY it). A migration that retries CREATE INDEX CONCURRENTLY IF NOT EXISTS after a failure will happily skip because the invalid index exists. Check pg_index.indisvalid. Need a unique constraint or a new primary key? Build the unique index concurrently, then attach it: CREATE UNIQUE INDEX CONCURRENTLY orders_external_ref_key ON orders (external_ref); ALTER TABLE orders ADD CONSTRAINT orders_external_ref_key UNIQUE USING INDEX orders_external_ref_key; The USING INDEX form is fast, with one exception worth reading twice: for a PRIMARY KEY, if the columns aren't already marked NOT NULL, the command runs SET NOT NULL on them, which "requires a full table scan." Do the NOT NULL dance from the previous section first. When the table really has to be rewritten: expand, backfill, swap Some changes have no catalog-only shortcut. The canonical one is an int primary key approaching 2,147,483,647. ALTER COLUMN id TYPE bigint rewrites the table and every index under ACCESS EXCLUSIVE, for as long as that takes. On a table big enough to be running out of ids, that's not a maintenance window, that's an outage. GitLab has a documented process for exactly this, and it spans four releases: add the new column with a sync trigger and start a batched background backfill, swap the columns in a post-deployment migration, drop the trigger and the old column, then remove the ignore rules. Crunchy Data published a pure-SQL version of the same idea in January 2026. Here's the shape, on a hypothetical orders table with id serial PRIMARY KEY and no inbound foreign keys (we'll come back to those). Expand: new column plus a sync trigger SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN id_new bigint; -- nullable, no default: catalog only CREATE FUNCTION orders_sync_id_new() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$; CREATE TRIGGER orders_sync_id_new BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION orders_sync_id_new(); CREATE TRIGGER takes SHARE ROW EXCLUSIVE, which blocks writes while it's queued, so it gets the timeout too. From this point on, every new or updated row carries its own id_new. Only the historical rows are missing it. Backfill: many small transactions The mistake here is a single UPDATE orders SET id_new = id. That's one transaction touching every row: it holds row locks on the entire table until it commits, generates a dead tuple per row that vacuum can't clean until it ends, and dumps a huge burst of WAL on your replicas. Break it up. Postgres 11 procedures can COMMIT between batches: CREATE PROCEDURE backfill_orders_id_new(batch_size int, pause_seconds float8) LANGUAGE plpgsql AS $$ DECLARE cursor_id bigint := 0; max_id bigint; BEGIN SELECT max(id) INTO max_id FROM orders; WHILE cursor_id < max_id LOOP UPDATE orders SET id_new = id WHERE id > cursor_id AND id <= cursor_id + batch_size AND id_new IS NULL; cursor_id := cursor_id + batch_size; COMMIT; -- each batch is its own transaction PERFORM pg_sleep(pause_seconds); -- give vacuum and replicas room to breathe END LOOP; END $$; CALL backfill_orders_id_new(10000, 0.1); The batch size and pause there are placeholders, not recommendations. Tune them by watching replication lag and query latency while it runs, and start small. Ranges on the primary key beat OFFSET pagination, which gets slower with every page. Rows inserted after max_id was read don't need the backfill; the trigger already covered them. Two things to watch while it runs. Dead tuples: every updated row leaves one, so either let autovacuum keep up or run VACUUM orders periodically during the backfill, which Crunchy's write-up recommends for large production moves. And replicas: a backfill that outruns replication turns into stale reads for everyone hitting a read replica. !Four-step timeline for converting an int id to bigint: expand with a sync trigger, backfill in committed batches, prepare a unique index concurrently and a validated CHECK, then swap in one short transaction, with brief lock spikes only at expand and swap Prepare: index and not-null, both online CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new); ALTER TABLE orders ADD CONSTRAINT orders_id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_not_null; Everything expensive is now done, and none of it blocked traffic. Swap: one short transaction BEGIN; SET LOCAL lock_timeout = '2s'; ALTER TABLE orders ALTER COLUMN id DROP DEFAULT; ALTER TABLE orders DROP CONSTRAINT orders_pkey; ALTER TABLE orders ALTER COLUMN id DROP NOT NULL; -- old column must accept NULLs from now on ALTER TABLE orders RENAME COLUMN id TO id_old; ALTER TABLE orders RENAME COLUMN id_new TO id; ALTER SEQUENCE orders_id_seq AS bigint; ALTER SEQUENCE orders_id_seq OWNED BY orders.id; ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('orders_id_seq'); ALTER TABLE orders ALTER COLUMN id SET NOT NULL; -- scan skipped: valid CHECK proves it ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_key; ALTER TABLE orders DROP CONSTRAINT orders_id_new_not_null; DROP TRIGGER orders_sync_id_new ON orders; COMMIT; Every statement in there is catalog-only, so the ACCESS EXCLUSIVE lock is held for milliseconds. If the lock isn't available within two seconds, the whole thing rolls back and you retry. A few lines exist because of gotchas that are easy to miss: - ALTER SEQUENCE ... AS bigint. Per the numeric types docs, a serial column is shorthand for CREATE SEQUENCE ... AS integer. Change the column and forget the sequence, and it still stops at 2,147,483,647. The ALTER SEQUENCE docs confirm the max moves with the type as long as it was still sitting at the old type's limit. - DROP NOT NULL on the old column. The old id was NOT NULL with a default. After the swap, inserts stop filling it, so without this line every insert fails. - The trigger has to go in the same transaction. Its function references NEW.id_new, which no longer exists after the rename. Drop id_old in a later release, once nothing reads it. DROP COLUMN is catalog-only, so that step is cheap. The part that makes it take four releases Foreign keys. If order_items.order_id references orders.id, dropping orders_pkey fails unless you CASCADE, which drops the FK. And order_items.order_id is also an int, so it needs the same expand-backfill-swap treatment first. Crunchy's guide orders it that way: update child FK columns before the parent swap. Then recreate the FKs with NOT VALID and validate them. On a schema with a dozen tables pointing at the one you're converting, this is where the calendar time goes, and it's why GitLab's helpers exist at all. Or let a tool do it Hand-rolling this is educational once. After that, there are tools with the same mechanics built in: - pg_repack rebuilds a table online using a log table, triggers, and a swap. It holds ACCESS EXCLUSIVE only "for a short period during initial setup" and again "during the final swap-and-drop phase." It needs a primary key or a unique index on a NOT NULL column, and roughly twice the table-plus-indexes size in free disk. It's built for removing bloat and reordering, not for arbitrary schema changes. - pg-osc is inspired by pg_repack and Percona's pt-online-schema-change: build a shadow table with the new schema, copy data over while triggers capture ongoing changes, then swap names. - pgroll from Xata takes a different route. It "works by creating virtual schemas by using views on top of the physical tables," follows expand/contract, and keeps old and new schema versions live at the same time so old and new app versions can both run. complete drops the old version; rollback drops the new one. My take: for column type changes on a handful of tables, the hand-rolled version above is fine and you'll understand every lock it takes. Shadow-table tools copy the whole table to change one column, which is a lot of I/O and disk for an int to bigint. pgroll earns its keep when you're doing this regularly and want the app-versioning side enforced rather than remembered. Whichever you pick, it still needs the same thing the manual version needs: no hour-long transactions sitting on the table when it's time to swap. The app is half of the migration Every technique here assumes something the database can't enforce: that your application can live with both the old and new schema for a while. Renaming a column in place is catalog-only and instant, and it still breaks every running instance of the old code that references the old name. That's why the GitLab guide treats a rename as rename_column_concurrently in one release and cleanup in the next, and why pgroll bothers with versioned views. The order that holds up: 1. Expand the schema so old and new code both work (new nullable column, new table, new index). 2. Deploy code that writes both shapes and reads the old one. 3. Backfill in small batches. 4. Deploy code that reads the new shape. 5. Contract: drop the old column, trigger, and constraints in a later release. Skip steps and you're back to needing a maintenance window, no matter how clever the SQL is. > Tip > Before you press enter on any production DDL, answer three questions: which lock does this take (look it up, don't guess), does it rewrite or scan while holding it, and is lock_timeout set in this session? If you can't answer the first two from the docs, run it against a production-sized copy with \timing on first. The billion-row table gets all the attention, but it's the forgotten open transaction and the missing lock_timeout that turn a two-millisecond ALTER TABLE into fifteen seconds of timeouts. Fix the wait first. Then deal with the size. --- Originally published at andriiboyko.com.