Materialized Views - Pre-Computed Query Results | Tags

<h2 id="materialized-views" class="position-relative d-flex align-items-center group"> Materialized Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="materialized-views" aria-haspopup="dialog" aria-label="Share link: Materialized Views"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Materialized views are pre-computed query results stored as physical data, providing orders-of-magnitude performance improvements for complex analytical queries. Unlike regular views that execute queries on-demand, materialized views cache results persistently, trading storage space and update complexity for dramatically faster read access. Essential for OLAP workloads, reporting dashboards, and data warehousing scenarios. <h3 id="understanding-materialized-views" class="position-relative d-flex align-items-center group"> Understanding Materialized Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="understanding-materialized-views" aria-haspopup="dialog" aria-label="Share link: Understanding Materialized Views"> Share link </button> </h3> <h4 id="what-are-materialized-views" class="position-relative d-flex align-items-center group"> What are Materialized Views? <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-are-materialized-views" aria-haspopup="dialog" aria-label="Share link: What are Materialized Views?"> Share link </button> </h4>Materialized views transform query execution: Pre-Computed - Results calculated ahead of time during refresh Persistently Stored - Saved to disk like regular tables or nodes Indexable - Support standard indexes for fast lookups Refreshable - Updated when source data changes via manual, scheduled, or incremental refresh Query-Transparent - Can be queried like regular tables <h4 id="benefits-over-regular-views" class="position-relative d-flex align-items-center group"> Benefits Over Regular Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="benefits-over-regular-views" aria-haspopup="dialog" aria-label="Share link: Benefits Over Regular Views"> Share link </button> </h4><table> <thead> <tr> <th>Aspect</th> <th>Regular View</th> <th>Materialized View</th> </tr> </thead> <tbody> <tr> <td>Query Speed</td> <td>Slow (recompute)</td> <td>Fast (pre-computed)</td> </tr> <tr> <td>Storage</td> <td>None</td> <td>Moderate to high</td> </tr> <tr> <td>Freshness</td> <td>Always current</td> <td>Depends on refresh</td> </tr> <tr> <td>Indexes</td> <td>No</td> <td>Yes</td> </tr> <tr> <td>Use Case</td> <td>Simple queries</td> <td>Complex aggregations</td> </tr> </tbody> </table> <h4 id="when-to-use-materialized-views" class="position-relative d-flex align-items-center group"> When to Use Materialized Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="when-to-use-materialized-views" aria-haspopup="dialog" aria-label="Share link: When to Use Materialized Views"> Share link </button> </h4>Ideal Scenarios: <ul> <li>Complex aggregations across millions of rows</li> <li>Multi-table joins with expensive computations</li> <li>Frequently accessed reports and dashboards</li> <li>Historical snapshots and time-series rollups</li> <li>OLAP cubes and dimensional analytics</li> </ul> Not Recommended For: <ul> <li>Transactional queries requiring real-time data</li> <li>Infrequently accessed queries</li> <li>Simple single-table lookups</li> <li>Data that changes constantly</li> </ul> <h3 id="creating-materialized-views" class="position-relative d-flex align-items-center group"> Creating Materialized Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-materialized-views" aria-haspopup="dialog" aria-label="Share link: Creating Materialized Views"> Share link </button> </h3> <h4 id="basic-syntax" class="position-relative d-flex align-items-center group"> Basic Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="basic-syntax" aria-haspopup="dialog" aria-label="Share link: Basic Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create materialized view for user statistics CREATE MATERIALIZED VIEW user_stats AS SELECT u.id AS user_id, u.name AS user_name, u.email, COUNT(DISTINCT p.id) AS post_count, COUNT(DISTINCT c.id) AS comment_count, COUNT(DISTINCT l.id) AS like_count, MAX(p.created_at) AS last_post_date, MIN(u.join_date) AS join_date FROM User u LEFT JOIN Post p ON u.id = p.author_id LEFT JOIN Comment c ON u.id = c.author_id LEFT JOIN Like l ON u.id = l.user_id GROUP BY u.id, u.name, u.email; -- Query materialized view (fast!) SELECT * FROM user_stats WHERE post_count > 100 ORDER BY like_count DESC LIMIT 10; </code></pre></div> <h4 id="with-indexes-for-performance" class="position-relative d-flex align-items-center group"> With Indexes for Performance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="with-indexes-for-performance" aria-haspopup="dialog" aria-label="Share link: With Indexes for Performance"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE MATERIALIZED VIEW product_sales_summary AS SELECT p.id AS product_id, p.name AS product_name, p.category, COUNT(DISTINCT o.id) AS order_count, SUM(oi.quantity) AS total_units_sold, SUM(oi.quantity * oi.unit_price) AS total_revenue, AVG(oi.unit_price) AS avg_price, MAX(o.created_at) AS last_sold_date FROM Product p LEFT JOIN OrderItem oi ON p.id = oi.product_id LEFT JOIN Order o ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY p.id, p.name, p.category; -- Add indexes for common query patterns CREATE INDEX idx_product_sales_category ON product_sales_summary(category); CREATE INDEX idx_product_sales_revenue ON product_sales_summary(total_revenue DESC); CREATE INDEX idx_product_sales_units ON product_sales_summary(total_units_sold DESC); -- Fast filtered queries SELECT * FROM product_sales_summary WHERE category = 'Electronics' AND total_revenue > 100000 ORDER BY total_units_sold DESC; </code></pre></div> <h3 id="refresh-strategies" class="position-relative d-flex align-items-center group"> Refresh Strategies <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="refresh-strategies" aria-haspopup="dialog" aria-label="Share link: Refresh Strategies"> Share link </button> </h3> <h4 id="manual-refresh" class="position-relative d-flex align-items-center group"> Manual Refresh <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="manual-refresh" aria-haspopup="dialog" aria-label="Share link: Manual Refresh"> Share link </button> </h4>Explicitly refresh when needed: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Full refresh (recompute everything) REFRESH MATERIALIZED VIEW user_stats; -- Concurrent refresh (non-blocking, allows queries during refresh) REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats; -- Check last refresh time SELECT view_name, last_refresh, row_count FROM system.materialized_views WHERE view_name = 'user_stats'; </code></pre></div> <h4 id="automatic-scheduled-refresh" class="position-relative d-flex align-items-center group"> Automatic Scheduled Refresh <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="automatic-scheduled-refresh" aria-haspopup="dialog" aria-label="Share link: Automatic Scheduled Refresh"> Share link </button> </h4>Configure automatic refresh schedules: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Refresh daily at 1 AM CREATE MATERIALIZED VIEW daily_revenue AS SELECT DATE(o.created_at) AS order_date, COUNT(*) AS order_count, SUM(o.total) AS total_revenue, AVG(o.total) AS avg_order_value, COUNT(DISTINCT o.customer_id) AS unique_customers FROM Order o WHERE o.status = 'completed' GROUP BY DATE(o.created_at) WITH REFRESH SCHEDULE '0 1 * * *'; -- Cron syntax -- Refresh every hour CREATE MATERIALIZED VIEW hourly_metrics AS SELECT DATE_TRUNC('hour', timestamp) AS hour, metric_name, AVG(value) AS avg_value, MAX(value) AS max_value, MIN(value) AS min_value, COUNT(*) AS sample_count FROM metrics GROUP BY DATE_TRUNC('hour', timestamp), metric_name WITH REFRESH SCHEDULE '0 * * * *'; -- Every hour on the hour </code></pre></div> <h4 id="incremental-refresh" class="position-relative d-flex align-items-center group"> Incremental Refresh <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="incremental-refresh" aria-haspopup="dialog" aria-label="Share link: Incremental Refresh"> Share link </button> </h4>Update only changed data for efficiency: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE MATERIALIZED VIEW product_inventory WITH INCREMENTAL REFRESH AS SELECT p.id AS product_id, p.name, p.stock_quantity, COALESCE(SUM( CASE WHEN oi.order_status = 'pending' THEN oi.quantity ELSE 0 END ), 0) AS reserved_quantity, p.stock_quantity - COALESCE(SUM( CASE WHEN oi.order_status = 'pending' THEN oi.quantity ELSE 0 END ), 0) AS available_quantity, COUNT(DISTINCT CASE WHEN oi.order_status = 'pending' THEN oi.order_id END) AS pending_orders FROM Product p LEFT JOIN OrderItem oi ON p.id = oi.product_id GROUP BY p.id, p.name, p.stock_quantity; -- Incremental refresh only updates changed products REFRESH MATERIALIZED VIEW INCREMENTALLY product_inventory; </code></pre></div> <h4 id="refresh-on-write-eager-materialization" class="position-relative d-flex align-items-center group"> Refresh on Write (Eager Materialization) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="refresh-on-write-eager-materialization" aria-haspopup="dialog" aria-label="Share link: Refresh on Write (Eager Materialization)"> Share link </button> </h4>Automatically refresh when source data changes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE MATERIALIZED VIEW current_inventory WITH REFRESH ON WRITE AS SELECT p.id, p.name, p.stock_quantity, p.stock_quantity - COALESCE(reserved.qty, 0) AS available FROM Product p LEFT JOIN ( SELECT product_id, SUM(quantity) AS qty FROM OrderItem WHERE order_status = 'pending' GROUP BY product_id ) reserved ON p.id = reserved.product_id; -- View automatically updates when: -- - Product stock_quantity changes -- - OrderItem is created/updated/deleted -- - Order status changes </code></pre></div> <h3 id="real-world-analytics-patterns" class="position-relative d-flex align-items-center group"> Real-World Analytics Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="real-world-analytics-patterns" aria-haspopup="dialog" aria-label="Share link: Real-World Analytics Patterns"> Share link </button> </h3> <h4 id="time-series-rollups" class="position-relative d-flex align-items-center group"> Time-Series Rollups <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="time-series-rollups" aria-haspopup="dialog" aria-label="Share link: Time-Series Rollups"> Share link </button> </h4>Create hierarchical time aggregations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Hourly rollup (base level) CREATE MATERIALIZED VIEW metrics_hourly AS SELECT DATE_TRUNC('hour', timestamp) AS hour, sensor_id, metric_type, AVG(value) AS avg_value, MIN(value) AS min_value, MAX(value) AS max_value, STDDEV(value) AS stddev_value, COUNT(*) AS sample_count FROM sensor_readings GROUP BY DATE_TRUNC('hour', timestamp), sensor_id, metric_type WITH REFRESH SCHEDULE '5 * * * *'; -- 5 minutes past each hour -- Daily rollup (aggregates hourly data) CREATE MATERIALIZED VIEW metrics_daily AS SELECT DATE_TRUNC('day', hour) AS day, sensor_id, metric_type, AVG(avg_value) AS avg_value, MIN(min_value) AS min_value, MAX(max_value) AS max_value, AVG(stddev_value) AS avg_stddev, SUM(sample_count) AS total_samples FROM metrics_hourly GROUP BY DATE_TRUNC('day', hour), sensor_id, metric_type WITH REFRESH SCHEDULE '0 2 * * *'; -- 2 AM daily -- Monthly rollup (aggregates daily data) CREATE MATERIALIZED VIEW metrics_monthly AS SELECT DATE_TRUNC('month', day) AS month, sensor_id, metric_type, AVG(avg_value) AS avg_value, MIN(min_value) AS min_value, MAX(max_value) AS max_value, SUM(total_samples) AS total_samples FROM metrics_daily GROUP BY DATE_TRUNC('month', day), sensor_id, metric_type WITH REFRESH SCHEDULE '0 3 1 * *'; -- 3 AM on 1st of month </code></pre></div> <h4 id="customer-segmentation" class="position-relative d-flex align-items-center group"> Customer Segmentation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="customer-segmentation" aria-haspopup="dialog" aria-label="Share link: Customer Segmentation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE MATERIALIZED VIEW customer_segments AS SELECT c.id AS customer_id, c.name, c.email, COUNT(DISTINCT o.id) AS order_count, SUM(o.total) AS lifetime_value, AVG(o.total) AS avg_order_value, MAX(o.created_at) AS last_order_date, DATEDIFF(DAY, MAX(o.created_at), NOW()) AS days_since_last_order, DATEDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS customer_lifetime_days, -- Segment classification CASE WHEN SUM(o.total) > 10000 AND DATEDIFF(DAY, MAX(o.created_at), NOW()) < 90 THEN 'vip_active' WHEN SUM(o.total) > 10000 AND DATEDIFF(DAY, MAX(o.created_at), NOW()) >= 90 THEN 'vip_at_risk' WHEN SUM(o.total) > 1000 AND DATEDIFF(DAY, MAX(o.created_at), NOW()) < 180 THEN 'loyal' WHEN DATEDIFF(DAY, MAX(o.created_at), NOW()) >= 365 THEN 'churned' WHEN COUNT(DISTINCT o.id) = 1 THEN 'one_time' ELSE 'regular' END AS segment, -- RFM score (Recency, Frequency, Monetary) NTILE(5) OVER (ORDER BY MAX(o.created_at) DESC) AS recency_score, NTILE(5) OVER (ORDER BY COUNT(DISTINCT o.id)) AS frequency_score, NTILE(5) OVER (ORDER BY SUM(o.total)) AS monetary_score FROM Customer c LEFT JOIN Order o ON c.id = o.customer_id WHERE o.status = 'completed' GROUP BY c.id, c.name, c.email WITH REFRESH SCHEDULE '0 0 * * 0'; -- Weekly on Sunday midnight </code></pre></div> <h4 id="product-performance-dashboard" class="position-relative d-flex align-items-center group"> Product Performance Dashboard <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="product-performance-dashboard" aria-haspopup="dialog" aria-label="Share link: Product Performance Dashboard"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE MATERIALIZED VIEW product_performance_dashboard AS SELECT p.id AS product_id, p.name AS product_name, p.category, p.base_price, -- Sales metrics COUNT(DISTINCT o.id) AS orders_with_product, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS gross_revenue, SUM(oi.quantity * p.cost) AS total_cost, SUM(oi.quantity * oi.unit_price) - SUM(oi.quantity * p.cost) AS gross_profit, (SUM(oi.quantity * oi.unit_price) - SUM(oi.quantity * p.cost)) / NULLIF(SUM(oi.quantity * oi.unit_price), 0) * 100 AS profit_margin, -- Customer metrics COUNT(DISTINCT o.customer_id) AS unique_customers, AVG(oi.quantity) AS avg_quantity_per_order, -- Time metrics MIN(o.created_at) AS first_sale_date, MAX(o.created_at) AS last_sale_date, DATEDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS days_on_market, -- Review metrics COUNT(DISTINCT r.id) AS review_count, AVG(r.rating) AS avg_rating, -- Inventory metrics p.stock_quantity AS current_stock, CASE WHEN p.stock_quantity = 0 THEN 'out_of_stock' WHEN p.stock_quantity < 10 THEN 'low_stock' WHEN p.stock_quantity < 50 THEN 'moderate_stock' ELSE 'well_stocked' END AS stock_status, -- Performance classification CASE WHEN SUM(oi.quantity * oi.unit_price) > 100000 THEN 'top_performer' WHEN SUM(oi.quantity * oi.unit_price) > 10000 THEN 'strong_seller' WHEN SUM(oi.quantity * oi.unit_price) > 1000 THEN 'moderate_seller' WHEN SUM(oi.quantity) > 0 THEN 'slow_mover' ELSE 'no_sales' END AS performance_tier FROM Product p LEFT JOIN OrderItem oi ON p.id = oi.product_id LEFT JOIN Order o ON oi.order_id = o.id AND o.status = 'completed' LEFT JOIN Review r ON p.id = r.product_id GROUP BY p.id, p.name, p.category, p.base_price, p.cost, p.stock_quantity WITH REFRESH SCHEDULE '0 */6 * * *'; -- Every 6 hours </code></pre></div> <h3 id="performance-optimization" class="position-relative d-flex align-items-center group"> Performance Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-optimization" aria-haspopup="dialog" aria-label="Share link: Performance Optimization"> Share link </button> </h3> <h4 id="choosing-what-to-materialize" class="position-relative d-flex align-items-center group"> Choosing What to Materialize <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="choosing-what-to-materialize" aria-haspopup="dialog" aria-label="Share link: Choosing What to Materialize"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Use EXPLAIN to identify expensive queries EXPLAIN ANALYZE SELECT category, COUNT(*) AS product_count, SUM(total_sales) AS category_revenue FROM products GROUP BY category; -- If execution time > 1000ms and query frequency > 100/day: -- → CREATE MATERIALIZED VIEW -- Monitor view usage SELECT view_name, query_count, avg_query_time_ms, last_refresh_duration_ms, storage_size_mb FROM system.materialized_view_stats ORDER BY query_count DESC; </code></pre></div> <h4 id="partitioning-large-views" class="position-relative d-flex align-items-center group"> Partitioning Large Views <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="partitioning-large-views" aria-haspopup="dialog" aria-label="Share link: Partitioning Large Views"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Partition by date for better refresh performance CREATE MATERIALIZED VIEW sales_by_month PARTITION BY month AS SELECT DATE_TRUNC('month', order_date) AS month, product_id, COUNT(*) AS order_count, SUM(quantity) AS units_sold, SUM(revenue) AS total_revenue FROM order_items GROUP BY DATE_TRUNC('month', order_date), product_id; -- Refresh only recent partitions REFRESH MATERIALIZED VIEW sales_by_month PARTITION WHERE month >= '2026-01-01'; </code></pre></div> <h4 id="cascading-refreshes" class="position-relative d-flex align-items-center group"> Cascading Refreshes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="cascading-refreshes" aria-haspopup="dialog" aria-label="Share link: Cascading Refreshes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create dependency chain for efficient updates CREATE MATERIALIZED VIEW base_metrics AS SELECT /* expensive base computation */ WITH REFRESH SCHEDULE '0 1 * * *'; CREATE MATERIALIZED VIEW derived_metrics AS SELECT /* aggregates from base_metrics */ FROM base_metrics WITH REFRESH AFTER base_metrics; -- Automatically refresh after base </code></pre></div> <h3 id="monitoring-and-maintenance" class="position-relative d-flex align-items-center group"> Monitoring and Maintenance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="monitoring-and-maintenance" aria-haspopup="dialog" aria-label="Share link: Monitoring and Maintenance"> Share link </button> </h3> <h4 id="view-health-monitoring" class="position-relative d-flex align-items-center group"> View Health Monitoring <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="view-health-monitoring" aria-haspopup="dialog" aria-label="Share link: View Health Monitoring"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check view freshness SELECT view_name, last_refresh_time, DATEDIFF(MINUTE, last_refresh_time, NOW()) AS minutes_since_refresh, last_refresh_duration_ms / 1000.0 AS refresh_duration_seconds, row_count, storage_size_mb FROM system.materialized_views ORDER BY minutes_since_refresh DESC; -- Identify stale views SELECT view_name, last_refresh_time FROM system.materialized_views WHERE last_refresh_time < NOW() - INTERVAL '24 hours' AND refresh_schedule IS NOT NULL; -- Should auto-refresh </code></pre></div> <h4 id="refresh-performance" class="position-relative d-flex align-items-center group"> Refresh Performance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="refresh-performance" aria-haspopup="dialog" aria-label="Share link: Refresh Performance"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Analyze refresh performance SELECT view_name, refresh_type, AVG(duration_ms) AS avg_duration_ms, MAX(duration_ms) AS max_duration_ms, COUNT(*) AS refresh_count FROM system.materialized_view_refresh_history WHERE refresh_time > NOW() - INTERVAL '7 days' GROUP BY view_name, refresh_type ORDER BY avg_duration_ms DESC; </code></pre></div> <h4 id="storage-management" class="position-relative d-flex align-items-center group"> Storage Management <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="storage-management" aria-haspopup="dialog" aria-label="Share link: Storage Management"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Identify large views SELECT view_name, storage_size_mb, row_count, storage_size_mb / NULLIF(row_count, 0) * 1024 AS kb_per_row FROM system.materialized_views ORDER BY storage_size_mb DESC LIMIT 20; -- Drop unused views DROP MATERIALIZED VIEW IF EXISTS old_unused_view; </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="design-guidelines" class="position-relative d-flex align-items-center group"> Design Guidelines <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="design-guidelines" aria-haspopup="dialog" aria-label="Share link: Design Guidelines"> Share link </button> </h4><ol> <li>Materialize Aggregations, Not Raw Data: Focus on expensive computations</li> <li>Add Appropriate Indexes: Index columns used in WHERE and ORDER BY clauses</li> <li>Choose Refresh Strategy Wisely: Balance freshness vs. performance cost</li> <li>Monitor Storage Growth: Regularly review view sizes and cleanup unused views</li> <li>Document Business Logic: Explain what each view represents and refresh schedule rationale</li> </ol> <h4 id="refresh-strategy-selection" class="position-relative d-flex align-items-center group"> Refresh Strategy Selection <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="refresh-strategy-selection" aria-haspopup="dialog" aria-label="Share link: Refresh Strategy Selection"> Share link </button> </h4><table> <thead> <tr> <th>Data Change Frequency</th> <th>Freshness Requirement</th> <th>Recommended Strategy</th> </tr> </thead> <tbody> <tr> <td>Constant</td> <td>Real-time</td> <td>Don’t use materialized view</td> </tr> <tr> <td>Hourly</td> <td>Minutes</td> <td>Scheduled refresh every hour</td> </tr> <tr> <td>Daily</td> <td>Hours</td> <td>Scheduled refresh daily</td> </tr> <tr> <td>Weekly</td> <td>Days</td> <td>Scheduled refresh weekly</td> </tr> <tr> <td>Rarely</td> <td>Any</td> <td>Manual refresh on-demand</td> </tr> <tr> <td>Append-only</td> <td>Near real-time</td> <td>Incremental refresh</td> </tr> </tbody> </table> <h4 id="testing-procedures" class="position-relative d-flex align-items-center group"> Testing Procedures <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="testing-procedures" aria-haspopup="dialog" aria-label="Share link: Testing Procedures"> Share link </button> </h4><ol> <li>Benchmark Performance: Compare query times before/after materialization</li> <li>Test Refresh Load: Ensure refresh doesn’t impact production workload</li> <li>Verify Correctness: Compare materialized results with live query results</li> <li>Monitor Resource Usage: Track CPU, memory, and disk I/O during refresh</li> </ol> <h3 id="troubleshooting" class="position-relative d-flex align-items-center group"> Troubleshooting <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="troubleshooting" aria-haspopup="dialog" aria-label="Share link: Troubleshooting"> Share link </button> </h3> <h4 id="slow-refresh-times" class="position-relative d-flex align-items-center group"> Slow Refresh Times <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="slow-refresh-times" aria-haspopup="dialog" aria-label="Share link: Slow Refresh Times"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Check underlying query performance EXPLAIN ANALYZE SELECT /* view definition query */; -- Consider incremental refresh ALTER MATERIALIZED VIEW slow_view SET REFRESH MODE INCREMENTAL; -- Partition large views ALTER MATERIALIZED VIEW slow_view PARTITION BY date_column; </code></pre></div> <h4 id="out-of-sync-data" class="position-relative d-flex align-items-center group"> Out-of-Sync Data <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="out-of-sync-data" aria-haspopup="dialog" aria-label="Share link: Out-of-Sync Data"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Force full refresh REFRESH MATERIALIZED VIEW problematic_view FORCE; -- Verify data consistency SELECT COUNT(*) FROM materialized_view; SELECT COUNT(*) FROM (/* view definition query */) AS live_data; </code></pre></div> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/indexes/" >Indexes</a> - Indexing strategies for materialized views</li> <li><a href="/tags/performance/" >Performance</a> - Query optimization techniques</li> <li><a href="/tags/caching/" >Caching</a> - Data caching strategies</li> <li><a href="/tags/computed-properties/" >Computed Properties</a> - Dynamic property calculations</li> <li><a href="/tags/aggregation/" >Aggregation</a> - Aggregation functions</li> <li><a href="/tags/analytics/" >Analytics</a> - Analytical processing patterns</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li><a href="https://www.postgresql.org/docs/current/rules-materializedviews.html" aria-label="Materialized Views (PostgreSQL) – opens in new window" target="_blank" rel="noopener noreferrer" >Materialized Views (PostgreSQL) ↗ </a> - PostgreSQL documentation</li> <li><a href="https://www.kimballgroup.com/" aria-label="Kimball Dimensional Modeling – opens in new window" target="_blank" rel="noopener noreferrer" >Kimball Dimensional Modeling ↗ </a> - Data warehouse design</li> <li><a href="https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/understanding-microsoft-olap-architecture" aria-label="OLAP Cube Design – opens in new window" target="_blank" rel="noopener noreferrer" >OLAP Cube Design ↗ </a> - Microsoft Analysis Services</li> <li><a href="https://dl.acm.org/doi/10.1145/170036.170066" aria-label="View Maintenance Research – opens in new window" target="_blank" rel="noopener noreferrer" >View Maintenance Research ↗ </a> - Academic foundations</li> </ul>

Popular

Materialized Views - Pre-Computed Query Results

Related Articles