Connection Pool — PgBouncer, sozlash

~20 daq · PostgreSQL · Index va Performance

Har bir API so'rovida yangi database ulanish ochiladi va yopiladi. PostgreSQL da har ulanish — alohida jarayon (process). 1000 ta bir vaqtdagi so'rov = 1000 ta jarayon. Server xotirasi tugaydi, PostgreSQL ishdan chiqadi.

Connection Pool — ulanishlarni qayta ishlatish. Bir marta ochilgan ulanishlar hovuzda saqlanadi, so'rovlar almashib ishlatadi.

Muammo: har so'rovda yangi ulanish

Mijoz 1  ──connect──► PostgreSQL (jarayon 1)  ──disconnect
Mijoz 2  ──connect──► PostgreSQL (jarayon 2)  ──disconnect
Mijoz 3  ──connect──► PostgreSQL (jarayon 3)  ──disconnect
...
Mijoz 1000 ──connect──► PostgreSQL (jarayon 1000) — xotira tugadi!

PostgreSQL har ulanish uchun ~5-10 MB xotira sarflaydi. 1000 ulanish = ~5-10 GB.

Yechim: Connection Pool

Mijoz 1  ─┐
Mijoz 2  ─┤                    ┌──► PostgreSQL ulanish 1
Mijoz 3  ─┤─► Pool (20 ulanish)├──► PostgreSQL ulanish 2
...       ─┤                   └──► PostgreSQL ulanish 3
Mijoz 1000─┘                        ...

1000 mijoz — lekin PostgreSQL faqat 20 ulanish ko'radi.

PgBouncer

Eng mashhur PostgreSQL connection pooler. Alohida jarayon sifatida ishlaydi — mijozlar PgBouncer ga, PgBouncer PostgreSQL ga ulanadi.

O'rnatish

sudo apt install pgbouncer

pgbouncer.ini asosiy sozlamalar

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432          ; PgBouncer porti
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction     ; eng ko'p ishlatiladigan

max_client_conn = 1000      ; maksimal mijoz ulanishlari
default_pool_size = 20      ; PostgreSQL ga ulanishlar soni

Pool rejimlari

session     — mijoz ulanishi davomida bittasi band
transaction — tranzaksiya davomida bittasi band (ko'p ishlatiladigan)
statement   — har SQL dan keyin qaytariladi (cheklovlar ko'p)

transaction rejimi optimal — har tranzaksiya tugagach ulanish hovuzga qaytadi.

Node.js da connection pool (pg moduli)

Kod darajasida ham pool:

const { Pool } = require('pg');

const pool = new Pool({
  host:     'localhost',
  database: 'myapp',
  user:     'postgres',
  password: 'secret',
  max:      20,    // maksimal ulanishlar
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Ishlatish
const { rows } = await pool.query('SELECT * FROM foydalanuvchilar');

Pool avtomatik boshqaradi — ulanish bo'sh bo'lsa qaytaradi, band bo'lsa kutadi.

Tez-tez uchraydigan xatolar

Pool size juda katta:

default_pool_size = 200  -- ❌

PostgreSQL max_connections = 100 bo'lsa, 200 ulanish xato beradi. Pool size ≤ max_connections - superuser_reserved.

Transaction rejimida prepared statements:

pool_mode = transaction

Transaction rejimida PREPARE / EXECUTE ishlamaydi — har tranzaksiyadan keyin ulanish boshqa mijozga ketishi mumkin. pg modulida prepare: false qiling yoki session rejimiga o'ting.

Xulosa

Muammo:  har so'rovda yangi ulanish → server xotirasi tugaydi
Yechim:  Connection Pool — ulanishlarni qayta ishlatish

PgBouncer — tashqi pooler, eng mashhur
  pool_mode = transaction  — optimal
  max_client_conn = 1000   — mijozlar
  default_pool_size = 20   — PostgreSQL ulanishlari

pg (Node.js) — ichki pool
  max: 20                  — pool hajmi

Keyingi dars

43-dars: Table Partitioning — RANGE, LIST, HASH