Postgres Cukup: Audit Bottleneck SQL Tanpa Tambah Stack

Sering kali gejala performa aplikasi langsung direspons dengan menambah Redis, Elasticsearch, message queue, atau bahkan database baru. Padahal, sebelum menambah stack, langkah yang paling murah dan paling sering memberi hasil besar adalah audit bottleneck SQL di PostgreSQL secara disiplin.

Gagasan Postgres is enough bukan berarti PostgreSQL cocok untuk semua masalah. Intinya: selesaikan dulu bottleneck yang memang berasal dari query, index, model akses data, dan pertumbuhan tabel. Jika query masih melakukan full scan tanpa alasan, index tidak sesuai pola filter, pagination makin lambat seiring offset membesar, atau statistik query tidak dipantau, menambah komponen baru biasanya hanya memindahkan masalah.

Mengapa audit dimulai dari PostgreSQL

PostgreSQL sudah memiliki banyak kemampuan yang sering belum dimanfaatkan penuh: planner yang matang, jenis index yang beragam, partisi, analisis statistik, agregasi kuat, dan tooling untuk membaca perilaku query. Dalam banyak sistem aplikasi, masalah nyata justru berasal dari hal-hal berikut:

Query mengambil data terlalu banyak.
Filter dan sorting tidak ditopang index yang tepat.
Join dilakukan pada kolom tanpa index.
Offset pagination makin mahal saat data tumbuh.
Tabel terus membesar tetapi pola retensi dan arsip tidak direncanakan.
Index ditambah sembarangan sampai write menjadi lambat.

Audit yang baik dimulai dari pertanyaan sederhana: query mana yang lambat, seberapa sering dijalankan, pola aksesnya seperti apa, dan apakah bottleneck ada di CPU, I/O, lock, atau transfer data.

Urutan investigasi bottleneck SQL di produksi

1. Temukan query yang paling mahal dulu

Jangan mulai dari asumsi. Kumpulkan daftar query berdasarkan frekuensi, total waktu, dan waktu rata-rata. Di PostgreSQL, praktik yang umum adalah memanfaatkan statistik query seperti pg_stat_statements bila tersedia, lalu menggabungkannya dengan metrik aplikasi dan observasi dari slow query log.

Prioritas biasanya seperti ini:

Query dengan total waktu terbesar: sering kecil per eksekusi, tetapi dampak totalnya paling besar.
Query dengan waktu rata-rata paling tinggi: biasanya kandidat bottleneck endpoint tertentu.
Query dengan jumlah pemanggilan sangat tinggi: indikasi pola N+1 atau polling berlebihan.

Metrik yang perlu dicatat untuk setiap query penting:

Jumlah eksekusi
Waktu rata-rata dan total
Rows returned
Rows scanned bila tersedia dari rencana eksekusi
Buffer hit vs buffer read
Temp file usage untuk sort atau hash yang meluap
Waktu tunggu lock

2. Ambil satu query nyata, lalu jalankan EXPLAIN ANALYZE

Setelah menemukan kandidat, ambil query yang benar-benar dijalankan aplikasi beserta parameternya. Jangan menganalisis query generik tanpa nilai parameter, karena selectivity bisa berbeda jauh. Gunakan EXPLAIN ANALYZE, idealnya dengan informasi buffer agar terlihat apakah query boros I/O.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at, status
FROM orders
WHERE user_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Yang perlu dibaca bukan hanya total waktu, tetapi juga struktur rencananya.

Cara membaca EXPLAIN ANALYZE tanpa tersesat

Pahami node dasar yang paling sering muncul

Seq Scan: PostgreSQL membaca tabel dari awal sampai akhir.
Index Scan: membaca baris lewat index lalu mengambil row dari heap/table.
Index Only Scan: bisa melayani query langsung dari index jika kondisi memungkinkan.
Bitmap Index Scan + Bitmap Heap Scan: cocok saat banyak row cocok, tetapi masih lebih efisien daripada index scan satu per satu.
Sort: data diurutkan setelah dibaca; mahal jika datanya besar atau spill ke disk.
Hash Join / Merge Join / Nested Loop: cara PostgreSQL menggabungkan tabel. Pilihan yang tepat tergantung cardinality dan index.

Bandingkan estimated rows vs actual rows

Ini salah satu sinyal terpenting. Jika planner memperkirakan 100 baris tetapi kenyataannya 100.000, ada kemungkinan statistik tidak akurat atau distribusi data tidak cocok dengan asumsi planner. Akibatnya, PostgreSQL bisa memilih strategi join atau akses data yang buruk.

Contoh gejala:

Planner memilih Nested Loop karena mengira hasil filter kecil, padahal aktualnya besar.
Planner memakai index scan yang mahal karena estimasi terlalu optimistis.
Planner menghindari index karena mengira selectivity buruk.

Dalam situasi seperti ini, langkah awal biasanya bukan menambah komponen baru, tetapi memastikan statistik tabel terbarui dan memahami distribusi data yang sebenarnya.

Perhatikan loops, rows, dan buffers

Node yang terlihat kecil bisa jadi mahal jika dijalankan berulang. Kolom loops menunjukkan berapa kali node dieksekusi. Jika ada operasi murah yang diulang puluhan ribu kali akibat join yang salah, total biayanya bisa besar.

Informasi Buffers membantu membedakan apakah query dominan di cache atau memicu banyak pembacaan disk. Secara praktis:

shared hit tinggi: data banyak terbaca dari cache.
shared read tinggi: banyak baca dari disk, indikasi I/O berat.
temp read/write muncul: sort atau hash mungkin spill ke disk.

Menemukan full scan yang memang masalah

Tidak semua Seq Scan salah. Full scan bisa masuk akal jika:

Tabel kecil.
Query memang mengambil sebagian besar isi tabel.
Biaya memakai index lalu bolak-balik ke heap lebih mahal daripada scan linear.

Yang perlu dicurigai adalah Seq Scan pada tabel besar untuk query yang seharusnya selektif. Misalnya:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM events
WHERE tenant_id = 1001
  AND created_at >= NOW() - INTERVAL '7 days';

Jika tabel events berisi ratusan juta baris dan query ini sering dipakai dashboard, full scan jelas mahal. Langkah berikutnya adalah menilai pola filter dan menentukan index yang sesuai, bukan sekadar menambah index acak.

Memilih index yang tepat, bukan yang terbanyak

Prinsip dasar: index harus mengikuti pola akses

Index berguna jika cocok dengan cara query melakukan filter, join, dan sort. Pertanyaan yang harus dijawab:

Kolom mana paling sering dipakai di WHERE?
Apakah filter berupa equality, range, atau keduanya?
Apakah hasil selalu diurutkan?
Apakah query butuh banyak kolom atau hanya sedikit?

Contoh query yang sering muncul:

SELECT id, user_id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;

Index yang lebih masuk akal:

CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

Mengapa ini bekerja:

user_id dipakai sebagai filter equality.
created_at dipakai untuk urutan hasil.
PostgreSQL bisa menemukan subset order milik user tertentu lalu membaca dalam urutan yang dibutuhkan.
LIMIT 50 menjadi murah karena database tidak perlu menyortir banyak baris dulu.

Urutan kolom pada composite index penting

Kesalahan umum adalah membuat index multikolom dengan urutan yang tidak mencerminkan pola query. Misalnya query paling sering adalah:

SELECT *
FROM invoices
WHERE tenant_id = 10
  AND status = 'open'
  AND due_date < CURRENT_DATE;

Maka index seperti ini biasanya lebih berguna:

CREATE INDEX idx_invoices_tenant_status_due_date
ON invoices (tenant_id, status, due_date);

Alasannya:

Kolom equality seperti tenant_id dan status biasanya diletakkan lebih depan.
Kolom range seperti due_date sering lebih cocok di belakang setelah equality narrowing terjadi.

Tetapi tetap verifikasi dengan EXPLAIN ANALYZE. Tidak ada aturan tunggal yang selalu benar untuk semua distribusi data.

Index berguna vs index berlebihan

Menambah index memang bisa mempercepat baca, tetapi ada biaya nyata:

INSERT, UPDATE, DELETE menjadi lebih mahal karena semua index terkait harus diperbarui.
Ukuran storage membesar.
VACUUM dan maintenance menjadi lebih berat.
Planner punya lebih banyak opsi, yang kadang memperumit diagnosis.

Index cenderung berlebihan jika:

Jarang dipakai oleh query produksi.
Duplikatif dengan index lain yang prefiksnya sama.
Dibuat untuk query yang tidak lagi relevan.
Dibuat karena dugaan, bukan hasil pengukuran.

Contoh duplikasi yang patut diaudit:

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_user_created_at ON orders (user_id, created_at DESC);

Pada beberapa beban kerja, index pertama bisa jadi tidak lagi diperlukan karena query utama sudah tercakup oleh index kedua. Tetapi keputusan menghapus harus berdasarkan observasi penggunaan query, bukan asumsi.

Gunakan partial index saat pola filter sangat spesifik

Jika sebagian besar query selalu menyasar subset kecil dan stabil, partial index bisa jauh lebih efisien daripada mengindeks semua baris.

CREATE INDEX idx_jobs_pending_created_at
ON jobs (created_at)
WHERE status = 'pending';

Ini cocok jika worker hampir selalu mengambil job pending. Keuntungannya:

Ukuran index lebih kecil.
Write overhead lebih rendah daripada full index pada seluruh tabel.
Planner punya jalur cepat untuk subset yang memang sering diakses.

Batasannya: query harus konsisten dengan predikat partial index agar planner bisa memanfaatkannya.

Anti-pattern query yang sering menjadi bottleneck

1. SELECT *

Mengambil semua kolom membuat transfer data lebih besar dan bisa menggagalkan peluang index-only scan. Ambil hanya kolom yang dibutuhkan.

2. OFFSET besar untuk pagination

Query seperti ini memburuk seiring pertumbuhan data:

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

Masalahnya: database tetap harus melangkahi banyak baris sebelum sampai ke halaman yang diminta. Solusi yang lebih stabil adalah keyset pagination.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2024-12-01 10:00:00', 500123)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dengan index yang sesuai, biaya query ini cenderung konsisten meski data terus tumbuh:

CREATE INDEX idx_posts_created_at_id_desc
ON posts (created_at DESC, id DESC);

3. Fungsi pada kolom terindeks di WHERE

Contoh:

SELECT *
FROM users
WHERE LOWER(email) = LOWER('[email protected]');

Jika query seperti ini sering dipakai, index biasa pada email mungkin tidak membantu. Solusinya bisa berupa menormalkan data saat tulis atau memakai expression index jika memang dibutuhkan. Intinya: bentuk ekspresi pada query harus cocok dengan bentuk index yang tersedia.

4. Join tanpa index pada foreign key atau kolom relasi

Join antar tabel besar tanpa index yang tepat dapat memicu scan luas dan nested loop mahal. Pastikan kolom yang sering dipakai untuk relasi dan filter memiliki dukungan index yang sesuai pola akses.

5. N+1 dari layer aplikasi

Database sering disalahkan padahal akar masalah ada di ORM atau kode aplikasi yang menembakkan query kecil ribuan kali. Cek endpoint yang melakukan loop lalu memanggil query tambahan per item. Satu query 2 ms yang dieksekusi 5.000 kali tetap menjadi masalah.

Pertumbuhan data: kapan index saja tidak cukup

Saat tabel terus membesar, bottleneck sering bergeser. Query yang tadinya cepat bisa melambat bukan karena PostgreSQL gagal, tetapi karena pola akses belum disesuaikan dengan skala data baru.

Tanda-tanda pertumbuhan data mulai menjadi isu utama

Query yang sama makin lambat dari bulan ke bulan.
VACUUM atau autovacuum makin lama.
Index membesar sampai cache hit ratio turun.
Retention data tidak jelas; semua data lama tetap aktif di tabel panas.
Pagination dengan offset makin mahal.

Langkah yang lebih murah sebelum partisi

Tentukan retensi: apakah data lama perlu tetap online di tabel yang sama.
Pisahkan jalur baca panas dan dingin di level query atau tabel arsip.
Tinjau kembali index lama yang tidak lagi memberi manfaat.
Ubah endpoint list dari offset ke keyset pagination.
Pastikan query dashboard dan laporan tidak memindai data historis tanpa batas waktu.

Kapan partisi diperlukan

Partisi bukan fitur yang otomatis membuat semua query lebih cepat. Partisi berguna jika masalah Anda memang terkait ukuran tabel besar dan pola akses yang bisa dipisah jelas, misalnya berdasarkan waktu atau tenant tertentu.

Kasus yang cocok untuk partisi

Tabel time-series atau event log yang terus bertambah.
Query hampir selalu dibatasi rentang waktu.
Perlu menghapus atau mengarsipkan data lama dengan cepat.
Maintenance pada tabel tunggal sudah terlalu berat.

Manfaat praktis partisi

Query bisa membaca partisi relevan saja jika kondisi filter cocok.
Drop partisi lama jauh lebih murah daripada delete massal.
Maintenance dan arsip data lebih terstruktur.

Batasan partisi

Desain query harus konsisten dengan key partisi agar pruning efektif.
Jumlah partisi yang berlebihan bisa menambah kompleksitas planner dan operasional.
Index tetap perlu dirancang; partisi bukan pengganti index.

Jika query utama tidak menyaring berdasarkan kolom partisi, manfaat partisi bisa kecil. Karena itu, partisi adalah keputusan arsitektural yang harus didorong oleh pola akses nyata, bukan tren.

Checklist investigasi produksi untuk audit bottleneck SQL

Gunakan checklist berikut sebelum memutuskan menambah cache, queue, search engine, atau database baru:

Identifikasi query termahal
Urutkan berdasarkan total waktu, rata-rata waktu, dan frekuensi.
Ambil query nyata beserta parameter
Jangan analisis query abstrak yang tidak mewakili produksi.
Jalankan EXPLAIN ANALYZE
Lihat node dominan, actual rows, loops, dan buffers.
Cari scan besar yang tidak perlu
Apakah ada Seq Scan pada tabel besar untuk filter selektif?
Evaluasi index terhadap pola query
Apakah urutan kolom index cocok dengan WHERE, JOIN, dan ORDER BY?
Periksa sort dan hash spill
Apakah query memakai temp file karena data terlalu besar atau result set terlalu lebar?
Periksa N+1 atau query berulang
Apakah masalah sebenarnya ada di aplikasi?
Periksa rows returned vs rows scanned
Jika yang dibaca jauh lebih besar daripada yang dipulangkan, ada peluang optimasi besar.
Tinjau pagination
Jika memakai OFFSET besar, pertimbangkan keyset pagination.
Tinjau pertumbuhan data dan retensi
Apakah tabel panas menampung data historis yang seharusnya sudah diarsipkan?
Uji perubahan secara terukur
Setelah menambah atau mengubah index, bandingkan plan dan waktu eksekusi sebelum-sesudah.

Metrik yang perlu dipantau terus, bukan hanya saat insiden

Latency query: p50, p95, p99 untuk endpoint atau query penting.
Throughput query: query per detik dan perubahan pola trafik.
Cache hit ratio: indikasi apakah workload dominan di memori atau sering ke disk.
Buffer read dan temp file usage: penting untuk mendeteksi sort/hash mahal.
Locks dan wait events: kadang bottleneck bukan query lambat, tetapi saling tunggu.
Tingkat bloat dan aktivitas vacuum: relevan pada tabel write-heavy.
Pertumbuhan ukuran tabel dan index: tren ini menentukan kapan strategi akses data perlu diubah.

Tanpa metrik, tim sering mengobati gejala secara sporadis. Dengan metrik, Anda bisa melihat apakah masalah datang dari query tertentu, perubahan distribusi data, atau beban tulis yang meningkat.

Kapan Postgres memang tidak cukup

Pendekatan Postgres cukup bukan berarti menolak tool lain. Ada kondisi ketika menambah komponen baru memang tepat.

Contoh situasi yang memang layak keluar dari PostgreSQL saja

Full-text search dengan kebutuhan ranking kompleks, typo tolerance tinggi, dan fitur pencarian lanjutan yang melampaui kebutuhan query relasional biasa.
Antrian kerja dengan throughput sangat tinggi, kebutuhan retry kompleks, fan-out besar, atau pola konsumsi yang lebih cocok untuk broker khusus.
Cache untuk data sangat panas yang dibaca sangat sering, ketika bottleneck jelas berada pada latensi baca berulang yang tidak perlu menyentuh database.
Analitik terpisah jika query OLAP berat mengganggu workload OLTP utama.
Skala distribusi khusus yang menuntut arsitektur di luar kemampuan operasional satu cluster PostgreSQL yang sehat.

Namun keputusan itu sebaiknya diambil setelah Anda bisa menunjukkan bahwa bottleneck tidak lagi berasal dari query, index, model data, atau pola akses yang masih bisa dibenahi di PostgreSQL.

Penutup

Audit bottleneck SQL tanpa tambah stack adalah pendekatan yang disiplin: ukur, baca EXPLAIN ANALYZE, cocokkan query dengan index, kurangi scan yang tidak perlu, dan sesuaikan strategi saat data tumbuh. Dalam banyak kasus, ini memberi perbaikan yang lebih nyata daripada menambah sistem baru yang memperbesar kompleksitas operasional.

Mulailah dari satu query paling mahal di produksi. Jika Anda belum tahu query mana, itu sendiri adalah masalah pertama yang harus diselesaikan. Setelah itu, keputusan teknis seperti menambah index, mengganti pagination, mengarsipkan data, atau mempartisi tabel akan jauh lebih presisi daripada sekadar menambah stack karena asumsi.

Quick Search

shopping_cart Keranjang