Database Indexing Cheat Sheet

Referensi cepat database indexing. B-tree, Hash, GIN, GiST, BRIN, partial index, composite index, EXPLAIN ANALYZE. Perfect buat developer yang mau optimalkan query PostgreSQL.

SQL12 min read2.242 kata

Silakan login atau daftar untuk membaca cheat sheet ini.

Cheat sheet ini membahas segala hal tentang database indexing di PostgreSQL, mulai dari tipe index, kapan pakai, sampai cara debugging dengan EXPLAIN ANALYZE. Index yang tepat bisa membuat query kamu cepat puluhan kali lipat.

#Dasar Index

#Apa Itu Index

Index adalah struktur data terpisah dari tabel yang mempercepat pencarian baris. Tanpa index, database harus melakukan sequential scan (membaca seluruh tabel). Dengan index, database bisa langsung loncat ke baris yang relevan.

#Anatomi Index PostgreSQL

Setiap index di PostgreSQL punya:

Index structure: B-tree, Hash, GIN, GiST, BRIN, atau SP-GiST
Index entries: Pasangan key dan TID (Tuple ID, pointer ke baris fisik)
Visibility map: Melacak halaman tabel yang sudah visible

#Membuat dan Menghapus Index

sql

-- Index pada satu kolom
CREATE INDEX idx_users_email ON users(email);
 
-- Index unik (mencegah duplikat)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
 
-- Index dengan nama custom
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
 
-- Index dengan expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
 
-- Index dengan operator class
CREATE INDEX idx_users_email_ci ON users(LOWER(email) varchar_pattern_ops);
 
-- Hapus index
DROP INDEX idx_users_email;
 
-- Hapus index tanpa blocking (CONCURRENTLY)
DROP INDEX CONCURRENTLY idx_users_email;

#CONCURRENTLY

Secara default, CREATE INDEX mengunci tabel untuk write. Pakai CONCURRENTLY untuk membuat index tanpa blocking write, tapi lebih lambat dan tidak bisa dijalankan dalam transaction block.

sql

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

#Tipe Index di PostgreSQL

#B-tree (Default)

B-tree adalah tipe index default di PostgreSQL. Cocok untuk query dengan operator =, <, >, BETWEEN, IN, IS NULL, dan LIKE dengan prefix.

sql

-- B-tree adalah default, tidak perlu specify
CREATE INDEX idx_users_age ON users(age);
 
-- Explicit B-tree
CREATE INDEX idx_users_age ON users USING btree(age);

Kapan pakai B-tree:

Equality dan range queries
Sorting (ORDER BY)
LIKE 'prefix%' (hanya prefix)
Kolom dengan cardinality menengah sampai tinggi

#Hash Index

Hash index hanya mendukung equality (=) comparison. Sebelum PostgreSQL 10, hash index tidak crash-safe. Sekarang sudah aman dipakai.

sql

CREATE INDEX idx_sessions_token ON sessions USING hash(token);

Kapan pakai Hash:

Hanya equality search
Key sangat panjang (hash lebih efisien dari B-tree)
Tidak butuh range query atau sorting

#GIN (Generalized Inverted Index)

GIN dirancang untuk data yang berisi multiple komponen, seperti array dan full-text search. Satu baris bisa punya banyak index entries.

sql

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING gin(to_tsvector('english', title || ' ' || body));
 
-- Array contains
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
 
-- JSONB
CREATE INDEX idx_events_data ON events USING gin(data jsonb_path_ops);

Kapan pakai GIN:

Full-text search (tsvector)
Array containment (@>, &&)
JSONB queries (@>, ?, ?|)
trigram fuzzy search

#GiST (Generalized Search Tree)

GiST adalah infrastruktur untuk index tipe data kompleks seperti geometri, range, dan full-text search dengan ranking.

sql

-- Geospatial (PostGIS)
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
 
-- Range type
CREATE INDEX idx_events_period ON events USING gist(during);
 
-- Exclusion constraint
CREATE TABLE reservations (
  id serial PRIMARY KEY,
  room_id int,
  during tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Kapan pakai GiST:

Spatial/geometric queries (<->, &&, <<)
Range overlap (&&)
Exclusion constraints
pg_trgm untuk fuzzy search dengan LIKE dan ILIKE

#BRIN (Block Range Index)

BRIN menyimpan ringkasan (min, max) untuk setiap block range tabel. Sangat kecil (kilobyte vs megabyte untuk B-tree) dan cepat dibuat.

sql

CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

Kapan pakai BRIN:

Tabel besar dengan data terurut secara fisik (append-only seperti log)
Storage sangat terbatas
Tidak butuh presisi tinggi (BRIN bisa return false positive yang di filter setelahnya)

#SP-GiST (Space-Partitioned GiST)

SP-GiST untuk data yang secara natural berbentuk tree atau partisi non-seimbang, seperti kd-tree, trie, atau quadtree.

sql

CREATE INDEX idx_words ON dictionary USING spgist(word);

#Perbandingan Tipe Index

Tipe	Operator	Ukuran	Cocok Untuk
B-tree	=, <, >, BETWEEN, LIKE 'prefix%'	Sedang	General purpose, range, sort
Hash	=	Kecil	Equality saja, key panjang
GIN	@>, ?, tsvector	Besar	Array, JSONB, FTS
GiST	&&, <->, <<	Sedang	Geometri, range, trigram
BRIN	=, <, >	Sangat kecil	Log append-only, tabel besar
SP-GiST	Tergantung	Sedang	Tree-like, trie

#Composite Index

#Apa Itu Composite Index

Composite index adalah index pada multiple kolom. Urutan kolom sangat penting karena menentukan kapan index bisa dipakai.

sql

-- Index pada dua kolom
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
 
-- Index ini optimal untuk:
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 123;
-- Tapi TIDAK optimal untuk:
SELECT * FROM orders WHERE status = 'paid';

#Aturan Urutan Kolom

Kolom equality dulu: Kolom dengan = condition di paling depan
Range di belakang: Kolom dengan <, >, BETWEEN di akhir
Sesuai pola query: Sesuaikan dengan ORDER BY dan WHERE yang sering dipakai

#Multi-Column Index Limit

PostgreSQL mendukung sampai 32 kolom dalam satu index, tapi lebih dari 3-4 kolom jarang efisien. Pertimbangkan index terpisah.

#Partial Index

#Apa Itu Partial Index

Partial index hanya mengindex subset baris yang memenuhi kondisi WHERE. Lebih kecil dan cepat untuk query spesifik.

sql

-- Index hanya untuk order yang belum lunas
CREATE INDEX idx_orders_unpaid ON orders(user_id) WHERE status = 'pending';
 
-- Index hanya untuk user aktif
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;
 
-- Index untuk high-value orders
CREATE INDEX idx_orders_high_value ON orders(amount) WHERE amount > 1000000;

Partial index akan dipakai otomatis jika query kamu menyertakan kondisi yang sama atau lebih spesifik.

#Covering Index

#Apa Itu Covering Index

Covering index menyertakan kolom tambahan dengan INCLUDE clause sehingga query bisa dijawab hanya dari index tanpa mengakses tabel (index-only scan).

sql

-- Index covering untuk query yang butuh user_id dan amount
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (amount, status);
 
-- Query ini bisa pakai index-only scan
SELECT amount, status FROM orders WHERE user_id = 123;

#Perbedaan Composite vs Covering

Aspek	Composite (`(a, b, c)`)	Covering (`(a) INCLUDE (b, c)`)
Bisa filter?	Ya, semua kolom	Hanya kolom key (a)
Bisa sort?	Ya, sesuai urutan	Tidak, kolom INCLUDE tidak sort
Bisa SELECT?	Ya	Ya
Ukuran	Lebih besar	Sedikit lebih besar dari key-only

#Index-Only Scan

#Apa Itu Index-Only Scan

Index-only scan terjadi saat query bisa dijawab sepenuhnya dari index tanpa membaca tabel. Ini sangat cepat karena menghindari disk I/O ke tabel.

Syarat index-only scan:

Semua kolom di SELECT dan WHERE ada di index
Visibility map menunjukkan halaman tabel sudah visible (sudah di VACUUM)

sql

-- Pastikan visibility map up to date
VACUUM users;

#EXPLAIN ANALYZE

#Apa Itu EXPLAIN ANALYZE

EXPLAIN ANALYZE menjalankan query dan menampilkan rencana eksekusi beserta waktu aktual. Ini tool paling penting untuk debugging performance.

sql

-- Query plan tanpa eksekusi
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- Query plan dengan eksekusi dan statistik waktu
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
 
-- Dengan buffer I/O detail
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';
 
-- Dengan format JSON
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE email = 'test@example.com';
 
-- Dengan format text dan detail verbose
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';

#Membaca Query Plan

Plan dibaca dari dalam ke luar (indentasi terdalam dieksekusi duluan).

sql

EXPLAIN ANALYZE SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

Output yang umum:

plaintext

HashAggregate  (cost=12.50..15.00 rows=100 width=40) (actual time=2.1..2.5 rows=85 loops=1)
  Hash Join  (cost=8.0..12.0 rows=200 width=40) (actual time=1.0..1.8 rows=200 loops=1)
    Hash Cond: (o.user_id = u.id)
    ->  Seq Scan on orders o  (cost=0..5.0 rows=200 width=8) (actual time=0.01..0.3 rows=200 loops=1)
    ->  Hash  (cost=6.0..6.0 rows=100 width=36) (actual time=0.5..0.5 rows=100 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 12kB
          ->  Seq Scan on users u  (cost=0..6.0 rows=100 width=36) (actual time=0.01..0.3 rows=100 loops=1)
                Filter: (status = 'active')

#Node Type yang Umum

Node Type	Arti
`Seq Scan`	Sequential scan, baca seluruh tabel
`Index Scan`	Baca index lalu akses tabel
`Index Only Scan`	Hanya baca index, tanpa akses tabel
`Bitmap Index Scan`	Bitmap dari index, lalu akses tabel
`Bitmap Heap Scan`	Akses tabel berdasarkan bitmap
`Hash Join`	Hash table untuk join
`Nested Loop`	Loop nested untuk join
`Merge Join`	Merge untuk join (butuh data terurut)
`Sort`	Pengurutan data
`HashAggregate`	Agregasi dengan hash
`Gather`	Parallel worker

#Indikasi Masalah

Seq Scan pada tabel besar: Mungkin butuh index
Filter dengan cost tinggi: Mungkin index salah atau hilang
Nested Loop dengan banyak rows: Mungkin butuh index pada join key
Sort dengan cost tinggi: Mungkin bisa pakai index untuk hindari sort

#Index yang Tidak Terpakai

#Mendeteksi Index Tak Terpakai

sql

-- Index yang tidak pernah dipakai
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Index duplikat atau redundant
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
       (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
       (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
  SELECT indexrelid::regclass as idx, indrelid::regclass as table_name,
         (indrelid::text || ' ' || indkey::text || ' ' ||
          coalesce(indpred::text, '')) as key
  FROM pg_index
) sub
GROUP BY table_name, key
HAVING count(*) > 1;

#Kapan TIDAK Perlu Index

Tabel kecil (kurang dari ratusan baris): Seq scan lebih cepat
Kolom yang jarang di query di WHERE
Kolom dengan cardinality sangat rendah (boolean, gender)
Tabel yang sering di insert/update/delete: Index memperlambat write
Query yang selalu mengambil mayoritas baris

#Maintenance Index

#REINDEX

Index bisa bloat (membengkak) seiring waktu karena update dan delete. REINDEX membangun ulang index.

sql

-- Reindex satu index
REINDEX INDEX idx_users_email;
 
-- Reindex seluruh tabel
REINDEX TABLE users;
 
-- Reindex seluruh database
REINDEX DATABASE mydb;
 
-- Reindex tanpa blocking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

#VACUUM dan Visibility Map

sql

-- Vacuum biasa
VACUUM users;
 
-- Vacuum dengan analisis statistik
VACUUM ANALYZE users;
 
-- Vacuum penuh (rebuild tabel, mengunci tabel)
VACUUM FULL users;
 
-- Autovacuum setting
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.05);

#Monitoring Index Bloat

sql

SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan as index_scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

#Index untuk Tipe Data Spesifik

#Text dan Pattern Matching

sql

-- LIKE dengan prefix: pakai text_pattern_ops
CREATE INDEX idx_users_name ON users(name varchar_pattern_ops);
-- Sekarang LIKE 'John%' bisa pakai index
 
-- ILIKE case-insensitive: pakai LOWER
CREATE INDEX idx_users_name_ci ON users(LOWER(name) varchar_pattern_ops);
-- Sekarang LOWER(name) LIKE 'john%' bisa pakai index
 
-- Trigram untuk fuzzy search
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
SELECT * FROM products WHERE name % 'iphone';

#JSONB

sql

-- GIN index untuk JSONB
CREATE INDEX idx_events_data ON events USING gin(data);
 
-- jsonb_path_ops lebih kecil tapi hanya dukung @>
CREATE INDEX idx_events_data_path ON events USING gin(data jsonb_path_ops);
 
-- B-tree index untuk key spesifik
CREATE INDEX idx_events_type ON events((data->>'type'));

#Array

sql

-- GIN index untuk array containment
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
 
SELECT * FROM posts WHERE 'javascript' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['javascript', 'python'];
SELECT * FROM posts WHERE tags @> ARRAY['javascript', 'react'];

#UUID

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-- B-tree index untuk UUID
CREATE INDEX idx_sessions_uuid ON sessions USING btree(session_id);
 
-- Untuk UUID v7 (sortable) atau UUID v4 (random)
-- UUID v7 lebih baik untuk index locality karena sequential

#Index dan Join

Untuk join yang cepat, pastikan kolom join punya index.

sql

-- Index pada foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);
 
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

#Glossary

Index: Struktur data yang mempercepat pencarian baris di tabel.
B-tree: Index tree balanced yang mendukung equality, range, dan sorting.
Hash Index: Index berbasis hash table, hanya untuk equality.
GIN (Generalized Inverted Index): Index untuk data multi-komponen seperti array dan JSONB.
GiST (Generalized Search Tree): Infrastruktur index untuk tipe data kompleks seperti geometri dan range.
BRIN (Block Range Index): Index ringan yang menyimpan ringkasan per block range.
SP-GiST: Index untuk data berbentuk tree atau partisi non-seimbang.
Composite Index: Index pada multiple kolom.
Partial Index: Index yang hanya mengindex subset baris dengan kondisi WHERE.
Covering Index: Index dengan kolom INCLUDE untuk index-only scan.
Index-Only Scan: Eksekusi query hanya dari index tanpa akses tabel.
Sequential Scan: Membaca seluruh tabel baris per baris, tanpa index.
Bitmap Index Scan: Kompromi antara index scan dan seq scan, pakai bitmap.
EXPLAIN ANALYZE: Command yang menjalankan query dan menampilkan plan dengan waktu aktual.
Visibility Map: Struktur yang melacak halaman tabel yang sudah visible untuk index-only scan.
Bloat: Ruang kosong di index akibat update dan delete.
VACUUM: Proses membersihkan dead tuples dan update visibility map.
Cardinality: Jumlah nilai unik dalam kolom.
TID (Tuple ID): Pointer ke lokasi fisik baris di tabel.

Baca Cheat Sheet Lengkap

Database PostgreSQL Performance