Optimasi SQL untuk Board Kolaborasi: Filter, Sort, dan Pagination

Pada aplikasi board kolaborasi mirip Jira ringan untuk human-AI collaboration seperti konteks yang bisa ditemui pada proyek seperti Paca, masalah performa biasanya muncul bukan karena satu query yang sangat rumit, melainkan karena pola akses yang tampak sederhana tetapi dipanggil terus-menerus: daftar issue dengan filter status, assignee, project, diurutkan berdasarkan updated_at, lalu dipaginasi.

Jika jumlah issue masih ratusan, query semacam ini terasa aman. Tetapi ketika tumbuh menjadi puluhan ribu atau lebih, kombinasi OFFSET besar, JOIN berulang, index yang salah urutan, dan COUNT(*) mahal akan membuat feed board melambat. Solusinya bukan sekadar menambah index sebanyak mungkin, melainkan menyelaraskan desain query dengan pola akses aktual aplikasi.

Masalah nyata pada feed board

Anggap kita punya tabel utama issues dan relasi ke projects serta users. Tampilan board biasanya meminta data seperti:

issue dalam project tertentu,
opsional difilter berdasarkan status,
opsional difilter berdasarkan assignee,
diurutkan berdasarkan updated_at DESC,
ditampilkan per halaman.

Contoh query yang sering muncul pada implementasi awal:

SELECT i.id, i.title, i.status, i.updated_at, i.assignee_id, p.name AS project_name, u.name AS assignee_name
FROM issues i
JOIN projects p ON p.id = i.project_id
LEFT JOIN users u ON u.id = i.assignee_id
WHERE i.project_id = 42
  AND i.status IN ('open', 'in_progress')
ORDER BY i.updated_at DESC
LIMIT 20 OFFSET 10000;

Query ini terlihat wajar, tetapi ada beberapa risiko:

OFFSET 10000 memaksa database melewati banyak baris sebelum mengambil 20 data.
Jika index tidak cocok dengan pola WHERE + ORDER BY, database akan melakukan sort mahal.
JOIN ke tabel lain dilakukan untuk setiap request daftar, padahal belum tentu semua kolom dibutuhkan.
Di sisi aplikasi, bisa muncul query tambahan per issue untuk memuat komentar, label, atau profil assignee. Di sinilah N+1 sering terjadi.

Mulai dari pola query, bukan dari menambah index acak

Optimasi SQL untuk board kolaborasi paling efektif dimulai dari pertanyaan: query apa yang paling sering dipanggil, dengan filter apa, urutan apa, dan ukuran halaman berapa. Index yang benar sangat bergantung pada kombinasi itu.

Untuk kasus umum feed board, pola akses utama biasanya:

WHERE project_id = ?
AND status IN (...)
AND assignee_id = ? atau tanpa filter assignee
ORDER BY updated_at DESC, id DESC

Tambahan id DESC penting sebagai tie-breaker. Jika banyak row punya updated_at yang sama, urutan hasil bisa tidak stabil tanpa kolom kedua. Ini juga membantu keyset pagination nanti.

Contoh schema sederhana

CREATE TABLE issues (
  id BIGINT PRIMARY KEY,
  project_id BIGINT NOT NULL,
  assignee_id BIGINT NULL,
  status VARCHAR(32) NOT NULL,
  title VARCHAR(255) NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Contoh kebutuhan index yang lebih masuk akal:

CREATE INDEX idx_issues_project_status_updated_id
  ON issues (project_id, status, updated_at DESC, id DESC);

CREATE INDEX idx_issues_project_assignee_updated_id
  ON issues (project_id, assignee_id, updated_at DESC, id DESC);

Apakah perlu satu index besar seperti (project_id, status, assignee_id, updated_at, id)? Kadang iya, tetapi jangan otomatis menganggap itu paling benar. Index terlalu lebar meningkatkan biaya tulis dan belum tentu dipakai optimal jika pola query bervariasi. Pilih berdasarkan query dominan di produksi.

Sebelum dan sesudah: memperbaiki query daftar issue

Query awal yang umum bermasalah

SELECT i.id, i.title, i.status, i.updated_at, p.name, u.name
FROM issues i
JOIN projects p ON p.id = i.project_id
LEFT JOIN users u ON u.id = i.assignee_id
WHERE i.project_id = 42
  AND i.status = 'open'
ORDER BY i.updated_at DESC
LIMIT 20 OFFSET 10000;

Masalah utamanya:

Offset besar tetap mahal meskipun ada index.
JOIN dilakukan sebelum benar-benar perlu, tergantung optimizer dan rencana eksekusi.
Kolom yang dipilih cukup banyak untuk query halaman daftar.

Langkah 1: ambil ID dulu, detail belakangan

Untuk feed board, sering kali lebih efisien mengambil baris inti dari issues terlebih dahulu, baru memuat detail tambahan untuk kumpulan ID kecil hasil halaman itu.

SELECT i.id, i.title, i.status, i.assignee_id, i.updated_at
FROM issues i
WHERE i.project_id = 42
  AND i.status = 'open'
ORDER BY i.updated_at DESC, i.id DESC
LIMIT 20;

Lalu, jika nama project atau assignee dibutuhkan, ambil dengan query kedua berbasis himpunan ID kecil, bukan per-row.

SELECT u.id, u.name
FROM users u
WHERE u.id IN (101, 102, 205, 333);

Ini sering lebih baik daripada N+1 query seperti:

SELECT * FROM users WHERE id = ?;
-- dipanggil berulang untuk setiap issue

Jika framework ORM Anda mendukung eager loading, gunakan itu. Tujuannya bukan menghindari JOIN sepenuhnya, melainkan menghindari query tambahan per item.

Langkah 2: gunakan urutan yang stabil

Ganti:

ORDER BY updated_at DESC

menjadi:

ORDER BY updated_at DESC, id DESC

Ini penting untuk pagination yang konsisten. Tanpa urutan stabil, item bisa lompat atau duplikat antar halaman saat ada banyak row dengan timestamp sama.

Bottleneck OFFSET besar dan solusi keyset pagination

Mengapa OFFSET lambat

LIMIT 20 OFFSET 10000 tidak berarti database langsung melompat ke baris ke-10001 dengan biaya nol. Dalam banyak kasus, database tetap harus memindai atau menavigasi sejumlah besar entri index untuk melewati 10000 data pertama. Semakin besar offset, semakin mahal latensinya.

Masalah ini sangat terasa pada board yang aktif, karena issue yang baru diupdate terus berpindah ke atas. Halaman ke-500 dengan offset besar bukan hanya lambat, tetapi juga tidak stabil saat data berubah di tengah navigasi.

Keyset pagination untuk feed yang diurutkan

Jika urutan utama adalah updated_at DESC, id DESC, gunakan cursor dari item terakhir pada halaman sebelumnya.

Halaman pertama:

SELECT id, title, status, assignee_id, updated_at
FROM issues
WHERE project_id = 42
  AND status IN ('open', 'in_progress')
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Misalnya item terakhir memiliki:

updated_at = '2026-08-08 10:30:00'
id = 98765

Halaman berikutnya:

SELECT id, title, status, assignee_id, updated_at
FROM issues
WHERE project_id = 42
  AND status IN ('open', 'in_progress')
  AND (
    updated_at < '2026-08-08 10:30:00'
    OR (updated_at = '2026-08-08 10:30:00' AND id < 98765)
  )
ORDER BY updated_at DESC, id DESC
LIMIT 20;

Keuntungannya:

database tidak perlu membuang ribuan row karena offset,
latensi lebih stabil untuk halaman dalam,
cocok untuk feed yang terus berubah.

Kekurangannya:

tidak cocok jika UI butuh lompat langsung ke halaman 500 secara presisi,
implementasi cursor sedikit lebih kompleks di API dan frontend,
perlu urutan yang deterministik.

Untuk board kolaborasi, trade-off ini biasanya layak, karena pengguna lebih sering melakukan scroll maju daripada lompat ke nomor halaman arbitrer.

Composite index: urutan kolom menentukan hasil

Kesalahan umum: kolom index benar, urutannya salah

Banyak tim membuat index seperti ini:

CREATE INDEX idx_issues_updated_status_project
  ON issues (updated_at, status, project_id);

Sekilas semua kolom yang dibutuhkan ada. Masalahnya, urutan ini sering tidak cocok dengan predicate yang paling selektif dan pola sorting. Jika query selalu memfilter project_id lebih dulu, meletakkan updated_at di depan dapat membuat database sulit memanfaatkan index secara efektif untuk filter utama.

Untuk query seperti:

WHERE project_id = ? AND status = ?
ORDER BY updated_at DESC, id DESC

index yang lebih cocok biasanya:

CREATE INDEX idx_issues_project_status_updated_id
  ON issues (project_id, status, updated_at DESC, id DESC);

Mengapa urutan ini membantu:

project_id dipakai dulu untuk mempersempit ruang pencarian.
status mempersempit lagi dalam project itu.
updated_at, id menyediakan urutan hasil sehingga sort tambahan bisa berkurang atau hilang.

Bagaimana jika filter assignee opsional?

Di sinilah tidak ada satu jawaban universal. Jika mayoritas query board adalah per project + status, buat index khusus untuk pola itu. Jika banyak juga query per project + assignee, pertimbangkan index kedua.

CREATE INDEX idx_issues_project_assignee_updated_id
  ON issues (project_id, assignee_id, updated_at DESC, id DESC);

Jangan buru-buru membuat semua kombinasi yang mungkin, karena tiap index punya biaya:

INSERT dan UPDATE menjadi lebih mahal,
ukuran storage bertambah,
vacuum/maintenance atau background cleanup bisa makin berat, tergantung database.

Prinsip praktis: buat index untuk pola query produksi yang benar-benar dominan, bukan untuk semua kemungkinan filter di masa depan.

COUNT(*) mahal: kapan perlu dihitung, kapan tidak

Pada pagination klasik, API sering mengembalikan:

data halaman saat ini,
total jumlah row,
total halaman.

Masalahnya, untuk filter yang kompleks dan dataset besar, COUNT(*) bisa mahal. Contoh:

SELECT COUNT(*)
FROM issues
WHERE project_id = 42
  AND status IN ('open', 'in_progress')
  AND assignee_id = 101;

Walaupun query utama daftar sudah cepat, total count dapat menjadi bottleneck sendiri.

Pendekatan praktis

Jika UI hanya butuh tombol Next/Previous, tidak perlu total count real-time.
Jika total count penting, pertimbangkan hitung terpisah, cache singkat, atau pre-aggregation tergantung kebutuhan konsistensi.
Jika butuh indikator sederhana, cukup ambil LIMIT page_size + 1 untuk mengetahui apakah masih ada halaman berikutnya.

Contoh pola has_next_page:

SELECT id, title, status, updated_at
FROM issues
WHERE project_id = 42
  AND status = 'open'
ORDER BY updated_at DESC, id DESC
LIMIT 21;

Jika hasil 21 row dan ukuran halaman 20, berarti masih ada halaman berikutnya. Ini jauh lebih murah daripada total count pada banyak skenario.

Menghindari N+1 query pada board

N+1 tidak selalu terlihat di query utama. Gejalanya justru sering muncul setelah daftar issue didapat, lalu aplikasi memuat relasi satu per satu:

nama assignee,
jumlah komentar,
label,
status AI review,
metadata project.

Contoh pola buruk:

Ambil 20 issue.
Untuk tiap issue, query user.
Untuk tiap issue, query comment count.
Untuk tiap issue, query labels.

Hasilnya bisa menjadi puluhan query per request.

Pola yang lebih sehat

Gunakan eager loading atau batched fetch untuk relasi sederhana.
Untuk agregasi seperti jumlah komentar, ambil sekaligus dengan GROUP BY untuk himpunan ID halaman saat ini.
Jangan memuat kolom atau relasi yang tidak benar-benar tampil di board.

Contoh hitung komentar untuk 20 issue sekaligus:

SELECT issue_id, COUNT(*) AS comment_count
FROM comments
WHERE issue_id IN (....20 id hasil halaman...)
GROUP BY issue_id;

Ini jauh lebih baik daripada:

SELECT COUNT(*) FROM comments WHERE issue_id = ?;
-- dipanggil 20 kali

Cara membaca EXPLAIN tanpa menebak-nebak

Jangan mengoptimasi query hanya berdasarkan intuisi. Jalankan EXPLAIN atau fitur ekuivalennya di database yang Anda pakai untuk melihat rencana eksekusi.

Hal yang perlu dicari

Apakah index yang diharapkan benar-benar dipakai?
Apakah ada sort tambahan? Jika ya, kemungkinan ORDER BY belum didukung baik oleh index.
Apakah jumlah row yang dipindai jauh lebih besar daripada row hasil?
Apakah JOIN dimulai dari tabel yang tepat?
Apakah ada full scan pada tabel besar?

Contoh interpretasi praktis

Jika query:

SELECT id, title, status, updated_at
FROM issues
WHERE project_id = 42
  AND status = 'open'
ORDER BY updated_at DESC, id DESC
LIMIT 20;

tetapi hasil EXPLAIN menunjukkan database membaca sangat banyak row lalu melakukan sort, ada beberapa kemungkinan:

index belum mencakup urutan project_id, status, updated_at, id,
predicate tidak cukup cocok dengan susunan index,
optimizer memilih index lain yang secara statistik dianggap lebih murah,
statistik database sudah tidak akurat.

Jika setelah menambah index yang relevan rencana masih buruk, periksa:

apakah statistik perlu diperbarui,
apakah query memakai ekspresi yang menghalangi pemakaian index,
apakah tipe data atau kolasi menyebabkan perilaku tak terduga.

Debugging performa SQL yang baik selalu berbasis dua hal: query aktual dari produksi dan rencana eksekusinya. Tanpa itu, diskusi index sering berubah menjadi spekulasi.

Trade-off: read lebih cepat, write lebih mahal

Setiap index tambahan mempercepat sebagian query baca, tetapi ada biaya yang harus dibayar:

INSERT lebih berat karena index harus diperbarui.
UPDATE pada kolom terindeks seperti status, assignee_id, atau updated_at bisa lebih mahal.
Storage bertambah, terutama pada tabel besar.
Maintenance meningkat untuk backup, reindex, vacuum, atau operasi housekeeping lain tergantung engine.

Pada board kolaborasi aktif, updated_at sering berubah karena komentar baru, assignment, status change, atau sinyal dari agen AI. Artinya index yang melibatkan updated_at memang membantu feed, tetapi juga menambah write amplification.

Karena itu, jangan membuat terlalu banyak variasi index untuk semua kombinasi filter. Lebih baik punya beberapa index yang sangat sering dipakai daripada banyak index yang jarang disentuh tetapi memperlambat alur tulis.

Strategi implementasi bertahap di aplikasi board

1. Profilkan endpoint daftar issue

Catat endpoint paling sering dipanggil, bentuk query final dari ORM, durasi, dan jumlah row hasil. Pisahkan waktu database dari waktu serialisasi API atau render frontend.

2. Identifikasi pola filter dominan

Misalnya:

semua issue per project,
issue open per project,
issue assignee tertentu per project.

Dari sini baru pilih composite index yang relevan.

3. Ubah pagination untuk feed aktif

Jika saat ini masih mengandalkan OFFSET untuk halaman dalam, migrasikan ke keyset pagination minimal pada endpoint infinite scroll atau board feed utama.

4. Pangkas payload daftar

Jangan ambil deskripsi panjang, metadata besar, atau relasi berat pada query list jika tidak tampil di layar daftar.

5. Hilangkan N+1

Audit jumlah query per request. Satu halaman board seharusnya tidak memicu puluhan query kecil jika data bisa di-batch.

6. Evaluasi kebutuhan total count

Jika hanya dipakai kosmetik UI, pertimbangkan menghapus count real-time atau menggantinya dengan pendekatan yang lebih ringan.

Checklist audit query produksi untuk feed board

Gunakan checklist ini agar tim bisa memperbaiki performa tanpa menebak-nebak:

Apakah query daftar issue menggunakan ORDER BY updated_at DESC, id DESC yang stabil?
Apakah masih ada OFFSET besar pada halaman dalam?
Apakah endpoint board utama cocok dipindah ke keyset pagination?
Apakah composite index mengikuti pola WHERE lalu ORDER BY, bukan sekadar berisi kolom yang sama?
Apakah ada index terpisah untuk pola query dominan seperti project + status atau project + assignee?
Apakah query list hanya mengambil kolom yang benar-benar dibutuhkan?
Apakah ada N+1 query untuk users, comments, labels, atau metadata lain?
Apakah jumlah komentar atau agregasi lain diambil per batch, bukan per issue?
Apakah COUNT(*) benar-benar diperlukan pada setiap request?
Apakah EXPLAIN menunjukkan index yang diharapkan dipakai?
Apakah rencana eksekusi masih melakukan sort atau scan row jauh lebih banyak dari hasil?
Apakah statistik database cukup mutakhir untuk membantu optimizer?
Apakah penambahan index baru sudah dievaluasi dampaknya ke write amplification?
Apakah perubahan diuji pada data yang ukuran dan distribusinya mendekati produksi?

Penutup

Optimasi feed board bukan tentang satu trik ajaib, tetapi tentang menyelaraskan query dengan pola akses nyata. Untuk board kolaborasi dengan issue yang terus bertambah, tiga perbaikan yang paling sering memberi dampak adalah: composite index dengan urutan benar, mengganti OFFSET besar dengan keyset pagination, dan menghilangkan N+1 query.

Mulailah dari query yang benar-benar lambat di produksi, baca EXPLAIN-nya, lalu perbaiki satu bottleneck setiap kali. Dengan pendekatan ini, tim bisa mempercepat filter, sort, dan pagination pada board tanpa menambah kompleksitas yang tidak perlu.

Quick Search

shopping_cart Keranjang