PostgreSQL JSONB for flexible metadata storage

<p>I have trace metadata that varies by domain (language version, framework name, OS). I don't want to add columns for every possible field. I need JSONB storage with indexing for specific keys.</p>

<p>JSONB for flexible schema with indexing:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Column definition:</span> <span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">ADD</span><span class="w"> </span><span class="k">COLUMN</span><span class="w"> </span><span class="n">metadata_json</span><span class="w"> </span><span class="n">JSONB</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="s1">'{}'</span><span class="p">;</span> <span class="c1">-- GIN index for any-key queries:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">ix_traces_metadata</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</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_json</span><span class="p">);</span> <span class="c1">-- Specific key index (faster for targeted queries):</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">ix_traces_language</span><span class="w"> </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">metadata_json</span><span class="o">-&gt;&gt;</span><span class="s1">'language'</span><span class="p">));</span> <span class="c1">-- Queries:</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="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata_json</span><span class="w"> </span><span class="o">@&gt;</span><span class="w"> </span><span class="s1">'{"language": "python"}'</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Contains</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="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata_json</span><span class="o">-&gt;&gt;</span><span class="s1">'language'</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'python'</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Key value</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="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">metadata_json</span><span class="w"> </span><span class="o">?</span><span class="w"> </span><span class="s1">'language'</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Key exists</span> <span class="c1">-- Update specific key:</span> <span class="k">UPDATE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">metadata_json</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">metadata_json</span><span class="w"> </span><span class="o">||</span><span class="w"> </span><span class="s1">'{"verified": true}'</span><span class="w"> </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> <span class="c1">-- Extract with default:</span> <span class="k">SELECT</span><span class="w"> </span><span class="k">COALESCE</span><span class="p">(</span><span class="n">metadata_json</span><span class="o">-&gt;&gt;</span><span class="s1">'language'</span><span class="p">,</span><span class="w"> </span><span class="s1">'unknown'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">traces</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.dialects.postgresql</span><span class="w"> </span><span class="kn">import</span> <span class="n">JSONB</span> <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="k">class</span><span class="w"> </span><span class="nc">Trace</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">metadata_json</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">dict</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">JSONB</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="nb">dict</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"># Query by JSONB key:</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span> <span class="n">Trace</span><span class="o">.</span><span class="n">metadata_json</span><span class="p">[</span><span class="s1">'language'</span><span class="p">]</span><span class="o">.</span><span class="n">as_string</span><span class="p">()</span> <span class="o">==</span> <span class="s1">'python'</span> <span class="p">)</span> <span class="c1"># Contains operator:</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span> <span class="n">Trace</span><span class="o">.</span><span class="n">metadata_json</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'@&gt;'</span><span class="p">)({</span><span class="s2">"language"</span><span class="p">:</span> <span class="s2">"python"</span><span class="p">})</span> <span class="p">)</span> </code></pre></div> <p>Key points: - JSONB stores parsed binary -- faster queries than JSON (text) - GIN index enables @&gt; (contains) queries - Specific key indexes for frequently filtered keys - @&gt; (contains) is indexable; -&gt;&gt; (extract text) uses functional index - Use JSONB for metadata; use columns for fields you filter/sort on frequently</p>