Akar Masalah: Index Bloat pada Pola Soft Delete

Pola soft delete umumnya menggunakan kolom penanda seperti deleted_at TIMESTAMP NULL. Endpoint API pada Nuxt 3 Nitro yang mengambil data aktif mengeksekusi filter WHERE deleted_at IS NULL pada setiap query.

Ketika data bertambah hingga jutaan baris dan persentase data terhapus meningkat, index B-Tree konvensional pada kolom gabungan (misalnya (user_id, deleted_at)) mengalami index bloat. PostgreSQL tetap menyimpan seluruh baris yang sudah terhapus ke dalam struktur B-Tree. Akibatnya:

  • Ukuran file index membengkak dan membebani shared_buffers PostgreSQL.
  • I/O disk meningkat saat traversal index node karena entri data yang tidak lagi relevan tetap diinspeksi.
  • Latensi endpoint Nitro melonjak seiring bertambahnya row mati pada tabel.

Solusi: Partial Index PostgreSQL

Partial index adalah index yang dibangun dengan klausa WHERE eksplisit. Index ini hanya menyimpan baris data yang memenuhi kondisi predikat, sehingga ukurannya jauh lebih ringkas.

DDL partial index untuk data aktif:

CREATE INDEX idx_orders_user_active 
ON orders (user_id, created_at DESC) 
WHERE deleted_at IS NULL;

Index ini tidak memasukkan baris yang memiliki nilai deleted_at (data terhapus). Dampaknya, ukuran index turun signifikan, cache hit memory meningkat, dan kecepatan write (INSERT/UPDATE data terhapus) lebih efisien karena PostgreSQL tidak perlu memperbarui index ini saat record di-soft delete.

Benchmark: Analisis EXPLAIN ANALYZE

Pengujian dilakukan pada tabel orders dengan 2.500.000 baris data, di mana 1.800.000 baris memiliki status deleted_at IS NOT NULL.

1. Sebelum Optimasi (B-Tree Biasa: orders(user_id, deleted_at))

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, amount, created_at 
FROM orders 
WHERE user_id = 48210 AND deleted_at IS NULL 
ORDER BY created_at DESC 
LIMIT 20;

Hasil eksekusi:

Limit  (cost=184.20..184.25 rows=20 width=24) (actual time=14.821..14.826 rows=20 loops=1)
  Buffers: shared hit=842 read=124
  ->  Sort  (cost=184.20..185.12 rows=368 width=24) (actual time=14.819..14.822 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Bitmap Heap Scan on orders  (cost=12.45..172.10 rows=368 width=24) (actual time=1.820..14.340 rows=412 loops=1)
              Recheck Cond: ((user_id = 48210) AND (deleted_at IS NULL))
              Buffers: shared hit=842 read=124
              ->  Bitmap Index Scan on idx_orders_user_deleted  (cost=0.00..12.36 rows=368 width=0) (actual time=1.512..1.512 rows=1840 loops=1)
                    Index Cond: ((user_id = 48210) AND (deleted_at IS NULL))
Execution Time: 14.912 ms

2. Sesudah Optimasi (Partial Index: WHERE deleted_at IS NULL)

Limit  (cost=0.42..12.85 rows=20 width=24) (actual time=0.041..0.052 rows=20 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_orders_user_active on orders  (cost=0.42..228.45 rows=368 width=24) (actual time=0.039..0.048 rows=20 loops=1)
        Index Cond: (user_id = 48210)
        Buffers: shared hit=4
Execution Time: 0.071 ms

Penggunaan buffer turun drastis dari 966 blok menjadi 4 blok. Execution time terpangkas dari ~14.9 ms menjadi ~0.07 ms. PostgreSQL langsung melakukan Index Scan backward/forward tanpa perlu melakukan sorting di memori.

Implementasi di Server Route Nitro Nuxt 3

Implementasikan route handler di direktori server/api/ menggunakan Drizzle ORM atau Kysely untuk memastikan predikat SQL dikompilasi secara tepat.

File: server/api/orders/index.get.ts

import { eq, and, isNull, desc } from 'drizzle-orm';
import { db } from '~~/server/utils/db';
import { orders } from '~~/server/database/schema';

export default defineEventHandler(async (event) => {
  const query = getQuery(event);
  const userId = Number(query.userId);

  if (!userId || isNaN(userId)) {
    throw createError({
      statusCode: 400,
      statusMessage: 'Parameter userId tidak valid',
    });
  }

  // Drizzle menghasilkan: WHERE orders.user_id = $1 AND orders.deleted_at IS NULL
  const result = await db
    .select({
      id: orders.id,
      amount: orders.amount,
      createdAt: orders.createdAt,
    })
    .from(orders)
    .where(
      and(
        eq(orders.userId, userId),
        isNull(orders.deletedAt)
      )
    )
    .orderBy(desc(orders.createdAt))
    .limit(20);

  return {
    data: result,
  };
});

Jebakan Predicate Mismatch

Masalah paling umum saat menggunakan partial index dari Nuxt/Nitro adalah PostgreSQL mengabaikan index tersebut dan kembali melakukan Seq Scan. Hal ini terjadi karena Predicate Mismatch antara index definition dan query SQL yang dihasilkan ORM.

1. Parameterized NULL Binding

Kueri berikut gagal menggunakan partial index:

-- ORM salah mengikat parameter:
SELECT * FROM orders WHERE user_id = $1 AND deleted_at = $2; -- $2 diisi NULL

Penyebab: Dalam standar SQL, deleted_at = NULL menghasilkan evaluasi UNKNOWN, bukan TRUE. PostgreSQL query planner tidak dapat menyamakan ekspresi tersebut dengan predikat index deleted_at IS NULL. Gunakan operator SQL literal IS NULL.

2. Ekspresi Predikat Tidak Identik

Jika index didefinisikan sebagai:

CREATE INDEX idx_orders_active ON orders (user_id) WHERE deleted_at IS NULL;

Tetapi query builder mengeksekusi:

-- Mengabaikan partial index
SELECT * FROM orders WHERE user_id = 48210 AND (deleted_at IS NULL OR status = 'draft');

Klausa OR memperluas scope row yang dicari. PostgreSQL tidak bisa membuktikan bahwa seluruh row hasil query berada di dalam partial index, sehingga index dilewati sepenuhnya.

Verifikasi Index Hit di Database

Jalankan query analitik berikut pada PostgreSQL untuk memastikan endpoint Nitro benar-benar memicu partial index yang dibuat:

SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_user_active';

Jika nilai idx_scan bertambah setiap kali endpoint /api/orders diakses, partial index bekerja sebagaimana mestinya.