معماری PostgreSQL و مقایسه با MySQL

قبل از وارد شدن به جزئیات PostgreSQL، باید معماری آن را بفهمیم. این فصل توضیح می‌دهد چرا PostgreSQL با MySQL متفاوت است، چطور MVCC کار می‌کند، چه پروسه‌هایی در پشت صحنه اجرا می‌شوند و WAL چه نقشی دارد.

PostgreSQL در یک نگاه

PostgreSQL (که به اختصار Postgres هم نامیده می‌شود) یک Object-Relational Database Management System (ORDBMS) متن‌باز است که از سال ۱۹۸۶ در دانشگاه برکلی توسعه یافت. ویژگی‌های اصلی:

• ACID-compliant به‌صورت کامل
• MVCC برای concurrency بدون lock
• Extensible: می‌توان نوع داده، تابع، عملگر و حتی index method جدید اضافه کرد
• Standards-compliant: نزدیک‌ترین به استاندارد SQL
• Enterprise features: Replication، Partitioning، Foreign Data Wrappers، LISTEN/NOTIFY

PostgreSQL vs MySQL

معماری Process-Based

برخلاف MySQL که thread-based است، PostgreSQL از مدل process-based استفاده می‌کند. هر اتصال یک پروسه جدا دارد:

                    ┌──────────────────┐
                    │   postgres       │  ← پروسه اصلی (postmaster)
                    │   (master)       │
                    └────────┬─────────┘
                             │ fork
        ┌────────────────────┼────────────────────┐
        │                    │                    │
   ┌────▼─────┐         ┌────▼─────┐        ┌────▼─────┐
   │ backend  │         │ backend  │        │ backend  │
   │ (client) │         │ (client) │        │ (client) │
   └──────────┘         └──────────┘        └──────────┘

پروسه‌های پس‌زمینه:
   ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
   │ WAL Writer      │  │ Background      │  │ Autovacuum      │
   │                 │  │ Writer          │  │ launcher        │
   └─────────────────┘  └─────────────────┘  └─────────────────┘
   ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
   │ Checkpointer    │  │ Stats Collector │  │ Logical Repl    │
   └─────────────────┘  └─────────────────┘  └─────────────────┘

پروسه‌های مهم

• postmaster: پروسه اصلی، listen روی پورت 5432، fork کردن backend برای هر اتصال
• backend: یک پروسه به ازای هر اتصال client
• WAL Writer: نوشتن Write-Ahead Log روی دیسک
• Background Writer: نوشتن dirty pages از shared_buffers به دیسک
• Checkpointer: انجام checkpoint دوره‌ای
• Autovacuum Launcher: شروع worker‌های autovacuum برای cleanup
• Stats Collector: جمع‌آوری آمار
• Logical Replication Workers: برای replication

معماری حافظه

┌─────────────────────────────────────────────────┐
│              Shared Memory                       │
│  ┌─────────────────────────────────────────┐    │
│  │  shared_buffers (پیش‌فرض ۱۲۸ مگ)        │    │
│  │  - کش data pages                         │    │
│  └─────────────────────────────────────────┘    │
│  ┌─────────────────────────────────────────┐    │
│  │  WAL buffers                             │    │
│  │  - بافر برای WAL records قبل از flush   │    │
│  └─────────────────────────────────────────┘    │
│  ┌─────────────────────────────────────────┐    │
│  │  CLOG، subtransaction، notify queue     │    │
│  └─────────────────────────────────────────┘    │
└─────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────┐
│         Process-Local Memory (هر backend)        │
│  - work_mem (sort، hash join)                   │
│  - maintenance_work_mem (CREATE INDEX، VACUUM)  │
│  - temp_buffers                                  │
└─────────────────────────────────────────────────┘

MVCC – Multi-Version Concurrency Control

MVCC یعنی هر تراکنش یک snapshot از داده در زمان شروع را می‌بیند، حتی اگر تراکنش‌های دیگر در حال تغییر داده باشند. این کار بدون lock کردن انجام می‌شود.

چطور کار می‌کند؟

هر row در PostgreSQL دو فیلد مخفی دارد:

• xmin: شناسه تراکنشی که این نسخه را ایجاد کرده
• xmax: شناسه تراکنشی که این نسخه را delete/update کرده (0 اگر هنوز معتبر است)

-- می‌توانید این فیلدهای مخفی را ببینید:
SELECT xmin, xmax, * FROM products LIMIT 5;

 xmin  | xmax | id | name        | price
-------+------+----+-------------+--------
 1234  |    0 |  1 | فرش کاشان   | 5000000
 1235  |    0 |  2 | گلیم        | 1500000

UPDATE در PostgreSQL

وقتی یک row را UPDATE می‌کنید، PostgreSQL:

1. row قدیمی را به‌جای حذف، با xmax تراکنش جاری علامت‌گذاری می‌کند
2. یک row جدید با xmin تراکنش جاری ایجاد می‌کند
3. تراکنش‌های قدیمی هنوز row قدیمی را می‌بینند (Read Committed/Repeatable Read)
4. تراکنش‌های جدید row جدید را می‌بینند

این dead tuple‌ها بعداً توسط VACUUM پاک می‌شوند.

مزیت‌های MVCC

• Reader هرگز Writer را block نمی‌کند
• Writer هرگز Reader را block نمی‌کند
• Read consistent بدون lock
• Concurrency بسیار بالا

هزینه MVCC

• Table bloat: dead tuples فضا اشغال می‌کنند
• نیاز به VACUUM دوره‌ای (autovacuum خودکار انجام می‌دهد)
• UPDATE گران‌تر از MySQL است

WAL – Write-Ahead Log

WAL (Write-Ahead Log) قلب durability و crash recovery در PostgreSQL است. قاعده:

قبل از تغییر data files، تغییر را در WAL بنویس.

Transaction Begins
       │
       ▼
   تغییر در shared_buffers (RAM)
       │
       ▼
   نوشتن WAL record در WAL buffers
       │
       ▼
   COMMIT → flush WAL به دیسک (fsync)
       │
       ▼
   تایید commit به client
       │
       ▼
   (بعداً) Background Writer داده را به data files می‌نویسد
       │
       ▼
   Checkpoint: همه dirty pages flush می‌شوند

چرا WAL اهمیت دارد؟

• Durability: اگر سرور crash کند، می‌توان از WAL داده را بازسازی کرد
• Performance: WAL sequential write است، بسیار سریع‌تر از random write به data files
• Replication: streaming WAL به standby
• PITR (Point-in-Time Recovery): با WAL می‌توان به هر زمان بازگشت

ذخیره داده روی دیسک

$PGDATA/                          ← مسیر داده (مثل /var/lib/postgresql/16/main)
├── base/                          ← دیتابیس‌ها
│   ├── 1/                         ← OID دیتابیس template1
│   ├── 13728/                     ← OID دیتابیس postgres
│   │   ├── 16384                  ← فایل جدول (به نام OID جدول)
│   │   ├── 16385                  ← ایندکس
│   │   └── ...
│   └── 16400/                     ← OID دیتابیس کاربر
├── pg_wal/                        ← WAL files
│   ├── 000000010000000000000001
│   └── ...
├── pg_xact/                       ← Commit log
├── pg_stat_tmp/                   ← آمار موقت
├── postgresql.conf                ← پیکربندی اصلی
├── pg_hba.conf                    ← قوانین احراز هویت
└── postmaster.pid                 ← PID پروسه اصلی

Page و Tuple

هر table در PostgreSQL از page‌های ۸ کیلوبایتی تشکیل شده. هر page شامل چند tuple (ردیف) است:

┌─────────────────────────────────┐  Page 1 (8KB)
│ Page Header (24 bytes)          │
├─────────────────────────────────┤
│ ItemId 1 → offset 7900          │
│ ItemId 2 → offset 7800          │
│ ItemId 3 → offset 7700          │
│ ...                              │
├─────────────────────────────────┤
│         Free Space               │
├─────────────────────────────────┤
│ Tuple 3 (xmin, xmax, data...)   │
│ Tuple 2 (xmin, xmax, data...)   │
│ Tuple 1 (xmin, xmax, data...)   │
└─────────────────────────────────┘

Extensions – قابلیت Plug-in

PostgreSQL با extension‌ها می‌تواند قابلیت‌های جدید بگیرد:

-- لیست extension‌های نصب‌شده
SELECT * FROM pg_extension;

-- لیست extension‌های موجود برای نصب
SELECT * FROM pg_available_extensions ORDER BY name;

-- نصب یک extension
CREATE EXTENSION pg_trgm;          -- جستجوی fuzzy
CREATE EXTENSION uuid-ossp;        -- توابع UUID
CREATE EXTENSION pgcrypto;         -- رمزنگاری
CREATE EXTENSION postgis;          -- داده‌های مکانی
CREATE EXTENSION pg_stat_statements; -- آمار query‌ها

Extension‌های محبوب

• PostGIS: تبدیل PG به یک Spatial Database حرفه‌ای
• TimescaleDB: time-series database روی PG
• Citus: sharding توزیع‌شده
• pg_partman: مدیریت partitioning
• pg_repack: rebuild جدول بدون lock
• pgvector: vector embedding برای AI/ML

نسخه‌بندی

PostgreSQL هر سال یک major version جدید منتشر می‌کند:

جمع‌بندی

• PostgreSQL یک ORDBMS قدرتمند و standards-compliant است
• معماری process-based: یک پروسه به ازای هر اتصال
• MVCC: concurrency بدون lock، با هزینه dead tuples
• WAL: لاگ تغییرات قبل از اعمال – برای durability و recovery
• Extensions: قابلیت plug-in قدرتمند
• تفاوت اساسی با MySQL: غنی‌تر، استانداردتر، scalable‌تر برای کارهای پیچیده

در فصل بعد، PostgreSQL را نصب می‌کنیم و فایل‌های پیکربندی را بررسی می‌کنیم.