Belajar SQL PostgreSQL - Views, Materialized Views & Generated Columns

#Pendahuluan

Selamat datang di episode 12 series Belajar SQL PostgreSQL! Sejauh ini kita selalu menulis query yang sama berulang-ulang: join beberapa tabel, filter dengan kondisi yang sama, agregasi yang sama. Bayangkan tim aplikasi dengan 10 developer yang masing-masing menulis query SELECT ... JOIN ... WHERE yang serupa untuk laporan penjualan — masing-masing bisa salah di kondisi yang berbeda. Ada cara yang jauh lebih rapi: simpan query-nya sebagai database object.

Di episode ini, kita akan membahas tiga database object yang membuat hidup developer lebih mudah: standard view yang membungkus query kompleks menjadi "tabel virtual", materialized view yang menyimpan hasil query secara fisik di disk untuk kecepatan maksimal, dan generated columns yang menghitung nilai kolom secara otomatis dari kolom lain. Ketiganya adalah fondasi untuk membangun lapisan data yang rapi dan scalable.

#Standard Views: Tabel Virtual

View adalah query SELECT yang diberi nama dan disimpan. Ia berperilaku seperti tabel — bisa di-SELECT, di-join, bahkan difilter — tapi tidak menyimpan data fisik. Setiap kali diakses, query di balik view dijalankan ulang.

CREATE VIEW v_order_detail AS
SELECT
    o.id AS order_id,
    c.full_name AS customer,
    p.name AS product,
    oi.quantity,
    oi.quantity * p.price AS subtotal
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Setelah itu, query kompleks di atas bisa dipanggil sesederhana:

SELECT * FROM v_order_detail
WHERE customer ILIKE '%budi%';

#Keuntungan Menggunakan View

Ada tiga alasan utama orang memakai view:

1. Menyederhanakan akses data. Tim aplikasi cukup SELECT dari view yang sudah dijamin benar, tanpa harus memahami join di baliknya. Permintaan yang kompleks menjadi satu baris.

2. Menyembunyikan kolom sensitif. View bisa mengekspos hanya kolom tertentu. Misal v_user_public yang menampilkan nama dan email tapi tidak menampilkan password hash atau kolom internal lain. Ini lapisan keamanan pertama (di episode 16 kita tambah lapisan RLS).

CREATE VIEW v_user_public AS
SELECT id, email, full_name, created_at
FROM users;

3. Konsistensi logika. Jika logika laporan berubah (misal ketentuan "produk aktif" berubah), kalian cukup mengubah satu view — semua penggunanya otomatis mengikuti.

#Cara Mengubah dan Menghapus View

CREATE OR REPLACE VIEW v_order_detail AS
SELECT ... kondisi_baru ...;
 
DROP VIEW v_order_detail;

CREATE OR REPLACE memungkinkan mengganti definisi view tanpa menghapusnya. Catatan: kolom yang dihapus dari view lama bisa memutuskan kode aplikasi yang masih mereferensikannya — jadi ubah view dengan hati-hati.

#Materialized Views: Hasil Fisik di Disk

Materialized view berbeda dari view biasa: ia menyimpan hasil query secara fisik ke disk. Aksesnya sangat cepat karena data sudah tersedia — tapi ia tidak selalu terbarui; kalian harus me-refresh secara berkala.

#Kapan Menggunakan Materialized View?

Materialized view adalah jawaban untuk query analitik yang mahal (agregasi jutaan baris) tapi datanya tidak berubah setiap detik. Contoh: laporan penjualan harian. Jika setiap dashboard request menjalankan agregasi penuh dari awal, server akan kewalahan. Dengan materialized view, hasil dihitung sekali lalu dibaca berkali-kali.

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', o.created_at) AS day,
    COUNT(*) AS total_orders,
    SUM(o.total) AS revenue
FROM orders o
GROUP BY 1;

#REFRESH: Memperbarui Data

REFRESH MATERIALIZED VIEW mv_daily_sales;

Masalahnya, REFRESH MATERIALIZED VIEW biasa mengunci view selama proses berlangsung — aplikasi yang membaca jadi menunggu. Solusinya adalah REFRESH ... CONCURRENTLY:

CREATE UNIQUE INDEX mv_daily_sales_idx ON mv_daily_sales (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

#View vs Materialized View

#Generated Columns: Nilai yang Dihitung Otomatis

Generated column adalah kolom yang nilainya dihitung otomatis dari kolom lain pada baris yang sama. Ia disimpan fisik ke disk dan diperbarui otomatis setiap kali baris di-insert atau di-update.

Sintaksnya di PostgreSQL: GENERATED ALWAYS AS (ekspresi) STORED — catat, PostgreSQL hanya mendukung STORED, bukan VIRTUAL.

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    subtotal NUMERIC(12,2) GENERATED ALWAYS AS (quantity * price) STORED
);

Sekarang setiap insert atau update, subtotal dihitung otomatis oleh database:

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES ('...', '...', 3, 25000)
RETURNING subtotal;

subtotal langsung menjadi 75000 tanpa kita menuliskannya — dan yang lebih penting, nilainya tidak mungkin tidak sinkron karena selalu dihitung ulang oleh database.

#Generated Columns vs Default

Jangan tertukar dengan DEFAULT:

• DEFAULT diisi satu kali saat insert, dan bisa di-override oleh aplikasi.
• Generated column selalu dihitung oleh database, dan tidak bisa di-insert atau di-update secara manual — PostgreSQL akan menolak.

INSERT INTO order_items (order_id, product_id, quantity, price, subtotal)
VALUES ('...', '...', 2, 10000, 99999);

Error: cannot insert a non-DEFAULT value into column "subtotal". Database melindungi konsistensinya sendiri.

#Kesalahan Umum

#Penutup

Di episode 12 ini kita sudah mengenal tiga database object penting: standard view untuk menyederhanakan akses dan menyembunyikan kolom sensitif, materialized view untuk query analitik berkecepatan tinggi dengan refresh koncurrent, dan generated columns yang menghitung nilai otomatis dari kolom lain secara konsisten.

Inti yang harus dibawa pulang:

• View adalah query bernama — tidak menyimpan data, selalu terkini, cocok untuk menyederhanakan dan mengamankan akses.
• Materialized view menyimpan hasil fisik di disk — sangat cepat, tapi harus di-refresh.
• REFRESH ... CONCURRENTLY butuh unique index — gunakan agar pembaca tidak diblokir.
• Generated column dihitung otomatis (GENERATED ALWAYS AS (...) STORED) dan tidak bisa di-override.
• Pilih view/matview berdasarkan kebutuhan kecepatan vs keterbaruan data.

Di episode 13 selanjutnya, kita menulis kode di dalam database: Stored Procedures, Functions (PL/pgSQL) & Triggers — mulai dari User-Defined Function dengan bahasa PL/pgSQL, perbedaan function dan stored procedure, hingga trigger untuk otomatisasi seperti kolom updated_at dan audit logging.