Index di PostgreSQL dan Trade-off

Kalau kamu pernah bikin aplikasi CRUD, mungkin kamu pernah mengalami hal ini:

Awalnya aplikasi terasa cepat. Tapi setelah data bertambah puluhan ribu atau jutaan row, query yang tadinya instan mulai terasa lambat.

Banyak orang langsung menyimpulkan, "Berarti harus bikin index."

Padahal sebelum membahas index, kita perlu memahami dulu kenapa database bisa lambat.

Sebelum Bahas Index, Pahami Cara Database Membaca Data

Komputer menyimpan data di dua tempat utama: RAM dan disk.

RAM sangat cepat, tetapi bersifat sementara. Ketika komputer dimatikan, isinya akan hilang.

Disk (SSD atau HDD) jauh lebih lambat dibanding RAM, tetapi bersifat permanen sehingga cocok untuk menyimpan data database.

Sebagai gambaran:

Akses RAM sekitar 100 nanosecond
Akses SSD sekitar puluhan hingga ratusan microsecond

Artinya, membaca data dari disk bisa ratusan hingga ribuan kali lebih lambat dibanding membaca data yang sudah ada di RAM.

Karena itulah PostgreSQL menyimpan data secara permanen di disk, tetapi akan berusaha menyimpan data yang sering diakses di memori (shared buffers dan cache dari sistem operasi).

Saat menjalankan query, PostgreSQL selalu berusaha mengurangi pekerjaan yang harus dilakukan. Dalam banyak kasus, pekerjaan yang paling mahal adalah membaca terlalu banyak data. Itulah alasan mengapa query yang membaca sedikit data biasanya jauh lebih cepat dibanding query yang harus membaca seluruh tabel.

Setup Percobaan

Supaya lebih mudah dipahami, kita buat tabel sederhana berisi 10.000 data produk.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2),
    stock INT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO products (name, price, stock, category)
SELECT
    'Product ' || i,
    (random() * 1000)::DECIMAL(10,2),
    (random() * 100)::INT,
    (ARRAY['electronics', 'food', 'clothing', 'books'])[floor(random() * 4 + 1)]
FROM generate_series(1, 10000) AS i;

Tanpa Index Apa yang Terjadi?

Sekarang kita jalankan query berikut.

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'electronics';

Misalnya hasilnya seperti ini.

Seq Scan on products
actual time=0.096..1.867 rows=2426
Rows Removed by Filter: 7574
Execution Time: 2.079 ms

Hal pertama yang perlu diperhatikan adalah:

Seq Scan

Artinya PostgreSQL melakukan Sequential Scan.

Sequential Scan berarti PostgreSQL membaca seluruh halaman (page) tabel secara berurutan, kemudian memeriksa setiap row untuk melihat apakah memenuhi kondisi query.

Dari 10.000 row yang ada:

2.426 row cocok
7.574 row dibaca tetapi akhirnya dibuang

Semakin besar tabel, semakin banyak data yang harus diperiksa.

Bayangkan seperti mencari satu nama diberkas atau dibuku telepon mungkin tanpa daftar isi. Kamu harus membuka halaman pertama, lalu membaca satu per satu sampai menemukan nama yang dicari.

Dengan Index Bedanya Sangat Terasa

Sekarang kita tambahkan index pada kolom category.

CREATE INDEX idx_products_category
ON products(category);

Lalu jalankan query yang sama.

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'electronics';

Hasilnya bisa berubah menjadi seperti berikut.

Bitmap Index Scan on idx_products_category
Heap Blocks: exact=94
Execution Time: 0.711 ms

Execution time turun dari sekitar 2 ms menjadi 0,7 ms.

Perbedaannya bukan karena PostgreSQL "bekerja lebih keras", tetapi karena sekarang database tahu di mana lokasi data yang dibutuhkan.

Index bekerja seperti daftar isi pada sebuah buku.

Tanpa daftar isi, kamu harus membaca seluruh halaman.

Dengan daftar isi, kamu langsung menuju bagian yang relevan.

Perhatikan juga bagian berikut.

Heap Blocks: exact=94

Artinya PostgreSQL hanya perlu mengakses sekitar 94 halaman data (heap page), jauh lebih sedikit dibanding harus membaca seluruh tabel.

Semakin sedikit halaman yang perlu diakses, semakin sedikit pekerjaan yang dilakukan database.

Kenapa Index Bisa Lebih Cepat?

Sebagian besar index di PostgreSQL menggunakan struktur data B-Tree.

B-Tree menyimpan data dalam bentuk pohon yang sudah terurut.

Misalnya seperti ini.

             50
          /      \
       20         80
      /  \      /   \
    10   30   60    90

Kalau ingin mencari angka 60, kita tidak perlu memeriksa semua angka satu per satu.

Cukup:

Semakin besar jumlah data, semakin terasa perbedaannya dibanding membaca seluruh tabel.

Tapi Index Tidak Selalu Dipakai

Ini bagian yang sering membuat orang bingung.

Misalnya kita punya index pada kolom price.

Lalu menjalankan query berikut.

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price > 10;

Walaupun index tersedia, PostgreSQL bisa saja tetap memilih:

Seq Scan

Kenapa?

Karena query tersebut mengembalikan hampir seluruh data.

Misalnya:

Total row = 10.000
Hasil query = 9.895 row

Kalau menggunakan index, PostgreSQL harus mencari ribuan pointer di index, lalu mengambil ribuan row dari tabel.

Dalam kondisi seperti ini, membaca seluruh tabel secara berurutan justru lebih murah dibanding menggunakan index.

Sebaliknya, kalau query menjadi lebih selektif:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price > 990;

Misalnya hanya menghasilkan sekitar 110 row.

Barulah PostgreSQL biasanya memilih menggunakan index karena jumlah data yang diambil relatif sedikit.

Yang perlu dipahami adalah:

PostgreSQL tidak selalu menggunakan index. Database akan memilih rencana eksekusi (query plan) dengan estimasi biaya paling rendah berdasarkan statistik tabel.

Siapa yang Menentukan Apakah Index Dipakai?

Jawabannya adalah Query Planner.

Setiap kali menerima query, PostgreSQL akan berpikir terlebih dahulu.

Kurang lebih seperti ini.

"Lebih cepat pakai index atau baca seluruh tabel?"

Keputusan itu dibuat berdasarkan banyak faktor, misalnya:

jumlah row pada tabel
statistik distribusi data
estimasi jumlah hasil query (selectivity)
biaya membaca index
biaya membaca tabel

Makanya dua query yang terlihat mirip belum tentu menggunakan strategi yang sama.

Karena itulah kita menggunakan:

EXPLAIN ANALYZE

untuk melihat bagaimana PostgreSQL benar-benar menjalankan query.

Index Juga Ada Biayanya

Index memang mempercepat operasi baca (SELECT).

Tetapi ada konsekuensinya.

Misalnya kita mengukur waktu insert.

Tanpa index:

10.000 row
≈ 52 ms

Dengan dua index:

10.000 row
≈ 114 ms

Angka ini bisa berbeda pada setiap komputer, tetapi polanya hampir selalu sama.

Kenapa?

Karena setiap kali terjadi:

INSERT
UPDATE
DELETE

PostgreSQL tidak hanya mengubah data di tabel, tetapi juga harus memperbarui semua index yang berkaitan.

Semakin banyak index, semakin banyak pekerjaan tambahan yang harus dilakukan.

Jadi index selalu memiliki trade-off.

SELECT menjadi lebih cepat.
INSERT, UPDATE, dan DELETE menjadi sedikit lebih lambat.

Jadi Kapan Sebaiknya Membuat Index?

Gunakan index jika:

Kolom sering digunakan pada klausa WHERE.
Kolom sering digunakan untuk JOIN.
Kolom sering digunakan untuk ORDER BY atau GROUP BY.
Query hanya mengambil sebagian kecil data dari tabel.

Contohnya:

SELECT *
FROM users
WHERE email = 'wahid@example.com';

atau

SELECT *
FROM transfers
WHERE wallet_id = 10;

Kedua query tersebut sangat cocok menggunakan index.

Kapan Sebaiknya Tidak Membuat Index?

Tidak semua kolom membutuhkan index.

Misalnya:

Kolom hampir tidak pernah digunakan untuk pencarian.
Tabel masih sangat kecil sehingga Sequential Scan sudah cukup cepat.
Kolom memiliki variasi nilai yang sangat sedikit (misalnya boolean), kecuali memang pola query menunjukkan index tersebut bermanfaat.

Semakin banyak index, semakin besar biaya penyimpanan dan semakin berat operasi write.

Kesimpulan

Index bukanlah hal ajaib yang membuat semua query menjadi cepat.

Index adalah struktur data tambahan yang membantu PostgreSQL menemukan data lebih efisien, tetapi juga menambah biaya pada operasi INSERT, UPDATE, dan DELETE.

Yang lebih penting lagi, memiliki index bukan berarti PostgreSQL pasti menggunakannya.

Database akan selalu memilih query plan yang dianggap paling murah berdasarkan statistik yang dimilikinya.

Karena itu, jangan membuat index di semua kolom.

Pahami dulu bagaimana aplikasi melakukan query, lihat hasil EXPLAIN ANALYZE, kemudian buat index pada kolom yang benar-benar sering digunakan.

Itulah cara kerja optimasi database di dunia nyata: bukan menambahkan index sebanyak mungkin, tetapi menambahkan index di tempat yang tepat.