Belajar SQL PostgreSQL - Deep Dive Indexing Strategies

#Pendahuluan

Selamat datang di episode 14 series Belajar SQL PostgreSQL! Inilah episode yang paling ditunggu banyak orang — indexing. Hampir semua masalah performa database "query-nya lambat!" berakar pada kurangnya index yang tepat. Sebaliknya, index yang salah juga bisa menjadi bumerang: memakan storage, memperlambat insert, tapi tak pernah dipakai. Memahami index adalah keterampilan yang membedakan developer biasa dari yang menguasai database.

Index di database bekerja seperti indeks di belakang buku: kalian tidak perlu membaca seluruh halaman untuk menemukan satu kata — cukup cek indeks, langsung ke halamannya. Tanpa index, database harus membaca setiap baris tabel (disebut Full Table Scan) untuk menemukan yang cocok. Semakin besar tabel, semakin lambat.

Di episode ini, kita akan membahas mengapa indexing mengubah pencarian dari O(N) menjadi O(log N), empat tipe index utama PostgreSQL (B-Tree, Hash, GIN, BRIN), lalu teknik indexing lanjutan: partial index, expression index, dan composite index beserta aturan leftmost column.

#Mengapa Indexing Sangat Krusial?

Secara formal: Full Table Scan membaca N baris — kompleksitasnya O(N). Dengan B-Tree index, database mencari lewat pohon seimbang yang kedalamannya log N — kompleksitasnya O(log N). Untuk tabel 10 juta baris, perbedaan antara membaca 10 juta baris vs sekitar 24 langkah pohon adalah perbedaan antara menit dan milidetik.

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

Tanpa index, PostgreSQL memindai semua baris users. Dengan CREATE INDEX ON users (email), query langsung menuju ke leaf node yang sesuai.

Namun perlu diingat: index adalah trade-off. Setiap index memperlambat INSERT, UPDATE, dan DELETE (karena harus dijaga tetap sinkron) dan memakan storage. Index yang tepat hanya untuk kolom yang sering difilter atau di-join.

#Tipe-Tipe Index di PostgreSQL

PostgreSQL menyediakan beberapa tipe index, masing-masing dioptimalkan untuk pola query berbeda.

#B-Tree Index: Default untuk Semua

B-Tree adalah tipe default dan paling umum. Ia mendukung operator =, <, >, <=, >=, dan BETWEEN — sekaligus mengembalikan data dalam urutan terurut (membantu ORDER BY). Ini pilihan yang tepat untuk hampir semua kolom skalar.

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created_at ON orders (created_at);

CREATE INDEX idx_users_email ON users (email) tanpa menyebut tipe akan membuat B-Tree. Index ini akan mempercepat WHERE email = '...' dan ORDER BY email.

#Hash Index: Khusus Kesamaan

Hash index dioptimalkan khusus operator =. Ia memetakan nilai ke bucket hash, sehingga pencarian kesamaan sangat cepat. Sejak PostgreSQL 10, Hash index bisa direplikasi dan loggable — aman dipakai.

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Kapan memakai Hash? Hampir semua case kesamaan sudah ditangani B-Tree dengan sangat baik, jadi Hash jarang membawa peningkatan signifikan. Ia biasanya menang hanya pada kolom dengan nilai sangat panjang yang tidak butuh operasi range.

#GIN Index: Untuk JSONB, Array, dan FTS

GIN (Generalized Inverted Index) adalah index "terbalik": ia memetakan setiap elemen ke baris yang mengandungnya. Ini tipe yang tepat untuk:

• Kolom Array: mencari elemen di dalam array.
• Kolom JSONB: operator @> dan ?.
• Full-Text Search: pencarian teks (episode 17).

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

Index pertama mempercepat query JSONB seperti WHERE attributes @> '{"warna": "hitam"}' (contoh episode 8). Index kedua mempercepat pencarian tag seperti WHERE 'sql' = ANY(tags).

#BRIN Index: Hemat Storage untuk Data Berurutan

BRIN (Block Range Index) menyimpan ringkasan rentang nilai per blok halaman, bukan per baris. Ukurannya sangat kecil — puluhan ribu kali lebih hemat dari B-Tree pada tabel raksasa. Cocok untuk tabel time-series (log, event, sensor) di mana data berurutan berdasarkan waktu.

CREATE INDEX idx_events_created_at ON events USING BRIN (created_at);

Tabel dengan jutaan baris log bisa diindex BRIN hanya dengan beberapa megabyte, sedangkan B-Tree bisa memakan gigabyte. Tapi BRIN hanya efisien jika datanya fisik berurutan di disk — pada tabel yang di-insert acak atau sering diupdate, performanya menurun.

#Teknik Indexing Lanjutan

Setelah memahami tipe dasar, sekarang teknik yang menghemat storage dan meningkatkan performa.

#Partial Index: Index Hanya untuk Subset Data

Partial index mengindex hanya baris yang memenuhi kondisi WHERE pada saat pembuatan. Konsepnya: banyak query hanya menyaring nilai tertentu (misal status active), jadi mengindex seluruh tabel sia-sia.

CREATE INDEX idx_users_active_email
ON users (email)
WHERE is_active = TRUE;

Index ini hanya berisi baris user aktif. Query WHERE is_active = TRUE AND email = '...' akan memakainya, dan ukurannya jauh lebih kecil daripada index penuh. Teknik ini populer untuk tabel dengan status yang sebagian besar datanya "mati".

#Expression Index: Index pada Hasil Fungsi

Expression index mengindex hasil dari sebuah ekspresi. Contoh klasik: pencarian email case-insensitive. Tanpa expression index, query WHERE LOWER(email) = 'budi@x.com' tidak bisa memakai index biasa:

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

Setelah index ini dibuat, query WHERE LOWER(email) = 'budi@example.com' menjadi Index Scan. Pola yang sama berlaku untuk WHERE (price * quantity) > 1000 dan fungsi-fungsi lain yang dipakai di filter.

#Composite Index dan Aturan Leftmost Column

Composite index mengindex beberapa kolom sekaligus, dalam urutan tertentu:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Index ini mempercepat query yang menyaring customer_id saja, customer_id + created_at, dan ORDER BY customer_id, created_at. Tapi ini memunculkan aturan leftmost column: index hanya bisa dipakai jika filter menggunakan kolom paling kiri terlebih dahulu. Query yang hanya memfilter created_at tanpa customer_id tidak akan memakai index di atas.

#Memeriksa Index yang Ada

\d users

\d users di psql menampilkan semua index beserta kolomnya. Untuk daftar index seluruh database, query pg_indexes:

SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

#Kesalahan Umum

#Penutup

Di episode 14 ini kita sudah mendalami indexing: mengapa index mengubah Full Table Scan O(N) menjadi Index Scan O(log N), empat tipe index utama (B-Tree, Hash, GIN, BRIN) beserta kasus penggunaan masing-masing, dan teknik lanjutan partial index, expression index, serta composite index dengan aturan leftmost column.

Inti yang harus dibawa pulang:

• Index mengubah pencarian dari O(N) menjadi O(log N) — perbedaan menit vs milidetik di tabel besar.
• B-Tree untuk range/sama-dengan, GIN untuk JSONB/Array/FTS, BRIN untuk time-series.
• Partial index hanya mengindex subset — kecil dan cepat.
• Expression index mengindex hasil fungsi seperti LOWER(email).
• Composite index mengikuti aturan leftmost column — urutkan kolom dengan benar.

Di episode 15 selanjutnya, kita belajar membuktikan index bekerja: Query Optimization & EXPLAIN ANALYZE — mulai dari membaca execution plan, memahami Sequential Scan vs Index Scan vs Bitmap, join algorithms seperti Nested Loop dan Hash Join, hingga identifikasi query lambat dengan ekstensi pg_stat_statements.