Deteksi Full Scan pada Tabel Queue Worker dengan SQLite

Full scan pada tabel queue worker SQLite biasanya terlihat sebagai worker yang makin lambat saat jumlah job bertambah, padahal logika aplikasinya tidak berubah. Cara paling langsung untuk mendeteksinya adalah memeriksa query klaim job dengan EXPLAIN QUERY PLAN dan memastikan SQLite melakukan SEARCH ... USING INDEX, bukan SCAN pada tabel jobs.

Masalah ini sering muncul karena tabel queue memiliki pola akses yang sangat panas: worker terus-menerus mencari satu job yang siap dieksekusi. Jika pencarian tersebut membaca banyak baris setiap polling, efeknya cepat terasa: lock contention meningkat, retry menumpuk, dan cache terlihat tidak efektif karena database harus menyentuh terlalu banyak halaman data.

Mengapa Tabel Queue Worker Rentan Full Table Scan

Queue worker biasanya menjalankan query seperti ini berkali-kali per detik atau per menit:

ambil job dengan status tertentu, misalnya pending;
hanya ambil job yang sudah waktunya jalan, misalnya run_at <= now;
hindari job yang sedang dikunci worker lain, misalnya locked_until <= now;
urutkan berdasarkan waktu atau prioritas;
ambil satu atau sejumlah kecil baris.

Jika tidak ada indeks yang sesuai dengan kombinasi filter dan urutan tersebut, SQLite dapat membaca seluruh tabel jobs untuk menemukan satu baris. Pada tabel kecil ini tidak terasa, tetapi pada tabel yang berisi ribuan hingga jutaan job historis, polling worker dapat berubah menjadi beban database yang dominan.

Gejala Operasional yang Sering Terlihat

Full scan jarang muncul sebagai error eksplisit. Biasanya ia terlihat dari gejala operasional berikut:

Worker melambat: waktu klaim job meningkat, bukan hanya waktu eksekusi job.
Lock contention meningkat: operasi tulis saling menunggu lebih lama karena transaksi klaim job berjalan lebih lama.
Retry menumpuk: job yang gagal atau tertunda tidak segera diproses ulang karena worker menghabiskan waktu untuk mencari job berikutnya.
Cache terlihat tidak efektif: meskipun data sering diakses, full scan tetap memaksa database membaca banyak halaman. Cache hit bisa saja tinggi, tetapi CPU dan waktu scanning tetap boros.
Latency polling naik mengikuti ukuran tabel: makin banyak job lama atau selesai yang tersimpan, makin lambat query klaim job.

Indikator penting: jika query klaim job semakin lambat saat jumlah baris historis bertambah, tetapi jumlah job siap proses tidak bertambah signifikan, curigai full table scan atau indeks yang tidak sesuai.

Contoh Schema Job Queue SQLite

Berikut contoh schema sederhana untuk queue berbasis SQLite. Waktu disimpan sebagai integer Unix timestamp agar query dapat menggunakan perbandingan numerik langsung tanpa membungkus kolom dengan fungsi.

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  queue TEXT NOT NULL DEFAULT 'default',
  status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'done', 'failed')),
  payload TEXT NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,
  run_at INTEGER NOT NULL,
  locked_until INTEGER NOT NULL DEFAULT 0,
  locked_by TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  last_error TEXT
);

-- Indeks awal yang sering terlalu lemah jika hanya status.
CREATE INDEX idx_jobs_status ON jobs (status);

Indeks tunggal pada status jarang cukup untuk queue yang aktif. Kolom status biasanya memiliki kardinalitas rendah: banyak baris memiliki status done, pending, atau failed. Worker tetap perlu menyaring run_at, locked_until, dan sering kali mengurutkan hasil.

Query Klaim Job yang Perlu Diperiksa

Contoh query klaim job yang umum:

SELECT id
FROM jobs
WHERE queue = 'default'
  AND status = 'pending'
  AND run_at <= 1710000000
  AND locked_until <= 1710000000
ORDER BY run_at ASC, locked_until ASC, id ASC
LIMIT 1;

Di aplikasi nyata, nilai waktu sebaiknya dikirim sebagai parameter, bukan ditulis literal. Contoh di atas menggunakan angka tetap agar mudah diuji dengan EXPLAIN QUERY PLAN.

Untuk mengklaim job dengan aman, lakukan pembacaan dan update dalam transaksi singkat. Contoh pola dasar:

BEGIN IMMEDIATE;

SELECT id
FROM jobs
WHERE queue = 'default'
  AND status = 'pending'
  AND run_at <= :now
  AND locked_until <= :now
ORDER BY run_at ASC, locked_until ASC, id ASC
LIMIT 1;

UPDATE jobs
SET status = 'running',
    locked_until = :lock_deadline,
    locked_by = :worker_id,
    attempts = attempts + 1,
    updated_at = :now
WHERE id = :id
  AND status = 'pending'
  AND locked_until <= :now;

COMMIT;

BEGIN IMMEDIATE meminta lock tulis di awal transaksi. Ini membantu menghindari dua worker mengklaim job yang sama, tetapi juga berarti transaksi harus dibuat sangat pendek. Jika query SELECT melakukan full scan, lock akan tertahan lebih lama dan contention akan meningkat.

Deteksi Full Scan dengan EXPLAIN QUERY PLAN

Gunakan EXPLAIN QUERY PLAN untuk melihat rencana eksekusi query klaim job:

EXPLAIN QUERY PLAN
SELECT id
FROM jobs
WHERE queue = 'default'
  AND status = 'pending'
  AND run_at <= 1710000000
  AND locked_until <= 1710000000
ORDER BY run_at ASC, locked_until ASC, id ASC
LIMIT 1;

Jika menggunakan SQLite shell, Anda juga dapat mengaktifkan mode rencana query:

.eqp on

Contoh output yang patut dicurigai:

SCAN jobs
USE TEMP B-TREE FOR ORDER BY

SCAN jobs berarti SQLite membaca tabel jobs, bukan mencari lewat indeks yang selektif. USE TEMP B-TREE FOR ORDER BY menunjukkan SQLite perlu struktur sementara untuk mengurutkan hasil. Pada query yang sering dipanggil worker, kombinasi ini biasanya buruk.

Setelah indeks yang sesuai dibuat, output yang lebih sehat biasanya berbentuk seperti ini:

SEARCH jobs USING INDEX idx_jobs_claim_default (...)

Atau, jika semua kolom yang diperlukan tersedia dari indeks:

SEARCH jobs USING COVERING INDEX idx_jobs_claim_default (...)

Teks persisnya dapat berbeda tergantung versi SQLite, statistik tabel, dan bentuk query. Fokus utamanya: hindari SCAN jobs pada query panas klaim job.

Memperbaiki dengan Indeks Komposit

Untuk query klaim job, indeks perlu mengikuti pola filter. Jika worker selalu memproses satu queue tertentu, dan query memakai queue, status, run_at, dan locked_until, indeks komposit yang masuk akal adalah:

CREATE INDEX idx_jobs_claim_default
ON jobs (queue, status, run_at, locked_until, id);

Jika aplikasi hanya memiliki satu queue atau kolom queue tidak dipakai dalam filter, gunakan indeks yang lebih langsung:

CREATE INDEX idx_jobs_claim
ON jobs (status, run_at, locked_until, id);

Indeks komposit status/run_at/locked_until membantu karena SQLite dapat mempersempit pencarian dari kiri ke kanan. status adalah filter kesetaraan, lalu run_at membatasi job yang sudah waktunya berjalan, dan locked_until membantu menyaring job yang masih terkunci. Kolom id ditambahkan untuk membuat urutan deterministik saat beberapa job memiliki waktu yang sama.

Perhatikan Urutan Kolom Indeks

Urutan kolom indeks bukan detail kosmetik. Jika query selalu menyertakan queue = ?, letakkan queue di kiri. Jika query tidak pernah memfilter queue, memasukkan queue di kiri justru dapat membuat indeks kurang berguna untuk query tersebut.

Untuk predicate range seperti run_at <= ? dan locked_until <= ?, planner tidak selalu dapat memanfaatkan semua kolom dengan cara yang sama kuatnya. Karena itu, jangan hanya menebak. Buat indeks, jalankan EXPLAIN QUERY PLAN, lalu ukur latency query klaim job pada data yang representatif.

Hindari Predicate yang Merusak Pemakaian Indeks

Kesalahan umum adalah membungkus kolom indeks dengan fungsi:

-- Hindari pola seperti ini pada query panas.
WHERE datetime(run_at, 'unixepoch') <= datetime('now')

Lebih baik hitung timestamp di aplikasi atau dengan ekspresi pada sisi kanan, lalu bandingkan langsung dengan kolom:

WHERE run_at <= :now

Pola lain yang perlu diwaspadai adalah OR yang terlalu luas, COALESCE pada kolom filter, atau perbedaan tipe data antara kolom dan parameter. Semuanya dapat membuat planner sulit memakai indeks secara efektif.

Mengelola Job Kedaluwarsa dan Retry

Jika worker mati saat job berstatus running, job perlu dikembalikan agar bisa diproses ulang. Jangan membuat query klaim utama terlalu rumit dengan banyak cabang OR jika hal itu menyebabkan full scan. Salah satu pola yang lebih mudah diindeks adalah memisahkan proses requeue:

CREATE INDEX idx_jobs_expired_running
ON jobs (status, locked_until);

UPDATE jobs
SET status = 'pending',
    locked_by = NULL,
    updated_at = :now
WHERE status = 'running'
  AND locked_until <= :now
  AND attempts < max_attempts;

Dengan pola ini, query klaim utama tetap fokus pada status = 'pending', run_at, dan locked_until. Query requeue memiliki indeks sendiri yang sesuai dengan filter status dan locked_until.

SQLite Locking: Indeks Buruk Membuat Transaksi Lebih Lama

SQLite mendukung banyak pembaca, tetapi operasi tulis tetap harus diserialisasi. Pada queue worker, klaim job adalah operasi tulis karena worker akhirnya mengubah status atau lock job. Jika pencarian job melakukan full scan di dalam transaksi, transaksi bertahan lebih lama dan worker lain lebih mudah menunggu.

Mode seperti WAL dapat membantu pola read-write tertentu, tetapi tidak memperbaiki query plan yang buruk. Begitu juga busy_timeout: ia dapat mengurangi error lock langsung, tetapi tidak menghilangkan akar masalah jika worker menahan lock karena full scan.

Praktik yang lebih aman:

buat transaksi klaim job sekecil mungkin;
jangan menjalankan eksekusi job di dalam transaksi klaim;
pastikan query klaim memakai indeks yang tepat;
arsipkan atau hapus job historis yang tidak lagi perlu dicari;
jalankan ANALYZE setelah perubahan indeks besar atau perubahan distribusi data yang signifikan agar planner memiliki statistik yang lebih baik.

Checklist Observability untuk Queue Worker SQLite

Agar full scan tidak hanya ditemukan saat insiden, pantau metrik yang dekat dengan perilaku queue:

Latency klaim job: ukur waktu dari worker mulai polling sampai berhasil mengklaim atau menyimpulkan tidak ada job.
Queue depth per status: jumlah pending, running, failed, dan done.
Umur job tertua: now - MIN(run_at) untuk job pending yang sudah siap.
Jumlah retry dan attempts: lonjakan retry bisa berarti worker tidak cukup cepat mengklaim ulang job.
Lock wait atau busy error: catat kejadian database locked, timeout, dan durasi tunggu.
Durasi query SQL panas: khususnya query klaim, requeue expired job, dan update status selesai.
Ukuran tabel jobs: pisahkan jumlah job aktif dan historis. Tabel yang terus tumbuh sering menjadi pemicu full scan yang sebelumnya tidak terasa.
Rencana query setelah migration: simpan output EXPLAIN QUERY PLAN untuk query kritis sebelum dan sesudah perubahan schema.

Checklist Regresi Performa

Regresi performa pada queue sering masuk lewat perubahan kecil: menambah filter, mengganti urutan sort, atau menghapus indeks yang tampak tidak dipakai. Gunakan checklist berikut sebelum merilis perubahan:

Uji dengan data representatif. Database kosong atau berisi puluhan baris tidak cukup untuk menangkap full scan.
Jalankan EXPLAIN QUERY PLAN untuk query klaim job, requeue, dan cleanup.
Pastikan tidak ada SCAN pada tabel jobs untuk query panas. Scan pada tabel kecil sementara mungkin tidak masalah, tetapi klaim job bukan query dingin.
Periksa penggunaan temporary sort. Jika muncul USE TEMP B-TREE FOR ORDER BY, pastikan dampaknya diterima atau sesuaikan indeks dan urutan query.
Verifikasi indeks komposit. Beberapa indeks satu kolom jarang setara dengan satu indeks komposit yang mengikuti pola query.
Ukur latency klaim sebelum dan sesudah perubahan. Jangan hanya mengukur throughput eksekusi job.
Cek distribusi status. Jika sebagian besar tabel berisi job done, pertimbangkan cleanup, archive, atau tabel terpisah untuk histori.
Hindari memaksa indeks di produksi tanpa alasan kuat. Klausa seperti INDEXED BY berguna untuk diagnosis, tetapi dapat membuat planner kehilangan pilihan yang lebih baik saat distribusi data berubah.

Ringkasan Praktis

Untuk mendeteksi full scan pada tabel queue worker SQLite, mulai dari query klaim job. Jalankan EXPLAIN QUERY PLAN, cari tanda SCAN jobs, lalu buat indeks komposit yang sesuai dengan filter paling penting seperti status, run_at, dan locked_until. Jika queue dipisah berdasarkan nama, sertakan queue sebagai kolom kiri saat query selalu memfilternya.

Perbaikan indeks harus disertai observability. Pantau latency klaim, lock contention, retry, queue depth, dan rencana query setelah migration. Dengan begitu, full table scan tidak hanya diperbaiki sekali, tetapi juga lebih mudah dicegah sebagai regresi performa di masa depan.

Quick Search

shopping_cart Keranjang