Indexing dan Partitioning

Kasir TokoKita mencari satu pelanggan dari satu juta baris pesanan; tim analitik meminta laporan tujuh hari terakhir dari tiga juta baris di data lake. Dua beban itu terlihat mirip — sama-sama query dengan filter — tetapi obatnya berbeda: index memangkas data berdasarkan nilai lewat struktur B-tree, sedangkan partition memangkas berdasarkan lokasi fisik data di folder. Chapter Indexing telah membedah cara kerja B-tree dan Chapter Partitioning Data telah memperkenalkan hive-style partitioning. Bab ini mengadu keduanya pada benchmark di mesin yang sama, lalu menutup dengan keputusan yang paling sering ditanyakan: kapan index, kapan partition, dan kapan keduanya.

Dua Teknik, Satu Tujuan #

Keduanya menjawab pertanyaan yang sama dengan cara yang berbeda: bagaimana agar data yang tidak relevan tidak pernah dibaca.

Konsekuensi fisiknya menentukan pemakaian: index menambah biaya di setiap tulis (struktur B-tree ikut diperbarui), sedangkan partition menambah biaya di setiap tulis dan baca ketika partisi terlalu halus sampai menjadi ribuan berkas kecil.

Eksperimen A: Index di SQLite #

Tabel pesanan berisi satu juta baris: 1.000 pelanggan, lima kota, 30 hari, nilai bervariasi. Dua query diuji — lookup satu pelanggan (0,1% data) dan rentang tiga hari (10% data) — masing-masing diukur sebagai median tujuh run setelah satu pemanasan, dan rencananya dibaca lewat EXPLAIN QUERY PLAN:

con_sql.execute("""CREATE TABLE pesanan (
    id INTEGER PRIMARY KEY, pelanggan_id INT, kota TEXT, tanggal TEXT, nilai REAL)""")
con_sql.execute("""
INSERT INTO pesanan (pelanggan_id, kota, tanggal, nilai)
WITH RECURSIVE seq(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 1000000)
SELECT 1 + i % 1000,
       CASE i % 5 WHEN 0 THEN 'Bandung' WHEN 1 THEN 'Jakarta' WHEN 2 THEN 'Surabaya'
                  WHEN 3 THEN 'Medan' ELSE 'Denpasar' END,
       date('2026-12-01', (i % 30) || ' days'),
       15000.0 + (i % 500) * 250.0
FROM seq
""")

Q_TITIK = ("SELECT count(*), round(sum(nilai), 0) FROM pesanan "
           "WHERE pelanggan_id = 42")            # 1.000 baris (0,1% data)
Q_RENTANG_3HARI = ("SELECT count(*), round(sum(nilai), 0) FROM pesanan "
                   "WHERE tanggal BETWEEN '2026-12-01' AND '2026-12-03'")   # 10% data

print("=== A. SQLite 1 juta baris ===")
print("query titik, TANPA index :")
print(f"  {ukur_sql(Q_TITIK):.2f} ms | plan: {plan_sql(Q_TITIK)}")
con_sql.execute("CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id)")
print("query titik, DENGAN index pelanggan_id:")
print(f"  {ukur_sql(Q_TITIK):.2f} ms | plan: {plan_sql(Q_TITIK)}")

print("query rentang 3 hari (10% data), index tetap di pelanggan_id:")
print(f"  {ukur_sql(Q_RENTANG_3HARI):.2f} ms | plan: {plan_sql(Q_RENTANG_3HARI)}")
con_sql.execute("CREATE INDEX idx_tanggal ON pesanan(tanggal)")
print("query rentang 3 hari, DENGAN index tanggal:")
print(f"  {ukur_sql(Q_RENTANG_3HARI):.2f} ms | plan: {plan_sql(Q_RENTANG_3HARI)}")

Output yang Dihasilkan:

=== A. SQLite 1 juta baris ===
query titik, TANPA index :
  37.72 ms | plan: ['SCAN pesanan']
query titik, DENGAN index pelanggan_id:
  0.77 ms | plan: ['SEARCH pesanan USING INDEX idx_pelanggan (pelanggan_id=?)']
query rentang 3 hari (10% data), index tetap di pelanggan_id:
  61.54 ms | plan: ['SCAN pesanan']
query rentang 3 hari, DENGAN index tanggal:
  36.66 ms | plan: ['SEARCH pesanan USING INDEX idx_tanggal (tanggal>? AND tanggal<?)']

Mari kita telaah bagian-bagian penting kode di atas:

1. Baris 21-30 (pengisian satu juta baris): recursive CTE seq menghasilkan angka 1 sampai sejuta; i % 1000 dan i % 30 membuat sebaran pelanggan dan tanggal yang rata.
2. Baris 33-40 (ukur_sql): satu run pemanasan dibuang, lalu median tujuh run diambil — pola disiplin pengukuran dari chapter sebelumnya.
3. Baris 47-48 versus 49-50: dua query dengan selektivitas berbeda — Q_TITIK menyentuh 0,1% data, Q_RENTANG_3HARI menyentuh 10%.
4. Baris 55 dan 61 (CREATE INDEX): index dibuat di tengah eksperimen sehingga sebelum-sesudahnya terukur pada tabel yang sama persis.

Hasilnya kontras tajam. Lookup titik melompat dari 37,72 milidetik ke 0,77 milidetik — 49 kali lebih cepat — dan rencananya berubah dari SCAN menjadi SEARCH USING INDEX. Query rentang tiga hari hanya membaik dari 61,54 ke 36,66 milidetik (1,7 kali), karena 100 ribu baris yang diambil lewat index berarti 100 ribu lompatan acak; pembacaan berurutan jauh lebih ramah cache disk. Pelajaran praktisnya: index bersinar saat selektivitas tinggi, dan mulai kehilangan daya saat query menyentuh porsi data yang besar.

Eksperimen B: Partition di Parquet #

Skenario analitiknya: tiga juta pesanan ditulis ke Parquet dengan dua tata letak — 30 folder hive-style tanggal=2026-12-01 sampai tanggal=2026-12-30, dan satu berkas tunggal 46,6 MB berisi 20 row group. Karena tanggal berganti setiap baris, statistik min-maks kolom tanggal di setiap row group membentang penuh — tidak ada row group yang bisa dilewati lewat statistik:

con.execute("COPY pesanan_parquet TO 'partisi_tanggal' (FORMAT parquet, PARTITION_BY (tanggal))")
con.execute("COPY pesanan_parquet TO 'satu_berkas.parquet' (FORMAT parquet, ROW_GROUP_SIZE 150000)")

FILTER = "WHERE tanggal BETWEEN DATE '2026-12-24' AND DATE '2026-12-30'"
Q_PARTISI = (f"SELECT count(*), round(sum(nilai), 0) FROM read_parquet("
             f"'partisi_tanggal/**/*.parquet', hive_partitioning=true) {FILTER}")
Q_SATU = (f"SELECT count(*), round(sum(nilai), 0) "
          f"FROM read_parquet('satu_berkas.parquet') {FILTER}")

Output yang Dihasilkan:

=== B. Parquet 3 juta baris: 30 partisi tanggal= vs 1 berkas ===
folder partisi : 30 (contoh: tanggal=2026-12-01 .. tanggal=2026-12-30)
satu_berkas.parquet : 46.6 MB, 20 row group
statistik kolom tanggal di 3 row group pertama satu_berkas.parquet:
  row group 0: 2026-12-01 .. 2026-12-30  (rentang penuh -> tidak ada yang bisa dilewati)
  row group 1: 2026-12-01 .. 2026-12-30  (rentang penuh -> tidak ada yang bisa dilewati)
  row group 2: 2026-12-01 .. 2026-12-30  (rentang penuh -> tidak ada yang bisa dilewati)
query 7 hari terakhir, direktori terpartisi:
  8.7 ms | hasil: (700000, Decimal('54425000000'))
query yang sama, satu berkas tanpa partisi:
  26.1 ms | hasil: (700000, Decimal('54425000000'))
rencana terpartisi:
  File Filters: (tanggal BETWEEN '2026-12-24'::DATE AND '2026-12-30'::DATE)
  Scanning Files: 7/30
rencana satu berkas:
  Filters: tanggal>='2026-12-24'::DATE AND tanggal<='2026-12-30'::DATE
rasio waktu satu berkas / terpartisi: 3.0x

Bukti pemangkasan terbaca langsung di rencana eksekusi — dua baris File Filters dan Scanning Files itu adalah kutipan dari kartu READ_PARQUET keluaran EXPLAIN, dengan baris yang terpotong lebar sudah digabung. Scanning Files: 7/30 berarti 23 folder tidak dibuka sama sekali — nilai tanggalnya sudah dikenali dari nama folder, sebelum satu byte data pun dibaca (mekanisme yang sama dengan bukti Partitioning Data dan Spark Partitioning). Pada satu berkas, filter yang sama tetap dieksekusi, tetapi setelah seluruh 46,6 MB dibuka; hasilnya 26,1 milidetik versus 8,7 milidetik. Rasio 3x ini akan melebar seiring jumlah partisi, karena biaya yang dihemat proporsional dengan folder yang dilewati.

Kapan Index, Kapan Partition #

Angka milidetik di bab ini spesifik untuk mesin uji dan akan berbeda di tempat lain; yang konsisten adalah polanya — 49x untuk lookup berselektivitas tinggi, 3x untuk scan yang dipangkas tiga perempat foldernya. Cara kerja query planner SQLite dibahas resmi di Query Planner SQLite , dan hive partitioning di DuckDB di dokumentasi DuckDB .

Index dan partition memutus berkas mana yang dibuka sebelum eksekusi dimulai. Pertanyaan berikutnya lebih halus: kapan filter dieksekusi terhadap data yang dibuka — di sumbernya, atau justru setelah seluruh data dipindahkan ke memori proses kita? Chapter Predicate Pushdown melanjutkan.