Deep OFFSET dan Dampaknya pada Performa Query di Database Skala Besar

Dalam pengembangan aplikasi modern, terutama sistem dengan data berukuran besar (ratusan ribu hingga jutaan baris), performa query database menjadi faktor krusial. Salah satu pola yang sering digunakan namun jarang dievaluasi secara serius adalah penggunaan LIMIT dan OFFSET untuk pagination.

Pada skala kecil, pendekatan ini terlihat sederhana dan aman. Namun, ketika nilai OFFSET semakin besar (deep offset), dampaknya terhadap performa database bisa menjadi signifikan dan bahkan berbahaya di lingkungan produksi.

Artikel ini membahas secara rinci apa itu deep offset, bagaimana database mengeksekusi query dengan OFFSET, mengapa ia menjadi bottleneck performa, serta strategi desain pagination yang lebih scalable.

Apa Itu Deep OFFSET?

Deep offset adalah kondisi ketika sebuah query SQL menggunakan nilai OFFSET yang besar, misalnya puluhan ribu hingga jutaan baris.

Contoh:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 1000000;

Secara konseptual, query ini berarti:

Ambil 20 data terbaru, tetapi lewati 1.000.000 data sebelumnya.

Masalahnya, cara database mengeksekusi perintah ini tidak sesederhana “melompat” ke baris ke-1.000.001.

Bagaimana Database Mengeksekusi LIMIT dan OFFSET

Untuk memahami dampaknya, kita perlu melihat bagaimana database bekerja di balik layar.

Secara umum, database akan melakukan langkah-langkah berikut:

1. Membaca semua baris yang memenuhi klausa WHERE (jika ada)
2. Mengurutkan data sesuai ORDER BY
3. Menghitung dan melewati sejumlah baris sesuai nilai OFFSET
4. Mengembalikan baris sesuai LIMIT

Poin pentingnya adalah:

Baris yang di-offset tetap diproses, meskipun tidak dikembalikan ke client.

Artinya, OFFSET 1.000.000 tetap memaksa database bekerja pada 1.000.020 baris untuk menghasilkan 20 baris hasil.

Mengapa Deep OFFSET Menurunkan Performa

Scan Baris dalam Jumlah Besar

Database tidak bisa secara efisien melewati jutaan baris tanpa membacanya terlebih dahulu. Akibatnya:

• CPU bekerja lebih keras
• Disk I/O meningkat
• Waktu eksekusi query bertambah seiring nilai offset

Semakin besar offset, semakin besar pula jumlah baris yang harus diproses.

ORDER BY Memperparah Kondisi

Kombinasi ORDER BY dan OFFSET adalah penyebab utama penurunan performa.

Jika kolom ORDER BY:

• Tidak memiliki index → database harus melakukan full sort
• Memiliki index → sorting terbantu, tetapi offset tetap harus discan

Pada dataset besar, proses sorting dapat menyebabkan:

• Penggunaan memory besar
• Temporary file di disk
• Query menjadi tidak stabil performanya

Performa Tidak Linear

Pagination berbasis offset memiliki karakteristik performa yang tidak konstan.

Setiap halaman semakin mahal untuk diakses, meskipun ukuran halaman (LIMIT) tetap sama.

Tidak Cache-Friendly

Pagination offset-based selalu menghitung ulang dari awal.

Artinya:

• Page 100 tidak memanfaatkan hasil page 99
• Database mengulang kerja yang sama berkali-kali
• Beban meningkat meskipun traffic tidak berubah

Hal ini membuat deep offset sangat tidak cocok untuk API publik dengan traffic tinggi.

Dampak di Lingkungan Produksi

Di sistem produksi berskala besar, deep offset dapat menyebabkan:

• Latency API meningkat drastis di page tinggi
• Lonjakan CPU dan I/O database
• Query timeout dan error sporadis
• Efek domino ke service lain yang bergantung pada database

Masalah ini sering tidak terdeteksi di environment development karena volume data yang kecil.

Peran Index: Membantu tapi Tidak Menyelesaikan

Index pada kolom ORDER BY memang penting dan sangat direkomendasikan. Namun perlu dicatat:

• Index membantu proses sorting
• Index tidak menghilangkan cost OFFSET

Dengan kata lain, index mengurangi sebagian masalah, tetapi tidak menyelesaikan akar persoalan deep offset.

Alternatif yang Lebih Scalable

Keyset / Cursor Pagination

Pendekatan ini menggantikan offset dengan kondisi berdasarkan nilai terakhir yang diterima client.

Contoh menggunakan timestamp:

SELECT *
FROM orders
WHERE created_at < :last_created_at
ORDER BY created_at DESC
LIMIT 20;

Atau menggunakan ID:

SELECT *
FROM orders
WHERE id < :last_id
ORDER BY id DESC
LIMIT 20;

Keunggulan utama:

• Tidak perlu scan baris sebelumnya
• Performa stabil di setiap halaman
• Sangat cocok untuk dataset besar

Membatasi Kedalaman Pagination

Jika offset tetap digunakan (misalnya untuk admin panel):

• Tetapkan batas maksimum page
• Setelah batas tertentu, arahkan user ke search atau filter

Pendekatan ini mengurangi risiko query ekstrem.

Pre-Aggregation dan Snapshot Data

Untuk kebutuhan reporting atau histori panjang:

• Gunakan materialized view
• Buat tabel snapshot (harian/mingguan)
• Hindari pagination langsung pada data mentah besar

Studi Kasus Singkat

Sebuah API menampilkan daftar transaksi:

SELECT *
FROM transactions
ORDER BY created_at DESC
LIMIT 20 OFFSET 500000;

Hasil observasi:

• Query time meningkat > 10x dibanding page awal
• Beban database naik signifikan

Solusi:

• Diganti dengan cursor pagination
• Query time menjadi konsisten di bawah 50 ms

Best Practice Pagination di Sistem Besar

• Hindari deep offset pada data besar
• Gunakan cursor/keyset pagination untuk API publik
• Pastikan kolom sort memiliki index
• Batasi pagination berbasis offset
• Desain API dengan mempertimbangkan scalability sejak awal

Kesimpulan

Deep offset bukan sekadar masalah optimasi kecil, melainkan masalah desain pagination.

Pada dataset besar:

• OFFSET besar memperlambat query secara signifikan
• Dampaknya meningkat seiring pertumbuhan data
• Cursor pagination hampir selalu menjadi solusi yang lebih tepat

Memahami cara database bekerja dengan OFFSET membantu engineer membuat keputusan arsitektur yang lebih sehat dan berkelanjutan.