Files

1460 lines
65 KiB
HTML

<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>reference</title>
<meta name="color-scheme" content="dark">
<style>
/* Committed to one visual world: a dark instrument panel. No light variant —
the dot field and the hairline rules only read on black, and a naive
inversion would wash both out. Every colour is painted explicitly so the page
never borrows a host background. */
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
:root{
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
--font-zen-pixel-square:'Zen Pixel Square',monospace;
--mono:var(--font-zen-mono);
--sans:var(--font-zen-sans);
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
--r:999px; --r-soft:14px;
}
*{box-sizing:border-box}
html{-webkit-text-size-adjust:100%}
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
/* The dot field. A halftone made of one repeating radial-gradient, faded out
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
/* No overflow clip here: the dot field is inset to the box already, and a clip
would cut off the copy-prompt menu. */
.hero{position:relative;border-bottom:1px solid var(--line)}
.hero::before{content:"";position:absolute;inset:0;
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
background-size:7px 7px;
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
pointer-events:none}
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
nav{border-bottom:1px solid var(--line)}
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
justify-content:space-between;align-items:center;gap:1rem}
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--ink);text-decoration:none;
display:inline-flex;align-items:center;gap:.6rem}
.brand i{font-style:normal;color:var(--faint)}
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--soft);text-decoration:none}
nav a.up:hover{color:var(--ink)}
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
h1 em{font-style:normal;color:var(--faint)}
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
line-height:1.2;font-weight:400}
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
.btn.solid:hover{background:#d8d8d4}
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
/* Copy-prompt selector. Each row copies the prompt for its own target. */
.copy{position:relative;display:inline-flex}
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
padding:.4rem;display:flex;flex-direction:column;
box-shadow:0 12px 32px rgba(0,0,0,.75)}
.menu[hidden]{display:none}
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
white-space:nowrap}
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
.mi:hover .mark{color:var(--ink)}
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
letter-spacing:.1em;text-transform:uppercase}
/* Works-with bar: one scrolling row, name and mark per agent. */
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
display:flex;align-items:center}
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
-webkit-overflow-scrolling:touch;
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
.bar::-webkit-scrollbar{display:none}
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
text-decoration:none;background:var(--ground)}
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
.agent .mark{width:.95rem;height:.95rem}
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
p{margin:0 0 1rem;max-width:70ch}
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
li{margin:.22rem 0}
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
text-decoration-color:var(--line-hi)}
a:hover{text-decoration-color:var(--ink)}
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
display:block;overflow-x:auto}
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
.curl{display:block;background:var(--panel);border:1px solid var(--line);
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
white-space:pre;line-height:1.9}
.curl b{color:var(--ink);font-weight:400}
.curl i{font-style:normal;color:var(--faint)}
/* index */
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
#q:focus{outline:none;border-color:var(--soft)}
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
text-transform:uppercase;margin:0 0 2rem}
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
padding-bottom:.45rem}
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
border-radius:var(--r-soft);text-decoration:none}
.row:hover{background:#0a0a0a}
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
color:var(--faint);font-size:.85rem}
footer a{color:var(--soft)}
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
margin:0 0 2rem;font-size:.85rem}
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
border-bottom:1px solid var(--line);padding-bottom:.4rem}
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
.fmap a:hover{text-decoration:underline}
.fmap div{color:var(--faint);line-height:1.5}
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
@media (max-width:640px){
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
.btns{gap:.5rem}
.btn{padding:.75rem 1.15rem;font-size:.68rem}
.copy,.copy .btn{width:100%;justify-content:center}
.menu{left:0;right:0;min-width:0}
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
.strip b{border-right:0;padding-right:0}
.bar{width:100%}
}
</style>
<link rel="icon" href="/favicon.svg">
<link rel="canonical" href="https://hanzoskills.com/reference">
<link rel="alternate" type="text/markdown" href="/reference.md">
<script type="module" src="/webmcp.js"></script>
</head>
<!-- Reading this with a machine? Every page here is also plain markdown.
This one: https://hanzoskills.com/reference.md
Index: https://hanzoskills.com/llms.txt
Corpus: https://hanzoskills.com/all.md
Append .md to any path for the markdown twin. -->
<body>
<nav><div class="in">
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
<a class="up" href="/">All skills</a>
</div></nav>
<div class="wrap">
<h1>reference</h1><p class="lede">Scope: Comprehensive guide to database migrations, schema versioning, zero-downtime deployments, and migration tooling Lines: ~1800 Last Updated: 2025-10-27</p><h1>PostgreSQL Migrations Reference</h1>
<p><strong>Scope</strong>: Comprehensive guide to database migrations, schema versioning, zero-downtime deployments, and migration tooling <strong>Lines</strong>: ~1800 <strong>Last Updated</strong>: 2025-10-27</p>
<h2>Table of Contents</h2>
<ol><li><a href="#migration-fundamentals">Migration Fundamentals</a></li><li><a href="#schema-versioning-strategies">Schema Versioning Strategies</a></li><li><a href="#migration-tools-deep-dive">Migration Tools Deep Dive</a></li><li><a href="#writing-safe-migrations">Writing Safe Migrations</a></li><li><a href="#zero-downtime-patterns">Zero-Downtime Patterns</a></li><li><a href="#postgresql-lock-behavior">PostgreSQL Lock Behavior</a></li><li><a href="#data-migrations">Data Migrations</a></li><li><a href="#testing-migrations">Testing Migrations</a></li><li><a href="#rollback-strategies">Rollback Strategies</a></li><li><a href="#common-pitfalls">Common Pitfalls</a></li><li><a href="#advanced-topics">Advanced Topics</a></li></ol>
<hr>
<h2>Migration Fundamentals</h2>
<h3>What Are Migrations?</h3>
<p>Migrations are <strong>versioned, ordered scripts</strong> that evolve database schema over time.</p>
<p><strong>Core Properties</strong>:</p>
<ul><li><strong>Versioned</strong>: Each migration has a unique identifier (timestamp, sequence number, hash)</li><li><strong>Ordered</strong>: Applied sequentially to maintain consistency</li><li><strong>Tracked</strong>: Database maintains record of applied migrations</li><li><strong>Reversible</strong>: Can roll back changes (ideally)</li><li><strong>Idempotent</strong>: Safe to run multiple times (ideally)</li><li><strong>Atomic</strong>: Changes succeed or fail as a unit (when possible)</li></ul>
<h3>Why Migrations Matter</h3>
<p><strong>Without migrations</strong>:</p>
<pre><code class="lang-sql">-- Developer A: Creates table manually
CREATE TABLE users (id INT, email VARCHAR(255));
-- Developer B: Different schema!
CREATE TABLE users (id SERIAL, email TEXT, created_at TIMESTAMP);
-- Production: Who knows what&#x27;s deployed?</code></pre>
<p><strong>With migrations</strong>:</p>
<pre><code class="lang-sql">-- V1: Everyone runs same migration
-- Version controlled, tested, reproducible
-- Production schema matches development</code></pre>
<h3>Migration Lifecycle</h3>
<pre><code>Development → Testing → Staging → Production
↓ ↓ ↓ ↓
V1, V2 V1, V2 V1, V2 V1, V2</code></pre>
<p><strong>Key principle</strong>: Same migrations run in all environments.</p>
<h3>Migration Tracking</h3>
<p>Most tools create a metadata table:</p>
<pre><code class="lang-sql">-- Example: Flyway&#x27;s schema_version table
CREATE TABLE flyway_schema_history (
installed_rank INT NOT NULL,
version VARCHAR(50),
description VARCHAR(200) NOT NULL,
type VARCHAR(20) NOT NULL,
script VARCHAR(1000) NOT NULL,
checksum INT,
installed_by VARCHAR(100) NOT NULL,
installed_on TIMESTAMP NOT NULL DEFAULT NOW(),
execution_time INT NOT NULL,
success BOOLEAN NOT NULL
);</code></pre>
<p><strong>Query applied migrations</strong>:</p>
<pre><code class="lang-sql">SELECT version, description, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;</code></pre>
<hr>
<h2>Schema Versioning Strategies</h2>
<h3>Strategy 1: Forward-Only Migrations</h3>
<p><strong>Approach</strong>: Only write &quot;up&quot; migrations, never roll back.</p>
<pre><code>V1 → V2 → V3 → V4
↓ ↓ ↓ ↓</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Simplest approach</li><li>No data loss from rollbacks</li><li>Production always moves forward</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Can&#x27;t easily undo mistakes</li><li>Failed migrations require fix-forward</li></ul>
<p><strong>Best for</strong>: Production systems where rollback is rare.</p>
<p><strong>Example</strong>:</p>
<pre><code class="lang-sql">-- V1__initial_schema.sql
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));
-- V2__add_username.sql
ALTER TABLE users ADD COLUMN username VARCHAR(100);
-- V3__add_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- V4 (fix for V3 if it failed)
-- V4__retry_index.sql
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<h3>Strategy 2: Bidirectional Migrations (Up/Down)</h3>
<p><strong>Approach</strong>: Every migration has &quot;up&quot; and &quot;down&quot; version.</p>
<pre><code>V1 ⇄ V2 ⇄ V3 ⇄ V4</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Can roll back bad deployments</li><li>Easier to develop locally (apply/revert)</li><li>Better for testing</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Down migrations can lose data</li><li>More code to maintain</li><li>Rollback may not be safe in production</li></ul>
<p><strong>Best for</strong>: Development, staging, new projects.</p>
<p><strong>Example</strong>:</p>
<pre><code class="lang-sql">-- 001_add_users.up.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
-- 001_add_users.down.sql
-- Example of rollback migration - destructive operation for reverting schema
DROP TABLE users;
-- 002_add_username.up.sql
ALTER TABLE users ADD COLUMN username VARCHAR(100);
-- 002_add_username.down.sql
ALTER TABLE users DROP COLUMN username;</code></pre>
<h3>Strategy 3: Immutable Migrations</h3>
<p><strong>Approach</strong>: Once applied, migrations NEVER change. Fixes go in new migrations.</p>
<p><strong>Rule</strong>: Migration files are immutable after merge to main.</p>
<pre><code>V1 (bug!) → V2 (fix) → V3</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>History preserved</li><li>No checksum conflicts</li><li>Clear audit trail</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Can&#x27;t fix typos in old migrations</li><li>Must create new migration for fixes</li></ul>
<p><strong>Best practice</strong>: Use checksums to detect unauthorized changes.</p>
<p><strong>Example</strong>:</p>
<pre><code class="lang-sql">-- V1__create_users.sql (has bug: wrong column type)
CREATE TABLE users (id INT, email VARCHAR(50)); -- Bug: email too short!
-- V2__fix_email_length.sql (fix in new migration)
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);</code></pre>
<h3>Strategy 4: State-Based Migrations</h3>
<p><strong>Approach</strong>: Define desired state, tool generates migrations.</p>
<p><strong>Tools</strong>: Atlas, Prisma Migrate, TypeORM with synchronize.</p>
<pre><code>Schema Definition (code) → Tool → Generated Migrations</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Less manual SQL</li><li>Auto-detects schema drift</li><li>Type-safe (if using ORM)</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Less control over migration details</li><li>Can generate suboptimal SQL</li><li>Tool lock-in</li></ul>
<p><strong>Example (Atlas)</strong>:</p>
<pre><code class="lang-hcl">// schema.hcl
table &quot;users&quot; {
column &quot;id&quot; { type = serial }
column &quot;email&quot; { type = varchar(255) }
primary_key { columns = [column.id] }
}</code></pre>
<pre><code class="lang-bash">atlas migrate diff --env local
# Generates: 20231027_initial.sql</code></pre>
<hr>
<h2>Migration Tools Deep Dive</h2>
<h3>Flyway</h3>
<p><strong>Language</strong>: Java/SQL <strong>Philosophy</strong>: SQL-first, version-based <strong>Best for</strong>: Enterprise Java applications</p>
<p><strong>Features</strong>:</p>
<ul><li>Simple SQL-based migrations</li><li>Commercial support available</li><li>Callbacks for custom logic</li><li>Out-of-order migrations (advanced)</li></ul>
<p><strong>Configuration</strong>:</p>
<pre><code class="lang-properties"># flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=postgres
flyway.password=secret
flyway.locations=filesystem:./migrations
flyway.baselineVersion=1
flyway.baselineOnMigrate=true</code></pre>
<p><strong>Naming Convention</strong>:</p>
<pre><code>V{version}__{description}.sql
V1__initial_schema.sql
V2__add_users_table.sql
V2.1__add_users_email_index.sql # Dot notation for sub-versions</code></pre>
<p><strong>Commands</strong>:</p>
<pre><code class="lang-bash">flyway migrate # Apply pending migrations
flyway info # Show migration status
flyway validate # Validate applied migrations
flyway baseline # Baseline existing database
flyway repair # Fix metadata (use with caution)
flyway clean # Drop all objects (DANGEROUS!)</code></pre>
<p><strong>Callbacks</strong>:</p>
<pre><code class="lang-sql">-- beforeMigrate.sql: Runs before any migration
SET statement_timeout = &#x27;30s&#x27;;
-- afterMigrate.sql: Runs after all migrations
ANALYZE;</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Battle-tested</li><li>Great documentation</li><li>Commercial support</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Java dependency (JVM required)</li><li>Pro features require license</li><li>No native down migrations</li></ul>
<hr>
<h3>golang-migrate</h3>
<p><strong>Language</strong>: Go/SQL <strong>Philosophy</strong>: Simple, CLI-focused, up/down pattern <strong>Best for</strong>: Go microservices</p>
<p><strong>Features</strong>:</p>
<ul><li>Native Go binary (no JVM)</li><li>Up/down migrations</li><li>Multiple database support</li><li>Programmatic API</li></ul>
<p><strong>Installation</strong>:</p>
<pre><code class="lang-bash">go install -tags &#x27;postgres&#x27; github.com/golang-migrate/migrate/v4/cmd/migrate@latest</code></pre>
<p><strong>Naming Convention</strong>:</p>
<pre><code>{version}_{description}.up.sql
{version}_{description}.down.sql
000001_initial_schema.up.sql
000001_initial_schema.down.sql
000002_add_users_table.up.sql
000002_add_users_table.down.sql</code></pre>
<p><strong>Commands</strong>:</p>
<pre><code class="lang-bash"># Create migration
migrate create -ext sql -dir migrations -seq add_users_table
# Apply all up migrations
migrate -database &quot;postgres://localhost:5432/db?sslmode=disable&quot; -path migrations up
# Apply one up migration
migrate -database &quot;$DB_URL&quot; -path migrations up 1
# Rollback one migration
migrate -database &quot;$DB_URL&quot; -path migrations down 1
# Force version (recovery from dirty state)
migrate -database &quot;$DB_URL&quot; -path migrations force 5
# Show current version
migrate -database &quot;$DB_URL&quot; -path migrations version</code></pre>
<p><strong>Programmatic Usage</strong>:</p>
<pre><code class="lang-go">import (
&quot;github.com/golang-migrate/migrate/v4&quot;
_ &quot;github.com/golang-migrate/migrate/v4/database/postgres&quot;
_ &quot;github.com/golang-migrate/migrate/v4/source/file&quot;
)
m, err := migrate.New(
&quot;file://migrations&quot;,
&quot;postgres://localhost:5432/db?sslmode=disable&quot;)
m.Up() // Apply all migrations
m.Steps(2) // Apply 2 migrations
m.Down() // Rollback all (DANGEROUS!)</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Lightweight, single binary</li><li>Native Go integration</li><li>Simple up/down pattern</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Manual version numbering</li><li>No automatic rollback on failure</li><li>Limited advanced features</li></ul>
<hr>
<h3>Alembic</h3>
<p><strong>Language</strong>: Python/SQL <strong>Philosophy</strong>: SQLAlchemy integration, auto-generate <strong>Best for</strong>: Python applications (Flask, FastAPI, Django alternatives)</p>
<p><strong>Features</strong>:</p>
<ul><li>Auto-generate from SQLAlchemy models</li><li>Branching/merging migrations</li><li>Offline SQL generation</li><li>Revision history</li></ul>
<p><strong>Installation</strong>:</p>
<pre><code class="lang-bash">uv add alembic psycopg2-binary
alembic init alembic</code></pre>
<p><strong>Configuration</strong>:</p>
<pre><code class="lang-python"># alembic.ini
[alembic]
script_location = alembic
sqlalchemy.url = postgresql://localhost:5432/mydb
# alembic/env.py
from myapp.models import Base
target_metadata = Base.metadata</code></pre>
<p><strong>Creating Migrations</strong>:</p>
<pre><code class="lang-bash"># Manual migration
alembic revision -m &quot;add users table&quot;
# Auto-generate from models
alembic revision --autogenerate -m &quot;add users table&quot;</code></pre>
<p><strong>Migration File</strong>:</p>
<pre><code class="lang-python"># alembic/versions/abc123_add_users_table.py
from alembic import op
import sqlalchemy as sa
revision = &#x27;abc123&#x27;
down_revision = &#x27;def456&#x27; # Previous migration
branch_labels = None
depends_on = None
def upgrade():
op.create_table(
&#x27;users&#x27;,
sa.Column(&#x27;id&#x27;, sa.Integer(), primary_key=True),
sa.Column(&#x27;email&#x27;, sa.String(255), unique=True, nullable=False),
sa.Column(&#x27;created_at&#x27;, sa.DateTime(), server_default=sa.func.now())
)
op.create_index(&#x27;idx_users_email&#x27;, &#x27;users&#x27;, [&#x27;email&#x27;])
def downgrade():
op.drop_index(&#x27;idx_users_email&#x27;, &#x27;users&#x27;)
op.drop_table(&#x27;users&#x27;)</code></pre>
<p><strong>Commands</strong>:</p>
<pre><code class="lang-bash">alembic upgrade head # Apply all migrations
alembic upgrade +1 # Apply one migration
alembic downgrade -1 # Rollback one migration
alembic current # Show current version
alembic history # Show migration history
alembic stamp head # Mark as current without running
alembic revision --sql ... # Generate SQL without applying</code></pre>
<p><strong>Branching</strong>:</p>
<pre><code class="lang-bash"># Create branch
alembic revision -m &quot;feature A&quot; --head=base --branch-label=feature_a
alembic revision -m &quot;feature B&quot; --head=base --branch-label=feature_b
# Merge branches
alembic merge -m &quot;merge features&quot; feature_a feature_b</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Tight SQLAlchemy integration</li><li>Auto-generate migrations</li><li>Advanced branching/merging</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Python-only</li><li>Auto-generate not always accurate</li><li>Complex for simple projects</li></ul>
<hr>
<h3>Liquibase</h3>
<p><strong>Language</strong>: Java/XML/YAML/SQL <strong>Philosophy</strong>: Database-agnostic, change sets <strong>Best for</strong>: Enterprise multi-database environments</p>
<p><strong>Features</strong>:</p>
<ul><li>Database-agnostic (Postgres, MySQL, Oracle, etc.)</li><li>XML/YAML/SQL/JSON formats</li><li>Preconditions and contexts</li><li>Rollback tags</li></ul>
<p><strong>Example (YAML)</strong>:</p>
<pre><code class="lang-yaml"># changelog.yml
databaseChangeLog:
- changeSet:
id: 1
author: developer
changes:
- createTable:
tableName: users
columns:
- column:
name: id
type: SERIAL
constraints:
primaryKey: true
- column:
name: email
type: VARCHAR(255)
constraints:
unique: true
nullable: false
rollback:
- dropTable:
tableName: users</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Database-agnostic</li><li>Rich feature set</li><li>Enterprise support</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Complex XML/YAML syntax</li><li>Java dependency</li><li>Steeper learning curve</li></ul>
<hr>
<h3>dbmate</h3>
<p><strong>Language</strong>: Any/SQL <strong>Philosophy</strong>: Simple, language-agnostic, minimal <strong>Best for</strong>: Polyglot projects, simple setups</p>
<p><strong>Features</strong>:</p>
<ul><li>Single binary</li><li>Simple up/down SQL</li><li>No dependencies</li><li>URL-based configuration</li></ul>
<p><strong>Installation</strong>:</p>
<pre><code class="lang-bash"># macOS
brew install dbmate
# Or download binary
curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
chmod +x /usr/local/bin/dbmate</code></pre>
<p><strong>Usage</strong>:</p>
<pre><code class="lang-bash"># Set database URL
export DATABASE_URL=&quot;postgres://localhost:5432/mydb?sslmode=disable&quot;
# Create migration
dbmate new add_users_table
# Apply migrations
dbmate up
# Rollback
dbmate down
# Status
dbmate status</code></pre>
<p><strong>Migration File</strong>:</p>
<pre><code class="lang-sql">-- migrations/20231027120000_add_users_table.sql
-- migrate:up
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
-- migrate:down
-- Example of rollback migration - destructive operation for reverting schema
DROP TABLE users;</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Dead simple</li><li>Language-agnostic</li><li>Lightweight</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Limited features</li><li>No auto-generate</li><li>No branching</li></ul>
<hr>
<h3>Atlas</h3>
<p><strong>Language</strong>: Go/HCL <strong>Philosophy</strong>: Modern, declarative, schema-as-code <strong>Best for</strong>: Modern Go applications, GitOps workflows</p>
<p><strong>Features</strong>:</p>
<ul><li>Declarative schema definition</li><li>Automatic migration generation</li><li>Schema diffing</li><li>Policy-as-code (Pro)</li><li>Cloud integration (Pro)</li></ul>
<p><strong>Schema Definition</strong>:</p>
<pre><code class="lang-hcl">// schema.hcl
table &quot;users&quot; {
schema = schema.public
column &quot;id&quot; {
type = serial
}
column &quot;email&quot; {
type = varchar(255)
}
primary_key {
columns = [column.id]
}
index &quot;idx_email&quot; {
unique = true
columns = [column.email]
}
}</code></pre>
<p><strong>Commands</strong>:</p>
<pre><code class="lang-bash"># Inspect current schema
atlas schema inspect -u &quot;postgres://localhost:5432/db&quot; &gt; schema.hcl
# Generate migration
atlas migrate diff add_users --env local
# Apply migrations
atlas migrate apply --env local
# Validate
atlas migrate validate --env local</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Modern, declarative approach</li><li>Type-safe schema definitions</li><li>Great CI/CD integration</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Newer tool (less mature)</li><li>Pro features require license</li><li>Go-centric</li></ul>
<hr>
<h2>Writing Safe Migrations</h2>
<h3>Safety Principles</h3>
<ol><li><strong>Idempotency</strong>: Safe to run multiple times</li><li><strong>Atomicity</strong>: All-or-nothing changes</li><li><strong>Reversibility</strong>: Can undo changes</li><li><strong>Non-blocking</strong>: Don&#x27;t lock tables</li><li><strong>Backward compatible</strong>: Old code still works</li><li><strong>Testable</strong>: Can verify locally</li></ol>
<h3>Idempotent Migrations</h3>
<p><strong>Problem</strong>: Migration fails halfway, retried, errors on existing objects.</p>
<p><strong>Solution</strong>: Use <code>IF NOT EXISTS</code> / <code>IF EXISTS</code>.</p>
<pre><code class="lang-sql">-- ✅ GOOD: Idempotent
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255)
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
ALTER TABLE users ADD CONSTRAINT IF NOT EXISTS users_email_unique UNIQUE (email);
-- ✅ GOOD: Idempotent drop (safe cleanup operation)
DROP TABLE IF EXISTS old_temp_table;
DROP INDEX IF EXISTS idx_old_index;</code></pre>
<p><strong>PostgreSQL version check</strong>:</p>
<pre><code class="lang-sql">-- IF NOT EXISTS added in:
-- CREATE TABLE IF NOT EXISTS: 9.1+
-- ALTER TABLE ADD COLUMN IF NOT EXISTS: 9.6+
-- CREATE INDEX IF NOT EXISTS: 9.5+</code></pre>
<h3>Transaction Wrapping</h3>
<p><strong>Use transactions when possible</strong>:</p>
<pre><code class="lang-sql">BEGIN;
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id),
total DECIMAL(10,2) NOT NULL
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
COMMIT;</code></pre>
<p><strong>When NOT to use transactions</strong>:</p>
<pre><code class="lang-sql">-- ❌ Cannot run in transaction block
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
DROP INDEX CONCURRENTLY idx_old_index;
VACUUM;
REINDEX CONCURRENTLY;</code></pre>
<p><strong>Solution</strong>: Split into separate migration files.</p>
<pre><code class="lang-sql">-- V1__add_orders_table.sql (transactional)
BEGIN;
CREATE TABLE orders (...);
COMMIT;
-- V2__add_orders_index.sql (non-transactional)
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);</code></pre>
<h3>Checking Constraints Without Locking</h3>
<p><strong>Problem</strong>: <code>ALTER TABLE ADD CONSTRAINT</code> locks table for validation.</p>
<p><strong>Solution</strong>: Add constraint as <code>NOT VALID</code>, then validate separately.</p>
<pre><code class="lang-sql">-- Step 1: Add constraint without validation (fast, allows writes)
ALTER TABLE users
ADD CONSTRAINT check_age_positive
CHECK (age &gt; 0) NOT VALID;
-- Step 2: Validate constraint (slow, but doesn&#x27;t block writes)
ALTER TABLE users VALIDATE CONSTRAINT check_age_positive;</code></pre>
<p><strong>Locking behavior</strong>:</p>
<ul><li><code>ADD CONSTRAINT ... NOT VALID</code>: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li><li><code>VALIDATE CONSTRAINT</code>: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li></ul>
<h3>Creating Indexes Without Locking</h3>
<p><strong>Problem</strong>: <code>CREATE INDEX</code> takes AccessExclusiveLock, blocks all queries.</p>
<p><strong>Solution</strong>: <code>CREATE INDEX CONCURRENTLY</code></p>
<pre><code class="lang-sql">-- ❌ BAD: Locks table
CREATE INDEX idx_users_email ON users(email);
-- ✅ GOOD: No lock
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<p><strong>Trade-offs</strong>:</p>
<ul><li>Takes 2-3x longer than regular CREATE INDEX</li><li>Cannot run in transaction</li><li>Can fail and leave invalid index</li></ul>
<p><strong>Check for invalid indexes</strong>:</p>
<pre><code class="lang-sql">SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE indexname NOT IN (
SELECT indexrelid::regclass::text
FROM pg_index
WHERE indisvalid
);
-- Or simpler:
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;</code></pre>
<p><strong>Clean up invalid index</strong>:</p>
<pre><code class="lang-sql">DROP INDEX CONCURRENTLY idx_users_email;
-- Then retry creation
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<h3>Adding NOT NULL Columns</h3>
<p><strong>Problem</strong>: Adding NOT NULL column to non-empty table fails.</p>
<p><strong>Solution</strong>: Multi-step approach.</p>
<pre><code class="lang-sql">-- ❌ FAILS on existing rows
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
-- ERROR: column &quot;phone&quot; contains null values
-- ✅ GOOD: Multi-step
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Step 2: Backfill (in batches, see Data Migrations section)
UPDATE users SET phone = &#x27;unknown&#x27; WHERE phone IS NULL;
-- Step 3: Add NOT NULL constraint (fast, already validated)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;</code></pre>
<p><strong>Alternative with default</strong>:</p>
<pre><code class="lang-sql">-- ✅ GOOD: Use default value
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT &#x27;unknown&#x27;;
-- Optionally remove default after
ALTER TABLE users ALTER COLUMN phone DROP DEFAULT;</code></pre>
<p><strong>Note</strong>: Adding column with DEFAULT rewrites entire table in PG &lt; 11. In PG 11+, it&#x27;s fast (default stored in metadata).</p>
<hr>
<h2>Zero-Downtime Patterns</h2>
<h3>Pattern 1: Adding Nullable Column</h3>
<p><strong>Scenario</strong>: Add new optional column.</p>
<p><strong>Strategy</strong>: Single-step, backward compatible.</p>
<pre><code class="lang-sql">-- Migration: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
<p><strong>Deployment</strong>:</p>
<ol><li>Apply migration</li><li>Deploy new code (can read/write phone)</li></ol>
<p><strong>Compatibility</strong>:</p>
<ul><li>Old code: Ignores new column</li><li>New code: Reads/writes new column</li><li>Zero downtime</li></ul>
<hr>
<h3>Pattern 2: Adding NOT NULL Column</h3>
<p><strong>Scenario</strong>: Add required column.</p>
<p><strong>Strategy</strong>: Multi-phase deployment.</p>
<p><strong>Phase 1 Migration</strong>:</p>
<pre><code class="lang-sql">-- Add nullable column with default
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT &#x27;active&#x27;;</code></pre>
<p><strong>Phase 1 Deployment</strong>:</p>
<ul><li>Deploy code that writes to status</li><li>Old code ignores column, new code writes it</li></ul>
<p><strong>Phase 2 Migration</strong> (after all instances updated):</p>
<pre><code class="lang-sql">-- Backfill any NULLs (shouldn&#x27;t be any if Phase 1 worked)
UPDATE users SET status = &#x27;active&#x27; WHERE status IS NULL;
-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
-- Optionally drop default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;</code></pre>
<p><strong>Timeline</strong>:</p>
<ul><li>Day 1: Phase 1 migration + deployment</li><li>Day 7: Verify all rows have status set</li><li>Day 7: Phase 2 migration</li></ul>
<hr>
<h3>Pattern 3: Removing Column (Expand-Contract)</h3>
<p><strong>Scenario</strong>: Remove unused column.</p>
<p><strong>Strategy</strong>: Three-phase deployment.</p>
<p><strong>Phase 1</strong>: Stop writing (code change only, no migration)</p>
<ul><li>Deploy code that stops writing to old column</li><li>Old code may still read column</li></ul>
<p><strong>Phase 2</strong>: Stop reading (code change only, no migration)</p>
<ul><li>Deploy code that doesn&#x27;t reference column at all</li><li>Wait for all instances to update</li></ul>
<p><strong>Phase 3</strong>: Remove column (migration)</p>
<pre><code class="lang-sql">ALTER TABLE users DROP COLUMN old_field;</code></pre>
<p><strong>Timeline</strong>:</p>
<ul><li>Week 1: Phase 1 - stop writing</li><li>Week 2: Phase 2 - stop reading</li><li>Week 3: Verify no references in logs</li><li>Week 3: Phase 3 - drop column</li></ul>
<hr>
<h3>Pattern 4: Renaming Column (Dual-Write)</h3>
<p><strong>Scenario</strong>: Rename column (email → email_address).</p>
<p><strong>Strategy</strong>: Four-phase deployment with dual-write.</p>
<p><strong>Phase 1 Migration</strong>:</p>
<pre><code class="lang-sql">-- Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Create trigger for dual-write
CREATE OR REPLACE FUNCTION sync_email_columns()
RETURNS TRIGGER AS $$
BEGIN
NEW.email_address := NEW.email;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_email_to_email_address
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION sync_email_columns();</code></pre>
<p><strong>Phase 1 Deployment</strong>:</p>
<ul><li>Application writes to email (trigger copies to email_address)</li></ul>
<p><strong>Phase 2 Migration</strong>:</p>
<pre><code class="lang-sql">-- Backfill existing rows
UPDATE users SET email_address = email WHERE email_address IS NULL;</code></pre>
<p><strong>Phase 3 Deployment</strong>:</p>
<ul><li>Application reads from email_address, writes to both</li></ul>
<p><strong>Phase 4 Migration</strong>:</p>
<pre><code class="lang-sql">-- Drop trigger
DROP TRIGGER sync_email_to_email_address ON users;
DROP FUNCTION sync_email_columns();
-- Drop old column
ALTER TABLE users DROP COLUMN email;
-- Optionally rename (fast metadata change)
-- Or keep email_address as final name</code></pre>
<p><strong>Timeline</strong>:</p>
<ul><li>Week 1: Phase 1 - add column, trigger</li><li>Week 2: Phase 2 - backfill</li><li>Week 3: Phase 3 - app reads new column</li><li>Week 4: Verify, Phase 4 - drop old column</li></ul>
<hr>
<h3>Pattern 5: Changing Column Type</h3>
<p><strong>Scenario</strong>: Change column type (age INT → age BIGINT).</p>
<p><strong>Strategy</strong>: Add new column, dual-write, swap.</p>
<p><strong>Phase 1 Migration</strong>:</p>
<pre><code class="lang-sql">-- Add new column with new type
ALTER TABLE users ADD COLUMN age_new BIGINT;</code></pre>
<p><strong>Phase 1 Deployment</strong>:</p>
<ul><li>Application writes to both age and age_new</li></ul>
<p><strong>Phase 2 Migration</strong>:</p>
<pre><code class="lang-sql">-- Backfill
UPDATE users SET age_new = age WHERE age_new IS NULL;</code></pre>
<p><strong>Phase 3 Deployment</strong>:</p>
<ul><li>Application reads from age_new</li></ul>
<p><strong>Phase 4 Migration</strong>:</p>
<pre><code class="lang-sql">-- Drop old, rename new
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_new TO age;</code></pre>
<p><strong>Shortcut for compatible types</strong>:</p>
<pre><code class="lang-sql">-- Some type changes don&#x27;t require table rewrite
ALTER TABLE users ALTER COLUMN email TYPE TEXT; -- VARCHAR → TEXT (fast)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT USING age::BIGINT; -- May rewrite table</code></pre>
<p><strong>Fast type changes (PG 12+)</strong>:</p>
<ul><li>VARCHAR(N) → VARCHAR(M) where M &gt; N</li><li>VARCHAR → TEXT</li><li>NUMERIC(a,b) → NUMERIC(c,d) where c &gt;= a and d = b</li></ul>
<hr>
<h3>Pattern 6: Adding Index (CONCURRENTLY)</h3>
<p><strong>Scenario</strong>: Add index without blocking writes.</p>
<p><strong>Strategy</strong>: CREATE INDEX CONCURRENTLY.</p>
<pre><code class="lang-sql">-- ❌ BAD: Locks table
CREATE INDEX idx_users_email ON users(email);
-- ✅ GOOD: No lock
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<p><strong>Migration file</strong>:</p>
<pre><code class="lang-sql">-- V5__add_users_email_index.sql
-- NOTE: Cannot run in transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);</code></pre>
<p><strong>Handling failures</strong>:</p>
<pre><code class="lang-sql">-- Check for invalid indexes
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
-- Drop and retry
DROP INDEX CONCURRENTLY idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<hr>
<h3>Pattern 7: Adding Foreign Key</h3>
<p><strong>Scenario</strong>: Add foreign key constraint.</p>
<p><strong>Strategy</strong>: Add NOT VALID, then validate.</p>
<pre><code class="lang-sql">-- Step 1: Add constraint without validation
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;
-- Step 2: Validate (allows concurrent reads/writes)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;</code></pre>
<p><strong>Locking</strong>:</p>
<ul><li>Step 1: ShareRowExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li><li>Step 2: ShareUpdateExclusiveLock (allows SELECT, INSERT, UPDATE, DELETE)</li></ul>
<hr>
<h2>PostgreSQL Lock Behavior</h2>
<h3>Lock Modes Reference</h3>
<p>| Lock Mode | SELECT | INSERT | UPDATE | DELETE | DDL | |-----------|--------|--------|--------|--------|-----| | AccessShareLock | ✓ | ✓ | ✓ | ✓ | ✗ | | RowShareLock | ✓ | ✓ | ✓ | ✓ | ✗ | | RowExclusiveLock | ✓ | ✓ | ✓ | ✓ | ✗ | | ShareUpdateExclusiveLock | ✓ | ✓ | ✓ | ✓ | ✗ | | ShareLock | ✓ | ✗ | ✗ | ✗ | ✗ | | ShareRowExclusiveLock | ✓ | ✗ | ✗ | ✗ | ✗ | | ExclusiveLock | ✓ | ✗ | ✗ | ✗ | ✗ | | AccessExclusiveLock | ✗ | ✗ | ✗ | ✗ | ✗ |</p>
<h3>Common DDL Operations and Locks</h3>
<p>| Operation | Lock Mode | Blocks Reads? | Blocks Writes? | |-----------|-----------|---------------|----------------| | CREATE TABLE | AccessExclusiveLock | ✗ (new table) | ✗ (new table) | | DROP TABLE | AccessExclusiveLock | ✓ | ✓ | &lt;!-- Example of operation impact --&gt; | ALTER TABLE ADD COLUMN | AccessExclusiveLock | ✓ | ✓ | | ALTER TABLE ADD COLUMN (with DEFAULT, PG 11+) | AccessExclusiveLock (brief) | ✗ (metadata only) | ✗ (metadata only) | | CREATE INDEX | ShareLock | ✗ | ✓ | | CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | ✗ | ✗ | | DROP INDEX | AccessExclusiveLock | ✓ | ✓ | | DROP INDEX CONCURRENTLY | ShareUpdateExclusiveLock | ✗ | ✗ | | ADD CONSTRAINT (validated) | AccessExclusiveLock | ✓ | ✓ | | ADD CONSTRAINT NOT VALID | ShareUpdateExclusiveLock | ✗ | ✗ | | VALIDATE CONSTRAINT | ShareUpdateExclusiveLock | ✗ | ✗ |</p>
<h3>Monitoring Locks</h3>
<p><strong>View current locks</strong>:</p>
<pre><code class="lang-sql">SELECT
locktype,
relation::regclass,
mode,
granted,
pid,
usename,
query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted
ORDER BY relation;</code></pre>
<p><strong>View blocking queries</strong>:</p>
<pre><code class="lang-sql">SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;</code></pre>
<p><strong>Set statement timeout for migrations</strong>:</p>
<pre><code class="lang-sql">-- Prevent migration from locking too long
SET statement_timeout = &#x27;30s&#x27;;
-- Then run migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
<hr>
<h2>Data Migrations</h2>
<h3>Batching Large Updates</h3>
<p><strong>Problem</strong>: <code>UPDATE users SET status = &#x27;active&#x27;</code> locks rows and can time out.</p>
<p><strong>Solution</strong>: Batch updates with pg_sleep.</p>
<pre><code class="lang-sql">-- ❌ BAD: Locks entire table
UPDATE users SET status = &#x27;active&#x27; WHERE status IS NULL;
-- ✅ GOOD: Batch updates
DO $$
DECLARE
batch_size INT := 1000;
rows_updated INT;
total_updated INT := 0;
BEGIN
LOOP
UPDATE users
SET status = &#x27;active&#x27;
WHERE id IN (
SELECT id
FROM users
WHERE status IS NULL
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
total_updated := total_updated + rows_updated;
RAISE NOTICE &#x27;Updated % rows (total: %)&#x27;, rows_updated, total_updated;
EXIT WHEN rows_updated = 0;
COMMIT; -- Release locks
PERFORM pg_sleep(0.1); -- Avoid overwhelming database
END LOOP;
END $$;</code></pre>
<p><strong>Alternative with external script</strong>:</p>
<pre><code class="lang-python"># batch_update.py
import psycopg2
import time
conn = psycopg2.connect(&quot;dbname=mydb&quot;)
cursor = conn.cursor()
batch_size = 1000
total_updated = 0
while True:
cursor.execute(&quot;&quot;&quot;
UPDATE users SET status = &#x27;active&#x27;
WHERE id IN (
SELECT id FROM users WHERE status IS NULL LIMIT %s
)
&quot;&quot;&quot;, (batch_size,))
rows = cursor.rowcount
total_updated += rows
conn.commit()
print(f&quot;Updated {rows} rows (total: {total_updated})&quot;)
if rows == 0:
break
time.sleep(0.1)
cursor.close()
conn.close()</code></pre>
<h3>Backfilling Data</h3>
<p><strong>Scenario</strong>: New column needs data from existing columns.</p>
<pre><code class="lang-sql">-- Add column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Backfill in batches
DO $$
DECLARE
batch_size INT := 1000;
rows_updated INT;
BEGIN
LOOP
UPDATE users
SET full_name = first_name || &#x27; &#x27; || last_name
WHERE id IN (
SELECT id
FROM users
WHERE full_name IS NULL
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
COMMIT;
PERFORM pg_sleep(0.1);
END LOOP;
END $$;</code></pre>
<h3>Complex Data Transformations</h3>
<p><strong>Scenario</strong>: Normalize data (e.g., extract JSON to columns).</p>
<pre><code class="lang-sql">-- Existing: user_data JSONB column
-- New: email, phone columns
ALTER TABLE users ADD COLUMN email VARCHAR(255);
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Backfill
UPDATE users
SET
email = user_data-&gt;&gt;&#x27;email&#x27;,
phone = user_data-&gt;&gt;&#x27;phone&#x27;
WHERE email IS NULL;</code></pre>
<p><strong>With batching</strong>:</p>
<pre><code class="lang-python">import psycopg2
import time
conn = psycopg2.connect(&quot;dbname=mydb&quot;)
cursor = conn.cursor()
batch_size = 1000
offset = 0
while True:
cursor.execute(&quot;&quot;&quot;
WITH batch AS (
SELECT id, user_data
FROM users
WHERE email IS NULL
LIMIT %s OFFSET %s
)
UPDATE users
SET
email = batch.user_data-&gt;&gt;&#x27;email&#x27;,
phone = batch.user_data-&gt;&gt;&#x27;phone&#x27;
FROM batch
WHERE users.id = batch.id
&quot;&quot;&quot;, (batch_size, offset))
rows = cursor.rowcount
conn.commit()
if rows == 0:
break
offset += batch_size
time.sleep(0.1)
cursor.close()
conn.close()</code></pre>
<hr>
<h2>Testing Migrations</h2>
<h3>Local Testing</h3>
<p><strong>1. Test on production-like data</strong>:</p>
<pre><code class="lang-bash"># Dump production schema + sample data
pg_dump -Fc --schema-only production &gt; schema.dump
pg_dump -Fc --data-only --table=users --limit=10000 production &gt; data.dump
# Restore to local
createdb local_test
pg_restore -d local_test schema.dump
pg_restore -d local_test data.dump
# Apply migration
migrate -database &quot;postgres://localhost:5432/local_test&quot; up
# Verify schema
psql local_test -c &quot;\d users&quot;</code></pre>
<p><strong>2. Test rollback</strong>:</p>
<pre><code class="lang-bash"># Apply migration
migrate up
# Test rollback
migrate down
# Re-apply
migrate up
# Verify data integrity
psql local_test -c &quot;SELECT COUNT(*) FROM users&quot;</code></pre>
<p><strong>3. Test application against new schema</strong>:</p>
<pre><code class="lang-bash"># Point application to migrated database
export DATABASE_URL=&quot;postgres://localhost:5432/local_test&quot;
# Run tests
pytest tests/
npm test
go test ./...</code></pre>
<h3>Staging Testing</h3>
<p><strong>1. Mirror production</strong>:</p>
<pre><code class="lang-bash"># Refresh staging from production
pg_dump -Fc production | pg_restore -d staging
# Apply migration
migrate -database &quot;$STAGING_DB&quot; up</code></pre>
<p><strong>2. Run smoke tests</strong>:</p>
<pre><code class="lang-bash"># API smoke tests
curl https://staging.example.com/api/users
curl https://staging.example.com/api/health
# Load test (optional)
ab -n 1000 -c 10 https://staging.example.com/api/users</code></pre>
<p><strong>3. Monitor</strong>:</p>
<pre><code class="lang-sql">-- Check for slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Check for locks
SELECT * FROM pg_locks WHERE NOT granted;</code></pre>
<h3>CI/CD Testing</h3>
<p><strong>GitHub Actions example</strong>:</p>
<pre><code class="lang-yaml">name: Test Migrations
on: [push, pull_request]
jobs:
test-migrations:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:15
env:
POSTGRES_DB: testdb
POSTGRES_PASSWORD: postgres
options: &gt;-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
ports:
- 5432:5432
steps:
- uses: actions/checkout@v3
- name: Apply migrations
run: |
migrate -database &quot;postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable&quot; up
- name: Verify schema
run: |
psql postgres://postgres:postgres@localhost:5432/testdb -c &quot;\d users&quot;
- name: Test rollback
run: |
migrate -database &quot;postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable&quot; down
migrate -database &quot;postgres://postgres:postgres@localhost:5432/testdb?sslmode=disable&quot; up
- name: Run application tests
run: |
export DATABASE_URL=&quot;postgres://postgres:postgres@localhost:5432/testdb&quot;
pytest tests/</code></pre>
<hr>
<h2>Rollback Strategies</h2>
<h3>Strategy 1: Down Migrations</h3>
<p><strong>Approach</strong>: Write explicit rollback in down migration.</p>
<p><strong>Pros</strong>:</p>
<ul><li>Fast rollback</li><li>Built into migration tool</li><li>Easy to test</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Data loss if columns dropped</li><li>Not all changes reversible</li></ul>
<p><strong>Example</strong>:</p>
<pre><code class="lang-sql">-- 001_add_users.up.sql
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));
-- 001_add_users.down.sql
-- Example of dangerous rollback - loses data! Use data preservation strategies instead.
DROP TABLE users; -- ⚠️ Loses data!</code></pre>
<p><strong>Safe rollback with data preservation</strong>:</p>
<pre><code class="lang-sql">-- 002_add_phone.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 002_add_phone.down.sql
-- Instead of DROP, could archive to another table
CREATE TABLE IF NOT EXISTS users_phone_archive AS
SELECT id, phone FROM users WHERE phone IS NOT NULL;
ALTER TABLE users DROP COLUMN phone;</code></pre>
<h3>Strategy 2: Database Backups</h3>
<p><strong>Approach</strong>: Backup before migration, restore if needed.</p>
<pre><code class="lang-bash"># Before migration
pg_dump -Fc production &gt; backup_$(date +%Y%m%d_%H%M%S).dump
# Apply migration
migrate up
# If rollback needed
pg_restore -d production backup_20231027_120000.dump</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Complete rollback</li><li>Restores data</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Slow for large databases</li><li>Downtime during restore</li><li>Loses data written after migration</li></ul>
<h3>Strategy 3: Point-in-Time Recovery (PITR)</h3>
<p><strong>Approach</strong>: Use WAL archiving to recover to specific point in time.</p>
<p><strong>Setup</strong>:</p>
<pre><code class="lang-sql">-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = &#x27;cp %p /mnt/archive/%f&#x27;</code></pre>
<p><strong>Recovery</strong>:</p>
<pre><code class="lang-bash"># Stop database
pg_ctl stop
# ⚠️ WARNING: This permanently deletes all database data
# Always verify backups before running
# Restore base backup
rm -rf $PGDATA/*
tar -xzf base_backup.tar.gz -C $PGDATA
# Create recovery.conf (PG &lt; 12) or recovery.signal (PG 12+)
cat &gt; $PGDATA/recovery.signal &lt;&lt;EOF
restore_command = &#x27;cp /mnt/archive/%f %p&#x27;
recovery_target_time = &#x27;2023-10-27 12:00:00&#x27;
EOF
# Start database
pg_ctl start</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>Precise rollback</li><li>No data loss (up to target time)</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Requires WAL archiving setup</li><li>Complex recovery process</li><li>Downtime</li></ul>
<h3>Strategy 4: Blue-Green Deployment</h3>
<p><strong>Approach</strong>: Run two databases, switch traffic.</p>
<pre><code>Blue (current) ←─── Traffic
Green (with migrations)</code></pre>
<p><strong>Process</strong>:</p>
<ol><li>Clone Blue → Green</li><li>Apply migrations to Green</li><li>Switch traffic Blue → Green</li><li>If issues, switch back to Blue</li></ol>
<p><strong>Pros</strong>:</p>
<ul><li>Instant rollback</li><li>Zero downtime</li><li>Test migrations in production environment</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Requires load balancer/proxy</li><li>Data sync complexity</li><li>Expensive (two databases)</li></ul>
<h3>Strategy 5: Forward-Fix</h3>
<p><strong>Approach</strong>: Don&#x27;t roll back, fix forward with new migration.</p>
<p><strong>Example</strong>:</p>
<pre><code class="lang-sql">-- V5__add_age_column.sql (has bug: wrong type)
ALTER TABLE users ADD COLUMN age INT; -- Should be SMALLINT!
-- Don&#x27;t rollback, instead:
-- V6__fix_age_column_type.sql
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;</code></pre>
<p><strong>Pros</strong>:</p>
<ul><li>No data loss</li><li>Simple</li><li>Maintains migration history</li></ul>
<p><strong>Cons</strong>:</p>
<ul><li>Requires quick fix</li><li>Can&#x27;t undo schema changes immediately</li></ul>
<p><strong>Best for</strong>: Production systems with continuous deployment.</p>
<hr>
<h2>Common Pitfalls</h2>
<h3>Pitfall 1: Adding NOT NULL Without Default</h3>
<p><strong>Problem</strong>:</p>
<pre><code class="lang-sql">-- ❌ Fails on existing rows
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
-- ERROR: column &quot;phone&quot; contains null values</code></pre>
<p><strong>Fix</strong>:</p>
<pre><code class="lang-sql">-- ✅ Add with default, or add nullable then backfill
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT &#x27;unknown&#x27;;
-- Or multi-step:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
UPDATE users SET phone = &#x27;unknown&#x27; WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;</code></pre>
<hr>
<h3>Pitfall 2: Renaming Columns Directly</h3>
<p><strong>Problem</strong>:</p>
<pre><code class="lang-sql">-- ❌ Breaks old code immediately
ALTER TABLE users RENAME COLUMN email TO email_address;</code></pre>
<p><strong>Fix</strong>: Use expand-contract pattern (see Zero-Downtime Patterns).</p>
<hr>
<h3>Pitfall 3: Creating Indexes Without CONCURRENTLY</h3>
<p><strong>Problem</strong>:</p>
<pre><code class="lang-sql">-- ❌ Locks table, blocks writes
CREATE INDEX idx_users_email ON users(email);</code></pre>
<p><strong>Fix</strong>:</p>
<pre><code class="lang-sql">-- ✅ No lock
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);</code></pre>
<hr>
<h3>Pitfall 4: Large UPDATEs in One Transaction</h3>
<p><strong>Problem</strong>:</p>
<pre><code class="lang-sql">-- ❌ Locks millions of rows, can timeout
UPDATE users SET status = &#x27;active&#x27;; -- 10M rows</code></pre>
<p><strong>Fix</strong>: Batch with LIMIT and pg_sleep (see Data Migrations).</p>
<hr>
<h3>Pitfall 5: Changing Column Type Without Care</h3>
<p><strong>Problem</strong>:</p>
<pre><code class="lang-sql">-- ❌ Rewrites entire table, locks
ALTER TABLE users ALTER COLUMN email TYPE TEXT;</code></pre>
<p><strong>Fix</strong>: Check if rewrite needed, or use add/drop pattern.</p>
<pre><code class="lang-sql">-- Fast (no rewrite) in PostgreSQL
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
-- VARCHAR → TEXT is safe, no rewrite
-- Slow (rewrites table)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT USING age::BIGINT;</code></pre>
<hr>
<h3>Pitfall 6: Not Testing Rollback</h3>
<p><strong>Problem</strong>: Migration applied, can&#x27;t be reversed, data lost.</p>
<p><strong>Fix</strong>: Always test down migration locally.</p>
<pre><code class="lang-bash">migrate up
migrate down # Test rollback
migrate up # Re-apply</code></pre>
<hr>
<h3>Pitfall 7: Mixing Schema and Data Changes</h3>
<p><strong>Problem</strong>: Hard to debug, slow migrations.</p>
<pre><code class="lang-sql">-- ❌ Mixing concerns
ALTER TABLE users ADD COLUMN status VARCHAR(20);
UPDATE users SET status = &#x27;active&#x27; WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;</code></pre>
<p><strong>Fix</strong>: Separate migrations.</p>
<pre><code class="lang-sql">-- V1__add_status_column.sql (schema)
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- V2__backfill_status.sql (data)
UPDATE users SET status = &#x27;active&#x27; WHERE status IS NULL;
-- V3__make_status_not_null.sql (schema)
ALTER TABLE users ALTER COLUMN status SET NOT NULL;</code></pre>
<hr>
<h2>Advanced Topics</h2>
<h3>Handling Schema Drift</h3>
<p><strong>Problem</strong>: Production schema differs from migration history.</p>
<p><strong>Detection</strong>:</p>
<pre><code class="lang-bash"># Dump production schema
pg_dump --schema-only production &gt; production_schema.sql
# Apply migrations to clean database
migrate -database &quot;postgres://localhost/test&quot; up
pg_dump --schema-only test &gt; migrated_schema.sql
# Compare
diff production_schema.sql migrated_schema.sql</code></pre>
<p><strong>Fix</strong>:</p>
<pre><code class="lang-bash"># Option 1: Baseline (for tools supporting it)
flyway baseline -baselineVersion=10
# Option 2: Force version
migrate force 10
# Option 3: Manually add/remove from metadata table
INSERT INTO schema_migrations (version) VALUES (&#x27;20231027120000&#x27;);</code></pre>
<hr>
<h3>Multi-Tenancy Migrations</h3>
<p><strong>Strategy 1: Shared schema</strong>:</p>
<pre><code class="lang-sql">-- All tenants share same schema
-- Tenant ID in each table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
email VARCHAR(255)
);
-- Migration applies to all tenants
ALTER TABLE users ADD COLUMN phone VARCHAR(20);</code></pre>
<p><strong>Strategy 2: Schema-per-tenant</strong>:</p>
<pre><code class="lang-sql">-- Each tenant has own schema
CREATE SCHEMA tenant_123;
CREATE TABLE tenant_123.users (...);
CREATE SCHEMA tenant_456;
CREATE TABLE tenant_456.users (...);
-- Migration must run for each schema
DO $$
DECLARE
schema_name TEXT;
BEGIN
FOR schema_name IN
SELECT nspname FROM pg_namespace WHERE nspname LIKE &#x27;tenant_%&#x27;
LOOP
EXECUTE format(&#x27;ALTER TABLE %I.users ADD COLUMN phone VARCHAR(20)&#x27;, schema_name);
END LOOP;
END $$;</code></pre>
<hr>
<h3>Handling Enum Changes</h3>
<p><strong>Problem</strong>: Can&#x27;t modify enum values in place.</p>
<pre><code class="lang-sql">-- Existing enum
CREATE TYPE status_enum AS ENUM (&#x27;pending&#x27;, &#x27;active&#x27;);
-- ❌ Can&#x27;t add value to enum in one step in transaction
ALTER TYPE status_enum ADD VALUE &#x27;inactive&#x27;; -- Works, but not in transaction</code></pre>
<p><strong>Safe approach</strong>:</p>
<pre><code class="lang-sql">-- Step 1: Create new enum
CREATE TYPE status_enum_new AS ENUM (&#x27;pending&#x27;, &#x27;active&#x27;, &#x27;inactive&#x27;);
-- Step 2: Add new column with new enum
ALTER TABLE users ADD COLUMN status_new status_enum_new;
-- Step 3: Migrate data
UPDATE users SET status_new = status::TEXT::status_enum_new;
-- Step 4: Drop old column, rename new
ALTER TABLE users DROP COLUMN status;
ALTER TABLE users RENAME COLUMN status_new TO status;
-- Step 5: Drop old enum
DROP TYPE status_enum;
-- Step 6: Rename new enum (optional)
ALTER TYPE status_enum_new RENAME TO status_enum;</code></pre>
<hr>
<h3>Partitioned Tables</h3>
<p><strong>Adding partitions</strong>:</p>
<pre><code class="lang-sql">-- Existing partitioned table
CREATE TABLE events (
id BIGSERIAL,
event_date DATE,
data JSONB
) PARTITION BY RANGE (event_date);
-- Add new partition (safe, no lock on parent)
CREATE TABLE events_2023_11 PARTITION OF events
FOR VALUES FROM (&#x27;2023-11-01&#x27;) TO (&#x27;2023-12-01&#x27;);</code></pre>
<p><strong>Detaching partitions</strong>:</p>
<pre><code class="lang-sql">-- Detach old partition (for archiving)
ALTER TABLE events DETACH PARTITION events_2023_01;
-- Archive
\copy events_2023_01 TO &#x27;/archive/events_2023_01.csv&#x27; CSV
-- Drop partition after archiving (safe cleanup operation)
DROP TABLE events_2023_01;</code></pre>
<hr>
<h3>Managing Long-Running Migrations</h3>
<p><strong>Problem</strong>: Migration takes hours, blocks deployments.</p>
<p><strong>Solution</strong>: Run migrations outside deployment.</p>
<p><strong>Workflow</strong>:</p>
<ol><li>Run migration manually during low-traffic window</li><li>Mark as applied in migration metadata</li><li>Deploy application (skips already-applied migration)</li></ol>
<p><strong>Example</strong>:</p>
<pre><code class="lang-bash"># Manually apply slow migration
psql production &lt; migrations/V5__add_large_index.sql
# Mark as applied
INSERT INTO schema_migrations (version, description, success)
VALUES (&#x27;V5&#x27;, &#x27;add_large_index&#x27;, TRUE);
# Deploy application
# Migration tool sees V5 already applied, skips it</code></pre>
<hr>
<p><strong>End of Reference</strong></p>
<p>This reference covers PostgreSQL migrations comprehensively. For practical scripts and examples, see the <code>scripts/</code> and <code>examples/</code> directories.</p>
<footer>
<div class="fmap">
<div><b>Build</b>
<a href="https://hanzo.app">hanzo.app</a> app builder
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
<div><b>Run</b>
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
<div><b>Account</b>
<a href="https://hanzo.id">hanzo.id</a> sign in
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
<div><b>Company</b>
<a href="https://hanzo.ai">hanzo.ai</a> canonical
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
</div>
<div class="fend"><a href="/reference.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
</footer>
</div></body></html>