Bottleneck OFFSET/LIMIT pada Repositori Audio Skala Besar

Pada arsitektur repositori audio berbasis kecerdasan buatan (AI audio restoration), basis data menyimpan katalog rekaman master bersama ribuan stem hasil dekonstruksi (vocal, drum, bass, instrument). Volume data pada repositori ini bertumbuh eksponensial hingga puluhan juta baris. Pola akses data untuk katalog umumnya memerlukan pemisahan halaman (pagination) saat diakses oleh model training pipeline maupun web interface.

Metode umum berbasis OFFSET dan LIMIT mengalami degradasi performa linear O(N) seiring bertambahnya kedalaman halaman:

-- Pola standar OFFSET/LIMIT
SELECT id, track_id, stem_type, file_path, created_at
FROM audio_stems
ORDER BY created_at DESC
LIMIT 50 OFFSET 2000000;

Query di atas memaksa mesin basis data melakukan traversal index atau membaca 2.000.050 baris tuple dari disk/buffer cache, mengurutkannya, lalu membuang 2.000.000 baris pertama hanya untuk mengambil 50 baris terakhir. Operasi ini memicu buffer churn masif, membebani I/O, dan meningkatkan CPU latency secara signifikan pada tabel berukuran gigabyte atau terabyte.

Skema Data dan Composite Index

Solusi deterministik untuk mengatasi bottleneck ini adalah Keyset Pagination (sering disebut seek method). Alih-alih melompati baris dengan offset, keyset memanfaatkan nilai penanda (kolom terurut) dari baris terakhir halaman sebelumnya sebagai predikat filter langsung pada B-Tree index.

Berikut struktur DDL PostgreSQL untuk tabel repositori stem audio:

CREATE TABLE audio_stems (
    id BIGSERIAL PRIMARY KEY,
    track_id UUID NOT NULL,
    stem_type VARCHAR(32) NOT NULL, -- 'vocals', 'drums', 'bass', 'other'
    s3_uri TEXT NOT NULL,
    restoration_model_version VARCHAR(20) NOT NULL,
    confidence_score NUMERIC(5,4),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Composite index untuk deterministic seek
CREATE INDEX idx_audio_stems_seek 
ON audio_stems (created_at DESC, id DESC);

Pentingnya Tie-Breaker (id)

Pada pipeline pemrosesan audio paralel, ratusan stem dapat dimasukkan secara serentak ke dalam basis data, menghasilkan nilai created_at yang identik (timestamp collision). Pengurutan hanya berdasarkan created_at menyebabkan urutan non-deterministik: tuple dapat terlewat atau muncul dua kali di halaman berikutnya. Kolom id yang bersifat unik dan monotonik naik wajib disertakan sebagai tie-breaker pada composite index.

Implementasi Query Keyset Pagination

1. Halaman Selanjutnya (Next Page)

Aplikasi menyimpan dua token kursor dari baris terakhir halaman aktif: $last_created_at dan $last_id.

Sintaks PostgreSQL row-constructor:

SELECT id, track_id, stem_type, s3_uri, created_at
FROM audio_stems
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Sintaks alternatif SQL standar (kompatibel penuh dengan optimizer MySQL tanpa deoptimasi tuple):

SELECT id, track_id, stem_type, s3_uri, created_at
FROM audio_stems
WHERE created_at < $last_created_at
   OR (created_at = $last_created_at AND id < $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

2. Halaman Sebelumnya (Previous Page / Bidirectional)

Navigasi mundur dilakukan dengan membalik tanda operator komparasi dan arah ORDER BY menggunakan nilai baris pertama dari halaman aktif ($first_created_at, $first_id), kemudian membalikkan urutan hasil query di layer aplikasi atau subquery:

SELECT * FROM (
    SELECT id, track_id, stem_type, s3_uri, created_at
    FROM audio_stems
    WHERE (created_at, id) > ($first_created_at, $first_id)
    ORDER BY created_at ASC, id ASC
    LIMIT 50
) AS previous_page
ORDER BY created_at DESC, id DESC;

Evaluasi Kinerja: EXPLAIN ANALYZE

Pengujian dilakukan pada PostgreSQL 16 terhadap tabel audio_stems berisi 5.000.000 baris tuple data audio.

Eksekusi OFFSET 1.000.000

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, track_id, stem_type, created_at
FROM audio_stems
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000000;

Output rencana eksekusi:

Limit  (cost=141872.23..141879.32 rows=50 width=64) (actual time=248.812..248.824 rows=50 loops=1)
  Buffers: shared hit=43820 read=18921
  ->  Index Scan using idx_audio_stems_seek on audio_stems (cost=0.43..709361.15 rows=5000000 width=64) (actual time=0.048..192.410 rows=1000050 loops=1)
        Buffers: shared hit=43820 read=18921
Planning Time: 0.158 ms
Execution Time: 249.012 ms

Eksekusi Keyset Pagination (Deep Page Setara)

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, track_id, stem_type, created_at
FROM audio_stems
WHERE (created_at, id) < ('2024-02-10 14:22:18.10234+00', 3999950)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Output rencana eksekusi:

Limit  (cost=0.43..7.53 rows=50 width=64) (actual time=0.038..0.062 rows=50 loops=1)
  Buffers: shared hit=4 read=0
  ->  Index Scan using idx_audio_stems_seek on audio_stems (cost=0.43..567489.20 rows=4000000 width=64) (actual time=0.036..0.055 rows=50 loops=1)
        Index Cond: (RowCompareExpr: (created_at, id) < ('2024-02-10 14:22:18.10234+00'::timestamptz, 3999950))
        Buffers: shared hit=4 read=0
Planning Time: 0.121 ms
Execution Time: 0.084 ms

Komparasi Metrik

  • Execution Time: Turun dari 249.012 ms menjadi 0.084 ms (akselerasi ~2900x lebih cepat).
  • Shared Buffers Read/Hit: OFFSET membaca 62.741 buffer pages (sekitar ~490 MB data dari disk dan RAM) untuk membuang baris. Keyset hanya menyentuh 4 shared hit buffer langsung pada node B-Tree terkait.
  • Karakteristik Skalabilitas: Keyset memiliki kompleksitas konsisten O(log N + LIMIT) berapa pun kedalaman halamannya.

Pitfall dan Optimasi Sargability

Peringatan Kolom Nullable: Hindari penggunaan kolom nullable sebagai komponen keyset pagination. Urutan nilai NULL (diatur oleh NULLS FIRST atau NULLS LAST) dapat mengecualikan tuple tertentu dari operator perbandingan B-Tree range scan. Pastikan semua kolom pada composite index didefinisikan dengan constraint NOT NULL.

1. Predikat Non-Sargable

Jangan membungkus kolom filter dalam fungsi atau casting runtime saat melakukan perbandingan, karena akan membatalkan index scan:

-- SALAH: Non-sargable, engine melakukan full scan
WHERE DATE(created_at) <= DATE($cursor_date)

-- BENAR: Sargable, index range scan langsung ke leaf node
WHERE created_at < $cursor_timestamp

2. Limitasi Trade-off Keyset

  • Tidak Ada Lompatan Acak (No Random Access): Klien tidak dapat melompat langsung ke "Halaman 42" tanpa membaca data batas dari halaman 41. Keyset optimal untuk sistem continuous scroll, infinite loading API, atau navigasi sequential (Next/Prev).
  • Coupling State Kursor: Klien harus memelihara token state (nilai id dan created_at) baris terakhir, bukan representasi numerik flat (halaman = 5).