Optimasi Feed Proyek Arsip: Index, COUNT, dan Keyset Pagination

Halaman arsip proyek terlihat sederhana sampai datanya membesar: daftar proyek mati, eksperimen lama, side project yang ditinggalkan, semuanya masuk ke satu feed yang harus tetap cepat dibuka. Masalahnya hampir selalu sama: query daftar makin lambat, OFFSET pagination makin mahal di halaman belakang, COUNT(*) total data ikut membebani, dan filter seperti status, tag, serta sorting berdasarkan created_at atau last_activity_at mulai memicu full scan.

Artikel ini fokus pada optimasi feed proyek arsip dari sisi SQL dan desain indeks. Tujuannya bukan sekadar “menambah index”, tetapi memahami mengapa query lambat, bagaimana membaca EXPLAIN, kapan composite index membantu, kapan covering index layak dipakai, serta kapan Anda sebaiknya beralih dari offset pagination ke keyset pagination.

Konteksnya cocok untuk aplikasi yang punya halaman seperti “graveyard” side-project: daftar proyek yang terus bertambah, bisa difilter, diurutkan, dan ditampilkan sebagai feed publik atau dashboard internal.

Masalah Utama pada Feed Arsip yang Terus Membesar

Skema dasarnya biasanya tampak aman:

projects(id, name, status, created_at, last_activity_at, archived_at, ...)

Lalu kebutuhan produk berkembang:

Tampilkan proyek terbaru atau paling terakhir aktif.
Filter berdasarkan status seperti archived, dead, active.
Filter berdasarkan tag.
Tampilkan total hasil untuk pagination UI.
Dukung halaman ke-2, ke-10, ke-100.

Query yang awalnya cepat bisa melambat karena beberapa alasan:

OFFSET makin besar: database tetap harus melewati banyak baris sebelum mengambil data yang diminta.
COUNT(*) pada hasil filter: menghitung total bisa mahal jika predicate dan join tidak didukung indeks yang tepat.
Sorting tanpa index yang cocok: database harus menyortir banyak baris di memori atau disk.
Join tabel tag: filter many-to-many sering memicu duplikasi baris atau rencana eksekusi yang buruk.
Pertumbuhan data: query yang “masih oke” di 10 ribu baris bisa runtuh di 10 juta baris.

Contoh Query Feed yang Umum dan Titik Lemahnya

Query dasar dengan OFFSET

SELECT id, name, status, created_at, last_activity_at
FROM projects
WHERE status = 'archived'
ORDER BY last_activity_at DESC, id DESC
LIMIT 20 OFFSET 1000;

Secara logika query ini benar. Masalahnya, untuk mengembalikan 20 baris setelah 1000 baris pertama, database biasanya tetap harus menelusuri dan membuang banyak baris. Semakin dalam halaman, semakin mahal biayanya.

Menambahkan COUNT untuk total halaman

SELECT COUNT(*)
FROM projects
WHERE status = 'archived';

Kalau UI menampilkan total hasil atau jumlah halaman, query COUNT(*) hampir selalu ikut dipanggil. Pada dataset besar, total biaya request sering bukan dari query daftar saja, tetapi dari kombinasi query data + query count.

Filter tag yang sering menjadi sumber masalah

SELECT p.id, p.name, p.status, p.created_at, p.last_activity_at
FROM projects p
JOIN project_tags pt ON pt.project_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE p.status = 'archived'
  AND t.slug = 'ai'
ORDER BY p.last_activity_at DESC, p.id DESC
LIMIT 20 OFFSET 1000;

Query ini terlihat normal, tetapi join many-to-many dapat menghasilkan banyak kandidat baris sebelum disaring dan diurutkan. Jika indeks tidak cocok, database bisa memilih rencana mahal: scan besar, sort besar, lalu limit kecil di akhir.

Cara Membaca EXPLAIN untuk Kasus Feed

EXPLAIN membantu melihat bagaimana database berencana menjalankan query. Nama kolom output berbeda antar engine, tetapi prinsip yang perlu Anda cari sama:

Apakah query memakai index atau full scan?
Berapa estimasi baris yang dibaca?
Apakah ada sort terpisah?
Apakah join dilakukan dalam urutan yang masuk akal?
Apakah filter dan order by bisa dipenuhi dari index yang sama?

Sinyal yang biasanya buruk

Scan ke seluruh tabel utama padahal filter selektif.
Estimasi baris sangat besar untuk query yang hanya mengembalikan 20 item.
Sort eksplisit pada hasil besar sebelum LIMIT.
Join tabel pivot/tag di awal sehingga kandidat baris membengkak.
Query count melakukan scan besar yang sama seperti query daftar.

Pertanyaan praktis saat melihat EXPLAIN

Index mana yang dipilih? Apakah cocok dengan WHERE dan ORDER BY?
Kalau query memfilter status dan mengurutkan last_activity_at, apakah index juga mengikuti urutan itu?
Kalau hasil diurutkan DESC dengan tie-breaker id, apakah index mempertimbangkan tie-breaker tersebut?
Apakah jumlah baris yang dibaca jauh lebih besar daripada jumlah baris yang dikembalikan?

Jangan hanya puas karena “query memakai index”. Banyak query tetap lambat walau memakai index yang salah atau kurang selektif.

Memilih Composite Index yang Benar

Untuk feed arsip, index tunggal seperti INDEX(status) dan INDEX(last_activity_at) sering belum cukup. Database sering butuh satu index yang bisa membantu filter sekaligus urutan data.

Kasus 1: filter status, urut berdasarkan last_activity_at

SELECT id, name, status, created_at, last_activity_at
FROM projects
WHERE status = 'archived'
ORDER BY last_activity_at DESC, id DESC
LIMIT 20;

Index yang umumnya relevan:

CREATE INDEX idx_projects_status_last_activity_id
ON projects (status, last_activity_at, id);

Mengapa urutannya seperti itu?

status dipakai sebagai filter kesetaraan.
last_activity_at dipakai untuk urutan utama.
id menjadi tie-breaker agar urutan stabil ketika banyak proyek punya timestamp yang sama.

Tanpa id sebagai tie-breaker, pagination bisa tidak stabil: item bisa terduplikasi atau terlewat saat ada banyak nilai waktu yang sama.

Kasus 2: filter status, urut berdasarkan created_at

CREATE INDEX idx_projects_status_created_id
ON projects (status, created_at, id);

Jika aplikasi mendukung dua mode sort berbeda, sering kali Anda butuh dua composite index terpisah. Satu index jarang ideal untuk dua pola urutan yang berbeda.

Kasus 3: query tanpa filter status

SELECT id, name, created_at
FROM projects
ORDER BY created_at DESC, id DESC
LIMIT 20;

Index yang lebih cocok:

CREATE INDEX idx_projects_created_id
ON projects (created_at, id);

Jangan menganggap index dengan prefix status otomatis cocok untuk semua query yang mengurutkan created_at. Jika query tidak memfilter status, index tersebut bisa kurang efektif.

Aturan praktis memilih urutan kolom

Letakkan kolom filter kesetaraan lebih dulu.
Lanjutkan dengan kolom untuk ORDER BY.
Tambahkan tie-breaker unik seperti id.
Jangan memasukkan terlalu banyak kolom “sekadar jaga-jaga”, karena setiap index menambah biaya write dan storage.

Kapan Covering Index Membantu

Covering index berarti semua kolom yang dibutuhkan query sudah tersedia di index, sehingga database tidak perlu bolak-balik ke tabel utama untuk mengambil kolom tambahan. Ini bisa sangat membantu pada feed yang hanya menampilkan field ringkas.

Contoh query ringan untuk kartu feed

SELECT id, name, status, last_activity_at
FROM projects
WHERE status = 'archived'
ORDER BY last_activity_at DESC, id DESC
LIMIT 20;

Secara teori, index seperti berikut bisa membantu:

CREATE INDEX idx_projects_feed_cover
ON projects (status, last_activity_at, id, name);

Namun ada trade-off penting:

Index menjadi lebih besar.
Write INSERT/UPDATE bisa lebih mahal.
Tidak semua kolom cocok dimasukkan ke index, terutama kolom panjang atau sering berubah.

Karena itu, covering index layak dipertimbangkan bila:

Query feed sangat sering dipanggil.
Kolom yang diambil sedikit dan relatif kecil.
Latency baca lebih penting daripada biaya storage tambahan.

Kalau feed mengambil banyak kolom deskriptif seperti ringkasan panjang, markdown, atau JSON besar, jangan memaksa covering index. Lebih baik ambil daftar ID cepat dulu, lalu ambil detail seperlunya.

Kenapa OFFSET Pagination Makin Mahal

Offset pagination nyaman untuk UI karena mudah menampilkan halaman 1, 2, 3, dan total halaman. Tetapi biayanya naik seiring pertumbuhan data.

SELECT id, name, last_activity_at
FROM projects
WHERE status = 'archived'
ORDER BY last_activity_at DESC, id DESC
LIMIT 20 OFFSET 20000;

Masalah utamanya bukan pada 20 baris yang diambil, tetapi pada 20.000 baris yang harus dilewati. Walaupun index membantu, database tetap harus menelusuri entri sebelum sampai ke posisi target.

Kapan OFFSET masih masuk akal

Dataset kecil atau sedang.
Halaman belakang jarang diakses.
Butuh nomor halaman absolut untuk admin UI.
Sederhana dan cepat diimplementasikan.

Kapan OFFSET mulai jadi masalah

Feed publik dengan trafik tinggi.
Data terus bertambah setiap hari.
User sering scroll dalam.
Query juga memakai join/filter yang berat.

Keyset Pagination untuk Feed yang Terus Tumbuh

Kalau pola akses utama adalah “ambil 20 item berikutnya” alih-alih “lompat ke halaman 137”, keyset pagination biasanya lebih efisien dan stabil.

Contoh keyset berdasarkan last_activity_at, id

Halaman pertama:

SELECT id, name, status, last_activity_at
FROM projects
WHERE status = 'archived'
ORDER BY last_activity_at DESC, id DESC
LIMIT 20;

Halaman berikutnya menggunakan cursor dari item terakhir halaman sebelumnya, misalnya (last_activity_at = '2025-07-01 10:30:00', id = 481):

SELECT id, name, status, last_activity_at
FROM projects
WHERE status = 'archived'
  AND (
    last_activity_at < '2025-07-01 10:30:00'
    OR (last_activity_at = '2025-07-01 10:30:00' AND id < 481)
  )
ORDER BY last_activity_at DESC, id DESC
LIMIT 20;

Dengan index:

CREATE INDEX idx_projects_status_last_activity_id
ON projects (status, last_activity_at, id);

Database tidak perlu menghitung atau melewati puluhan ribu baris seperti pada OFFSET. Ia cukup melanjutkan dari titik cursor.

Kelebihan keyset pagination

Skala lebih baik pada data besar.
Latensi lebih stabil untuk halaman dalam.
Lebih aman terhadap perubahan data di tengah pagination dibanding offset.

Kekurangan keyset pagination

Tidak cocok untuk “lompat ke halaman N” secara langsung.
Implementasi UI dan API sedikit lebih kompleks.
Butuh urutan yang stabil dan deterministic.

Kapan memilih keyset

Pilih keyset jika feed proyek arsip dipakai seperti timeline atau infinite scroll. Jika aplikasi benar-benar butuh nomor halaman absolut, Anda bisa memakai pendekatan hibrida: offset untuk admin/internal dan keyset untuk feed publik.

Strategi COUNT(*) agar Tidak Menjadi Bottleneck

COUNT(*) bukan selalu buruk, tetapi pada tabel besar dengan filter/join tertentu, ia bisa lebih mahal daripada query data utama.

Kasus sederhana

SELECT COUNT(*)
FROM projects
WHERE status = 'archived';

Dengan index yang tepat pada status atau composite index yang diawali status, query ini bisa lebih ringan. Namun jika filter melibatkan tag atau kondisi kompleks, count bisa tetap mahal.

Pilihan strategi

Hitung persis hanya saat perlu: jangan selalu tampilkan total halaman jika UI tidak membutuhkannya.
Pisahkan query count dari query feed: jangan menganggap keduanya harus berjalan dalam satu jalur request yang sama.
Gunakan count tertunda atau async untuk dashboard non-kritis.
Cache count jika filter terbatas dan perubahan data tidak terlalu sering.
Simpan counter teragregasi untuk kasus tertentu, misalnya total proyek per status, jika kebutuhan bisnis stabil.

Catatan penting untuk filter tag

Count pada join many-to-many sering memerlukan COUNT(DISTINCT p.id) agar hasil tidak salah karena duplikasi baris hasil join.

SELECT COUNT(DISTINCT p.id)
FROM projects p
JOIN project_tags pt ON pt.project_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE p.status = 'archived'
  AND t.slug = 'ai';

Query seperti ini bisa jauh lebih mahal daripada count sederhana. Jika fitur tag sangat populer, optimasi index pada tabel pivot menjadi penting.

Optimasi Filter Status dan Tag

Index untuk tabel pivot tag

Pada relasi many-to-many, tabel pivot seperti project_tags(project_id, tag_id) atau project_tags(tag_id, project_id) sangat menentukan performa.

Untuk query yang berawal dari tag lalu mencari proyek, index yang biasanya berguna adalah:

CREATE INDEX idx_project_tags_tag_project
ON project_tags (tag_id, project_id);

Jika ada query yang sering berawal dari proyek lalu mengambil semua tag, index kebalikannya juga bisa relevan:

CREATE INDEX idx_project_tags_project_tag
ON project_tags (project_id, tag_id);

Anda tidak selalu butuh keduanya. Lihat pola query aktual di aplikasi.

Hindari join berlebihan jika filter bisa dipecah

Dalam beberapa kasus, lebih efisien mencari kandidat proyek lewat subquery atau EXISTS daripada join penuh yang memperbanyak baris.

SELECT p.id, p.name, p.status, p.last_activity_at
FROM projects p
WHERE p.status = 'archived'
  AND EXISTS (
    SELECT 1
    FROM project_tags pt
    JOIN tags t ON t.id = pt.tag_id
    WHERE pt.project_id = p.id
      AND t.slug = 'ai'
  )
ORDER BY p.last_activity_at DESC, p.id DESC
LIMIT 20;

Pendekatan ini sering membantu menjaga satu baris proyek tetap satu baris, meskipun efektivitas akhirnya tetap bergantung pada engine dan index yang tersedia.

Perhatikan selectivity

Filter seperti status = 'archived' kadang kurang selektif jika mayoritas data memang sudah diarsipkan. Dalam kondisi seperti ini, index yang hanya diawali status mungkin tidak cukup kuat. Gabungan dengan kolom sort dan pola akses aktual menjadi lebih penting.

Menghindari Full Scan dan Sort Mahal

Berikut prinsip yang sering paling berdampak:

Sesuaikan index dengan query nyata, bukan dengan tebakan.
Samakan urutan index dengan filter dan sort.
Gunakan tie-breaker seperti id pada sort berbasis timestamp.
Kurangi kolom yang di-select pada feed list.
Hindari fungsi pada kolom terindeks di klausa filter.

Anti-pattern umum

WHERE DATE(created_at) = '2025-07-01'

Fungsi pada kolom sering membuat index sulit dipakai optimal. Lebih baik gunakan rentang:

WHERE created_at >= '2025-07-01 00:00:00'
  AND created_at < '2025-07-02 00:00:00'

Anti-pattern lain: ORDER BY tidak konsisten dengan index

WHERE status = 'archived'
ORDER BY COALESCE(last_activity_at, created_at) DESC

Ekspresi seperti ini nyaman di level aplikasi, tetapi sering mempersulit pemanfaatan index biasa. Jika pola ini penting, pertimbangkan kolom materialized atau kolom turunan yang disimpan, misalnya sort_activity_at, lalu index kolom tersebut.

Dampak Pertumbuhan Data yang Sering Diremehkan

Salah satu kesalahan paling umum adalah menguji query pada dataset kecil lalu menganggap desainnya aman. Feed arsip justru cenderung tumbuh terus tanpa banyak penghapusan.

Ketika data bertambah, efek berikut ikut membesar:

Biaya scan dan offset makin tinggi.
Ukuran index membesar dan cache hit ratio menurun.
Update pada kolom yang ikut diindex, seperti last_activity_at, makin mahal.
Query count dan query dengan join makin sering memicu latensi puncak.

Karena itu, optimasi feed sebaiknya dievaluasi berdasarkan tren pertumbuhan, bukan kondisi data saat ini saja.

Checklist Diagnosis Query Feed Lambat

Ambil satu query nyata dari produksi, bukan contoh sintetis.
Jalankan EXPLAIN dan lihat apakah terjadi full scan atau sort besar.
Cek apakah ada OFFSET besar.
Bandingkan biaya query data dan query COUNT(*).
Pastikan sort memakai tie-breaker yang stabil, misalnya id.
Evaluasi apakah composite index sudah sesuai urutan WHERE + ORDER BY.
Jika ada filter tag, audit index pada tabel pivot dan cek apakah join menghasilkan duplikasi.
Kurangi kolom select untuk feed list.
Uji keyset pagination untuk endpoint yang di-scroll dalam.
Pantau setelah perubahan: latency, rows examined, dan load write.

Langkah Rollout Index yang Aman

Menambah index pada tabel besar bukan operasi tanpa risiko. Walau banyak database modern mendukung pembuatan index dengan dampak terbatas, Anda tetap perlu rollout yang hati-hati.

Urutan yang disarankan

Identifikasi query prioritas: pilih endpoint feed yang paling sering lambat.
Rancang index minimal: jangan menambah banyak index sekaligus tanpa hipotesis jelas.
Uji di staging dengan data realistis: dataset kecil sering menipu planner.
Bandingkan EXPLAIN sebelum dan sesudah.
Deploy di jam aman dan monitor lock, latency, dan throughput write.
Verifikasi query benar-benar memakai index baru.
Hapus index lama yang redundan bila sudah terbukti tidak dibutuhkan.

Hal yang perlu dimonitor setelah rollout

Latency endpoint feed.
Latency query count.
Beban CPU database.
Rows scanned atau rows examined.
Dampak ke operasi write karena tambahan maintenance index.

Index baru yang mempercepat baca bisa memperlambat tulis. Pada tabel arsip yang mostly-read, trade-off ini sering layak. Pada tabel yang sangat aktif diupdate, ukur dampaknya dengan serius.

Contoh Desain Praktis untuk Feed Arsip Proyek

Misalkan kebutuhan utama Anda:

Feed publik hanya menampilkan proyek archived.
Sort default berdasarkan last_activity_at DESC, id DESC.
Ada filter tag opsional.
UI publik memakai infinite scroll.

Desain awal yang masuk akal:

Index utama feed: (status, last_activity_at, id).
Index pivot tag: (tag_id, project_id).
Gunakan keyset pagination untuk endpoint publik.
Hindari COUNT(*) real-time di feed publik jika tidak penting.
Pilih kolom select seminimal mungkin untuk kartu daftar.

Jika Anda juga butuh halaman admin dengan nomor halaman absolut, offset pagination masih bisa dipakai di sana, selama ekspektasinya jelas dan query admin tidak menjadi jalur trafik utama.

Kesimpulan

Optimasi feed proyek arsip bukan soal satu trik, tetapi soal mencocokkan pola query dengan desain index dan strategi pagination. Untuk dataset yang terus membesar, bottleneck paling umum adalah OFFSET besar, COUNT(*) yang mahal, sorting yang tidak didukung index, dan join tag yang memicu scan berlebih.

Mulailah dari query nyata, baca EXPLAIN, lalu perbaiki tiga hal paling berdampak: composite index yang sesuai, keyset pagination untuk feed dalam, dan pengendalian query count. Dengan pendekatan ini, feed arsip bisa tetap responsif meski jumlah proyek terus tumbuh dari ratusan menjadi jutaan baris.

Quick Search

shopping_cart Keranjang