Pada backend game RTS lintas perangkat, masalah performa database biasanya tidak muncul saat data masih kecil. Begitu jumlah savegame, replay, match history, dan leaderboard tumbuh, query yang tadinya terasa cepat mulai melambat, terutama pada endpoint daftar data, sinkronisasi akun, dan layar riwayat pertandingan. Dalam konteks proyek seperti Generals-Mac-iOS-iPad yang menginspirasi skenario RTS di beberapa platform, pola beban seperti ini sangat realistis: satu pemain bisa punya banyak savegame, ribuan replay bisa diakses dengan filter berbeda, dan leaderboard dibaca jauh lebih sering daripada ditulis.

Fokus optimasi di sini bukan sekadar “tambahkan index”, tetapi memilih index yang sesuai dengan pola query nyata, membaca EXPLAIN untuk memahami kenapa query lambat, dan menghindari pagination yang memburuk saat data membesar. Jika salah memilih index, performa baca memang bisa naik, tetapi biaya tulis, storage, dan maintenance ikut meningkat. Karena itu, optimasi harus dilakukan berdasarkan gejala, query, dan trade-off yang jelas.

Studi Kasus: Backend Savegame, Leaderboard, Match History, dan Replay List

Anggap backend RTS memiliki beberapa kebutuhan umum berikut:

  • Savegame: daftar save milik pemain, urut berdasarkan waktu update terbaru.
  • Leaderboard: ranking pemain per mode, musim, atau region.
  • Match history: riwayat pertandingan pemain, biasanya difilter berdasarkan pemain dan diurutkan waktu selesai.
  • Replay list: daftar replay publik atau milik pemain, dengan filter mode, map, atau patch version.

Masalah mulai terasa saat endpoint berikut menjadi berat:

  • GET /players/{id}/savegames
  • GET /leaderboards/{season}
  • GET /players/{id}/matches
  • GET /replays?mode=1&map_id=12&page=200

Gejala umumnya:

  • Latency melonjak pada halaman daftar data yang lebih dalam.
  • CPU database tinggi walau beban aplikasi biasa saja.
  • Query yang sama cepat di data kecil, tetapi melambat setelah jutaan baris.
  • I/O meningkat karena database membaca banyak baris yang akhirnya dibuang.
  • Lock atau antrian write memburuk setelah terlalu banyak index ditambahkan.

Skema Tabel yang Realistis

Contoh berikut disederhanakan, tetapi cukup dekat dengan kebutuhan backend RTS nyata.

Tabel savegames

CREATE TABLE savegames (
  id BIGINT PRIMARY KEY,
  player_id BIGINT NOT NULL,
  device_id VARCHAR(64),
  slot_name VARCHAR(100) NOT NULL,
  mission_id BIGINT,
  payload_size_bytes INT NOT NULL,
  checksum VARCHAR(128),
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP NULL
);

Tabel matches

CREATE TABLE matches (
  id BIGINT PRIMARY KEY,
  player_id BIGINT NOT NULL,
  opponent_id BIGINT,
  mode VARCHAR(32) NOT NULL,
  result VARCHAR(16) NOT NULL,
  duration_seconds INT NOT NULL,
  ended_at TIMESTAMP NOT NULL,
  replay_id BIGINT,
  season_id BIGINT,
  created_at TIMESTAMP NOT NULL
);

Tabel replays

CREATE TABLE replays (
  id BIGINT PRIMARY KEY,
  owner_player_id BIGINT NOT NULL,
  visibility VARCHAR(16) NOT NULL,
  mode VARCHAR(32) NOT NULL,
  map_id BIGINT,
  patch_version VARCHAR(32),
  file_size_bytes BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Tabel leaderboard_entries

CREATE TABLE leaderboard_entries (
  id BIGINT PRIMARY KEY,
  season_id BIGINT NOT NULL,
  mode VARCHAR(32) NOT NULL,
  player_id BIGINT NOT NULL,
  rating INT NOT NULL,
  wins INT NOT NULL,
  losses INT NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

Struktur seperti ini sudah cukup untuk menunjukkan akar masalah: query baca biasanya memfilter beberapa kolom lalu mengurutkan dengan kolom lain. Index tunggal sering tidak cukup.

Gejala Query Lambat yang Perlu Dicurigai

Sebelum menambah index, identifikasi pola lambatnya.

1. Query daftar pemain makin lambat di page besar

Contoh:

SELECT id, slot_name, updated_at
FROM savegames
WHERE player_id = 42 AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20 OFFSET 10000;

Masalah utamanya bukan hanya filter, tetapi OFFSET 10000. Database tetap harus menemukan, mengurutkan, atau menelusuri banyak baris sebelum mengambil 20 baris terakhir yang dibutuhkan.

2. Query terlihat “pakai index” tetapi tetap lambat

Ini sering terjadi saat index tidak cocok dengan WHERE + ORDER BY. Misalnya ada index pada player_id, tetapi query juga butuh urutan updated_at DESC. Akibatnya database mungkin tetap melakukan sorting tambahan atau membaca terlalu banyak baris.

3. Leaderboard mahal saat rank dibaca berulang

Contoh:

SELECT player_id, rating, wins, losses
FROM leaderboard_entries
WHERE season_id = 9 AND mode = 'ranked'
ORDER BY rating DESC, player_id ASC
LIMIT 100;

Tanpa composite index yang sesuai, query ini akan memburuk seiring pertumbuhan data per season dan mode.

Cara Membaca EXPLAIN dengan Benar

EXPLAIN membantu melihat rencana eksekusi query. Format output berbeda antar database, tetapi ide utamanya serupa. Anda ingin menjawab pertanyaan berikut:

  • Apakah query memakai index atau full scan?
  • Berapa banyak baris yang diperkirakan dibaca?
  • Apakah ada sorting tambahan yang mahal?
  • Apakah filter dilakukan setelah terlalu banyak baris terambil?

Contoh query savegame

EXPLAIN
SELECT id, slot_name, updated_at
FROM savegames
WHERE player_id = 42 AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20;

Hal yang perlu dicari dari hasil EXPLAIN:

  • Scan type: hindari full table scan untuk endpoint yang sering dipanggil.
  • Rows examined / estimated rows: makin besar angkanya dibanding hasil akhir, makin banyak kerja sia-sia.
  • Extra sort: misalnya ada indikasi sorting terpisah karena index tidak mendukung urutan yang diminta.
  • Filter pushdown: idealnya filter utama dipakai sedini mungkin, bukan setelah banyak baris dibaca.

Jika query mengembalikan 20 baris tetapi harus membaca puluhan ribu baris, biasanya masalah ada pada desain index atau pagination, bukan pada ORM semata.

Contoh interpretasi sederhana

Misalnya Anda punya index hanya pada player_id. Database bisa cepat menemukan semua savegame pemain, tetapi masih perlu menyaring deleted_at IS NULL dan mengurutkan berdasarkan updated_at DESC. Jika satu pemain memiliki banyak savegame, sorting dan scan tambahan tetap mahal.

Index yang lebih cocok adalah composite index yang mengikuti pola query.

Memilih Composite Index yang Tepat

Aturan praktis: desain index berdasarkan query paling penting, bukan berdasarkan daftar kolom yang “sering dipakai”.

Kasus 1: daftar savegame per pemain

SELECT id, slot_name, updated_at
FROM savegames
WHERE player_id = ? AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 20;

Index yang masuk akal:

CREATE INDEX idx_savegames_player_deleted_updated
ON savegames (player_id, deleted_at, updated_at DESC);

Kenapa ini bekerja:

  • player_id dipakai untuk mempersempit baris milik pemain tertentu.
  • deleted_at membantu menghindari baris soft-deleted jika memang proporsinya signifikan.
  • updated_at DESC selaras dengan urutan hasil, sehingga database bisa mengambil baris terbaru tanpa sort mahal.

Jika database yang dipakai tidak terlalu membedakan arah index naik/turun dalam praktik tertentu, prinsip utamanya tetap sama: susunan kolom pada index harus cocok dengan pola filter dan urutan.

Kasus 2: leaderboard per season dan mode

SELECT player_id, rating, wins, losses
FROM leaderboard_entries
WHERE season_id = ? AND mode = ?
ORDER BY rating DESC, player_id ASC
LIMIT 100;

Index yang cocok:

CREATE INDEX idx_leaderboard_season_mode_rating_player
ON leaderboard_entries (season_id, mode, rating DESC, player_id ASC);

Kenapa bukan index terpisah pada season_id, mode, dan rating saja? Karena database sering tidak bisa menggabungkan index tunggal seefisien satu composite index yang langsung sesuai dengan query utama.

Kasus 3: match history pemain

SELECT id, opponent_id, result, ended_at
FROM matches
WHERE player_id = ?
ORDER BY ended_at DESC
LIMIT 50;

Index yang umum dipakai:

CREATE INDEX idx_matches_player_ended_at
ON matches (player_id, ended_at DESC);

Kasus 4: replay list dengan filter gabungan

SELECT id, owner_player_id, map_id, created_at
FROM replays
WHERE visibility = 'public'
  AND mode = '1v1'
  AND map_id = 12
ORDER BY created_at DESC
LIMIT 30;

Index yang mungkin cocok:

CREATE INDEX idx_replays_visibility_mode_map_created
ON replays (visibility, mode, map_id, created_at DESC);

Namun di sinilah trade-off muncul. Jika kombinasi filter replay sangat banyak dan berubah-ubah, Anda tidak bisa menambah composite index untuk semua kombinasi. Pilih query paling penting dan paling mahal berdasarkan data produksi, bukan intuisi.

Urutan kolom dalam composite index

Pedoman umum:

  1. Kolom equality filter seperti player_id = ?, season_id = ?, mode = ? biasanya diletakkan di depan.
  2. Kolom range atau sorting seperti updated_at, rating, created_at biasanya menyusul.
  3. Jangan membuat composite index yang sangat lebar tanpa alasan kuat.

Meski ini aturan praktis yang sering benar, hasil terbaik tetap harus diverifikasi dengan EXPLAIN dan metrik query nyata.

Offset Pagination: Mudah, tetapi Mahal saat Data Tumbuh

Offset pagination nyaman di awal karena sederhana:

SELECT id, created_at
FROM replays
WHERE visibility = 'public'
ORDER BY created_at DESC
LIMIT 30 OFFSET 3000;

Masalahnya, untuk sampai ke halaman itu, database biasanya tetap harus melewati ribuan baris dulu. Semakin besar offset, semakin besar biaya scan. Pada data kecil ini terasa baik-baik saja, tetapi pada replay list atau match history besar, efeknya jelas.

Kapan offset masih layak

  • Jumlah data per pengguna kecil.
  • Halaman yang diakses umumnya halaman awal.
  • Fitur admin atau backoffice yang trafiknya rendah.
  • Kebutuhan UI mengharuskan lompat ke halaman nomor tertentu.

Kapan beralih ke keyset pagination

Jika endpoint publik atau mobile sering membaca data berurutan dan page dalam, keyset pagination biasanya lebih stabil.

Contoh offset pagination untuk match history:

SELECT id, result, ended_at
FROM matches
WHERE player_id = 42
ORDER BY ended_at DESC, id DESC
LIMIT 20 OFFSET 1000;

Versi keyset pagination:

SELECT id, result, ended_at
FROM matches
WHERE player_id = 42
  AND (ended_at, id) < ('2026-07-01 10:30:00', 987654)
ORDER BY ended_at DESC, id DESC
LIMIT 20;

Cursor diambil dari baris terakhir halaman sebelumnya: (ended_at, id). Ini membuat database melanjutkan pencarian dari titik tertentu, bukan menghitung ulang dari awal.

Index untuk keyset pagination

CREATE INDEX idx_matches_player_ended_id
ON matches (player_id, ended_at DESC, id DESC);

Kenapa keyset pagination lebih cepat:

  • Database tidak perlu membuang ribuan baris akibat offset besar.
  • Pola akses lebih selaras dengan index yang terurut.
  • Latency antar halaman lebih konsisten pada dataset besar.

Keterbatasan keyset pagination

  • Tidak cocok jika UI harus lompat bebas ke page nomor 200.
  • Cursor lebih rumit daripada offset integer biasa.
  • Urutan harus stabil; sering perlu tie-breaker seperti id.
  • Implementasi API dan client sedikit lebih kompleks.

Untuk feed, replay list, match history, dan daftar savegame terbaru, keyset pagination biasanya lebih cocok daripada offset ketika data terus bertambah.

Anti-Pattern Umum pada Optimasi Database Savegame RTS

1. Index tunggal di setiap kolom, tetapi query utama tetap lambat

Menambah index pada player_id, updated_at, dan deleted_at secara terpisah belum tentu membantu query yang membutuhkan ketiganya sekaligus.

2. Mengambil terlalu banyak kolom

Replay list sering hanya butuh id, map_id, created_at, dan metadata ringkas. Jika query ikut mengambil payload besar atau blob terpisah dalam tabel yang sama, I/O menjadi lebih mahal. Pisahkan metadata list dari data besar jika perlu.

3. ORDER BY yang tidak punya tie-breaker

Misalnya hanya ORDER BY created_at DESC. Jika banyak baris punya timestamp sama, pagination bisa tidak stabil. Tambahkan tie-breaker seperti id DESC.

4. Soft delete tanpa mempertimbangkan query utama

Jika hampir semua query memakai deleted_at IS NULL, tetapi index tidak memasukkan kolom itu, database bisa tetap membaca banyak baris yang tidak relevan.

5. Menambah terlalu banyak index

Setiap index menambah biaya insert, update, delete, storage, dan maintenance. Pada savegame yang sering di-update atau leaderboard yang sering di-recalculate, ini bisa menciptakan write amplification.

Trade-off: Read Performance vs Write Amplification

Index mempercepat baca karena database punya struktur tambahan untuk menemukan baris lebih cepat. Namun setiap kali data ditulis, index juga harus diperbarui.

Dampak write amplification

  • Insert lebih mahal: savegame baru harus masuk ke tabel dan semua index terkait.
  • Update lebih mahal: jika kolom terindeks berubah, entri index ikut dipindahkan atau diperbarui.
  • Delete/soft delete lebih mahal: penandaan hapus bisa berdampak pada beberapa index.
  • Storage bertambah: index lebar pada tabel besar mengonsumsi ruang signifikan.

Pada backend RTS, ini penting karena karakter beban bisa campuran:

  • Leaderboard sering heavy-read.
  • Savegame sync bisa heavy-write saat autosave dan sinkronisasi multi-device.
  • Replay list biasanya read-heavy, tetapi upload replay tetap ada.

Karena itu, jangan menambah index hanya berdasarkan satu query lambat tanpa melihat dampaknya ke alur tulis.

Checklist Debugging Bottleneck SQL

Gunakan checklist ini sebelum dan sesudah optimasi:

  1. Ambil query nyata dari produksi, bukan hanya asumsi dari kode.
  2. Kelompokkan query paling mahal berdasarkan frekuensi dan total waktu.
  3. Periksa pola WHERE + ORDER BY + LIMIT.
  4. Jalankan EXPLAIN dan lihat apakah terjadi full scan, sorting tambahan, atau terlalu banyak rows examined.
  5. Cek apakah index yang ada benar-benar dipakai.
  6. Bandingkan hasil sebelum dan sesudah index pada data yang representatif.
  7. Uji offset besar jika endpoint masih memakai offset pagination.
  8. Pastikan urutan pagination stabil dengan tie-breaker.
  9. Audit kolom yang di-select; jangan ambil payload besar pada endpoint list.
  10. Evaluasi biaya write setelah menambah index baru.

Strategi Rollout Index yang Aman di Produksi

Menambah index pada tabel besar bisa berisiko: waktu build lama, konsumsi I/O naik, dan potensi gangguan write. Strategi rollout perlu konservatif.

1. Ukur dulu, jangan langsung migrasi di jam sibuk

Pastikan Anda tahu query mana yang ingin diperbaiki dan bagaimana cara mengukurnya: latency p95/p99 endpoint, rows examined, atau durasi query rata-rata. Hindari rollout buta.

2. Uji pada replika, staging, atau snapshot data produksi

Karena efektivitas index sangat tergantung distribusi data, pengujian pada dataset kecil sering menipu. Dataset yang mirip produksi jauh lebih berguna.

3. Tambahkan satu index per langkah yang terukur

Jika Anda menambahkan lima index sekaligus, sulit mengetahui mana yang benar-benar membantu dan mana yang justru menambah beban tulis.

4. Gunakan metode pembuatan index yang minim gangguan jika tersedia

Setiap database punya kemampuan berbeda untuk membangun index dengan locking yang lebih kecil. Karena perilaku ini bergantung pada engine dan versi, cek dokumentasi database Anda dan pilih pendekatan yang paling aman untuk traffic produksi.

5. Pantau dampak setelah rollout

Perhatikan:

  • latency endpoint target,
  • durasi query terkait,
  • throughput write,
  • CPU dan I/O database,
  • ukuran storage index.

6. Siapkan rollback

Rollback bisa berupa menghapus index yang tidak efektif atau menonaktifkan jalur query baru jika Anda juga mengganti pola pagination. Pastikan prosedurnya jelas sebelum deployment.

Contoh Perbaikan Nyata: dari Offset ke Keyset pada Replay List

Misalkan replay publik makin besar dan endpoint berikut lambat:

SELECT id, owner_player_id, mode, map_id, created_at
FROM replays
WHERE visibility = 'public' AND mode = '1v1'
ORDER BY created_at DESC, id DESC
LIMIT 30 OFFSET 6000;

Perbaikannya:

  1. Tambahkan index yang sesuai.
  2. Ganti pagination ke cursor/keyset.
  3. Pastikan order stabil dengan created_at DESC, id DESC.
CREATE INDEX idx_replays_visibility_mode_created_id
ON replays (visibility, mode, created_at DESC, id DESC);
SELECT id, owner_player_id, mode, map_id, created_at
FROM replays
WHERE visibility = 'public'
  AND mode = '1v1'
  AND (created_at, id) < ('2026-07-10 12:00:00', 555000)
ORDER BY created_at DESC, id DESC
LIMIT 30;

Hasil yang diharapkan bukan sekadar query “pakai index”, tetapi jumlah baris yang dibaca menjadi lebih dekat ke jumlah baris yang benar-benar dikembalikan.

Kesimpulan

Pada backend savegame RTS, performa database memburuk bukan hanya karena data bertambah, tetapi karena pola query list, sort, dan pagination tidak lagi cocok dengan struktur index. Endpoint seperti savegame list, leaderboard, match history, dan replay list biasanya membaik jika Anda:

  • menganalisis query nyata yang lambat,
  • membaca EXPLAIN untuk melihat scan dan sort yang tidak efisien,
  • memakai composite index yang sesuai dengan WHERE dan ORDER BY,
  • menghindari offset pagination pada data besar, dan
  • mempertimbangkan trade-off write amplification sebelum menambah terlalu banyak index.

Jika harus memilih prioritas, mulai dari endpoint dengan trafik tinggi dan pola query sederhana: daftar savegame terbaru per pemain, match history per pemain, dan leaderboard per season/mode. Itu biasanya memberi dampak paling nyata dengan perubahan yang masih terukur dan aman.