PostgreSQL Performance: Indexing & Query Tuning

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.

-- B-tree index for equality and range queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite index: column order matters — most selective first
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index: only index active records (much smaller, faster)
CREATE INDEX idx_active_sessions ON sessions(user_id)
  WHERE expires_at > NOW();

-- Expression index: for computed WHERE clauses
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

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).

-- EXPLAIN ANALYZE: shows actual execution time and row estimates
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 20;

-- Look for these warning signs in the output:
-- Seq Scan on large tables → add an index
-- Rows Removed by Filter: high number → poor selectivity
-- Hash Join with large batches → work_mem too low
-- "actual rows=1 loops=10000" → N+1 query pattern

-- Tune work_mem for complex sorts and hash joins
SET work_mem = '64MB';  -- session-level

-- Find the slowest queries in pg_stat_statements
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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.

-- PgBouncer configuration (pgbouncer.ini)
[databases]
myapp = host=postgres port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
log_connections = 0

-- VACUUM strategies
-- Auto-vacuum handles routine bloat cleanup
-- Manual VACUUM ANALYZE after large batch operations
VACUUM (ANALYZE, VERBOSE) orders;

-- For heavily updated tables, check dead tuple ratio
SELECT relname, n_dead_tup, n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

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.

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.

Konsep performa PostgreSQL kunci dalam postingan ini meliputi B-tree index, EXPLAIN ANALYZE, PgBouncer, VACUUM, Seq Scan, and pg_stat_statements.

Artikel Terkait

• Arsitektur Software: MVC, CQRS, Event-Driven
• Strategi Migrasi Data ERP