PostgreSQL JSONB indexing and querying patterns

<p>Storing metadata as JSONB columns for flexibility, but queries like <code>WHERE data-&gt;&gt;'status' = 'active'</code> do full table scans. Need to index JSONB fields and write efficient queries.</p>

<p>Use GIN indexes for containment queries, expression indexes for specific key access:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- GIN index for containment @&gt; operator</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_metadata_gin</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">events</span><span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="n">GIN</span><span class="p">(</span><span class="n">metadata</span><span class="p">);</span> <span class="c1">-- Expression index for specific key (more efficient when querying one key)</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_metadata_status</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">events</span><span class="w"> </span><span class="p">((</span><span class="n">metadata</span><span class="o">-&gt;&gt;</span><span class="s1">'status'</span><span class="p">));</span> <span class="c1">-- Containment query (uses GIN)</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">events</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata</span><span class="w"> </span><span class="o">@&gt;</span><span class="w"> </span><span class="s1">'{"status": "active"}'</span><span class="p">;</span> <span class="c1">-- Key access query (uses expression index)</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">events</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata</span><span class="o">-&gt;&gt;</span><span class="s1">'status'</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'active'</span><span class="p">;</span> <span class="c1">-- Nested key access</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">events</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata</span><span class="o">-&gt;</span><span class="s1">'user'</span><span class="o">-&gt;&gt;</span><span class="s1">'email'</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">'%@example.com'</span><span class="p">;</span> <span class="c1">-- JSONB array containment</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">events</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata</span><span class="o">-&gt;</span><span class="s1">'tags'</span><span class="w"> </span><span class="o">?</span><span class="w"> </span><span class="s1">'python'</span><span class="p">;</span> <span class="c1">-- Update specific key</span> <span class="k">UPDATE</span><span class="w"> </span><span class="n">events</span> <span class="k">SET</span><span class="w"> </span><span class="n">metadata</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">jsonb_set</span><span class="p">(</span><span class="n">metadata</span><span class="p">,</span><span class="w"> </span><span class="s1">'{status}'</span><span class="p">,</span><span class="w"> </span><span class="s1">'"archived"'</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">1</span><span class="p">;</span> </code></pre></div> <p>In SQLAlchemy:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">cast</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.dialects.postgresql</span><span class="w"> </span><span class="kn">import</span> <span class="n">JSONB</span> <span class="c1"># Containment</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Event</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Event</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">contains</span><span class="p">({</span><span class="s1">'status'</span><span class="p">:</span> <span class="s1">'active'</span><span class="p">}))</span> <span class="c1"># Key access</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Event</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Event</span><span class="o">.</span><span class="n">metadata</span><span class="p">[</span><span class="s1">'status'</span><span class="p">]</span><span class="o">.</span><span class="n">astext</span> <span class="o">==</span> <span class="s1">'active'</span><span class="p">)</span> <span class="c1"># Type cast for comparison</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Event</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span> <span class="n">Event</span><span class="o">.</span><span class="n">metadata</span><span class="p">[</span><span class="s1">'priority'</span><span class="p">]</span><span class="o">.</span><span class="n">as_integer</span><span class="p">()</span> <span class="o">&gt;</span> <span class="mi">3</span> <span class="p">)</span> </code></pre></div> <p>Rule: GIN for <code>@&gt;</code>, <code>?</code>, <code>?|</code>, <code>?&amp;</code> (containment/existence). Expression index for <code>-&gt;&gt;'key'</code> equality. Never index the entire JSONB column with btree.</p>