Partial Index

Katalog dengan 1000 produk sering kali hanya benar-benar “hidup” untuk sebagian kecil barisnya: sisanya sudah discontinued, habis, atau disembunyikan dari etalase. Query operasional — halaman produk aktif, pengecekan stok, unggah ulang harga — hanya menyentuh subset itu berulang kali, tetapi index biasa tetap mengindeks seluruh 1000 baris dengan setia. PostgreSQL menyediakan jalan keluar yang elegan: partial index — index yang hanya menampung baris-baris yang memenuhi sebuah predikat WHERE. Hasilnya index lebih kecil, tulis lebih murah, dan pola unik yang mustahil dipaksakan pada seluruh tabel menjadi mungkin di dalam subset. Chapter ini membuktikan ketiga klaim itu lewat perbandingan ukuran, EXPLAIN, dan demo constraint unik parsial.

Index yang Hanya Berisi Baris Terpilih #

Sintaksnya sederhana: tambahkan klausa WHERE di akhir CREATE INDEX. Server lalu menyeleksi baris yang masuk ke index pada saat index dibangun — baris yang tidak memenuhi predikat tidak pernah disimpan di dalamnya. Tabel berikut memakai domain khas section ini: 1000 produk yang dibangun deterministik dengan generate_series, dan hanya baris dengan g % 20 = 0 (50 baris, tepat 5%) yang berstatus aktif:

sql
buat-tabel.sql
-- Katalog 1000 produk; hanya 5% yang berstatus aktif.
-- Data dibangun deterministik lewat generate_series.
CREATE TABLE produk (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    harga    integer NOT NULL,
    kategori text NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO produk
SELECT g,
       'Produk ' || g,
       1000 + (g * 7919) % 500000,
       (ARRAY['elektronik', 'rumah tangga', 'olahraga', 'buku', 'pakaian'])
           [1 + g % 5],
       (g % 20 = 0)
FROM generate_series(1, 1000) AS g;

ANALYZE produk;

SELECT count(*) AS total_produk,
       count(*) FILTER (WHERE aktif) AS produk_aktif
FROM produk;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 1000
ANALYZE
 total_produk | produk_aktif 
--------------+--------------
         1000 |           50
(1 row)

Mari kita bedah bagian pentingnya:

Baris aktif boolean NOT NULL: Kolom status inilah kandidat predikat partial index — nilainya menentukan baris mana yang “panas” dan sering diquery.
Baris (g % 20 = 0): Ekspresi ini menghasilkan true hanya untuk 50 dari 1000 baris, sehingga subset aktif memang kecil dan simetris — kondisi ideal partial index.
Baris ANALYZE produk: Statistik dikumpulkan agar planner mengestimasi jumlah baris secara realistis pada demo EXPLAIN di bagian berikutnya.
Query count(*) FILTER: Ringkasan ini membuktikan proporsi 1000 banding 50 sebelum kita membangun index apa pun.

Ukuran Lebih Kecil, Tulis Lebih Hemat #

Manfaat paling mudah dibuktikan adalah ukuran. Dua index berikut memakai kolom kunci yang persis sama — harga — tetapi yang satu penuh dan yang satu parsial:

sql
ukuran-parsial.sql
-- Bandingkan ukuran index penuh vs partial pada kolom yang sama.
CREATE INDEX idx_produk_harga_penuh ON produk (harga);

CREATE INDEX idx_produk_harga_aktif ON produk (harga) WHERE aktif;

SELECT pg_size_pretty(pg_relation_size('idx_produk_harga_penuh')) AS ukuran_penuh,
       pg_size_pretty(pg_relation_size('idx_produk_harga_aktif')) AS ukuran_parsial;

-- Index penuh dijatuhkan agar demo EXPLAIN berikutnya bersih.
DROP INDEX idx_produk_harga_penuh;

Output yang Dihasilkan:

text
CREATE INDEX
CREATE INDEX
 ukuran_penuh | ukuran_parsial 
--------------+----------------
 40 kB        | 16 kB
(1 row)

DROP INDEX

Index penuh empat puluh kilobyte, index parsial enam belas kilobyte — lebih dari separuh ruang dihemat hanya dengan mengecualikan 95% baris yang tidak pernah dicari. Fungsi pg_relation_size() membaca ukuran relasi dari catalog, jadi angkanya deterministik untuk data yang sama dan bisa Anda andalkan sebagai eksperimen berulang. Penghematan ini bersifat ganda: selain ukuran di disk, setiap INSERT, UPDATE, dan DELETE pada baris di luar predikat tidak perlu menyentuh index sama sekali — biaya tulis untuk 950 produk nonaktif menjadi nol. Di tabel besar dengan ratusan juta baris historis, selisih seperti ini yang menentukan index layak dipelihara atau tidak.

Predikat Query Harus Mencakup Predikat Index #

Aturan ketat partial index: planner hanya memakai index bila kondisi query menyiratkan predikat index. Query dengan WHERE aktif AND harga = ... jelas memuat predikat aktif, sehingga aman; query dengan WHERE harga = ... saja tidak menjamin baris hasilnya berada di dalam index, sehingga planner menolak memakainya. Bukti lewat EXPLAIN:

sql
gunakan-parsial.sql
-- Query DENGAN predikat index: partial index terpakai.
EXPLAIN
SELECT id, nama
FROM produk
WHERE aktif AND harga = 159380;

-- Query TANPA predikat index: partial index tidak bisa dipakai.
EXPLAIN
SELECT id, nama
FROM produk
WHERE harga = 159380;

Output yang Dihasilkan:

text
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using idx_produk_harga_aktif on produk  (cost=0.14..8.16 rows=1 width=14)
   Index Cond: (harga = 159380)
(2 rows)

                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on produk  (cost=0.00..20.50 rows=1 width=14)
   Filter: (harga = 159380)
(2 rows)

Rencana pertama memperlihatkan Index Scan using idx_produk_harga_aktif dengan Index Cond: (harga = 159380) — predikat aktif sendiri tidak muncul sebagai kondisi karena seluruh isi index sudah pasti aktif. Rencana kedua jatuh ke Seq Scan dengan filter biasa: satu-satunya index pada harga hanya menampung baris aktif, jadi dipakai untuk query tanpa predikat aktif berisiko menghilangkan hasil. Kedua query ini dijalankan pada sesi yang sama dengan output di atas; angka cost dan rows bergantung pada statistik dan konfigurasi planner, sehingga nilainya dapat berbeda di mesin Anda — namun bentuk rencananya (index vs seq scan) stabil.

Unik di Dalam Subset #

Aplikasi paling khas partial index adalah keunikan yang hanya berlaku untuk sebagian baris: satu email aktif per pelanggan, satu nomor kursi per penerbangan yang belum dibatalkan, satu slot parkir aktif per kendaraan. UNIQUE penuh pada kolom akan menolak data historis yang sah — pelanggan lama yang kembali mendaftar dengan email yang sama. Partial unique index menyelesaikan konflik ini:

sql
unique-parsial.sql
-- Email boleh kembar SELAMA keduanya bukan pelanggan aktif.
CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    email text NOT NULL,
    aktif boolean NOT NULL DEFAULT true
);

INSERT INTO pelanggan (id, email, aktif) VALUES
    (1, '[email protected]', true),
    (2, '[email protected]', true),
    (3, '[email protected]', false),
    (4, '[email protected]', false);

-- Unik hanya dijalankan pada baris yang memenuhi WHERE aktif.
CREATE UNIQUE INDEX idx_pelanggan_email_aktif
    ON pelanggan (email) WHERE aktif;

SELECT id, email, aktif
FROM pelanggan
ORDER BY id;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 4
CREATE INDEX
 id |      email       | aktif 
----+------------------+-------
  1 | [email protected] | t
  2 | [email protected] | t
  3 | [email protected] | f
  4 | [email protected] | f
(4 rows)

Perhatikan hasil SELECT: dua baris [email protected] diterima tanpa keluhan karena keduanya tidak aktif — index unik memang tidak menampung mereka. Pembahasan penuh hubungan index unik dengan constraint UNIQUE dan primary key sudah dibahas di chapter Unique Index ; di sini kita fokus pada dimensi parsialnya. Segera setelah ada upaya menambahkan email kembar di antara pelanggan aktif, index menolaknya:

sql
duplikat-aktif.sql
-- Duplikat email di antara pelanggan AKTIF ditolak.
INSERT INTO pelanggan (id, email, aktif) VALUES
    (5, '[email protected]', true);

Output yang Dihasilkan:

text

psql:duplikat-aktif.sql:3: ERROR:  duplicate key value violates unique constraint "idx_pelanggan_email_aktif"
DETAIL:  Key (email)=([email protected]) already exists.

Error duplicate key value violates unique constraint "idx_pelanggan_email_aktif" menunjukkan nama index — bukan nama constraint tabel — yang menolak baris tersebut, karena memang index-lah yang berperan sebagai penjaga keunikan di sini.

Baris Masuk dan Keluar Predikat Secara Otomatis #

Pertanyaan wajar: apa yang terjadi pada index ketika sebuah baris berubah status? Jawabannya tidak ada pekerjaan manual — UPDATE yang membuat baris memenuhi predikat otomatis memasukkannya ke index, dan UPDATE sebaliknya otomatis mengeluarkannya:

sql
masuk-predikat.sql
-- Baris masuk ke predikat index: UPDATE saja, tanpa menyentuh index.
UPDATE pelanggan SET aktif = true WHERE id = 3;

SELECT id, email, aktif
FROM pelanggan
ORDER BY id;

Output yang Dihasilkan:

text
UPDATE 1
 id |      email       | aktif 
----+------------------+-------
  1 | [email protected] | t
  2 | [email protected] | t
  3 | [email protected] | t
  4 | [email protected] | f
(4 rows)

Baris id 3 kini aktif — dan pada saat yang sama sudah terdaftar di dalam idx_pelanggan_email_aktif tanpa perintah REINDEX atau pembangunan ulang. Perlindungan keunikannya aktif seketika, yang bisa kita buktikan dengan mencoba menambahkan email yang sama:

sql
duplikat-setelah-update.sql
-- Email [email protected] kini dilindungi karena baris id 3 sudah aktif.
INSERT INTO pelanggan (id, email, aktif) VALUES
    (6, '[email protected]', true);

Output yang Dihasilkan:

text

psql:duplikat-setelah-update.sql:3: ERROR:  duplicate key value violates unique constraint "idx_pelanggan_email_aktif"
DETAIL:  Key (email)=([email protected]) already exists.

Upaya INSERT ditolak dengan pesan yang sama persis seperti sebelumnya — satu-satunya perbedaan adalah baris pelanggar kini berstatus aktif. Perilaku “hidup-mati mengikuti data” inilah yang membuat partial index cocok untuk kolom status: tidak ada proses batch, tidak ada trigger, tidak ada tabel bayangan yang harus disinkronkan.

Situasi data	Alasan partial index menang
Subset panas kecil (order tertunda, invoice belum dibayar, produk aktif)	Ukuran kecil, cache-friendly, tulis murah untuk mayoritas baris
Keunikan hanya berlaku pada subset	`UNIQUE` penuh menolak data historis yang sah
Tabel besar dengan banyak data arsip	Baris arsip tidak membayar biaya index sama sekali
Predikat tidak bisa diekspresikan pada query	Index sia-sia — planner tidak akan memakainya

Syarat terakhir pada tabel itu adalah harga yang harus dibayar: partial index menuntut disiplin bahwa aplikasi selalu menyertakan predikatnya. Begitu bentuk query dan bentuk index sudah berjalan beriringan, teknik berikutnya memindahkan syarat “bentuk harus sama” itu ke arah yang lebih menarik: mengindeks bukan kolom, melainkan hasil ekspresi seperti lower(email) — topik chapter Expression Index .

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Katalog 1000 produk; hanya 5% yang berstatus aktif.
-- Data dibangun deterministik lewat generate_series.
CREATE TABLE produk (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    harga    integer NOT NULL,
    kategori text NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO produk
SELECT g,
       'Produk ' || g,
       1000 + (g * 7919) % 500000,
       (ARRAY['elektronik', 'rumah tangga', 'olahraga', 'buku', 'pakaian'])
           [1 + g % 5],
       (g % 20 = 0)
FROM generate_series(1, 1000) AS g;

ANALYZE produk;

SELECT count(*) AS total_produk,
       count(*) FILTER (WHERE aktif) AS produk_aktif
FROM produk;

sql
-- Duplikat email di antara pelanggan AKTIF ditolak.
INSERT INTO pelanggan (id, email, aktif) VALUES
    (5, '[email protected]', true);

sql
-- Email [email protected] kini dilindungi karena baris id 3 sudah aktif.
INSERT INTO pelanggan (id, email, aktif) VALUES
    (6, '[email protected]', true);

sql
-- Query DENGAN predikat index: partial index terpakai.
EXPLAIN
SELECT id, nama
FROM produk
WHERE aktif AND harga = 159380;

-- Query TANPA predikat index: partial index tidak bisa dipakai.
EXPLAIN
SELECT id, nama
FROM produk
WHERE harga = 159380;

sql
-- Baris masuk ke predikat index: UPDATE saja, tanpa menyentuh index.
UPDATE pelanggan SET aktif = true WHERE id = 3;

SELECT id, email, aktif
FROM pelanggan
ORDER BY id;

sql
-- Bandingkan ukuran index penuh vs partial pada kolom yang sama.
CREATE INDEX idx_produk_harga_penuh ON produk (harga);

CREATE INDEX idx_produk_harga_aktif ON produk (harga) WHERE aktif;

SELECT pg_size_pretty(pg_relation_size('idx_produk_harga_penuh')) AS ukuran_penuh,
       pg_size_pretty(pg_relation_size('idx_produk_harga_aktif')) AS ukuran_parsial;

-- Index penuh dijatuhkan agar demo EXPLAIN berikutnya bersih.
DROP INDEX idx_produk_harga_penuh;

sql
-- Email boleh kembar SELAMA keduanya bukan pelanggan aktif.
CREATE TABLE pelanggan (
    id    integer PRIMARY KEY,
    email text NOT NULL,
    aktif boolean NOT NULL DEFAULT true
);

INSERT INTO pelanggan (id, email, aktif) VALUES
    (1, '[email protected]', true),
    (2, '[email protected]', true),
    (3, '[email protected]', false),
    (4, '[email protected]', false);

-- Unik hanya dijalankan pada baris yang memenuhi WHERE aktif.
CREATE UNIQUE INDEX idx_pelanggan_email_aktif
    ON pelanggan (email) WHERE aktif;

SELECT id, email, aktif
FROM pelanggan
ORDER BY id;