Indexing PostgreSQL: Kapan Index Benar-Benar Mempercepat dan Kapan Cuma Membebani Database

Minggu lalu saya menerima laporan dari tim bahwa endpoint laporan transaksi di salah satu SaaS multi-tenant yang saya kelola tiba-tiba lambat — dari 200ms menjadi 8 detik. Ternyata seorang developer menambahkan enam index baru "supaya cepat", tanpa sadar bahwa query utamanya justru tidak memakai index mana pun, sementara tabel write-heavy-nya kini harus memelihara enam struktur B-tree ekstra. Itulah momen yang membuat saya sadar: masalah index di PostgreSQL jarang soal kurang index, tapi index yang salah kolom, salah tipe, atau tidak pernah terpakai sama sekali.

Artikel ini adalah rangkuman pendekatan yang sekarang saya pakai setelah dua kali kena masalah serupa — kapan index layak dibuat, kapan tidak, dan bagaimana membuktikannya lewat EXPLAIN, bukan feeling.

Kenapa Index Mempercepat Query (dan Kenapa Bukan Selalu)

Index di PostgreSQL — biasanya B-tree — bekerja seperti daftar isi: mencari satu baris dari jutaan baris jadi operasi O(log n) alih-alih full table scan. Tapi index bukan magic. Query hanya memakai index kalau polanya cocok dengan urutan dan tipe index-nya.

Kesalahan paling sering saya lihat: index dibuat pada kolom yang tidak muncul di predicate query, atau fungsi diterapkan di kolom yang terindeks sehingga index-nya diabaikan. Contoh klasik:

-- Index di kolom email
CREATE INDEX idx_users_email ON users(email);

-- Query ini TIDAK pakai index, karena lower() memutus pola
SELECT * FROM users WHERE lower(email) = 'tomi@example.com';

Solusinya adalah expression index:

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

Membaca EXPLAIN: Buktikan Sebelum Percaya

Saya punya aturan pribadi: tidak ada index yang saya buat tanpa EXPLAIN ANALYZE sebelum dan sesudah. Perintah ini menjalankan query sungguhan dan menunjukkan rencana eksekusi aktual:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

Yang saya cari:

• Seq Scan pada tabel besar — kandidat kuat butuh index.
• Rows Removed by Filter: 500000 — database membaca 500 ribu baris lalu membuangnya. Ini tanda index hilang atau tidak terpakai.
• Index Scan dengan actual time yang turun signifikan setelah index dibuat.

Catatan kecil yang sering dilupakan: ANALYZE benar-benar mengeksekusi query, jadi jangan jalankan EXPLAIN ANALYZE DELETE ... di production database tanpa transaksi yang di-rollback.

Pola Index yang Terbukti Berguna di Production

Composite Index dan Urutan Kolomnya

Untuk query multi-tenant (hampir semua query selalu punya tenant_id), composite index adalah penyelamat saya:

CREATE INDEX idx_orders_tenant_status_created
ON orders(tenant_id, status, created_at DESC);

Urutan kolom penting: kolom equality (tenant_id = ?, status = ?) dulu, kolom range/sort (created_at) belakangan. Saya pernah membuat index dengan urutan terbalik dan heran kenapa ORDER BY created_at tetap memicu sort mahal di luar index.

Partial Index untuk Data Panas

Kalau 95% query hanya menyentuh status = 'pending', kenapa index-nya harus mencakup seluruh tabel? Partial index jauh lebih kecil dan lebih cepat:

CREATE INDEX idx_orders_pending
ON orders(tenant_id, created_at)
WHERE status = 'pending';

Di proyek saya, ukurannya sekitar 8% dari index penuh — dan query queue justru lebih cepat karena lebih sedikit halaman yang dibaca.

Tipe Index Lain Selain B-tree

• GIN untuk kolom jsonb dan array — ya, PostgreSQL bisa mengindeks kolom array, dan ini menjawab pertanyaan lama di Stack Overflow yang sudah ratusan vote.
• BRIN untuk tabel append-only berukuran raksasa (log, event), di mana urutan fisik baris mengikuti kolom waktu.
• pgvector/ekstensi vector seperti VectorChord untuk similarity search — bahkan ada yang mengindeks 100 juta vektor dalam 20 menit di mesin 12GB RAM, yang menunjukkan seberapa jauh indexing PostgreSQL sudah berkembang jauh melampaui B-tree klasik.

Sisi Gelapnya: Kapan Index Membebani

Ini bagian yang jarang dibahas. Setiap index adalah struktur data yang harus diperbarui di setiap INSERT, UPDATE, dan DELETE pada tabelnya. Konsekuensi konkret yang saya ukuran sendiri:

• Tabel event log yang menerima ~2.000 insert/detik mengalami throughput turun hampar 40% setelah empat index tambahan ditambahkan "untuk jaga-jaga". Begitu index tak terpakai itu dihapus, throughput kembali normal.
• Setiap index memakan disk dan memperbesar kebutuhan vacuum.
• Index yang tidak pernah dipakai adalah utang murni — biaya terus dibayar, manfaat nol.

Cara mendeteksinya, lewat statistik bawaan PostgreSQL:

SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

Index dengan idx_scan = 0 selama beberapa pekan di production adalah kandidat kuat untuk di-drop. Saya menjadwalkan pengecekan ini bulanan, sama ritualnya dengan mengecek slow query log. Ini juga melengkapi audit query level aplikasi — pola N+1 yang pernah saya tangani di Prisma sering menyamarkan masalah index, karena biaya query kecil berulang tidak terlihat di query log sampai Anda menggabungkan keduanya.

Checklist yang Saya Pakai Sebelum Membuat Index

Sebelum CREATE INDEX, saya jawab lima pertanyaan ini:

1. Apakah EXPLAIN menunjukkan Seq Scan dengan Rows Removed by Filter besar pada tabel >100 ribu baris?
2. Apakah urutan kolom composite index cocok dengan predicate dan ORDER BY query dominan?
3. Apakah partial index cukup, ketimbang index penuh?
4. Seberapa sering tabel ini ditulis? Write-heavy table menuntut alasan jauh lebih kuat.
5. Apakah saya pakai CREATE INDEX CONCURRENTLY agar tidak mengunci tabel production?

Poin terakhir sering dilupakan: CREATE INDEX biasa mengambil lock yang memblokir write. Di tabel aktif, selalu gunakan CREATE INDEX CONCURRENTLY, meskipun lebih lambat dan tidak bisa jalan di dalam transaksi.

Takeaway

Index yang tepat bisa memotong query dari detik ke milidetik; index yang asal tambah bisa memperlambat keseluruhan sistem sambil memakan disk. Jangan pernah menambah index karena "kayaknya butuh" — buktikan dengan EXPLAIN ANALYZE, pantau pg_stat_user_indexes untuk index zombie, dan biasakan CONCURRENTLY di production. Kalau Anda juga menangani query boros di level ORM, cek juga tulisan saya tentang memperbaiki N+1 query problem di Prisma dan Laravel Eloquent, karena dua masalah ini sering muncul bersamaan. Dan untuk task berat hasil query besar yang tidak harus sinkron, saya biasanya mendorongnya ke background job dengan BullMQ alih-alih mengandalkan index untuk menyelamatkan endpoint.

Sumber

• A Collection of PostgreSQL Indexing Tips — dasar praktik indexing yang sampai sekarang masih relevan.
• PostgreSQL indexing in Rails — contoh pola index composite dan partial dari aplikasi nyata.
• List columns with indexes in PostgreSQL — query katalog untuk mengaudit index yang ada.
• Can PostgreSQL index array columns? — dasar penggunaan GIN index pada kolom array.
• Indexing 100M vectors in 20 minutes on PostgreSQL — bukti kemampuan indexing PostgreSQL di luar B-tree konvensional.