Belajar System Design - Database Deep Dive: Indexing & Optimization

#Pendahuluan

Setelah di episode 4 kita memahami SQL vs NoSQL, replikasi, dan sharding, pada episode ini kita masuk ke level detail database: indexing dan query optimization. Database tanpa indexing yang tepat seperti buku tanpa indeks — semua halaman harus dibaca satu per satu. Di skala jutaan baris, perbedaan antara query dengan index dan tanpa index bisa mencapai 1000x perbedaan latency.

Episode ini penting karena bahkan database yang sudah di-shard dan di-replikasi tetap lambat jika query-nya tidak dioptimasi. Indexing adalah optimization paling berdampak yang bisa kalian lakukan — seringkali tanpa mengubah kode aplikasi sama sekali.

#Index: Mengapa dan Kapan

#B-Tree Index

B-Tree adalah struktur data balanced tree yang digunakan oleh hampir semua relational database modern (PostgreSQL, MySQL InnoDB).

         [50]
        /    \
    [25]      [75]
   /   \     /   \
 [10] [30] [60] [90]

Cara kerja: query WHERE id = 30 memulai dari root (50), turun ke subtree kiri (25), lalu ke leaf (30). Dibanding scan seluruh table, B-Tree hanya perlu log(n) langkah.

Kapan index membantu:

• WHERE clause pada kolom yang sering di-query.
• ORDER BY pada kolom tertentu.
• JOIN pada kolom foreign key.
• GROUP BY pada kolom tertentu.

Kapan index menambah write cost:

• Setiap INSERT, UPDATE, DELETE harus update index juga.
• Banyak index pada satu table = write latency meningkat.
• Rule of thumb: jangan buat index pada kolom yang jarang di-query.

#LSM-Tree (Log-Structured Merge-Tree)

LSM-Tree digunakan oleh database NoSQL seperti Cassandra, LevelDB, dan RocksDB (yang digunakan oleh PostgreSQL sebagai extension).

Cara kerja:

1. Write masuk ke memtable (in-memory sorted structure).
2. Saat memtable penuh, flush ke SSTable (sorted string table) di disk.
3. Periodik, compaction menggabungkan beberapa SSTable menjadi satu yang lebih besar.

Kelebihan: write throughput sangat tinggi (sequential write). Kekurangan: read bisa lebih lambat (harus cek beberapa SSTable), space amplification.

#Hash Index

Index berbasis hash: hash(key) → bucket → pointer ke record.

Kelebihan: lookup O(1) — sangat cepat untuk equality check. Kekurangan: tidak bisa range query (BETWEEN, >), tidak bisa ORDER BY.

#Covering Index

Index yang mencakup semua kolom yang dibutuhkan query — database tidak perlu mengakses table row.

-- Query
SELECT name, email FROM users WHERE status = 'active';
 
-- Covering index
CREATE INDEX idx_users_status_name_email
ON users (status) INCLUDE (name, email);

Query ini tidak perlu mengakses table utama — semua data ada di index. Dampaknya: 10-100x lebih cepat.

#Query Optimization dengan EXPLAIN ANALYZE

EXPLAIN ANALYZE adalah tool paling powerful untuk memahami bagaimana database mengeksekusi query.

EXPLAIN ANALYZE
SELECT p.name, o.total
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE p.category = 'electronics';

Output akan menunjukkan:

• Seq Scan vs Index Scan — apakah database scan seluruh table atau pakai index.
• Join strategy — nested loop, hash join, atau merge join.
• Actual time — waktu eksekusi aktual.
• Rows — estimasi vs aktual jumlah baris.

#Join Strategy

#Connection Pooling

Setiap koneksi ke database memakan memory dan CPU. Membuat koneksi baru setiap request sangat mahal.

Tanpa pooling: Request → buka koneksi → query → tutup koneksi (expensive!)
Dengan pooling: Request → ambil dari pool → query → kembalikan ke pool (murah!)

Tool: pgBouncer (PostgreSQL), ProxySQL (MySQL), HikariCP (Java), pg (Node.js).

Recommendation:

• Pool size = 2-4x jumlah CPU cores database server.
• Gunakan transaction pooling mode untuk pgBouncer.

#Praktik: Optimasi Query Lambat

#Skenario: Query Lambat di PostgreSQL

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1234
AND status = 'pending'
ORDER BY created_at DESC;

Output tanpa index:

Seq Scan on orders  (cost=0.00..185000.00 rows=100 width=64)
  Filter: ((user_id = 1234) AND (status = 'pending'))
  Rows Removed by Filter: 9999900
Planning Time: 0.1 ms
Execution Time: 1250.00 ms

Scan seluruh 10 juta baris! Solution: tambahkan composite index.

#Solusi: Tambah Composite Index

-- Tambah composite index
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
 
-- Jalankan ulang
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1234
AND status = 'pending'
ORDER BY created_at DESC;

Output dengan index:

Index Scan using idx_orders_user_status_created on orders
  Index Cond: ((user_id = 1234) AND (status = 'pending'))
Planning Time: 0.1 ms
Execution Time: 0.5 ms

Perbedaan: 1250ms → 0.5ms (2500x lebih cepat).

#Index Maintenance

• Monitor unused index: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
• Hapus index yang tidak dipakai — mengurangi write overhead.
• Reindex periodik: REINDEX INDEX CONCURRENTLY idx_name;

#Penutup

Inti yang harus dibawa pulang:

• B-Tree untuk equality dan range query; hash index untuk equality cepat; LSM-Tree untuk write-heavy.
• Covering index menghilangkan kebutuhan akses table — 10-100x lebih cepat.
• EXPLAIN ANALYZE adalah tool utama untuk memahami query execution plan.
• Connection pooling (pgBouncer/ProxySQL) wajib untuk production — jangan buka tutup koneksi setiap request.
• Composite index harus sesuai urutan kolom di query WHERE clause.

Di episode 6 selanjutnya kita akan membahas message queue & event streaming — task queue (Bull/Beanstalkd), message broker (RabbitMQ), event streaming (Apache Kafka), serta kapan menggunakan async processing untuk mengurangi latency dan decouple services!