PostgreSQL Database Performance Tuning: Panduan Praktis

Query yang harusnya selesai 50ms tiba-tiba butuh 4 detik. Log penuh dengan slow query. Dashboard monitoring merah semua. Kalau kamu pernah di posisi ini, kamu tahu rasanya — panik, terus buka StackOverflow, terus copy-paste solusi random yang kadang bikin lebih parah.

Masalah postgresql database performance tuning sebenarnya lebih sistematis dari yang kelihatan. Bukan soal "tambah RAM" atau "pindah ke cloud yang lebih mahal". Sebagian besar bottleneck PostgreSQL bisa diselesaikan di level konfigurasi, indexing, dan cara kamu nulis query — tanpa keluar uang sepeser pun.

Artikel ini bukan pengantar teori. Ini adalah checklist yang aku pakai sendiri setiap kali ada laporan performa database di project self-hosted. PostgreSQL versi yang aku pakai sebagai referensi: 16.x (rilis Oktober 2023), tapi sebagian besar berlaku sampai versi 13 ke atas.

Masalah Pertama: Konfigurasi Default PostgreSQL Itu Buruk

Ini bukan opini — ini fakta yang bahkan didokumentasikan sendiri oleh tim PostgreSQL. File postgresql.conf yang fresh install punya nilai-nilai yang dirancang supaya bisa jalan di hardware apa pun, termasuk VPS $5 dengan 512MB RAM.

Beberapa parameter kritis yang harus kamu ubah pertama kali:

# postgresql.conf

# Sesuaikan dengan 25% total RAM
shared_buffers = 2GB

# Untuk SSD, set 1.1. Untuk HDD spinning, set 2.0
random_page_cost = 1.1

# Sesuaikan dengan jumlah CPU core
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Penting untuk analytic queries
work_mem = 64MB

# Maintenance operations (VACUUM, CREATE INDEX)
maintenance_work_mem = 512MB

# WAL settings untuk write-heavy workload
wal_buffers = 64MB
checkpoint_completion_target = 0.9

Kalau server kamu punya 8GB RAM, shared_buffers = 2GB itu titik awal yang masuk akal. Jangan set lebih dari 40% total RAM — sisanya butuh untuk OS dan koneksi aktif.

Satu tool yang aku rekomendasikan: PGTune. Masukkan spesifikasi server, dia generate konfigurasi yang reasonable. Bukan solusi ajaib, tapi jauh lebih baik dari default.

Diagnosis Dulu: Pakai pg_stat_statements

Sebelum kamu tuning apa pun, kamu harus tahu apa yang lambat. Banyak engineer langsung loncat ke solusi tanpa data. Jangan.

Aktifkan extension pg_stat_statements:

-- Di postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- Lalu di psql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Setelah restart PostgreSQL, query ini akan kasih kamu gambaran jelas:

SELECT 
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Fokus ke kolom percentage. Query yang makan 60%+ total waktu eksekusi — itu yang harus diserang duluan. Jangan buang waktu optimasi query yang cuma 0.5% dari total beban.

Index: Senjata Utama yang Sering Salah Dipakai

Index bukan solusi ajaib. Index yang salah justru memperlambat write operations dan makan storage sia-sia. Tapi index yang tepat bisa mengubah query 10 detik jadi 2ms.

Temukan Missing Index

SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY n_distinct DESC;

Lebih langsung lagi, pakai EXPLAIN ANALYZE untuk lihat apakah query kamu melakukan sequential scan di tabel besar:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'pending'
  AND created_at > NOW() - INTERVAL '30 days';

Kalau output-nya ada Seq Scan di tabel dengan jutaan row, kamu butuh index.

Composite Index: Urutan Kolom Penting

Ini yang sering salah. Kalau query kamu filter by user_id dan status, urutan kolom di composite index harus sesuai dengan selectivity-nya:

-- Lebih baik: kolom dengan cardinality tinggi duluan
CREATE INDEX CONCURRENTLY idx_orders_user_status 
ON orders (user_id, status, created_at);

-- Gunakan CONCURRENTLY supaya tidak lock table di production

Keyword CONCURRENTLY itu wajib di production. Tanpanya, PostgreSQL akan lock tabel selama proses indexing — bisa bikin downtime.

Partial Index untuk Kasus Spesifik

Kalau 90% query kamu hanya akses data dengan kondisi tertentu, partial index jauh lebih efisien:

-- Hanya index orders yang belum selesai
CREATE INDEX CONCURRENTLY idx_orders_pending 
ON orders (user_id, created_at)
WHERE status = 'pending';

Index ini ukurannya jauh lebih kecil dan lebih cepat karena cakupannya terbatas.

VACUUM dan Bloat: Masalah yang Tidak Kelihatan

PostgreSQL pakai MVCC (Multi-Version Concurrency Control). Artinya setiap UPDATE sebenarnya bikin row baru dan tandai row lama sebagai "dead". Row dead ini accumulate dan bikin tabel jadi bloated.

Cek bloat sekarang:

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Kalau dead_ratio di atas 20% di tabel yang sering di-update, kamu punya masalah bloat.

Autovacuum PostgreSQL memang ada, tapi setting default-nya konservatif. Untuk tabel high-traffic, override per-tabel:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 2
);

Ini bilang autovacuum untuk jalan lebih agresif di tabel orders — trigger setelah 1% row berubah, bukan 20% default.

Connection Pooling: Jangan Biarkan PostgreSQL Kewalahan Koneksi

Setiap koneksi ke PostgreSQL itu mahal. Tiap koneksi fork proses baru, makan ~5-10MB RAM. Kalau aplikasi kamu buka 200 koneksi sekaligus, itu 1-2GB RAM cuma buat overhead koneksi.

Solusinya: PgBouncer.

Install di server yang sama dengan PostgreSQL:

apt install pgbouncer

Konfigurasi /etc/pgbouncer/pgbouncer.ini:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling: paling efisien untuk web apps
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

Dengan konfigurasi ini, 1000 koneksi client bisa dilayani oleh cuma 25 koneksi aktif ke PostgreSQL. Perbedaannya dramatis di aplikasi dengan traffic tinggi.

Satu catatan: pool_mode = transaction tidak kompatibel dengan prepared statements di beberapa ORM. Kalau pakai Prisma atau Sequelize, cek dulu dokumentasinya.

Query Patterns yang Harus Dihindari

Konfigurasi bagus tidak akan menolong kalau query-nya memang bermasalah dari sananya.

Anti-pattern 1: SELECT * di tabel lebar

-- Jangan
SELECT * FROM users WHERE id = 123;

-- Lakukan ini
SELECT id, email, name, created_at FROM users WHERE id = 123;

Kalau tabel punya kolom avatar yang menyimpan binary data 500KB, SELECT * akan transfer data itu setiap query.

Anti-pattern 2: N+1 Query

Ini lebih ke masalah aplikasi, tapi efeknya terasa di database. Kalau kamu lihat ratusan query identik dengan parameter berbeda di pg_stat_statements, kemungkinan besar N+1.

-- Buruk: query ini dipanggil 1000x dalam loop
SELECT * FROM products WHERE id = $1;

-- Baik: ambil sekaligus
SELECT * FROM products WHERE id = ANY($1::int[]);

Anti-pattern 3: Function di WHERE clause

-- Buruk: index di created_at tidak akan dipakai
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- Baik: index bisa dipakai
SELECT * FROM orders 
WHERE created_at >= '2024-01-15' 
  AND created_at < '2024-01-16';

Kalau kamu wrap kolom dengan function, PostgreSQL tidak bisa pakai index di kolom itu — kecuali kamu bikin expression index secara eksplisit.

Perbandingan Pendekatan Tuning

Pendekatan	Effort	Impact	Risiko
Konfigurasi postgresql.conf	Rendah	Tinggi	Rendah (butuh restart)
Tambah index yang tepat	Sedang	Sangat Tinggi	Rendah (CONCURRENTLY)
PgBouncer connection pooling	Sedang	Tinggi (high traffic)	Sedang (kompatibilitas ORM)
Rewrite query bermasalah	Tinggi	Bervariasi	Rendah
Partitioning tabel besar	Tinggi	Tinggi (tabel >100GB)	Tinggi
Upgrade hardware	Rendah effort, tinggi biaya	Sedang	Rendah

Urutan prioritas aku: konfigurasi dulu, index kedua, connection pooling ketiga. Hardware upgrade itu pilihan terakhir — bukan pertama.

Monitoring yang Tidak Ribet

Tuning tanpa monitoring itu seperti nyetir sambil tutup mata. Kamu perlu tahu apakah perubahan yang kamu buat benar-benar membantu.

Setup minimal yang aku pakai untuk self-hosted PostgreSQL:

# docker-compose.yml untuk monitoring stack
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    environment:
      DATA_SOURCE_NAME: "postgresql://monitoring_user:password@postgres:5432/myapp?sslmode=disable"
    ports:
      - "9187:9187"

Sambungkan ke Prometheus + Grafana dengan dashboard PostgreSQL dari Grafana Labs. Dashboard ini langsung kasih kamu visibility ke cache hit ratio, transaction rate, lock waits, dan replication lag — semua dalam satu view.

Target cache hit ratio yang sehat: >95%. Kalau di bawah itu, shared_buffers kamu mungkin terlalu kecil.

Kalau kamu sudah setup monitoring untuk self-hosted stack lainnya, this guide on self-hosted monitoring with Prometheus and Grafana di sini bisa jadi referensi tambahan.

Kesimpulan

PostgreSQL database performance tuning bukan satu langkah — ini proses iteratif. Tapi kalau harus mulai dari satu tempat, mulai dari postgresql.conf. Konfigurasi default itu memang sengaja konservatif, dan mengubah 5-6 parameter saja sudah bisa bikin perbedaan signifikan di sebagian besar workload.

Urutan kerja yang aku sarankan:

1. Aktifkan pg_stat_statements, tunggu 24 jam, identifikasi query terburuk
2. Update postgresql.conf pakai PGTune sebagai baseline
3. Audit index — hapus yang tidak terpakai, tambah yang kurang
4. Pasang PgBouncer kalau koneksi aktif sudah di atas 50
5. Baru bicara soal hardware atau partitioning

Coba langkah pertama besok pagi. Aktifkan pg_stat_statements, jalankan query diagnosis di atas, dan lihat sendiri query mana yang paling menyedot waktu di database kamu. Data itu yang akan menentukan langkah selanjutnya — bukan asumsi.