Indexing SQL untuk Dunia 3D: Menjaga Query Spasial Tetap Cepat Saat Data Tumbuh

Pada aplikasi 3D, masalah performa database biasanya tidak muncul saat jumlah objek masih ratusan atau ribuan. Bottleneck mulai terasa ketika scene berisi banyak entitas, collision layer bertambah, histori state terus di-append, dan query harus memfilter area koordinat sambil mengurutkan hasil untuk kebutuhan render, sinkronisasi, atau replay. Di titik ini, indexing SQL untuk dunia 3D bukan sekadar menambah index sebanyak mungkin, melainkan memilih index yang cocok dengan pola baca dan tulis yang nyata.

Jawaban singkatnya: untuk menjaga query spasial tetap cepat saat data tumbuh, Anda perlu menyelaraskan WHERE, ORDER BY, dan bentuk pagination dengan struktur index. Gunakan index majemuk sesuai urutan filter yang paling selektif dan stabil, pertimbangkan covering index untuk mengurangi lookup ke tabel utama, pakai partial index bila hanya sebagian data yang sering diakses, dan hindari OFFSET besar jika urutan hasil harus konsisten. Artikel ini fokus pada praktik yang relevan untuk backend aplikasi 3D, bukan pada engine fisika atau rendering.

Model data yang sering memicu query lambat

Bayangkan sebuah backend yang menyimpan objek 3D per scene. Setiap objek memiliki posisi, layer collision, status aktif, metadata, dan histori state. Struktur sederhananya bisa seperti ini:

scene_objects
- id
- scene_id
- object_type
- layer_id
- is_active
- x
- y
- z
- updated_at

object_metadata
- object_id
- material
- owner_id
- flags

object_state_history
- id
- object_id
- tick
- x
- y
- z
- rotation
- created_at

Workload yang umum:

Mengambil objek aktif pada scene tertentu dalam rentang koordinat.
Mengambil objek berdasarkan layer collision dan mengurutkan berdasarkan waktu update atau id.
Melakukan pagination pada daftar objek besar.
Menampilkan metadata bersama objek tanpa membuat join menjadi mahal.
Menyimpan histori state terus-menerus ke tabel yang sangat sering ditulis.

Kalau index tidak mengikuti pola ini, query planner cenderung memilih sequential scan, bitmap scan yang mahal, atau tetap memakai index namun masih harus sort dan membaca banyak row yang sebenarnya tidak diperlukan.

Bottleneck umum pada query spasial dan data 3D

1. Filter koordinat dengan banyak kondisi rentang

Contoh query yang sangat umum:

SELECT id, layer_id, x, y, z, updated_at
FROM scene_objects
WHERE scene_id = 42
  AND is_active = TRUE
  AND x BETWEEN 100 AND 180
  AND y BETWEEN 200 AND 260
  AND z BETWEEN 0 AND 40
ORDER BY updated_at DESC, id DESC
LIMIT 100;

Masalahnya, kondisi BETWEEN pada beberapa sumbu adalah range predicate. Pada index B-tree biasa, sekali planner masuk ke kolom rentang, kemampuan index untuk membantu kolom setelahnya sering berkurang, terutama untuk urusan filtering yang sangat efisien. Karena itu, urutan kolom pada index majemuk sangat penting.

2. Sorting yang tidak sejalan dengan index

Banyak query 3D tidak hanya memfilter area, tetapi juga perlu urutan stabil, misalnya objek terbaru, objek dengan prioritas tertentu, atau urutan id untuk sinkronisasi. Jika ORDER BY tidak sejalan dengan index, database bisa tetap memakai index untuk filter tetapi lalu membuat langkah sort terpisah yang mahal, terutama jika hasil antara besar.

3. LIMIT/OFFSET yang makin mahal di halaman belakang

Query seperti ini tampak sederhana:

SELECT id, x, y, z, updated_at
FROM scene_objects
WHERE scene_id = 42 AND is_active = TRUE
ORDER BY updated_at DESC, id DESC
LIMIT 100 OFFSET 20000;

Namun OFFSET 20000 berarti database tetap perlu berjalan melewati banyak baris sebelum memberikan 100 baris berikutnya. Pada dataset besar, biaya ini bertambah seiring nomor halaman.

4. Join metadata yang membuat index utama tidak cukup

Misalnya setelah memfilter objek dalam area tertentu, Anda juga ingin menampilkan material atau owner. Jika join dilakukan tanpa index yang tepat di tabel metadata, keuntungan dari index di tabel objek bisa hilang karena tahap berikutnya tetap mahal.

5. Hot table append pada histori state

Tabel histori seperti object_state_history biasanya menerima insert sangat sering. Menambahkan terlalu banyak index pada tabel seperti ini memperlambat write, memperbesar ukuran storage, dan meningkatkan pekerjaan vacuum atau maintenance. Di sini trade-off baca vs tulis harus benar-benar dipertimbangkan.

Strategi indexing SQL untuk dunia 3D

Pahami dulu bentuk query dominan

Sebelum membuat index, kumpulkan 5-10 query paling penting di produksi. Lihat kombinasi:

Kolom yang hampir selalu ada di WHERE.
Kolom dengan selektivitas tinggi, misalnya scene_id atau layer_id tergantung distribusi data.
Urutan hasil pada ORDER BY.
Kolom yang hanya ditampilkan di SELECT tetapi tidak memfilter.

Jangan mulai dari asumsi bahwa setiap kolom butuh index sendiri. Untuk query nyata, satu index majemuk yang tepat sering lebih berguna daripada banyak index tunggal.

Index majemuk untuk filter scene, status, dan urutan

Jika mayoritas query selalu difilter berdasarkan scene_id dan is_active, lalu diurutkan dengan updated_at DESC, id DESC, index berikut biasanya lebih berguna daripada index terpisah per kolom:

CREATE INDEX idx_scene_objects_scene_active_updated_id
ON scene_objects (scene_id, is_active, updated_at DESC, id DESC);

Mengapa bekerja:

scene_id mempersempit dataset ke satu scene.
is_active memisahkan objek aktif dan tidak aktif.
updated_at, id membantu urutan hasil tetap stabil.

Trade-off-nya, index ini sangat baik untuk query yang cocok dengan pola tersebut, tetapi belum tentu membantu untuk filter koordinat berat. Bila query area adalah jalur panas utama, Anda mungkin perlu index lain yang berorientasi pada koordinat.

Index majemuk untuk area query berbasis koordinat

Untuk B-tree biasa, Anda perlu realistis: index tunggal seperti (scene_id, x, y, z) tidak otomatis ideal untuk semua pencarian kubus 3D. Index ini biasanya paling efektif bila query memiliki prefiks kesetaraan yang kuat, misalnya scene_id = ?, lalu satu atau lebih rentang yang masih cukup selektif.

CREATE INDEX idx_scene_objects_scene_layer_x_y_z
ON scene_objects (scene_id, layer_id, x, y, z);

Index seperti ini masuk akal jika workload Anda sering berbentuk:

SELECT id, x, y, z
FROM scene_objects
WHERE scene_id = 42
  AND layer_id = 3
  AND x BETWEEN 100 AND 180
  AND y BETWEEN 200 AND 260
  AND z BETWEEN 0 AND 40;

Namun ada batasannya:

Jika distribusi koordinat lebar dan semua sumbu memakai rentang, efisiensi B-tree bisa cepat turun.
Urutan kolom harus mengikuti filter yang paling sering dan paling menyempitkan data.
Satu index tidak akan optimal untuk semua kombinasi query area.

Jika database Anda mendukung tipe dan index spasial khusus, itu bisa lebih cocok untuk geometri tertentu. Tetapi jika skema Anda menyimpan koordinat sebagai kolom numerik biasa, pendekatan B-tree tetap relevan selama Anda memahami batasannya dan fokus pada pola query yang dominan.

Partial index untuk objek aktif atau scene panas

Bila sebagian besar query hanya menyentuh objek aktif, partial index bisa memangkas ukuran index dan meningkatkan efisiensi:

CREATE INDEX idx_scene_objects_active_scene_updated_id
ON scene_objects (scene_id, updated_at DESC, id DESC)
WHERE is_active = TRUE;

Kelebihan:

Ukuran index lebih kecil.
Write overhead lebih rendah dibanding index penuh.
Planner bisa memilih index yang lebih fokus.

Kekurangannya, index ini tidak membantu query yang mencari objek nonaktif atau query yang tidak memenuhi kondisi partial tersebut. Partial index sangat cocok bila pola aksesnya konsisten dan jelas.

Covering index untuk mengurangi lookup ke tabel utama

Pada beberapa database, index bisa dirancang agar mencakup kolom yang sering diambil di SELECT. Tujuannya adalah mengurangi kebutuhan membaca tabel utama setelah baris kandidat ditemukan. Secara konsep:

-- Bentuk konseptual, fitur detail bergantung pada database
CREATE INDEX idx_scene_objects_scene_active_updated_cover
ON scene_objects (scene_id, is_active, updated_at DESC, id DESC);

Jika query Anda hanya mengambil kolom yang sudah ada dalam index atau yang bisa disertakan sebagai kolom tambahan, database mungkin dapat melakukan pembacaan yang lebih hemat I/O. Ini sangat berguna untuk endpoint daftar objek atau sinkronisasi state yang sering dipanggil.

Trade-off-nya jelas: semakin lebar index, semakin besar storage dan biaya update. Jangan memasukkan terlalu banyak kolom hanya karena “mungkin nanti dipakai”.

Index pada tabel metadata untuk menjaga join tetap murah

Join sederhana:

SELECT o.id, o.x, o.y, o.z, m.material, m.owner_id
FROM scene_objects o
JOIN object_metadata m ON m.object_id = o.id
WHERE o.scene_id = 42
  AND o.is_active = TRUE
ORDER BY o.updated_at DESC, o.id DESC
LIMIT 100;

Minimal, pastikan kolom join di tabel metadata diindex:

CREATE INDEX idx_object_metadata_object_id
ON object_metadata (object_id);

Jika metadata juga sering difilter, misalnya hanya objek dengan owner tertentu, Anda mungkin perlu index lain yang mengikuti pola tersebut. Intinya, index bagus di tabel utama tidak menolong banyak jika tahap join tetap harus memindai tabel kedua secara mahal.

Index histori state: sedikit, tajam, dan sesuai query

Pada tabel append-heavy seperti object_state_history, godaannya adalah menambah banyak index untuk berbagai kebutuhan analitik. Hati-hati. Setiap insert harus memperbarui semua index terkait.

Jika query utama adalah mengambil histori terakhir per objek atau rentang tick per objek, index seperti ini biasanya lebih masuk akal:

CREATE INDEX idx_state_history_object_tick
ON object_state_history (object_id, tick DESC);

Untuk query per scene, sering lebih baik melakukan denormalisasi terbatas atau menyimpan relasi yang memudahkan akses, daripada membuat banyak index mahal pada tabel histori yang terus tumbuh.

Kapan keyset pagination lebih aman daripada LIMIT/OFFSET

Untuk daftar besar yang terus berubah, keyset pagination hampir selalu lebih stabil dan lebih murah daripada LIMIT/OFFSET. Misalnya:

SELECT id, scene_id, x, y, z, updated_at
FROM scene_objects
WHERE scene_id = 42
  AND is_active = TRUE
ORDER BY updated_at DESC, id DESC
LIMIT 100;

Setelah halaman pertama didapat, simpan pasangan (updated_at, id) dari baris terakhir, lalu lanjutkan:

SELECT id, scene_id, x, y, z, updated_at
FROM scene_objects
WHERE scene_id = 42
  AND is_active = TRUE
  AND (updated_at < :last_updated_at
       OR (updated_at = :last_updated_at AND id < :last_id))
ORDER BY updated_at DESC, id DESC
LIMIT 100;

Mengapa lebih aman:

Database tidak perlu membuang ribuan atau jutaan row hanya untuk mencapai halaman berikutnya.
Urutan lebih stabil saat ada insert atau update baru.
Index majemuk pada (scene_id, is_active, updated_at DESC, id DESC) bisa dimanfaatkan lebih konsisten.

Kapan LIMIT/OFFSET masih layak dipakai:

Data relatif kecil.
Pagination hanya untuk backoffice atau admin yang jarang dipakai.
Kebutuhan nomor halaman eksplisit lebih penting daripada performa absolut.

Jika hasil query harus konsisten untuk scrolling, replay, atau sinkronisasi klien, keyset pagination biasanya pilihan yang lebih aman dibanding OFFSET besar.

Cara membaca EXPLAIN secara praktis

Jangan berhenti setelah membuat index. Cek apakah planner benar-benar memakainya. Bentuk output EXPLAIN berbeda antar database, tetapi prinsip praktisnya sama.

Apa yang perlu dicari

Jenis scan: apakah menggunakan index scan, range scan, bitmap scan, atau malah full table scan.
Filter vs index condition: kolom mana yang dipakai benar-benar untuk menyempitkan pencarian, dan kolom mana yang baru difilter setelah row ditemukan.
Sort terpisah: apakah ada langkah sort tambahan karena urutan tidak sesuai index.
Rows estimated vs actual: bila jauh berbeda, statistik mungkin tidak akurat atau distribusi data tidak sesuai asumsi planner.
Lookup berulang: pada join, lihat apakah ada nested loop yang masuk akal atau justru menyebabkan banyak pembacaan acak.

Contoh pembacaan praktis

Misalnya query area + sorting tetap menunjukkan langkah sort besar walau Anda sudah punya index untuk filter. Itu berarti index membantu menemukan kandidat, tetapi belum bisa melayani urutan hasil. Solusinya bisa salah satu dari berikut:

Ubah urutan kolom pada index agar lebih cocok dengan ORDER BY.
Pecah kebutuhan menjadi dua jalur query: satu fokus area, satu fokus feed terurut.
Kurangi jumlah row kandidat sebelum sorting, misalnya dengan filter tambahan yang lebih selektif.

Jika EXPLAIN menunjukkan full scan pada tabel histori, jangan langsung menyimpulkan planner salah. Bisa jadi query memang terlalu lebar, atau index yang tersedia tidak cocok dengan bentuk WHERE. Selalu cocokkan EXPLAIN dengan query nyata, bukan dengan harapan kita.

Debugging saat index tidak dipakai

Pastikan urutan kolom index sesuai pola WHERE dan ORDER BY.
Cek apakah query memakai fungsi atau cast pada kolom yang membuat index sulit dipakai.
Lihat selektivitas. Jika kondisi terlalu longgar, planner bisa memilih scan penuh karena memang lebih murah.
Perbarui statistik bila database Anda membutuhkannya.
Periksa apakah parameter query membuat planner memilih rencana generik yang kurang ideal.

Gejala over-indexing yang sering diabaikan

Menambah index memang bisa mempercepat query baca tertentu, tetapi terlalu banyak index juga merusak performa produksi. Tanda-tandanya:

Insert/update pada tabel objek atau histori makin lambat tanpa perubahan query baca yang signifikan.
Ukuran storage membengkak karena index lebih besar daripada data yang benar-benar sering dipakai.
Vacuum, maintenance, atau reindex menjadi lebih berat.
Banyak index yang mirip, misalnya (scene_id), (scene_id, is_active), dan (scene_id, is_active, updated_at) padahal sebagian tidak pernah dipakai.
Planner memilih index yang berbeda-beda tetapi hasilnya tidak konsisten karena statistik dan distribusi data rumit.

Prinsip yang aman: setiap index harus punya alasan query yang jelas. Jika Anda tidak bisa menunjuk query produksi yang diuntungkan, index tersebut patut dipertanyakan.

Checklist audit indexing untuk workload produksi 3D

Daftar query termahal
Ambil query paling sering dan paling mahal dari log atau monitoring. Pisahkan jalur baca scene, collision layer, histori state, dan sinkronisasi klien.
Cocokkan setiap query dengan satu strategi index utama
Tentukan apakah query itu butuh index untuk filter area, urutan, join, atau kombinasi beberapa kebutuhan. Hindari berharap satu index menyelesaikan semua pola akses.
Periksa urutan kolom index majemuk
Kolom prefiks harus sesuai dengan kondisi yang paling stabil dan paling sering dipakai. Jangan menaruh kolom berselektivitas rendah di depan tanpa alasan kuat.
Evaluasi partial index
Jika mayoritas traffic hanya menyentuh is_active = TRUE atau subset data tertentu, partial index sering memberi rasio manfaat/biaya yang baik.
Audit pagination
Cari endpoint yang masih memakai OFFSET besar. Untuk data yang terus berubah, pertimbangkan migrasi ke keyset pagination.
Audit join metadata
Pastikan kolom foreign key atau kolom filter pada tabel metadata memiliki index yang relevan. Optimasi di tabel utama tidak cukup jika join tetap mahal.
Minimalkan index pada hot append table
Untuk tabel histori state, simpan hanya index yang benar-benar dibutuhkan jalur query utama. Pertimbangkan retensi, partisi, atau arsip jika volume terus tumbuh.
Baca EXPLAIN sebelum dan sesudah perubahan
Jangan menilai dari teori saja. Pastikan rencana eksekusi benar-benar membaik dan tidak sekadar berpindah bottleneck.
Hapus index yang redundan
Index yang tumpang tindih dan tidak dipakai menambah biaya write tanpa manfaat nyata.
Uji dengan distribusi data realistis
Query spasial sangat sensitif terhadap distribusi scene, layer, dan koordinat. Dataset kecil atau terlalu rapi sering memberi kesan performa palsu.

Penutup

Indexing SQL untuk dunia 3D bukan masalah menaruh index pada setiap kolom koordinat, melainkan menyusun index berdasarkan bentuk query yang benar-benar terjadi: filter scene, layer collision, area koordinat, urutan hasil, join metadata, dan histori state yang terus bertambah. Fokus utama Anda adalah memastikan index membantu kerja yang paling mahal, bukan sekadar membuat planner “mungkin” punya lebih banyak opsi.

Jika harus mulai dari satu langkah praktis, lakukan ini: ambil query produksi yang paling lambat, baca EXPLAIN, lalu cocokkan ulang index dengan kombinasi WHERE, ORDER BY, dan pola pagination-nya. Dari sana, Anda akan lebih mudah memutuskan kapan butuh index majemuk, kapan partial index layak dipakai, kapan covering index masuk akal, dan kapan keyset pagination lebih aman daripada LIMIT/OFFSET.

Quick Search

shopping_cart Keranjang