Indexing PostgreSQL dalam Praktik: B-tree, GIN, dan Partial Index

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:

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.

-- The composite index that fixed our invoice list (most selective first
-- is a myth: order by HOW YOU QUERY, equality columns before ranges)
CREATE INDEX idx_invoices_customer_status_created
  ON invoices (customer_id, status, created_at DESC);

-- Served by the index above (equality, equality, range):
SELECT * FROM invoices
WHERE customer_id = 42 AND status = 'unpaid'
ORDER BY created_at DESC LIMIT 20;

-- NOT served well: leading column missing → full scan territory
SELECT * FROM invoices WHERE status = 'unpaid';

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.

-- 97% of our rows were status = 'done'. Queries only ever asked
-- for the live 3%. A partial index keeps only what matters:
CREATE INDEX idx_jobs_pending
  ON background_jobs (scheduled_at)
  WHERE status IN ('pending', 'retrying');

-- 1.1 GB full index → 38 MB partial index.
-- Bonus: partial UNIQUE index for "one active draft per user"
CREATE UNIQUE INDEX uniq_active_draft
  ON purchase_orders (created_by)
  WHERE status = 'draft';

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.

-- JSONB containment: which orders have a line item for SKU-123?
CREATE INDEX idx_orders_payload ON orders USING GIN (payload jsonb_path_ops);
SELECT id FROM orders WHERE payload @> '{"sku": "SKU-123"}';

-- Full-text search without Elasticsearch:
CREATE INDEX idx_products_fts ON products
  USING GIN (to_tsvector('indonesian', name || ' ' || description));

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:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE customer_id = 42 AND status = 'unpaid';

-- BEFORE (no usable index):
Seq Scan on invoices (cost=0.00..48291.00 rows=312 width=184)
  (actual time=0.41..312.55 rows=287 loops=1)
  Filter: ((customer_id = 42) AND (status = 'unpaid'))
  Rows Removed by Filter: 1199713        ← read 1.2M rows for 287
  Buffers: shared read=33291             ← 260 MB off disk

-- AFTER (composite index):
Index Scan using idx_invoices_customer_status_created
  (cost=0.43..954.12 rows=312 width=184)
  (actual time=0.09..1.8 rows=287 loops=1)
  Buffers: shared hit=119                ← ~1 MB, all from cache
-- 312 ms → 1.8 ms. Same query, same data.

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.

Alur Kerja Indexing Saya di Setiap Proyek

Ini loop yang saya jalankan kira-kira bulanan di sistem production, dan selalu sebelum go-live:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.