PostgreSQL partial indexes for filtered queries

<p>I have a PostgreSQL table where most queries filter on a specific condition (e.g., status='validated'). Creating a full index wastes space on non-matching rows and slows down writes. I want to create a partial index that only covers the subset of rows I actually query.</p>

<p>Create indexes with WHERE clause to cover only relevant rows:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Partial index: only validated traces (not pending)</span> <span class="c1">-- If 90% of queries filter WHERE status='validated', this index is 90% smaller</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">CONCURRENTLY</span><span class="w"> </span><span class="n">ix_traces_validated_embedding</span> <span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="p">(</span><span class="n">created_at</span><span class="w"> </span><span class="k">DESC</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">embedding</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> <span class="c1">-- For NULL checks (embedding queue):</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">CONCURRENTLY</span><span class="w"> </span><span class="n">ix_traces_pending_embed</span> <span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="p">(</span><span class="n">created_at</span><span class="w"> </span><span class="k">ASC</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">embedding</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="p">;</span> <span class="c1">-- Composite partial index for search:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">CONCURRENTLY</span><span class="w"> </span><span class="n">ix_traces_seed_lookup</span> <span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="p">(</span><span class="n">title</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">is_seed</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">TRUE</span><span class="p">;</span> </code></pre></div> <p>Verify the planner uses your partial index:</p> <div class="highlight"><pre><span></span><code><span class="k">EXPLAIN</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">traces</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">embedding</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span> <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">created_at</span><span class="w"> </span><span class="k">DESC</span><span class="w"> </span><span class="k">LIMIT</span><span class="w"> </span><span class="mi">10</span><span class="p">;</span> <span class="c1">-- Should show: Index Scan using ix_traces_validated_embedding</span> <span class="c1">-- Force index usage for testing (bypass planner heuristics):</span> <span class="k">SET</span><span class="w"> </span><span class="n">enable_seqscan</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">off</span><span class="p">;</span> <span class="k">EXPLAIN</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="p">...;</span> <span class="k">SET</span><span class="w"> </span><span class="n">enable_seqscan</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">on</span><span class="p">;</span> </code></pre></div> <p>Alembic migration:</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="n">op</span><span class="o">.</span><span class="n">create_index</span><span class="p">(</span> <span class="s1">'ix_traces_pending_embed'</span><span class="p">,</span> <span class="s1">'traces'</span><span class="p">,</span> <span class="p">[</span><span class="s1">'created_at'</span><span class="p">],</span> <span class="n">postgresql_where</span><span class="o">=</span><span class="s2">"embedding IS NULL AND status = 'validated'"</span><span class="p">,</span> <span class="p">)</span> </code></pre></div> <p>Key points: - Partial index size = full index size * (fraction of matching rows) - Query WHERE clause must match index WHERE clause for planner to use it - Partial indexes update faster than full indexes (fewer rows to maintain) - Use <code>CONCURRENTLY</code> in production — avoids <code>ACCESS EXCLUSIVE</code> table lock</p>