پروژه نهایی – اتصال از Django/PHP با pgBouncer

در این فصل پایانی، PostgreSQL را به‌صورت عملی با Django و PHP/PDO وصل می‌کنیم، با pgBouncer connection pooling حرفه‌ای راه می‌اندازیم و آنتی‌پترن‌های رایج پروداکشن را پوشش می‌دهیم.

چرا Connection Pooling؟

یادآوری: PostgreSQL process-based است – هر اتصال یک پروسه جدا. هر پروسه ~10MB حافظه می‌گیرد:

راه‌حل: pooler مثل pgBouncer که یک‌جا اتصال‌های زیاد client را به اتصال‌های کم backend به PG تبدیل می‌کند.

5000 client connections
        ↓
    pgBouncer
        ↓
   50 connections to PG

نصب pgBouncer

sudo apt install pgbouncer

# config files:
# /etc/pgbouncer/pgbouncer.ini
# /etc/pgbouncer/userlist.txt

pgbouncer.ini

[databases]
shopdb = host=127.0.0.1 port=5432 dbname=shopdb
* = host=127.0.0.1 port=5432            ; همه DB‌های دیگر

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; pool mode
pool_mode = transaction          ; session | transaction | statement
default_pool_size = 25           ; backend connections per (db, user)
min_pool_size = 5
max_client_conn = 1000           ; max client connections
reserve_pool_size = 5
reserve_pool_timeout = 5

; logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; admin
admin_users = postgres
stats_users = stats_collector

server_idle_timeout = 600
server_lifetime = 3600
client_idle_timeout = 0
query_timeout = 0

userlist.txt

"shop_user" "SCRAM-SHA-256$4096:salt$..."
"reporting" "SCRAM-SHA-256$4096:salt$..."

برای ساخت hash:

# در PostgreSQL
sudo -u postgres psql -c "SELECT 'shop_user', passwd FROM pg_shadow WHERE usename='shop_user';"

sudo systemctl enable --now pgbouncer

# تست
psql -h 127.0.0.1 -p 6432 -U shop_user -d shopdb

Pool Modes

اتصال Django به PostgreSQL

pip install psycopg[binary]    # psycopg3 (پیشنهاد) - یا psycopg2-binary

# requirements.txt
psycopg[binary,pool]==3.1.18
django==5.0
django-environ

settings.py

import environ

env = environ.Env()
environ.Env.read_env()

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': env('DB_NAME', default='shopdb'),
        'USER': env('DB_USER', default='shop_user'),
        'PASSWORD': env('DB_PASSWORD'),
        'HOST': env('DB_HOST', default='127.0.0.1'),
        'PORT': env('DB_PORT', default='6432'),    # pgBouncer port
        'OPTIONS': {
            'sslmode': 'require',
            'connect_timeout': 10,
        },
        'CONN_MAX_AGE': 0,        # 0 برای pgBouncer transaction mode
        'CONN_HEALTH_CHECKS': True,
        'ATOMIC_REQUESTS': False,
    }
}

.env

DB_NAME=shopdb
DB_USER=shop_user
DB_PASSWORD=StrongPass123!
DB_HOST=127.0.0.1
DB_PORT=6432
DB_SSL_MODE=require

Models نمونه

from django.db import models
from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex

class Product(models.Model):
    name = models.CharField(max_length=200, db_index=True)
    description = models.TextField(blank=True)
    price = models.DecimalField(max_digits=12, decimal_places=2)
    
    # PostgreSQL-specific fields
    tags = ArrayField(models.CharField(max_length=50), blank=True, default=list)
    attributes = models.JSONField(default=dict)        # JSONB در PG
    
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        indexes = [
            GinIndex(fields=['tags']),                 # ایندکس GIN روی array
            GinIndex(fields=['attributes']),           # ایندکس GIN روی JSONB
            models.Index(fields=['-created_at']),
        ]
    
    def __str__(self):
        return self.name

Query‌های PostgreSQL-specific

from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank
from django.db.models import F, Q

# array contains
Product.objects.filter(tags__contains=['دستباف'])

# JSONB query
Product.objects.filter(attributes__color='قرمز')
Product.objects.filter(attributes__contains={'color': 'قرمز'})
Product.objects.filter(attributes__has_key='warranty_years')

# nested JSONB
Product.objects.filter(attributes__specs__weight__gt=20)

# Full-Text Search
Product.objects.annotate(
    search=SearchVector('name', 'description'),
).filter(search='فرش کاشان')

# با rank
query = SearchQuery('فرش', config='simple')
Product.objects.annotate(
    rank=SearchRank(SearchVector('name', 'description', config='simple'), query)
).filter(rank__gt=0).order_by('-rank')

# Trigram similarity (نیاز به django.contrib.postgres + pg_trgm)
from django.contrib.postgres.search import TrigramSimilarity
Product.objects.annotate(
    similarity=TrigramSimilarity('name', 'فرش کشان')
).filter(similarity__gt=0.3).order_by('-similarity')

Connection Pool در سمت اپ (psycopg3)

from psycopg_pool import ConnectionPool

# pool داخل اپ - اضافه بر pgBouncer
pool = ConnectionPool(
    "host=localhost port=6432 dbname=shopdb user=shop_user",
    min_size=4,
    max_size=20,
    timeout=30,
)

# استفاده
with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM products WHERE id = %s", (1,))
        row = cur.fetchone()

اتصال PHP به PostgreSQL

sudo apt install php-pgsql

# در php.ini یا extension config
extension=pdo_pgsql

PDO نمونه

<?php
try {
    $pdo = new PDO(
        'pgsql:host=127.0.0.1;port=6432;dbname=shopdb;sslmode=require',
        'shop_user',
        'StrongPass!',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
    
    // Prepared Statement (امن از SQL injection)
    $stmt = $pdo->prepare('SELECT * FROM products WHERE id = :id');
    $stmt->execute(['id' => $productId]);
    $product = $stmt->fetch();
    
    // INSERT با RETURNING
    $stmt = $pdo->prepare(
        'INSERT INTO products(name, price) VALUES(:name, :price) RETURNING id'
    );
    $stmt->execute(['name' => 'فرش کاشان', 'price' => 5000000]);
    $newId = $stmt->fetchColumn();
    
    // JSONB
    $stmt = $pdo->prepare(
        'INSERT INTO products(name, attributes) VALUES(:name, :attrs::jsonb)'
    );
    $stmt->execute([
        'name' => 'گلیم',
        'attrs' => json_encode(['color' => 'آبی', 'size' => '2x3']),
    ]);
    
    // Transaction
    $pdo->beginTransaction();
    try {
        $pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
        $pdo->exec('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
} catch (PDOException $e) {
    error_log('DB error: ' . $e->getMessage());
}

WordPress با PostgreSQL

WordPress به‌طور پیش‌فرض MySQL است، اما با plugin‌هایی مثل PG4WP یا HyperDB می‌توان به PG وصل شد. در عمل برای WP، MySQL/MariaDB ساده‌تر است.

آنتی‌پترن‌های پروداکشن

۱. اتصال‌های زیاد بدون pooler

❌ 5000 web workers، هر یک با اتصال persistent
✅ pgBouncer در میان

۲. تراکنش‌های طولانی

# ❌ بد
with transaction.atomic():
    user = User.objects.get(id=1)
    response = requests.get('https://api.com/...')   # ساعت‌ها block می‌کند
    user.data = response.json()
    user.save()

# ✅ خوب
response = requests.get('https://api.com/...')
data = response.json()
with transaction.atomic():
    user = User.objects.get(id=1)
    user.data = data
    user.save()

۳. SELECT *

# ❌ کل ستون‌ها (شامل JSONB بزرگ)
products = Product.objects.all()

# ✅ فقط ستون‌های لازم
products = Product.objects.only('id', 'name', 'price')
products = Product.objects.values('id', 'name', 'price')

۴. N+1 Query

# ❌ N+1
for order in Order.objects.all():
    print(order.user.name)         # 1 query به ازای هر order

# ✅ با select_related (FK forward)
for order in Order.objects.select_related('user').all():
    print(order.user.name)         # 1 query کلی

# ✅ با prefetch_related (M2M یا FK reverse)
for user in User.objects.prefetch_related('orders').all():
    for order in user.orders.all():
        ...

۵. ایندکس نداشتن روی FK

-- Django خودکار ایندکس می‌سازد، اما در SQL خام:
-- ❌
CREATE TABLE order_items(
    order_id INTEGER REFERENCES orders(id),
    ...
);

-- ✅
CREATE TABLE order_items(
    order_id INTEGER REFERENCES orders(id),
    ...
);
CREATE INDEX ON order_items(order_id);

۶. OFFSET بزرگ

# ❌ صفحه 1000 - 50000 ردیف skip می‌شود
products = Product.objects.all()[50000:50050]

# ✅ Keyset pagination
last_id = request.GET.get('last_id', 0)
products = Product.objects.filter(id__gt=last_id).order_by('id')[:50]

۷. عدم استفاده از database constraints

-- ❌ validation فقط در app
-- اگر کسی مستقیم به DB وصل شود، نقض می‌شود

-- ✅ constraint در DB
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);
ALTER TABLE users ADD CONSTRAINT valid_email CHECK (email ~ '^[^@]+@[^@]+.[^@]+$');

۸. autovacuum غیرفعال یا misconfigured

-- ✅ همیشه autovacuum فعال
-- برای جدول‌های پر تغییر:
ALTER TABLE high_traffic_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

بهترین شیوه‌های پروداکشن

1. pgBouncer در transaction mode
2. SSL/TLS اجباری
3. monitoring با Prometheus + Grafana
4. backup پیوسته با pgBackRest یا WAL-G
5. replica برای DR و read scaling
6. autovacuum tuning
7. connection limits per user
8. statement_timeout و idle_in_transaction_session_timeout
9. pg_stat_statements همیشه فعال
10. log slow queries (> 1s)
11. partition برای جدول‌های > 100M ردیف
12. BRIN برای time-series
13. JSONB با GIN index برای داده‌های flexible
14. RLS برای multi-tenant
15. تست backup ماهانه

قدم‌های بعدی

بعد از این دوره می‌توانید روی این موضوعات تخصصی کار کنید:

• PostGIS: داده‌های مکانی، GPS، نقشه
• TimescaleDB: time-series با مقیاس بسیار بالا
• Citus: sharding توزیع‌شده
• pgvector: vector embedding برای AI/ML/RAG
• Foreign Data Wrappers: اتصال به MongoDB، MySQL، Oracle
• Logical Decoding & CDC: stream تغییرات به Kafka

جمع‌بندی – پایان دوره

تبریک! 🎉 شما این دوره PostgreSQL را به پایان رساندید. در ۱۵ فصل آموختیم:

• معماری PostgreSQL، MVCC، WAL
• نصب، پیکربندی و مدیریت
• انواع داده پیشرفته: JSONB، ARRAY، UUID، Range
• ایندکس‌گذاری حرفه‌ای: B-Tree، GIN، GiST، BRIN
• بهینه‌سازی query با EXPLAIN و pg_stat_statements
• PL/pgSQL و triggers برای منطق در دیتابیس
• Transactions، Isolation، Locking
• JSONB و Full-Text Search فارسی
• Partitioning برای جدول‌های بسیار بزرگ
• Replication و HA با Patroni
• Backup و PITR با pgBackRest
• امنیت، RLS، SSL
• monitoring با Prometheus و Grafana
• اتصال از Django/PHP با pgBouncer