Devlog

postgres advance

7 min read

PostgreSQL — Shared Buffer, Cache, Query Processor & Indexing

Shared Buffer

خیلی وقت‌ها حجم نوشتن و خواندن بالا است؛ به‌جای I/O فیزیکی مستقیم روی فایل‌های دیتابیس، PostgreSQL از shared_buffers استفاده می‌کند: داده‌ها در حافظه خوانده/نوشته می‌شوند و سپس به‌صورت منطقی (logical I/O) و دسته‌ای به دیسک منتقل می‌شوند.

TIP

استفاده از shared buffers باعث کاهش I/O فیزیکی و بهبود عملکرد می‌شود؛ تنظیم بهینه بستگی به RAM سیستم و نوع بار کاری دارد.

OS Cache

علاوه بر shared buffer، یک لایهٔ کش دیگر در سطح سیستم‌عامل وجود دارد (OS page cache). این لایه یک «اسنپ‌شات» از صفحات فایل‌ها نگه می‌دارد و جلوی I/O فیزیکی اضافی را می‌گیرد. زمان‌هایی که چندین تغییر روی دیتابیس انجام می‌شود، تغییرات ابتدا در کش‌ها باقی مانده و سپس به‌صورت دسته‌ای (flush) روی فایل‌های فیزیکی نوشته می‌شوند.

Query Processor

یکی از بخش‌های اصلی PostgreSQL، Query Processor است که وظیفه‌اش دریافت کوئری و بازگرداندن نتیجه است. معمولاً از ۵ بخش تشکیل شده است:

1. Connection

بررسی و مدیریت اتصال کلاینت به سرور.

2. Parser

بررسی سینتکس کوئری؛ اگر درست باشد آن را به صورت یک درخت (parse tree) تولید می‌کند.

3. Rewriter

قابلیت بازنویسی کوئری (مثل rule system). (در این سند: توضیح بیشتر بعداً مطالعه شود).

4. Planner / Optimizer

مسئلهٔ اصلی: پیدا کردن بهینه‌ترین مسیر برای واکشی داده — انتخاب جدول‌ها، ایندکس‌ها، یا حتی انتخاب نکردن ایندکس.

مثال: اگر تعداد ردیف‌های برگشتی کم باشد، ممکن است از ایندکس استفاده شود؛ اما اگر بخش زیادی از جدول قرار است خوانده شود، sequential scan انتخاب بهتری است.

5. Executor

اجرای پلان انتخاب‌شده و بازگرداندن نتیجه. در صورت وجود JOIN، داده‌ها را از چند جدول ترکیب می‌کند.

Checkpoint

وقتی داده‌ای INSERT/UPDATE می‌شود، به‌صورت مستقیم روی فایل‌های اصلی نوشته نمی‌شود تا از توقف مکرر جلوگیری شود؛ در عوض داده «dirty» در حافظه نگه داشته می‌شود. تغییرات در WAL ذخیره می‌شوند و در زمان checkpoint به‌صورت دسته‌ای (flush) روی فایل‌های فیزیکی اعمال می‌شوند.

این مزیت را دارد که اگر crash رخ دهد، از آخرین checkpoint به‌بعد WALها قابل استفاده برای بازیابی (recovery) هستند.

NOTE

مقدار پیش‌فرض checkpoint ممکن است بر اساس تعداد ردیف یا بازهٔ زمانی (مثلاً هر 5 دقیقه) تعیین شود.

WAL (Write-Ahead Log)

WAL یک buffer اصلی (wal master buffer) دارد که برای سازگاری و recovery استفاده می‌شود. می‌توان از WALهای اضافی هم استفاده کرد تا قابلیت‌هایی مثل replica و recovery فراهم شود.

  • کاربردها:

    • recovery
    • replication (streaming replica)

هشدار: غیرفعال کردن WAL می‌تواند سرعت نوشتن را افزایش دهد، اما در صورت crash داده‌ها از بین می‌روند.

VACUUM

Default VACUUM

  • به‌صورت دوره‌ای اجرا می‌شود و تاپل‌هایی که mark removed شده‌اند را پاک می‌کند.
  • نیازی به قفل طولانی ندارد، ولی همواره فضای آزاد را به OS بازنمی‌گرداند (فقط برای همان جدول قابل‌استفاده مجدد می‌شود).
  • می‌توان پارامترها مثل بازه‌ها و thresholds را تنظیم کرد.

سوال: چرا بعضی‌وقت‌ها بهتر است از VACUUM FULL استفاده کنیم؟

پاسخ: VACUUM FULL فضا را به OS بازمی‌گرداند اما قفل کامل روی جدول می‌گیرد و زمان‌بر است. موارد لازم:

  • اگر حجم جدول خیلی بیشتر از اندازهٔ واقعی شده باشد.
  • هنگام تغییرات ساختاری بزرگ در دیتابیس.

VACUUM FULL

  • اجرای دستی (قفل‌گیرنده).
  • علاوه بر حذف تاپل‌های مرده، فضای دیسک را فشرده و بازمی‌گرداند.
  • ایندکس‌ها و رِلِیشِن‌ها نیز تحت تأثیر قرار می‌گیرند، پس زمان قفل طولانی می‌شود.

Indexing

  • برای بررسی حجم و ایندکس‌ها می‌توان از ابزارهای متنوع استفاده کرد.
  • sequential scan یعنی جدول ایندکس نشده یا ایندکس‌ها برای آن کوئری مناسب نیستند.

Bitmap Scan

وقتی یک کوئری کامل ایندکس نشده ولی می‌توان از ترکیب چند ایندکس استفاده کرد، PostgreSQL می‌تواند bitmap index scan انجام دهد: ایندکس‌ها را بلاک‌به‌بلاک بررسی کرده و یک bitmap از بلاک‌های احتمالی می‌سازد و سپس فقط بلاک‌های لازم را از جدول بخواند.

مثال:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department_id INT,
    salary INT
);
 
CREATE INDEX idx_department ON employees(department_id);
CREATE INDEX idx_salary ON employees(salary);
 
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department_id = 5 AND salary > 100000;

خروجی نمونه (شرح):

Bitmap Heap Scan on employees  (cost=8.45..25.11 rows=5 width=44)
  Recheck Cond: ((department_id = 5) AND (salary > 100000))
  ->  BitmapAnd  (cost=8.45..8.45 rows=5 width=0)
        ->  Bitmap Index Scan on idx_department  (cost=0.00..4.20 rows=50 width=0)
              Index Cond: (department_id = 5)
        ->  Bitmap Index Scan on idx_salary  (cost=0.00..4.20 rows=50 width=0)
              Index Cond: (salary > 100000)

Combine Index (Multi-column / Composite Index)

ایندکس‌های ترکیبی با btree چند ستون را پوشش می‌دهند و ترتیبِ ستون‌ها (left-to-right) مهم است. اگر می‌دانیم یک کوئری خاص غالب است، می‌توان ستون‌های مرتبط را در ایندکس ترکیبی قرار داد تا نیازی به join بین ایندکس و جدول نباشد.

نکته: این کار زمانی مفید است که تعداد ستون‌های اضافی کوچک باشد و نتایج به‌صورت پوشش‌دهی (covering index) کارایی را بالا ببرد.

Combine vs Bitmap Index

  • اگر چندین ستون را جدا ایندکس کنیم، تنوع کوئری‌ها بیشتر پشتیبانی می‌شود.
  • اما برای کوئری مشخص، ممکن است PostgreSQL نیاز به BitmapAnd و ادغام ایندکس‌ها داشته باشد که نیازمند حافظه و CPU بیشتر است.
  • Composite index سریع‌تر است وقتی که ترتیب شروط با ترتیب ایندکس هم‌خوانی داشته باشد.

Partial Index (Filtered Index)

گاهی ایندکسی که می‌سازیم باید فقط مطابق یک شرط خاص باشد (مثلاً فقط برای ردیف‌هایی با status = 'success') — در این حالت partial index مناسب است.

CREATE INDEX index_name ON table_name(column_list)
WHERE condition;
 
-- مثال: ایمیل یکتا فقط برای کاربران فعال
CREATE UNIQUE INDEX idx_users_unique_active_email ON users(email)
WHERE is_active = true;

این روش اجازه می‌دهد: برای کاربران غیرفعال، تکرار ایمیل مجاز باشد ولی برای کاربران فعال، ایمیل یکتا حفظ شود.

Table Cluster

اگر می‌خواهید ترتیب فیزیکی یک جدول را بر اساس یک معیار مرتب کنید (مثلاً بر اساس یک ایندکس)، می‌توانید از CLUSTER استفاده کنید. این کار جدول را قفل کرده، کپی می‌سازد و داده‌ها را بر اساس ایندکس مرتب می‌کند؛ اما با گذشت زمان ترتیب جدید حفظ نمی‌شود مگر vacuum/cluster مجدد.

معمولاً نیازمندی‌های CLUSTER را می‌توان با VACUUM FULL یا بازآرایی هدفمند کاهش داد.

Enum vs String

  • ENUM حجم کمتری دارد، خوانایی بالاتر و سرعت دسترسی اندکی بهتر.
  • معایب: پشتیبانی کمتر در برخی دیتابیس‌ها، مدیریت پیچیده‌تر در ORMها (مثل GORM)، و عدم انعطاف در تعداد زیاد مقادیر.

Fill Factor

پارامتری برای رزرو فضای خالی در صفحات جدول برای عملیات UPDATE. مثلاً FILLFACTOR = 70 باعث می‌شود ۳۰٪ صفحه برای آپدیت‌ها آزاد بماند تا از HOT updates بهتر پشتیبانی شود.

Query Statistics & Monitoring

هدف: مشخص کردن کوئری‌های کند و ذخیرهٔ متریک‌ها در طول زمان.

pg_stat_statements

  • یکی از مهم‌ترین اکستنشن‌ها برای مشاهدهٔ پرمصرف‌ترین کوئری‌ها و میزان منابع مصرفی آنها.
  • فعال‌سازی ممکن است چند درصد overhead داشته باشد اما دادهٔ مفیدی برای بهینه‌سازی فراهم می‌کند.

pg_stat_user_tables

این نما اطلاعاتی درباره جدول‌های ساخته‌شده توسط کاربران ارائه می‌دهد، از جمله:

  • تعداد تاپل‌های مرده و زنده (برای ارزیابی نیاز به vacuum)
  • تعداد اسکن‌های sequential و index و مواردی که خوانده اما فِچ نشده‌اند.

این داده‌ها کمک می‌کند بفهمیم کدام ایندکس‌ها کاربردی هستند و کدام کوئری‌ها نیازمند بهبودند.

تفاوت Prepared Statements در MySQL vs PostgreSQL

  • در MySQL: prepared statementها ممکن است در cache سراسری (global cache) نگهداری شوند.
  • در PostgreSQL: prepared statement به ازای هر کانکشن ساخته می‌شود؛ اگر کانکشن قطع شود، باید مجدداً ساخته شود.

ID Generation: خودتان یا PostgreSQL؟

  • اگر حجم داده خیلی زیاد یا چند نود دارید، ممکن است بخواهید IDها را خودتان تولید کنید (مثلاً UUID یا Snowflake) تا از مشکلات مقیاس و همزمانی جلوگیری کنید.
  • اگر یک نود و حجم کم/متوسط دارید، بهتر است PostgreSQL SERIAL/IDENTITY را استفاده کنید — تضمینِ یونیک بودن و عملکرد مناسب.

برای گرفتن id تولیدشده توسط PostgreSQL:

INSERT INTO table_name(columns...) VALUES(...) RETURNING id;

Graph View