Optimasi Query Aktivitas Repo: Index Komposit dan Keyset Pagination

Query pada halaman aktivitas repository atau audit log biasanya terlihat sederhana: filter berdasarkan repo, user, atau aksi, lalu urutkan berdasarkan waktu. Masalahnya muncul ketika tabel terus membesar. OFFSET pagination memaksa database melewati banyak baris yang tidak dikembalikan, COUNT(*) untuk total halaman menjadi mahal, dan kombinasi filter + sorting sering gagal memanfaatkan index secara efisien.

Jika Anda mengelola platform VCS self-hosted atau sistem audit internal, bottleneck ini cepat terasa di produksi: halaman aktivitas lambat, CPU database naik, dan latency melonjak saat pengguna membuka halaman-halaman akhir. Solusi yang paling sering efektif adalah kombinasi desain index komposit yang tepat dan migrasi dari OFFSET pagination ke keyset pagination. Artikel ini membahas pendekatan praktisnya, dengan konteks yang relevan untuk sistem seperti Forgejo dan layanan audit log serupa.

Masalah Nyata pada Query Aktivitas Repo

Pola query aktivitas repo umumnya memiliki ciri berikut:

Volume data terus tumbuh, sering kali tanpa retensi pendek.
Filter umum: repo_id, user_id, action, rentang waktu.
Urutan default: terbaru dulu, biasanya ORDER BY created_at DESC.
Pagination untuk UI atau API.
Kadang tetap menampilkan total hasil, misalnya "1.245 event".

Contoh skema tabel yang realistis:

CREATE TABLE repo_activity (
  id BIGINT PRIMARY KEY,
  repo_id BIGINT NOT NULL,
  user_id BIGINT,
  action VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  ip_address VARCHAR(64),
  metadata JSONB
);

Untuk beban baca, query yang sering muncul biasanya mirip seperti ini:

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Di atas kertas query ini tampak wajar. Dalam praktiknya, database perlu menemukan lalu melewati 10.000 baris sebelum mengambil 50 baris berikutnya. Jika filter makin kompleks, misalnya ditambah user_id atau action, performa bisa makin buruk bila index tidak sesuai.

Gejala di Produksi yang Perlu Diwaspadai

Sebelum memperbaiki query, pastikan gejalanya memang mengarah ke bottleneck SQL pada aktivitas repo.

Gejala umum

Latency endpoint aktivitas repo naik tajam pada halaman tinggi atau filter tertentu.
CPU database tinggi saat pengguna membuka halaman histori lama.
I/O meningkat karena banyak pembacaan index dan heap/table.
Lonjakan query lambat pada slow query log.
Waktu respons COUNT(*) lebih buruk daripada query daftar data.
Lock write ikut terasa jika terlalu banyak index ditambahkan tanpa evaluasi.

Metrik yang sebaiknya dipantau

p95 dan p99 latency endpoint aktivitas/audit log.
Rata-rata dan p95 waktu query berdasarkan template SQL.
Jumlah rows scanned vs rows returned.
Frekuensi full scan atau file sort/sort disk spill, tergantung engine database.
Buffer/cache hit ratio.
CPU, IOPS, dan disk read latency di database.
Durasi COUNT(*) terpisah dari query data utama.
Laju insert/update pada tabel aktivitas setelah penambahan index.

Jika Anda punya observability yang memadai, kelompokkan query berdasarkan bentuknya, bukan berdasarkan nilai parameternya. Dari situ biasanya terlihat jelas query mana yang dominan: daftar event per repo, daftar event per user, atau filter per aksi.

Mengapa OFFSET Besar dan COUNT Mahal

Masalah OFFSET

OFFSET bukan berarti lompat langsung ke baris ke-N. Pada banyak engine database, optimizer tetap harus menelusuri atau memindai sejumlah baris sebelum mencapai titik offset. Semakin besar offset, semakin mahal biayanya.

Contoh query sebelum optimasi:

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 20000;

Walaupun ada index di repo_id atau created_at, optimizer sering tetap harus membaca banyak entri untuk melewati 20.000 hasil pertama. Pada halaman akhir, biaya ini menjadi dominan.

Masalah COUNT

COUNT(*) untuk kebutuhan total halaman juga sering mahal:

SELECT COUNT(*)
FROM repo_activity
WHERE repo_id = 42
  AND action = 'push';

Pada dataset besar, count yang tampak ringan bisa menjadi query terpisah yang mahal, apalagi jika filter berubah-ubah. Beberapa engine bisa mengoptimalkan sebagian kasus, tetapi secara umum Anda tidak boleh berasumsi bahwa count akan selalu murah.

Dalam UI aktivitas repo, pertanyaan yang lebih penting biasanya bukan "ada berapa total halaman?" melainkan "apakah ada halaman berikutnya?". Pergeseran kebutuhan UX ini sering membuka jalan ke keyset pagination dan menghilangkan kebutuhan count sinkron pada setiap request.

Desain Index Komposit yang Tepat

Optimasi query aktivitas repo hampir selalu bergantung pada kesesuaian urutan kolom index dengan pola filter dan sorting. Menambahkan index tunggal di setiap kolom sering tidak cukup.

Prinsip dasar index komposit

Untuk query seperti:

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang lebih cocok biasanya:

CREATE INDEX idx_activity_repo_created_id
ON repo_activity (repo_id, created_at DESC, id DESC);

Mengapa urutan ini membantu:

repo_id diletakkan di depan karena digunakan sebagai filter kesetaraan.
created_at dan id mengikuti pola pengurutan.
id sebagai tie-breaker penting jika banyak event memiliki timestamp sama, agar urutan stabil dan pagination konsisten.

Untuk filter yang sering memakai kombinasi repo + action:

CREATE INDEX idx_activity_repo_action_created_id
ON repo_activity (repo_id, action, created_at DESC, id DESC);

Untuk tampilan aktivitas per user:

CREATE INDEX idx_activity_user_created_id
ON repo_activity (user_id, created_at DESC, id DESC);

Jangan langsung membuat semua kombinasi yang mungkin. Pilih berdasarkan query yang benar-benar dominan. Terlalu banyak index akan meningkatkan biaya tulis dan penggunaan storage.

Kapan index komposit lebih penting daripada banyak index tunggal

Misalnya Anda hanya punya index terpisah:

CREATE INDEX idx_activity_repo_id ON repo_activity (repo_id);
CREATE INDEX idx_activity_created_at ON repo_activity (created_at DESC);

Ini belum tentu cukup. Database mungkin dapat memanfaatkan salah satu index untuk filter atau sorting, tetapi belum tentu keduanya sekaligus dengan efisien. Pada query aktivitas repo, targetnya adalah menghindari scan besar dan sort tambahan. Index komposit yang mengikuti pola WHERE + ORDER BY biasanya jauh lebih efektif.

Kapan covering index membantu

Covering index berguna jika seluruh kolom yang dibutuhkan query tersedia dari index, sehingga database tidak perlu bolak-balik membaca baris utama (heap/table lookup) untuk setiap hasil.

Contohnya, jika UI list hanya menampilkan:

id
repo_id
user_id
action
created_at

Maka index seperti ini dapat membantu, tergantung kemampuan engine database:

CREATE INDEX idx_activity_repo_created_id_cover
ON repo_activity (repo_id, created_at DESC, id DESC, user_id, action);

Atau, pada database yang mendukung kolom included, pendekatan yang lebih hemat bisa dipakai. Namun karena dukungan ini berbeda antar engine, prinsip umumnya tetap sama: masukkan kolom output yang sering diambil jika biaya lookup table menjadi bottleneck yang nyata.

Catatan: covering index bukan selalu pilihan terbaik. Index menjadi lebih besar, cache pressure meningkat, dan write amplification ikut naik. Gunakan hanya untuk query yang benar-benar panas dan kolom hasilnya terbatas.

Trade-off: write amplification dan ukuran index

Setiap index tambahan berarti:

Insert event baru harus menulis ke lebih banyak struktur index.
Storage bertambah.
Vacuum, maintenance, atau rebalancing index bisa lebih mahal.
Cache database terbagi ke lebih banyak index.

Pada tabel aktivitas yang append-heavy, ini trade-off penting. Karena itu, desain index sebaiknya dimulai dari top query patterns, bukan dari semua filter yang mungkin dibutuhkan suatu hari nanti.

Contoh Query Sebelum dan Sesudah Optimasi

Sebelum: OFFSET pagination dan count sinkron

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = 42
  AND action = 'push'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

SELECT COUNT(*)
FROM repo_activity
WHERE repo_id = 42
  AND action = 'push';

Masalah utama pendekatan ini:

Offset besar membuat scan bertambah linear terhadap halaman.
Count menambah satu query mahal lagi.
Performa memburuk pada data lama dan filter populer.

Sesudah: keyset pagination

Dengan keyset pagination, request berikutnya tidak lagi mengatakan "lewati 5000 baris", tetapi "ambil data setelah baris terakhir yang saya lihat".

Halaman pertama:

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = 42
  AND action = 'push'
ORDER BY created_at DESC, id DESC
LIMIT 51;

Ambil 51 baris untuk mengetahui apakah ada halaman berikutnya. Kirim 50 ke klien, simpan baris ke-51 sebagai indikator has_next.

Halaman berikutnya dengan cursor berdasarkan pasangan (created_at, id) terakhir:

SELECT id, repo_id, user_id, action, created_at
FROM repo_activity
WHERE repo_id = 42
  AND action = 'push'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 51;

Index yang mendukung query ini:

CREATE INDEX idx_activity_repo_action_created_id
ON repo_activity (repo_id, action, created_at DESC, id DESC);

Mengapa ini lebih cepat:

Database dapat langsung melanjutkan penelusuran dari titik cursor.
Tidak perlu membuang ribuan hasil hanya untuk mencapai halaman berikutnya.
Biaya query cenderung stabil antar halaman.

Kenapa perlu tie-breaker selain timestamp

Jika hanya memakai created_at sebagai cursor, event yang memiliki timestamp sama bisa terduplikasi atau terlewat saat berpindah halaman. Karena itu gunakan pasangan yang unik dan terurut, misalnya (created_at, id).

Cara Membaca EXPLAIN untuk Kasus Ini

Setelah membuat index atau mengubah query, jangan berhenti di asumsi. Validasi dengan EXPLAIN atau fitur setara pada database Anda.

Yang ingin dilihat

Query memakai index yang sesuai dengan filter dan urutan.
Jumlah baris yang diperkirakan/di-scan turun signifikan.
Tidak ada sort terpisah yang mahal jika index sudah mendukung ORDER BY.
Untuk query list sederhana, idealnya pengambilan baris berhenti cepat setelah LIMIT terpenuhi.

Tanda query belum optimal

Full table scan atau full index scan pada tabel besar.
Sort eksplisit setelah filtering, padahal seharusnya urutan bisa mengikuti index.
Rows examined jauh lebih besar daripada rows returned.
Optimizer memilih index yang salah karena statistik usang atau selectivity rendah.

Contoh interpretasi praktis:

Jika query WHERE repo_id = ? ORDER BY created_at DESC LIMIT 50 masih menunjukkan sort besar, kemungkinan index belum menyatukan kolom filter dan sort dalam urutan yang tepat.
Jika query dengan action masih membaca terlalu banyak baris, mungkin action perlu masuk ke index komposit untuk pola query tersebut.
Jika count masih mahal, pertimbangkan apakah count benar-benar harus real-time.

Tips debugging: uji dengan parameter yang merepresentasikan kasus terburuk, misalnya repo paling aktif atau action paling sering. Query yang tampak cepat pada repo kecil belum tentu mewakili produksi.

Migrasi dari OFFSET ke Keyset Pagination

Migrasi terbaik biasanya dilakukan bertahap agar API dan UI tetap kompatibel.

1. Tentukan urutan yang stabil

Gunakan urutan deterministik, misalnya:

ORDER BY created_at DESC, id DESC

Pastikan pasangan ini konsisten di semua query list dan semua cursor.

2. Tambahkan index yang sesuai sebelum mengubah aplikasi

Buat index komposit yang cocok dengan pola query target. Lakukan dengan metode pembuatan index yang meminimalkan blocking sesuai kemampuan database Anda.

3. Ubah kontrak API secara kompatibel

Jika endpoint lama memakai page dan per_page, Anda bisa:

tetap mendukung offset untuk sementara,
menambah parameter cursor,
mengembalikan next_cursor pada respons baru.

Contoh bentuk respons:

{
  "items": [
    {"id": 9012, "action": "push", "created_at": "2026-07-10T08:15:00Z"}
  ],
  "next_cursor": "2026-07-10T08:15:00Z_9012",
  "has_next": true
}

Cursor sebaiknya dienkode agar klien tidak bergantung pada format internal. Format bisa berupa nilai gabungan yang ditandatangani atau dienkode secara aman.

4. Hindari ketergantungan pada total halaman

UI yang sebelumnya menampilkan "halaman 1 dari 325" perlu disesuaikan. Pada keyset pagination, pola yang lebih natural adalah:

Tombol Next dan Previous.
Penanda has_next.
Opsional: hitung total secara asinkron atau cached jika benar-benar dibutuhkan.

5. Siapkan backward compatibility

Untuk integrasi lama, pertahankan endpoint offset sampai klien berpindah. Namun untuk data besar, arahkan UI utama ke cursor-based pagination lebih dulu karena dampaknya paling terasa di pengalaman pengguna.

Alternatif untuk COUNT yang Mahal

Jika produk masih membutuhkan total hasil, jangan otomatis menjalankan COUNT(*) pada setiap request.

Pilihan yang lebih aman

Tidak menampilkan total sama sekali untuk halaman aktivitas yang fokus pada navigasi terbaru-ke-lama.
Count asinkron di background untuk filter yang sering dipakai.
Cached count dengan toleransi stale yang jelas.
Approximate count jika kebutuhan bisnis menerima estimasi, bukan angka presisi.

Pilih pendekatan berdasarkan kebutuhan UX, bukan kebiasaan lama dari pagination berbasis halaman.

Checklist Implementasi Aman Tanpa Downtime

Identifikasi query dominan dari slow query log, APM, atau database insights.
Kelompokkan pola filter: per repo, per user, per action, dan kombinasi yang paling sering.
Rancang index komposit minimum berdasarkan pola WHERE + ORDER BY, bukan semua kemungkinan.
Buat index secara online/non-blocking sesuai fitur database yang digunakan.
Verifikasi dengan EXPLAIN pada parameter yang merepresentasikan beban terburuk.
Tambahkan keyset pagination di endpoint baru atau parameter baru.
Gunakan cursor berbasis urutan stabil, misalnya created_at + id.
Ambil satu baris ekstra untuk menentukan has_next tanpa count sinkron.
Uji konsistensi data: tidak ada duplikasi atau item hilang antar halaman.
Monitor write overhead setelah index baru dibuat, terutama pada tabel append-heavy.
Roll out bertahap ke sebagian trafik atau satu endpoint dulu.
Hapus index yang tidak terpakai setelah yakin query baru stabil.

Kesalahan Umum

Membuat index hanya di created_at lalu berharap semua filter + sorting otomatis cepat.
Menggunakan keyset pagination tanpa tie-breaker unik, sehingga data lompat atau duplikat.
Menambah terlalu banyak index komposit untuk semua variasi filter tanpa data penggunaan nyata.
Tetap memaksa COUNT(*) real-time pada setiap request walau UI hanya butuh has_next.
Tidak menguji query pada repo paling aktif atau rentang waktu terpadat.
Menganggap EXPLAIN yang terlihat "memakai index" pasti sudah optimal, padahal rows scanned masih tinggi.

Penutup

Optimasi query aktivitas repo hampir selalu kembali ke dua hal: index komposit yang mengikuti pola query dan pagination yang tidak bergantung pada OFFSET besar. Untuk tabel aktivitas atau audit log yang terus tumbuh, keyset pagination biasanya memberi perbaikan paling konsisten karena biaya query tetap stabil seiring navigasi ke data lama.

Mulailah dari pola query yang paling sering dipakai, validasi dengan EXPLAIN, lalu migrasikan UI dan API secara bertahap. Jika dilakukan dengan hati-hati, Anda bisa mengurangi latency halaman aktivitas secara signifikan tanpa downtime dan tanpa menambah kompleksitas yang tidak perlu.

Quick Search

shopping_cart Keranjang