Masalah Index Bloat pada Status Transaksional

Pada aplikasi transaksional skala besar seperti e-commerce atau sistem pembayaran, tabel transaksi (misalnya Order atau Payment) dapat mengakumulasi jutaan record dalam hitungan bulan. Karakteristik umum data ini adalah distribusi status yang timpang: lebih dari 95% record berada dalam status terminal (COMPLETED, CANCELLED), sedangkan record aktif (PENDING, PROCESSING) biasanya kurang dari 5%.

Ketika query operasional, worker queue, atau dashboard monitoring memfilter record aktif:

Order.objects.filter(status='PENDING').order_by('created_at')

Pendekatan konvensional dengan menambahkan index standar B-tree pada field status atau (status, created_at) menyebabkan masalah efisiensi database:

  • Index Bloat: Index menyimpan pointer untuk 100% baris, termasuk jutaan baris historis yang tidak akan pernah di-query kembali sebagai status aktif.
  • Polusi Buffer Pool (RAM): Saat database memindai index B-tree besar, leaf pages yang berisi data historis ikut dimuat ke dalam memori (PostgreSQL shared_buffers), menggusur data cache yang sebenarnya sering diakses (cache thrashing).
  • Write Overhead: Setiap operasi INSERT dan UPDATE pada record terminal tetap memicu pembaruan B-tree index, memperlambat proses throughput penulisan.

Solusi arsitektural untuk problem ini adalah Partial Index (index kondisional), yang hanya mengindeks subset baris yang memenuhi klausa filter tertentu.

Implementasi Partial Index pada Django ORM

Django mendukung partial index secara native sejak versi 2.2 melalui class models.Index dengan argumen condition yang menerima objek Q().

1. Definisi Model Django

Definisikan index pada atribut Meta.indexes dengan menyertakan kondisi subset data yang diinginkan:

from django.db import models
from django.utils.translation import gettext_lazy as _

class Order(models.Model):
    class Status(models.TextChoices):
        PENDING = 'PENDING', _('Pending')
        PROCESSING = 'PROCESSING', _('Processing')
        COMPLETED = 'COMPLETED', _('Completed')
        CANCELLED = 'CANCELLED', _('Cancelled')

    order_number = models.CharField(max_length=64, unique=True)
    customer_id = models.BigIntegerField()
    status = models.CharField(max_length=20, choices=Status.choices, default=Status.PENDING)
    total_amount = models.DecimalField(max_digits=12, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            # Partial index hanya untuk status in-flight/aktif
            models.Index(
                fields=['created_at'],
                name='idx_orders_active_created',
                condition=models.Q(status__in=['PENDING', 'PROCESSING'])
            ),
        ]

2. Migrasi Database yang Dihasilkan

Jalankan python manage.py makemigrations. Django menghasilkan file migrasi berikut:

# Generated by Django
from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = [
        ('orders', '0001_initial'),
    ]

    operations = [
        migrations.AddIndex(
            model_name='order',
            index=models.Index(
                condition=models.Q(status__in=['PENDING', 'PROCESSING']),
                fields=['created_at'],
                name='idx_orders_active_created'
            ),
        ),
    ]

Ketika dieksekusi ke PostgreSQL via python manage.py migrate, DDL SQL yang dieksekusi di database adalah:

CREATE INDEX "idx_orders_active_created" 
ON "orders_order" ("created_at") 
WHERE "status" IN ('PENDING', 'PROCESSING');

Analisis EXPLAIN ANALYZE: Sebelum vs Sesudah

Pengujian dilakukan pada PostgreSQL dengan tabel berisi 5.000.000 baris, di mana hanya 25.000 baris (0.5%) berstatus PENDING atau PROCESSING.

Kondisi 1: Index Standar B-Tree pada (status, created_at)

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders_order 
WHERE status = 'PENDING' 
ORDER BY created_at ASC 
LIMIT 50;

Output eksekusi:

Limit  (cost=0.43..18.25 rows=50 width=128) (actual time=0.082..0.194 rows=50 loops=1)
  Buffers: shared hit=42 read=12
  ->  Index Scan using idx_orders_status_created on orders_order  (cost=0.43..8905.21 rows=25000 width=128) (actual time=0.081..0.187 rows=50 loops=1)
        Index Cond: (status = 'PENDING'::text)
        Buffers: shared hit=42 read=12
Planning Time: 0.145 ms
Execution Time: 0.225 ms

Ukuran Index fisik di disk: ~142 MB. Seluruh 5.000.000 entri harus dipelihara di B-tree.

Kondisi 2: Menggunakan Partial Index

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders_order 
WHERE status = 'PENDING' 
ORDER BY created_at ASC 
LIMIT 50;

Output eksekusi:

Limit  (cost=0.28..4.12 rows=50 width=128) (actual time=0.021..0.045 rows=50 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using idx_orders_active_created on orders_order  (cost=0.28..1920.10 rows=25000 width=128) (actual time=0.020..0.039 rows=50 loops=1)
        Buffers: shared hit=3
Planning Time: 0.121 ms
Execution Time: 0.068 ms

Ukuran Partial Index fisik di disk: ~1.1 MB (reduksi ukuran >99%). I/O disk turun drastis (hanya 3 page buffer hits vs 54 blocks buffer read/hit) karena tingkat kedalaman B-tree (tree height) lebih rendah dan muat sepenuhnya dalam CPU cache/L1 buffer.

Dampak Arsitektur: Buffer Pool dan Disk I/O

PostgreSQL membaca data dalam satuan halaman (default 8 KB). Pada index konvensional:

  • Tiap modifikasi baris status terminal tetap menulis pointer leaf page baru.
  • Query periodik untuk worker scheduler memaksa database me-load node-node B-tree yang bercampur dengan entri non-aktif.
  • Hal ini menghasilkan fenomena cache eviction: baris data relasional penting lainnya terbuang dari shared_buffers karena memori tersita untuk index data historis yang jarang dibaca.

Dengan partial index, hanya 25.000 baris yang diregistrasikan. Ukuran index yang sangat kecil memastikan seluruh struktur tree index menetap di RAM secara permanen tanpa mengorbankan kapasitas cache tabel lain.

Trade-off dan Karakteristik Biaya (Cost Overhead)

Partial index bukan solusi serba guna tanpa konsekuensi. Pahami trade-off berikut sebelum menerapkannya:

  • Biaya Evaluasi Predikat pada Penulisan: Saat operasi INSERT atau UPDATE terjadi, database engine harus mengevaluasi predikat WHERE index terlebih dahulu untuk menentukan apakah baris tersebut layak dimasukkan ke dalam index. Jika kondisi kompleks, terdapat penambahan komputasi CPU mikro pada setiap operasi tulis.
  • Keuntungan Terbalik pada Mutasi Terminal: Untuk jutaan transaksi yang statusnya beralih ke COMPLETED atau CANCELLED, baris tersebut secara otomatis dikeluarkan dari partial index. Database tidak perlu memperbarui index sama sekali saat record terminal dimodifikasi di kemudian hari.
  • Query Coverage Terbatas: Index ini 100% tidak berguna untuk query analitik atau pencarian historis (misal: mencari order COMPLETED milik user). Anda memerlukan index terpisah atau table partitioning jika query historis tersebut juga membutuhkan performa tinggi.

Jebakan Django ORM: Mismatched Query Predicate

Database query planner (seperti PostgreSQL planner) hanya akan menggunakan partial index jika klausa WHERE pada SQL secara matematis merupakan subset atau sama persis dengan predikat pada definisi index. Kegagalan memahami perilaku ini menyebabkan Django ORM melakukan Sequential Scan yang fatal.

Kasus 1: Query Tidak Memasukkan Klausa Status Lengkap

# SALAH: Engine tidak dapat membuktikan bahwa semua record yang diambil berstatus aktif
Order.objects.filter(created_at__gte=start_date)
# Hasil: Seq Scan pada tabel orders (Partial Index diabaikan total)
# BENAR: Engine mengenali kondisi subset dari partial index
Order.objects.filter(
    status='PENDING',
    created_at__gte=start_date
)
# Hasil: Index Scan menggunakan idx_orders_active_created

Kasus 2: Bentuk Predikat Q() yang Tidak Selaras

Jika partial index didefinisikan dengan predikat:

condition=Q(status__in=['PENDING', 'PROCESSING'])

Query ORM berikut tetap dapat menggunakan partial index:

Order.objects.filter(status='PENDING')
Order.objects.filter(status='PROCESSING')
Order.objects.filter(status__in=['PENDING', 'PROCESSING'])

Namun jika query menyertakan nilai di luar predikat:

Order.objects.filter(status__in=['PENDING', 'COMPLETED'])

PostgreSQL planner tidak dapat memanfaatkan partial index karena COMPLETED tidak ada di dalam predikat index, memaksa engine melakukan Sequential Scan atau memilih index lain.

Checklist Implementasi

  1. Pastikan rasio data yang diindeks tidak melebihi 10-15% dari total tabel untuk efektivitas maksimal.
  2. Gunakan naming convention yang jelas (misal: prefiks idx_part_ atau idx_active_) agar tim memahami batasan index tersebut.
  3. Periksa eksekusi query ORM di stage staging dengan connection.queries atau django-debug-toolbar untuk memastikan WHERE clause selalu cocok dengan partial index predikat.