Alembic migration for adding column with default to existing table

<p>I need to add a new NOT NULL column to an existing PostgreSQL table that has data. I need to avoid table locking in production and understand the correct staging for nullable -&gt; backfill -&gt; NOT NULL.</p>

<p>Staged column addition to avoid locks:</p> <div class="highlight"><pre><span></span><code><span class="c1"># migrations/versions/0005_add_is_seed_column.py</span> <span class="kn">from</span><span class="w"> </span><span class="nn">alembic</span><span class="w"> </span><span class="kn">import</span> <span class="n">op</span> <span class="kn">import</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="nn">sa</span> <span class="k">def</span><span class="w"> </span><span class="nf">upgrade</span><span class="p">()</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="c1"># Step 1: Add as nullable (no lock, no backfill needed)</span> <span class="n">op</span><span class="o">.</span><span class="n">add_column</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'is_seed'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Boolean</span><span class="p">(),</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span> <span class="c1"># Step 2: Backfill existing rows</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"UPDATE traces SET is_seed = FALSE WHERE is_seed IS NULL"</span><span class="p">)</span> <span class="c1"># Step 3: Set NOT NULL (fast -- no nulls exist)</span> <span class="n">op</span><span class="o">.</span><span class="n">alter_column</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="s1">'is_seed'</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span> <span class="c1"># Step 4: Set server default for future inserts</span> <span class="n">op</span><span class="o">.</span><span class="n">alter_column</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="s1">'is_seed'</span><span class="p">,</span> <span class="n">server_default</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">false</span><span class="p">())</span> <span class="k">def</span><span class="w"> </span><span class="nf">downgrade</span><span class="p">()</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="n">op</span><span class="o">.</span><span class="n">drop_column</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="s1">'is_seed'</span><span class="p">)</span> </code></pre></div> <p>For small tables (safe to lock briefly):</p> <div class="highlight"><pre><span></span><code><span class="k">def</span><span class="w"> </span><span class="nf">upgrade</span><span class="p">()</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="n">op</span><span class="o">.</span><span class="n">add_column</span><span class="p">(</span> <span class="s1">'traces'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'is_seed'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Boolean</span><span class="p">(),</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">server_default</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">false</span><span class="p">())</span> <span class="p">)</span> <span class="c1"># Remove server_default after migration (keep ORM in sync):</span> <span class="n">op</span><span class="o">.</span><span class="n">alter_column</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="s1">'is_seed'</span><span class="p">,</span> <span class="n">server_default</span><span class="o">=</span><span class="kc">None</span><span class="p">)</span> </code></pre></div> <p>Key points: - Adding NOT NULL column with default backfills all rows and locks table in PG &lt; 11 - PostgreSQL 11+ supports ADD COLUMN ... DEFAULT without full table rewrite - Stage as nullable -&gt; backfill -&gt; NOT NULL for zero-downtime on large tables - Always test migrations on a copy of production data first</p>