Root Cause: Mekanika B-Tree dan Aturan Equality-Range

Struktur data B-Tree mengurutkan data secara hierarki dari kolom pertama ke kolom berikutnya yang terdefinisi di dalam composite index. Pada index dengan definisi fields=['A', 'B'], data diurutkan berdasarkan A terlebih dahulu. Database hanya mengurutkan nilai B untuk record-record yang memiliki nilai A identik.

Ketika query menjalankan filter rentang (range scan seperti >, <, atau BETWEEN) pada kolom pertama, traversal pohon B-Tree berhenti mengeksploitasi urutan kolom kedua untuk pencarian langsung (seek). Database harus memindai seluruh rentang kolom pertama, lalu memfilter kolom kedua satu per satu di tingkat memori atau disk. Hal ini melanggar efisiensi leftmost prefix matching.

Aturan baku urutan composite index untuk query analitikal dan transaksional adalah:

  1. Equality First: Letakkan kolom yang dievaluasi dengan perbandingan eksak (=) di posisi paling kiri (leftmost).
  2. Range Scan Later: Letakkan kolom yang dievaluasi dengan perbandingan rentang (>=, <=, rentang tanggal) setelah kolom equality.

Studi Kasus: Query Filter Transaksi

Perhatikan model Django berikut yang mencatat data pembayaran:

# models.py
from django.db import models

class Transaction(models.Model):
    STATUS_CHOICES = [
        ('PENDING', 'Pending'),
        ('COMPLETED', 'Completed'),
        ('FAILED', 'Failed'),
    ]

    status = models.CharField(max_length=16, choices=STATUS_CHOICES)
    created_at = models.DateTimeField(db_index=False)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    reference_id = models.CharField(max_length=64, unique=True)

    class Meta:
        # Index keliru: menempatkan range scan sebelum equality
        indexes = [
            models.Index(fields=['created_at', 'status'], name='idx_trans_created_status'),
        ]

Query Django ORM yang dieksekusi aplikasi:

from datetime import datetime, timezone
from myapp.models import Transaction

records = Transaction.objects.filter(
    status='COMPLETED',
    created_at__gte=datetime(2025, 1, 1, tzinfo=timezone.utc),
    created_at__lte=datetime(2025, 1, 31, tzinfo=timezone.utc)
)

Query SQL yang dihasilkan:

SELECT id, status, created_at, amount, reference_id
FROM myapp_transaction
WHERE status = 'COMPLETED'
  AND created_at >= '2025-01-01 00:00:00+00'
  AND created_at <= '2025-01-31 23:59:59+00';

Analisis EXPLAIN ANALYZE: Sebelum vs Sesudah

1. Sebelum Perbaikan: Index (created_at, status)

Query planner PostgreSQL menggunakan index untuk mencari batas tanggal, namun harus memfilter nilai status secara manual pada setiap baris dalam rentang tersebut:

Bitmap Heap Scan on myapp_transaction (cost=412.30..8920.15 rows=1250 width=48) (actual time=8.210..42.890 rows=1200 loops=1)
  Recheck Cond: ((created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at <= '2025-01-31 23:59:59+00'::timestamptz))
  Filter: ((status)::text = 'COMPLETED'::text)
  Rows Removed by Filter: 48800
  Buffers: shared hit=4210 read=1890
  -> Bitmap Index Scan on idx_trans_created_status (cost=0.00..412.00 rows=50000 width=0) (actual time=7.105..7.105 rows=50000 loops=1)
        Index Cond: ((created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at <= '2025-01-31 23:59:59+00'::timestamptz))
Planning Time: 0.285 ms
Execution Time: 43.120 ms

Masalah: Index hanya mempersempit 50.000 record berdasarkan tanggal. Database membuang 48.800 record via Rows Removed by Filter karena status dievaluasi terpisah pada tingkat heap.

2. Sesudah Perbaikan: Index (status, created_at)

Ubah urutan kolom menjadi fields=['status', 'created_at']:

Index Scan using idx_trans_status_created on myapp_transaction (cost=0.42..310.20 rows=1200 width=48) (actual time=0.045..1.120 rows=1200 loops=1)
  Index Cond: (((status)::text = 'COMPLETED'::text) AND (created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at <= '2025-01-31 23:59:59+00'::timestamptz))
  Buffers: shared hit=85
Planning Time: 0.190 ms
Execution Time: 1.195 ms

Hasil: Rows Removed by Filter bernilai 0. Database langsung melompat ke node B-Tree berlabel status = 'COMPLETED', lalu menjalankan range scan hanya pada subset tanggal di dalam segmen tersebut. Total shared hit buffers turun dari 6.100 blok menjadi 85 blok.

Migrasi Skema Zero-Downtime dengan AddIndexConcurrently

Eksekusi migrasi standar Django (RunPython atau auto-generated AddIndex) menggunakan perintah PostgreSQL CREATE INDEX biasa. Operasi ini mengunci tabel dengan lock ACCESS EXCLUSIVE (atau SHARE) yang memblokir penulisan (INSERT, UPDATE, DELETE). Pada tabel produksi berukuran besar, operasi ini memicu lock timeout atau downtime aplikasi.

Gunakan operasi AddIndexConcurrently dari modul PostgreSQL bawaan Django dengan menonaktifkan transaksi atomik pada migrasi.

Langkah Implementasi Migrasi

Ubah metadata index pada models.py terlebih dahulu:

# models.py
class Transaction(models.Model):
    # ...
    class Meta:
        indexes = [
            models.Index(fields=['status', 'created_at'], name='idx_trans_status_created'),
        ]

Generate file migrasi kosong menggunakan CLI: python manage.py makemigrations myapp --empty -n add_composite_index_status_created. Tulis operasi migrasi secara manual:

# migrations/0002_add_composite_index_status_created.py
from django.contrib.postgres.operations import AddIndexConcurrently, RemoveIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    # atomic = False wajib disetel agar PostgreSQL CREATE/DROP INDEX CONCURRENTLY dapat berjalan
    atomic = False

    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        AddIndexConcurrently(
            model_name='transaction',
            index=models.Index(
                fields=['status', 'created_at'],
                name='idx_trans_status_created'
            ),
        ),
        RemoveIndexConcurrently(
            model_name='transaction',
            name='idx_trans_created_status',
        ),
    ]

Penting: AddIndexConcurrently tidak dapat dijalankan di dalam blok transaksi PostgreSQL (BEGIN ... COMMIT). Kegagalan menonaktifkan atomic = False akan memicu error: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Checklist Evaluasi Index Redundan

Penambahan index baru meningkatkan biaya write amplification (overhead disk write dan CPU pada setiap query tulis). Gunakan checklist berikut sebelum mempertahankan composite index baru:

  • Deteksi Redundansi Prefix: Jika terdapat index baru pada (status, created_at), index tunggal yang hanya mencakup (status) menjadi mubazir. Hapus index tunggal tersebut karena (status, created_at) sudah dapat melayani query yang hanya memfilter kolom status.
  • Periksa Statistik Pemakaian (PostgreSQL): Verifikasi apakah index lama masih terpakai via system catalog: 
    SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'myapp_transaction';
    Jika idx_scan bernilai nol setelah periode beban produksi tertentu, index tersebut kandidat kuat untuk dihapus.
  • Kardinalitas Kolom Equality: Jika kolom equality hanya memiliki dua nilai yang distribusinya sangat timpang (misal boolean 99% false dan 1% true), pertimbangkan penggunaan Partial Index (models.Index(..., condition=models.Q(status='PENDING'))) untuk menghemat memori cache buffer ketimbang composite index penuh.