PostgreSQL: Yang Bikin Cepat dan yang Bikin Down

Tim dev bilang "query lemot, kita naikin RAM server". Tiga hari dan Rp 5 juta later, query masih 8 detik. Ternyata tidak ada index di kolom user_id yang di-join ke 40 juta row. PostgreSQL tidak otomatis tahu kolom mana yang sering dicari — kamu yang kasih tahu lewat index. Tapi index sembarangan malah bikin write lambat dan disk bengkak. Artikel ini tidak mendefinisikan ACID dari buku. Kita bedah keputusan nyata: kapan index bantu, gimana baca EXPLAIN, kenapa connection pool wajib, dan kesalahan yang bikin prod down.

Index: bukan jimat, tapi struktur

Index itu seperti daftar isi buku. Tanpa index, PostgreSQL baca semua baris satu per satu (sequential scan) untuk cari yang cocok. Dengan index di kolom yang sering difilter, ia bisa lompat langsung ke baris target.

-- SALAH: filter di kolom tanpa index → seq scan 40 juta row
SELECT * FROM orders WHERE user_id = 123;

-- BENAR: index di kolom filter
CREATE INDEX idx_orders_user ON orders(user_id);

Tapi: tiap index memperlambat INSERT/UPDATE/DELETE (harus update index juga) dan makan disk. Jangan index semua kolom. Index kolom yang: (1) sering di WHERE, (2) di JOIN, (3) di ORDER BY.

Baca EXPLAIN, jangan nebak

Sebelum "optimasi", lihat apa yang sebenarnya dilakukan query. EXPLAIN ANALYZE menunjukkan plan eksekusi + waktu nyata.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

Kalau output bilang Seq Scan on orders (bukan Index Scan), berarti index tidak kepakai. Penyebab umum: - Tipe data tidak cocok: WHERE user_id = '123' (string) padahal kolom int → index tidak dipakai. - Fungsi di kolom: WHERE LOWER(email) = 'a@b.com' → index di email tidak kepakai; butuh index fungsional CREATE INDEX ON users(LOWER(email)). - LIKE '%kata%' (leading wildcard) → index B-tree tidak bisa, butuh trigram (pg_trgm) atau full-text.

Alasan: optimasi tanpa EXPLAIN = menebak. Kamu bisa naikin RAM tapi plan tetap seq scan.

Composite index dan urutan

Kalau sering filter WHERE a = ? AND b = ?, index composite CREATE INDEX ON t(a,b) lebih baik dari dua index terpisah. Tapi urutan penting: kolom paling selektif (yang paling membedakan) sebaiknya di depan kalau filter partial. Untuk a = ? AND b = ?, (a,b) cocok. Untuk b = ? saja, index (a,b) tidak membantu — butuh leading column cocok.

-- cocok untuk: (a) saja, atau (a,b)
CREATE INDEX ON orders(user_id, created_at);-- tidak cocok untuk: WHERE created_at = '...' saja

Connection pool: jangan buka koneksi tiap request

Ini pembunuh prod paling sering. App buka koneksi PostgreSQL baru tiap HTTP request. PostgreSQL fork proses per koneksi (bukan thread ringan) — di 200 koneksi bersamaan, RAM meledak dan latency naik drastis. Default max_connections 100, tapi aplikasi Node/Python dengan 50 worker × 4 instance = 200 koneksi yang nge-req terus.

Fix: pakai connection pooler di depan DB: - PgBouncer (mode transaction paling efisien) — 1 pool ke DB, app buka banyak koneksi ke PgBouncer. - PgCat (modern, Rust). - Managed: Supabase/RDS punya pool endpoint terpisah.

# pgbouncer.ini[databases]app = host=db port=5432 dbname=app[pgbouncer]pool_mode = transactionmax_client_conn = 1000default_pool_size = 20

Alasan: aplikasi bisa punya 1000 koneksi logical ke PgBouncer, tapi ke PostgreSQL cuma 20 koneksi fisik. DB tidak kehabisan proses.

N+1 query: masalah di app, bukan DB

Sering query lambat bukan salah PostgreSQL, tapi pola di kode. Ambil 100 user, lalu untuk tiap user query SELECT * FROM orders WHERE user_id = ? → 101 query (1 + 100). Itu N+1.

# SALAH (N+1)users = db.query("SELECT id FROM users LIMIT 100")for u in users:    orders = db.query(f"SELECT * FROM orders WHERE user_id = {u.id}")

# BENAR: satu query dengan JOIN atau INorders = db.query("SELECT * FROM orders WHERE user_id = ANY(%s)", [user_ids])

Di ORM (Prisma/Django), pakai include / select related supaya eager load, bukan lazy. Alasan: 1 query vs 101 query = perbedaan ratusan ms ke detik.

VACUUM dan autovacuum: jangan diabaikan

PostgreSQL pakai MVCC — tiap UPDATE/DELETE meninggalkan row lama (dead tuples). Kalau tidak dibersihkan (VACUUM), tabel bengkak dan index memburuk. Autovacuum jalan otomatis, tapi untuk tabel yang sering di-update berat, bisa ketinggalan.

-- cek dead tuples
SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Kalau n_dead_tup tinggi, naikkan autovacuum_vacuum_scale_factor khusus tabel itu, atau jalankan VACUUM (ANALYZE) manual di jam sepi. Alasan: table bloat bikin seq scan makin lambat dan index tidak efektif.

Transaction dan locking: jangan bikin table lock lama

ALTER TABLE di tabel sibuk bisa lock seluruh tabel — aplikasi hang. Solusi: - Untuk add column dengan default: di PostgreSQL 11+ add column tanpa default (atau default konstan) instant, tidak rewrite. Tapi ADD COLUMN WITH DEFAULT tetap rewrite di versi lama. - Hindari LOCK TABLE eksplisit di traffic tinggi. - Pakai CONCURRENTLY untuk create/drop index supaya tidak lock write:

CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

(Perhatikan: tidak bisa di dalam transaction block.)

Alasan: DDL tanpa CONCURRENTLY = write freeze sampai selesai, bisa detik sampai menit di tabel besar.

JSONB: kapan pakai, kapan jangan

PostgreSQL punya JSONB — simpan JSON terstruktur. Berguna untuk: data yang schemanya berubah-ubah, metadata fleksibel, atau atribut produk bervariasi. Tapi jangan jadi "noSQL dalam SQL" — kalau datanya selalu di-query dengan filter tertentu, lebih baik kolom biasa + index.

-- index JSONB untuk akses cepat
CREATE INDEX ON products USING gin (attributes jsonb_path_ops);
SELECT * FROM products WHERE attributes @> '{"color":"red"}';

Alasan: JSONB tanpa GIN index = seq scan + parse JSON tiap query. GIN index mahal di write tapi cepat di read.

Replication dan backup

  • Streaming replication (primary → replica) untuk read scaling + failover. Replica bisa terima read query (SELECT), bagi beban.

  • Logical replication untuk subset tabel atau versi beda.

  • Backup: pg_dump (logical) untuk restore fleksibel, atau filesystem/physical backup (pg_basebackup + WAL archive) untuk point-in-time recovery (PITR). Managed service (RDS/Supabase) sudah sediakan, tapi verifikasi restore-nya (lihat artikel Ransomware).

Alasan: replica bukan backup. Replica ikut replicate DELETE yang salah. Backup PITR menyelamatkan dari "delete dari tabel salah".

Kesalahan yang sering lolos

  1. Filter di kolom tanpa index → seq scan lambat.

  2. Optimasi tanpa EXPLAIN → nebak, buang budget.

  3. Tipe tidak cocok di WHERE ('123' vs int) → index tidak dipakai.

  4. Buka koneksi tiap request → PostgreSQL kehabisan proses.

  5. N+1 query di app → 101 query buat 100 row.

  6. Autovacuum ketinggalan → table bloat.

  7. ALTER TABLE di traffic tinggi tanpa CONCURRENTLY → lock.

  8. JSONB tanpa GIN index → seq scan + parse.

  9. Replica dianggap backup → DELETE salah ikut replicate.

  10. SELECT * untuk semua → bandwidth + memory mubazir.

Takeaways

  • Index struktur, bukan jimat: bikin write lambat kalau kelebihan.

  • Selalu EXPLAIN ANALYZE sebelum optimasi.

  • Composite index: urutan leading column menentukan kepakai atau tidak.

  • Connection pooler (PgBouncer) wajib di production.

  • N+1 adalah bug app, fix di kode bukan DB.

  • Jaga autovacuum; cek dead tuples.

  • DDL pakai CONCURRENTLY di tabel sibuk.

  • JSONB butuh GIN index kalau di-query.

  • Replica ≠ backup; PITR untuk human error.

  • SELECT kolom spesifik, bukan *.