Indeks PostgreSQL untuk aplikasi SaaS: pilih antara btree, GIN, dan GiST menggunakan bentuk kueri nyata seperti filter, pencarian, JSONB, dan array.

Sebuah indeks mengubah cara PostgreSQL menemukan baris. Tanpa indeks, database sering kali harus membaca banyak bagian tabel (sequential scan) lalu membuang sebagian besar hasil itu. Dengan indeks yang tepat, database bisa langsung melompat ke baris yang cocok (index lookup), lalu mengambil hanya apa yang dibutuhkan.
Anda akan merasakannya lebih awal di SaaS karena layar sehari-hari penuh kueri. Satu klik bisa memicu beberapa pembacaan: halaman daftar, total count, beberapa kartu dashboard, dan kotak pencarian. Saat tabel tumbuh dari ribuan ke jutaan baris, kueri yang dulu terasa instan mulai melambat.
Contoh umum adalah halaman Orders yang difilter berdasarkan status dan tanggal, diurutkan dengan yang terbaru terlebih dahulu, dengan pagination. Jika PostgreSQL harus memindai seluruh tabel orders untuk menemukan order berbayar dalam 30 hari terakhir, setiap pemuatan halaman melakukan pekerjaan ekstra. Indeks yang baik mengubah itu menjadi lompatan cepat ke potongan data yang tepat.
Indeks tidak gratis. Setiap indeks mempercepat pembacaan untuk kueri tertentu, tetapi juga memperlambat penulisan (INSERT/UPDATE/DELETE harus memperbarui indeks) dan menggunakan lebih banyak penyimpanan (plus menambah tekanan cache). Trade-off inilah alasan Anda harus memulai dari pola kueri nyata, bukan dari tipe indeks.
Aturan sederhana yang mencegah kerja sia-sia: tambahkan indeks hanya ketika Anda bisa menunjuk ke kueri spesifik dan sering yang akan dipercepat olehnya. Jika Anda membangun layar dengan pembuat berbasis chat seperti Koder.ai, membantu untuk menangkap SQL di balik halaman daftar dan dashboard Anda dan menggunakan itu sebagai daftar keinginan indeks.
Kebingungan soal indeks sebagian besar hilang ketika Anda berhenti berpikir dalam fitur (JSON, pencarian, array) dan mulai berpikir dalam bentuk kueri: apa yang dilakukan klausa WHERE, dan bagaimana Anda mengharapkan hasil diurutkan?
Gunakan B-tree ketika kueri Anda mirip perbandingan normal dan Anda peduli tentang urutan. Ini adalah andalan untuk equality, range, dan join.
Contoh bentuk: memfilter dengan tenant_id = ?, status = 'active', created_at >= ?, join users.id = orders.user_id, atau menampilkan “terbaru terlebih dahulu” dengan ORDER BY created_at DESC.
GIN (Generalized Inverted Index) cocok ketika satu kolom mengandung banyak anggota dan Anda bertanya, “apakah ia mengandung X?” Itu umum pada kunci JSONB, elemen array, dan vektor full-text.
Contoh bentuk: metadata @> {'plan':'pro'} pada JSONB, tags @> ARRAY['urgent'], atau to_tsvector(body) @@ plainto_tsquery('reset password').
GiST (Generalized Search Tree) cocok untuk pertanyaan tentang jarak atau overlap, di mana nilai berperilaku seperti rentang atau bentuk. Sering dipakai untuk tipe range, data geometris, dan beberapa pencarian “cocok terdekat”.
Contoh bentuk: rentang waktu yang overlap dengan kolom range, beberapa pencarian bergaya similarity (misalnya dengan operator trigram), atau kueri spasial (jika Anda menggunakan PostGIS).
Cara praktis memilih:
Indeks mempercepat pembacaan, tetapi mereka menambah biaya penulisan dan disk. Di SaaS, trade-off itu paling terasa pada tabel panas seperti events, sessions, dan activity logs.
Kebanyakan layar daftar SaaS berbagi bentuk yang sama: batas tenant, beberapa filter, dan urutan yang bisa diprediksi. Indeks B-tree adalah pilihan default di sini, dan biasanya paling murah untuk dipelihara.
Pola umum adalah WHERE tenant_id = ? plus filter seperti status = ?, user_id = ?, dan rentang waktu seperti created_at >= ?. Untuk composite B-tree index, letakkan filter equality lebih dulu (kolom yang dicocokkan dengan =), lalu tambahkan kolom yang Anda urutkan.
Aturan yang bekerja baik di sebagian besar aplikasi:
tenant_id jika setiap kueri berskala per-tenant.= berikutnya (sering status, user_id).ORDER BY terakhir (sering created_at atau id).INCLUDE untuk menutup kebutuhan halaman daftar tanpa membuat key lebih lebar.Contoh realistis: halaman Tickets yang menampilkan item terbaru terlebih dahulu, difilter berdasarkan status.
-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);
Indeks itu mendukung baik filter maupun sort, sehingga Postgres bisa menghindari pengurutan pada hasil besar. Bagian INCLUDE (title) membantu halaman daftar menyentuh lebih sedikit halaman tabel, sementara menjaga key indeks fokus pada filtering dan ordering.
Untuk rentang waktu, ide yang sama berlaku:
SELECT id, created_at
FROM events
WHERE tenant_id = $1
AND created_at >= $2
AND created_at < $3
ORDER BY created_at DESC
LIMIT 100;
CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);
Pagination adalah tempat banyak aplikasi SaaS melambat. Offset pagination (OFFSET 50000) memaksa database melewati banyak baris. Seek pagination tetap cepat dengan menggunakan sort key terakhir yang terlihat:
SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;
Dengan B-tree yang tepat, ini tetap cepat bahkan saat tabel tumbuh.
Sebagian besar aplikasi SaaS multi-tenant: setiap kueri harus tetap berada dalam satu tenant. Jika indeks Anda tidak menyertakan tenant_id, Postgres masih bisa menemukan baris dengan cepat, tetapi seringkali harus memindai jauh lebih banyak entry indeks daripada yang diperlukan. Indeks-aware tenant menjaga data tiap tenant terkumpul dalam indeks sehingga layar umum tetap cepat dan dapat diprediksi.
Aturan sederhana: letakkan tenant_id di awal indeks ketika kueri selalu memfilter berdasarkan tenant. Lalu tambahkan kolom yang paling sering Anda filter atau urutkan.
Indeks membosankan tapi berdampak tinggi sering terlihat seperti:
(tenant_id, created_at) untuk daftar item terbaru dan cursor pagination(tenant_id, status) untuk filter status (Open, Paid, Failed)(tenant_id, user_id) untuk layar “items owned by this user”(tenant_id, updated_at) untuk tampilan admin “baru saja diubah”(tenant_id, external_id) untuk lookup dari webhook atau importOver-indexing terjadi ketika Anda menambahkan indeks baru untuk setiap variasi layar yang sedikit berbeda. Sebelum membuat indeks lain, periksa apakah composite index yang ada sudah men-cover leftmost columns yang Anda butuhkan. Misalnya, jika Anda punya (tenant_id, created_at), biasanya Anda tidak juga perlu (tenant_id, created_at, id) kecuali Anda benar-benar memfilter pada id setelah kolom-kolom itu.
Partial index bisa memangkas ukuran dan biaya penulisan ketika sebagian besar baris tidak relevan. Mereka bekerja baik dengan soft deletes dan data “hanya aktif”, misalnya: indeks hanya dimana deleted_at IS NULL, atau hanya dimana status = 'active'.
Setiap indeks tambahan membuat penulisan lebih berat. Inserts harus memperbarui setiap indeks, dan updates bisa menyentuh banyak indeks meskipun Anda mengubah satu kolom. Jika aplikasi Anda menerima banyak events (termasuk aplikasi yang dibangun cepat dengan Koder.ai), jaga indeks tetap fokus pada beberapa bentuk kueri yang digunakan pengguna setiap hari.
JSONB berguna saat aplikasi Anda butuh field ekstra fleksibel seperti feature flags, atribut pengguna, atau pengaturan per-tenant. Masalahnya adalah operator JSONB yang berbeda berperilaku berbeda, jadi indeks terbaik tergantung pada bagaimana Anda melakukan query.
Dua bentuk yang paling penting:
@>.-> / ->> (sering dibandingkan dengan =).Jika Anda sering memfilter dengan @>, GIN pada kolom JSONB biasanya memberikan keuntungan.
-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata @> '{"region":"eu","plan":"pro"}';
-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);
Jika struktur JSON Anda dapat diprediksi dan Anda kebanyakan memakai @> pada kunci top-level, jsonb_path_ops bisa lebih kecil dan lebih cepat, tetapi mendukung lebih sedikit tipe operator.
Jika UI Anda berulang kali memfilter pada satu field (seperti plan), mengekstrak field itu dan mengindeksnya seringkali lebih cepat dan lebih murah daripada GIN yang lebar.
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata->>'plan' = 'pro';
CREATE INDEX accounts_plan_expr
ON accounts ((metadata->>'plan'));
Aturan praktis: gunakan JSONB untuk atribut yang fleksibel dan jarang difilter, tetapi promosikan field yang stabil dan sering dipakai (plan, status, created_at) menjadi kolom nyata. Jika Anda cepat beriterasi pada aplikasi yang di-generate, sering kali ini tweak skema yang mudah setelah Anda melihat filter mana yang muncul di setiap halaman.
Contoh: jika Anda menyimpan {"tags":["beta","finance"],"region":"us"} di JSONB, gunakan GIN ketika Anda memfilter berdasarkan bundel atribut (@>), dan tambahkan indeks ekspresi untuk beberapa kunci yang mendorong sebagian besar tampilan daftar (plan, region).
Array tampak menggoda karena mudah disimpan dan mudah dibaca. Kolom users.roles text[] atau projects.labels text[] bisa bekerja baik ketika Anda biasanya menanyakan satu hal: apakah baris ini mengandung sebuah nilai (atau set nilai)? Itu adalah titik di mana GIN membantu.
GIN adalah pilihan utama untuk query membership pada array. Ia memecah array menjadi item individual dan membangun lookup cepat ke baris yang mengandungnya.
Bentuk query array yang sering mendapat keuntungan:
@> (array contains)&& (array berbagi item)= ANY(...), tetapi @> sering lebih dapat diprediksiContoh tipikal untuk memfilter pengguna berdasarkan role:
-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];
CREATE INDEX users_roles_gin ON users USING GIN (roles);
Dan memfilter proyek berdasarkan set label (harus mengandung kedua label):
SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];
CREATE INDEX projects_labels_gin ON projects USING GIN (labels);
Yang mengejutkan orang: beberapa pola tidak menggunakan indeks seperti yang Anda harapkan. Jika Anda mengubah array menjadi string (array_to_string(labels, ',')) lalu menjalankan LIKE, indeks GIN tidak akan membantu. Juga, jika Anda butuh “starts with” atau pencocokan fuzzy di dalam label, Anda berurusan dengan text search, bukan membership array.
Array juga bisa sulit dipelihara ketika mereka berubah menjadi mini-database: update sering, butuh metadata per item (siapa menambahkan label, kapan, kenapa), atau butuh analitik per label. Pada titik itu, tabel join seperti project_labels(project_id, label) biasanya lebih mudah divalidasi, di-query, dan dikembangkan.
Untuk kotak pencarian, dua pola muncul berulang: full-text search (mencari record tentang topik) dan fuzzy matching (menangani typo, nama parsial, dan pola ILIKE). Pilihan indeks yang tepat adalah perbedaan antara “instan” dan “timeout pada 10k pengguna”.
Gunakan full-text search ketika pengguna mengetik kata nyata dan Anda ingin hasil diberi peringkat berdasarkan relevansi, seperti mencari tiket berdasarkan subjek dan deskripsi. Setup biasa adalah menyimpan tsvector (sering di kolom generated) dan mengindeksnya dengan GIN. Anda mencari dengan @@ dan tsquery.
-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);
-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');
-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);
SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';
Apa yang disimpan di vector: hanya field yang benar-benar Anda cari. Jika Anda memasukkan semuanya (notes, internal logs), Anda membayar dalam ukuran indeks dan biaya penulisan.
Gunakan trigram similarity ketika pengguna mencari nama, email, atau frasa pendek dan Anda butuh partial matches atau toleransi typo. Trigram membantu dengan ILIKE '%term%' dan operator similarity. GIN biasanya lebih cepat untuk lookup “apakah ini cocok?”; GiST bisa lebih cocok ketika Anda juga peduli tentang pengurutan berdasarkan similarity.
Aturan praktis:
tsvector untuk pencarian teks berbasis relevansi.Pitfall yang perlu diperhatikan:
ILIKE '%abc') memaksa scan.Jika Anda mengirimkan layar pencarian dengan cepat, perlakukan indeks sebagai bagian dari fitur: UX pencarian dan pilihan indeks perlu dirancang bersama.
Mulai dengan kueri persis yang dijalankan aplikasi Anda, bukan tebakan. “Layar lambat” biasanya adalah satu pernyataan SQL dengan WHERE dan ORDER BY tertentu. Salin dari log, debug ORM, atau alat tangkap kueri yang Anda gunakan.
Alur kerja yang bertahan di aplikasi nyata:
EXPLAIN (ANALYZE, BUFFERS) pada kueri yang sama.=, >=, LIKE, @>, @@), bukan hanya nama kolom.EXPLAIN (ANALYZE, BUFFERS) dengan volume data realistis.Berikut contoh konkret. Halaman Customers memfilter berdasarkan tenant dan status, mengurutkan berdasarkan terbaru, dan melakukan pagination:
SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;
Jika EXPLAIN menunjukkan sequential scan dan sort, indeks B-tree yang cocok dengan filter dan sort sering memperbaikinya:
CREATE INDEX ON customers (tenant_id, status, created_at DESC);
Jika bagian lambatnya adalah filtering JSONB seperti metadata @> '{"plan":"pro"}', itu menunjuk ke GIN. Jika itu full-text search seperti to_tsvector(...) @@ plainto_tsquery(...), itu juga menunjuk ke indeks pencarian berbasis GIN. Jika itu “closest match” atau operator overlap-style, GiST sering menjadi padanan.
Setelah menambahkan indeks, ukur trade-off-nya. Periksa ukuran indeks, waktu insert dan update, dan apakah indeks membantu beberapa kueri lambat teratas atau hanya satu kasus pinggiran. Dalam proyek yang bergerak cepat (termasuk yang dibangun di Koder.ai), pemeriksaan ulang ini membantu menghindari menumpuk indeks yang tidak terpakai.
Sebagian besar masalah indeks bukan tentang memilih B-tree vs GIN vs GiST. Mereka tentang membuat indeks yang terlihat benar, tetapi tidak cocok dengan cara aplikasi meng-query tabel.
Kesalahan yang paling menyakitkan:
tenant_id dan created_at, tetapi indeks dimulai dengan created_at, planner bisa melewatinya.status, is_active, atau boolean seringkali sedikit membantu karena mencocokkan terlalu banyak baris. Padukan dengan kolom selektif (seperti tenant_id atau created_at) atau lewati.ANALYZE tidak dijalankan baru-baru ini, planner bisa memilih rencana yang buruk meskipun indeks yang tepat ada.Contoh konkret: layar Invoices memfilter berdasarkan tenant_id dan status, lalu mengurutkan dengan created_at DESC. Indeks hanya pada status hampir tidak membantu. Pilihan yang lebih cocok adalah indeks komposit yang dimulai dengan tenant_id, lalu status, lalu created_at (filter dulu, sort terakhir). Perubahan tunggal itu sering mengungguli menambahkan tiga indeks terpisah.
Perlakukan setiap indeks sebagai biaya. Ia harus membuktikan manfaatnya pada kueri nyata, bukan hanya di teori.
Perubahan indeks mudah dikirim dan menyebalkan untuk dibalik jika menambah biaya penulisan atau mengunci tabel sibuk. Sebelum Anda merge, perlakukan seperti rilis kecil.
Mulai dengan memutuskan apa yang Anda optimalkan. Tarik dua peringkat singkat dari log atau monitoring Anda: kueri yang paling sering dijalankan, dan kueri dengan latency tertinggi. Untuk masing-masing, catat bentuk persisnya: kolom filter, urutan sort, join, dan operator yang digunakan (equals, range, IN, ILIKE, operator JSONB, array contains). Ini mencegah tebakan dan membantu memilih tipe indeks yang benar.
Checklist pra-kirim:
Setelah menambah indeks, verifikasi apakah itu membantu di rencana nyata. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada kueri persis yang sama dan bandingkan sebelum vs sesudah. Lalu amati perilaku produksi selama sehari:
Jika Anda membangun dengan Koder.ai, ada baiknya menyimpan SQL yang digenerasi untuk satu atau dua layar lambat di dekat perubahan, sehingga indeks cocok dengan apa yang aplikasi jalankan.
Bayangkan layar admin umum: daftar Users dengan scoping tenant, beberapa filter, urut berdasarkan last active, dan kotak pencarian. Di sini indeks berhenti jadi teori dan mulai menghemat waktu nyata.
Tiga bentuk kueri yang biasanya Anda lihat:
-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;
-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;
-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
AND metadata @> '{"plan":"pro"}'::jsonb;
Set indeks kecil namun sengaja untuk layar ini:
(tenant_id, status, last_active_at DESC).tsvector yang dihasilkan dengan indeks GIN.GIN (metadata) ketika Anda sering menggunakan @>, atau ekspresi B-tree seperti ((metadata->>'plan')) ketika Anda paling sering memfilter satu kunci.Kebutuhan campuran itu normal. Jika satu halaman melakukan filter + search + JSON, hindari memadatkan semuanya ke satu mega-index. Jaga B-tree untuk sorting/pagination, lalu tambahkan satu indeks khusus (sering GIN) untuk bagian mahal.
Langkah selanjutnya: pilih satu layar lambat, tuliskan 2-3 bentuk kueri teratasnya, dan tinjau setiap indeks berdasarkan tujuan (filter, sort, search, JSON). Jika sebuah indeks tidak jelas cocok dengan kueri nyata, keluarkan dari rencana. Jika Anda iterasi cepat di koder.ai, melakukan tinjauan ini saat menambahkan layar baru bisa mencegah sprawl indeks sementara skema masih berubah.
An index membuat PostgreSQL dapat menemukan baris yang cocok tanpa membaca sebagian besar tabel. Untuk layar SaaS umum seperti daftar, dashboard, dan pencarian, indeks yang tepat dapat mengubah sequential scan yang lambat menjadi lookup cepat yang lebih skalabel saat tabel tumbuh.
Mulailah dengan B-tree untuk sebagian besar kueri aplikasi karena paling cocok untuk filter =, filter rentang, join, dan ORDER BY. Jika kueri Anda pada dasarnya soal keterkandungan (JSONB, array) atau pencarian teks, maka GIN biasanya pilihan berikutnya; GiST lebih untuk kasus overlap dan kueri “terdekat/terkait”.
Letakkan kolom yang Anda filter dengan = di depan, lalu taruh kolom yang Anda urutkan di akhir. Urutan ini sesuai cara planner berjalan di indeks sehingga bisa memfilter dan mengembalikan baris dalam urutan yang benar tanpa langkah sortir tambahan.
Jika setiap kueri dibatasi oleh tenant_id, menaruh tenant_id di awal menjaga baris tiap tenant berkumpul di dalam indeks. Itu biasanya mengurangi jumlah entry indeks dan halaman tabel yang harus disentuh untuk layar daftar sehari-hari.
INCLUDE memungkinkan Anda menambahkan kolom ekstra untuk mendukung index-only reads pada halaman daftar tanpa membuat key indeks lebih lebar. Berguna ketika Anda memfilter dan mengurutkan oleh beberapa kolom tapi juga menampilkan beberapa field tambahan di layar.
Gunakan partial index ketika Anda hanya peduli pada subset baris, misalnya “tidak terhapus” atau “hanya aktif”. Itu membuat indeks lebih kecil dan lebih murah untuk dipelihara, penting untuk tabel yang sering mendapat insert dan update.
Gunakan GIN pada kolom JSONB ketika Anda sering melakukan query containment seperti metadata @> '{"plan":"pro"}'. Jika Anda paling sering memfilter pada satu atau dua kunci JSON tertentu, indeks ekspresi B-tree pada (metadata->>'plan') seringkali lebih kecil dan lebih cepat.
GIN cocok ketika pertanyaan utama Anda adalah “apakah array ini mengandung X?” memakai operator seperti @> atau &&. Jika Anda butuh metadata per item, update sering, atau analitik per label/role, tabel relasi (join table) biasanya lebih mudah dipelihara dan diindeks dengan baik.
Untuk full-text search, simpan tsvector (sering sebagai generated column) dan indeks dengan GIN, lalu query dengan @@ untuk pencarian berbasis relevansi. Untuk fuzzy matching seperti ILIKE '%name%' dan toleransi typo, indeks trigram (sering GIN) biasanya alat yang tepat.
Salin SQL persis yang dijalankan aplikasi Anda dan jalankan EXPLAIN (ANALYZE, BUFFERS) untuk melihat bagian mana yang memakan waktu dan apakah Anda sedang melakukan scan, sort, atau filter mahal. Tambahkan indeks terkecil yang sesuai dengan operator dan urutan sortir kueri, lalu jalankan kembali EXPLAIN yang sama untuk memastikan indeks digunakan dan memperbaiki rencana.