D.N. Zaki Zamani
Cara Membuat Index PostgreSQL untuk Performa Query

Cara Membuat Index PostgreSQL untuk Performa Query


NEW
postgresql database optimasi

Kenapa Index PostgreSQL Itu Penting?

Cara membuat index PostgreSQL yang tepat adalah salah satu skill paling krusial bagi developer backend. Tanpa index, PostgreSQL harus melakukan sequential scan - membaca seluruh tabel baris per baris untuk menemukan data yang kamu cari. Bayangkan tabel dengan jutaan baris, dan setiap query harus scan semuanya. Lambat, boros resource, dan bikin user frustasi.

Index bekerja seperti daftar isi di buku. Daripada membaca seluruh halaman, kamu langsung loncat ke halaman yang tepat. Di artikel ini, kita akan bahas jenis-jenis index, kapan harus pakai masing-masing, dan kesalahan umum yang sering dilakukan developer.

Jenis-Jenis Index di PostgreSQL

PostgreSQL punya beberapa jenis index. Masing-masing punya use case yang berbeda.

B-Tree Index (Default)

B-Tree adalah jenis index default di PostgreSQL. Cocok untuk operasi perbandingan seperti =, <, >, <=, >=, dan BETWEEN.

-- Membuat B-Tree index
CREATE INDEX idx_users_email ON users (email);

-- PostgreSQL otomatis pakai B-Tree kalau tidak disebut jenisnya
-- Query yang terbantu:
SELECT * FROM users WHERE email = 'zaki@example.com';

B-Tree juga mendukung sorting. Jadi kalau kamu sering pakai ORDER BY pada kolom tertentu, B-Tree index sangat membantu.

-- Index untuk sorting
CREATE INDEX idx_orders_created ON orders (created_at DESC);

-- Query ini jadi cepat:
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

Hash Index

Hash index hanya berguna untuk operasi equality (=). Lebih compact dari B-Tree untuk kasus ini, tapi tidak support sorting atau range query.

CREATE INDEX idx_sessions_token ON sessions USING hash (token);

-- Optimal untuk:
SELECT * FROM sessions WHERE token = 'abc123xyz';

Sejak PostgreSQL 10, hash index sudah WAL-logged dan aman untuk production. Tapi dalam praktiknya, B-Tree hampir selalu cukup.

GIN Index (Generalized Inverted Index)

GIN index adalah pilihan terbaik untuk kolom yang berisi array, JSONB, atau full-text search.

-- Index untuk kolom JSONB
CREATE INDEX idx_products_metadata ON products USING gin (metadata);

-- Query JSONB yang terbantu:
SELECT * FROM products WHERE metadata @> '{"category": "elektronik"}';

-- Index untuk full-text search
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('indonesian', title || ' ' || content));

GIN index lebih lambat saat INSERT/UPDATE dibanding B-Tree, tapi jauh lebih cepat saat query data bertipe kompleks.

GiST Index (Generalized Search Tree)

GiST cocok untuk data geometri, range, dan full-text search. Kalau kamu kerja dengan data geospasial (PostGIS), GiST adalah pilihan utama.

-- Index untuk range query
CREATE INDEX idx_events_duration ON events USING gist (duration);

-- Index untuk data geometri
CREATE INDEX idx_locations_point ON locations USING gist (coordinates);

Composite Index: Gabungan Beberapa Kolom

Kalau query kamu sering filter berdasarkan kombinasi kolom, composite index lebih efisien daripada membuat index terpisah.

-- Composite index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Query yang optimal:
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Query ini JUGA terbantu (kolom pertama match):
SELECT * FROM orders WHERE user_id = 42;

-- Tapi query ini TIDAK terbantu (kolom pertama di-skip):
SELECT * FROM orders WHERE status = 'pending';

Aturan penting: urutan kolom dalam composite index sangat berpengaruh. PostgreSQL hanya bisa menggunakan index dari kiri ke kanan. Letakkan kolom dengan selectivity tinggi (paling sedikit duplikat) di posisi pertama.

Partial Index: Index dengan Kondisi

Partial index hanya meng-index baris yang memenuhi kondisi tertentu. Ini menghemat storage dan mempercepat query untuk subset data tertentu.

-- Hanya index order yang belum selesai
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status IN ('pending', 'processing');

-- Query yang terbantu:
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01';

Partial index sangat berguna di tabel besar di mana kamu hanya sering query sebagian kecil data. Misalnya, dari 10 juta order, mungkin hanya 1% yang statusnya pending.

Unique Index: Menjaga Integritas Data

Unique index memastikan tidak ada nilai duplikat di kolom tertentu. PostgreSQL otomatis membuat unique index saat kamu define UNIQUE constraint atau PRIMARY KEY.

-- Unique index eksplisit
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- Unique partial index - email unik hanya untuk user aktif
CREATE UNIQUE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

Pattern unique partial index sangat berguna untuk soft delete. User yang sudah dihapus boleh punya email yang sama dengan user aktif.

Cara Cek Apakah Index Digunakan

Membuat index saja tidak cukup. Kamu harus verifikasi bahwa PostgreSQL benar-benar menggunakannya.

Gunakan EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'zaki@example.com';

Output yang kamu harapkan:

Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=128)
  Index Cond: (email = 'zaki@example.com'::text)
Planning Time: 0.089 ms
Execution Time: 0.031 ms

Kalau masih terlihat Seq Scan, kemungkinan:

  • Data terlalu sedikit (PostgreSQL memilih seq scan karena lebih cepat)
  • Query tidak cocok dengan index
  • Statistik tabel belum update - jalankan ANALYZE nama_tabel;

Cek Index yang Tidak Terpakai

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Index yang tidak pernah dipakai hanya membuang storage dan memperlambat write. Hapus kalau memang tidak diperlukan.

Kesalahan Umum Saat Membuat Index

1. Over-Indexing

Setiap index memperlambat operasi INSERT, UPDATE, dan DELETE karena PostgreSQL harus menjaga semua index tetap sinkron. Jangan buat index untuk setiap kolom - buat hanya untuk kolom yang sering di-query.

2. Index pada Kolom Low Cardinality

Index pada kolom boolean atau kolom dengan sedikit variasi nilai (misalnya status yang hanya punya 3 nilai) biasanya tidak efektif. PostgreSQL lebih memilih seq scan.

Pengecualian: partial index pada subset kecil tetap berguna.

-- Kurang efektif:
CREATE INDEX idx_users_active ON users (is_active);

-- Lebih efektif:
CREATE INDEX idx_users_inactive ON users (id) WHERE is_active = false;

3. Lupa CONCURRENTLY di Production

Membuat index pada tabel besar akan lock tabel untuk write. Di production, selalu gunakan CONCURRENTLY:

-- JANGAN lakukan ini di production pada tabel besar:
CREATE INDEX idx_big_table ON big_table (column);

-- Lakukan ini:
CREATE INDEX CONCURRENTLY idx_big_table ON big_table (column);

CONCURRENTLY memakan waktu lebih lama, tapi tidak memblokir operasi write.

4. Tidak Menjalankan ANALYZE

Setelah membuat index atau load data besar, jalankan ANALYZE agar PostgreSQL update statistik tabel. Tanpa statistik yang akurat, query planner bisa membuat keputusan yang salah.

ANALYZE users;
-- Atau untuk semua tabel:
ANALYZE;

Tips Praktis untuk Developer

  1. Mulai tanpa index, jalankan query, lalu tambahkan index berdasarkan kebutuhan nyata dari EXPLAIN ANALYZE
  2. Monitor index usage secara rutin via pg_stat_user_indexes
  3. Gunakan partial index untuk tabel besar dengan query yang hanya menyentuh subset data
  4. Perhatikan urutan kolom di composite index - selalu dari selectivity tinggi ke rendah
  5. Hapus index yang tidak terpakai - mereka hanya membebani write performance

Kesimpulan

Membuat index PostgreSQL yang tepat bisa mengubah query dari hitungan detik menjadi milidetik. Kuncinya bukan membuat index sebanyak-banyaknya, tapi memahami pola query aplikasi kamu dan memilih jenis index yang sesuai. Mulai dari B-Tree untuk kasus umum, gunakan GIN untuk JSONB dan full-text search, dan manfaatkan partial index untuk menghemat resource.

Selalu validasi dengan EXPLAIN ANALYZE dan monitor secara berkala. Index yang dibuat tanpa analisis hanya akan menjadi beban, bukan solusi.

© 2026 D.N. Zaki Zamani