Pada pipeline pemrosesan batch audio dan konversi tape rekaman ke teks, tabel riwayat transkripsi bertambah dengan cepat hingga mencapai jutaan baris. Permasalahan performa umumnya muncul saat pengguna atau sistem antrean menarik data lama menggunakan pagination berbasis OFFSET. Semakin jauh halaman diakses, waktu eksekusi melonjak signifikan karena database membaca dan membuang baris data sebelumnya secara percuma.

Solusi deterministik untuk masalah ini adalah keyset pagination (cursor-based pagination) yang memanfaatkan struktur index B-Tree. Pendekatan ini mempertahankan kompleksitas pencarian data tetap konstan di level O(log N) tanpa terpengaruh kedalaman halaman data.

Penyebab Bottleneck OFFSET pada Data Skala Besar

Pola paginasi konvensional menggunakan sintaks LIMIT n OFFSET m. Cara kerja database engine seperti PostgreSQL atau MySQL saat mengeksekusi query tersebut:

SELECT id, task_id, status, created_at
FROM transcription_logs
ORDER BY created_at DESC
LIMIT 20 OFFSET 500000;

Database tidak langsung melompat ke baris ke-500.001. Engine harus membaca 500.020 baris dari disk atau buffer pool, mengurutkannya sesuai index, mengambil 20 baris terakhir, lalu membuang 500.000 baris sisanya. Operasi ini memicu high buffer hit ratio, lonjakan disk I/O, saturasi CPU, serta memperlambat koneksi database lain yang berjalan paralel.

Skema Database dan Composite Index B-Tree

Keyset pagination membutuhkan titik acuan unik (cursor) dari baris terakhir yang berhasil dibaca. Karena kolom timestamp (created_at) rentan memiliki nilai kembar saat batch processing memproses banyak data dalam satu milidetik, kolom unik penentu seperti id harus disertakan sebagai tie-breaker.

Struktur skema tabel dan composite index yang optimal:

CREATE TABLE transcription_logs (
    id BIGSERIAL PRIMARY KEY,
    task_id UUID NOT NULL,
    audio_duration_seconds INT NOT NULL,
    transcription_text TEXT,
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Composite index B-Tree urutan menurun (DESC)
CREATE INDEX idx_transcription_logs_created_id 
ON transcription_logs (created_at DESC, id DESC);

Composite index B-Tree tersebut menyusun daun data (leaf nodes) secara terurut berdasarkan created_at DESC, kemudian id DESC. Struktur ini memungkinkan traversal pencarian langsung mengarah ke node referensi.

Implementasi Query SQL: Keyset Pagination

Saat mengambil halaman pertama, query hanya membutuhkan klausa LIMIT biasa:

-- Halaman pertama
SELECT id, task_id, status, created_at
FROM transcription_logs
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dari hasil halaman pertama, catat nilai kolom terakhir, misalnya: created_at = '2024-03-30 08:15:22.102341+00' dan id = 4982103. Gunakan kedua nilai ini sebagai cursor batas penarikan halaman kedua.

Gunakan operator perbandingan tuple (row constructor) standar SQL:

-- Halaman berikutnya menggunakan Cursor Tuple
SELECT id, task_id, status, created_at
FROM transcription_logs
WHERE (created_at, id) < ('2024-03-30 08:15:22.102341+00', 4982103)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Jika database engine tidak mengoptimalkan row-value comparison secara efisien, pecah kondisi tuple menjadi logika Boolean eksplisit:
WHERE created_at < $cursor_time OR (created_at = $cursor_time AND id < $cursor_id)

Perbandingan Eksekusi EXPLAIN ANALYZE

Uji coba pada tabel riwayat transkripsi berisi 5.000.000 baris menghasilkan perbedaan rencana eksekusi dan pemakaian resource yang signifikan.

1. Query OFFSET (Halaman Dalam)

EXPLAIN ANALYZE
SELECT id, task_id, status, created_at
FROM transcription_logs
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 500000;

-- Rencana Eksekusi:
-- Limit  (cost=35122.10..35123.50 rows=20 width=64) (actual time=142.128..142.133 rows=20 loops=1)
--   ->  Index Scan using idx_transcription_logs_created_id on transcription_logs (cost=0.43..351221.05 rows=5000000 width=64) (actual time=0.041..118.892 rows=500020 loops=1)
-- Planning Time: 0.082 ms
-- Execution Time: 142.164 ms

Database membaca 500.020 tuple indeks fisik dari memori atau disk, menghabiskan waktu sekitar 142 ms hanya untuk mengabaikan datanya.

2. Query Keyset Pagination

EXPLAIN ANALYZE
SELECT id, task_id, status, created_at
FROM transcription_logs
WHERE (created_at, id) < ('2024-03-30 08:15:22.102341+00', 4982103)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Rencana Eksekusi:
-- Limit  (cost=0.43..1.85 rows=20 width=64) (actual time=0.038..0.052 rows=20 loops=1)
--   ->  Index Scan using idx_transcription_logs_created_id on transcription_logs (cost=0.43..316099.02 rows=4500000 width=64) (actual time=0.036..0.048 rows=20 loops=1)
--         Index Cond: (ROW(created_at, id) < ROW('2024-03-30 08:15:22.102341+00'::timestamptz, 4982103))
-- Planning Time: 0.091 ms
-- Execution Time: 0.071 ms

Index scan langsung melompat ke posisi leaf node B-Tree yang ditargetkan dan hanya membaca 20 baris. Waktu eksekusi turun drastis dari 142 ms menjadi di bawah 0.1 ms.

Trade-off dan Batasan

  • Navigasi Acak (Random Page Jump): Keyset pagination tidak mendukung tautan acak seperti tombol "Loncat ke Halaman 50". Pola ini ditujukan untuk infinite scrolling, tombol Next/Previous, atau konsumsi data via REST/gRPC API.
  • Konsistensi Bidireksional: Untuk fitur halaman sebelumnya (Previous Page), query perlu membalik operator perbandingan (>) dan ORDER BY ... ASC, kemudian hasil array dibalik kembali di level aplikasi.
  • Ketergantungan Kolom Index: Filter pencarian harus selalu selaras dengan urutan kolom pada composite index. Mengubah filter pencarian memerlukan index B-Tree pendukung baru agar performa query tidak mengalami degradasi.