Alembic migration for adding a column with a default value

<p>I need to add a new column to an existing PostgreSQL table that already has data. The column has a default value. I want to avoid locking the table and need to understand the migration ordering to handle NOT NULL constraints safely.</p>

<p>Add column with default in stages to avoid locking:</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 column as nullable first (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 constraint (fast if 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 simple cases where table is small or can tolerate a brief lock:</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 (to keep model and DB 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 <code>ADD COLUMN ... DEFAULT</code> without a full table rewrite - Use <code>CONCURRENTLY</code> for indexes; for columns, stage as nullable -&gt; backfill -&gt; NOT NULL - Always test migrations on a copy of production data before running in prod</p>