Bottleneck SQL di Event Fisik: Index dan Pagination yang Tahan Growth

Pada aplikasi event fisik seperti registrasi peserta, check-in on-site, pemantauan device, dan log aktivitas booth atau hardware hackathon, bottleneck SQL biasanya bukan muncul karena satu query besar yang jelas salah, tetapi karena pertumbuhan pola akses. Query yang awalnya terasa aman mulai melambat ketika data check-in bertambah, device mengirim status berkala, panitia sering memfilter dashboard, dan halaman daftar masih memakai OFFSET besar.

Masalah utamanya sederhana: struktur query dan index yang cocok untuk ratusan baris sering tidak lagi cocok untuk ratusan ribu atau jutaan baris. Solusinya bukan sekadar “tambahkan index”, tetapi memahami query mana yang benar-benar kritis, bagaimana database memilih akses plan, dan kapan harus berpindah dari pagination berbasis offset ke keyset, menambah composite index, atau memisahkan data panas dan data dingin.

Dalam konteks hardware hackathon, beban ini masuk akal. Selain tabel peserta, biasanya ada tabel device, telemetry ringan, status terbaru, antrian check-in, dan audit log. Fokus artikel ini adalah sisi operasional aplikasinya: bagaimana membuat query tetap cepat saat event bertumbuh, tanpa membabi buta menambah index yang justru memperlambat write.

Gejala bottleneck SQL yang paling sering muncul

1. OFFSET makin lambat saat data daftar membesar

Gejala klasik pada halaman admin:

SELECT id, participant_name, team_name, checkin_status, created_at
FROM participants
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Query ini terlihat normal, tetapi pada offset besar database tetap harus melewati banyak baris sebelum menemukan 50 baris yang diminta. Walau ada index pada created_at, biaya baca tetap naik seiring angka offset. Pada dashboard panitia yang sering membuka halaman akhir atau menelusuri data lama, ini terasa sebagai loading yang makin lambat dari hari ke hari.

2. Filter dashboard berat karena kombinasi kolom tidak diindeks dengan benar

Contoh query yang umum di event:

SELECT id, participant_name, checkin_status, gate_id, created_at
FROM checkins
WHERE event_id = 42
  AND checkin_status = 'success'
  AND created_at >= '2026-08-16 08:00:00'
  AND created_at < '2026-08-17 08:00:00'
ORDER BY created_at DESC
LIMIT 100;

Jika yang tersedia hanya index terpisah seperti (event_id), (checkin_status), dan (created_at), database belum tentu bisa menjalankan query ini seefisien yang Anda bayangkan. Sering kali dibutuhkan composite index yang sesuai urutan filter dan sort dominan.

3. Mencari status terbaru per device menjadi mahal

Pada hardware hackathon, tiap device bisa mengirim heartbeat, status baterai, hasil pembacaan sensor, atau status konektivitas. Kebutuhan dashboard biasanya adalah: “ambil status terbaru untuk setiap device”. Query naif sering berbentuk:

SELECT device_id, status, recorded_at
FROM device_logs dl
WHERE recorded_at = (
  SELECT MAX(recorded_at)
  FROM device_logs
  WHERE device_id = dl.device_id
);

Pola ini bisa sangat mahal pada tabel log besar karena menghitung pencarian maksimum berulang kali. Solusinya sering bukan hanya index, tetapi juga perubahan bentuk query atau bahkan materialisasi status terbaru ke tabel terpisah.

4. Lonjakan write setelah registrasi dibuka atau check-in dimulai

Read dan write punya trade-off. Setiap index tambahan membantu sebagian query baca, tetapi juga menambah biaya saat insert atau update. Saat banyak device mengirim log atau relawan check-in memasukkan data serentak, write amplification mulai terasa. Akibatnya bukan hanya insert lambat, tetapi juga lock contention, replication lag, atau antrian kerja backend menumpuk.

Audit query: mulai dari yang nyata, bukan asumsi

Langkah pertama bukan membuat 10 index baru, tetapi mengaudit query yang benar-benar membebani sistem.

Apa yang perlu dicatat

Query paling sering dieksekusi, terutama endpoint dashboard, daftar peserta, check-in, dan status device.
Query paling lambat berdasarkan waktu eksekusi.
Query dengan total beban terbesar: query sedang yang dipanggil ribuan kali sering lebih merusak daripada satu query sangat lambat.
Pola parameter: filter apa yang paling sering dipakai panitia, rentang waktu berapa, dan urutan sort apa.

Gunakan EXPLAIN untuk memeriksa rencana akses

Contoh:

EXPLAIN
SELECT id, participant_name, checkin_status, gate_id, created_at
FROM checkins
WHERE event_id = 42
  AND checkin_status = 'success'
  AND created_at >= '2026-08-16 08:00:00'
  AND created_at < '2026-08-17 08:00:00'
ORDER BY created_at DESC
LIMIT 100;

Yang ingin Anda cari dari output EXPLAIN secara umum:

Apakah query memakai index yang relevan atau malah melakukan scan besar.
Apakah urutan sort memaksa database melakukan sort tambahan yang mahal.
Apakah jumlah baris yang diperkirakan terlalu besar untuk operasi yang seharusnya sempit.
Apakah query bisa menjadi covering index, sehingga database tidak perlu bolak-balik ke data utama untuk kolom yang dipilih.

Jangan membaca EXPLAIN hanya sebagai “pakai index atau tidak”. Query bisa memakai index tetapi tetap buruk, misalnya karena index yang dipilih tidak sesuai urutan filter dan sort, atau karena jumlah baris yang tetap harus dibaca masih terlalu besar.

Memilih composite index yang benar

Kesalahan paling umum adalah membuat index satu kolom untuk setiap field yang sering dipakai, lalu berharap optimizer akan menggabungkannya secara ideal. Dalam praktik, query operasional event lebih sering terbantu oleh composite index yang mengikuti pola akses nyata.

Contoh pola query check-in

SELECT id, participant_id, checkin_status, gate_id, created_at
FROM checkins
WHERE event_id = ?
  AND checkin_status = ?
  AND created_at >= ?
  AND created_at < ?
ORDER BY created_at DESC
LIMIT 100;

Index yang biasanya lebih relevan untuk pola ini adalah:

CREATE INDEX idx_checkins_event_status_created_at
ON checkins (event_id, checkin_status, created_at DESC);

Mengapa urutan ini masuk akal?

event_id biasanya membatasi ruang pencarian paling awal.
checkin_status menyempitkan lagi data dalam event itu.
created_at dipakai untuk rentang waktu sekaligus ORDER BY.

Jika query paling sering justru hanya memfilter event_id dan rentang waktu tanpa checkin_status, maka Anda mungkin perlu index lain seperti:

CREATE INDEX idx_checkins_event_created_at
ON checkins (event_id, created_at DESC);

Artinya, satu index tidak selalu cocok untuk semua query. Tetapi jangan juga membuat index untuk semua kemungkinan filter UI. Pilih berdasarkan frekuensi dan biaya query.

Index untuk status terbaru per device

Jika kebutuhan Anda adalah mencari data terbaru untuk device tertentu atau banyak device, index dasar yang hampir selalu berguna adalah:

CREATE INDEX idx_device_logs_device_recorded_at
ON device_logs (device_id, recorded_at DESC);

Ini membantu query seperti:

SELECT device_id, status, battery_level, recorded_at
FROM device_logs
WHERE device_id = ?
ORDER BY recorded_at DESC
LIMIT 1;

Namun untuk mengambil status terbaru untuk semua device sekaligus, index ini saja belum selalu cukup jika Anda tetap membaca dari tabel log mentah. Pada skala yang tumbuh cepat, pendekatan yang lebih tahan growth adalah menyimpan status terbaru di tabel ringkas:

CREATE TABLE device_latest_status (
  device_id BIGINT PRIMARY KEY,
  status VARCHAR(32) NOT NULL,
  battery_level INTEGER,
  recorded_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

Setiap kali log baru masuk, aplikasi atau worker memperbarui tabel ini jika timestamp lebih baru. Trade-off-nya adalah ada kompleksitas sinkronisasi, tetapi dashboard tidak perlu menghitung “latest per device” dari jutaan log setiap kali dibuka.

Index untuk kolom waktu dan status

Kolom waktu dan status hampir selalu dominan pada aplikasi event. Gunakan dengan hati-hati:

Index waktu berguna untuk query rentang waktu, arsip, dan urutan terbaru.
Index status bermanfaat jika nilai status cukup selektif atau selalu dikombinasikan dengan kolom lain seperti event, gate, atau waktu.
Index tunggal pada status kadang kurang berguna bila nilai status hanya sedikit, misalnya success, failed, pending. Lebih efektif bila dijadikan bagian dari composite index.

Keyset pagination: pengganti OFFSET yang tahan growth

Untuk daftar peserta, check-in, atau log terbaru, pagination berbasis OFFSET akan terus memburuk saat tabel membesar. Solusi yang lebih tahan growth adalah keyset pagination atau seek method.

Masalah OFFSET

SELECT id, participant_name, created_at
FROM participants
WHERE event_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

Database tetap harus melewati banyak baris sebelum mengembalikan hasil.

Versi keyset pagination

Halaman pertama:

SELECT id, participant_name, created_at
FROM participants
WHERE event_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

Halaman berikutnya memakai cursor dari baris terakhir halaman sebelumnya, misalnya created_at dan id:

SELECT id, participant_name, created_at
FROM participants
WHERE event_id = 42
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang sesuai:

CREATE INDEX idx_participants_event_created_id
ON participants (event_id, created_at DESC, id DESC);

Mengapa ini lebih cepat?

Database tidak perlu menghitung dan membuang puluhan ribu baris sebelumnya.
Query bergerak langsung dari titik cursor terakhir.
Performa lebih stabil walau jumlah total data bertambah besar.

Kapan keyset pagination cocok

Daftar yang diurutkan berdasarkan kolom stabil seperti created_at dan id.
Feed log, daftar peserta terbaru, riwayat check-in, dan antrian operasional.

Keterbatasannya

Tidak ideal untuk kebutuhan “lompat ke halaman 237”.
Cursor harus memakai urutan yang konsisten dan unik, sehingga sering butuh tie-breaker seperti id.
UI perlu menyesuaikan: lebih cocok untuk “next/previous” daripada nomor halaman absolut.

Pola query untuk status terbaru per device

Ada tiga pendekatan umum. Pilih sesuai kebutuhan read/write Anda.

1. Ambil dari tabel log langsung

Cocok jika jumlah device masih kecil dan dashboard tidak terlalu sering dibuka.

SELECT device_id, status, battery_level, recorded_at
FROM device_logs
WHERE device_id = ?
ORDER BY recorded_at DESC
LIMIT 1;

Butuh index (device_id, recorded_at DESC).

2. Gunakan subquery atau join untuk latest per device

Contoh bentuk yang lebih masuk akal dibanding correlated subquery naif:

SELECT dl.device_id, dl.status, dl.battery_level, dl.recorded_at
FROM device_logs dl
JOIN (
  SELECT device_id, MAX(recorded_at) AS max_recorded_at
  FROM device_logs
  GROUP BY device_id
) latest
  ON latest.device_id = dl.device_id
 AND latest.max_recorded_at = dl.recorded_at;

Ini bisa bekerja, tetapi pada tabel log besar tetap berat karena agregasi seluruh device harus dihitung berulang jika dashboard sering dimuat.

3. Materialisasi ke tabel status terbaru

Untuk event yang write-nya tinggi dan dashboard sering dibaca, ini sering paling sehat secara operasional. Tabel log tetap disimpan untuk audit, sementara dashboard membaca dari tabel ringkas.

Trade-off-nya:

Kelebihan: read cepat, query sederhana, dashboard stabil.
Kekurangan: logika update tambahan, potensi race condition jika update tidak dirancang hati-hati.

Jika sinkronisasi dilakukan asynchronous, tentukan apakah dashboard boleh menerima data sedikit tertinggal. Untuk monitoring panitia, jeda kecil kadang masih dapat diterima; untuk kontrol perangkat real-time, mungkin tidak.

Trade-off read vs write saat menambah index

Setiap index punya biaya. Pada event fisik, ini penting karena banyak tim terlalu fokus mempercepat dashboard, lalu lupa bahwa scanner check-in dan device log justru didominasi write.

Dampak positif index

Filter lebih cepat.
Sort lebih murah bila urutannya sesuai index.
Range query waktu lebih efisien.

Dampak negatif index

Insert dan update lebih mahal karena semua index terkait harus diperbarui.
Storage bertambah.
Vacuum, maintenance, atau reorganization menjadi lebih berat tergantung engine database.

Prinsip praktis

Index-kan query operasional inti, bukan semua kombinasi filter UI.
Hapus atau evaluasi index yang jarang dipakai tetapi mahal dipelihara.
Pisahkan kebutuhan audit historis dari kebutuhan dashboard real-time bila pola aksesnya berbeda jauh.

Kapan partisi atau arsip data diperlukan

Partisi bukan obat pertama. Jika query inti masih buruk karena tidak ada composite index yang tepat atau masih memakai OFFSET besar, partisi tidak akan menyelesaikan akar masalah. Namun ada titik ketika partisi atau arsip menjadi masuk akal.

Sinyal bahwa partisi patut dipertimbangkan

Tabel log berbasis waktu tumbuh sangat cepat dan query hampir selalu fokus pada data terbaru atau rentang tanggal tertentu.
Retensi data jelas, misalnya log mentah lebih dari beberapa minggu atau bulan jarang disentuh operasional harian.
Maintenance tabel tunggal mulai berat: backup, vacuum, scan, atau purge memakan waktu besar.

Pola yang umum aman

Partisi berdasarkan waktu untuk log check-in atau device logs.
Arsip tabel historis untuk data yang jarang dibaca, sementara tabel utama hanya menyimpan data panas.

Keuntungan utamanya bukan hanya performa query, tetapi juga penghapusan data lama yang lebih mudah dan maintenance yang lebih terprediksi.

Jangan mempartisi tabel transaksi kecil hanya karena terdengar skalabel. Partisi menambah kompleksitas operasional, migrasi, monitoring, dan kadang mengubah cara optimizer bekerja.

Anti-pattern umum yang sering menyebabkan query lambat

Mengandalkan OFFSET besar untuk semua daftar admin.
Menambah index satu kolom secara acak tanpa melihat pola WHERE dan ORDER BY.
Mengambil semua kolom dengan SELECT * padahal UI hanya butuh beberapa field.
Mencari latest status dari tabel log mentah untuk setiap refresh dashboard.
Filter tanggal memakai fungsi pada kolom terindeks, karena dapat membuat index sulit dipakai secara optimal. Lebih aman gunakan rentang waktu eksplisit.
Menyimpan log besar dan data dashboard dalam satu pola akses tanpa ringkasan atau arsip.
Mengabaikan biaya write saat menambah banyak index pada tabel yang menerima insert tinggi.

Contoh audit dan perbaikan bertahap

Kasus 1: daftar check-in lambat

Query awal:

SELECT *
FROM checkins
WHERE event_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 20000;

Masalah:

SELECT * memaksa baca kolom yang mungkin tidak dibutuhkan.
OFFSET besar.
Belum tentu ada index yang cocok dengan filter dan urutan.

Perbaikan:

CREATE INDEX idx_checkins_event_created_id
ON checkins (event_id, created_at DESC, id DESC);

SELECT id, participant_id, checkin_status, gate_id, created_at
FROM checkins
WHERE event_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

Lalu halaman berikutnya:

SELECT id, participant_id, checkin_status, gate_id, created_at
FROM checkins
WHERE event_id = 42
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kasus 2: dashboard filter sukses per gate berat

Query:

SELECT id, participant_id, gate_id, created_at
FROM checkins
WHERE event_id = 42
  AND checkin_status = 'success'
  AND gate_id = 3
  AND created_at >= :start_time
  AND created_at < :end_time
ORDER BY created_at DESC
LIMIT 100;

Jika query ini sangat dominan, index yang lebih tepat mungkin:

CREATE INDEX idx_checkins_event_status_gate_created
ON checkins (event_id, checkin_status, gate_id, created_at DESC);

Namun jangan langsung buat semua variasi. Jika filter gate_id jarang dipakai, index sebelumnya mungkin sudah cukup.

Checklist implementasi aman

Inventaris query inti: daftar peserta, check-in terbaru, filter dashboard, latest status device, log audit.
Ambil baseline: catat waktu query dan volume eksekusi sebelum perubahan.
Periksa EXPLAIN untuk query termahal, bukan hanya yang terasa lambat secara subjektif.
Buat composite index berdasarkan pola nyata dari WHERE + ORDER BY.
Kurangi SELECT * dan ambil kolom yang memang dipakai UI/API.
Ganti OFFSET besar dengan keyset pagination untuk daftar yang sering diakses berurutan.
Pisahkan latest state dari raw log bila dashboard terlalu sering menghitung status terbaru dari tabel besar.
Ukur dampak write setelah menambah index, terutama pada insert check-in dan device log.
Tentukan retensi data: apa yang tetap panas, apa yang bisa diarsip.
Pertimbangkan partisi hanya setelah query dan index dasar sehat.
Rollout bertahap: terapkan satu perubahan penting, ukur lagi, lalu lanjutkan.

Penutup

Bottleneck SQL di event fisik jarang selesai dengan satu trik. Yang paling sering berhasil adalah kombinasi disiplin dasar: audit query nyata, pilih composite index yang sesuai pola akses, hentikan penggunaan OFFSET untuk data besar, dan jangan paksa dashboard membaca dari tabel log mentah jika yang dibutuhkan hanya status terbaru.

Dalam operasional hardware hackathon atau event fisik yang perangkat dan log-nya tumbuh cepat, tujuan Anda bukan membuat semua query sempurna, tetapi menjaga jalur kritis tetap stabil saat data dan traffic naik. Mulailah dari query yang paling sering dipakai panitia dan paling mahal total bebannya. Biasanya di situlah bottleneck SQL paling nyata—dan paling layak diperbaiki lebih dulu.

Quick Search

shopping_cart Keranjang