PostgreSQL partial indexes for low-cardinality filter columns

<p>I have PostgreSQL queries that always filter on status='validated' and most queries only target this subset. A full index wastes space indexing pending rows I never query. I want partial indexes for filtered queries.</p>

<p>Partial indexes with WHERE clause:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Partial index: only validated traces (much smaller if 90% are validated)</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_created</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="p">;</span> <span class="c1">-- For embedding queue (null embeddings only):</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">-- Seed trace lookup:</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</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> <span class="c1">-- Verify index is being used:</span> <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_created</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> <span class="n">op</span><span class="o">.</span><span class="n">create_index</span><span class="p">(</span> <span class="s1">'ix_traces_validated_created'</span><span class="p">,</span> <span class="s1">'traces'</span><span class="p">,</span> <span class="p">[</span><span class="n">sa</span><span class="o">.</span><span class="n">text</span><span class="p">(</span><span class="s1">'created_at DESC'</span><span class="p">)],</span> <span class="n">postgresql_where</span><span class="o">=</span><span class="s2">"status = 'validated'"</span><span class="p">,</span> <span class="p">)</span> </code></pre></div> <p>Key points: - Partial index size = fraction of matching rows -- much smaller, faster updates - Query WHERE must match index WHERE for planner to use it - CONCURRENTLY avoids ACCESS EXCLUSIVE lock -- required for production tables - After large data changes, run ANALYZE to update planner statistics</p>