Optimasi Query PostgreSQL: Index, EXPLAIN, dan Kesalahan yang Sering Terlewat

Setelah bertahun-tahun memakai PostgreSQL di proyek produksi, saya belajar satu hal: ketika query lambat, penyebabnya hampir tidak pernah PostgreSQL-nya — melainkan cara kita menulis query dan merancang skema. Berikut teknik konkret yang paling sering menyelamatkan saya.

Mulai dari EXPLAIN ANALYZE, Bukan Menebak

Kesalahan paling umum adalah mengoptimasi berdasarkan firasat. Jangan. PostgreSQL memberi tahu Anda persis apa yang dilakukannya lewat EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';

Yang perlu diperhatikan di outputnya:

• Seq Scan pada tabel besar — tanda kuat index tidak terpakai
• Rows estimasi vs aktual yang jauh berbeda — statistik tabel mungkin usang
• Execution Time yang sebenarnya, bukan sekadar biaya perkiraan

Index: Kapan, dan Kapan Tidak

Index B-tree (default) mempercepat pencarian, tapi bukan sihir gratis. Setiap index memperlambat operasi tulis dan memakan ruang. Aturan praktis:

• Beri index pada kolom yang sering dipakai di WHERE, JOIN, dan ORDER BY
• Untuk kondisi multi-kolom, gunakan composite index dengan urutan kolom yang tepat (kolom paling selektif atau yang dipakai untuk kesetaraan lebih dulu)
• Jangan meng-index kolom dengan sedikit variasi nilai (misalnya boolean) kecuali dengan partial index

Jebakan: Fungsi di Kolom Mematikan Index

Ini kesalahan yang halus tapi mahal. Query berikut TIDAK akan memakai index pada kolom email:

SELECT * FROM users WHERE LOWER(email) = 'a@mail.com';

Karena fungsi LOWER() diterapkan ke kolom, PostgreSQL tidak bisa memakai index biasa. Solusinya: buat expression index pada LOWER(email), atau simpan email dalam bentuk yang sudah dinormalisasi.

Masalah N+1: Musuh Senyap ORM

Kalau Anda memakai ORM seperti Eloquent, masalah N+1 adalah penyebab lambat nomor satu: satu query untuk mengambil daftar, lalu satu query tambahan untuk setiap baris. Solusinya adalah eager loading. Ini prinsip yang sama pentingnya seperti mengurangi request di sisi frontend, seperti saya bahas di artikel optimasi performa web.

Berhenti Memakai SELECT *

Mengambil semua kolom padahal hanya butuh tiga membebani jaringan, memori, dan bisa menggagalkan optimasi index-only scan. Ambil hanya kolom yang Anda perlukan.

Jangan Lupakan Foreign Key

PostgreSQL tidak otomatis membuat index pada kolom foreign key. Tanpa index ini, operasi JOIN dan penghapusan berkaskade bisa sangat lambat pada tabel besar. Selalu tambahkan index pada kolom FK secara manual.

Rawat Statistik Tabel

PostgreSQL mengandalkan statistik untuk memilih rencana eksekusi. Setelah operasi tulis besar, jalankan ANALYZE agar perencana query punya data terkini. Autovacuum menangani ini secara otomatis, tapi untuk beban kerja berat, memahami dan menyetelnya sangat berharga.

Kesimpulan

Optimasi query PostgreSQL bukan soal trik rahasia, tapi soal disiplin: ukur dengan EXPLAIN, pahami cara kerja index, dan hindari pola yang diam-diam mematikannya. Basis data yang cepat adalah fondasi API yang cepat — sesuatu yang tak terpisahkan dari cara Anda merancang REST API.