Konsep Analytical Query Engine

Laporan bulanan TokoKita menanyakan hal yang sederhana: berapa rata-rata nilai pesanan per kota? Pertanyaan ini harus memindai ratusan ribu baris pesanan sekaligus, padahal database produksi kita biasanya sibuk melayani checkout pelanggan yang menulis baris demi baris. Menjalankan query analitik berat di database transaksional membuat laporan lambat sekaligus mengganggu transaksi. Analytical query engine hadir sebagai mesin terpisah yang dirancang khusus untuk pola baca agregasi besar. Section ini membuka pintu ke dunia mesin tersebut, dimulai dari konsep dasarnya.

OLTP dan OLAP: Dua Pola Beban Kerja #

Sistem database menghadapi dua keluarga beban kerja yang menuntut desain berlawanan. OLTP (Online Transaction Processing) melayani operasi transaksional: baca atau tulis sedikit baris, sangat sering, dengan latensi kecil dan jaminan ACID. OLAP (Online Analytical Processing) melayani analitik: baca jutaan baris sekaligus, agregasi lintas kolom, dan jarang menulis. Kunci perbedaannya ada pada rasio baris yang disentuh terhadap baris yang dikembalikan: checkout TokoKita menyentuh satu baris pesanan dan mengembalikan satu baris, sedangkan laporan penjualan menyentuh seluruh tabel dan mengembalikan lima baris ringkasan.

Course Database dan Penyimpanan Data membedah dunia OLTP sampai tuntas; section ini berfokus pada sisi analitik. Karena desainnya berbeda, praktik industri memisahkan keduanya: database OLTP menjalankan aplikasi, lalu data disalin secara berkala ke engine analitik untuk dikueri.

Demo Nyata: Query yang Sama di Dua Mesin #

Teori paling meyakinkan bila diukur. Skrip berikut menyiapkan 500 ribu pesanan TokoKita di dua tempat: database SQLite (row-store, wakil mesin OLTP) dan berkas Parquet yang dibaca DuckDB (columnar). Kedua mesin kemudian menjalankan agregasi yang identik:

"""Ch1: OLTP vs OLAP — agregasi yang sama di SQLite (row-store) vs DuckDB (column-store).

Dataset: 500 ribu pesanan TokoKita (id, pelanggan, kota, produk, qty, nilai).
Query analitik: rata-rata nilai pesanan per kota.
"""
import os
import sqlite3
import time

import duckdb

N = 500_000
DB = "toko_oltp.db"

# --- 1. Bangun database OLTP SQLite (row-store) ---
if os.path.exists(DB):
    os.remove(DB)
con = sqlite3.connect(DB)
con.execute("PRAGMA journal_mode=WAL")
con.execute(
    "CREATE TABLE pesanan (id INTEGER PRIMARY KEY, pelanggan TEXT, "
    "kota TEXT, produk TEXT, qty INTEGER, nilai REAL)"
)
kota = ["Jakarta", "Bandung", "Surabaya", "Medan", "Denpasar"]
produk = ["Laptop", "Smartphone", "Headset", "Keyboard", "Mouse"]
data = [
    (i,
     f"pelanggan-{i % 1000:04d}",
     kota[i % 5],
     produk[i % 5],
     1 + i % 3,
     15000.0 + (i % 500) * 250.0)
    for i in range(1, N + 1)
]
con.executemany("INSERT INTO pesanan VALUES (?,?,?,?,?,?)", data)
con.commit()
print(f"baris pesanan: {con.execute('SELECT count(*) FROM pesanan').fetchone()[0]}")

# --- 2. Query analitik di SQLite (row-store) ---
sql = "SELECT kota, count(*), round(avg(nilai), 2) FROM pesanan GROUP BY kota"

# run pemanasan untuk KEDUA engine (data di page cache + plan tersiapkan),
# waktu diambil dari run kedua agar perbandingannya setara
con.execute(sql).fetchall()
t0 = time.perf_counter()
hasil_sqlite = con.execute(sql).fetchall()
durasi_sqlite = time.perf_counter() - t0
print(f"SQLite  (row-store)  : {durasi_sqlite:.3f} detik")
con.close()

# --- 3. Query identik di DuckDB (columnar, dari Parquet) ---
import pyarrow as pa  # noqa: E402
import pyarrow.parquet as pq  # noqa: E402

if os.path.exists("pesanan.parquet"):
    os.remove("pesanan.parquet")
kolom = list(zip(*data))
pq.write_table(
    pa.table({
        "id": pa.array(kolom[0], pa.int64()),
        "pelanggan": pa.array(kolom[1], pa.string()),
        "kota": pa.array(kolom[2], pa.string()),
        "produk": pa.array(kolom[3], pa.string()),
        "qty": pa.array(kolom[4], pa.int32()),
        "nilai": pa.array(kolom[5], pa.float64()),
    }),
    "pesanan.parquet",
)
del data, kolom

dcon = duckdb.connect()
dsql = ("SELECT kota, count(*), round(avg(nilai), 2) "
        "FROM read_parquet('pesanan.parquet') GROUP BY kota")
# satu run pemanasan agar pembacaan berkas tidak dihitung sebagai run dingin
dcon.execute(dsql).fetchall()

t0 = time.perf_counter()
hasil_duck = dcon.execute(dsql).fetchall()
durasi_duck = time.perf_counter() - t0
print(f"DuckDB  (columnar)   : {durasi_duck:.3f} detik")

for baris in sorted(hasil_duck):
    print(baris)
print(f"hasil identik        : {sorted(hasil_sqlite) == sorted(hasil_duck)}")
print(f"ukuran SQLite db     : {os.path.getsize(DB):,} byte")
print(f"ukuran Parquet       : {os.path.getsize('pesanan.parquet'):,} byte")

Output yang Dihasilkan:

baris pesanan: 500000
SQLite  (row-store)  : 0.173 detik
DuckDB  (columnar)   : 0.008 detik
('Bandung', 100000, 77125.0)
('Denpasar', 100000, 77875.0)
('Jakarta', 100000, 76875.0)
('Medan', 100000, 77625.0)
('Surabaya', 100000, 77375.0)
hasil identik        : True
ukuran SQLite db     : 22,515,712 byte
ukuran Parquet       : 2,478,832 byte

Mari kita telaah baris-baris kode di atas:

1. Baris 15-36 (pembangunan SQLite): 500 ribu baris dimasukkan ke tabel berorientasi baris dengan executemany; PRAGMA journal_mode=WAL memakai mode journal yang lazim untuk beban tulis OLTP.
2. Baris 39-49 (pengukuran SQLite): query agregasi dijalankan dua kali; waktu diambil dari run kedua setelah pemanasan agar data sudah berada di page cache.
3. Baris 51-70 (penulisan Parquet): dataset yang sama ditulis sebagai berkas kolom dengan pyarrow — langkah ini meniru proses ekstraksi berkala dari OLTP ke penyimpanan analitik.
4. Baris 74-80 (pengukuran DuckDB): DuckDB membaca berkas Parquet langsung lewat read_parquet tanpa proses import; pemanasan satu run diterapkan agar perlakuannya setara dengan SQLite.

Hasilnya tegas: DuckDB menyelesaikan agregasi yang sama sekitar 21 kali lebih cepat, dan berkas Parquet hanya sepersembilan ukuran database SQLite. Angka pasti bergeser antar mesin dan antar run — pada 500 ribu baris kedua mesin masih sama-sama cepat dalam arti praktis — tetapi polanya konsisten dan semakin lebar seiring data tumbuh. Dua sumber selisihnya: SQLite harus memindai halaman demi halaman yang memuat seluruh baris (enam kolom) padahal query hanya memakai dua kolom, sementara DuckDB membaca data kolom kota dan nilai saja dari berkas Parquet.

Anatomi Analytical Query Engine #

Dari demo di atas, kita bisa menyebut komponen inti sebuah analytical query engine:

• Parser dan optimizer: menerjemahkan SQL deklaratif menjadi rencana eksekusi fisik yang efisien; kita bahas tuntas di chapter Query Optimization.
• Penyimpanan columnar: data disimpan per kolom sehingga query yang butuh dua kolom tidak membaca sepuluh kolom lainnya.
• Eksekusi vectorized: operator memproses data per batch (vektor), bukan baris per baris, seperti dibedah di chapter Vectorized Query Execution.
• Akses berkas langsung: engine modern membaca Parquet, CSV, bahkan database lain tanpa tahap import; DuckDB melakukannya di satu proses, Trino dan ClickHouse di banyak mesin.
• Pemisahan penyimpanan dan komputasi: data tinggal sebagai berkas di object storage atau data lake, engine datang sebagai penghitung yang bisa dinyalakan sesuai kebutuhan.

Dokumentasi SQLite dan DuckDB memuat detail masing-masing desain. Fondasi paling penting dari semuanya adalah cara penyimpanan per kolom — itulah pembahasan chapter berikutnya, Columnar Storage.