Materialized View (MV) pada PostgreSQL menyimpan hasil query kompleks ke disk untuk mempercepat latensi pembacaan data analitik. Namun, pembaruan data menggunakan perintah REFRESH MATERIALIZED VIEW dapat memicu degradasi performa sistem jika tidak dikonfigurasi dengan tepat. Operasi ini dapat memblokir traffic aplikasi atau menyebabkan lonjakan I/O dan WAL (Write-Ahead Logging) disk.

Mekanisme Lock: Refresh Standar vs CONCURRENTLY

Perintah refresh standar beroperasi secara destruktif terhadap akses konkurensi:

REFRESH MATERIALIZED VIEW sales_summary;

Perintah di atas meminta AccessExclusiveLock pada MV. Lock ini memblokir semua operasi pembacaan (SELECT) dan pembaruan lain hingga proses selesai. Pada dataset berukuran puluhan gigabyte, proses penulisan ulang data ke storage dapat memakan waktu beberapa menit hingga hitungan jam, mengakibatkan query starvation pada aplikasi.

Untuk mencegah pemblokiran SELECT, PostgreSQL menyediakan opsi konkurensi:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Opsi CONCURRENTLY hanya meminta ExclusiveLock. Lock level ini tetap mengizinkan query SELECT berjalan membaca snapshot lama secara bersamaan saat data diperbarui di latar belakang.

Syarat dan Biaya Internal REFRESH CONCURRENTLY

Mode concurrent tidak berjalan tanpa prasyarat teknis dan membawa konsekuensi komputasi yang lebih tinggi.

1. Syarat Unique Index

PostgreSQL mewajibkan minimal satu Unique Index tanpa klausa WHERE (bukan partial index) yang mencakup setidaknya satu kolom berkarakteristik NOT NULL. Syarat ini diperlukan agar engine dapat mengidentifikasi baris secara unik selama proses pencocokan data.

CREATE UNIQUE INDEX idx_sales_summary_id ON sales_summary (transaction_id);

2. Mekanisme Diffing dan Beban I/O

Berbeda dengan refresh standar yang langsung menulis ulang seluruh tabel (truncate and rewrite), CONCURRENTLY menjalankan mekanisme berikut:

  1. PostgreSQL mengeksekusi query dasar view dan menyimpannya ke tabel transient sementara (temporary heap file).
  2. Engine menjalankan operasi diffing menggunakan join perbandingan internal untuk mendeteksi baris mana yang disisipkan (INSERT), diubah (UPDATE), atau dihapus (DELETE).
  3. Perubahan diaplikasikan ke MV baris per baris atau blok per blok.

Konsekuensi: Proses diffing ini menghasilkan pembacaan I/O ganda (membaca data lama dan data baru), peningkatan pemakaian CPU untuk sorting/hashing, serta volume WAL yang jauh lebih besar akibat log transaksi dari masing-masing statement DML individual.

Optimasi Performa Query dan Work Mem

Untuk menekan durasi diffing dan beban I/O saat refresh concurrent:

  • Alokasi Memori: Tingkatkan maintenance_work_mem pada sesi yang menjalankan refresh. Ini mempercepat pembangunan index sementara dan operasi diffing berbasis memori tanpa harus tumpah (spill) ke disk.
  • Query Execution Plan: Pastikan tabel dasar (base tables) yang menjadi sumber MV memiliki indeks yang optimal. Evaluasi execution plan query pembentuk MV menggunakan EXPLAIN (ANALYZE, BUFFERS).
-- Naikkan memory untuk sesi refresh saja
SET maintenance_work_mem = '2GB';
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
RESET maintenance_work_mem;

Tuning Autovacuum Pasca-Refresh

Operasi CONCURRENTLY menghasilkan dead tuples dalam jumlah besar akibat operasi UPDATE dan DELETE selama rekonsiliasi data. Jika tidak dibersihkan, dead tuples menyebabkan table bloat dan memperlambat pembacaan indeks.

Konfigurasikan threshold autovacuum yang lebih agresif secara spesifik pada Materialized View target:

ALTER MATERIALIZED VIEW sales_summary SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_cost_limit = 2000,
    autovacuum_vacuum_cost_delay = 2
);

Konfigurasi ini memastikan autovacuum terpicu ketika 5% data berubah, dan meningkatkan throughput vacuum agar pembersihan dead tuples selesai lebih cepat tanpa menunggu jadwal default global.

Alternatif Beban Masif: Atomic Table Swap

Pada tabel analitik berskala ratusan juta baris, REFRESH CONCURRENTLY sering kali memakan waktu terlalu lama dan membebani disk secara berkepanjangan akibat I/O diffing. Alternatif performan yang mempertahankan ketersediaan pembacaan adalah pola Atomic Table Swap menggunakan tabel standar.

Langkah implementasi:

-- 1. Buat tabel penampung baru dan isi data
CREATE TABLE sales_summary_new AS
SELECT category, SUM(amount) AS total_sales, COUNT(*) AS count
FROM raw_sales
GROUP BY category;

-- 2. Bangun indeks yang dibutuhkan
CREATE INDEX idx_sales_summary_new_cat ON sales_summary_new (category);

-- 3. Lakukan swap secara atomik dalam satu transaksi
BEGIN;
DROP TABLE IF EXISTS sales_summary_old;
ALTER TABLE IF EXISTS sales_summary RENAME TO sales_summary_old;
ALTER TABLE sales_summary_new RENAME TO sales_summary;
COMMIT;

-- 4. Bersihkan tabel lama di luar transaksi utama
DROP TABLE sales_summary_old;

Pola ini hanya menahan AccessExclusiveLock selama beberapa milidetik saat statement RENAME dieksekusi di dalam blok transaksi. Keuntungannya adalah tidak ada overhead diffing data dan tidak ada WAL bloat akibat individual tuple updates.