بررسی متادیتا و ساختار داخلی جداول

PostgreSQL یک سیستم مدیریت پایگاه داده رابطه‌ای قدرتمند است که همه چیز در آن، از جداول گرفته تا توابع و ایندکس‌ها، به صورت منظم در دایرکتوری داده‌ها نگهداری می‌شود. فهمیدن اینکه هر جدول و هر ستون کجا و چگونه ذخیره می‌شود، به ما کمک می‌کند عملکرد دیتابیس را بهتر درک کنیم و کار با متادیتا را ساده‌تر کنیم.

۱. دایرکتوری base/ و پایگاه داده‌ها

وقتی PostgreSQL اجرا می‌شود، هر پایگاه داده در دایرکتوری base/ یک پوشه مخصوص دارد. نام پوشه همان OID پایگاه داده است:

.../base/
├── ۱      ← template0
├── ۴      ← template1
├── ۵      ← postgres
└── ۱۶۳۸۴  ← یک پایگاه داده کاربری

• داخل هر پوشه فایل‌هایی وجود دارد که هر کدام relfilenode خاص خود را دارند و جدول، ایندکس یا sequence مربوطه را نشان می‌دهند.
• برای اینکه بفهمیم هر OID مربوط به کدام پایگاه داده است، می‌توانیم این کوئری را اجرا کنیم:

SELECT oid, datname
FROM pg_database
ORDER BY oid;

نتیجه مثلاً چنین چیزی خواهد بود:

  oid  | datname
-------+-----------
     ۱ | template0
     ۴ | template1
 ۱۶۳۸۴ | postgres

پس پوشه base/16384 همان پایگاه داده postgres است.

۲. اسکیماها (Schemas) در PostgreSQL

هر پایگاه داده مجموعه‌ای از اسکیماها دارد. اسکیما مثل یک پوشه منطقی است که جداول و سایر اشیاء در آن دسته‌بندی می‌شوند.

SELECT oid, nspname
FROM pg_namespace
ORDER BY oid;

نمونه خروجی:

  oid  |      nspname
-------+--------------------
    ۱۱ | pg_catalog
    ۹۹ | pg_toast
  ۲۲۰۰ | public
 ۱۳۲۹۳ | information_schema

• pg_catalog: قلب PostgreSQL، شامل تمام جداول و توابع سیستمی.
• pg_toast: جداول مخفی برای مقادیر بزرگ (text, bytea, jsonb).
• public: اسکیما پیش‌فرض کاربر، جایی که جداول شما معمولاً ساخته می‌شوند.
• information_schema: نمای استاندارد SQL برای مشاهده متادیتا، قابل استفاده در DBهای دیگر.

۳. بررسی جداول و ایندکس‌ها

هر جدول یا ایندکس یک relfilenode دارد که نام فایل روی دیسک است:

SELECT
    n.nspname AS schema,
    c.relname AS object_name,
    c.relfilenode AS file_id,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'i' THEN 'index'
        WHEN 'S' THEN 'sequence'
        WHEN 't' THEN 'toast table'
        ELSE c.relkind
    END AS object_type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relfilenode IS NOT NULL
ORDER BY c.relfilenode;

مثلاً فایل base/16384/24576 مربوط به جدول یا ایندکس خاصی است و با این کوئری می‌توان نام آن را پیدا کرد:

SELECT pg_relation_filepath('public.my_table'::regclass) AS file_path;

۴. TOAST: جداول بزرگ مخفی

• TOAST = The Oversized-Attribute Storage Technique
• برای ستون‌های بزرگ، PostgreSQL به صورت خودکار یک جدول مخفی در pg_toast می‌سازد.
• ارتباط جدول با TOAST در pg_class.reltoastrelid ذخیره می‌شود.

SELECT relname, reltoastrelid
FROM pg_class
WHERE reltoastrelid <> 0;

۵. کاتالوگ‌های سیستم

۶. ساختار فیزیکی فایل‌ها

PGDATA/
├── base/        ← دیتابیس‌ها (هر DB یک پوشه OID)
├── global/      ← جداول اشتراکی cluster-wide
├── pg_tblspc/   ← جداولی که در tablespace ذخیره شده‌اند
├── pg_wal/      ← لاگ تراکنش‌ها
└── pg_stat/     ← فایل‌های آماری

• global/: شامل جداولی است که بین همه پایگاه داده‌ها مشترک است، مثل pg_authid و pg_database.
• base/<db_oid>/: شامل جداول سیستم و کاربر هر پایگاه داده است.
• TOAST و ایندکس‌ها فایل‌های جداگانه دارند.

۷. تراکنش‌ها و همگام‌سازی

• هر پایگاه داده مستقل است و تغییرات در جداول اتمیک و تراکنشی انجام می‌شوند.
• هنگام ایجاد پایگاه داده جدید با CREATE DATABASE mydb TEMPLATE template1:
  1. یک ردیف جدید در pg_database اضافه می‌شود.
  2. تمام فایل‌ها و کاتالوگ‌های template1 به دایرکتوری جدید کپی می‌شوند.
  3. تراکنش commit می‌شود و DB جدید آماده استفاده است.
• نیازی به همگام‌سازی دستی بین دیتابیس‌ها وجود ندارد.

۸. بررسی مسیر فایل یک جدول

برای پیدا کردن فایل ذخیره جدول:

SELECT pg_relation_filepath('public.my_table'::regclass) AS file_path;

• این تابع مسیر فایل روی دیسک را نسبت به PGDATA نشان می‌دهد.
• جداول بزرگ، TOAST و ایندکس‌ها هر کدام فایل‌های جدا دارند.

۹. نکات مهم

• pg_catalog = اسکیما منطقی برای جداول سیستم
• base/ = مسیر فیزیکی جداول و ایندکس‌ها
• global/ = جداول مشترک در سطح cluster
• TOAST = ذخیره‌سازی مقادیر بزرگ (text, jsonb, bytea)
• تغییرات متادیتا اتمیک و تراکنشی هستند، همگام‌سازی اضافی لازم نیست.

🔗 منابع و کوئری‌های کاربردی

۱. لیست تمام جداول و مسیر فایل آن‌ها

SELECT
  n.nspname AS schema,
  c.relname AS table_name,
  c.relfilenode,
  pg_relation_filepath(c.oid) AS path
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY n.nspname, c.relname;

۲. مشاهده پایگاه داده‌ها و مسیر storage

SELECT datname, oid, pg_tablespace_location(dattablespace) AS tablespace_path
FROM pg_database;

۳. مشاهده ستون‌های یک جدول

SELECT attnum, attname, atttypid::regtype AS data_type
FROM pg_attribute
WHERE attrelid = 'public.my_table'::regclass
  AND attnum > 0
ORDER BY attnum;