Desain Query Peta Raksasa: Index dan Pagination untuk Data 15 TB

Saat dataset tumbuh seperti peta Minecraft raksasa hingga belasan terabyte, masalah utamanya bukan lagi sekadar menyimpan data, tetapi membaca potongan yang tepat dengan cepat. Pada backend nyata, pola ini muncul pada tabel koordinat, log pergerakan, event area, atau histori aktivitas yang terus bertambah: filter berdasarkan area, urut berdasarkan waktu, lalu diambil per halaman.

Desain Query Peta Raksasa yang baik biasanya ditentukan oleh tiga hal: bentuk query yang konsisten, index yang sesuai dengan pola WHERE + ORDER BY, dan strategi pagination yang tidak memaksa database membaca terlalu banyak baris. Jika query sudah benar tetapi tetap lambat, langkah berikutnya adalah memeriksa rencana eksekusi, cardinality filter, biaya sort, dan apakah data perlu dipartisi atau diarsipkan.

Masalah Nyata di Balik Analogi Peta 15 TB

Bayangkan peta Minecraft yang terus direkam: setiap blok, pergerakan, kunjungan chunk, atau perubahan area disimpan sebagai event. Dalam sistem backend, analoginya bisa berupa tabel seperti ini:

coordinate_events: menyimpan x, z, world, waktu kejadian, jenis event.
access_logs: menyimpan aktivitas user per area dan timestamp.
telemetry: menyimpan event sensor atau pergerakan objek dalam ruang 2D/3D.

Pola query yang paling sering memicu bottleneck biasanya seperti:

Ambil event dalam area tertentu: x antara A dan B, z antara C dan D.
Urutkan hasil berdasarkan waktu terbaru.
Ambil halaman ke-N.
Hitung total hasil untuk UI pagination.

Di skala kecil, query semacam ini terlihat normal. Di skala ratusan juta hingga miliaran baris, kombinasi filter rentang, sort, dan offset besar bisa berubah menjadi operasi mahal.

Model Data dan Bentuk Query yang Perlu Diwaspadai

Contoh tabel generik:

CREATE TABLE coordinate_events (
  id BIGINT PRIMARY KEY,
  world_id BIGINT NOT NULL,
  x INT NOT NULL,
  z INT NOT NULL,
  event_time TIMESTAMP NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  actor_id BIGINT,
  payload JSON
);

Contoh query yang umum:

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
  AND x BETWEEN 120000 AND 121000
  AND z BETWEEN -45000 AND -44000
ORDER BY event_time DESC, id DESC
LIMIT 100;

Query ini terlihat sederhana, tetapi ada beberapa tantangan:

Filter area memakai dua rentang pada x dan z.
Sort by waktu dapat memaksa database melakukan sort besar jika index tidak cocok.
Data sangat besar, sehingga salah memilih index bisa berujung ke scan banyak halaman disk.

Kesalahan umum adalah menganggap satu index terpisah pada x, satu pada z, dan satu pada event_time otomatis cukup. Sering kali tidak. Database bisa saja tetap memilih scan yang mahal, atau menggabungkan index dengan hasil yang kurang efisien dibanding satu composite index yang tepat.

Composite Index untuk WHERE + ORDER BY

Aturan praktis yang paling penting: desain index mengikuti pola query dominan, bukan mengikuti daftar kolom populer secara terpisah.

Kenapa single-column index sering gagal

Misalnya Anda punya index berikut:

CREATE INDEX idx_events_world ON coordinate_events (world_id);
CREATE INDEX idx_events_x ON coordinate_events (x);
CREATE INDEX idx_events_z ON coordinate_events (z);
CREATE INDEX idx_events_time ON coordinate_events (event_time);

Secara teori semua kolom penting sudah di-index. Dalam praktik, query area + urut waktu belum tentu terbantu banyak karena database tetap harus:

menemukan banyak kandidat dari filter rentang,
mengakses tabel utama berulang kali,
lalu melakukan sort terpisah untuk ORDER BY event_time DESC.

Index yang lebih dekat ke pola akses

Jika query dominan selalu dimulai dari world_id, lalu area, lalu urut waktu, maka composite index lebih masuk akal. Contoh umum:

CREATE INDEX idx_events_world_time_id
ON coordinate_events (world_id, event_time DESC, id DESC);

Index ini sangat membantu untuk query seperti:

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
  AND event_time <= :cursor_time
ORDER BY event_time DESC, id DESC
LIMIT 100;

Namun untuk filter area dua dimensi, desain index menjadi lebih rumit. Index B-tree biasa kurang ideal untuk dua rentang sekaligus pada x dan z. Karena itu ada beberapa pendekatan:

Prioritaskan dimensi waktu jika akses utama adalah timeline terbaru dalam dunia tertentu, lalu area difilter setelah kandidat waktu dipersempit.
Gunakan bucketing atau tile/chunk agar query area tidak langsung memakai dua rentang mentah.
Partisi berdasarkan waktu atau world untuk mengecilkan subset data yang harus diperiksa.

Mengubah query area menjadi lebih ramah index

Untuk data peta, sering kali lebih efektif menyimpan kolom turunan seperti chunk_x dan chunk_z, atau tile_id. Misalnya area Minecraft dibagi per chunk, lalu query menjadi:

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
  AND chunk_x BETWEEN 7500 AND 7562
  AND chunk_z BETWEEN -2813 AND -2750
ORDER BY event_time DESC, id DESC
LIMIT 100;

Atau bahkan lebih baik jika area dipetakan ke daftar tile yang eksplisit dan jumlahnya terbatas.

Composite index yang mungkin relevan:

CREATE INDEX idx_events_world_chunk_time_id
ON coordinate_events (world_id, chunk_x, chunk_z, event_time DESC, id DESC);

Trade-off-nya:

Index jadi besar.
Biaya write naik karena setiap insert/update harus memelihara index.
Tidak semua urutan kolom cocok untuk semua query.

Karena itu, jangan membuat composite index panjang hanya berdasarkan tebakan. Validasi dengan query nyata dan EXPLAIN.

Mendiagnosis Query Lambat dengan EXPLAIN

Sebelum menambah index, lihat dulu bagaimana database mengeksekusi query. Tujuannya bukan sekadar mencari kata “index dipakai”, tetapi memahami berapa banyak baris dibaca, diurutkan, dan dibuang.

Pola yang perlu diperiksa

Full table scan atau full partition scan: tanda bahwa filter tidak cukup selektif atau index tidak cocok.
Rows examined jauh lebih besar daripada rows returned: artinya query membaca terlalu banyak kandidat.
Sort terpisah untuk ORDER BY: sering muncul jika urutan index tidak cocok.
Temporary structure / filesort / external sort: indikasi sort tidak bisa dipenuhi dari index.
Bitmap/index merge yang mahal: kadang lebih baik dari scan penuh, tetapi belum tentu efisien di dataset sangat besar.
Lookup berulang ke heap/table: mahal jika hasil kandidat sangat banyak.

Contoh query untuk dianalisis

EXPLAIN
SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
  AND x BETWEEN 120000 AND 121000
  AND z BETWEEN -45000 AND -44000
ORDER BY event_time DESC, id DESC
LIMIT 100;

Saat membaca hasil EXPLAIN, tanyakan:

Apakah filter world_id benar-benar mempersempit data?
Apakah database memakai index yang sekaligus membantu sort?
Apakah ada operasi sort besar setelah filtering?
Berapa perkiraan jumlah baris yang dipindai sebelum dapat 100 hasil?

Jika query hanya mengambil 100 baris tetapi harus memeriksa jutaan baris lebih dulu, bottleneck utama biasanya ada pada desain akses data, bukan pada ukuran LIMIT.

Gejala bottleneck umum

CPU tinggi: sering karena sort besar, evaluasi filter masif, atau terlalu banyak row comparison.
I/O disk tinggi: sering karena scan luas, random lookup ke banyak halaman, atau index terlalu besar untuk cache.
Latency naik saat offset membesar: ciri klasik offset pagination pada tabel besar.
Write melambat setelah menambah banyak index: biaya maintenance index meningkat.

Offset Pagination vs Keyset Pagination

Pada data besar, pagination sering lebih mahal daripada filter. Dua pendekatan yang paling umum adalah offset pagination dan keyset pagination.

Offset pagination

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
ORDER BY event_time DESC, id DESC
LIMIT 100 OFFSET 10000;

Kelebihan:

Mudah dipahami.
Cocok untuk UI sederhana yang benar-benar butuh nomor halaman.

Kekurangan:

Semakin besar OFFSET, semakin banyak baris yang harus dilewati.
Hasil bisa tidak stabil jika data baru terus masuk.
Latency cenderung memburuk pada halaman dalam.

Pada tabel yang terus bertambah, offset pagination sering berarti database tetap membaca banyak baris lalu membuangnya sebelum mengembalikan hasil.

Keyset pagination

Keyset pagination memakai nilai dari baris terakhir halaman sebelumnya sebagai cursor. Misalnya urutan utama adalah event_time DESC, id DESC:

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
  AND (
    event_time < :last_event_time
    OR (event_time = :last_event_time AND id < :last_id)
  )
ORDER BY event_time DESC, id DESC
LIMIT 100;

Kelebihan:

Tidak perlu melompati ribuan atau jutaan baris.
Lebih stabil untuk data yang terus berubah.
Biasanya jauh lebih cocok dengan index terurut.

Kekurangan:

Tidak nyaman untuk lompat langsung ke halaman 500.
Butuh urutan yang jelas dan stabil, biasanya kombinasi kolom unik seperti event_time, id.

Untuk API timeline, feed, event browser, atau hasil pencarian besar, keyset pagination hampir selalu pilihan yang lebih aman dibanding offset.

Kapan offset masih masuk akal

Dataset kecil atau hasil filter sempit.
Pengguna benar-benar butuh nomor halaman absolut.
Halaman dalam jarang diakses.

Jika kebutuhan bisnis mewajibkan nomor halaman, batasi ekspektasi: offset bisa dipakai untuk subset kecil, sedangkan query besar diarahkan ke filter tambahan atau mode cursor.

Risiko COUNT(*) yang Mahal

Banyak implementasi pagination otomatis menjalankan dua query: satu untuk data, satu lagi untuk total jumlah baris. Pada tabel besar dengan filter area dan waktu, COUNT(*) bisa menjadi biaya tersembunyi yang justru lebih mahal daripada query data itu sendiri.

SELECT COUNT(*)
FROM coordinate_events
WHERE world_id = 7
  AND x BETWEEN 120000 AND 121000
  AND z BETWEEN -45000 AND -44000;

Kenapa mahal?

Database tetap harus mengevaluasi semua kandidat yang cocok.
Filter rentang pada data besar bisa menyentuh banyak halaman index atau tabel.
COUNT untuk hasil dinamis sulit di-cache secara akurat.

Alternatif yang lebih praktis

Jangan tampilkan total absolut jika tidak benar-benar perlu.
Gunakan “has_next_page” dengan mengambil LIMIT + 1.
Simpan agregat terpisah untuk statistik kasar, bukan total hasil query arbitrer.
Gunakan estimasi bila UI hanya perlu kisaran, dengan catatan akurasi bukan jaminan.

Contoh pola LIMIT + 1:

SELECT id, world_id, x, z, event_time, event_type
FROM coordinate_events
WHERE world_id = 7
ORDER BY event_time DESC, id DESC
LIMIT 101;

Jika hasil lebih dari 100, berarti masih ada halaman berikutnya. Ini sering cukup untuk API.

Strategi Arsip dan Partisi Saat Data Terus Tumbuh

Pada skala mirip peta 15 TB, tidak semua data harus dilayani dari tabel aktif yang sama. Jika semua tahun, semua dunia, dan semua event bercampur dalam satu ruang kerja panas, performa akan memburuk meski index sudah cukup baik.

Partisi berdasarkan waktu

Jika query sering memfilter rentang waktu, partisi per bulan atau per periode tertentu bisa membantu mengurangi data yang dipindai. Keuntungannya:

Query hanya menyentuh partisi relevan.
Arsip data lama lebih mudah.
Maintenance seperti vacuum, rebuild, atau drop arsip lebih terkontrol.

Trade-off:

Terlalu banyak partisi juga bisa menambah overhead perencanaan query.
Query lintas banyak partisi tetap bisa mahal.
Partisi bukan pengganti index yang buruk.

Partisi berdasarkan domain akses

Untuk kasus peta, kadang masuk akal membagi data berdasarkan world_id, shard wilayah, atau kelompok tenant. Ini cocok jika akses cenderung terlokalisasi. Namun jangan langsung sharding jika masalah masih bisa diselesaikan dengan perbaikan query dan index.

Arsip data dingin

Jika sebagian besar trafik hanya membaca data terbaru, pindahkan data lama ke tabel atau storage arsip. Pola umum:

hot table untuk data aktif, query cepat, index lebih ketat;
archive table untuk data lama, query lebih jarang, mungkin index lebih minimal.

Penting untuk jelas di level aplikasi: apakah endpoint tertentu hanya membaca data aktif, atau bisa fallback ke arsip dengan latency lebih tinggi.

Kapan Menambah Index, Kapan Query Perlu Didesain Ulang

Menambah index adalah solusi yang sering benar, tetapi tidak selalu. Pada beberapa kasus, query dasarnya memang tidak cocok untuk cara data disimpan sekarang.

Tambahkan index jika:

Pola query dominan stabil dan jelas.
EXPLAIN menunjukkan scan luas atau sort mahal yang bisa dihindari oleh urutan index yang tepat.
Kolom filter memiliki selektivitas yang masuk akal.
Biaya write tambahan masih bisa diterima.

Desain ulang query jika:

Query menggabungkan beberapa rentang besar dan sort yang saling bertentangan.
Offset sangat besar menjadi kebutuhan utama.
Anda bergantung pada COUNT(*) mahal untuk setiap request.
Query perlu mencari area 2D besar pada B-tree biasa tanpa strategi spatial, tile, atau bucketing.
Index yang dibutuhkan menjadi terlalu banyak dan saling tumpang tindih.

Pola redesign yang sering efektif

Ganti offset pagination menjadi keyset pagination.
Tambahkan kolom turunan seperti chunk_x, chunk_z, atau tile_id.
Pisahkan query list dan query detail agar halaman daftar hanya mengambil kolom penting.
Arsipkan data lama agar working set mengecil.
Partisi data sesuai waktu atau domain akses.

Checklist Praktis untuk Query Area, Waktu, dan Rentang

Tulis pola query utama, bukan semua kemungkinan query.
Pastikan ORDER BY stabil, misalnya event_time DESC, id DESC.
Uji keyset pagination sebelum memaksakan offset pada data besar.
Desain composite index berdasarkan WHERE + ORDER BY, bukan per kolom terpisah.
Periksa EXPLAIN: scan luas, sort mahal, rows examined, dan lookup acak.
Evaluasi biaya COUNT(*); ganti dengan LIMIT + 1 jika total tidak wajib.
Pertimbangkan bucketing area seperti chunk atau tile untuk data koordinat.
Arsip atau partisi data saat working set aktif makin sulit muat di cache.
Audit index berkala; index berlebih bisa memperlambat write tanpa membantu read.
Ukur dengan data nyata; query plan yang baik di staging kecil belum tentu baik di produksi besar.

Penutup

Pada skala seperti peta Minecraft 15 TB, query lambat jarang disebabkan oleh satu faktor tunggal. Biasanya masalah datang dari kombinasi filter area yang luas, sorting waktu, pagination yang tidak efisien, dan index yang tidak mengikuti pola akses nyata. Solusi terbaik hampir selalu dimulai dari diagnosis yang disiplin: lihat query dominan, baca EXPLAIN, ukur jumlah baris yang dipindai, lalu pilih apakah cukup menambah composite index atau perlu mengubah bentuk query.

Jika Anda hanya mengingat satu prinsip dari artikel ini, gunakan ini: optimasi query besar bukan tentang menaruh index di semua kolom, tetapi tentang membuat database bisa menemukan baris yang dibutuhkan dalam urutan yang sudah benar, dengan pembacaan seminimal mungkin.

Quick Search

shopping_cart Keranjang