de-postgres-note/materialized_view_postgresql.MD

Apa itu Materialized View di PostgreSQL?

Materialized View adalah objek database yang menyimpan hasil query secara fisik (seperti tabel), bukan menjalankan ulang query setiap kali dipanggil seperti VIEW biasa.

Perbedaan VIEW vs MATERIALIZED VIEW

Aspek	VIEW	MATERIALIZED VIEW
Penyimpanan data	Tidak disimpan	Disimpan fisik
Kecepatan query	Lebih lambat (query ulang)	Lebih cepat
Update otomatis	Selalu real-time	❌ Tidak otomatis
Perlu refresh	❌	✅ Ya

---

Contoh penggunaan

CREATE MATERIALIZED VIEW laporan_penjualan AS
SELECT
  tanggal,
  SUM(total) AS total_penjualan
FROM penjualan
GROUP BY tanggal;

Mengambil data:

SELECT * FROM laporan_penjualan;

---

Refresh data (WAJIB agar update)

REFRESH MATERIALIZED VIEW laporan_penjualan;

Tanpa mengunci (PostgreSQL ≥ 9.4, wajib ada index UNIQUE):

REFRESH MATERIALIZED VIEW CONCURRENTLY laporan_penjualan;

---

Kapan sebaiknya pakai Materialized View?

✅ Query berat (JOIN besar, agregasi) ✅ Data laporan / dashboard ✅ Data tidak harus real-time ❌ Data yang sering berubah dan harus langsung update

---

Kekurangan

• Data bisa tidak up-to-date
• Perlu mekanisme refresh manual / cron
• Memakai storage tambahan

Berikut strategi performa Materialized View di PostgreSQL (fokus INDEX & refresh) yang sudah terbukti efektif untuk kasus laporan & query berat.

---

1️⃣ WAJIB: Buat INDEX di Materialized View

Materialized view TIDAK otomatis punya index.

Contoh

CREATE MATERIALIZED VIEW mv_laporan_penjualan AS
SELECT
  tanggal,
  cabang_id,
  SUM(total) AS total_penjualan
FROM penjualan
GROUP BY tanggal, cabang_id;

Index untuk filter & join

CREATE INDEX idx_mv_penjualan_tanggal
ON mv_laporan_penjualan (tanggal);

CREATE INDEX idx_mv_penjualan_cabang
ON mv_laporan_penjualan (cabang_id);

📌 Rule utama

• WHERE → index kolom filter
• JOIN → index kolom join
• ORDER BY → index kolom sorting

---

2️⃣ UNIQUE INDEX untuk REFRESH CONCURRENTLY

Tanpa ini, CONCURRENTLY akan error.

CREATE UNIQUE INDEX ux_mv_penjualan
ON mv_laporan_penjualan (tanggal, cabang_id);

Lalu refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_laporan_penjualan;

🎯 Benefit

• Query tetap bisa baca data
• Tidak lock materialized view

---

3️⃣ Gunakan Composite Index (bukan banyak index kecil)

❌ Buruk:

(tanggal)
(cabang_id)

✅ Lebih optimal:

CREATE INDEX idx_mv_penjualan_tanggal_cabang
ON mv_laporan_penjualan (tanggal, cabang_id);

Urutan kolom HARUS mengikuti pola query:

WHERE tanggal = ?
AND cabang_id = ?

---

4️⃣ Partial Index untuk Data Populer

Kalau query sering ambil data terbaru:

CREATE INDEX idx_mv_penjualan_30hari
ON mv_laporan_penjualan (tanggal)
WHERE tanggal >= CURRENT_DATE - INTERVAL '30 days';

⚡ Sangat cepat untuk dashboard “bulan ini”.

---

5️⃣ Covering Index (INCLUDE)

Agar Index Only Scan terjadi:

CREATE INDEX idx_mv_cover
ON mv_laporan_penjualan (tanggal, cabang_id)
INCLUDE (total_penjualan);

📈 Mengurangi akses ke heap → performa naik.

---

6️⃣ VACUUM & ANALYZE setelah REFRESH

Materialized view tidak auto-analyze.

ANALYZE mv_laporan_penjualan;

Untuk rutin:

VACUUM ANALYZE mv_laporan_penjualan;

📌 Sangat penting untuk planner memilih index yang benar.

---

7️⃣ Pisahkan MV untuk Use Case Berbeda

❌ Satu MV dipakai semua kebutuhan ✅ Beberapa MV kecil & spesifik

Contoh:

mv_penjualan_harian
mv_penjualan_bulanan
mv_penjualan_per_cabang

Lebih cepat refresh & index lebih fokus.

---

8️⃣ Refresh Incremental (Strategi Lanjutan)

PostgreSQL tidak native incremental refresh.

Solusi umum:

• MV hanya data lama
• Data hari ini query langsung tabel

SELECT * FROM mv_penjualan_lama
UNION ALL
SELECT ... FROM penjualan WHERE tanggal = CURRENT_DATE;

⚡ Refresh MV cukup seminggu sekali.

---

9️⃣ Gunakan EXPLAIN ANALYZE

Pastikan index benar-benar dipakai.

EXPLAIN ANALYZE
SELECT *
FROM mv_laporan_penjualan
WHERE tanggal = '2025-12-01';

Cari:

• Index Scan
• Index Only Scan

---

🔥 Checklist Cepat (Best Practice)

✅ Selalu buat index ✅ Buat UNIQUE index jika pakai CONCURRENTLY ✅ Composite index > banyak index kecil ✅ Partial index untuk data populer ✅ ANALYZE setelah refresh ✅ Jangan jadikan MV “tabel serba guna”

---

Siap 👍 Berikut template INDEX Materialized View PostgreSQL berbasis pola query. Kamu tinggal sesuaikan nama kolom & MV.

---

🧠 Cara Pakai Template

1. Ambil query paling sering dipakai
2. Cocokkan dengan pola di bawah
3. Terapkan index sesuai template

---

1️⃣ Query dengan WHERE = (Filter Exact)

SELECT *
FROM mv_laporan
WHERE tanggal = '2025-12-01'
  AND cabang_id = 10;

✅ Index (Composite)

CREATE INDEX idx_mv_laporan_tanggal_cabang
ON mv_laporan (tanggal, cabang_id);

📌 Urutan kolom penting! Kolom yang paling sering difilter → di depan.

---

2️⃣ Query WHERE + ORDER BY

SELECT *
FROM mv_laporan
WHERE cabang_id = 10
ORDER BY tanggal DESC
LIMIT 20;

✅ Index

CREATE INDEX idx_mv_laporan_cabang_tanggal_desc
ON mv_laporan (cabang_id, tanggal DESC);

⚡ Menghindari sort mahal.

---

3️⃣ Query Range (BETWEEN, >=, <=)

SELECT *
FROM mv_laporan
WHERE tanggal BETWEEN '2025-12-01' AND '2025-12-31';

✅ Index

CREATE INDEX idx_mv_laporan_tanggal
ON mv_laporan (tanggal);

---

4️⃣ Query JOIN

SELECT m.*, c.nama
FROM mv_laporan m
JOIN cabang c ON c.id = m.cabang_id;

✅ Index

CREATE INDEX idx_mv_laporan_cabang_id
ON mv_laporan (cabang_id);

📌 Foreign key WAJIB di-index.

---

5️⃣ Query Dashboard (Data Terbaru)

SELECT *
FROM mv_laporan
WHERE tanggal >= CURRENT_DATE - INTERVAL '7 days';

✅ Partial Index

CREATE INDEX idx_mv_laporan_7hari
ON mv_laporan (tanggal)
WHERE tanggal >= CURRENT_DATE - INTERVAL '7 days';

🚀 Sangat cepat untuk data “recent”.

---

6️⃣ Index untuk REFRESH CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_laporan;

✅ Wajib UNIQUE index

CREATE UNIQUE INDEX ux_mv_laporan
ON mv_laporan (tanggal, cabang_id);

Tanpa ini → ❌ ERROR.

---

7️⃣ Index Only Scan (Covering Index)

SELECT tanggal, total_penjualan
FROM mv_laporan
WHERE cabang_id = 10;

✅ Index

CREATE INDEX idx_mv_laporan_cover
ON mv_laporan (cabang_id)
INCLUDE (tanggal, total_penjualan);

⚡ Planner bisa tanpa baca table.

---

8️⃣ Query Aggregasi Ulang (GROUP BY kecil)

SELECT cabang_id, SUM(total_penjualan)
FROM mv_laporan
GROUP BY cabang_id;

✅ Index

CREATE INDEX idx_mv_laporan_group
ON mv_laporan (cabang_id, total_penjualan);

---

🔥 Template Universal (Aman untuk 80% kasus)

CREATE UNIQUE INDEX ux_mv_laporan_pk
ON mv_laporan (tanggal, cabang_id);

CREATE INDEX idx_mv_laporan_filter
ON mv_laporan (cabang_id, tanggal DESC)
INCLUDE (total_penjualan);

---

✅ Setelah Buat Index

Jangan lupa:

ANALYZE mv_laporan;

---