Teknik optimasi PostgreSQL yang saya gunakan di database produksi. EXPLAIN ANALYZE, strategi index, query rewrites, dan perubahan tepat yang memangkas waktu query.
Bulan lalu saya mendapat pesan Slack jam 2 pagi: "Dashboard timeout." Saya SSH ke server produksi, buka pg_stat_activity, dan menemukan satu query yang melakukan scan 14 juta baris untuk mengembalikan 12 hasil. Perbaikannya adalah partial index yang butuh 30 detik untuk dibuat. Query-nya berubah dari 4.2 detik menjadi 0.3 milidetik.
Itulah masalahnya dengan performa PostgreSQL. Masalahnya hampir tidak pernah eksotis. Itu index yang terlewat, statistik yang basi, query yang masuk akal saat tabel punya 10.000 baris tapi sekarang punya 10 juta. Perbaikannya biasanya sederhana — begitu kamu tahu di mana harus mencari.
Postingan ini adalah semua yang saya pelajari tentang optimasi query PostgreSQL dari menjalankan database produksi. Tidak ada teori tanpa praktik. Setiap teknik di sini pernah menghemat waktu nyata di sistem nyata.
Kesalahan terbesar yang saya lihat dilakukan developer dengan query lambat adalah menebak. "Mungkin kita butuh index di kolom itu." "Mungkin JOIN-nya lambat." "Mungkin kita harus tambah RAM."
Berhenti menebak. PostgreSQL punya salah satu query analyzer terbaik dari semua database. Gunakan.
EXPLAIN biasa menunjukkan apa yang PostgreSQL rencanakan untuk dilakukan, tanpa benar-benar menjalankan query:
AkousaEXPLAIN SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
Index Cond: (customer_id = 42)
Ini menunjukkan rencana — index scan — dan estimasi cost. Tapi ini prediksi. PostgreSQL bisa sepenuhnya salah tentang jumlah baris. Saya pernah melihat estimasi "1 baris" padahal hasil sebenarnya 50.000 baris. Kesalahan estimasi semacam itu merambat menjadi pilihan rencana yang buruk.
Gunakan EXPLAIN biasa saat kamu ingin melihat rencana secara cepat tanpa benar-benar menjalankan query. Ini penting saat kamu menganalisis DELETE yang akan memodifikasi data, atau query yang butuh 30 menit untuk berjalan.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
(actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.041 ms
Sekarang kamu melihat waktu eksekusi aktual dan jumlah baris aktual. Di sinilah debugging sesungguhnya terjadi. Bandingkan rows=1 (estimasi) dengan rows=1 (aktual) — keduanya cocok, jadi planner membuat keputusan yang bagus. Saat keduanya tidak cocok, itulah petunjuk pertamamu.
Peringatan: EXPLAIN ANALYZE benar-benar menjalankan query. Kalau kamu menganalisis UPDATE atau DELETE, bungkus dalam transaksi:
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;Ini yang sebenarnya saya gunakan 90% waktu:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
AND o.status = 'completed'; QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=425.00..1893.25 rows=2150 width=48)
(actual time=2.341..15.892 rows=2347 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=312 read=45
-> Bitmap Heap Scan on orders o (cost=12.50..1450.75 rows=2150 width=32)
(actual time=0.512..12.105 rows=2347 loops=1)
Recheck Cond: (created_at > '2026-01-01'::date)
Filter: (status = 'completed')
Rows Removed by Filter: 893
Heap Blocks: exact=189
Buffers: shared hit=195 read=45
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..11.96 rows=3043 width=0)
(actual time=0.287..0.287 rows=3240 loops=1)
Index Cond: (created_at > '2026-01-01'::date)
Buffers: shared hit=12
-> Hash (cost=287.50..287.50 rows=10000 width=24)
(actual time=1.753..1.753 rows=10000 loops=1)
Buffers: shared hit=117
-> Seq Scan on customers c (cost=0.00..287.50 rows=10000 width=24)
(actual time=0.008..0.892 rows=10000 loops=1)
Buffers: shared hit=117
Planning Time: 0.215 ms
Execution Time: 16.147 ms
Output BUFFERS sangat kritis. shared hit=312 berarti 312 halaman datang dari buffer cache (RAM). read=45 berarti 45 halaman harus dibaca dari disk. Kalau kamu melihat banyak read relatif terhadap hit, shared_buffers-mu mungkin terlalu kecil, atau tabelnya terlalu besar untuk tetap ter-cache.
Saya juga menggunakan FORMAT JSON saat perlu menempelkan rencana ke explain.dalibo.com untuk visualisasi. Tampilan tree membuat rencana kompleks jauh lebih mudah dibaca:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;Ini query diagnostik lengkap yang saya jalankan pertama kali untuk investigasi query lambat apa pun:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;TIMING — timing per-node (aktif secara default dengan ANALYZE, tapi saya eksplisit)VERBOSE — menampilkan daftar kolom output dan nama tabel schema-qualifiedSetiap query plan adalah tree. PostgreSQL membacanya dari bawah ke atas: node yang paling dalam dieksekusi pertama, menyuplai hasil ke atas. Memahami tipe scan adalah dasar dari segalanya.
Seq Scan on orders (cost=0.00..25000.00 rows=1000000 width=128)
Sequential scan membaca setiap baris dalam tabel. Untuk tabel sejuta baris, itu setiap halaman di disk.
Tapi ini nuansanya: Seq Scan tidak selalu buruk. Kalau kamu memilih 30% atau lebih dari tabel, sequential scan sebenarnya lebih cepat dari index scan karena sequential I/O jauh lebih cepat dari random I/O. PostgreSQL tahu ini. Kalau ia memilih Seq Scan saat kamu punya index, cek seberapa selektif klausa WHERE-mu sebenarnya.
Kapan ini menjadi masalah: saat kamu memilih sebagian kecil baris dari tabel besar dan tidak ada index.
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=128)
Index Cond: (customer_id = 42)
Pergi ke B-tree index, menemukan entri yang cocok, lalu mengambil baris sebenarnya dari table heap. Setiap pengambilan baris adalah operasi random I/O ke heap. Ini bagus untuk query yang sangat selektif tapi setiap pengambilan heap punya biaya.
Index Only Scan using idx_orders_customer_total on orders (cost=0.43..4.15 rows=5 width=12)
Index Cond: (customer_id = 42)
Heap Fetches: 0
Ini tipe scan tercepat. PostgreSQL mendapatkan semua yang dibutuhkan dari index saja, tidak pernah menyentuh table heap. Kamu melihat ini saat semua kolom yang kamu SELECT dan WHERE ada di index.
Gotcha-nya: Heap Fetches: 0 berarti visibility map sudah up to date. Kalau tabelmu punya banyak dead tuple (belum di-vacuum baru-baru ini), PostgreSQL tetap harus memeriksa heap untuk memverifikasi visibilitas baris. Ini salah satu alasan autovacuum penting untuk performa, bukan cuma ruang disk.
Bitmap Heap Scan on orders (cost=45.00..1250.00 rows=5000 width=128)
Recheck Cond: (created_at > '2026-01-01')
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..43.75 rows=5000 width=0)
Index Cond: (created_at > '2026-01-01')
Bitmap scan adalah jawaban PostgreSQL untuk masalah "terlalu banyak baris untuk index scan, terlalu sedikit untuk seq scan". Ia membangun bitmap tentang halaman mana yang berisi baris yang cocok, mengurutkannya berdasarkan lokasi fisik, lalu mengambilnya secara berurutan. Ini mengubah random I/O menjadi sequential I/O.
Kamu sering melihat bitmap scan saat dua atau lebih index dikombinasikan:
Bitmap Heap Scan on orders
Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
-> BitmapAnd
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'completed')
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at > '2026-01-01')
BitmapAnd ini menggabungkan dua index terpisah. PostgreSQL berkata "saya tidak punya composite index, tapi saya bisa menggabungkan kedua single-column index ini." Ini bekerja, tapi composite index yang tepat akan lebih cepat.
Hal nomor satu yang saya cari di query plan adalah estimasi yang tidak cocok:
Nested Loop (cost=0.43..50.12 rows=1 width=128)
(actual time=0.025..4521.337 rows=48753 loops=1)
Planner mengestimasi 1 baris. Hasil sebenarnya 48.753 baris. Itu kesalahan estimasi lima orde magnitudo. Planner memilih Nested Loop karena mengira ia melakukan join terhadap 1 baris. Dengan 48.753 baris, Hash Join akan jauh lebih cepat.
Penyebab umum estimasi buruk:
ANALYZE pada tabelstatus = 'shipped' dan created_at > '2026-01-01' berkorelasi (pesanan terbaru biasanya sudah dikirim), planner meremehkan selektivitas gabunganSaat kamu melihat estimasi buruk, perbaikannya biasanya salah satu dari: jalankan ANALYZE, buat extended statistics, tulis ulang query, atau gunakan CTE sebagai optimization fence.
-- Buat extended statistics untuk kolom yang berkorelasi
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;Index tidak gratis. Setiap index memperlambat write, mengonsumsi ruang disk, dan perlu di-maintain. Tujuannya bukan "index semuanya" — tapi "index persis apa yang dibutuhkan."
B-tree adalah default dan menangani sebagian besar kasus. Ia mendukung operator equality dan range (=, <, >, <=, >=, BETWEEN, IN, IS NULL).
CREATE INDEX idx_orders_created_at ON orders (created_at);Urutan kolom B-tree index sangat penting untuk composite index. Kolom paling kiri adalah sort primer, lalu berikutnya, dan seterusnya. Index pada (a, b, c) bisa menjawab secara efisien:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b > 5WHERE a = 1 ORDER BY bIa tidak bisa menjawab secara efisien:
WHERE b = 2 (melewati kolom pertama)WHERE c = 3 (melewati dua kolom pertama)WHERE a = 1 AND c = 3 (celah di tengah — kondisi a digunakan, kondisi c butuh filter)Bayangkan seperti buku telepon yang diurutkan berdasarkan nama belakang, lalu nama depan. Kamu bisa cepat menemukan semua entri "Smith", atau "Smith, John" secara spesifik. Tapi kamu tidak bisa cepat menemukan semua entri "John" tanpa memindai seluruh buku.
Aturannya: letakkan kondisi equality dulu, lalu kondisi range, lalu kolom sort.
-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
-- Bagus: equality dulu, lalu range, lalu sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
-- Buruk: range sebelum equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);PostgreSQL 11+ memungkinkan menambahkan kolom non-key ke index dengan INCLUDE. Kolom-kolom ini disimpan di leaf page tapi bukan bagian dari struktur B-tree. Mereka memungkinkan index-only scan tanpa membengkakkan tree:
-- Kita sering menjalankan:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);Sekarang PostgreSQL bisa memenuhi seluruh query dari index saja. Tanpa INCLUDE, ia perlu mengambil total dan status dari heap. Perbedaannya pada cold cache bisa dramatis — saya pernah melihat query berubah dari 50ms ke 0.2ms hanya dengan menambahkan kolom INCLUDE.
Ini fitur PostgreSQL favorit saya dan yang paling banyak developer tidak tahu.
-- Hanya 2% pesanan yang 'pending', tapi kita terus-menerus query mereka
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';Index ini kecil dibandingkan full index pada created_at. Ia hanya berisi baris di mana status = 'pending'. Query yang cocok dengan klausa WHERE menggunakan index kecil dan cepat ini. Query yang tidak cocok mengabaikannya.
Contoh nyata dari produksi: saya punya tabel sessions dengan 50 juta baris. Hanya sekitar 200.000 yang aktif (belum expired). Full index pada user_id berukuran 1.2 GB. Partial index WHERE expires_at > now() berukuran 8 MB. Query berubah dari 12ms ke 0.1ms karena seluruh index muat di cache.
-- Pola umum lainnya: soft delete
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
-- Unique constraint hanya pada record aktif
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;Yang terakhir itu sangat berguna. Ia memungkinkan constraint email unik pada pengguna aktif sambil mengizinkan email yang sama muncul di record yang sudah dihapus.
GIN (Generalized Inverted Index) adalah jawaban saat kamu perlu mencari di dalam nilai — array, dokumen JSONB, atau vektor full-text search.
-- Query containment JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Sekarang ini cepat:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));GIN index besar dan lambat dibangun, tapi sangat cepat di-query. Mereka layak untuk workload read-heavy dengan query containment kompleks.
Untuk JSONB, ada juga jsonb_path_ops yang lebih kecil dan lebih cepat untuk query containment @>, tapi tidak mendukung operator existence (?, ?|, ?&):
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);GiST (Generalized Search Tree) menangani tipe data yang tumpang tindih: bentuk geometri, range, full-text search (alternatif GIN, lebih kecil tapi lebih lambat).
-- Pencarian range IP
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
-- Range temporal (konflik penjadwalan)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
tstzrange(start_time, end_time)
);
-- Temukan booking yang tumpang tindih:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');Hash index hanya berguna untuk perbandingan equality. Sejak PostgreSQL 10 mereka sudah WAL-logged dan crash-safe. Mereka lebih kecil dari B-tree untuk kolom lebar dan sedikit lebih cepat untuk pure equality:
-- Bagus untuk pencarian equality-only pada kolom teks besar
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- Ini cepat:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
-- Ini TIDAK BISA menggunakan hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';Dalam praktiknya, saya jarang menggunakan hash index. B-tree menangani equality dengan baik, dan ketidakmampuan melakukan range query atau ordering membuat hash index terlalu kaku untuk kebanyakan kasus nyata.
Setiap index punya biaya:
Saya rutin mengaudit index pada tabel produksi:
-- Temukan index yang tidak terpakai
SELECT
schemaname, tablename, indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- kecualikan primary key dan unique constraint
)
ORDER BY pg_relation_size(indexrelid) DESC;Saya pernah menemukan index multi-gigabyte yang dibuat saat migrasi sekali jalan dan tidak pernah digunakan lagi. Menghapusnya mempercepat write secara nyata.
-- Temukan index duplikat (kolom sama, urutan sama)
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey = b.indkey;Setiap developer ORM pasti pernah mengalami ini. Ini masalah performa paling umum yang saya debug.
# Python / SQLAlchemy — jebakan klasik
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
# Ini memicu query terpisah untuk SETIAP order
print(f"Customer: {order.customer.name}")Ini menghasilkan:
-- Query 1: Ambil semua pesanan pending
SELECT * FROM orders WHERE status = 'pending';
-- Query 2: Ambil customer untuk order 1
SELECT * FROM customers WHERE id = 101;
-- Query 3: Ambil customer untuk order 2
SELECT * FROM customers WHERE id = 102;
-- ...Query 502: Ambil customer untuk order 500
SELECT * FROM customers WHERE id = 600;501 query alih-alih 1 atau 2. Setiap query cepat secara individual, mungkin 0.5ms. Tapi 501 query menumpuk menjadi 250ms hanya waktu database, plus latensi network round-trip untuk masing-masing.
Cara tercepat menangkap query N+1 adalah mengaktifkan statement logging sementara:
-- Log semua query yang memakan lebih dari 0ms (yaitu semua query)
SET log_min_duration_statement = 0;
-- Lebih baik untuk produksi: log hanya query lambat
ALTER SYSTEM SET log_min_duration_statement = 50; -- threshold 50ms
SELECT pg_reload_conf();Lalu lihat log-nya. N+1 tidak bisa disembunyikan — kamu akan melihat ratusan query identik dengan nilai parameter berbeda secara berurutan cepat.
Pendekatan lebih terarah untuk development:
-- Aktifkan auto_explain untuk query lambat
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100; -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;Ini mencatat output EXPLAIN ANALYZE lengkap untuk query apa pun di atas 100ms, termasuk query di dalam fungsi.
Pendekatan ORM — beritahu ORM untuk memuat data terkait di awal:
# SQLAlchemy — joinedload mengambil customer di query yang sama
from sqlalchemy.orm import joinedload
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.filter_by(status='pending')
.all()
)Pendekatan Raw SQL — cukup gunakan JOIN:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';Satu query. Selesai.
Ada debat valid di sini. Kadang dua query lebih baik dari JOIN:
-- Pendekatan 1: Single JOIN (mungkin menghasilkan data duplikat jika one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
-- Pendekatan 2: Dua query (transfer data lebih sedikit jika orders punya banyak kolom)
SELECT * FROM orders WHERE status = 'pending';
-- aplikasi mengumpulkan order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);Pendekatan JOIN menduplikasi data order untuk setiap item. Kalau setiap order punya 20 item dan baris order-nya lebar, itu 20x transfer data. Pendekatan dua query mengirim setiap order persis sekali.
Aturan saya: gunakan JOIN untuk relasi one-to-one, pertimbangkan query terpisah untuk one-to-many saat sisi "one" lebar. Tapi selalu benchmark — network round-trip dari query kedua sering lebih mahal dari data duplikat.
Beberapa query lambat bukan karena index yang hilang tapi karena cara penulisannya. Planner PostgreSQL bagus, tapi bukan sihir.
Tiga pendekatan ini bisa menghasilkan plan yang sangat berbeda:
-- Subquery di WHERE — sering fine, kadang buruk
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US'
);
-- JOIN — biasanya pilihan terbaik
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
-- CTE — dimaterialisasi di PG 11 ke bawah, dioptimasi di PG 12+
WITH us_customers AS (
SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;Di PostgreSQL 12+, CTE biasanya di-inline (planner memperlakukan seperti subquery), jadi performanya identik. Tapi di PostgreSQL 11 ke bawah, CTE adalah optimization fence — planner mematerialisasinya dan tidak bisa mendorong predikat melewatinya. Kalau kamu masih di PG 11, hindari CTE untuk query kritis performa.
Ini terus muncul:
-- EXISTS — berhenti di match pertama, bagus untuk mengecek keberadaan
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM returns r WHERE r.order_id = o.id
);
-- IN dengan subquery — membangun seluruh result set dulu
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
-- JOIN — bisa menghasilkan duplikat jika returns punya banyak baris per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;Untuk tabel besar, EXISTS sering menang karena short-circuit. Versi IN harus membangun seluruh daftar return order_id sebelum memfilter. Versi JOIN bisa menghasilkan duplikat, membutuhkan DISTINCT yang menambahkan langkah sort atau hash.
Default saya: gunakan EXISTS saat mengecek keberadaan baris terkait. Ini paling jelas secara semantik dan biasanya paling cepat.
Tapi ada counterexample. Kalau result set subquery kecil dan kamu membutuhkannya untuk banyak kondisi:
-- Kalau subquery mengembalikan sedikit baris, IN perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);Ini bukan cuma preferensi code style. Ini punya implikasi performa nyata:
-- Buruk: mengambil semua 25 kolom termasuk kolom TEXT dengan kilobyte data
SELECT * FROM products WHERE category = 'electronics';
-- Baik: mengambil hanya yang dibutuhkan
SELECT id, name, price FROM products WHERE category = 'electronics';Dengan SELECT *:
Window function adalah salah satu fitur PostgreSQL paling powerful dan hampir selalu lebih cepat dari correlated subquery:
-- Lambat: correlated subquery berjalan sekali per baris
SELECT
o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
(SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
-- Cepat: window function menghitung dalam satu pass
SELECT
o.*,
COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';Pola umum lainnya — mendapatkan baris terbaru per grup:
-- Lambat: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
SELECT MAX(created_at) FROM notifications
WHERE user_id = n.user_id
);
-- Cepat: DISTINCT ON (spesifik PostgreSQL, sangat berguna)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
-- Juga cepat: ROW_NUMBER window function
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM notifications
) sub
WHERE rn = 1;DISTINCT ON adalah andalan saya untuk pola ini. Ringkas, mudah dibaca, dan PostgreSQL mengoptimasinya dengan baik dengan index yang tepat:
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);OFFSET adalah jebakan untuk dataset besar:
-- Halaman 1: cepat
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Halaman 500: lambat — PostgreSQL harus scan dan buang 9.980 baris
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
-- Halaman 5000: sangat lambat — scan 99.980 baris untuk mengembalikan 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;Perbaikannya adalah keyset pagination (juga disebut cursor-based pagination):
-- Halaman pertama
SELECT * FROM products ORDER BY id LIMIT 20;
-- Halaman berikutnya: gunakan id terakhir dari halaman sebelumnya
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;Ini selalu cepat karena menggunakan index untuk melompat langsung ke posisi yang tepat, terlepas dari "halaman" mana kamu berada. Trade-off-nya adalah kamu tidak bisa melompat ke nomor halaman sembarang, tapi untuk infinite scroll atau UI "halaman berikutnya", keyset pagination secara tegas lebih unggul.
Untuk urutan sort yang kompleks:
-- Keyset pagination dengan banyak kolom sort
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;Ini menggunakan perbandingan row-value, yang PostgreSQL tangani secara efisien dengan composite index pada (price, id).
Query planner PostgreSQL membuat keputusan berdasarkan statistik tentang datamu. Statistik buruk menghasilkan plan buruk. Sesederhana itu.
-- Analyze satu tabel
ANALYZE orders;
-- Analyze seluruh database
ANALYZE;
-- Lihat statistik saat ini untuk kolom
SELECT
tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';Nilai correlation sangat menarik. Berkisar dari -1 ke 1 dan mengukur seberapa baik urutan fisik baris cocok dengan urutan logis kolom. Korelasi mendekati 1 atau -1 berarti data secara fisik terurut pada kolom tersebut, membuat range scan sangat efisien (sequential I/O). Korelasi mendekati 0 berarti random I/O untuk range query.
-- Tingkatkan statistics target untuk kolom dengan distribusi miring
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;Statistics target default adalah 100, berarti PostgreSQL menyampel 300 * 100 = 30.000 baris. Untuk kolom dengan banyak nilai distinct atau distribusi miring, menaikkan ini ke 500 atau 1000 memberi planner data lebih baik dengan biaya waktu ANALYZE sedikit lebih lama.
Autovacuum melakukan dua hal: merebut kembali ruang dari dead tuple (baris yang dihapus atau diupdate) dan mengupdate statistik. Pada tabel sibuk, setting autovacuum default sering terlalu konservatif.
-- Cek status autovacuum
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;Kalau kamu melihat tabel dengan jutaan dead tuple dan vacuum terakhir berjam-jam yang lalu, autovacuum-mu tertinggal.
Untuk tabel high-churn (seperti session, job queue, atau metrik), saya set pengaturan autovacuum per-tabel:
-- Autovacuum lebih agresif untuk tabel high-write
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum di 1% dead tuple (default 20%)
autovacuum_analyze_scale_factor = 0.005, -- analyze di 0.5% perubahan (default 10%)
autovacuum_vacuum_cost_delay = 2 -- vacuum lebih cepat (default 2ms di PG baru)
);Saat PostgreSQL mengupdate baris, ia tidak memodifikasi baris di tempat — ia membuat versi baru dan menandai yang lama sebagai dead. Vacuum merebut kembali baris dead, tapi ruangnya hanya dipakai ulang oleh tabel itu. File tabel di disk tidak menyusut.
Seiring waktu, tabel yang sering diupdate bisa punya bloat signifikan — tabel jauh lebih besar di disk dari yang dibutuhkan data hidup. Ini berarti lebih banyak halaman untuk di-scan, lebih banyak I/O, lebih banyak tekanan cache.
-- Estimasi table bloat (versi disederhanakan)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(
pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)
) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;Untuk tabel yang sangat bloated, opsi nuklir adalah VACUUM FULL — ia menulis ulang seluruh tabel. Tapi ia mengambil exclusive lock, jadi tidak bisa dilakukan di tabel produksi live tanpa downtime. Pendekatan yang lebih baik adalah pg_repack, yang melakukan hal sama tanpa locking:
pg_repack --table orders --no-kill-backend -d mydbIni sesuatu yang mengejutkan banyak developer: koneksi PostgreSQL itu mahal. Setiap koneksi memunculkan proses baru (bukan thread), mengonsumsi sekitar 5-10 MB memori, dan punya overhead fork yang tidak trivial.
Default max_connections adalah 100. Kalau kamu punya application server dengan 20 worker, masing-masing membuka 5 koneksi, kamu sudah di limit. Tambahkan background job processor, monitoring tool, dan migrasi yang berjalan di suatu tempat, dan kamu dalam masalah.
Tanpa pooler, kalau aplikasimu perlu menangani 500 request bersamaan, kamu butuh 500 koneksi PostgreSQL. Itu 5 GB memori hanya untuk overhead koneksi, dan performa PostgreSQL menurun signifikan di atas beberapa ratus koneksi karena overhead manajemen proses.
Dengan PgBouncer di depan PostgreSQL, 500 koneksi aplikasi itu dipetakan ke mungkin 20 koneksi PostgreSQL sebenarnya. Pooler mengantrekan request saat semua koneksi database sibuk.
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Pool mode
pool_mode = transaction
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30pool_mode = transaction): Koneksi dikembalikan ke pool setelah setiap transaksi. Ini yang kamu mau 95% waktu. Penggunaan ulang koneksi maksimal.pool_mode = session): Koneksi dipegang selama seluruh sesi client. Gunakan ini jika kamu butuh prepared statement, perintah SET, LISTEN/NOTIFY, atau fitur level sesi lainnya.pool_mode = statement): Koneksi dikembalikan setelah setiap statement. Terlalu ketat untuk kebanyakan aplikasi — kamu bahkan tidak bisa menggunakan transaksi eksplisit.Gotcha dengan mode transaction: kamu tidak bisa menggunakan prepared statement (mereka state level sesi), kamu tidak bisa menggunakan SET untuk variabel sesi, dan LISTEN/NOTIFY tidak akan bekerja sesuai harapan. Kebanyakan ORM punya cara untuk menonaktifkan prepared statement.
Untuk Node.js dengan driver pg:
const pool = new Pool({
host: "127.0.0.1",
port: 6432, // Port PgBouncer, bukan port PostgreSQL
database: "mydb",
max: 20, // cocok dengan PgBouncer default_pool_size
statement_timeout: 30000,
// Nonaktifkan prepared statement untuk mode transaction PgBouncer
prepare: false,
});Ada formula umum untuk ukuran pool koneksi PostgreSQL yang optimal:
optimal_connections = (core_count * 2) + effective_spindle_count
Untuk server modern dengan 4 core dan SSD (1 effective spindle):
optimal = (4 * 2) + 1 = 9
Ini terlihat kontra-intuitif rendah. Tapi PostgreSQL CPU-bound di kebanyakan hardware modern (SSD cukup cepat sehingga I/O jarang menjadi bottleneck). Lebih banyak koneksi dari ini menghasilkan overhead context switching yang justru memperlambat.
Dalam praktiknya, saya biasanya set default_pool_size ke 2-3x angka ini untuk menangani burst traffic, dengan pemahaman bahwa saat puncak beban, beberapa query akan menunggu di antrian PgBouncer alih-alih semua menghantam PostgreSQL bersamaan.
Ini proses saya yang sebenarnya saat mendapat laporan "query ini lambat". Saya mengikuti langkah-langkah ini dalam urutan ini, setiap kali.
Bukan "endpoint-nya lambat" — SQL sebenarnya. Kalau kamu menggunakan ORM, aktifkan query logging:
-- Log sementara semua query di atas 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();Atau cek pg_stat_statements untuk top query berdasarkan total waktu:
-- Top 10 query berdasarkan total waktu eksekusi
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;pg_stat_statements adalah extension paling berharga untuk performa PostgreSQL. Kalau kamu belum menjalankannya, install sekarang:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query yang lambat>;Yang dicari:
work_mem atau tambahkan index untuk sort-- Kapan statistik terakhir diupdate?
SELECT
relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';Kalau last_analyze sudah lama atau n_dead_tup tinggi relatif terhadap n_live_tup, jalankan:
ANALYZE orders;Lalu jalankan ulang EXPLAIN ANALYZE. Kalau plan-nya berubah, statistik basi adalah masalahnya.
-- Index apa yang ada di tabel ini?
SELECT
indexname,
indexdef,
idx_scan,
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;Mungkin index-nya ada tapi tidak digunakan. Mungkin urutan kolomnya salah untuk query-mu.
Berdasarkan query plan, buat index yang sesuai. Test dengan EXPLAIN ANALYZE sebelum dan sesudah.
-- Buat index secara concurrent (tidak mengunci tabel)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
-- Verifikasi index digunakan
EXPLAIN (ANALYZE, BUFFERS) <query yang lambat>;Selalu gunakan CONCURRENTLY pada tabel produksi. CREATE INDEX biasa mengambil full table lock yang memblokir semua write.
Kalau index ada dan statistik fresh tapi query masih lambat, lihat query-nya sendiri:
OFFSET menyebabkan masalah? Beralih ke keyset paginationUntuk pola query tertentu, pengaturan server penting:
-- work_mem: memori untuk sort dan hash join (per operasi, bukan per koneksi)
-- Default 4MB, yang terlalu rendah untuk query kompleks
SET work_mem = '64MB'; -- coba ini dan jalankan ulang EXPLAIN ANALYZE
-- effective_cache_size: memberi tahu planner berapa disk cache yang diharapkan
-- Set ke ~75% total RAM
SHOW effective_cache_size;
-- random_page_cost: rasio random I/O ke sequential I/O
-- Default 4.0, set ke 1.1-1.5 untuk SSD
SHOW random_page_cost;Kalau mengubah work_mem memperbaiki masalah sort-to-disk, pertimbangkan menaikkannya secara global. Tapi hati-hati — ini per-operasi, bukan per-koneksi. Query kompleks dengan 10 operasi sort dan work_mem = 256MB bisa menggunakan 2.5 GB untuk satu query.
Jangan cuma perbaiki dan lupakan. Verifikasi perbaikannya bertahan:
-- Reset pg_stat_statements untuk melihat data fresh setelah perubahan
SELECT pg_stat_statements_reset();
-- Cek kembali dalam satu jam/hari
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(max_exec_time::numeric, 2) AS max_time_ms,
query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;Ini pengaturan dan praktik yang saya terapkan ke setiap database PostgreSQL yang saya setup, sebelum masalah performa muncul:
-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. Aktifkan slow query logging (50ms threshold saya)
ALTER SYSTEM SET log_min_duration_statement = 50;
-- 3. Pengaturan sesuai SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
-- 4. Naikkan work_mem dari default yang kecil
ALTER SYSTEM SET work_mem = '32MB';
-- 5. Set effective_cache_size ke 75% RAM
-- Untuk server 16GB:
ALTER SYSTEM SET effective_cache_size = '12GB';
-- 6. Set shared_buffers ke 25% RAM
-- Untuk server 16GB:
ALTER SYSTEM SET shared_buffers = '4GB';
-- 7. Tingkatkan responsivitas autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
-- Terapkan perubahan
SELECT pg_reload_conf();
-- Catatan: shared_buffers butuh restartDan query monitoring yang saya jalankan mingguan:
-- Tabel yang butuh perhatian
SELECT
schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;Optimasi query PostgreSQL bukan seni gelap. Ini proses sistematis:
Perbedaan antara query yang butuh 4 detik dan yang butuh 0.3 milidetik jarang soal hardware. Hampir selalu soal pengetahuan — tahu di mana harus mencari dan apa yang harus diubah. Dan sekarang kamu tahu di mana harus mencari.