Rust sqlx: Diagnosa Query Lambat dan Index untuk Pagination Besar

Saat tabel masih kecil, query list dengan OFFSET/LIMIT sering terlihat baik-baik saja. Masalahnya muncul ketika data tumbuh: halaman awal tetap cepat, tetapi halaman besar mulai lambat, CPU database naik, dan latency API menjadi tidak stabil. Pada aplikasi Rust dengan sqlx, akar masalahnya biasanya bukan di Rust, melainkan pada bentuk query, pemilihan index, dan cara pagination.

Artikel ini fokus pada langkah praktis untuk diagnosa query lambat dan optimasi index untuk pagination besar. Kita akan membahas gejala nyata: OFFSET yang makin mahal, kombinasi filter + sort yang tidak memakai index efektif, query COUNT(*) yang menjadi bottleneck, cara membaca EXPLAIN/EXPLAIN ANALYZE, serta contoh implementasi Rust + sqlx yang bisa langsung diterapkan.

Gejala yang Umum Terjadi Saat Data Membesar

1. OFFSET pagination makin lambat

Query seperti ini terlihat sederhana:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3 OFFSET $4;

Masalahnya, saat OFFSET besar, database tetap harus melewati banyak baris sebelum menemukan baris yang akan dikembalikan. Walaupun hasil akhirnya hanya 50 row, biaya untuk melewati puluhan atau ratusan ribu row tetap dibayar.

2. Filter dan sort tidak memakai index yang cocok

Query yang memfilter tenant_id dan status, lalu mengurutkan berdasarkan created_at DESC, id DESC, tidak akan optimal bila index hanya ada di salah satu kolom saja. Database bisa memilih:

Memakai index untuk filter, lalu melakukan sort terpisah.
Memakai index untuk sort, tetapi filter menjadi kurang efisien.
Menyerah dan melakukan scan lebih besar dari yang seharusnya.

Jika pola query utama API sudah jelas, biasanya diperlukan composite index yang mengikuti pola WHERE dan ORDER BY.

3. COUNT query ikut menjadi bottleneck

Banyak endpoint list mengembalikan data seperti ini:

Daftar item halaman saat ini
Total jumlah item untuk kebutuhan UI pagination

Query datanya mungkin masih cukup cepat, tetapi SELECT COUNT(*) ... pada filter yang sama bisa menjadi mahal ketika tabel membesar atau filter tidak selektif. Ini sering luput karena developer fokus ke query utama, padahal bottleneck justru ada di count.

Contoh Skenario Nyata

Misalkan Anda punya tabel orders untuk banyak tenant:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    customer_id BIGINT NOT NULL,
    total_amount BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    notes TEXT
);

Endpoint API:

GET /orders?status=paid&page=2000&per_page=50

Query awal:

SELECT id, created_at, status, total_amount, notes
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT $3 OFFSET $4;

Pada volume kecil, query ini mungkin tidak terlihat bermasalah. Ketika jumlah data per tenant mencapai jutaan row, gejalanya mulai muncul:

Halaman 1 cepat, halaman 2000 lambat.
Latency naik tajam saat traffic list endpoint tinggi.
Database sering melakukan sort besar atau scan row yang tidak sedikit.
Kolom notes ikut diambil padahal tidak dibutuhkan untuk list.

Empat masalah tersebut bisa terjadi bersamaan.

Cara Mendiagnosa Query Lambat dengan EXPLAIN dan EXPLAIN ANALYZE

Bedakan EXPLAIN dan EXPLAIN ANALYZE

EXPLAIN: menampilkan rencana query yang diperkirakan optimizer.
EXPLAIN ANALYZE: benar-benar mengeksekusi query dan menampilkan realisasi plan, termasuk waktu aktual dan jumlah row yang diproses.

Untuk investigasi performa, EXPLAIN ANALYZE biasanya jauh lebih berguna karena Anda melihat apakah estimasi optimizer sesuai dengan realitas.

Jalankan EXPLAIN ANALYZE di lingkungan yang aman untuk investigasi. Pada query berat, perintah ini tetap mengeksekusi query dan dapat membebani database.

Apa yang perlu dilihat

Saat membaca output, fokus pada hal berikut:

Scan type: apakah seq scan, index scan, atau bitmap scan.
Rows: berapa banyak row yang diperkirakan vs yang benar-benar diproses.
Filter: apakah filter terjadi setelah scan besar.
Sort: apakah database melakukan sort eksplisit yang mahal.
Actual time: node mana yang paling banyak memakan waktu.

Contoh interpretasi sederhana

Misalkan plan menunjukkan hal seperti:

Scan banyak row untuk tenant_id
Lalu filter status = 'paid'
Lalu sort created_at DESC
Baru setelah itu buang banyak row karena OFFSET

Artinya ada tiga sinyal penting:

Index saat ini tidak cukup membantu filter dan sort sekaligus.
OFFSET memaksa database membaca lebih banyak row dari yang dikembalikan.
Urutan kolom index kemungkinan tidak sesuai dengan pola query.

Cara praktis menguji plan dari aplikasi Rust

Saat debugging, Anda bisa menjalankan query EXPLAIN dari sqlx secara manual. Biasanya ini dilakukan pada tool admin atau test internal, bukan pada endpoint publik.

use sqlx::{PgPool, Row};

pub async fn explain_orders_query(pool: &PgPool, tenant_id: i64, status: &str) -> Result<Vec<String>, sqlx::Error> {
    let rows = sqlx::query(
        r#"
        EXPLAIN ANALYZE
        SELECT id, created_at, status, total_amount
        FROM orders
        WHERE tenant_id = $1 AND status = $2
        ORDER BY created_at DESC, id DESC
        LIMIT 50 OFFSET 50000
        "#,
    )
    .bind(tenant_id)
    .bind(status)
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|row| row.get::<String, _>(0))
        .collect())
}

Pada banyak database, hasil EXPLAIN dikembalikan per baris sebagai string. Simpan hasil ini ke log atau tampilkan pada tool internal agar bisa dibaca tim backend.

Memilih Composite Index yang Sesuai Pola WHERE dan ORDER BY

Prinsip dasar

Index yang baik bukan sekadar “semua kolom penting di-index”. Yang lebih penting adalah urutan kolom pada index sesuai dengan pola query yang paling sering dipakai.

Untuk query:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang lebih masuk akal adalah index komposit yang mendukung:

Filter kesetaraan: tenant_id, status
Sort stabil: created_at DESC, id DESC

Contoh:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);

Mengapa urutan ini membantu?

tenant_id dan status menyempitkan himpunan data lebih dulu.
Setelah itu row sudah tersusun sesuai kebutuhan ORDER BY.
Database bisa mengambil baris teratas lebih efisien tanpa sort besar.

Kesalahan umum saat membuat index

Hanya index di kolom sort: misalnya hanya created_at. Filter tetap mahal.
Hanya index di kolom filter: filter terbantu, tetapi sort tetap mahal.
Urutan kolom terbalik: misalnya menaruh kolom sort terlalu awal padahal query bergantung pada filter equality terlebih dahulu.
Terlalu banyak index mirip: menambah beban write tanpa manfaat jelas.

Jika ada banyak pola filter

Ini bagian penting. Satu index tidak selalu bisa melayani semua variasi query. Jika API Anda mendukung filter berikut:

status
customer_id
date range

maka Anda perlu melihat pola akses dominan, bukan membuat index untuk semua kombinasi secara membabi buta. Prioritaskan query yang:

Paling sering dipanggil
Paling mahal
Paling penting untuk SLA endpoint

Index tambahan selalu ada biaya: insert, update, delete menjadi lebih mahal karena setiap perubahan data harus memperbarui index terkait.

OFFSET/LIMIT vs Keyset Pagination

Kapan OFFSET/LIMIT mulai menjadi masalah

OFFSET/LIMIT masih layak untuk:

Tabel kecil atau menengah
Halaman awal
Kebutuhan UI yang benar-benar bergantung pada nomor halaman acak

Namun untuk dataset besar, terutama ketika pengguna sering membuka halaman jauh, keyset pagination hampir selalu lebih stabil.

Query OFFSET/LIMIT

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3 OFFSET $4;

Kelemahannya: semakin besar offset, semakin banyak row yang harus dilewati.

Query keyset pagination

Alih-alih meminta “halaman ke-2000”, client mengirim cursor dari item terakhir halaman sebelumnya, misalnya created_at dan id.

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND (
    created_at < $3
    OR (created_at = $3 AND id < $4)
  )
ORDER BY created_at DESC, id DESC
LIMIT $5;

Mengapa lebih cepat?

Database tidak perlu melewati row sebanyak offset.
Query dapat langsung melanjutkan dari posisi cursor.
Sangat cocok dengan index komposit (tenant_id, status, created_at DESC, id DESC).

Contoh implementasi keyset di Rust dengan sqlx

use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use sqlx::PgPool;

#[derive(Debug, FromRow, Serialize)]
pub struct OrderListItem {
    pub id: i64,
    pub created_at: NaiveDateTime,
    pub status: String,
    pub total_amount: i64,
}

#[derive(Debug, Deserialize)]
pub struct OrderCursor {
    pub created_at: NaiveDateTime,
    pub id: i64,
}

pub async fn list_orders_keyset(
    pool: &PgPool,
    tenant_id: i64,
    status: &str,
    cursor: Option<OrderCursor>,
    limit: i64,
) -> Result<Vec<OrderListItem>, sqlx::Error> {
    let limit = limit.clamp(1, 100);

    let rows = match cursor {
        Some(c) => {
            sqlx::query_as::<_, OrderListItem>(
                r#"
                SELECT id, created_at, status, total_amount
                FROM orders
                WHERE tenant_id = $1
                  AND status = $2
                  AND (
                    created_at < $3
                    OR (created_at = $3 AND id < $4)
                  )
                ORDER BY created_at DESC, id DESC
                LIMIT $5
                "#,
            )
            .bind(tenant_id)
            .bind(status)
            .bind(c.created_at)
            .bind(c.id)
            .bind(limit)
            .fetch_all(pool)
            .await?
        }
        None => {
            sqlx::query_as::<_, OrderListItem>(
                r#"
                SELECT id, created_at, status, total_amount
                FROM orders
                WHERE tenant_id = $1
                  AND status = $2
                ORDER BY created_at DESC, id DESC
                LIMIT $3
                "#,
            )
            .bind(tenant_id)
            .bind(status)
            .bind(limit)
            .fetch_all(pool)
            .await?
        }
    };

    Ok(rows)
}

Trade-off keyset pagination

Kelebihan: lebih stabil untuk data besar, cocok untuk infinite scroll dan API cursor-based.
Kekurangan: tidak nyaman untuk lompat langsung ke halaman 2000, implementasi cursor lebih kompleks, dan urutan harus stabil.

Jika produk Anda butuh pengalaman “next/previous” atau feed yang berurutan, keyset biasanya pilihan yang lebih tepat. Jika UI mewajibkan nomor halaman acak, Anda mungkin tetap memakai OFFSET tetapi perlu menerima keterbatasan performanya atau mendesain ulang UX.

Bottleneck COUNT Query dan Cara Menguranginya

Masalah umum

Endpoint list sering menjalankan dua query:

Query data halaman
Query total count

Contoh count:

SELECT COUNT(*)
FROM orders
WHERE tenant_id = $1 AND status = $2;

Pada tabel besar, query ini bisa mahal walaupun query data utama sudah memakai index yang bagus.

Pendekatan yang bisa dipilih

Hitung hanya saat dibutuhkan: jangan selalu mengembalikan total count jika UI tidak selalu memakainya.
Pisahkan endpoint count: biarkan client meminta count secara terpisah.
Gunakan estimasi atau angka tertunda: cocok bila total presisi mutlak tidak penting.
Pre-aggregation: simpan ringkasan count per tenant/status jika pola akses sangat sering dan data write masih bisa menanggung kompleksitas tambahan.

Contoh pemisahan query di Rust

pub async fn count_orders_by_status(
    pool: &PgPool,
    tenant_id: i64,
    status: &str,
) -> Result<i64, sqlx::Error> {
    let count: i64 = sqlx::query_scalar(
        r#"
        SELECT COUNT(*)
        FROM orders
        WHERE tenant_id = $1 AND status = $2
        "#,
    )
    .bind(tenant_id)
    .bind(status)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

Jangan langsung mengasumsikan query count selalu murah hanya karena “cuma satu angka”. Pada dataset besar, kerja yang dilakukan database tetap bisa besar.

Dampak SELECT Kolom Berlebihan

Masalah performa bukan hanya di WHERE dan ORDER BY. Mengambil kolom yang tidak dibutuhkan juga menambah biaya:

Lebih banyak data dibaca dari storage
Lebih banyak data dikirim dari database ke aplikasi
Lebih banyak alokasi dan decoding di sisi aplikasi Rust

Contoh query awal:

SELECT id, created_at, status, total_amount, notes
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3 OFFSET $4;

Jika endpoint list hanya menampilkan ringkasan, kolom notes sebaiknya tidak ikut diambil:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT $3 OFFSET $4;

Ini terlihat kecil, tetapi dampaknya nyata pada query yang sering dipanggil dan volume data besar, terutama bila ada kolom teks besar atau JSON yang tidak diperlukan pada list view.

Contoh Sebelum dan Sesudah Optimasi

Sebelum

SELECT id, created_at, status, total_amount, notes
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Memakai OFFSET besar
Sort tidak stabil bila banyak row memiliki created_at sama
Mengambil kolom berlebih
Belum tentu ada composite index yang sesuai

Sesudah: masih OFFSET, tetapi lebih baik

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

Perbaikan:

Index lebih cocok
Sort lebih stabil dengan tie-breaker id
Payload lebih kecil

Namun bottleneck OFFSET besar tetap ada.

Sesudah: beralih ke keyset

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND (
    created_at < $3
    OR (created_at = $3 AND id < $4)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Ini biasanya menjadi langkah paling signifikan untuk endpoint dengan pagination besar dan akses berurutan.

Checklist Investigasi yang Bisa Langsung Dipakai

Identifikasi query paling lambat
Log SQL yang lambat, ukur latency per endpoint, dan pisahkan query data dari query count.
Bandingkan halaman awal vs halaman besar
Jika halaman 1 cepat tetapi halaman 1000 lambat, curigai OFFSET.
Jalankan EXPLAIN ANALYZE
Lihat apakah terjadi seq scan besar, sort besar, atau scan row jauh lebih banyak dari hasil akhir.
Cek pola WHERE + ORDER BY
Pastikan urutan composite index sesuai pola query utama, bukan sekadar semua kolom di-index terpisah.
Kurangi kolom SELECT
Ambil hanya kolom yang benar-benar dipakai oleh response list.
Tambahkan tie-breaker pada ORDER BY
Gunakan urutan stabil seperti ORDER BY created_at DESC, id DESC agar pagination konsisten.
Uji keyset pagination
Jika endpoint sering mengakses halaman jauh, buat versi cursor-based dan bandingkan plan-nya.
Evaluasi count query
Tentukan apakah total count benar-benar harus selalu real-time dan presisi.
Perhatikan biaya write
Setiap index baru membantu read tertentu, tetapi menambah biaya insert/update/delete.

Trade-off Read Performance vs Write Cost

Menambah index hampir selalu meningkatkan performa read untuk query yang tepat. Tetapi biaya yang dibayar adalah:

Insert lebih lambat karena index harus diperbarui
Update lebih mahal bila kolom yang di-index berubah
Delete juga memerlukan pemeliharaan index
Ukuran storage bertambah

Karena itu, jangan membuat banyak index yang tumpang tindih tanpa bukti kebutuhan. Pendekatan yang sehat adalah:

Ambil query paling mahal atau paling sering
Buat index untuk pola tersebut
Verifikasi dengan EXPLAIN ANALYZE
Pantau dampaknya terhadap write path

Pada sistem dengan write throughput tinggi, satu index tambahan yang tidak tepat bisa menjadi beban permanen. Pada sistem dengan read-heavy list endpoint, index yang tepat sering memberi pengaruh jauh lebih besar daripada optimasi kecil di kode Rust.

Penutup

Pada aplikasi Rust dengan sqlx, diagnosa query lambat untuk pagination besar biasanya berujung pada tiga hal: bentuk query, desain index, dan strategi pagination. Jika OFFSET pagination makin lambat, jangan hanya menambah resource database. Lihat plan query, cek apakah filter dan sorting didukung composite index yang benar, kurangi kolom yang diambil, dan pertimbangkan beralih ke keyset pagination.

Untuk banyak kasus produksi, urutan langkah yang paling praktis adalah: ukur query lambat, baca EXPLAIN ANALYZE, perbaiki composite index, kurangi payload SELECT, lalu evaluasi apakah OFFSET harus diganti dengan cursor. Dengan pendekatan ini, optimasi yang dilakukan biasanya lebih terarah dan hasilnya lebih stabil saat data terus bertambah.

Quick Search

shopping_cart Keranjang