Pola soft delete umum digunakan untuk mempertahankan riwayat data dengan mengisi kolom penanda seperti deleted_at TIMESTAMP WITH TIME ZONE alih-alih menghapus baris fisik secara permanen melalui DELETE. Seiring berjalannya waktu, akumulasi data nonaktif menyebabkan degradasi performa query. PostgreSQL harus memindai data yang sebenarnya sudah tidak dibutuhkan oleh aplikasi.

Kombinasi Actix Web dan SQLx membutuhkan strategi pengindeksan presisi agar eksekusi query tetap berada pada latensi sub-milidetik. Artikel ini membedah implementasi partial index PostgreSQL untuk mengeliminasi beban baris terhapus pada index B-tree dan mempercepat throughput API secara signifikan.

Masalah Index Bloat pada Pola Soft Delete

Ketika Anda membuat indeks B-tree standar pada tabel dengan pola soft delete, indeks tersebut menyimpan seluruh baris data, termasuk baris dengan deleted_at IS NOT NULL:

-- Indeks standar B-tree
CREATE INDEX idx_orders_user_id ON orders(user_id);

Pada sistem transaksi bervolume tinggi, jumlah data terhapus sering kali melampaui jumlah data aktif. Struktur indeks standar memicu dua masalah teknis utama:

  • Index Bloat: Ukuran file indeks di disk membengkak karena menyimpan pointer ke baris yang tidak lagi diakses oleh query operasional. Hal ini menghabiskan kapasitas RAM pada shared_buffers PostgreSQL.
  • Buffer Cache Churn: PostgreSQL harus memuat halaman-halaman indeks yang memuat referensi data historis ke memori, menyingkirkan data aktif lainnya dari cache.
  • Query Degradation: Query dengan filter WHERE user_id = $1 AND deleted_at IS NULL tetap menelusuri leaf pages B-tree yang memuat pointer baris nonaktif sebelum menyaringnya di level heap table.

Solusi DDL: PostgreSQL Partial Index

PostgreSQL menyediakan fitur partial index, yaitu indeks yang dibangun dengan predikat klausa WHERE. Indeks ini hanya memetakan entitas baris yang memenuhi kondisi spesifik saat DDL dieksekusi.

Jalankan migrasi DDL berikut untuk membuat partial index khusus baris aktif:

-- Hapus indeks lama jika ada
DROP INDEX IF EXISTS idx_orders_user_id;

-- Buat partial index hanya untuk data aktif
CREATE INDEX idx_orders_active_user_id 
ON orders(user_id) 
WHERE deleted_at IS NULL;

Indeks hanya menyimpan kunci user_id dari baris yang nilai deleted_at-nya adalah NULL. Ukuran indeks berkurang proporsional terhadap rasio data nonaktif, dan PostgreSQL Query Planner akan otomatis memilih indeks ini jika predikat query mencakup atau mengimplikasikan kondisi deleted_at IS NULL.

Implementasi Query pada Actix Web dan SQLx

Untuk memanfaatkan partial index, klausa WHERE di query SQLx wajib menyertakan kondisi predikat yang sama persis secara semantik. Gunakan makro compile-time sqlx::query_as! untuk menjamin tipe data dan sintaks tervalidasi saat build Rust.

Berikut implementasi endpoint Actix Web untuk mengambil order aktif milik user:

use actix_web::{get, web, HttpResponse, Responder};
use chrono::{DateTime, Utc};
use serde::Serialize;
use sqlx::PgPool;
use uuid::Uuid;

#[derive(Debug, Serialize, sqlx::FromRow)]
pub struct Order {
    pub id: Uuid,
    pub user_id: Uuid,
    pub amount: i64,
    pub created_at: DateTime<Utc>,
}

#[get("/users/{user_id}/orders")]
pub async fn get_active_user_orders(
    pool: web::Data<PgPool>,
    path: web::Path<Uuid>,
) -> impl Responder {
    let user_id = path.into_inner();

    // Query ini secara eksplisit mencocokkan predikat partial index
    let result = sqlx::query_as!(
        Order,
        r#"
        SELECT id, user_id, amount, created_at
        FROM orders
        WHERE user_id = $1 AND deleted_at IS NULL
        ORDER BY created_at DESC
        LIMIT 50
        "#,
        user_id
    )
    .fetch_all(pool.get_ref())
    .await;

    match result {
        Ok(orders) => HttpResponse::Ok().json(orders),
        Err(err) => {
            log::error!("Database error: {:?}", err);
            HttpResponse::InternalServerError().finish()
        }
    }
}
Perhatian: Jika query Anda menghilangkan AND deleted_at IS NULL atau menggunakan ekspresi yang berbeda seperti COALESCE(deleted_at, ...), PostgreSQL Query Planner tidak dapat menggunakan partial index ini dan akan beralih ke Sequential Scan.

Verifikasi Eksekusi: EXPLAIN ANALYZE

Pengujian dilakukan pada tabel orders dengan total 5.000.000 baris, di mana 4.200.000 baris (84%) berstatus soft-deleted (deleted_at IS NOT NULL).

Sebelum: Indeks Penuh Standar (atau Tanpa Indeks)

EXPLAIN ANALYZE 
SELECT id, user_id, amount, created_at 
FROM orders 
WHERE user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' 
  AND deleted_at IS NULL;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on orders  (cost=4210.12..35890.45 rows=24 width=48) (actual time=14.821..48.302 rows=18 loops=1)
  Recheck Cond: (user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
  Filter: (deleted_at IS NULL)
  Rows Removed by Filter: 1420
  Buffers: shared hit=892 read=3104
  ->  Bitmap Index Scan on idx_orders_user_id  (cost=0.00..4210.11 rows=1444 width=0) (actual time=14.210..14.210 rows=1438 loops=1)
        Index Cond: (user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
Planning Time: 0.182 ms
Execution Time: 48.355 ms

Indeks membaca 1.438 pointer baris, namun 1.420 di antaranya langsung dibuang oleh filter heap (Rows Removed by Filter: 1420). Beban I/O tinggi dengan pembacaan 3.104 buffer disk.

Sesudah: Partial Index (WHERE deleted_at IS NULL)

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_orders_active_user_id on orders  (cost=0.42..8.46 rows=18 width=48) (actual time=0.034..0.052 rows=18 loops=1)
  Index Cond: (user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
  Buffers: shared hit=4
Planning Time: 0.128 ms
Execution Time: 0.071 ms

Eksekusi beralih menjadi Index Scan murni. Waktu eksekusi terpangkas dari 48.35 ms menjadi 0.07 ms (akselerasi lebih dari 600x). Tidak ada baris yang dibuang oleh filter, dan buffer memory yang diakses turun dari ~4.000 pages menjadi hanya 4 pages.

Konfigurasi Pool SQLx untuk Latensi Rendah

Query yang cepat di database bisa tertahan jika pool koneksi pada runtime Actix Web mengalami exhaustion atau lock contention. Inisialisasi PgPool pada fungsi main dengan konfigurasi koneksi yang seimbang:

use actix_web::{web, App, HttpServer};
use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

#[actix_web::main]
async fn main() -> std::io::Result<()> {
    let database_url = std::env::var("DATABASE_URL")
        .expect("DATABASE_URL environment variable must be set");

    // Tuning PgPoolOptions untuk throughput tinggi dan latensi rendah
    let pool = PgPoolOptions::new()
        .max_connections(25)
        .min_connections(5)
        .acquire_timeout(Duration::from_secs(2))
        .idle_timeout(Duration::from_secs(300))
        .max_lifetime(Duration::from_secs(1800))
        .connect(&database_url)
        .await
        .expect("Gagal menghubungkan pool database PostgreSQL");

    HttpServer::new(move || {
        App::new()
            .app_data(web::Data::new(pool.clone()))
            .service(get_active_user_orders)
    })
    .bind(("0.0.0.0", 8080))?
    .run()
    .await
}

Panduan Nilai Konfigurasi:

  • max_connections(25): Sesuaikan dengan rumus ((Core CPU Database * 2) + Disk Spindle). Nilai berlebih memicu context switching di server database.
  • min_connections(5): Menjaga sejumlah koneksi tetap warm untuk menghindari overhead TCP dan TLS handshake di awal request.
  • acquire_timeout(Duration::from_secs(2)): Membatasi waktu tunggu thread Actix Web jika pool penuh. Kegagalan cepat (fast-fail) lebih aman daripada membiarkan koneksi menumpuk hingga service mengalami starvation.