# PostgreSQL Performance: Indexing & Query Tuning > Percepat query PostgreSQL dengan B-tree index, partial index, EXPLAIN ANALYZE, query planning, connection pooling dengan PgBouncer, dan strategi VACUUM. URL: https://www.matthewswong.com/id/blog/database-optimization-postgresql/ - Author: Matthews Wong (https://www.matthewswong.com/id/about/) - Published: 2025-03-04 - Updated: 2025-03-04 - Category: Software Development Optimasi performa PostgreSQL adalah salah satu skill yang paling berdampak bagi seorang backend developer. Satu index yang hilang dapat mengubah query 5ms menjadi table scan 5 detik. Postingan ini membahas strategi indexing esensial, interpretasi EXPLAIN ANALYZE, connection pooling dengan PgBouncer, dan strategi VACUUM yang akan membantu Anda secara sistematis mendiagnosis dan memperbaiki query lambat di database PostgreSQL produksi. ## Strategi Indexing yang Benar-Benar Penting Index adalah struktur data yang memungkinkan PostgreSQL menemukan baris tanpa memindai setiap baris dalam tabel. Mendapatkan indexing yang tepat adalah optimasi tunggal paling berdampak yang dapat Anda lakukan. Index yang salah — atau index yang hilang — bertanggung jawab atas sebagian besar masalah performa PostgreSQL. Memahami kapan dan bagaimana menggunakan B-tree index, composite index, partial index, dan expression index akan mencakup 95% use case Anda. ### B-tree, Composite, Partial, dan Expression Index B-tree index adalah default PostgreSQL dan menangani query equality (=) dan range (>, <, BETWEEN, LIKE 'prefix%') secara efisien. Composite index pada beberapa kolom sangat powerful tetapi urutan penting — letakkan kolom paling selektif terlebih dahulu, dan cocokkan left-prefix dalam query Anda. Partial index hanya mengindeks baris yang memenuhi klausa WHERE, membuatnya jauh lebih kecil dan lebih cepat untuk query yang difilter. Expression index mengindeks hasil fungsi, memungkinkan pencarian cepat pada nilai yang dihitung. > Gunakan pg_stat_user_indexes untuk mengidentifikasi index yang tidak digunakan. Index memiliki biaya write — setiap INSERT, UPDATE, dan DELETE harus memperbarui setiap index pada tabel. Index yang tidak digunakan adalah overhead murni. Audit dan hapus secara teratur, terutama setelah evolusi schema. ## Membaca Output EXPLAIN ANALYZE EXPLAIN ANALYZE adalah alat diagnostik paling powerful Anda. Ia menampilkan query plan yang dipilih PostgreSQL, estimasi vs jumlah baris aktual, dan waktu eksekusi aktual untuk setiap node dalam plan. Memahami cara membaca output EXPLAIN ANALYZE akan memungkinkan Anda mengidentifikasi penyebab tepat dari query lambat dalam hitungan menit. ### Tanda Bahaya Utama EXPLAIN ANALYZE Waspadai pola ini: Seq Scan pada tabel besar (index hilang), perbedaan besar antara estimasi dan baris aktual (statistik basi — jalankan ANALYZE), Hash Join dengan banyak batch (work_mem terlalu rendah), Nested Loop dengan banyak loop (pola N+1 dari ORM Anda), dan 'Rows Removed by Filter' tinggi relatif terhadap 'rows' yang dikembalikan (selectivity index buruk atau index hilang). ### Query Planning dan Statistik Query planner PostgreSQL menggunakan statistik kolom (dikumpulkan oleh ANALYZE) untuk memperkirakan jumlah baris dan memilih strategi join. Jika statistik Anda basi — misalnya setelah batch insert besar — planner mungkin memilih plan yang buruk. Jalankan ANALYZE setelah perubahan data yang signifikan. Tingkatkan statistics target untuk distribusi yang sangat tidak seragam. ## Connection Pooling Dengan PgBouncer Koneksi PostgreSQL berat — masing-masing menelurkan proses backend baru yang menggunakan ~5-10MB RAM. Pada skala besar, ratusan thread aplikasi yang semuanya mencoba mempertahankan koneksi persisten akan menguras memori server PostgreSQL Anda sebelum Anda mencapai batas performa query apa pun. PgBouncer menyelesaikan ini dengan melakukan pooling koneksi di layer proxy, multiplexing banyak koneksi aplikasi ke pool kecil koneksi PostgreSQL nyata. ### Mode Pool PgBouncer PgBouncer menawarkan tiga mode pool. Session mode: satu koneksi nyata per sesi client (mirip tanpa pooling). Transaction mode: koneksi nyata hanya dipegang selama durasi transaksi, lalu dikembalikan ke pool — ini adalah mode yang direkomendasikan untuk sebagian besar aplikasi web dan mendukung multiplexing tertinggi. Statement mode: paling restriktif, mengembalikan koneksi setelah setiap statement — tidak kompatibel dengan transaksi multi-statement. > **Transaction Mode PgBouncer dan Advisory Lock** > Dalam mode transaction pooling, Anda tidak dapat menggunakan fitur level sesi seperti advisory lock (pg_advisory_lock), LISTEN/NOTIFY, atau prepared statement antar transaksi — karena setiap transaksi mungkin berjalan pada koneksi nyata yang berbeda. Jika aplikasi Anda menggunakan salah satu dari ini, Anda harus mengonfigurasi PgBouncer untuk menggunakan session mode untuk koneksi tersebut, atau menggunakan pool PgBouncer terpisah dengan session mode. ### Menentukan Ukuran Connection Pool Anda Titik awal yang umum: set max_connections PostgreSQL ke 100-200, dan default_pool_size PgBouncer ke 20-25 per server aplikasi. Rumusnya adalah: pool size = (jumlah CPU * 2) + effective_spindle_count. Untuk SSD tanpa disk berputar, effective_spindle_count adalah ~1-2. Benchmarking dengan pgbench di bawah beban realistis adalah satu-satunya cara untuk memvalidasi ukuran pool Anda untuk workload spesifik Anda. ## VACUUM, Bloat, dan Tuning Autovacuum PostgreSQL menggunakan Multi-Version Concurrency Control (MVCC): update tidak menimpa baris di tempat tetapi menulis versi baru, meninggalkan dead tuple yang harus dibersihkan oleh VACUUM. Pada tabel yang banyak write, bloat dead tuple dapat berkembang secara signifikan, memperbesar ukuran tabel dan index serta memperlambat sequential scan. Memahami dan menyetel autovacuum sangat penting untuk performa produksi yang berkelanjutan. ### Monitoring dan Tuning Autovacuum Autovacuum PostgreSQL berjalan secara otomatis tetapi mungkin tidak cukup agresif untuk tabel dengan banyak write. Periksa pg_stat_user_tables untuk tabel dengan n_dead_tup tinggi atau last_autovacuum yang berjam-jam lalu pada tabel sibuk. Untuk tabel panas, turunkan autovacuum_vacuum_scale_factor dan tingkatkan autovacuum_vacuum_cost_delay untuk memungkinkan lebih banyak I/O. > Gunakan pg_stat_statements dengan RESET untuk mengukur performa query sebelum dan sesudah perubahan index. pg_stat_statements melacak total_exec_time, mean_exec_time, dan calls untuk setiap fingerprint query yang berbeda, memberi Anda bukti objektif tentang peningkatan. ## Alur Kerja Performa yang Sistematis Mulai dengan pg_stat_statements untuk mengidentifikasi query terlambat berdasarkan mean execution time. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada masing-masing. Cari index yang hilang, sequential scan pada tabel besar, dan estimasi baris yang buruk. Tambahkan index yang ditargetkan pada pola query tertentu. Ukur sebelum dan sesudah. Monitor kesehatan autovacuum setiap minggu. Sesuaikan connection pooling seiring pertumbuhan beban aplikasi Anda. Siklus ini — ukur, diagnosis, optimalkan, ukur — adalah fondasi performa PostgreSQL yang berkelanjutan. ## Source links - https://www.postgresql.org/docs/current/indexes.html - https://www.postgresql.org/docs/current/using-explain.html - https://www.pgbouncer.org/config.html - https://use-the-index-luke.com/ ## FAQ ### Apa optimasi tunggal paling berdampak yang bisa dilakukan pada query PostgreSQL yang lambat? Menambahkan index yang tepat adalah optimasi tunggal paling berdampak. Satu index yang hilang dapat mengubah query 5ms menjadi table scan 5 detik. Namun, index memiliki biaya write — setiap INSERT, UPDATE, dan DELETE harus memperbarui setiap index pada tabel — sehingga index yang tidak digunakan harus diaudit dan dihapus secara teratur menggunakan pg_stat_user_indexes. ### Mengapa EXPLAIN ANALYZE menunjukkan selisih besar antara estimasi dan jumlah baris aktual? Perbedaan besar antara estimasi dan baris aktual biasanya berarti statistik tabel Anda basi. Query planner PostgreSQL mengandalkan statistik yang dikumpulkan oleh ANALYZE untuk memperkirakan jumlah baris dan memilih strategi join. Menjalankan ANALYZE setelah perubahan data yang signifikan — seperti batch insert besar — memperbarui statistik dan membantu planner memilih plan yang efisien. ### Kapan sebaiknya menggunakan transaction mode vs session mode di PgBouncer? Transaction mode adalah pilihan yang direkomendasikan untuk sebagian besar aplikasi web karena koneksi PostgreSQL nyata hanya dipegang selama durasi transaksi, lalu dikembalikan ke pool, sehingga mendukung multiplexing tertinggi. Namun, jika aplikasi Anda menggunakan fitur level sesi seperti advisory lock (pg_advisory_lock), LISTEN/NOTIFY, atau prepared statement antar transaksi, koneksi tersebut harus menggunakan session mode — karena dalam transaction mode setiap transaksi mungkin berjalan pada koneksi nyata yang berbeda. ### Bagaimana cara menentukan ukuran connection pool PgBouncer yang tepat untuk server PostgreSQL? Titik awal yang umum adalah set max_connections PostgreSQL ke 100–200 dan default_pool_size PgBouncer ke 20–25 per server aplikasi. Rumus yang direkomendasikan adalah: pool size = (jumlah CPU × 2) + effective_spindle_count, di mana effective_spindle_count sekitar 1–2 untuk penyimpanan SSD. Post ini menekankan bahwa benchmarking dengan pgbench di bawah beban realistis adalah satu-satunya cara untuk memvalidasi ukuran pool untuk workload spesifik Anda. ### Bagaimana MVCC menyebabkan table bloat, dan bagaimana cara mengendalikannya? Model MVCC PostgreSQL menulis versi baris baru pada setiap UPDATE alih-alih menimpa di tempat, meninggalkan dead tuple. Pada tabel yang banyak write, bloat dead tuple ini memperbesar ukuran tabel dan index serta memperlambat sequential scan. Autovacuum membersihkan dead tuple secara otomatis, tetapi untuk tabel dengan banyak write Anda perlu menurunkan autovacuum_vacuum_scale_factor dan memantau pg_stat_user_tables untuk tabel dengan n_dead_tup tinggi atau timestamp last_autovacuum yang sudah berjam-jam lalu. --- - HTML version: https://www.matthewswong.com/id/blog/database-optimization-postgresql/ - English: https://www.matthewswong.com/en/blog/database-optimization-postgresql/ - Site index for agents: https://www.matthewswong.com/llms.txt - Sitemap: https://www.matthewswong.com/sitemap.xml - API description (OpenAPI): https://www.matthewswong.com/openapi.json