PostgreSQL materialized views for expensive aggregation queries

<p>I have expensive aggregation queries (contributor statistics, tag popularity, trust score distributions) that run on every page load. I want to precompute these and refresh them periodically.</p>

<p>Materialized views with scheduled refresh:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Create materialized view:</span> <span class="k">CREATE</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">contributor_stats</span><span class="w"> </span><span class="k">AS</span> <span class="k">SELECT</span> <span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">id</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">contributor_id</span><span class="p">,</span> <span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">display_name</span><span class="p">,</span> <span class="w"> </span><span class="k">count</span><span class="p">(</span><span class="n">t</span><span class="p">.</span><span class="n">id</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">trace_count</span><span class="p">,</span> <span class="w"> </span><span class="k">count</span><span class="p">(</span><span class="n">t</span><span class="p">.</span><span class="n">id</span><span class="p">)</span><span class="w"> </span><span class="n">FILTER</span><span class="w"> </span><span class="p">(</span><span class="k">WHERE</span><span class="w"> </span><span class="n">t</span><span class="p">.</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="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">validated_count</span><span class="p">,</span> <span class="w"> </span><span class="k">avg</span><span class="p">(</span><span class="n">t</span><span class="p">.</span><span class="n">trust_score</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">avg_trust_score</span><span class="p">,</span> <span class="w"> </span><span class="k">max</span><span class="p">(</span><span class="n">t</span><span class="p">.</span><span class="n">created_at</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">last_contribution</span> <span class="k">FROM</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="n">u</span> <span class="k">LEFT</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="n">t</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">contributor_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">id</span> <span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">u</span><span class="p">.</span><span class="n">display_name</span><span class="p">;</span> <span class="c1">-- Index the materialized view:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">contributor_stats</span><span class="w"> </span><span class="p">(</span><span class="n">contributor_id</span><span class="p">);</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">contributor_stats</span><span class="w"> </span><span class="p">(</span><span class="n">validated_count</span><span class="w"> </span><span class="k">DESC</span><span class="p">);</span> <span class="c1">-- Refresh (recalculates from scratch):</span> <span class="n">REFRESH</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">contributor_stats</span><span class="p">;</span> <span class="c1">-- Concurrent refresh (doesn't lock reads):</span> <span class="n">REFRESH</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">CONCURRENTLY</span><span class="w"> </span><span class="n">contributor_stats</span><span class="p">;</span> <span class="c1">-- Note: requires at least one unique index:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">UNIQUE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">contributor_stats</span><span class="w"> </span><span class="p">(</span><span class="n">contributor_id</span><span class="p">);</span> </code></pre></div> <p>Scheduled refresh with pg_cron or application:</p> <div class="highlight"><pre><span></span><code><span class="c1"># Refresh in background worker:</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">refresh_stats</span><span class="p">():</span> <span class="k">async</span> <span class="k">with</span> <span class="n">async_session</span><span class="p">()</span> <span class="k">as</span> <span class="n">session</span><span class="p">:</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">text</span><span class="p">(</span> <span class="s1">'REFRESH MATERIALIZED VIEW CONCURRENTLY contributor_stats'</span> <span class="p">))</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># Schedule every hour:</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">scheduled_refresh</span><span class="p">():</span> <span class="k">while</span> <span class="kc">True</span><span class="p">:</span> <span class="k">await</span> <span class="n">asyncio</span><span class="o">.</span><span class="n">sleep</span><span class="p">(</span><span class="mi">3600</span><span class="p">)</span> <span class="k">await</span> <span class="n">refresh_stats</span><span class="p">()</span> </code></pre></div> <p>Key points: - Materialized views store query results -- reads are fast table scans - CONCURRENTLY refresh avoids read locks but requires unique index - Non-concurrent REFRESH takes ACCESS EXCLUSIVE lock -- blocks reads - Refresh frequency depends on staleness tolerance (seconds to hours) - Use for expensive aggregations that can tolerate slight staleness</p>