PostgreSQL Materialized Views menyediakan mekanisme yang hebat untuk meningkatkan kinerja query dengan melakukan prakomputasi dan menyimpan set hasil query sebagai tabel fisik. Tutorial ini akan memandu Anda melalui pembuatan Materialized Views di PostgreSQL, menggunakan DVD Rental Database sebagai contoh praktis.
Memahami Materialized Views
Materialized View adalah cuplikan dari kumpulan hasil query yang disimpan sebagai tabel fisik. Tidak seperti tampilan biasa, yang bersifat virtual dan menjalankan query yang mendasarinya setiap kali dirujuk, Materialized View menyimpan data, yang memungkinkan kinerja query yang lebih cepat dengan biaya penyegaran berkala.
Materialized View sangat berguna dalam skenario di mana data yang mendasarinya jarang berubah dibandingkan dengan frekuensi eksekusi query. Hal ini membuatnya ideal untuk skenario seperti pelaporan, pergudangan data, dan situasi di mana data waktu nyata bukanlah persyaratan yang ketat.
Setting Up the DVD Rental Database
Sebelum kita menyelami Materialized Views, mari kita siapkan DVD Rental Database. Ini adalah versi PostgreSQL dari Sakila Sample Database for MySQL yang populer. Anda dapat mengunduh Database Rental DVD dari halaman tutorial resmi PostgreSQL (PostgreSQL Sample Database).
File database dalam format ZIP (dvdrental.zip) sehingga Anda perlu mengekstraknya ke dvdrental.tar sebelum memuat database sampel ke server database PostgreSQL. Setelah Anda mengekstrak file .tar, buat database baru bernama "dvdrental" dan jalankan perintah pg_restore untuk mengisi database dvdrental dari konten file .tar:
pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar
Ganti path di atas dengan jalur yang mengarah ke dvdrental.tar yang diekstrak di sistem Anda.
Anda dapat menemukan petunjuk instalasi terperinci di sini.
Membuat Materialized View
Katakan kita ingin membuat Materialized View yang menunjukkan total pendapatan yang dihasilkan oleh setiap kategori film. Berikut adalah panduan langkah-langkahnya:
- Hubungkan ke database PostgreSQL Anda
- Membuat Materialized View menggunakan pernyataan DML sebagai berikut:
CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECT
c.name AS category,
SUM(p.amount) AS total_revenue
FROM
category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
c.name;Pada contoh ini, kita akan menggabungkan beberapa tabel dari Database Rental DVD untuk menghitung total pendapatan dari setiap kategori film.
Di Navicat For PostgreSQL (atau Navicat Premium) 16:
- Klik tombol "Materialized View" untuk menampilkan Daftar Objek Materialized View, lalu klik "+ Materialized View Baru" di bilah alat Objek untuk membuka Desainer Tampilan:
- Masukkan bagian SELECT dari pernyataan di atas ke dalam editor Definisi:
- Kita dapat klik tombol Preview untuk verifikasi bahwa pernyataan kita bekerja sesuai ekspetasi:
- Untuk membuat Materialized View baru, klik tombol Simpan. Sebuah dialog akan muncul yang meminta Nama Tampilan Terwujud. Mari kita sebut saja "mv_category_revenue" seperti yang kita lakukan pada pernyataan CREATE MATERIALIZED VIEW di atas:
- Setelah mengklik tombol dialog Simpan, Navicat akan mengubah nama tampilan terwujud baru dari "tanpa judul" menjadi nama yang kami berikan. Navicat juga akan menambahkan tampilan terwujud baru kami ke Tampilan Terwujud di Panel Navigasi sebelah kiri:
Kesimpulan
PostgreSQL Materialized Views merupakan alat yang berharga untuk mengoptimalkan kinerja query dalam skenario di mana data waktu nyata tidak penting. Dengan melakukan pra-komputasi dan menyimpan hasil query yang kompleks, Materialized Views dapat secara signifikan meningkatkan waktu respons untuk tugas analitis dan pelaporan. Dalam tutorial ini, kita mempelajari cara membuat Materialized View untuk Basis Data Penyewaan DVD, yang menunjukkan aplikasi praktisnya dalam skenario dunia nyata.
