Napa perlu connection pooling (PgBouncer)?

Question

Accepted Answer

**Connection pooling** nggunakake manèh saset cilik koneksine basis data ing akèh permintaan klien, nanging tinimbang mbukak koneksine anyar saben permintaan. Koneksine PostgreSQL iku **larang** (saben-saben iku proses kapisah nggunakake memori akeh), mula pooling iku penting kanggo performa lan skalabilitas ing beban. **PgBouncer** iku pooler standar.

## Masalahe: koneksine PostgreSQL iku larang

```text
Each PostgreSQL connection = a separate OS PROCESS, using ~5-10MB of memory.
  ✗ Opening a connection has overhead (process creation, auth, setup)
  ✗ Many connections (e.g. 1000s from a busy app) → huge memory use, contention
  ✗ Postgres has a max_connections limit (often ~100); exceeding it = errors
→ Opening/closing a connection per request, or holding thousands open, doesn't scale.
```

## Solusine: connection pool

```text
A POOL maintains a small set of REUSABLE connections to Postgres.
Application requests BORROW a connection from the pool, use it, and RETURN it.
  ✓ Connections are reused (no per-request open/close overhead)
  ✓ Many app requests share a few DB connections (e.g. 10000 clients → 20 connections)
  ✓ Stays within max_connections, controls resource use → scales
```

## PgBouncer — pooler eksternal standar

```text
PgBouncer sits BETWEEN the application and PostgreSQL — lightweight, efficient.
Pooling modes:
  Session   → a connection is assigned for the client's whole session
  Transaction → a connection is held only for the duration of a TRANSACTION (most
               efficient — connections returned between transactions; common choice)
  Statement → per-statement (most aggressive; limited)
→ Transaction pooling maximizes connection reuse for typical web apps.
```

## Endi pooling ditindakake

```text
Application-level → many ORM/driver libraries have built-in pools (per app instance)
External pooler (PgBouncer) → a shared pool in front of Postgres (best for many app
  instances / serverless, where per-instance pools would still overwhelm the DB)
→ Serverless especially needs an external pooler (many short-lived instances).
```

## Apa wigati

Connection pooling iku pengetahuan penting kanggo mbukak PostgreSQL **ing skala gedhe**, amarga koneksine PostgreSQL iku unik larang (saben-saben iku proses OS kapisah nggunakake memori akeh), mula mangertèni napa kok pooling dibutuhake lan carane nggarap iku wigati kanggo pengetahuan tingkat senior kanggo deployment sing epektif lan skalabel.

Masala pokok iku pendekatan naif — mbukak koneksine saben permintaan, utawa tetep koneksine libu-libu mbukak saka aplikasi sibuk — ora skalabel nganggo Postgres: koneksine larang (memori saben proses, overhead pembuatan), lan Postgres duwe watesan `max_connections` (asring ~100) sing gampang kelebihan, nyebabake kesalahan lan kelelahan sumber daya. **Connection pooling** ngatasi iki kanthi njaga saset cilik koneksine sing bisa digunakake manèh sing akèh permintaan aplikasi bagi (minjem lan ngembalik) — supaya ribu klien bisa dilayani dening pirang puluh koneksine basis data, ngilangake overhead saben permintaan lan tetep ing watesan.

Mangertèni **PgBouncer** (pooler eksternal standar) lan mode poolinge — utamane **transaction pooling** (tetep koneksine mung kanggo durasi transaksi, maksimalisasi penggunaan manèh, pilihan umum kanggo aplikasi web) — iku pengetahuan praktis penting.

Ngetok **endi pooling ditindakake** (pool tingkat aplikasi saben instansi, versus pooler eksternal kaya PgBouncer kanggo akèh instansi utawa serverless) iku poin arsitektur kunci — **serverless utamane butuh pooler eksternal** amarga akèh instansi sedheng-sedheng bakal nglelehke Postgres karo koneksine (masala umum, nyata).

Amarga manajemen koneksine langsung kena skalabilitas lan stabilitas ing beban (lan model Postgres proses-per-koneksine nggaweke iki akut), lan amarga pooling (utamane PgBouncer karo transaction pooling) iku solusi standar, penting, mangertèn connection pooling — napa Postgres butuh iku, carane nggarap, PgBouncer lan mode-ne, lan pertimbangan serverless — iku pengetahuan tingkat senior penting kanggo ngoperasikake PostgreSQL ing skala, kawigatènan asring-asring kritis kanggo sistem produksi sing mbedakake sing bisa skalab Postgres saka sing entuk kesalahan watesan koneksine.