Deep OFFSET dan Performa Query

Pagination dengan LIMIT dan OFFSET adalah salah satu pola paling umum dalam pengembangan aplikasi — mudah dipahami, mudah diimplementasi, dan bekerja dengan sempurna di tahap awal. Tapi ada satu karakteristik yang jarang dievaluasi sebelum sistem masuk ke production: performa OFFSET tidak linear. Setiap kali pengguna maju ke halaman berikutnya, database tidak “melompat” ke posisi yang diinginkan — ia membaca ulang semua baris sebelumnya dan membuangnya. Di halaman ke-100 dengan 20 item per halaman, database memproses 1.980 baris hanya untuk membuangnya. Di halaman ke-50.000, database memproses satu juta baris untuk mengembalikan dua puluh.

Bagaimana Database Mengeksekusi OFFSET

Untuk memahami mengapa deep offset menjadi masalah, kamu perlu punya model mental yang akurat tentang apa yang sebenarnya dilakukan database ketika menerima query dengan OFFSET.

Ambil query ini sebagai contoh:

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

Secara intuitif, kita mengharapkan database “melompat” ke baris ke-100.001 dan langsung mengambil 20 baris dari sana. Tapi bukan itu yang terjadi. Langkah eksekusi sesungguhnya adalah sebagai berikut.

flowchart TD
    A[Query diterima database] --> B[Scan baris yang memenuhi WHERE]
    B --> C[Urutkan semua hasil sesuai ORDER BY]
    C --> D["Baca dan hitung baris satu per satu\nHingga mencapai OFFSET"]
    D --> E["Buang 100.000 baris pertama\n(sudah dibaca, tidak dikembalikan)"]
    E --> F["Ambil 20 baris berikutnya\nsesuai LIMIT"]
    F --> G[Kembalikan ke client]

    style E fill:#ffebee,stroke:#e53935
    style D fill:#fff3e0,stroke:#fb8c00

Kuncinya ada di langkah keempat dan kelima: baris yang di-skip tetap dibaca dan diproses. Database tidak memiliki mekanisme untuk benar-benar “melompati” baris — ia harus menghitung posisinya satu per satu. Artinya, OFFSET 100000 memaksa database memproses 100.020 baris untuk mengembalikan 20.

Tidak ada perbedaan beban antara OFFSET 100000 LIMIT 20 dan LIMIT 100020 dalam hal jumlah baris yang dibaca — bedanya hanya 100.000 baris pertama dibuang sebelum dikembalikan ke client.

Mengapa Performa Tidak Linear

Ini adalah karakteristik paling berbahaya dari offset-based pagination: beban kerja tumbuh seiring nilai offset, bukan seiring ukuran halaman. Ukuran halaman tetap konstan, tapi setiap halaman semakin mahal.

flowchart LR
    subgraph Page1["Halaman 1 (OFFSET 0)"]
        direction TB
        R1["Baca 20 baris ✓"]
    end

    subgraph Page2["Halaman 100 (OFFSET 1980)"]
        direction TB
        R2["Baca + buang 1980 baris\nKembalikan 20 baris"]
    end

    subgraph Page3["Halaman 1000 (OFFSET 19980)"]
        direction TB
        R3["Baca + buang 19.980 baris\nKembalikan 20 baris"]
    end

    subgraph Page4["Halaman 50000 (OFFSET 999980)"]
        direction TB
        R4["Baca + buang 999.980 baris\nKembalikan 20 baris"]
    end

    style R2 fill:#fff3e0,stroke:#fb8c00
    style R3 fill:#ffe0b2,stroke:#ef6c00
    style R4 fill:#ffebee,stroke:#e53935

Dalam angka yang lebih konkret:

Di halaman terakhir dataset besar, database menghabiskan hampir seluruh energinya untuk memproses baris yang tidak pernah sampai ke client.

Dampak ORDER BY

Kombinasi ORDER BY dan OFFSET memperparah situasi lebih jauh. Jika kolom yang di-sort tidak memiliki index, database harus melakukan full sort pada seluruh result set sebelum bisa mulai menghitung offset. Ini berarti:

• Penggunaan memory yang besar (atau temp file di disk jika result set melebihi buffer)
• Query plan yang tidak stabil saat data tumbuh
• Performa yang bisa berubah drastis ketika ada perubahan distribusi data

Bahkan jika kolom ORDER BY sudah punya index, index hanya membantu proses pengurutan — bukan menghilangkan kebutuhan untuk membaca baris-baris yang di-skip.

Dampak di Production yang Sering Tidak Terdeteksi

Masalah deep offset hampir tidak pernah terdeteksi di tahap development karena volume data yang kecil. Database dengan 10.000 baris merespons OFFSET 5000 dalam milidetik. Database yang sama dengan 50 juta baris merespons OFFSET 5.000.000 dalam detik — atau tidak merespons sama sekali karena query timeout.

Ini menciptakan pola kegagalan yang berbahaya: sistem berfungsi sempurna selama berbulan-bulan, lalu tiba-tiba mulai menunjukkan gejala performa yang tidak jelas sumbernya seiring data tumbuh.

sequenceDiagram
    participant U as User
    participant API as API Server
    participant DB as Database

    Note over DB: 6 bulan kemudian, data sudah 50 juta baris

    U->>API: GET /orders?page=1
    API->>DB: SELECT ... OFFSET 0 LIMIT 20
    DB-->>API: ✓ 8ms
    API-->>U: 200 OK (cepat)

    U->>API: GET /orders?page=50000
    API->>DB: SELECT ... OFFSET 999980 LIMIT 20
    Note over DB: Memproses ~1 juta baris...
    DB-->>API: ✓ 28.000ms (28 detik)
    API-->>U: 504 Gateway Timeout

    Note over API,DB: Alert masuk: "Latency spike tidak jelas sumbernya"

Gejala yang muncul di production biasanya bukan “query lambat” yang mudah didiagnosis — melainkan serangkaian efek sekunder yang membingungkan: CPU database naik tanpa alasan jelas, koneksi pool habis, service lain yang berbagi database ikut terdampak, dan alert latency menyala di jam-jam tertentu ketika user cenderung scroll jauh ke halaman belakang.

Anti-Pattern: Offset Pagination Tanpa Batas

-- ✗ Anti-pattern 1: OFFSET tanpa batasan maksimum
-- Dibiarkan user mengakses halaman berapapun via parameter
GET /api/orders?page=50000&limit=20

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 999980;
-- Akibat: query bisa memproses jutaan baris, risiko timeout tinggi

-- ✗ Anti-pattern 2: OFFSET untuk data export / bulk processing
-- Export semua data dengan looping pagination offset
page = 0
while True:
    results = query(f"SELECT * FROM orders LIMIT 1000 OFFSET {page * 1000}")
    if not results: break
    process(results)
    page += 1
-- Akibat: setiap iterasi semakin lambat; iterasi ke-1000 memproses 1 juta baris

-- ✗ Anti-pattern 3: COUNT(*) + OFFSET untuk total pages
SELECT COUNT(*) FROM orders;  -- full scan untuk hitung total
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;  -- query kedua
-- Akibat: dua query mahal untuk setiap load halaman; COUNT(*) sendiri sudah lambat
--        di tabel besar tanpa index yang tepat

Solusi 1: Keyset Pagination (Cursor-Based)

Keyset pagination menggantikan konsep “halaman ke-N” dengan konsep “data setelah nilai ini”. Alih-alih mengatakan “lewati 100.000 baris pertama”, kamu mengatakan “ambil data yang created_at-nya lebih kecil dari nilai terakhir yang sudah kamu lihat”.

-- ✓ Request pertama: tidak ada cursor, ambil dari awal
SELECT id, created_at, amount, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Response menyertakan cursor dari item terakhir:
-- { last_created_at: "2025-01-15 10:30:00", last_id: 84521 }

-- ✓ Request berikutnya: gunakan cursor dari response sebelumnya
SELECT id, created_at, amount, status
FROM orders
WHERE (created_at, id) < ('2025-01-15 10:30:00', 84521)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Database tidak perlu membaca baris manapun sebelum cursor — ia langsung mencari posisi yang tepat menggunakan index. Performa query ini identik di halaman pertama maupun halaman ke-satu-juta.

flowchart TD
    subgraph Offset["OFFSET Pagination"]
        A1[Page 1] -->|"baca 20 baris"| R1[20 baris]
        A2[Page 100] -->|"baca + buang 1980, ambil 20"| R2[20 baris]
        A3[Page 50000] -->|"baca + buang 999980, ambil 20"| R3[20 baris]
        style A3 fill:#ffebee,stroke:#e53935
    end

    subgraph Cursor["Cursor Pagination"]
        B1[Page 1] -->|"WHERE id < MAX → baca 20"| S1[20 baris]
        B2[Page 100] -->|"WHERE id < cursor → baca 20"| S2[20 baris]
        B3[Page 50000] -->|"WHERE id < cursor → baca 20"| S3[20 baris]
        style B1 fill:#e8f5e9,stroke:#43a047
        style B2 fill:#e8f5e9,stroke:#43a047
        style B3 fill:#e8f5e9,stroke:#43a047
    end

Cursor Pagination dengan ID Monoton

Untuk tabel yang primary key-nya monoton naik (integer atau UUID v7), implementasinya lebih sederhana:

-- ✓ Halaman berikutnya setelah id terakhir = 84521
SELECT id, title, created_at
FROM products
WHERE id < 84521
ORDER BY id DESC
LIMIT 20;

-- ✓ Halaman sebelumnya (navigasi ke belakang)
SELECT id, title, created_at
FROM products
WHERE id > 84521
ORDER BY id ASC
LIMIT 20;

Index pada id (yang sudah ada sebagai primary key) langsung dimanfaatkan secara penuh — tidak ada baris yang terbuang, tidak ada scan yang sia-sia.

Trade-off Cursor Pagination

Cursor pagination memang lebih efisien, tapi ada batasan yang perlu dipahami sebelum memutuskan menggunakannya.

Solusi 2: Late Row Lookup

Untuk kasus di mana offset pagination benar-benar diperlukan (misalnya admin panel dengan navigasi halaman acak), ada teknik optimasi yang disebut late row lookup atau deferred join. Idenya adalah: lakukan scanning dan sorting hanya pada kolom yang diindex, baru join ke tabel utama untuk mengambil kolom lainnya.

-- ✗ Tanpa late row lookup: fetch semua kolom sambil scanning
SELECT id, user_id, created_at, amount, status, notes, metadata
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- Database membaca semua kolom (termasuk yang besar) untuk 100.020 baris

-- ✓ Dengan late row lookup: scan dulu, baru join
SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 100000
) AS paged ON o.id = paged.id;
-- Subquery hanya membaca kolom id (index-only scan jika tersedia)
-- JOIN ke tabel utama hanya untuk 20 baris yang benar-benar dibutuhkan

Teknik ini tidak menghilangkan masalah deep offset, tapi bisa mengurangi beban I/O secara signifikan karena scanning dilakukan pada index yang lebih kecil daripada tabel lengkap.

Solusi 3: Membatasi Kedalaman Pagination

Untuk API publik yang menggunakan offset pagination, salah satu pendekatan paling pragmatis adalah menetapkan batas maksimum yang keras.

-- Validasi di layer aplikasi sebelum query dieksekusi
MAX_OFFSET = 10000

if offset > MAX_OFFSET:
    return error("Gunakan filter atau pencarian untuk mengakses data lebih dalam")

-- Query hanya dieksekusi jika offset dalam batas aman
SELECT *
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset;

Jika user perlu mengakses data yang jauh ke belakang, arahkan mereka ke fitur pencarian atau filter berdasarkan rentang tanggal — yang jauh lebih efisien karena bisa memanfaatkan index secara langsung.

Solusi 4: Snapshot untuk Reporting

Untuk kebutuhan reporting atau export data historis yang besar, pagination terhadap data mentah sama sekali bukan pendekatan yang tepat. Gunakan materialized view atau tabel snapshot yang di-refresh secara periodik.

-- Buat snapshot harian untuk reporting
CREATE MATERIALIZED VIEW orders_daily_summary AS
SELECT
    DATE(created_at) AS order_date,
    status,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(created_at), status;

-- Query reporting yang cepat dan tidak tergantung ukuran tabel utama
SELECT *
FROM orders_daily_summary
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY order_date DESC;

Dengan pendekatan ini, query reporting tidak pernah menyentuh tabel orders yang terus tumbuh — ia hanya membaca snapshot yang jauh lebih kecil dan sudah pre-aggregated.

Decision Tree: Pilih Strategi Pagination

flowchart TD
    A{Apakah user perlu\nlompat ke halaman acak?} -- Ya --> B{Berapa estimasi\nmaksimum offset?}
    A -- Tidak --> C["✓ Gunakan cursor pagination\n(ideal untuk infinite scroll,\nfeed, atau sequential browsing)"]

    B -- "< 10.000 baris" --> D["✓ Offset pagination dengan\nbatas maksimum\n+ late row lookup jika perlu"]
    B -- "> 10.000 baris" --> E{Apa tujuan akses\ndata dalam?}

    E -- "Reporting / analytics" --> F["✓ Gunakan materialized view\natau tabel snapshot"]
    E -- "Search / filter" --> G["✓ Tambahkan filter wajib\n(rentang tanggal, status, dll)\nsebelum pagination"]
    E -- "Export data" --> H["✓ Gunakan streaming\natau batch job async\nbukan pagination"]

Checklist Implementasi Pagination

SEBELUM IMPLEMENTASI:
  □ Identifikasi apakah user membutuhkan navigasi halaman acak
    atau cukup prev/next (sequential browsing)
  □ Estimasi volume data dalam 1 dan 3 tahun ke depan
  □ Tentukan apakah ini API publik (traffic tinggi) atau
    internal tool (traffic rendah, bisa toleransi lebih tinggi)

JIKA MENGGUNAKAN OFFSET PAGINATION:
  □ Ada batas maksimum offset yang di-enforce di layer aplikasi
  □ Kolom ORDER BY memiliki index
  □ Late row lookup diterapkan untuk tabel dengan kolom yang besar
  □ Monitoring query time per halaman (bukan hanya rata-rata)

JIKA MENGGUNAKAN CURSOR PAGINATION:
  □ Cursor di-encode (base64 atau signed token) agar tidak bisa dimanipulasi
  □ Kolom yang digunakan sebagai cursor memiliki index
  □ Kombinasi kolom cursor bersifat unik (tambahkan id sebagai tie-breaker)
  □ API response menyertakan next_cursor dan has_more
  □ Cursor memiliki expiry time yang wajar

UNTUK REPORTING / EXPORT:
  □ Tidak menggunakan pagination offset untuk dataset > 100.000 baris
  □ Materialized view atau snapshot di-refresh sesuai frekuensi kebutuhan
  □ Export besar menggunakan background job, bukan request synchronous

Ringkasan

• OFFSET tidak “melompat” — ia membaca dan membuang. Setiap baris sebelum nilai OFFSET tetap diproses oleh database, meskipun tidak pernah sampai ke client. OFFSET 1.000.000 memproses lebih dari satu juta baris untuk mengembalikan dua puluh.
• Performa offset pagination tidak linear — setiap halaman semakin mahal. Di halaman ke-50.000 dengan 20 item per halaman, database membuang 99,998% dari semua kerja yang dilakukannya.
• Masalah ini tidak terdeteksi di development karena volume data kecil — ia baru muncul di production setelah data tumbuh, dalam bentuk latency spike, CPU tinggi, dan query timeout yang membingungkan.
• Cursor/keyset pagination adalah solusi utama: alih-alih “lewati N baris”, gunakan WHERE id < cursor. Performa identik di halaman pertama maupun halaman ke-sejuta.
• Late row lookup bisa mengurangi dampak deep offset jika offset pagination benar-benar diperlukan — scan hanya di index, join ke tabel utama hanya untuk baris yang dikembalikan.
• Batasi kedalaman pagination untuk API publik dengan batas keras (misalnya maksimum offset 10.000), dan arahkan kebutuhan lebih dalam ke fitur filter atau search.
• Reporting dan export tidak boleh menggunakan pagination offset pada data besar — gunakan materialized view, snapshot periodik, atau background job streaming.