سوالات مصاحبه SQL و دیتابیس سطح سینیور (50 سوال)

Question 1

PostgreSQL چگونه MVCC را پیاده‌سازی می‌کند؟

Accepted Answer

هر نسخه سطر `xmin`/`xmax` دارد و visibility بر اساس snapshot از transaction IDها تعیین می‌شود

Answer

نسخه‌های قدیمی را در یک undo log جداگانه نگه می‌دارد و سطر را درجا بازنویسی می‌کند

Answer

خواندن‌ها همیشه قفل shared روی سطرها می‌گیرند

Answer

هر transaction یک کپی کامل از جدول می‌گیرد

Question 2

مهم‌ترین مزیت عملیاتی MVCC در مقایسه با قفل‌گذاری خواندن/نوشتن کلاسیک (2PL) چیست؟

Accepted Answer

خواننده‌ها نویسنده‌ها را block نمی‌کنند و نویسنده‌ها خواننده‌ها را

Answer

نوشتن‌های هم‌زمان روی یک سطر هرگز منتظر نمی‌مانند

Answer

هیچ‌گاه فضای اضافی مصرف نمی‌کند

Answer

همه anomalyها را در هر isolation level حذف می‌کند

Question 3

قانون: حداقل یک پزشک باید on-call باشد. دو پزشک هم‌زمان در Repeatable Read (snapshot isolation) بررسی می‌کنند که دیگری on-call است و هر کدام خود را خارج می‌کنند. چه می‌شود؟

Accepted Answer

هر دو commit می‌شوند و هیچ پزشکی on-call نمی‌ماند (write skew)

Answer

دومی به‌خاطر conflict نوشتن روی یک سطر abort می‌شود

Answer

deadlock رخ می‌دهد

Answer

snapshot isolation تضمین می‌کند قانون حفظ شود

Question 4

Serializable در PostgreSQL (SSI) چگونه کار می‌کند و اپلیکیشن باید چه کند؟

Accepted Answer

وابستگی‌های read/write خطرناک را ردیابی و یکی را با `40001` abort می‌کند؛ اپلیکیشن باید transaction را retry کند

Answer

همه transactionها را واقعاً پشت سر هم روی یک thread اجرا می‌کند

Answer

روی هر سطر خوانده‌شده قفل exclusive می‌گیرد

Answer

فقط هشدار می‌دهد و هرگز transactionی را abort نمی‌کند

Question 5

در Repeatable Read در PostgreSQL، اگر transaction سطری را update کند که transaction دیگری پس از شروع snapshot آن را update و commit کرده، چه می‌شود؟

Accepted Answer

خطای `could not serialize access due to concurrent update` و نیاز به retry

Answer

update روی نسخه جدید اعمال می‌شود، مثل Read Committed

Answer

update دیگری بی‌صدا بازنویسی می‌شود (lost update)

Answer

transaction تا ابد منتظر می‌ماند

Question 6

در Read Committed، `UPDATE t SET n = n + 1 WHERE n = 5` منتظر یک transaction هم‌زمان است که `n` آن سطر را به `6` تغییر داده و commit می‌کند. چه اتفاقی می‌افتد؟

Accepted Answer

شرط `WHERE` روی نسخه جدید دوباره ارزیابی می‌شود و چون دیگر `5` نیست، سطر update نمی‌شود

Answer

سطر بر اساس snapshot اولیه به `6` تغییر می‌کند

Answer

سطر به `7` تغییر می‌کند بدون بررسی دوباره شرط

Answer

خطای serialization رخ می‌دهد

Question 7

اصل اصلی Write-Ahead Logging (WAL) چیست؟

Accepted Answer

تغییرات قبل از نوشته شدن صفحات داده باید در log روی دیسک پایدار شوند

Answer

صفحات داده قبل از log نوشته می‌شوند

Answer

log فقط برای replication است و ربطی به durability ندارد

Answer

هر commit همه صفحات تغییرکرده را فوراً روی دیسک می‌نویسد

Question 8

`synchronous_commit = off` در PostgreSQL چه trade-offی دارد؟

Accepted Answer

commit سریع‌تر، با خطر از دست رفتن چند transaction اخیر در crash ولی بدون خرابی داده

Answer

خطر خرابی کامل دیتابیس در crash

Answer

غیرفعال شدن کامل WAL

Answer

عدم امکان rollback

Question 9

checkpointهای بسیار پرتکرار در PostgreSQL چه اثر منفی دارند؟

Accepted Answer

I/O زیاد و افزایش حجم WAL به‌خاطر full-page writeها بعد از هر checkpoint

Answer

زمان recovery پس از crash طولانی‌تر می‌شود

Answer

transactionها durability خود را از دست می‌دهند

Answer

autovacuum غیرفعال می‌شود

Question 10

تفاوت `VACUUM` با `VACUUM FULL` چیست؟

Accepted Answer

`VACUUM` فضا را برای استفاده مجدد آزاد می‌کند بدون قفل سنگین؛ `VACUUM FULL` جدول را بازنویسی و فضا را به OS برمی‌گرداند ولی `ACCESS EXCLUSIVE` می‌گیرد

Answer

`VACUUM FULL` فقط indexها را vacuum می‌کند

Answer

`VACUUM` جدول را قفل می‌کند و `VACUUM FULL` نه

Answer

تفاوتی ندارند؛ `FULL` فقط verbose است

Question 11

یک transaction که ساعت‌ها باز مانده (`idle in transaction`) چه اثری روی دیتابیس پرنوشتن دارد؟

Accepted Answer

مانع حذف dead tupleها توسط vacuum می‌شود و باعث bloat می‌شود

Answer

هیچ اثری ندارد چون کاری انجام نمی‌دهد

Answer

همه نوشتن‌ها را block می‌کند

Answer

WAL را غیرفعال می‌کند

Question 12

transaction ID wraparound در PostgreSQL چیست و چرا خطرناک است؟

Accepted Answer

XIDها ۳۲ بیتی هستند؛ بدون freeze شدن سطرها توسط vacuum، دیتابیس برای جلوگیری از گم شدن داده نوشتن را متوقف می‌کند

Answer

وقتی تعداد connectionها از `max_connections` بیشتر شود

Answer

وقتی sequence یک ستون `serial` سرریز می‌شود

Answer

وقتی WAL از دیسک بزرگ‌تر شود

Question 13

HOT update (Heap-Only Tuple) در PostgreSQL چه زمانی ممکن است؟

Accepted Answer

وقتی هیچ ستون index‌شده‌ای تغییر نکند و صفحه فضای خالی برای نسخه جدید داشته باشد

Answer

فقط برای جدول‌هایی بدون primary key

Answer

وقتی update داخل transaction صریح باشد

Answer

همیشه، برای هر update

Question 14

جدولی با نرخ نوشتن بالا ۱۵ index دارد. محتمل‌ترین اثر چیست؟

Accepted Answer

write amplification شدید، WAL بیشتر، فشار روی cache و حذف HOT update

Answer

خواندن‌ها کندتر می‌شوند چون planner گیج می‌شود

Answer

هیچ اثر منفی جز فضای دیسک ندارد

Answer

دیتابیس به‌طور خودکار indexهای اضافی را حذف می‌کند

Question 15

Hash index در PostgreSQL چه محدودیتی نسبت به B-tree دارد؟

Accepted Answer

فقط برابری (`=`) را پشتیبانی می‌کند؛ نه بازه، نه `ORDER BY`، نه unique

Answer

در PostgreSQL مدرن crash-safe نیست

Answer

فقط روی ستون‌های عددی کار می‌کند

Answer

همیشه بزرگ‌تر از B-tree است

Question 16

GIN index برای کدام نوع کوئری مناسب است؟

Accepted Answer

containment روی `jsonb`، آرایه‌ها و full-text search (مثل `@>` و `@@`)

Answer

کوئری‌های بازه روی timestamp

Answer

`ORDER BY` روی ستون عددی

Answer

primary key جدول

Question 17

BRIN index برای چه داده‌ای ایده‌آل است؟

Accepted Answer

جدول‌های بسیار بزرگ append-only که ستون با ترتیب فیزیکی همبستگی دارد، مثل `created_at` در log

Answer

ستون‌هایی با مقادیر کاملاً تصادفی مثل UUID v4

Answer

جستجوی متن کامل

Answer

lookupهای یکتای primary key

Question 18

plan یک Index Only Scan نشان می‌دهد ولی `Heap Fetches` بسیار بالاست. علت چیست؟

Accepted Answer

visibility map به‌روز نیست و باید برای بررسی visibility به heap مراجعه شود؛ vacuum لازم است

Answer

index خراب است و باید حذف شود

Answer

covering index همیشه heap را می‌خواند

Answer

`work_mem` خیلی کم است

Question 19

planner چه زمانی معمولاً Nested Loop را بر Hash Join ترجیح می‌دهد؟

Accepted Answer

وقتی سمت بیرونی سطرهای کمی دارد و سمت درونی index مناسب روی کلید join دارد

Answer

وقتی هر دو جدول میلیون‌ها سطر دارند و index ندارند

Answer

فقط برای `CROSS JOIN`

Answer

وقتی شرط join نابرابری نیست

Question 20

در `EXPLAIN ANALYZE` عبارت `Sort Method: external merge Disk: 512MB` دیده می‌شود. معنی آن چیست؟

Accepted Answer

sort در `work_mem` جا نشده و به دیسک spill کرده است

Answer

جدول ۵۱۲ مگابایت bloat دارد

Answer

index با حجم ۵۱۲ مگابایت استفاده شده است

Answer

نتیجه کوئری روی دیسک کش شده است

Question 21

یک prepared statement بعد از چند اجرا ناگهان برای برخی پارامترها بسیار کند می‌شود. علت محتمل در PostgreSQL چیست؟

Accepted Answer

پس از ۵ اجرا ممکن است generic plan انتخاب شود که برای مقادیر با توزیع نامتوازن بد است

Answer

prepared statementها پس از ۵ اجرا cache خود را پاک می‌کنند

Answer

پارامترها پس از چند بار به رشته تبدیل می‌شوند

Answer

connection pool کوئری را دوباره parse می‌کند

Question 22

برای تغییر نام ستون `name` به `full_name` بدون downtime در سیستمی با deploy تدریجی، روش درست کدام است؟

Accepted Answer

expand/contract: افزودن ستون جدید، نوشتن در هر دو، backfill، سوئیچ خواندن، سپس حذف ستون قدیمی

Answer

یک `ALTER TABLE ... RENAME COLUMN` و deploy هم‌زمان کد

Answer

توقف همه سرویس‌ها و اجرای migration

Answer

ساخت view با نام جدید و حذف جدول اصلی

Question 23

یک `ALTER TABLE` ساده و سریع روی جدول پرترافیک باعث قطعی کامل شد. محتمل‌ترین علت چیست؟

Accepted Answer

منتظر `ACCESS EXCLUSIVE` پشت یک کوئری طولانی ماند و همه کوئری‌های بعدی پشت آن در صف قفل گیر کردند

Answer

`ALTER TABLE` همه connectionها را قطع می‌کند

Answer

WAL پر شد

Answer

`ALTER TABLE` همیشه کل جدول را بازنویسی می‌کند

Question 24

برای backfill ستون جدید در جدولی با ۵۰۰ میلیون سطر، چرا یک `UPDATE` واحد ایده بدی است؟

Accepted Answer

transaction طولانی، قفل سطرها، انفجار WAL و replication lag، و bloat به‌خاطر نسخه‌های جدید همه سطرها

Answer

`UPDATE` بیش از یک میلیون سطر پشتیبانی نمی‌شود

Answer

ستون جدید index نمی‌شود

Answer

فقط کمی کندتر است و مشکلی ندارد

Question 25

برای افزودن `NOT NULL` به ستون موجود در جدول بزرگ در PostgreSQL 12+ بدون اسکن طولانی زیر قفل، روش رایج چیست؟

Accepted Answer

افزودن `CHECK (col IS NOT NULL) NOT VALID`، سپس `VALIDATE`، سپس `SET NOT NULL` که از constraint معتبر استفاده می‌کند

Answer

`ALTER COLUMN ... SET NOT NULL` مستقیم همیشه بدون اسکن است

Answer

ساخت unique index روی ستون

Answer

تغییر نوع ستون به `text`

Question 26

trade-off اصلی replication همگام (synchronous) در برابر غیرهمگام چیست؟

Accepted Answer

همگام: بدون از دست رفتن داده در failover ولی latency بیشتر و وابستگی در دسترس بودن به replica

Answer

همگام: سریع‌تر ولی با خطر از دست رفتن داده

Answer

غیرهمگام: replica هرگز عقب نمی‌ماند

Answer

تفاوتی در durability ندارند

Question 27

یک روش دقیق برای تضمین read-your-writes هنگام خواندن از replica چیست؟

Accepted Answer

ذخیره LSN پس از نوشتن و خواندن از replica فقط اگر `pg_last_wal_replay_lsn()` آن به آن LSN رسیده باشد

Answer

خواندن همیشه از تصادفی‌ترین replica

Answer

افزایش تعداد replicaها

Answer

استفاده از `SELECT ... FOR UPDATE` روی replica

Question 28

logical replication در PostgreSQL چه کاری را ممکن می‌کند که physical (streaming) replication نمی‌کند؟

Accepted Answer

replication انتخابی جدول‌ها و بین نسخه‌های major متفاوت (مثلاً برای upgrade کم‌downtime)

Answer

کپی بایت به بایت کل cluster شامل indexها

Answer

replication خودکار DDL

Answer

replication همگام بدون هیچ overhead

Question 29

split-brain در failover دیتابیس چیست و چگونه از آن جلوگیری می‌شود؟

Accepted Answer

دو node هم‌زمان خود را primary می‌دانند و نوشتن می‌پذیرند؛ با consensus/quorum و fencing جلوگیری می‌شود

Answer

replica داده را دو نسخه ذخیره می‌کند؛ با vacuum حل می‌شود

Answer

جدول به دو partition تقسیم می‌شود؛ با `DETACH` حل می‌شود

Answer

connection pool دو بار وصل می‌شود؛ با PgBouncer حل می‌شود

Question 30

تفاوت اصلی partitioning و sharding چیست؟

Accepted Answer

partitioning داده را روی یک node تقسیم می‌کند؛ sharding آن را روی چند node مستقل توزیع می‌کند

Answer

sharding فقط برای indexهاست

Answer

partitioning همیشه به چند سرور نیاز دارد

Answer

هیچ تفاوتی ندارند

Question 31

برای یک SaaS چند-مستأجری (multi-tenant)، کدام shard key معمولاً بهترین است؟

Accepted Answer

`tenant_id`، تا کوئری‌ها و transactionهای هر مستأجر روی یک shard بمانند

Answer

`created_at`، تا داده جدید روی shard جدید برود

Answer

یک عدد تصادفی برای هر سطر

Answer

`country`، چون تعداد مقادیر کم است

Question 32

جدولی بر اساس `created_at` partition شده ولی کوئری `WHERE user_id = 42` کند است و همه partitionها را اسکن می‌کند. چرا؟

Accepted Answer

شرط شامل partition key نیست، پس pruning ممکن نیست و باید index هر partition جداگانه بررسی شود

Answer

partitioning همیشه کوئری‌ها را کندتر می‌کند

Answer

`user_id` در جدول partition‌شده قابل index نیست

Answer

باید `VACUUM FULL` اجرا شود

Question 33

چرا PostgreSQL با هزاران connection مستقیم کند می‌شود حتی اگر بیشترشان بیکار باشند؟

Accepted Answer

مدل process-per-connection، حافظه هر backend و هزینه محاسبه snapshot با تعداد backendها رشد می‌کند

Answer

connectionهای بیکار قفل جدول نگه می‌دارند

Answer

هر connection یک کپی از کل shared buffers دارد

Answer

هیچ هزینه‌ای ندارد؛ فقط connectionهای فعال مهم‌اند

Question 34

advisory lock در PostgreSQL (مثلاً `pg_advisory_xact_lock(42)`) برای چه کاربردی مناسب است؟

Accepted Answer

هماهنگی سطح اپلیکیشن، مثل تضمین اجرای تنها یک instance از یک cron job

Answer

قفل خودکار همه سطرهای یک جدول

Answer

جلوگیری از SQL injection

Answer

سریع‌تر کردن کوئری‌های خواندن

Question 35

حذف یک سطر از `users` بسیار کند است و جدول `orders` (با foreign key به `users`) را قفل می‌کند. علت محتمل چیست؟

Accepted Answer

ستون `orders.user_id` index ندارد و بررسی FK به ازای هر حذف یک seq scan روی `orders` انجام می‌دهد

Answer

primary key `users` خراب است

Answer

foreign keyها همیشه کل جدول فرزند را قفل exclusive می‌کنند

Answer

`DELETE` در PostgreSQL پشتیبانی از FK ندارد

Question 36

در سیستم رزرو بلیت با تقاضای بسیار بالا برای چند صندلی محدود، optimistic locking چه مشکلی دارد؟

Accepted Answer

نرخ conflict بالا باعث retryهای زیاد، کار هدررفته و بار اضافی می‌شود

Answer

optimistic locking داده را خراب می‌کند

Answer

optimistic locking با transaction سازگار نیست

Answer

هیچ مشکلی ندارد و همیشه بهترین گزینه است

Question 37

client پس از timeout درخواست پرداخت را retry می‌کند. چگونه در سطح دیتابیس از پرداخت دوباره جلوگیری می‌شود؟

Accepted Answer

ذخیره idempotency key با unique constraint در همان transaction پرداخت

Answer

چک کردن وجود پرداخت با `SELECT` و سپس `INSERT` در Read Committed

Answer

افزایش timeout client

Answer

حذف retry در client

Question 38

سرویسی باید یک سفارش را در دیتابیس ذخیره و یک event به Kafka بفرستد. چرا transactional outbox pattern پیشنهاد می‌شود؟

Accepted Answer

event در همان transaction در جدول outbox نوشته و سپس جداگانه منتشر می‌شود، پس dual-write ناسازگار رخ نمی‌دهد

Answer

Kafka از transaction دیتابیس پشتیبانی مستقیم دارد

Answer

سریع‌ترین راه انتشار event است

Answer

نیاز به هر نوع retry را حذف می‌کند

Question 39

تفاوت BCNF با 3NF چیست؟

Accepted Answer

در BCNF سمت چپ هر وابستگی تابعی غیربدیهی باید superkey باشد، حتی اگر سمت راست بخشی از یک candidate key باشد

Answer

BCNF اجازه وابستگی transitive را می‌دهد

Answer

BCNF فقط برای جدول‌های بدون primary key است

Answer

BCNF همان 2NF با نام دیگر است

Question 40

برای محصولات با ویژگی‌های متغیر زیاد، چرا `jsonb` با GIN معمولاً بر مدل EAV (Entity-Attribute-Value) ترجیح دارد؟

Accepted Answer

EAV به joinهای زیاد، نبود type safety و کوئری‌های پیچیده منجر می‌شود؛ `jsonb` داده را کنار سطر نگه می‌دارد و قابل index است

Answer

`jsonb` همه constraintهای relational را خودکار اعمال می‌کند

Answer

EAV در PostgreSQL پشتیبانی نمی‌شود

Answer

`jsonb` همیشه فضای کمتری از ستون‌های عادی می‌گیرد

Question 41

چرا UUID v7 برای primary key در جدول‌های پرنوشتن بهتر از UUID v4 است؟

Accepted Answer

پیشوند timestamp باعث درج تقریباً ترتیبی در B-tree، page split کمتر و locality بهتر می‌شود

Answer

UUID v7 فقط ۸ بایت است

Answer

UUID v7 کاملاً غیرقابل پیش‌بینی‌تر از v4 است

Answer

UUID v7 نیاز به index را حذف می‌کند

Question 42

چرا ستون `serial`/`identity` در PostgreSQL ممکن است شکاف (gap) داشته باشد؟

Accepted Answer

`nextval` تراکنشی نیست؛ مقادیر گرفته‌شده در transactionهای rollback‌شده یا insertهای ناموفق برنمی‌گردند

Answer

به‌خاطر باگ در PostgreSQL

Answer

چون vacuum شماره‌ها را دوباره تخصیص می‌دهد

Answer

فقط وقتی سطرها حذف شوند

Question 43

یک stored procedure از `EXECUTE 'SELECT * FROM t WHERE name = ''' || p_name || ''''` استفاده می‌کند. آیا parameterized بودن فراخوانی از اپلیکیشن کافی است؟

Accepted Answer

خیر؛ الحاق رشته داخل dynamic SQL همچنان injection ایجاد می‌کند و باید از `EXECUTE ... USING` یا `format('%L')` استفاده شود

Answer

بله؛ پارامترهای اپلیکیشن همه جا امن می‌مانند

Answer

بله؛ stored procedureها ذاتاً در برابر injection مصون‌اند

Answer

خیر؛ ولی فقط اگر دیتابیس MySQL باشد

Question 44

Row-Level Security (RLS) در PostgreSQL برای یک اپلیکیشن multi-tenant چه مزیتی دارد؟

Accepted Answer

دیتابیس خودش فیلتر tenant را روی هر کوئری اعمال می‌کند، پس یک `WHERE` فراموش‌شده داده را نشت نمی‌دهد

Answer

کوئری‌ها را همیشه سریع‌تر می‌کند

Answer

نیاز به authentication را حذف می‌کند

Answer

داده‌های هر tenant را رمزنگاری می‌کند

Question 45

چرا replica جایگزین backup نیست و Point-In-Time Recovery (PITR) چه چیزی اضافه می‌کند؟

Accepted Answer

`DROP TABLE` اشتباهی فوراً روی replica هم اعمال می‌شود؛ PITR با base backup و آرشیو WAL امکان بازگشت به لحظه قبل از خطا را می‌دهد

Answer

replica داده را فشرده نمی‌کند

Answer

replica فقط داده خواندنی دارد و قابل بازیابی نیست

Answer

PITR فقط برای MySQL وجود دارد

Question 46

طبق PACELC، یک cluster PostgreSQL با replication همگام در حالت عادی (بدون partition) چه انتخابی می‌کند؟

Accepted Answer

consistency را به قیمت latency بیشتر انتخاب می‌کند (EC)

Answer

latency کم را به قیمت consistency انتخاب می‌کند (EL)

Answer

هم latency صفر و هم consistency کامل

Answer

PACELC فقط برای دیتابیس‌های NoSQL معنا دارد

Question 47

برای گرفتن ۳ سفارش آخر هر user وقتی index روی `orders(user_id, created_at DESC)` وجود دارد، کدام روش معمولاً کاراتر است؟

Accepted Answer

`users u CROSS JOIN LATERAL (SELECT ... WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3)`

Answer

`ROW_NUMBER()` روی کل جدول `orders` و فیلتر `rn <= 3` همیشه سریع‌تر است

Answer

`GROUP BY user_id` با `LIMIT 3`

Answer

`SELECT DISTINCT` روی `user_id`

Question 48

در PostgreSQL 12+، `WITH x AS MATERIALIZED (SELECT * FROM big) SELECT * FROM x WHERE id = 5` چه اثری دارد؟

Accepted Answer

CTE به‌عنوان optimization fence عمل می‌کند و شرط `id = 5` به داخل آن push down نمی‌شود

Answer

نتیجه CTE به‌صورت دائمی در دیسک ذخیره می‌شود

Answer

planner شرط را push down می‌کند و از index استفاده می‌کند

Answer

خطای syntax؛ `MATERIALIZED` فقط برای view است

Question 49

MySQL InnoDB در Repeatable Read چگونه از phantom در `SELECT ... FOR UPDATE` با شرط بازه جلوگیری می‌کند؟

Accepted Answer

با next-key lock (قفل رکورد به‌علاوه gap) روی بازه index اسکن‌شده

Answer

با قفل کردن کل جدول

Answer

با abort کردن خودکار transactionهای دیگر پس از commit

Answer

از phantom جلوگیری نمی‌کند

Question 50

دیسک primary به‌تدریج با فایل‌های WAL پر می‌شود در حالی که checkpointها عادی‌اند. محتمل‌ترین علت چیست؟

Accepted Answer

یک replication slot غیرفعال که مصرف‌کننده‌اش قطع شده و WAL را نگه می‌دارد

Answer

autovacuum بیش از حد فعال است

Answer

تعداد indexها زیاد است

Answer

`work_mem` خیلی بزرگ است