B-tree vs GIN vs GiST: panduan praktis indeks PostgreSQL

Apa yang sebenarnya Anda pilih saat memilih indeks

Kebanyakan masalah indeks PostgreSQL bermula sama: tampilan daftar terasa cepat pada 1.000 baris, lalu melambat pada 1.000.000. Atau kotak pencarian yang bekerja saat pengujian berubah menjadi jeda beberapa detik di produksi. Saat itu terjadi, menggoda untuk bertanya, “Indeks mana yang terbaik?” Pertanyaan yang lebih baik adalah: “Apa yang layar ini minta database lakukan?”

Tabel yang sama bisa membutuhkan tipe indeks berbeda karena layar berbeda membacanya dengan cara berbeda. Satu tampilan memfilter berdasarkan satu status dan disortir oleh created_at. Lainnya melakukan full-text search. Lainnya memeriksa apakah field JSON berisi sebuah kunci. Lainnya menemukan item dekat titik di peta. Itu pola akses yang berbeda, jadi satu jenis indeks tidak akan cocok di semua tempat.

Itulah yang Anda pilih saat memilih indeks: bagaimana aplikasi mengakses data. Apakah Anda kebanyakan melakukan pencocokan exact, rentang, dan pengurutan? Apakah Anda mencari di dalam dokumen atau array? Apakah Anda menanyakan "apa yang dekat dengan lokasi ini" atau "apa yang overlap rentang ini"? Jawaban menentukan apakah B-tree, GIN, atau GiST yang tepat.

B-tree, GIN, dan GiST dengan bahasa sederhana

Memilih indeks lebih sedikit soal tipe kolom dan lebih banyak soal apa yang query Anda lakukan padanya. PostgreSQL memilih indeks berdasarkan operator seperti =, <, @>, atau @@, bukan pada apakah kolom itu “text” atau “json”. Itulah mengapa field yang sama bisa butuh indeks berbeda pada layar berbeda.

B-tree: cepat untuk lookup berurutan

B-tree adalah default dan pilihan yang paling umum. Ia bersinar ketika Anda memfilter dengan nilai exact, memfilter rentang, atau membutuhkan hasil dalam urutan tertentu.

Contoh khas adalah daftar admin yang difilter dengan status dan diurutkan berdasarkan created_at. Indeks B-tree pada (status, created_at) dapat membantu filter dan sort. B-tree juga alat umum untuk keunikan (unique constraints).

GIN: cepat ketika setiap baris mengandung banyak kunci yang bisa dicari

GIN dibangun untuk pertanyaan “apakah baris ini mengandung term/nilai ini?”, di mana satu baris bisa mencocokkan banyak kunci. Contoh umum adalah full-text search (dokumen berisi kata-kata) dan keanggotaan JSONB/array (JSON berisi kunci/nilai).

Bayangkan record customer dengan objek JSONB preferences, dan sebuah layar yang memfilter user di mana preferences berisi { "newsletter": true }. Itu tipe lookup gaya GIN.

GiST: fleksibel untuk rentang, geo, dan kemiripan

GiST adalah kerangka umum yang dipakai oleh tipe data yang tidak pas dalam pengurutan sederhana. Ia cocok untuk rentang (overlaps, contains), query geometrik dan geografis (near, within), dan beberapa pencarian kemiripan.

Saat memutuskan antara B-tree vs GIN vs GiST, mulailah dengan menuliskan operator yang dipakai layar tersibuk Anda. Indeks yang tepat biasanya menjadi jelas setelah itu.

Tabel keputusan untuk layar umum (filter, pencarian, JSON, geo)

Sebagian besar aplikasi hanya membutuhkan beberapa pola indeks. Trik-nya adalah mencocokkan perilaku layar ke operator yang digunakan query Anda.

Dua indeks bisa coexist ketika endpoint berbeda. Contohnya, daftar admin mungkin membutuhkan B-tree pada (tenant_id, created_at) untuk sort cepat, sementara halaman pencarian membutuhkan GIN untuk @@. Simpan keduanya hanya jika kedua pola query memang sering dipakai.

Jika ragu, lihat operator dulu. Indeks membantu saat database bisa menggunakannya untuk melewati sebagian besar tabel.

Filter dan sorting: di mana B-tree biasanya menang

Untuk kebanyakan layar sehari-hari, B-tree adalah pilihan membosankan yang bekerja. Jika query Anda seperti “ambil baris di mana kolom sama dengan nilai, mungkin urutkan, lalu tampilkan halaman 1,” B-tree biasanya hal pertama yang dicoba.

Filter equality adalah kasus klasik. Kolom seperti status, user_id, account_id, type, atau tenant_id sering muncul di dashboard dan panel admin. B-tree dapat langsung lompat ke nilai yang cocok.

Filter rentang juga cocok untuk B-tree. Saat Anda memfilter berdasarkan waktu atau rentang numerik, struktur berurut membantu: created_at >= ..., price BETWEEN ..., id > .... Jika UI Anda menawarkan “7 hari terakhir” atau “$50 hingga $100”, B-tree melakukan tepat apa yang Anda inginkan.

Sorting dan pagination adalah tempat B-tree dapat menghemat banyak pekerjaan. Jika urutan indeks cocok dengan ORDER BY Anda, PostgreSQL sering bisa mengembalikan baris sudah terurut alih-alih mengurutkan kumpulan besar di memori.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Indeks komposit mengikuti satu aturan sederhana: PostgreSQL hanya bisa menggunakan bagian terdepan dari indeks secara efisien. Pikirkan "kiri ke kanan." Dengan (user_id, status, created_at), query yang memfilter user_id (dan opsional status) diuntungkan. Query yang hanya memfilter status biasanya tidak.

Partial index adalah peningkatan kuat ketika layar Anda hanya peduli pada irisan data tertentu. Irisan umum adalah “hanya baris aktif,” “tidak soft-deleted,” atau “aktivitas terbaru.” Mereka menjaga indeks lebih kecil dan lebih cepat.

Kolom kardinalitas tinggi dan biaya indeks tambahan

Kolom kardinalitas tinggi memiliki banyak nilai unik, seperti user_id, order_id, email, atau created_at sampai tingkat detik. Indeks biasanya bersinar di sini karena filter bisa cepat mempersempit hasil menjadi irisan kecil tabel.

Kolom kardinalitas rendah adalah kebalikan: boolean dan enum kecil seperti is_active, status IN ('open','closed'), atau plan IN ('free','pro'). Indeks pada ini seringkali mengecewakan karena tiap nilai mencocokkan sebagian besar baris. PostgreSQL mungkin dengan benar memilih sequential scan karena melompat melalui indeks tetap berarti membaca banyak halaman tabel.

Biaya halus lain adalah pengambilan baris. Bahkan jika indeks menemukan ID yang cocok cepat, database mungkin masih harus mengunjungi tabel untuk kolom lain. Jika query Anda hanya butuh beberapa field, indeks pelengkap (covering) bisa membantu, tetapi itu juga membuat indeks lebih besar dan lebih mahal untuk dipelihara.

Setiap indeks tambahan punya harga tulis. Insert harus menulis ke setiap indeks. Update yang mengubah kolom terindeks harus memperbarui entri tersebut juga. Menambahkan indeks “untuk berjaga-jaga” dapat memperlambat seluruh aplikasi, bukan hanya satu layar.

Panduan praktis:

• Mulailah dengan 1–2 indeks utama per tabel sibuk, berdasarkan filter dan sort nyata.
• Utamakan kolom kardinalitas tinggi yang dipakai di WHERE dan ORDER BY.
• Berhati-hatilah mengindeks boolean dan enum kecil kecuali digabung dengan kolom selektif lain.
• Tambah indeks baru hanya setelah Anda bisa menyebutkan query tepat yang akan dipercepat.

Contoh: daftar tiket support yang difilter oleh assignee_id (kardinalitas tinggi) mendapat manfaat dari indeks, sementara is_archived = false saja seringkali tidak.

Layar pencarian: full-text, prefix, dan “mengandung”

Kotak pencarian terlihat sederhana, tapi pengguna mengharapkan banyak: beberapa kata, bentuk kata berbeda, dan peringkat yang wajar. Di PostgreSQL, itu biasanya full-text search: Anda menyimpan tsvector (teks yang sudah dipersiapkan) dan query dengan tsquery (apa yang diketik pengguna, di-parse menjadi term).

Untuk full-text search, GIN adalah default umum karena cepat menjawab “apakah dokumen ini mengandung term-term ini?” di banyak baris. Trade-off-nya adalah penulisan lebih berat: insert dan update baris cenderung lebih mahal.

GiST juga bisa bekerja untuk full-text search. Ia sering lebih kecil dan lebih murah untuk diupdate, tapi biasanya lebih lambat untuk pembacaan dibanding GIN. Jika data Anda sering berubah (misalnya tabel seperti event), keseimbangan baca-tulis itu bisa penting.

Prefix search bukan full-text

Prefix search berarti “dimulai dengan,” seperti mencari customer berdasarkan prefix email. Itu bukan tujuan full-text search. Untuk pola prefix, indeks B-tree dapat membantu (sering dengan operator class yang tepat) karena cocok dengan cara string diurutkan.

Untuk pencarian “mengandung” seperti ILIKE '%error%', B-tree biasanya tidak membantu. Di sinilah trigram indexing atau pendekatan pencarian lain menjadi relevan.

Ketika pengguna menginginkan filter plus pencarian teks

Kebanyakan layar nyata menggabungkan pencarian dengan filter: status, assignee, rentang tanggal, tenant, dan sebagainya. Setup praktis adalah:

• Indeks GIN (atau kadang GiST) untuk kolom tsvector.
• Indeks B-tree untuk filter paling selektif (misalnya account_id, status, created_at).
• Aturan ketat “jaga seminimal mungkin”, karena terlalu banyak indeks membuat penulisan lebih lambat.

Contoh: layar tiket support yang mencari “refund delayed” dan memfilter status = 'open' dan account_id tertentu. Full-text memberi Anda baris relevan, sementara B-tree membantu PostgreSQL mempersempit ke account dan status dengan cepat.

Field JSONB: memilih antara GIN dan indeks B-tree terarah

JSONB bagus untuk data fleksibel, tapi bisa memperlambat query jika Anda memperlakukannya seperti kolom biasa. Keputusan inti sederhana: apakah Anda mencari “di mana saja di JSON ini,” atau Anda memfilter pada beberapa path tertentu berulang kali?

Untuk query containment seperti metadata @> '{"plan":"pro"}', GIN biasanya pilihan pertama. Ia dibangun untuk “apakah dokumen ini mengandung bentuk ini?” dan juga mendukung pengecekan keberadaan kunci seperti ?, ?|, dan ?&.

Jika aplikasi Anda sebagian besar memfilter berdasarkan satu atau dua field JSON, indeks ekspresi B-tree yang ditargetkan seringkali lebih cepat dan lebih kecil. Ini juga membantu saat Anda perlu mengurutkan atau melakukan perbandingan numerik pada nilai yang diekstrak.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Aturan praktis:

• Gunakan GIN ketika pengguna mencari banyak kunci, tag, atau struktur nested.
• Gunakan B-tree expression ketika pengguna sering memfilter pada path tertentu.
• Indeks hanya apa yang muncul di layar nyata, bukan semuanya.
• Jika performa bergantung pada beberapa kunci JSON yang selalu dipakai, pertimbangkan mempromosikannya menjadi kolom nyata.

Contoh: layar support mungkin memfilter tiket berdasarkan metadata->>'priority' dan mengurutkan menurut created_at. Indeks path JSON priority dan kolom created_at normal. Lewatkan GIN luas kecuali pengguna juga mencari tag atau atribut nested.

Geo dan query range: di mana GiST paling cocok

Layar geo dan range adalah tempat GiST sering menjadi pilihan jelas. GiST dibangun untuk “apakah benda ini overlap, contain, atau dekat dengan benda itu?” daripada “apakah nilai ini sama dengan nilai itu?”

Data geo biasanya berarti titik (lokasi toko), garis (rute), atau poligon (zona pengiriman). Layar umum termasuk “toko dekat saya,” “lowongan dalam radius 10 km,” “tampilkan item di dalam bounding box peta ini,” atau “apakah alamat ini di dalam area layanan kami?” Indeks GiST (sering melalui tipe geometry/geography PostGIS) mempercepat operator spasial sehingga database bisa melewati sebagian besar baris alih-alih memeriksa tiap shape.

Range serupa. PostgreSQL punya tipe range seperti daterange dan int4range, dan pertanyaan tipikal adalah overlap: “apakah booking ini berbenturan dengan booking lain?” atau “tunjukkan subscription aktif selama minggu ini.” GiST mendukung operator overlap dan containment secara efisien, itulah mengapa ia umum pada kalender, penjadwalan, dan pemeriksaan ketersediaan.

B-tree masih bisa penting pada layar geo-like. Banyak halaman pertama memfilter berdasarkan tenant, status, atau waktu, lalu menerapkan kondisi spasial, lalu mengurutkan. Contoh: “hanya pengiriman perusahaan saya, dari 7 hari terakhir, terdekat terlebih dahulu.” GiST menangani bagian spasial, tapi B-tree membantu filter selektif dan sorting.

Cara memilih indeks langkah demi langkah

Pilihan indeks sebagian besar tentang operator, bukan nama kolom. Kolom yang sama bisa butuh indeks berbeda tergantung apakah Anda memakai =, >, LIKE 'prefix%', full-text search, containment JSON, atau jarak geo.

Bacalah query seperti checklist: WHERE memutuskan baris yang lolos, JOIN memutuskan bagaimana tabel terhubung, ORDER BY memutuskan urutan keluaran, dan LIMIT memutuskan berapa banyak baris yang benar-benar Anda butuhkan. Indeks terbaik seringkali yang membantu Anda menemukan 20 baris pertama dengan cepat.

Proses sederhana yang bekerja untuk kebanyakan layar aplikasi:

1. Tuliskan operator tepat yang dipakai layar (contoh: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Mulai dengan indeks yang cocok dengan filter paling selektif atau sort default. Jika layar disortir created_at DESC, mulai dari situ.
3. Tambah composite index hanya jika Anda melihat filter yang sama sering dipakai bersama. Taruh kolom equality di depan, lalu kolom range, lalu key sort.
4. Gunakan partial index ketika Anda selalu memfilter pada subset (contoh: hanya status = 'open'). Gunakan expression index ketika Anda query nilai komputasi (contoh: lower(email) untuk pencarian case-insensitive).
5. Validasi dengan EXPLAIN ANALYZE. Pertahankan jika memang mengurangi waktu eksekusi dan jumlah baris yang dibaca secara signifikan.

Contoh konkret: dashboard support mungkin memfilter tiket berdasarkan status dan mengurutkan menurut terbaru. B-tree pada (status, created_at DESC) adalah percobaan awal yang kuat. Jika layar yang sama juga memfilter berdasarkan flag JSONB seperti meta @> '{"vip": true}', itu operator berbeda dan biasanya membutuhkan indeks JSON terpisah.

Kesalahan umum yang membuang waktu (dan memperlambat write)

Cara umum untuk kecewa adalah memilih tipe indeks “yang benar” untuk operator yang salah. PostgreSQL hanya bisa menggunakan indeks saat query cocok dengan apa yang indeks dibangun untuk jawab. Jika aplikasi Anda memakai ILIKE '%term%', B-tree biasa pada kolom teks itu akan tidak terpakai, dan Anda masih akan melakukan table scan.

Perangkap lain adalah membuat indeks multi-kolom raksasa “untuk berjaga-jaga.” Mereka tampak aman, tapi mahal untuk dipelihara dan seringkali tidak cocok pola query nyata. Jika kolom paling kiri tidak dipakai di filter, sisa indeks mungkin tidak membantu.

Kolom selektivitas rendah juga mudah di-overindex. B-tree pada boolean seperti is_active atau status dengan sedikit nilai bisa hampir tidak berguna kecuali Anda menjadikannya partial index yang sesuai dengan potongan yang Anda query.

JSONB punya jebakan sendiri. GIN luas bisa sangat baik untuk filter fleksibel, tapi banyak pengecekan path JSON lebih cepat dengan expression index pada nilai yang diekstrak. Jika layar Anda selalu memfilter payload->>'customer_id', mengindeks ekspresi itu sering lebih kecil dan lebih cepat daripada mengindeks seluruh dokumen.

Terakhir, setiap indeks tambahan membebani write. Pada tabel yang sering diupdate (pikirkan tiket atau order), setiap insert dan update harus memperbarui semua indeks.

Sebelum menambahkan indeks, jeda dan periksa:

• Apakah indeks cocok dengan operator tepat yang dipakai query Anda?
• Bisa kah Anda mengganti indeks multi-kolom lebar dengan satu atau dua indeks fokus?
• Haruskah ini menjadi partial index untuk menghindari noise dari selektivitas rendah?
• Untuk JSONB, apakah expression index lebih cocok untuk layar ini?
• Apakah tabel cukup write-heavy sehingga biaya indeks melebihi keuntungan baca?

Pengecekan cepat sebelum Anda menambah (atau mempertahankan) indeks

Sebelum membuat indeks baru, jadilah spesifik tentang apa yang sebenarnya dilakukan aplikasi. Indeks "bagus untuk dimiliki" sering berubah menjadi penulisan lebih lambat dan penyimpanan lebih banyak dengan sedikit manfaat.

Mulailah dengan tiga layar teratas (atau endpoint API) dan tuliskan bentuk query tepatnya: filter, urutan, dan apa yang diketik pengguna. Banyak “masalah indeks” sebenarnya adalah “masalah query yang tidak jelas,” terutama ketika orang berdebat B-tree vs GIN vs GiST tanpa menyebut operator.

Checklist sederhana:

• Pilih 3 layar nyata dan daftar pola WHERE dan ORDER BY mereka secara tepat (termasuk arah dan penanganan NULL).
• Konfirmasi tipe operator: equality (=), range (>, BETWEEN), prefix, contains, overlap, atau distance.
• Pilih satu indeks per pola layar umum, uji, dan pertahankan hanya yang secara terukur mengurangi waktu atau jumlah bacaan.
• Jika tabel write-heavy, bersikap ketat: indeks ekstra menggandakan biaya write dan dapat menaikkan tekanan vacuum.
• Periksa ulang setelah perubahan fitur. Filter baru, urutan default baru, atau beralih dari “starts with” ke “contains” bisa membuat indeks lama tidak relevan.

Contoh: dashboard menambah default sort last_activity DESC. Jika Anda hanya mengindeks status, filter mungkin tetap cepat, tetapi sort sekarang memaksa kerja ekstra.

Contoh: memetakan layar aplikasi nyata ke indeks yang tepat

Tabel keputusan hanya membantu jika Anda dapat memetakkannya ke layar nyata yang dikirim. Berikut tiga layar umum dan bagaimana mereka cocok dengan pilihan indeks.

Cara praktis menerapkannya adalah mulai dari UI:

• Daftar setiap kontrol yang mempersempit hasil (filter).
• Catat urutan default.
• Jelaskan perilaku pencarian secara spesifik (full-text vs starts-with vs contains).
• Tandai field “spesial” (JSONB, geo, range).

Langkah berikutnya: jadikan indexing bagian dari proses build Anda

Indeks yang baik mengikuti layar Anda: filter yang orang klik, urutan yang mereka harapkan, dan kotak pencarian yang sebenarnya mereka gunakan. Perlakukan indexing sebagai kebiasaan selama pengembangan dan Anda akan menghindari sebagian besar kejutan performa nanti.

Buat bisa diulang: identifikasi 1–3 query yang dijalankan layar, tambahkan indeks terkecil yang cocok, uji dengan data realistis, lalu hapus yang tidak memberikan manfaat.

Jika Anda membangun tool internal atau portal pelanggan, rencanakan kebutuhan indeks lebih awal karena aplikasi ini sering tumbuh dengan menambah filter dan lebih banyak layar daftar. Jika Anda membangun dengan AppMaster (appmaster.io), ada baiknya memperlakukan konfigurasi filter dan sort setiap layar sebagai pola query konkret, lalu tambahkan hanya indeks yang cocok dengan klik nyata tersebut.