SQL Query and performance tuning - Materialized views

 

🚀 Boost Query Speed with PostgreSQL Materialized Views

When performance matters and you're running expensive queries repeatedly, there's a simple trick in PostgreSQL that can save you time: Materialized Views.

Let’s break it down, step by step.

---

🧠 What is a Materialized View?

A Materialized View is like a snapshot of a query. It stores the result of a query physically on disk, so you can reuse it without re-executing the original query every time.

Think of it like:

📋 A view = live feed
🧊 A materialized view = frozen snapshot (until refreshed)

---

✅ Why Use Materialized Views?

• 💨 Faster query responses (no need to rejoin large tables every time)

• 🔁 Reuse precomputed results

• 💾 Trade storage for speed

---

📘 Example: Creating a Materialized View

Let’s say you frequently run a report showing staff and their region:

CREATE MATERIALIZED VIEW mv_staff AS
SELECT 
  s.last_name,
  s.department,
  s.job_title,
  c.region AS company_region
FROM 
  staff s
JOIN 
  company_regions c ON s.region_id = c.id;

Boom 💥—you’ve now stored the result of a join!

---

📊 Querying the Materialized View

Query it just like a regular table or view:

SELECT * FROM mv_staff;

Super fast ⚡—because it doesn't recompute the join each time.

---

🔄 Keeping It Up-to-Date

Remember: Materialized Views don’t update automatically. If the source tables change, your view won’t reflect it unless you tell it to.

To update it:

REFRESH MATERIALIZED VIEW mv_staff;

You can run this manually or on a schedule (e.g., using cron or pg_cron).

---

⏱ Want it to update automatically?

You can even add a trigger on the source tables to refresh the view whenever data changes—if your use case demands real-time accuracy.

But beware:

⚠️ Frequent refreshes = performance cost
Best for read-heavy, write-light workloads

---

🧮 Tradeoff: Speed vs. Freshness

Feature	Materialized View
Performance	🔥 Very fast
Freshness	🧊 Stale until refreshed
Storage Usage	📦 Requires disk space
Auto-updated	❌ Not by default

📝 Final Tips

• Use materialized views when:

  • Your query is expensive

  • Data doesn’t change too frequently

  • You can tolerate a slight delay in freshness

• Avoid if:

  • You need real-time updates

  • You can't afford extra disk usage

---

✅ TL;DR

Materialized Views = Precomputed query results stored on disk
Refresh manually or with automation
Great for speeding up heavy queries