Audit Query Kompatibilitas: Indexing untuk Porting Aplikasi Lama

Saat aplikasi lama dipindahkan ke OS, runtime, atau stack baru, masalah performa sering muncul bukan karena fitur rusak, tetapi karena asumsi lama tentang database tidak lagi cocok. Query yang dulu terasa aman bisa menjadi lambat karena planner berbeda, pola I/O berubah, indeks tidak lagi dipakai, atau distribusi data saat ini jauh lebih besar daripada saat aplikasi pertama dibuat.

Ini mirip dengan konteks teknis porting software lintas platform seperti upaya menjalankan kompatibilitas aplikasi lama di lingkungan baru, misalnya analogi dari porting WINE ke hobby OS: kompatibilitas bukan hanya soal API tersedia, tetapi juga apakah perilaku runtime, scheduler, filesystem, dan asumsi performa masih masuk akal. Dalam database, query kompatibilitas berarti memastikan SQL lama tetap efisien di engine dan beban kerja yang baru. Di sinilah audit query kompatibilitas dan indexing menjadi penting.

Mengapa porting aplikasi lama sering memunculkan query lambat

Masalah performa setelah migrasi biasanya bukan satu bug besar, melainkan akumulasi asumsi yang dulu benar:

Planner database berubah: engine baru bisa memilih rencana eksekusi berbeda untuk query yang sama.
Pola I/O berbeda: storage, cache, filesystem, atau virtualisasi dapat mengubah biaya random read vs sequential scan.
Data bertambah: query yang dulu berjalan cepat pada ribuan baris menjadi mahal pada jutaan baris.
Index ada, tetapi tidak relevan: urutan kolom salah, selektivitas buruk, atau kondisi query tidak cocok dengan struktur index.
ORM atau driver berubah perilaku: eager loading, prepared statement, atau cara pagination dapat berubah saat runtime berganti.

Karena itu, audit performa saat porting tidak cukup hanya menguji apakah query mengembalikan hasil yang benar. Anda juga perlu memeriksa bagaimana query dieksekusi.

Fokus audit query kompatibilitas

Untuk audit yang praktis, fokus pada pola yang paling sering menyebabkan regresi setelah porting.

1. N+1 query

N+1 terjadi ketika aplikasi mengambil satu daftar data, lalu menembakkan query tambahan untuk setiap baris. Pada lingkungan lama hal ini kadang tidak terlihat karena data sedikit atau latensi rendah. Setelah porting, overhead koneksi, driver, atau scheduler bisa membuat pola ini meledak.

-- Buruk: satu query ambil order, lalu query lain per order untuk customer/details

Tanda-tandanya:

Jumlah query per request tinggi.
Latency aplikasi naik meski setiap query terlihat singkat secara individual.
Profiling menunjukkan banyak query serupa dengan parameter berbeda.

Perbaikannya biasanya dengan join yang tepat, batch fetch, atau eager loading yang terkontrol.

2. Full table scan yang tidak disengaja

Full scan tidak selalu buruk; untuk tabel kecil atau agregasi tertentu, itu bisa wajar. Masalahnya adalah full scan pada tabel besar untuk filter yang seharusnya bisa selektif. Ini sering muncul saat indeks tidak cocok dengan predicate query, atau planner menganggap index scan lebih mahal.

SELECT id, email
FROM users
WHERE status = 'active' AND created_at >= '2026-01-01';

Jika hanya ada index pada created_at tetapi mayoritas baris juga active, planner mungkin tetap memilih scan yang mahal. Di sini, desain index dan cardinality data sangat menentukan.

3. Offset pagination yang mahal

Query seperti berikut tampak sederhana, tetapi biaya meningkat seiring offset membesar:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

Database tetap harus melewati banyak baris sebelum mengembalikan hasil akhir. Saat aplikasi lama dipindahkan ke stack baru dengan dataset lebih besar, pola ini menjadi bottleneck nyata.

4. Index ada tetapi tidak terpakai

Ini sering menipu. Tim melihat index sudah dibuat, lalu menganggap masalah selesai. Padahal index bisa tidak dipakai karena:

Kolom dibungkus fungsi, misalnya LOWER(email) tanpa index yang sesuai.
Tipe data atau collation membuat predicate tidak cocok.
Urutan kolom pada composite index tidak mendukung filter dan sort.
Predicate terlalu tidak selektif.
Query menggunakan OR atau ekspresi kompleks yang mempersulit optimisasi.

5. Bottleneck yang baru terlihat saat data tumbuh

Banyak aplikasi lama didesain saat data masih kecil. Setelah porting, masalah lama yang tersembunyi menjadi terlihat karena migrasi biasanya dilakukan saat sistem juga sudah lebih besar, lebih sibuk, dan lebih sensitif terhadap lonjakan latency.

Langkah investigasi: audit sebelum menambah index

Menambahkan index secara acak bisa membantu, tetapi juga bisa memperburuk write performance dan memperbesar storage. Audit yang baik dimulai dari observasi.

1. Identifikasi query paling mahal

Kumpulkan data dari aplikasi dan database:

Latency p50, p95, p99 per endpoint atau per query.
Frekuensi query: query cepat yang dipanggil ribuan kali bisa lebih mahal daripada satu query lambat.
Total waktu kumulatif: penting untuk prioritisasi.
Rows examined vs rows returned bila tersedia.

Jangan hanya mengejar query paling lambat secara absolut. Query dengan durasi sedang tetapi dipanggil sangat sering sering menjadi target optimasi terbaik.

2. Jalankan EXPLAIN pada query kritis

EXPLAIN membantu melihat rencana eksekusi. Nama kolom output berbeda antar engine, tetapi prinsip yang dicari mirip:

Apakah planner memakai index atau full scan?
Berapa perkiraan jumlah baris yang diproses?
Apakah ada sort mahal atau temporary structure?
Apakah join order masuk akal?

EXPLAIN
SELECT id, email
FROM users
WHERE status = 'active'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 100;

Bila engine mendukung analisis runtime, gunakan varian yang menunjukkan waktu aktual dan jumlah baris nyata. Itu membantu membandingkan estimasi planner dengan kondisi riil.

3. Periksa cardinality dan selektivitas

Cardinality menjelaskan seberapa beragam nilai dalam kolom. Kolom dengan sedikit variasi, seperti status yang hanya berisi beberapa nilai, sering kurang efektif bila di-index sendirian. Tetapi kolom itu tetap berguna sebagai bagian dari composite index jika dipasangkan dengan kolom lain yang lebih selektif atau mendukung urutan sorting.

Pertanyaan yang perlu dijawab:

Apakah filter utama benar-benar menyaring banyak data?
Apakah query lebih sering memfilter, menyortir, atau keduanya?
Apakah urutan kolom pada index sesuai dengan pola akses nyata?

4. Cocokkan query aplikasi dengan pola akses produksi

Jangan audit hanya dengan data uji kecil. Query yang terlihat cepat di laptop belum tentu cocok dengan produksi. Uji dengan data yang mendekati ukuran dan distribusi riil, terutama untuk:

Tabel besar dengan data historis.
Tenant besar jika sistem multi-tenant.
Filter tanggal yang berat di rentang tertentu.
Pagination pada halaman dalam.

Strategi perbaikan yang paling sering berhasil

1. Gunakan composite index sesuai pola filter dan sort

Composite index biasanya lebih efektif daripada banyak single-column index yang tidak selaras dengan query. Misalnya, untuk query:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang lebih masuk akal biasanya mengikuti pola akses tersebut:

-- Contoh generik
CREATE INDEX idx_posts_tenant_status_created_id
ON posts (tenant_id, status, created_at, id);

Mengapa ini bekerja:

tenant_id dan status membantu menyaring subset data.
created_at dan id membantu urutan hasil yang stabil.
Planner punya peluang lebih baik untuk menghindari sort tambahan.

Trade-off: semakin banyak index, semakin mahal operasi INSERT, UPDATE, dan storage. Karena itu, index harus mengikuti query nyata, bukan daftar kolom populer.

2. Ganti offset pagination dengan keyset pagination

Untuk daftar besar, keyset pagination jauh lebih stabil daripada OFFSET. Contoh:

-- Halaman pertama
SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Halaman berikutnya, gunakan cursor dari baris terakhir halaman sebelumnya
SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND (created_at, id) < ('2026-04-01 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Mengapa ini lebih baik:

Database tidak perlu membuang ribuan baris lebih dulu.
Biaya per halaman lebih konsisten saat data tumbuh.
Lebih cocok dengan index yang juga mendukung sorting.

Keterbatasan: keyset pagination lebih sulit untuk kebutuhan “lompat ke halaman 200”, dan memerlukan urutan yang stabil serta deterministik.

3. Rewrite query agar planner lebih mudah bekerja

Terkadang masalah bukan pada kurangnya index, tetapi bentuk query yang membuat optimisasi sulit. Contoh umum:

Hindari fungsi pada kolom yang difilter jika memungkinkan.
Pecah query sangat kompleks bila join dan subquery membuat estimasi planner buruk.
Pindahkan filter agar lebih awal menyusutkan dataset.
Hindari SELECT * bila hanya butuh beberapa kolom.

Contoh rewrite sederhana:

-- Kurang ramah index
SELECT *
FROM users
WHERE LOWER(email) = LOWER(:email);

-- Lebih mudah dioptimalkan bila aplikasi menormalisasi input
SELECT id, email, name
FROM users
WHERE email = :normalized_email;

Jika kebutuhan pencarian memang case-insensitive, pertimbangkan desain kolom, normalisasi data, atau index yang sesuai dengan kemampuan engine yang dipakai.

4. Hilangkan N+1 dengan batch loading

Misalnya aplikasi memuat daftar order lalu mengambil item satu per satu. Ubah menjadi dua query terkontrol atau join yang sesuai:

-- Ambil order utama
SELECT id, customer_id, created_at
FROM orders
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC
LIMIT 100;

-- Ambil item untuk semua order terkait sekaligus
SELECT order_id, sku, quantity, price
FROM order_items
WHERE order_id IN (:order_ids);

Pendekatan ini sering lebih aman daripada join besar yang menduplikasi banyak baris, terutama bila relasi child cukup besar. Pilihan terbaik tergantung pola rendering dan kebutuhan memory di aplikasi.

5. Audit index yang tidak terpakai dan index duplikat

Saat porting, tim sering menambah index baru tanpa membersihkan yang lama. Hasilnya write path membengkak. Lakukan inventaris:

Index mana yang benar-benar dipakai query produksi?
Apakah ada index yang tercakup oleh composite index lain?
Apakah index lama dibuat untuk query yang sudah tidak ada?

Penghapusan index harus dilakukan hati-hati dan berbasis observasi, bukan asumsi.

Contoh alur audit query kompatibilitas

Berikut alur praktis yang bisa dipakai saat porting aplikasi lama:

Ambil daftar endpoint lambat dari APM, log aplikasi, atau slow query log.
Kelompokkan query berdasarkan pola, bukan hanya teks mentah. Cari N+1, pagination, dashboard agregasi, dan pencarian teks.
Ukur baseline: latency, frekuensi, rows returned, dan bila tersedia rows examined.
Jalankan EXPLAIN untuk query paling mahal secara kumulatif.
Periksa cardinality kolom filter dan urutan sort.
Usulkan satu perubahan per iterasi: tambah composite index, rewrite query, atau ubah pagination.
Uji pada data realistis dan bandingkan rencana eksekusi sebelum/sesudah.
Validasi trade-off pada write performance dan storage.

Catatan: Jangan mengoptimasi hanya berdasarkan intuisi. Query yang terlihat “teorinya buruk” bisa saja tidak relevan secara operasional, sedangkan query kecil yang sangat sering dipanggil dapat menjadi sumber bottleneck utama.

Checklist migrasi performa untuk porting aplikasi lama

Sebelum cutover

Identifikasi query kritis per fitur: login, dashboard, pencarian, listing, laporan, sinkronisasi.
Siapkan dataset uji yang mendekati ukuran dan distribusi produksi.
Bandingkan latency endpoint lama vs stack baru.
Aktifkan logging atau observabilitas untuk query lambat.
Pastikan statistik database mutakhir agar planner tidak bekerja dengan estimasi basi.

Saat audit

Cari N+1 di ORM, serializer, dan layer service.
Periksa full scan pada tabel besar.
Evaluasi semua penggunaan OFFSET besar.
Tinjau query yang memakai fungsi pada kolom filter.
Audit kecocokan index dengan query paling sering dan paling mahal.
Pastikan urutan kolom composite index selaras dengan filter dan sorting.

Sebelum produksi penuh

Uji beban pada fitur yang paling banyak membaca data.
Verifikasi bahwa penambahan index tidak merusak throughput write.
Pastikan rollback plan tersedia untuk perubahan schema dan query.
Monitor p95/p99 setelah deployment, bukan hanya rata-rata.

Kesalahan umum saat melakukan indexing untuk kompatibilitas

Menambah index untuk setiap kolom filter tanpa melihat query aktual.
Mengandalkan data staging kecil sehingga masalah cardinality tidak terlihat.
Mengoptimasi query tunggal tetapi mengabaikan total biaya per request.
Tidak memperhatikan sort order pada query listing.
Melupakan write amplification setelah menambah banyak index.
Menganggap engine baru akan otomatis lebih cepat untuk semua pola query lama.

Penutup

Audit query kompatibilitas adalah pekerjaan memastikan bahwa SQL lama tidak hanya masih jalan, tetapi juga masih efisien di lingkungan baru. Dalam porting aplikasi lama, masalah performa sering muncul dari ketidakcocokan asumsi: planner berbeda, pola I/O berubah, data membesar, dan index lama tidak lagi mewakili cara aplikasi diakses sekarang.

Mulailah dari metrik nyata, gunakan EXPLAIN, pahami cardinality, lalu perbaiki dengan pendekatan yang spesifik: composite index untuk pola filter-sort yang jelas, keyset pagination untuk listing besar, rewrite query agar planner lebih mudah bekerja, dan eliminasi N+1 di level aplikasi. Dengan pendekatan ini, porting tidak berhenti pada kompatibilitas fungsional, tetapi juga mencapai kompatibilitas performa yang benar-benar siap produksi.

Quick Search

shopping_cart Keranjang