Belajar Database Administrator - Performance Engineering Lanjutan

#Pendahuluan

Di episode 6 kita menyelesaikan query lambat dengan index dan konfigurasi buffer; di episode 20 kita selamat dari incident. Sekarang pertanyaannya berbeda: bagaimana kalau aplikasi terus tumbuh dan database menjadi titik sempit (bottleneck) meski semua query sudah optimal?

Episode ini adalah performance engineering — melihat sistem sebagai satu kesatuan. Tidak semua beban harus dipukul database: sebagian bisa di-cache, sebagian dipisah secara fisik (partitioning), dan sebagian hasil mahal bisa disimpan (materialized views). Semua keputusan ditutup satu disiplin: benchmark yang jujur. Tanpa pengukuran, tuning hanyalah opini.

#Lapisan Cache In-Memory: Redis di Depan Database

Pertanyaan pertama saat sistem melambat: apakah datanya harus diambil dari database setiap kali? Banyak beban bersifat read-heavy dengan data yang jarang berubah — katalog produk, konfigurasi, profil. Menaruh data ini di cache in-memory seperti Redis menurunkan beban database secara drastis.

Pola paling umum adalah cache-aside:

1. Aplikasi cek Redis dulu.
2. Jika ada (hit) — langsung dipakai, database tidak tersentuh.
3. Jika tidak ada (miss) — ambil dari database, simpan ke Redis dengan TTL.

redis-cli SET product:1024 '{"name":"Meja","price":250000}' EX 300
# EX 300 = expire setelah 300 detik, mencegah data basi selamanya

Keuntungan: latensi turun dari milidetik ke mikrodetik, query database berkurang drastis untuk data panas. Kerugian: data bisa basi (stale) dan ada komponen baru yang harus dioperasikan. Tiga pitfall yang wajib dikelola:

• Stale data: TTL terlalu panjang membuat perubahan lambat terlihat; TTL pendek membuat data lebih segar tapi hit rate turun.
• Cache stampede: saat data expired dan banyak request membanjiri database bersamaan. Solusinya refresh di background (single flight), bukan refresh on-demand.
• Jangan cache data pribadi ke cache bersama: tanpa isolation yang benar, data satu user bisa bocor ke user lain.

#Table Partitioning: Memecah Tabel Raksasa

Saat sebuah tabel mencapai puluhan hingga ratusan gigabyte, index b-tree raksasa melambat dan VACUUM/backup makin berat. Solusi struktural: partitioning — memecah satu tabel logis menjadi beberapa partisi fisik.

PostgreSQL mendukung declarative partitioning. Contoh paling umum: partition per bulan berdasarkan waktu.

CREATE TABLE orders (
    id bigint,
    created_at timestamptz,
    total numeric
) PARTITION BY RANGE (created_at);
 
CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Poin penting yang harus kalian pahami:

• Transparan untuk aplikasi: query SELECT ... FROM orders WHERE created_at ... tetap sama; planner memilih partisi yang relevan (partition pruning).
• Perawatan rutin: partisi baru dibuat lewat job berkala; partisi lama bisa di-detach tanpa menghapus data — jalan menuju storage tiering di episode 23.
• Index per partisi: index dibuat per partisi, bukan di tabel utama.
• Keterbatasan: kunci asing antar partisi, constraint unik global, dan ON CONFLICT hanya valid di dalam satu partisi — baca dokumentasi sebelum mengadopsi.

Partitioning adalah keputusan arsitektur, bukan tuning — mengubah tabel besar menjadi partitioned butuh migrasi (episode 11 dan 17). Terapkan sejak desain jika jelas data akan tumbuh tak terbatas per waktu.

#Materialized Views: Menyimpan Agregasi Mahal

Dashboard dan laporan sering menghitung agregasi besar (SUM/COUNT/GROUP BY jutaan baris) setiap kali dibuka. Hasilnya jarang berubah tiap detik, tapi biaya komputasinya besar. Materialized view menyimpan hasil query sebagai tabel fisik — query mahal dihitung sekali, lalu dibaca cepat.

CREATE MATERIALIZED VIEW mv_revenue_daily AS
SELECT date_trunc('day', created_at) AS day,
       sum(total) AS revenue
FROM orders
GROUP BY 1;
 
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_revenue_daily;

CONCURRENTLY membuat refresh tidak mengunci pembacaan — wajib di produksi. Catatan penting: materialized view tidak selalu fresh; schedulkan refresh (cron atau pg_cron) sesuai kebutuhan kesegaran. Ini trade-off antara kesegaran dan performa — cocok untuk dashboard, bukan untuk transaksi real-time.

#Index Tuning Lanjutan: Partial dan Covering

Setelah index dasar (episode 4 dan 6), tiga teknik berikut menyelesaikan kasus paling umum di produksi.

Partial index — index hanya untuk subset baris yang memang dicari. Query WHERE status = 'pending' memeriksa jutaan baris padahal hanya sebagian kecil yang pending; index untuk semua baris itu boros.

CREATE INDEX idx_orders_pending ON orders (created_at)
    WHERE status = 'pending';

Covering index (INCLUDE) — menyimpan kolom ekstra agar query dilayani tanpa menyentuh tabel utama (index-only scan). Berguna untuk query yang memfilter satu kolom lalu mengambil beberapa kolom lain.

CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (total, status);
-- SELECT total, status FROM orders WHERE user_id = ?;

BRIN untuk data yang terurut secara fisik (misal kolom waktu pada tabel append-only) — jauh lebih kecil daripada b-tree dan sangat cepat untuk range scan.

Pitfall umum: terlalu banyak index. Setiap index memperlambat INSERT/UPDATE/DELETE dan memakan storage. Pakai pg_stat_user_indexes (episode 6) untuk menemukan index yang jarang digunakan dan drop yang jelas mati.

#Benchmark: Bukti, Bukan Perasaan

Semua teknik di atas harus diuji dengan disiplin:

1. Tentukan metrik: p50 dan p95 latensi, throughput (query/detik), utilisasi CPU/IO database.
2. Buat baseline: ukur kondisi sebelum perubahan.
3. Uji satu perubahan: satu variabel per percobaan, ulangi di staging dengan data yang mirip produksi.
4. Ukur p95, bukan rata-rata: rata-rata menipu saat ada outlier lambat.
5. Reproduksibel: simpan skenario, script, dan hasil di repositori.

pgbench adalah tool benchmark bawaan PostgreSQL:

pgbench -i -s 100 mydb
pgbench -c 20 -j 4 -T 60 -P 5 mydb
# -c 20 koneksi, -T 60 durasi 60 detik, -P 5 laporan tiap 5 detik

Untuk beban aplikasi nyata, gunakan skenario yang benar-benar dipakai aplikasi (misal k6 yang mengarah ke API). Teknik yang terlihat hebat di atas kertas harus lulus uji p95 di beban nyata — kalau tidak, itu bukan peningkatan, hanya kompleksitas baru.

#Penutup

Inti yang harus dibawa pulang:

• Cache in-memory (Redis, pola cache-aside + TTL) mengurangi beban database untuk data panas — kelola stale data dan stampede.
• Partitioning memecah tabel raksasa; keputusan arsitektur sejak desain, bukan tuning dadakan.
• Materialized views menyimpan agregasi mahal; refresh CONCURRENTLY untuk produksi.
• Partial/covering index dan BRIN menyelesaikan kasus spesifik; audit index yang mati.
• Benchmark jujur dengan p95 dan baseline — satu perubahan, satu pengukuran.

Di episode 22 selanjutnya kita menaikkan level arsitektur: distributed & NewSQL — CockroachDB/YugabyteDB, Vitess, dan model konsistensi — kapan satu node tidak lagi cukup dan bagaimana mengevaluasi arsitektur terdistribusi tanpa terjebak hype. Sampai jumpa di episode 22!