Connection Pooling

Aplikasi Toko Maju Jaya tumbuh: dua belas worker web, tiga pekerja cron, dashboard, dan layanan notifikasi — semuanya ingin koneksi PostgreSQL. Kebijakan naif “buat koneksi baru setiap butuh” atau “naikkan saja max_connections” ternyata menabrak arsitektur fundamental PostgreSQL: satu koneksi = satu proses sistem operasi. Seratus koneksi berarti seratus proses yang harus di-fork, diberi memori, dan dijadwalkan CPU — biaya yang tumbuh linier justru ketika server sedang paling sibuk. Chapter ini membongkar biaya nyata tiap koneksi dengan query katalog, menunjukkan mengapa menaikkan max_connections bukan cara menskalakan, lalu membedah arsitektur connection pooler seperti PgBouncer: lapisan perantara yang mendaur ulang sedikit koneksi server untuk melayani banyak klien — lengkap dengan jebakan fitur sesi yang diputusnya.

Satu Koneksi, Satu Proses Sistem Operasi #

PostgreSQL memakai arsitektur process per connection: setiap koneksi baru men-trigger fork() proses backend baru di server. Proses itu memakan memori sejak lahir — struktur data internal, cache katalog, konteks memori per sesi — bahkan sebelum menjalankan satu query pun. Batas atas jumlahnya dikendalikan max_connections; query berikut membaca tiga parameter yang menentukan anggaran memori koneksi di server latihan:

-- Batas koneksi dan memori per query di server latihan.
-- max_connections: jumlah proses backend maksimum sekaligus.
-- superuser_reserved_connections: slot cadangan khusus superuser.
-- work_mem: memori per operasi sort/hash per koneksi.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('max_connections',
               'superuser_reserved_connections',
               'work_mem')
ORDER BY name;

-- Matematika bahaya: bila 100 koneksi masing-masing menjalankan
-- operasi sort dengan work_mem 10 MB serentak, kebutuhan memori
-- hanya untuk operasi sort saja sudah sebesar angka ini.
SELECT pg_size_pretty((100 * 10 * 1024 * 1024)::bigint)
       AS work_mem_potensial_100_koneksi;

Output yang Dihasilkan:

              name              | setting | unit 
--------------------------------+---------+------
 max_connections                | 100     | 
 superuser_reserved_connections | 3       | 
 work_mem                       | 4096    | kB
(3 rows)

 work_mem_potensial_100_koneksi 
--------------------------------
 1000 MB
(1 row)

Angka kuncinya ada di baris terakhir: work_mem bukan anggaran per koneksi, melainkan per operasi sort/hash — dan satu query kompleks bisa menjalankan beberapa operasi sekaligus. Skrip pembuktian berikut membuka dua koneksi paralel lewat bash, lalu menghitung backend yang melayani database dari salah satunya:

# Koneksi pertama sibuk selama 5 detik menjalankan pg_sleep.
psql -X -c "SELECT pg_sleep(5);" >/dev/null 2>&1 &

# Beri waktu koneksi pertama terbentuk.
sleep 1

# Koneksi kedua menghitung proses backend yang sedang
# melayani database ini: satu koneksi = satu baris = satu proses.
psql -X -c "SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() AND backend_type = 'client backend' GROUP BY state ORDER BY state;"

# Tunggu koneksi pertama selesai agar skrip berakhir bersih.
wait

Output yang Dihasilkan:

 state  | count 
--------+-------
 active |     2
(1 row)

Mari kita bedah skrip tersebut baris demi baris:

1. Baris 2: psql -c "SELECT pg_sleep(5);" & membuka koneksi pertama di latar belakang. Selama 5 detik penuh, backend-nya menempati satu baris pg_stat_activity dengan state active — walaupun yang dilakukannya hanya tidur.
2. Baris 5: sleep 1 memberi jeda agar koneksi pertama pasti sudah terbentuk sebelum dihitung.
3. Baris 9: koneksi kedua query pg_stat_activity difilter ke database saat ini dan backend_type = 'client backend' — filter yang membuang proses latar seperti autovacuum worker. Hasilnya: active | 2, tepat dua baris untuk dua koneksi.
4. Baris 12: wait menahan skrip sampai koneksi latar belakang selesai supaya skrip keluar dengan rapi.

Dua koneksi, dua proses. Kalikan dengan beban nyata: 200 koneksi aplikasi berarti 200 proses OS yang saling berebut 2 inti CPU untuk context switch, masing-masing membawa memori dasarnya sendiri. View pg_stat_activity yang dipakai skrip ini adalah alat pengawasan koneksi sehari-hari — states active, idle, dan idle in transaction beserta cara membacanya dibahas tuntas di chapter Connection Management , sedangkan pemetaan proses OS ke backend dibahas di chapter Process dan Activity Monitoring .

Menaikkan max_connections Bukan Skala #

Ketika aplikasi mulai menolak koneksi dengan pesan FATAL: too many connections, solusi yang paling sering dipakai pemula adalah menaik max_connections dari 100 ke 500. Angka berikut menunjukkan mengapa justru itu mempercepat kejatuhan server:

Angka 1000 MB pada query pertama berasal dari skenario “hanya” 100 koneksi × satu operasi sort 10 MB. Server dengan RAM 4 GB akan tertekan sebelum query kesepuluh selesai; server 8 GB tetap rugi karena memori itu diambil dari wilayah yang seharusnya diberikan ke shared_buffers dan cache operasi sistem. Semakin banyak proses, semakin besar pula biaya penjadwalan — dan pada titik tertentu throughput justru turun ketika koneksi ditambah.

Connection Pooler: Mendaur Ulang Koneksi #

Connection pooler adalah server perantara yang memutus hubungan satu-ke-satu antara klien dan backend. Klien membuka koneksi ke pooler sebanyak apa pun (murah — pooler hanya menjaga socket, bukan proses), sementara pooler memelihara sejumlah kecil koneksi server yang benar-benar dibuka ke PostgreSQL. Ketika seorang klien memulai transaksi, pooler meminjamkan satu koneksi server yang sedang menganggur; begitu transaksi berakhir, koneksi dikembalikan ke pool untuk dipakai klien berikutnya.

Diagram di atas meringkas transformasinya: sisi kiri bisa ratusan koneksi murah, sisi kanan tetap 20 proses backend yang sibuk dan efisien. PostgreSQL sendiri tidak memiliki pooler bawaan — perannya harus diisi komponen eksternal, dan tiga mode pooling yang ditawarkan PgBouncer menentukan seberapa agresif koneksi didaur:

Fitur Sesi yang Diputus Transaction Pooling #

Mode transaction adalah alasan PgBouncer begitu efektif — dan juga sumber kejutannya. Karena koneksi server bisa bertukar di antara transaksi, semua fitur yang melekat pada sesi kehilangan jangkarnya. File berikut mendemokan tiga fitur itu pada koneksi langsung tanpa pooler — ketiganya bekerja mulus:

-- Tiga fitur yang melekat pada SESI: variabel khusus aplikasi,
-- advisory lock level sesi, dan prepared statement.
-- Ketiganya bekerja mulus pada koneksi langsung seperti file ini --
-- dan justru karena itulah mode transaction pooling memutusnya.

-- 1. Variabel sesi khusus aplikasi (custom GUC).
SET myapp.pengguna = 'budi';
SELECT current_setting('myapp.pengguna') AS pengguna_aktif;

-- 2. Advisory lock level sesi: kunci bertahan sampai sesi berakhir.
SELECT pg_advisory_lock(4242);
SELECT locktype, objid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory' AND objid = 4242;
SELECT pg_advisory_unlock(4242);

-- 3. Prepared statement: rencana siap pakai di memori sesi.
PREPARE hitung_diskon(integer) AS SELECT $1 * 0.1 AS diskon;
EXECUTE hitung_diskon(50000);
DEALLOCATE hitung_diskon;

Output yang Dihasilkan:

SET
 pengguna_aktif 
----------------
 budi
(1 row)

 pg_advisory_lock 
------------------
 
(1 row)

 locktype | objid |     mode      | granted 
----------+-------+---------------+---------
 advisory |  4242 | ExclusiveLock | t
(1 row)

 pg_advisory_unlock 
--------------------
 t
(1 row)

PREPARE
 diskon 
--------
 5000.0
(1 row)

DEALLOCATE

Output membuktikan ketiganya hidup di koneksi langsung: current_setting mengembalikan budi, advisory lock 4242 terlihat di pg_locks dengan status granted, dan EXECUTE memakai prepared statement tanpa mengirim ulang query. Di balik pooler mode transaction, tabel berikut mengubah gambarannya:

PgBouncer: Pooler de Facto #

PgBouncer adalah pooler paling banyak dipakai di ekosistem PostgreSQL: satu proses C ringan yang sanggup memelihara puluhan ribu koneksi klien. Konfigurasinya hidup dalam satu file ini — berikut sketsa minimalnya untuk kasus Toko Maju Jaya:

;; Sketsa pgbouncer.ini -- disalin dari pola dokumentasi resmi PgBouncer.
;; PgBouncer tidak terpasang di lingkungan latihan sehingga tidak ada
;; output eksekusi untuk blok ini; nilai-nilainya mengikuti dokumentasi.

[databases]
;; Klien menyambut alias "toko_maju"; PgBouncer meneruskannya
;; ke server PostgreSQL sungguhan.
toko_maju = host=127.0.0.1 port=5432 dbname=toko_maju

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200

Tiga baris terakhir memuat keputusan arsitektur utamanya: pool_mode = transaction mengaktifkan pendauran per transaksi, default_pool_size = 20 membatasi koneksi server yang dibuka PgBouncer ke PostgreSQL, dan max_client_conn = 200 menampung koneksi klien yang murah. Aplikasi kemudian cukup mengubah connection string-nya dari port 5432 ke port 6432 — PostgreSQL di baliknya tidak perlu dikonfigurasi ulang. Selain PgBouncer, ada Odyssey dari Yandex: pooler dengan dukungan TLS dan pengelolaan user per database yang lebih kaya; pilihan lain yang juga sering dipakai adalah pooler di sisi aplikasi itu sendiri.

Ukuran Pool dan Pool Sisi Aplikasi #

Berapa besar pool yang sehat? Aturan praktis yang paling beredar luas di komunitas database: jumlah koneksi aktif ≈ jumlah inti CPU × 2 + jumlah spindle. Intuisinya sederhana — pada beban berat, query yang benar-benar berjalan di CPU hanya sebanyak inti yang tersedia (dikali dua karena pipeline I/O), selebihnya hanya mengantre dan memakan memori. Server latihan dengan 2 inti idealnya hanya butuh pool sekitar 4–6 koneksi untuk beban tulis-baca biasa. Angka ini titik awal, bukan dogma: beban yang didominasi I/O lambat atau kueri pelaporan panjang menuntut angka berbeda, dan satu-satunya cara memastikan adalah mengukur antrian dan durasi query seperti dibahas di chapter Monitoring dan Alerting .

Pooler tidak selalu berarti komponen terpisah. Sebagian besar driver aplikasi memelihara pool sendiri: PDO di PHP, pgxpool di Go, connection pool bawaan framework Java. Pool sisi aplikasi mendaur koneksi dari satu proses aplikasi, dan pola yang lazim di sistem besar adalah kombinasi keduanya — pool kecil di tiap proses aplikasi, lalu seluruhnya melalui PgBouncer agar total koneksi ke PostgreSQL tetap terkendali. Praktik mengonfigurasi pool di sisi kode dibahas detail di chapter Database Connection Pooling pada section Integrasi Aplikasi.

Dengan koneksi yang terkendali, fondasi operasional server sudah stabil. Pekerjaan rutin berikutnya ada di dalam: menjaga tabel tetap bersih dari baris mati dan statistiknya tetap segar lewat VACUUM dan ANALYZE — topik chapter berikutnya.

Referensi #

• Connections and Authentication Settings
• PgBouncer Configuration
• PgBouncer Features

-- Batas koneksi dan memori per query di server latihan.
-- max_connections: jumlah proses backend maksimum sekaligus.
-- superuser_reserved_connections: slot cadangan khusus superuser.
-- work_mem: memori per operasi sort/hash per koneksi.
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('max_connections',
               'superuser_reserved_connections',
               'work_mem')
ORDER BY name;

-- Matematika bahaya: bila 100 koneksi masing-masing menjalankan
-- operasi sort dengan work_mem 10 MB serentak, kebutuhan memori
-- hanya untuk operasi sort saja sudah sebesar angka ini.
SELECT pg_size_pretty((100 * 10 * 1024 * 1024)::bigint)
       AS work_mem_potensial_100_koneksi;

-- Tiga fitur yang melekat pada SESI: variabel khusus aplikasi,
-- advisory lock level sesi, dan prepared statement.
-- Ketiganya bekerja mulus pada koneksi langsung seperti file ini --
-- dan justru karena itulah mode transaction pooling memutusnya.

-- 1. Variabel sesi khusus aplikasi (custom GUC).
SET myapp.pengguna = 'budi';
SELECT current_setting('myapp.pengguna') AS pengguna_aktif;

-- 2. Advisory lock level sesi: kunci bertahan sampai sesi berakhir.
SELECT pg_advisory_lock(4242);
SELECT locktype, objid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory' AND objid = 4242;
SELECT pg_advisory_unlock(4242);

-- 3. Prepared statement: rencana siap pakai di memori sesi.
PREPARE hitung_diskon(integer) AS SELECT $1 * 0.1 AS diskon;
EXECUTE hitung_diskon(50000);
DEALLOCATE hitung_diskon;

# Koneksi pertama sibuk selama 5 detik menjalankan pg_sleep.
psql -X -c "SELECT pg_sleep(5);" >/dev/null 2>&1 &

# Beri waktu koneksi pertama terbentuk.
sleep 1

# Koneksi kedua menghitung proses backend yang sedang
# melayani database ini: satu koneksi = satu baris = satu proses.
psql -X -c "SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() AND backend_type = 'client backend' GROUP BY state ORDER BY state;"

# Tunggu koneksi pertama selesai agar skrip berakhir bersih.
wait