### 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

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

Mengambil data:

```sql
SELECT * FROM laporan_penjualan;
```

---

### Refresh data (WAJIB agar update)

```sql
REFRESH MATERIALIZED VIEW laporan_penjualan;
```

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

```sql
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

```sql
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

```sql
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**.

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

Lalu refresh:

```sql
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:

```sql
(tanggal)
(cabang_id)
```

✅ Lebih optimal:

```sql
CREATE INDEX idx_mv_penjualan_tanggal_cabang
ON mv_laporan_penjualan (tanggal, cabang_id);
```

Urutan kolom **HARUS mengikuti pola query**:

```sql
WHERE tanggal = ?
AND cabang_id = ?
```

---

## 4️⃣ **Partial Index untuk Data Populer**

Kalau query sering ambil data terbaru:

```sql
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:

```sql
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**.

```sql
ANALYZE mv_laporan_penjualan;
```

Untuk rutin:

```sql
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:

```sql
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

```sql
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.

```sql
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)

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

### ✅ Index (Composite)

```sql
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`

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

### ✅ Index

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

⚡ Menghindari sort mahal.

---

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

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

### ✅ Index

```sql
CREATE INDEX idx_mv_laporan_tanggal
ON mv_laporan (tanggal);
```

---

## 4️⃣ Query JOIN

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

### ✅ Index

```sql
CREATE INDEX idx_mv_laporan_cabang_id
ON mv_laporan (cabang_id);
```

📌 Foreign key **WAJIB di-index**.

---

## 5️⃣ Query Dashboard (Data Terbaru)

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

### ✅ Partial Index

```sql
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`

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_laporan;
```

### ✅ Wajib UNIQUE index

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

Tanpa ini → ❌ ERROR.

---

## 7️⃣ Index Only Scan (Covering Index)

```sql
SELECT tanggal, total_penjualan
FROM mv_laporan
WHERE cabang_id = 10;
```

### ✅ Index

```sql
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)

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

### ✅ Index

```sql
CREATE INDEX idx_mv_laporan_group
ON mv_laporan (cabang_id, total_penjualan);
```

---

## 🔥 Template Universal (Aman untuk 80% kasus)

```sql
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:

```sql
ANALYZE mv_laporan;
```

---