# Indexing PostgreSQL dalam Praktik: B-tree, GIN, dan Partial Index > Panduan praktis indexing PostgreSQL: kapan B-tree, GIN, BRIN, dan partial index menang, cara membaca EXPLAIN ANALYZE, dan angka nyata di balik speedup 170x. URL: https://www.matthewswong.com/id/blog/postgres-indexing-practical-guide/ - Author: Matthews Wong (https://www.matthewswong.com/id/about/) - Published: 2026-04-09 - Updated: 2026-04-09 - Category: Backend Indexing adalah pekerjaan performa dengan leverage tertinggi yang bisa Anda lakukan di database PostgreSQL, sekaligus tempat saya paling sering melihat cargo-culting: index di setiap kolom, tidak ada yang tahu apakah ada yang terpakai, dan jalur tulis yang merangkak menanggung beban pemeliharaannya. Setelah beberapa tahun menjalankan Postgres di belakang sistem ERP NestJS, polanya selalu sama. Query lambat itu tidak pernah eksotis. Penyebabnya composite index yang hilang, kesalahan kolom terdepan, atau index 1,1 GB yang seharusnya partial index 38 MB. Panduan ini adalah subset praktis dari dokumentasi index PostgreSQL yang benar-benar saya pakai: tipe index mana yang cocok untuk bentuk query mana, cara membaca output EXPLAIN ANALYZE tanpa gelar doktor, dan kemenangan yang terukur supaya Anda tahu seperti apa hasil yang bagus. Semua di sini diuji pada data berbentuk production, dan setiap klaim tentang perilaku index berasal langsung dari dokumentasi resmi yang ditautkan di akhir. ## Tipe Index yang Penting (dan Dua yang Jarang Penting) PostgreSQL menyediakan enam tipe index: B-tree, Hash, GiST, SP-GiST, GIN, dan BRIN. Untuk backend transaksional biasa Anda akan hidup di tiga di antaranya. Ini peta jujurnya: Tipe index Paling cocok untuk Hati-hati dengan B-tree (default) Query equality dan range, sorting, uniqueness. Menutupi 95 persen kebutuhan OLTP: lookup berdasarkan ID, status, tanggal, filter komposit. Urutan kolom di composite index adalah segalanya. Index pada customer dan tanggal tidak berguna untuk query yang hanya memfilter tanggal. GIN Containment JSONB, array, full-text search. Alasan banyak tim tidak pernah butuh Elasticsearch. Lambat di-update pada tabel yang write-heavy; ukuran index bisa melebihi tabelnya pada JSONB yang ramai. Pakai jsonb_path_ops kalau hanya butuh containment. GiST Data geometris, range, urutan nearest-neighbor, exclusion constraint seperti mencegah booking yang tumpang tindih. Lossy untuk beberapa operator, jadi Postgres mengecek ulang baris heap; umumnya lookup lebih lambat dari GIN untuk pencarian teks. BRIN Tabel append-only raksasa yang urutan fisiknya berkorelasi dengan sebuah kolom, seperti log time-series. Mungil: hitungan megabyte di mana B-tree butuh gigabyte. Tidak berguna kalau data Anda tidak berkorelasi fisik dengan kolom yang di-index. Update dan delete menurunkan kualitasnya. Hash Lookup equality murni, sedikit lebih kecil dan cepat dari B-tree pada kasus sempit. Tidak ada range query, sorting, maupun uniqueness. B-tree melakukan semua tugasnya hampir sama baik, jadi jarang sepadan dengan kebaruannya. ## Composite B-tree Index: Urutkan Kolom Sesuai Cara Anda Query Kesalahan indexing paling umum yang saya review adalah urutan kolom komposit. Aturan yang bertahan menghadapi production: kolom equality dulu, lalu kolom range atau sort. Postgres bisa menelusuri index dari kiri ke kanan; begitu sebuah query berhenti membatasi kolom terdepan, sisa index itu jadi beban mati untuk query tersebut. Perhatikan kasus gagal di snippet: query yang hanya memfilter status tidak bisa memakai index yang diawali customer_id. Itu bukan berarti Anda refleks menambah index kedua. Cek pg_stat_user_indexes dulu; setiap index yang Anda pertahankan membebani INSERT, UPDATE, dan waktu autovacuum. Di satu tabel sibuk kami mengukur write sekitar 12 persen lebih lambat akibat dua index redundan yang tidak disentuh query mana pun selama berbulan-bulan. ## Partial Index: Index 3 Persen yang Benar-Benar Anda Query Partial index adalah fitur paling kurang dimanfaatkan di dunia Postgres. Kalau query Anda selalu membawa predikat seperti status pending, Anda bisa membangun index hanya di atas baris-baris itu. Dokumentasinya eksplisit bahwa ini mengecilkan index sekaligus mempercepat update pada baris yang dikecualikan, karena mengubah baris done tidak lagi menyentuh index sama sekali. Di tabel background jobs kami subset yang hidup hanya 3 persen baris: partial index-nya 29x lebih kecil dan query poll worker turun dari 90 ms ke di bawah 2 ms. Partial unique index menyelesaikan aturan bisnis yang tidak bisa ditangani constraint level tabel: satu draft aktif per user, satu alamat default per pelanggan, satu shift terbuka per karyawan. Ia menegakkan uniqueness hanya di dalam predikat, dan itu persis semantik yang dibutuhkan aturan-aturan tersebut. Saya memakainya minimal sekali di tiap proyek ERP. ## GIN: JSONB dan Full-Text Search Tanpa Infrastruktur Tambahan GIN membalik relasinya: alih-alih satu entri per baris, ia menyimpan satu entri per elemen, menunjuk balik ke setiap baris yang mengandungnya. Itu membuat query containment pada JSONB dan pencarian tsvector cukup cepat sehingga untuk katalog di bawah beberapa juta baris, saya default ke full-text search Postgres sebelum meraih layanan pencarian terpisah. Satu contoh ERP nyata: pencarian produk pada 400 ribu SKU dengan GIN index pada kolom tsvector kembali dalam 8 sampai 15 ms, dan datanya tidak pernah meninggalkan database transaksional, jadi tidak ada pipeline sinkronisasi yang harus dijaga. ## Membaca EXPLAIN ANALYZE: Empat Angka yang Penting EXPLAIN menampilkan rencana yang dipilih optimizer; ANALYZE benar-benar menjalankan query dan menampilkan yang terjadi; BUFFERS menampilkan seberapa banyak data yang disentuh. Perbandingan sebelum-sesudah di bawah adalah bentuk nyata dari endpoint daftar invoice, dianonimkan tapi dengan magnitudo asli: Saat membaca plan, saya melihat empat hal berurutan, dan keempatnya mendiagnosis hampir semua query lambat: Rows Removed by Filter: barang bukti utama. Membaca 1,2 juta baris untuk mengembalikan 287 berarti database mengerjakan klausa WHERE Anda dengan cara keras. Itu permintaan index yang ditulis dalam bahasa plan. Estimasi rows vs actual rows: ketika selisihnya 10x atau lebih, statistik basi atau planner tidak bisa menembus sebuah ekspresi. Jalankan ANALYZE pada tabelnya, atau index ekspresinya langsung. Buffers shared read vs hit: read berarti disk, hit berarti cache. Query yang membaca puluhan ribu buffer dalam kondisi dingin akan cepat di pengujian Anda dan lambat Senin jam 9 pagi saat cache penuh data tenant lain. Loops pada node bersarang: inner index scan dengan loops 50000 dieksekusi 50000 kali. Node murah, total mahal. Di sinilah query N-plus-satu bersembunyi di dalam satu statement SQL. Index tidak gratis. Setiap index tambahan memperlambat setiap INSERT dan UPDATE pada tabel, memakan disk dan cache, serta menambah kerja autovacuum. Audit pemakaiannya dengan pg_stat_user_indexes dan buang apa pun yang nol scan setelah periode yang representatif. Index tercepat sering kali adalah yang Anda hapus. ## Alur Kerja Indexing Saya di Setiap Proyek Ini loop yang saya jalankan kira-kira bulanan di sistem production, dan selalu sebelum go-live: Nyalakan pg_stat_statements dan tarik 10 query teratas berdasarkan total waktu eksekusi. Optimalkan berdasarkan biaya agregat, bukan query mana yang membuat orang kesal hari ini. Untuk tiap kandidat, jalankan EXPLAIN dengan ANALYZE dan BUFFERS pada data berbentuk production. Database dev 10 ribu baris akan membohongi Anda; planner memilih plan berbeda pada skala berbeda. Desain index terkecil yang melayani query: kolom equality, lalu range atau sort. Pertimbangkan partial kalau query selalu membawa predikat tetap, dan klausa INCLUDE covering kalau heap fetch mendominasi. Buat dengan CREATE INDEX CONCURRENTLY supaya tidak mengunci write, lalu jalankan ulang EXPLAIN dan catat angka sebelum-sesudah di pesan commit migrasi. Anda di masa depan akan butuh bukti itu. Sebulan kemudian, cek pg_stat_user_indexes. Kalau index baru itu nol scan atau remeh, diagnosis Anda salah; hapus dan cari lagi daripada membiarkannya memajaki write selamanya. ## Index yang Membosankan, Kemenangan yang Terukur Indexing menghargai alur kerja yang terukur dan tidak glamor: temukan query terberat, baca plan-nya, bangun index tersempit yang melayaninya, dan verifikasi kemenangannya dengan angka. Query invoice 312 ms ke 1,8 ms di tulisan ini tidak pintar. Itu composite B-tree dengan kolom di urutan yang benar, ditemukan dengan membaca satu output EXPLAIN secara saksama. Intinya: kuasai composite B-tree dan partial index dulu, tambah GIN saat JSONB atau pencarian masuk ke gambar, ingat BRIN ada saat tabel log menyentuh 100 juta baris, dan audit index Anda seganas Anda membuatnya. PostgreSQL memberi Anda alat kelas dunia; keterampilan praktisnya adalah tahu 20 persen mana yang dipakai. ## Sumber dan bacaan lanjutan - Dokumentasi PostgreSQL — Index Types - Dokumentasi PostgreSQL — Partial Indexes - Dokumentasi PostgreSQL — Using EXPLAIN ## Source links - https://www.postgresql.org/docs/current/indexes-types.html - https://www.postgresql.org/docs/current/indexes-partial.html - https://www.postgresql.org/docs/current/using-explain.html ## FAQ ### Tipe index PostgreSQL mana yang harus dipelajari pertama untuk backend transaksional? Untuk sebagian besar beban kerja OLTP, B-tree, GIN, dan partial index mencakup mayoritas kasus. B-tree menangani query equality dan range serta memenuhi sekitar 95 persen kebutuhan OLTP, GIN adalah pilihan tepat untuk containment JSONB dan full-text search, dan partial index secara dramatis mengecilkan ukuran index ketika query Anda selalu menargetkan subset baris yang tetap. BRIN layak diketahui untuk tabel append-only besar seperti log time-series, tapi hanya bekerja ketika urutan fisik baris berkorelasi dengan kolom yang di-index. ### Mengapa urutan kolom begitu penting dalam composite B-tree index? PostgreSQL menelusuri composite index dari kiri ke kanan, sehingga query yang melewati kolom terdepan tidak bisa memakai index secara efisien. Aturan praktisnya: letakkan kolom filter equality di depan, lalu kolom range atau sort. Akibat nyata dari kesalahan ini ditunjukkan dalam tulisan ini: satu tabel sibuk mengalami write sekitar 12 persen lebih lambat akibat dua index redundan yang kolom terdepannya tidak cocok dengan filter query yang paling umum. ### Kapan sebaiknya memakai partial index daripada index kolom penuh? Gunakan partial index ketika query Anda secara konsisten membawa predikat tetap, misalnya hanya memfilter baris dengan status pending. Index hanya dibangun di atas baris yang cocok, sehingga jauh lebih kecil dan update pada baris yang dikecualikan sama sekali tidak menyentuhnya. Dalam satu contoh tabel background-jobs di tulisan ini, subset yang aktif hanya 3 persen dari total baris, partial index-nya 29 kali lebih kecil, dan query poll worker turun dari 90 ms ke di bawah 2 ms. ### Bagaimana cara mengetahui apakah index yang ada benar-benar dipakai? Kueri view pg_stat_user_indexes yang mencatat jumlah scan per index. Index yang menunjukkan nol atau hampir nol scan setelah periode production yang representatif adalah kandidat untuk dihapus. Tulisan ini juga merekomendasikan mengaktifkan pg_stat_statements untuk mengidentifikasi 10 query teratas berdasarkan total waktu eksekusi sebelum memutuskan index mana yang akan ditambah atau dihapus, karena mengoptimalkan berdasarkan biaya agregat lebih efektif daripada bereaksi terhadap keluhan individual. ### Apa empat angka kunci yang harus diperhatikan dalam output EXPLAIN ANALYZE? Tulisan ini mengidentifikasi: Rows Removed by Filter (angka tinggi menandakan index yang hilang), estimasi rows versus actual rows (selisih 10x atau lebih berarti statistik basi atau ekspresi yang belum di-index), buffers shared read versus hit (read berarti I/O disk yang akan lambat di bawah beban nyata), dan loops pada node bersarang (inner scan dengan 50.000 loops dieksekusi 50.000 kali terlepas dari seberapa murah setiap iterasinya). --- - HTML version: https://www.matthewswong.com/id/blog/postgres-indexing-practical-guide/ - English: https://www.matthewswong.com/en/blog/postgres-indexing-practical-guide/ - Site index for agents: https://www.matthewswong.com/llms.txt - Sitemap: https://www.matthewswong.com/sitemap.xml - API description (OpenAPI): https://www.matthewswong.com/openapi.json