Optimasi database relasional sering diasumsikan bekerja pada distribusi data seragam (uniform distribution). Dalam sistem produksi riil, kondisi ini jarang terpenuhi. Fenomena data skew—kondisi saat sebaran nilai kolom sangat timpang, misalnya perbandingan data arsip terhadap aktif sebesar 99:1—kerap memicu query planner PostgreSQL mengambil keputusan eksekusi yang keliru.

Ketika query planner mengabaikan indeks atau sebaliknya memilih index scan yang tidak efisien, penambahan indeks B-Tree standar sering memperburuk performa write tanpa memperbaiki latency read. Artikel ini membahas akar kegagalan B-Tree pada data asimetris, identifikasi anomali estimasi biaya melalui EXPLAIN (ANALYZE, BUFFERS), serta implementasi partial index dan extended statistics untuk memulihkan efisiensi database.

Mengapa B-Tree Standar Gagal Menangani Data Skew

Indeks B-Tree konvensional menyimpan referensi pointer ke setiap baris tabel, terlepas dari frekuensi kemunculan nilai tersebut. Masalah muncul dari dua skenario:

  • Low Selectivity (Nilai Mayoritas): Jika sebuah nilai mencakup 99% isi tabel (misal status archived), query planner PostgreSQL akan memprediksi bahwa Sequential Scan lebih murah daripada Index Scan. Membaca indeks B-Tree lalu melompat ke heap page secara acak (random I/O) jauh lebih lambat dibanding membaca blok data berurutan (sequential I/O). B-Tree pada kolom ini menjadi dead weight: mengonsumsi storage dan buffer cache, memperlambat proses INSERT/UPDATE, tetapi tidak digunakan.
  • Korelasi Multi-Kolom Buta: Jika query menyaring berdasarkan kombinasi kolom yang saling berkorelasi (misal status processed dan flag retry_count = 0), planner standar mengasumsikan independensi statistik. Rumus probabilitas gabungan mengalikan selektivitas masing-masing kolom: P(A and B) = P(A) * P(B). Jika kedua kolom berkorelasi akibat data skew, hasil estimasi baris (rows estimation) meleset secara ekstrem, berujung pada pemilihan join algorithm atau scan method yang salah.

Setup Kasus: Anomali Data Antrean Pesanan

Berikut skema tabel transaksi pemrosesan pesanan dengan rasio status PROCESSED (99%) berbanding PENDING (1%):

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed 1.000.000 baris dengan 99% PROCESSED dan 1% PENDING
INSERT INTO orders (customer_id, status, total_amount, created_at)
SELECT 
    (random() * 100000)::int,
    CASE WHEN random() < 0.99 THEN 'PROCESSED' ELSE 'PENDING' END,
    (random() * 500 + 10)::numeric(12,2),
    NOW() - (random() * interval '90 days')
FROM generate_series(1, 1000000);

-- Tambah indeks B-Tree standar
CREATE INDEX idx_orders_status ON orders(status);
VACUUM ANALYZE orders;

Identifikasi Masalah via EXPLAIN ANALYZE

Saat mencari data PENDING, PostgreSQL dapat menggunakan indeks. Namun, ukuran idx_orders_status membengkak karena menyimpan 990.000 pointer baris PROCESSED yang tidak pernah difilter secara spesifik oleh transaksi operasional.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'PENDING';

Hasil eksekusi:

Bitmap Heap Scan on orders  (cost=115.34..12890.12 rows=10045 width=37) (actual time=0.820..4.150 rows=9980 loops=1)
  Recheck Cond: ((status)::text = 'PENDING'::text)
  Buffers: shared hit=4320
  ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..112.83 rows=10045 width=0) (actual time=0.680..0.680 rows=9980 loops=1)
        Index Cond: ((status)::text = 'PENDING'::text)
        Buffers: shared hit=34

Kelemahan pendekatan ini:

  1. Ukuran indeks mencakup 1.000.000 entri, memakan kapasitas RAM (shared buffers) secara tidak perlu.
  2. Dampak modifikasi data: Setiap transaksi yang memperbarui status dari PENDING ke PROCESSED harus menulis ulang indeks B-Tree dan menghasilkan write amplification.

Solusi 1: Implementasi Partial Indexing

Gunakan indeks parsial (partial index) untuk membatasi cakupan indeks hanya pada nilai minoritas yang membutuhkan penelusuran cepat.

-- Hapus indeks lama yang tidak efisien
DROP INDEX idx_orders_status;

-- Buat partial index hanya untuk data target operasional
CREATE INDEX idx_orders_pending_partial ON orders(created_at) 
WHERE status = 'PENDING';

VACUUM ANALYZE orders;

Jalankan kembali query pencarian status PENDING:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE status = 'PENDING' 
ORDER BY created_at ASC;

Output rencana eksekusi:

Index Scan using idx_orders_pending_partial on orders  (cost=0.29..320.10 rows=9980 width=37) (actual time=0.019..2.110 rows=9980 loops=1)
  Buffers: shared hit=845

Keuntungan langsung dari partial indexing:

  • Penurunan Shared Buffers Hit: Dari ~4.354 buffer hits menjadi 845 buffer hits karena seluruh pointer data PROCESSED diabaikan dari struktur pohon B-Tree.
  • Ukuran Indeks Minimal: Ukuran indeks menyusut hingga ~99% dibanding indeks penuh, membebaskan memory footprint di working memory (RAM).
  • Maintenance Cost Rendah: Modifikasi data pada baris berstatus PROCESSED tidak lagi memicu update pada indeks idx_orders_pending_partial.

Catatan: Query predicate pada klausa WHERE aplikasi harus identik atau merupakan subset logika dari predikat partial index. Query SELECT * FROM orders WHERE status = 'PROCESSED' otomatis mengabaikan indeks ini dan memilih Sequential Scan yang memang merupakan jalur I/O terefisien untuk data mayoritas.

Solusi 2: Mengatasi Korelasi Antarkolom via Extended Statistics

Data skew sering diperparah oleh ketergantungan antar kolom yang memicu kesalahan estimasi planner rows secara signifikan. Contoh kasus:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE status = 'PENDING' AND total_amount > 450.00;

Jika PostgreSQL mengasumsikan kolom status dan total_amount independen, planner dapat mengkalkulasi estimasi rows=100 padahal baris aktualnya rows=2500. Perbedaan ini menyebabkan planner memilih Nested Loop alih-alih Hash Join saat digabungkan dengan tabel lain, menghabiskan waktu eksekusi CPU akibat under-allocation pada work_mem.

Gunakan fitur PostgreSQL Extended Statistics untuk menangkap korelasi data:

CREATE STATISTICS stats_orders_status_amount (dependencies, mcv) 
ON status, total_amount FROM orders;

ANALYZE orders;
  • dependencies: Memberitahukan planner tingkat ketergantungan fungsional antara status dan nilai order.
  • mcv (Most Common Values): Menghasilkan daftar nilai kombinasi yang paling sering muncul beserta frekuensi sebenarnya, mengatasi skew gabungan.

Setelah statistik diperbarui, nilai rows pada EXPLAIN akan mendekati baris riil actual rows, mencegah pemilihan execution node yang keliru.

Trade-offs dan Kesalahan Umum

  • Kueri Dinamis Mengabaikan Indeks: Menggunakan variabel parameter seperti WHERE status = $1 sering kali memicu prepared statement fallback ke generic plan, yang mengabaikan partial index. Solusinya, gunakan literal string untuk kondisi filter status skew, atau manfaatkan dynamic SQL di level repository code.
  • Over-indexing Predikat: Membuat terlalu banyak partial index dengan predikat terfragmentasi (misal status pending, review, retrying) meningkatkan overhead metadata tracking. Batasi partial index hanya untuk data anomali berkonsentrasi tinggi (< 5-10% dari total tabel).