Engineering Note

PgBouncer + Django/FastAPI/Flask: sozlash, performance va 5000 so‘rov benchmark

Django, FastAPI va Flask bilan PostgreSQL uchun PgBouncer bo‘yicha amaliy qo‘llanma: pooling rejimlari, real performance natijalari, config misollari va 5000 so‘rov benchmark.

2 min#PostgreSQL#PgBouncer#Django#FastAPI#Flask#Performance

TL;DR

  • API uchun odatiy rejim: pool_mode = transaction
  • Django: CONN_MAX_AGE = 0
  • SQLAlchemy: ko‘pincha NullPool
  • Faqat average emas, p95/p99 ni ham o‘lchang

1) PgBouncer nima va nima uchun kerak

PgBouncer — PostgreSQL uchun yengil connection pooler. Ilova PgBouncer ga ulanadi, PgBouncer esa Postgres bilan real ulanishlarni boshqaradi.

Ayniqsa quyidagida foydali:

  • traffic piklarida ulanishlar ko‘payganda;
  • worker soni katta bo‘lsa;
  • Postgres connection limitga yaqinlashsa.

Muhim: PgBouncer SQL ni tezlashtirmaydi, lekin yuklama ostida barqarorlik va tail latency ni yaxshilaydi.

2) Pooling rejimlari: session / transaction / statement

transaction odatda API uchun eng yaxshi:

  • yuqori pool zichligi;
  • qisqa transaction lar uchun yaxshi scalability;
  • session-state ga bog‘lanishni kamaytiradi.

session mosligi yuqori, lekin ko‘p hollarda kamroq samarali.

statement kam ishlatiladi va murakkab transaction larni buzishi mumkin.

3) Qanday natijalarni kutish mumkin

Ko‘p hollarda:

  • too many connections va connect timeout kamroq bo‘ladi;
  • p95/p99 tekisroq bo‘ladi;
  • yuqori concurrency da throughput oshadi.

4) PgBouncer config misoli

[databases]
appdb = host=POSTGRES_HOST port=5432 dbname=appdb user=appuser password=apppass

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 20
reserve_pool_timeout = 5
server_idle_timeout = 30
server_lifetime = 3600
ignore_startup_parameters = extra_float_digits,search_path
stats_period = 60

"appuser" "md5<md5passwordhash>"

5) Django + PgBouncer

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "appdb",
        "USER": "appuser",
        "PASSWORD": "apppass",
        "HOST": "pgbouncer",
        "PORT": "6432",
        "CONN_MAX_AGE": 0,
        "OPTIONS": {"connect_timeout": 5},
    }
}

6) FastAPI + SQLAlchemy + PgBouncer

from fastapi import FastAPI
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.pool import NullPool

app = FastAPI()
engine = create_async_engine(
    "postgresql+asyncpg://appuser:apppass@pgbouncer:6432/appdb",
    poolclass=NullPool,
    pool_pre_ping=True,
)

@app.get("/ping-db")
async def ping_db():
    async with engine.connect() as conn:
        await conn.execute(text("SELECT 1"))
    return {"ok": True}

7) Flask + SQLAlchemy + PgBouncer

SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://appuser:apppass@pgbouncer:6432/appdb"
SQLALCHEMY_ENGINE_OPTIONS = {
    "poolclass": __import__("sqlalchemy").pool.NullPool,
    "pool_pre_ping": True,
}

8) 5000 so‘rov benchmark script

import asyncio
import time
import statistics
import httpx

TOTAL = 5000
CONCURRENCY = 200
URL = "http://127.0.0.1:8000/ping-db"

async def worker(client: httpx.AsyncClient, n: int, latencies: list[float]):
    for _ in range(n):
        t0 = time.perf_counter()
        r = await client.get(URL, timeout=10.0)
        r.raise_for_status()
        latencies.append((time.perf_counter() - t0) * 1000.0)

async def main():
    per_worker = TOTAL // CONCURRENCY
    remainder = TOTAL % CONCURRENCY
    latencies: list[float] = []
    start = time.perf_counter()

    limits = httpx.Limits(max_connections=CONCURRENCY, max_keepalive_connections=CONCURRENCY)
    async with httpx.AsyncClient(limits=limits) as client:
      tasks = []
      for i in range(CONCURRENCY):
          n = per_worker + (1 if i < remainder else 0)
          tasks.append(asyncio.create_task(worker(client, n, latencies)))
      await asyncio.gather(*tasks)

    elapsed = time.perf_counter() - start
    rps = TOTAL / elapsed
    latencies.sort()
    p50 = latencies[int(0.50 * len(latencies))]
    p95 = latencies[int(0.95 * len(latencies))]
    p99 = latencies[int(0.99 * len(latencies))]
    avg = statistics.mean(latencies)

    print(f"TOTAL={TOTAL} CONCURRENCY={CONCURRENCY}")
    print(f"Elapsed: {elapsed:.2f}s RPS: {rps:.1f}")
    print(f"Latency ms: avg={avg:.2f} p50={p50:.2f} p95={p95:.2f} p99={p99:.2f}")

if __name__ == "__main__":
    asyncio.run(main())

pip install httpx
python bench_http.py

Adolatli taqqoslash uchun faqat DB endpointni almashtiring (Postgres:5432 va PgBouncer:6432), qolgan yuklama parametrlarini bir xil qoldiring.

9) Production uchun amaliy tavsiyalar

  • Faqat average emas, p95/p99 ni kuzating.
  • pool_mode=transaction da transaction larni qisqa tuting.
  • Django: CONN_MAX_AGE = 0.
  • SQLAlchemy: ko‘pincha NullPool ishlating.
  • default_pool_size ni Postgres barqaror ishlaydigan qilib tanlang.

Tezkor checklist:

  • PgBouncer metrikalari: SHOW POOLS;, SHOW STATS;
  • Postgres metrikalari: pg_stat_activity, transaction davomiyligi, connection soni
  • Load test: 50 -> 200 -> 500 concurrency va p95/p99 tail larni solishtiring
← Blogga qaytish