آموزش بیش از ۵۰۰ سوال مصاحبه PostgreSQL به همراه پاسخ‌ها ۲۰۲۶ - آخرین آپدیت

پوشش جامع حوزه‌های آزمون

این بانک تست تمرینی به صورت سیستماتیک سازماندهی شده است تا با توزیع فنی و سطوح پیچیدگی موجود در مصاحبه‌های واقعی مهندسی و مدیریت پایگاه داده مطابقت داشته باشد.

• بهینه‌سازی کوئری و تنظیم عملکرد (۲۵٪): تحلیل عمیق طرح‌های اجرای EXPLAIN و EXPLAIN ANALYZE، تنظیمات هدفمند ایندکس، استراتژی‌های Join (Nested Loop, Hash Join, Merge Join)، تکنیک‌های پیشگیرانه بهینه‌سازی کوئری و تنظیم دقیق تخصیص حافظه با استفاده از work_mem و تاکتیک‌های پیشرفته دسته‌بندی (Batching).

• SQL و تفکر رابطه‌ای (۲۰٪): تسلط بر SQL پیچیده، عملیات پیشرفته CRUD، مدل‌سازی داده‌های تراکنشی، اصول هسته پایگاه داده‌های رابطه‌ای و حفظ ویژگی‌های سخت‌گیرانه ACID در لایه‌های برنامه‌های با هم‌روندی بالا.

• طراحی و معماری پایگاه داده (۱۵٪): اصول طراحی پایگاه داده در سطح Production، طراحی اسکیمای تمیز، مدیریت Tablespaceهای سفارشی، پارتیشن‌بندی افقی (اعلامی و مبتنی بر ارث‌بری) و راه‌اندازی معماری Replication قدرتمند (Streaming, Logical و نودهای High-Availability).

• هم‌روندی، قفل‌شدگی و مدیریت تراکنش (۱۰٪): کنترل داخلی هم‌روندی، مکانیسم‌های قفل‌شدگی (در سطح سطر، جدول و Advisory locks)، پردازش در سطوح مختلف جداسازی تراکنش، مدیریت پیشگیرانه Deadlock و سازوکارهای داخلی کنترل هم‌روندی چندنسختی (MVCC).

• انواع داده‌ها، محدودیت‌ها و دامین‌ها (۱۰٪): انواع داده‌های پیشرفته (هندسی، شبکه، Enums سفارشی)، اعمال قوانین کسب‌وکار از طریق Constraints و Domains، استراتژی‌های اصلی ایندکس‌گذاری (B-Tree, BRIN, GIN, GiST) و قوانین نرمال‌سازی هدفمند.

• مباحث پیشرفته و بهترین تجربیات (۱۰٪): شتاب‌بخشی به ویژگی‌های برنامه با استفاده از جستجوی تمام‌متنی (Full-Text Search)، پشتیبانی و ایندکس‌گذاری کارآمد JSON/JSONB، نوشتن Store Procedureها و Triggerهای بهینه، و پیاده‌سازی تکنیک‌های پیشرفته ایندکس مانند ایندکس‌های Partial یا Expression.

• امنیت، پشتیبان‌گیری و بازیابی (۵٪): رعایت استانداردهای امنیتی Production، طراحی استراتژی‌های ضدخطای پشتیبان و بازیابی (PITR, pg_dump, pg_basebackup)، الگوهای پیچیده مدیریت تراکنش و نگهداری سیستماتیک Vacuum و آمارها.

• عیب‌یابی و نگهداری (۵٪): مدیریت سریع خطاها، تحلیل ساختاریافته لاگ‌های موتور، مانیتورینگ عملکرد در لحظه، وظایف حیاتی نگهداری پایگاه داده و استخراج بینش‌های سیستمی زنده با استفاده از Viewهای pg_stat_activity و pg_stat_statements.

درباره دوره

موفقیت در مصاحبه برای نقش‌های توسعه‌دهنده PostgreSQL، مدیر پایگاه داده (DBA) یا معمار داده، بسیار فراتر از نوشتن دستورات ساده SELECT است. تیم‌های داده‌محور مدرن به مهندسانی نیاز دارند که بدانند هنگام اجرای یک کوئری در پشت صحنه چه اتفاقی می‌افتد، موتور چگونه تغییرات هم‌زمان را بدون مسدود کردن مدیریت می‌کند و چگونه فضای ذخیره‌سازی را از طریق پارتیشن‌بندی و Replication هوشمند مقیاس‌بندی می‌کند. من این مخزن جامع تست‌های تمرینی را برای بازسازی دقیق چالش‌های فنی، پازل‌های بهینه‌سازی و دلماهای معماری طراحی کرده‌ام که مصاحبه‌کنندگان تراز اول برای ارزیابی متخصصان پایگاه داده از آن‌ها استفاده می‌کنند.

این دوره با ۵۵۰ سوال عمیق و بسیار فنی، کاملاً بر سناریوهای دنیای واقعی تمرکز دارد. به جای تست اصطلاحات پایه، من طرح‌های اجرای پیچیده، ناهنجاری‌های تراکنشی، اشتباهات پیکربندی و چالش‌های طراحی ساختاری را ارائه می‌دهم. هر سوال دارای یک تحلیل فنی جامع است که توضیح می‌دهد چرا راه‌حل بهینه به این شکل عمل می‌کند و تحلیل می‌کند که چرا سایر تغییرات در پیکربندی یا کوئری در محیط عملیاتی شکست می‌خورند. چه بخواهید بر وضعیت‌های قفل MVCC مسلط شوید، چه مصرف حافظه را از طریق پارامترهای work_mem بهینه کنید و یا با ساختارهای پیچیده ایندکس‌گذاری به طور ایمن کار کنید، این منبع تمرین‌های سخت‌گیرانه‌ای را در اختیار شما قرار می‌دهد تا تخصص خود را تایید کرده و در اولین تلاش، مراحل فنی را پشت سر بگذارید.

نمونه سوالات تمرینی

این سه نمونه سوال را بررسی کنید تا با عمق، ساختار و کیفیت توضیحات فنی ارائه شده در این بانک سوالات آشنا شوید.

سوال ۱: تحلیل انواع Scan در طرح‌های اجرای پیچیده EXPLAIN

هنگام پروفایل کردن یک کوئری کند روی جدولی با ۵۰ میلیون سطر، دستور EXPLAIN ANALYZE را اجرا می‌کنید. خروجی یک Bitmap Heap Scan را نشان می‌دهد که بلافاصله پس از یک Bitmap Index Scan آمده است. کوئری از یک عبارت WHERE پیچیده استفاده می‌کند که دو ستون را فیلتر می‌کند که هر کدام ایندکس‌های B-Tree مجزا و مستقلی دارند. این توالی اجرای خاص چه چیزی را در مورد نحوه حل کوئری توسط موتور نشان می‌دهد؟

• الف) برنامه‌ریز کوئری کاملاً ایندکس‌گذاری را رها کرده و در حال اجرای یک اسکن متوالی موازی (Parallel Sequential Scan) روی چندین رشته Worker در پس‌زمینه است.

• ب) موتور ابتدا کل ساختار فیزیکی جدول را در حافظه می‌خواند و سپس سطرها را با استفاده از استراتژی Hash Map در حافظه فیلتر می‌کند.

• پ) برنامه‌ریز به طور هم‌زمان اشاره‌گرهای سطرهای فیزیکی مطابق را از هر دو ایندکس استخراج کرده، آن‌ها را در یک آرایه Bitmap بهینه در حافظه ترکیب می‌کند و سپس فقط بلوک‌های داده هدف را از دیسک فراخوانی می‌کند.

• ت) یک فساد ساختاری شدید در ایندکس رخ داده است که موتور پایگاه داده را مجبور می‌کند برای تکمیل بلوک خواندن، به یک فایل کش موقت و کندتر روی آورد.

• ث) توزیع داده‌ها کاملاً یکنواخت است و باعث شده برنامه‌ریز کوئری به اشتباه هدر جدول را هنگام تلاش برای ایندکس مجدد ویژگی‌های هدف در لحظه، قفل کند.

• ج) تخصیص پیکربندی work_mem کاملاً به پایان رسیده است، که برنامه‌ریز را مجبور می‌کند تمام ساختارهای مرتب‌سازی میانی را مستقیماً در فضای Swap فیزیکی بنویسد.

پاسخ صحیح و توضیح:

• پاسخ صحیح: پ

• چرا درست است: یک Bitmap Index Scan ساختارهای ایندکس را ارزیابی می‌کند تا ورودی‌های مطابق را بیابد و یک بیت‌مپ داده در حافظه ایجاد می‌کند که صفحات فیزیکی دقیق حاوی آن سطرها را نشان می‌دهد. وقتی از چندین ایندکس مستقل استفاده می‌شود، PostgreSQL می‌تواند اسکن‌های ایندکس بیت‌مپ مجزا را انجام دهد، بیت‌مپ‌ها را با استفاده از عملیات AND/OR بیتی ترکیب کند و سپس بیت‌مپ نهایی را به Bitmap Heap Scan تحویل دهد تا فقط صفحات داده مربوطه را از دیسک بگیرد. این کار در مقایسه با اسکن استاندارد ایندکس، از I/O تصادفی دیسک جلوگیری می‌کند.

• چرا گزینه‌های دیگر غلط هستند:

  • گزینه الف غلط است: یک اسکن متوالی موازی به صراحت در طرح‌های اجرا به عنوان Parallel Sequential Scan برچسب می‌خورد و بیت‌مپ‌های ایندکس تولید نمی‌کند.

  • گزینه ب غلط است: خواندن کل جدول در حافظه، توصیف‌کننده یک ساختار متوالی بدون ایندکس یا کشینگ شدید است، نه یک بهینه‌سازی هدفمند بیت‌مپ.

  • گزینه ت غلط است: فساد ایندکس باعث بروز خطاهای سیستمی صریح و شکست کوئری می‌شود، نه یک عملیات استاندارد و سالم اسکن بیت‌مپ.

  • گزینه ث غلط است: توزیع یکنواخت داده‌ها معمولاً برنامه‌ریز را ترغیب می‌کند تا اگر تشخیص دهد ایندکس‌گذاری هزینه I/O را کاهش نمی‌دهد، یک Sequential Scan ساده انجام دهد.

  • گزینه ج غلط است: در حالی که work_mem پایین می‌تواند باعث شود یک اسکن بیت‌مپ به حالت "lossy" (ردیابی صفحات به جای سطرهای خاص) تبدیل شود، اما باعث ایجاد ترتیب ساختاری توالی اسکن بیت‌مپ نمی‌شود.

سوال ۲: ارزیابی دینامیک Deadlock در MVCC تحت سطح جداسازی Read Committed

دو تراکنش موازی (تراکنش آلفا و تراکنش بتا) به طور هم‌زمان تحت سطح جداسازی پیش‌فرض Read Committed در حال اجرا هستند. هر دو تراکنش سعی می‌کنند مجموعه‌ای از سطرهای یک جدول را به‌روزرسانی کنند. تراکنش آلفا سطر ۱۰ را به‌روزرسانی می‌کند و سپس سعی می‌کند سطر ۲۰ را به‌روزرسانی کند. به طور هم‌زمان، تراکنش بتا سطر ۲۰ را به‌روزرسانی می‌کند و سپس بلافاصله سعی می‌کند سطر ۱۰ را به‌روزرسانی کند. PostgreSQL این توالی از عملیات هم‌زمان را به صورت داخلی چگونه مدیریت می‌کند؟

• الف) موتور به طور خودکار تراکنش‌ها را با استفاده از قفل‌های داخلی در سطح جدول سریال‌سازی می‌کند و تراکنش بتا را مجبور می‌کند بدون انتظار، فوراً Rollback شود.

• ب) هر دو به‌روزرسانی فوراً موفق می‌شوند زیرا معماری MVCC نسخه‌های سطرهای کاملاً ایزوله ایجاد می‌کند و به موتور اجازه می‌دهد مقادیر متضاد را در چرخه Vacuum بعدی ادغام کند.

• پ) دستور به‌روزرسانی دوم در هر تراکنش وارد وضعیت مسدود شده (Blocking) می‌شود تا زمانی که یک رشته تشخیص Deadlock در پس‌زمینه، چرخه وابستگی متقابل را شناسایی کرده و یک تراکنش را مجبور به لغو با خطا کند.

• ت) تراکنش آلفا تغییرات ثبت نشده توسط تراکنش بتا را می‌خواند، شمارنده داخلی خود را به‌روز می‌کند و بدون مسدود شدن، پردازش را زودتر از موعد به پایان می‌رساند.

• ث) موتور هر دو قفل سطح سطر را به یک قفل انحصاری در سطح کل پایگاه داده ارتقا می‌دهد و تمام کوئری‌های خواندن برنامه ورودی خارجی را تا زمان بسته شدن هر دو اتصال مسدود می‌کند.

• ج) تراکنش‌ها فوراً با خطای شکست سریال‌سازی (Serialization Failure) مواجه می‌شوند زیرا تغییر هم‌زمان سطرهای یکسان تحت سطح Read Committed اکیداً ممنوع است.

پاسخ صحیح و توضیح:

• پاسخ صحیح: پ

• چرا درست است: PostgreSQL برای تغییرات داده از قفل‌های سطح سطر (Row-level locks) استفاده می‌کند. تراکنش آلفا سطر ۱۰ و تراکنش بتا سطر ۲۰ را قفل می‌کند. وقتی آلفا سعی می‌کند سطر ۲۰ را به‌روزرسانی کند، مسدود می‌شود و منتظر می‌ماند تا بتا قفل را آزاد کند. وقتی بتا سعی می‌کند سطر ۱۰ را به‌روزرسانی کند، او هم مسدود شده و منتظر آلفا می‌ماند. این یک وابستگی چرخشی ایجاد می‌کند. تشخیص‌دهنده Deadlock در PostgreSQL (پارامتر deadlock_timeout) به طور منظم این وضعیت را بررسی کرده و راه‌حل آن است که عمداً یکی از تراکنش‌های مسدود شده را متوقف کند تا دیگری بتواند به کار خود ادامه دهد.

• چرا گزینه‌های دیگر غلط هستند:

  • گزینه الف غلط است: PostgreSQL به‌روزرسانی‌های سطر را به طور خودکار و تهاجمی به قفل‌های سطح جدول ارتقا نمی‌دهد و تراکنش‌ها را بدون انتظار برای Timeout یا تشخیص مسدودیت، فوراً لغو نمی‌کند.

  • گزینه ب غلط است: MVCC به خوانندگان اجازه می‌دهد از مسدود کردن نویسندگان اجتناب کنند، اما نویسندگانی که به سطرهای فیزیکی دقیقاً یکسان دسترسی دارند باید قفل‌های انحصاری سطر را به دست آورند؛ آن‌ها نمی‌توانند داده‌های متضاد را به طور هم‌زمان بنویسند.

  • گزینه ت غلط است: جداسازی Read Committed خواندن‌های کثیف (Dirty Reads) را ممنوع می‌کند؛ تراکنش‌ها هرگز نمی‌توانند تغییرات ثبت نشده از نشست‌های موازی را ببینند یا تغییر دهند.

  • گزینه ث غلط است: تضادهای سطح سطر هرگز به قفل‌های انحصاری کل پایگاه داده ارتقا نمی‌یابند، زیرا این کار ظرفیت پردازشی (Throughput) موتور را به طور کامل متوقف می‌کند.

  • گزینه ج غلط است: به‌روزرسانی‌های هم‌زمان تحت Read Committed مجاز هستند؛ آن‌ها صرفاً پشت سر یکدیگر در صف قرار می‌گیرند. خطاهای شکست سریال‌سازی (40001) منحصر به سطح جداسازی Serializable هستند.

سوال ۳: تنظیم دقیق استراتژی‌های ایندکس‌گذاری JSONB برای جستجو در اسناد تودرتو

یک برنامه، اسناد پروفایل کاربر بدون ساختار و عمیق را در یک ستون JSONB به نام meta_data ذخیره می‌کند. توسعه‌دهندگان مکرراً کوئری‌های جستجو را با استفاده از اپراتور Containment (@>) اجرا می‌کنند تا رکوردهایی را پیدا کنند که کلیدهای تودرتوی خاص با مقادیر دقیق مطابقت دارند (مثلاً meta_data @>'{"location": {"country": "India"}}'). برای به حداکثر رساندن سرعت کوئری در این ستون، کدام استراتژی ایندکس‌گذاری را باید پیاده‌سازی کنید؟

• الف) یک ایندکس B-Tree استاندارد که بر روی عبارت تبدیل (Cast) مستقیم ستون JSONB به رشته متنی تعریف شده است.

• ب) یک GIN (Generalized Inverted Index) با استفاده از کلاس اپراتور پیش‌فرض jsonb_ops روی ستون meta_data.

• پ) یک BRIN (Block Range Index) که با یک فیلتر هشینگ سفارشی برای فشرده‌سازی الگوهای متنی زیرین پیکربندی شده است.

• ت) یک ایندکس GiST تخصصی که از مدل اپراتور هندسی R-Tree استاندارد برای نقشه‌برداری مکان‌های داده‌های رشته‌ای استفاده می‌کند.

• ث) یک ایندکس Hash محلی که روی هر بلوک ویژگی تودرتو ایجاد شده تا سربار پیمایش چندصفحه‌ای را کاملاً حذف کند.

• ج) یک ایندکس B-Tree جزئی (Partial) مبتنی بر عبارت که هر ترکیب کلید-مقدار موجود در اسکیمای سند را ردیابی می‌کند.

پاسخ صحیح و توضیح:

• پاسخ صحیح: ب

• چرا درست است: ساختار ایندکس GIN یک ایندکس معکوس است که به طور خاص برای مدیریت آیتم‌های ترکیبی مانند آرایه‌ها و اسناد JSONB طراحی شده است. وقتی با کلاس اپراتور پیش‌فرض jsonb_ops پیکربندی شود، کل ساختار JSONB را به کلیدها، مسیرها و مقادیر مجزا تجزیه کرده و هر کدام را جداگانه ایندکس می‌کند. این به برنامه‌ریز کوئری اجازه می‌دهد تا کوئری‌های پیچیده و تودرتوی Containment (@>) را با استفاده از جستجوهای سریع ایندکس حل کند.

• چرا گزینه‌های دیگر غلط هستند:

  • گزینه الف غلط است: ایندکس B-Tree روی تبدیل متنی فقط کوئری‌هایی را سرعت می‌بخشد که دقیقاً با متن لیتِرال کل سند از ابتدا تا انتها مطابقت داشته باشند؛ این ایندکس نمی‌تواند جستجوهای منعطف در مسیرهای تودرتو را به طور کارآمد حل کند.

  • گزینه پ غلط است: ایندکس‌های BRIN برای جداول بزرگ با داده‌های فیزیکی بسیار همبسته و به طور طبیعی مرتب شده (مانند Timestampها) بهینه شده‌اند، نه برای مسیرهای متنی نامنظم و بدون ساختار JSON.

  • گزینه ت غلط است: ایندکس‌های GiST می‌توانند داده‌های JSONB را با استفاده از jsonb_path_ops مدیریت کنند، اما پیکربندی‌های هندسی استاندارد GiST برای جفت مختصات مکانی و کوئری‌های بازه‌ای هستند، نه تجزیه متن تودرتو.

  • گزینه ث غلط است: ایندکس‌های Hash در PostgreSQL از باز کردن چند-کلیدی یا مسیرهای عمیق پشتیبانی نمی‌کنند؛ آن‌ها فقط مقایسه‌های برابری پایه را روی مقادیر اولیه تک را مدیریت می‌کنند.

  • گزینه ج غلط است: یک ایندکس B-Tree مبتنی بر عبارت می‌تواند برای یک مسیر ثابت و واحد (مانند استخراج با ->>) به خوبی عمل کند، اما ایجاد یک ایندکس B-Tree جزئی برای هر ترکیب کلید ممکن در یک سند بدون ساختار، کاملاً غیرقابل نگهداری است.

چه انتظاراتی داشته باشید

• به تست‌های سوالات مصاحبه خوش آمدید تا شما را برای آزمون تمرینی سوالات مصاحبه PostgreSQL آماده کنیم.

• می‌توانید هر چند بار که بخواهید در آزمون‌ها شرکت کنید.

• این یک بانک سوالات عظیم و اورجینال است.

• در صورت داشتن سوال، از پشتیبانی مدرسان بهره‌مند می‌شوید.

• هر سوال دارای یک توضیح مفصل است.

• سازگار با موبایل از طریق اپلیکیشن Udemy.

امیدواریم تا الان متقاعد شده باشید! سوالات بسیار بیشتری در داخل دوره وجود دارد.