بررسی عمیق PostgreSQL برای مصاحبه‌های طراحی سیستم

PostgreSQL

در این فصل یاد می‌گیریم PostgreSQL را کی و چطور در مصاحبه‌های طراحی سیستم به کار ببریم.

احتمال زیادی هست که در مصاحبه طراحی سیستم‌تان درباره PostgreSQL بحث کنید. از این گذشته، در نظرسنجی توسعه‌دهندگان Stack Overflow همواره به‌عنوان محبوب‌ترین پایگاه داده رتبه‌بندی می‌شود و شرکت‌هایی از Reddit تا Instagram، و حتی وب‌سایتی که الان می‌خوانید، از آن استفاده می‌کنند.

با این حال، مهم است بدانید که با اینکه PostgreSQL پر از ویژگی و قابلیت است، مصاحبه‌گر شما دنبال مدیر پایگاه داده (DBA) نیست. می‌خواهد ببیند می‌توانید تصمیم‌های معماری آگاهانه بگیرید. کی باید PostgreSQL را انتخاب کنید؟ کی باید سراغ چیز دیگری بروید؟ مصالحه‌های کلیدی که باید در نظر گرفت چیست؟

اغلب می‌بینم کاندیداها اینجا به مشکل می‌خورند. یا خیلی عمیق در درون PostgreSQL فرو می‌روند (وقتی مصاحبه‌گر فقط می‌خواهد بداند می‌توانید روابط داده‌هایشان را مدیریت کنید، آن‌ها درباره MVCC و WAL حرف می‌زنند)، یا جمله‌های خیلی کلی می‌گویند، مثل «NoSQL بهتر از PostgreSQL مقیاس می‌گیرد»، بدون اینکه ظرافت‌ها را بفهمند.

در این بررسی عمیق مشخصاً روی چیزهایی تمرکز می‌کنیم که باید درباره PostgreSQL برای مصاحبه‌های طراحی سیستم بدانید. با یک مثال عملی شروع می‌کنیم، قابلیت‌ها و محدودیت‌های کلیدی را که باید انتخاب‌هایتان را شکل بدهند مرور می‌کنیم و سناریوهای رایج مصاحبه را می‌بینیم.

فرض می‌کنیم درک پایه‌ای از SQL دارید. اگر ندارید، در انتهای همین فصل یک پیوست درباره مفاهیم پایه SQL اضافه کرده‌ام تا مرورش کنید.

شروع کنیم.

یک مثال انگیزشی

بیایید شهود خود را درباره PostgreSQL با یک مثال مشخص بسازیم. فرض کنید پلتفرم شبکه اجتماعی طراحی می‌کنیم. نه پلتفرم عظیمی مثل فیس‌بوک، بلکه پلتفرمی در حال رشد که به پایه محکم نیاز دارد.

پلتفرم ما باید چند رابطه پایه را مدیریت کند.

• کاربران می‌توانند پست بسازند.
• کاربران می‌توانند زیر پست‌ها نظر بگذارند.
• کاربران می‌توانند کاربران دیگر را دنبال کنند.
• کاربران می‌توانند هم پست‌ها و هم نظرها را لایک کنند.
• کاربران می‌توانند با کاربران دیگر پیام مستقیم (DM) بسازند.

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

از دید پایگاه داده چه چیزی این را جالب می‌کند؟ عملیات مختلف الزامات متفاوتی دارند.

• عملیات چندمرحله‌ای مثل ساخت thread در DM باید اتمیک باشد (ساخت thread، اضافه کردن شرکت‌کننده‌ها و ذخیره اولین پیام باید با هم انجام شود).
• روابط نظر و دنبال کردن به یکپارچگی ارجاعی (referential integrity) نیاز دارند (نمی‌توانید بدون پست معتبر نظر داشته باشید یا کاربر ناموجود را دنبال کنید).
• تعداد لایک‌ها می‌تواند در نهایت سازگار باشد (اگر چند ثانیه طول بکشد تا به‌روز شود، مهم نیست).
• وقتی کسی پروفایل کاربری را درخواست می‌کند، باید پست‌های اخیر، تعداد دنبال‌کننده‌ها و metadata دیگرش را کارآمد بگیریم.
• کاربران باید بتوانند در پست‌ها جستجو کنند و کاربران دیگر را پیدا کنند.
• با رشد پلتفرم، باید داده بیشتری مدیریت کنیم و کوئری‌های پیچیده‌تری اجرا کنیم.

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

قابلیت‌ها و محدودیت‌های اصلی

با داشتن یک مثال انگیزشی، ببینیم PostgreSQL چه کارهایی را خوب انجام می‌دهد و چه کارهایی را نه. بیشتر بحث‌های طراحی سیستم درباره PostgreSQL حول کارایی خواندن، قابلیت‌های نوشتن، تضمین‌های سازگاری و انعطاف schema می‌چرخد. فهمیدن این ویژگی‌های اصلی کمک می‌کند درباره زمان استفاده از PostgreSQL در طراحی‌تان تصمیم آگاهانه بگیرید.

کارایی خواندن

اول کارایی خواندن است. این خیلی مهم است، چون در بیشتر اپلیکیشن‌ها تعداد خواندن‌ها خیلی بیشتر از نوشتن است. در مثال شبکه اجتماعی ما، کاربران زمان بیشتری را صرف مرور پست‌ها و پروفایل‌ها می‌کنند تا ساختن محتوا.

در مصاحبه‌های طراحی سیستم لازم نیست وارد درون query planner شوید. در عوض روی الگوهای عملی کارایی و زمانی که انواع مختلف ایندکس معنا دارند تمرکز کنید.

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

ایندکس‌گذاری پایه

اساسی‌ترین راه برای سریع‌تر کردن خواندن در PostgreSQL ایندکس است. به‌طور پیش‌فرض PostgreSQL از ایندکس B-Tree استفاده می‌کند که برای این موارد خوب است.

• تطبیق دقیق (WHERE email = '[email protected]').
• کوئری بازه‌ای (WHERE created_at > '2024-01-01').
• مرتب‌سازی (ORDER BY username اگر ستون‌های ORDER BY با ترتیب ستون‌های ایندکس هم‌خوان باشند).

به‌طور پیش‌فرض PostgreSQL روی ستون کلید اصلی شما ایندکس B-Tree می‌سازد، اما می‌توانید روی ستون‌های دیگر هم ایندکس بسازید.

-- This is your bread and butter index
CREATE INDEX idx_users_email ON users(email);

-- Multi-column indexes for common query patterns
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

تله رایج در مصاحبه این است که برای هر ستون پیشنهاد ایندکس بدهید. یادتان باشد هر ایندکس:

• نوشتن را کندتر می‌کند (چون ایندکس باید به‌روز شود).
• فضای دیسک مصرف می‌کند.
• اگر query planner فکر کند اسکن ترتیبی سریع‌تر است، حتی ممکن است استفاده نشود.

فراتر از ایندکس‌های پایه

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

جستجوی متن کامل با ایندکس GIN: Postgres از جستجوی متن کامل به‌صورت پیش‌فرض با ایندکس GIN (Generalized Inverted Index) پشتیبانی می‌کند. ایندکس GIN مثل ایندکس پشت کتاب کار می‌کند. نقشه‌ای از هر کلمه به همه مکان‌هایی که ظاهر می‌شود نگه می‌دارد. این آن‌ها را برای جستجوی متن کامل عالی می‌کند، جایی که باید سریع documentهای حاوی کلمات مشخص را پیدا کنید:

-- Add a tsvector column for search
ALTER TABLE posts ADD COLUMN search_vector tsvector;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Now you can do full-text search
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('postgresql & database');

برای بسیاری از اپلیکیشن‌ها، این قابلیت جستجوی داخلی یعنی به cluster جداگانه Elasticsearch نیاز ندارید. همه این‌ها را پشتیبانی می‌کند:

• Stemming (پیدا کردن «finding»، «find» و «finds» با هم).
• رتبه‌بندی ارتباط (relevance).
• چند زبان.
• کوئری پیچیده با AND/OR/NOT.

با اینکه جستجوی متن کامل PostgreSQL قدرتمند است، ممکن است برای همه کاربردها جای Elasticsearch را کامل نگیرد. در این موارد Elasticsearch را در نظر بگیرید:

• امتیازدهی پیچیده‌تر ارتباط.
• قابلیت faceted search.
• تطبیق فازی و ویژگی‌های «جستجو حین تایپ».
• جستجوی توزیع‌شده روی مجموعه داده‌های بسیار بزرگ.
• تحلیل و aggregation پیشرفته.

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

ستون‌های JSONB با ایندکس GIN: این‌ها به‌خصوص وقتی مفیدند که metadata انعطاف‌پذیری در پست‌هایتان لازم دارید. مثلاً در پلتفرم شبکه اجتماعی ما، هر پست ممکن است attributeهای مختلفی مثل مکان، کاربران منشن‌شده، هشتگ یا رسانه ضمیمه داشته باشد. به‌جای ساختن ستون جداگانه برای هر احتمال، می‌توانیم آن را در یک ستون JSONB ذخیره کنیم (انعطاف می‌دهد هر وقت لازم شد attributeهای جدید اضافه کنیم، درست مثل پایگاه داده NoSQL!).

-- Add a JSONB column for post metadata
ALTER TABLE posts ADD COLUMN metadata JSONB;
CREATE INDEX idx_posts_metadata ON posts USING GIN(metadata);

-- Now we can efficiently query posts with specific metadata
SELECT * FROM posts
WHERE metadata @> '{"type": "video"}'
  AND metadata @> '{"hashtags": ["coding"]}';

-- Or find all posts that mention a specific user
SELECT * FROM posts
WHERE metadata @> '{"mentions": ["user123"]}';

جستجوی مکانی با PostGIS: با اینکه در هسته PostgreSQL نیست، افزونه PostGIS قابلیت‌های فضایی قدرتمندی اضافه می‌کند. همان‌طور که می‌توانیم متن را برای جستجوی سریع ایندکس کنیم، PostGIS اجازه می‌دهد داده مکان را برای جستجوهای مکانی کارآمد ایندکس کنیم. این برای پلتفرم شبکه اجتماعی ما وقتی مناسب است که می‌خواهیم پست‌های کاربران را از منطقه نزدیک نشان دهیم:

-- Enable PostGIS
CREATE EXTENSION postgis;

-- Add a location column to posts
ALTER TABLE posts
ADD COLUMN location geometry(Point);

-- Create a spatial index
CREATE INDEX idx_posts_location
ON posts USING GIST(location);

-- Find all posts within 5km of a user
SELECT * FROM posts
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(-122.4194, 37.7749)::geography, -- SF coordinates
    5000  -- 5km in meters
);

PostGIS فوق‌العاده قدرتمند است. می‌تواند این‌ها را مدیریت کند:

• انواع مختلف داده مکانی (نقطه، خط، چندضلعی).
• محاسبه‌های مختلف فاصله (خط مستقیم هوایی، مسافت رانندگی).
• عملیات مکانی (تقاطع، شمول).
• سیستم‌های مختصات مختلف.

PostGIS آن‌قدر توانمند است که شرکت‌هایی مثل Uber ابتدا از آن برای کل سیستم تطبیق سفر خودشان استفاده می‌کردند. با اینکه از آن زمان برای مقیاس بزرگ سراغ راه‌حل‌های سفارشی رفته‌اند، نشان می‌دهد با افزونه‌های PostgreSQL می‌توانید تا کجا پیش بروید، قبل از نیاز به پایگاه داده‌های تخصصی.

نوع ایندکسی که اینجا استفاده شد (GiST) مخصوص داده‌های هندسی بهینه شده و در پشت صحنه از ایندکس‌گذاری R-Tree استفاده می‌کند. یعنی کوئری‌هایی مثل «همه پست‌ها در X کیلومتری را پیدا کن» یا «پست‌های داخل این مرز را پیدا کن» بدون بررسی تک‌تک سطرها کارآمد اجرا می‌شوند.

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

بهتر از آن، می‌توانیم همه این قابلیت‌ها را ترکیب کنیم تا تجربه جستجوی غنی بسازیم. مثلاً می‌توانیم همه پست‌های ویدیویی را در فاصله ۵ کیلومتری سان‌فرانسیسکو پیدا کنیم که در محتوایشان «food» ذکر شده و با «restaurant» تگ شده‌اند:

SELECT * FROM posts
WHERE search_vector @@ to_tsquery('food')
  AND metadata @> '{"type": "video", "hashtags": ["restaurant"]}'
  AND ST_DWithin(
    location::geography,
    ST_MakePoint(-122.4194, 37.7749)::geography,
    5000
  );

ملزومات بهینه‌سازی کوئری

تا اینجا انواع مختلف ایندکس‌هایی را که PostgreSQL ارائه می‌دهد پوشش دادیم، اما بهینه‌سازی کوئری بیشتر از انتخاب نوع درست ایندکس است. چند استراتژی پیشرفته ایندکس‌گذاری را می‌بینیم که می‌توانند کارایی خواندن را چشمگیر بهتر کنند.

ایندکس‌های پوششی (Covering Index)

وقتی PostgreSQL از ایندکس برای پیدا کردن سطر استفاده می‌کند، معمولاً باید دو کار انجام دهد.

1. مقدار را در ایندکس جستجو کند تا محل سطر را پیدا کند.
2. سطر واقعی را از جدول بگیرد تا ستون‌های دیگر موردنیازتان را برسد.

اما اگر بتوانیم همه داده لازم را درست داخل خود ایندکس ذخیره کنیم چه؟ ایندکس‌های پوششی دقیقاً همین کار را می‌کنند:

-- Let's say this is a common query in our social media app:
SELECT title, created_at
FROM posts
WHERE user_id = 123
ORDER BY created_at DESC;

-- A covering index that includes all needed columns
CREATE INDEX idx_posts_user_include
ON posts(user_id) INCLUDE (title, created_at);

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

ایندکس‌های جزئی (Partial Index)

گاهی فقط لازم است زیرمجموعه‌ای از داده‌هایتان را ایندکس کنید. مثلاً در پلتفرم شبکه اجتماعی ما، بیشتر کوئری‌ها احتمالاً دنبال کاربران فعال‌اند، نه کاربران حذف‌شده:

-- Standard index indexes everything
CREATE INDEX idx_users_email ON users(email);  -- Indexes ALL users

-- Partial index only indexes active users
CREATE INDEX idx_active_users
ON users(email) WHERE status = 'active';  -- Smaller, faster index

ایندکس‌های جزئی در این موارد به‌خصوص مؤثرند: وقتی بیشتر کوئری‌های شما فقط به زیرمجموعه‌ای از سطرها نیاز دارند، وقتی رکوردهای «غیرفعال» یا «حذف‌شده» زیادی دارید که نیازی به ایندکس شدن ندارند، یا وقتی می‌خواهید اندازه کلی و هزینه نگهداری ایندکس‌ها را کم کنید. با ایندکس کردن فقط زیرمجموعه مرتبط داده، ایندکس‌های جزئی می‌توانند کارایی کوئری و مصرف منابع را چشمگیر بهتر کنند.

محدودیت‌های عملی کارایی

احتمال زیادی هست که در الزامات غیرعملکردی‌تان هدف‌هایی برای تأخیر (latency) تعیین کرده باشید. در حالت ایده‌آل، حتی کمی‌شان کرده‌اید! یعنی وقتی وارد عمق طراحی می‌شوید، به چند عدد پایه کارایی نیاز دارید. این اعداد تخمین‌های بسیار تقریبی‌اند، چون اعداد واقعی به سخت‌افزار و بار کاری مشخص بسیار وابسته‌اند. با این حال، تخمین‌ها برای شروع مصاحبه باید کافی باشند.

کارایی جستجو:

• جستجوهای ایندکس‌شده ساده: ده‌ها هزار در ثانیه به ازای هر هسته.
• Joinهای پیچیده: هزاران در ثانیه.
• اسکن کامل جدول: بسیار بستگی دارد که داده در حافظه جا شود یا نه.

محدودیت‌های مقیاس:

• جدول‌ها بعد از ۱۰۰ میلیون سطر شروع به سخت شدن می‌کنند.
• جستجوی متن کامل تا ده‌ها میلیون document خوب کار می‌کند.
• Joinهای پیچیده با جدول‌های بیش از ۱۰ میلیون سطر چالش‌برانگیز می‌شوند.
• وقتی working set از RAM موجود بیشتر شود، کارایی چشمگیر افت می‌کند.

این‌ها محدودیت سخت نیستند. PostgreSQL با بهینه‌سازی درست می‌تواند بیشتر از این هم انجام دهد. اما قاعده‌های سرانگشتی خوبی هستند برای وقتی که باید به پارتیشن‌بندی، Sharding یا استراتژی‌های مقیاس‌دهی دیگر فکر کنید.

یادتان باشد وقتی صحبت از کارایی است، حافظه پادشاه است! کوئری‌هایی که از حافظه جواب داده می‌شوند، همیشه از کوئری‌هایی که به دیسک نیاز دارند سریع‌ترند. به‌عنوان قاعده کلی، باید سعی کنید working set (داده‌هایی که زیاد دسترسی می‌شوند) را برای کارایی بهینه در RAM نگه دارید.

در مصاحبه، نشان دادن دانش این محدودیت‌های عملی کمک می‌کند نشان دهید نه تنها می‌دانید PostgreSQL را چطور استفاده کنید، بلکه می‌دانید کی ممکن است لازم باشد گزینه‌های جایگزین یا استراتژی‌های بهینه‌سازی اضافه را در نظر بگیرید.

کارایی نوشتن

حالا درباره نوشتن صحبت کنیم. با اینکه خواندن ممکن است بر بیشتر بارهای کاری غالب باشد، کارایی نوشتن اغلب بسیار مهم است، چون مستقیم روی تجربه کاربری اثر می‌گذارد. هیچ‌کس نمی‌خواهد بعد از زدن «پست» چند ثانیه منتظر بماند تا محتوایش ظاهر شود.

وقتی نوشتنی در PostgreSQL رخ می‌دهد، چند مرحله برای تضمین کارایی و دوام انجام می‌شود.

1. نوشتن در Transaction Log (WAL) [دیسک]: تغییرات اول در Write-Ahead Log (WAL) روی دیسک نوشته می‌شوند. این یک عملیات نوشتن متوالی است، که نسبتاً سریع است. WAL برای دوام حیاتی است. وقتی تغییرات اینجا نوشته شد، تراکنش بادوام حساب می‌شود، چون حتی اگر سرور crash کند، PostgreSQL می‌تواند تغییرات را از WAL بازیابی کند.
2. به‌روزرسانی Buffer Cache [حافظه]: تغییرات روی صفحه‌های داده در buffer cache مشترک PostgreSQL اعمال می‌شود، جایی که جدول‌ها و ایندکس‌های واقعی در حافظه هستند. وقتی صفحه‌ها تغییر می‌کنند، «کثیف» (dirty) علامت می‌خورند تا نشان دهند در نهایت باید روی دیسک نوشته شوند.
3. Background Writer [حافظه به دیسک]: صفحه‌های کثیف در حافظه به‌صورت دوره‌ای روی فایل‌های داده واقعی روی دیسک نوشته می‌شوند. این کار ناهمزمان از طریق فرایند background writer، وقتی فشار حافظه بالا می‌رود، یا هنگام checkpoint انجام می‌شود. این استراتژی نوشتن تأخیری به PostgreSQL اجازه می‌دهد چند تغییر را برای کارایی بهتر با هم ترکیب کند.
4. به‌روزرسانی ایندکس [حافظه و دیسک]: هر ایندکس باید به‌روز شود تا تغییرات را منعکس کند. مثل داده جدول، تغییرات ایندکس هم برای دوام از WAL رد می‌شوند. به همین دلیل داشتن ایندکس زیاد می‌تواند نوشتن را به‌طور چشمگیر کند کند. هر ایندکس ورودی WAL اضافه و به‌روزرسانی حافظه لازم دارد.

این معماری دلیل سریع بودن نوشتن در PostgreSQL است. بیشتر کار در حافظه انجام می‌شود و دوام از طریق WAL تضمین می‌شود. نوشتن واقعی صفحه‌های داده روی دیسک بعداً انجام می‌شود و برای عملیات دسته‌ای بهینه است.

پیامد عملی این طراحی این است که کارایی نوشتن معمولاً با سرعت نوشتن WAL (I/O دیسک)، تعداد ایندکس‌هایی که باید به‌روز شوند و مقدار حافظه موجود برای buffer cache محدود می‌شود.

محدودیت‌های throughput

حالا که می‌دانیم هنگام نوشتن در PostgreSQL چه می‌گذرد، قبل از رفتن سراغ بهینه‌سازی، درباره محدودیت‌های عملی throughput نوشتن صحبت کنیم. دانستن این مهم است، چون کمک می‌کند تصمیم بگیرید PostgreSQL برای سیستمتان مناسب است یا نه.

یک نمونه PostgreSQL که خوب تنظیم شده و روی سخت‌افزار خوب (نه عالی) اجرا می‌شود می‌تواند این‌ها را مدیریت کند:

• درج‌های ساده: حدود ۵۰۰۰ در ثانیه به ازای هر هسته.
• به‌روزرسانی با تغییر ایندکس: حدود ۱۰۰۰ تا ۲۰۰۰ در ثانیه به ازای هر هسته.
• تراکنش‌های پیچیده (چند جدول و ایندکس): صدها در ثانیه.
• عملیات انبوه: ده‌ها هزار سطر در ثانیه.

این اعداد سطح ایزولاسیون پیش‌فرض تراکنش در PostgreSQL (Read Committed) را فرض می‌کنند، که در آن کوئری‌ها فقط داده‌ای را می‌بینند که قبل از شروع کوئری commit شده بود. اگر سطح ایزولاسیون پیش‌فرض را عوض کنید، این اعداد می‌توانند بالا یا پایین بروند.

چه چیزی روی این محدودیت‌ها اثر می‌گذارد؟ چند عامل:

• سخت‌افزار: throughput نوشتن اغلب با I/O دیسک برای WAL به گلوگاه می‌خورد.
• ایندکس‌ها: هر ایندکس اضافه throughput نوشتن را کم می‌کند.
• Replication: در صورت پیکربندی، replication هم‌زمان تأخیر اضافه می‌کند، چون منتظر تأیید replicaها هستیم.
• پیچیدگی تراکنش: جدول یا ایندکس بیشتر که لمس شود، تراکنش کندتر است.

یادتان باشد اینجا درباره یک گره صحبت می‌کنیم! پس اگر سیستم شما throughput نوشتن بالاتری دارد، مثلاً بیشتر از ۵ هزار نوشتن در ثانیه، این به معنای کنار رفتن PostgreSQL نیست. فقط یعنی باید داده‌ها را بین چند گره یا ماشین پخش کنید.

بهینه‌سازی کارایی نوشتن

خب، یک گره حدود ۵ هزار نوشتن در ثانیه را انجام می‌دهد. پس چه کار می‌توانیم بکنیم؟ اگر به بیشتر از این نیاز داریم، چطور می‌توانیم کارایی نوشتن را بهتر کنیم؟ استراتژی‌ها را از بهینه‌سازی ساده تا تغییرات معماری می‌بینیم. چند گزینه داریم:

1. مقیاس عمودی.
2. پردازش دسته‌ای (batch).
3. واگذاری نوشتن (write offloading).
4. پارتیشن‌بندی جدول.
5. Sharding.

هرکدام را به نوبت بحث می‌کنیم.

۱. مقیاس عمودی: قبل از پریدن سراغ راه‌حل‌های پیچیده، همیشه می‌توانیم به ارتقای سخت‌افزار فکر کنیم. می‌تواند یعنی استفاده از دیسک NVMe سریع‌تر برای کارایی بهتر WAL، اضافه کردن RAM بیشتر برای بزرگ‌تر کردن buffer cache، یا ارتقا به CPUهایی با هسته بیشتر برای مدیریت مؤثرتر عملیات موازی. این معمولاً قانع‌کننده‌ترین راه‌حل در مصاحبه نیست، اما جای خوبی برای شروع است.

۲. پردازش دسته‌ای: ساده‌ترین بهینه‌سازی نوشتن، دسته کردن نوشتن‌ها با هم است. به‌جای پردازش جداگانه هر نوشتن، چند عملیات را جمع می‌کنیم و در یک تراکنش واحد اجرا می‌کنیم. مثلاً به‌جای درج ۱۰۰۰ لایک تک‌تک، می‌توانیم همه را در یک تراکنش درج کنیم. یعنی نوشتن‌ها را قبل از commit روی دیسک در حافظه سرور خودمان بافر می‌کنیم. ریسک اینجا روشن است: اگر وسط دسته crash کنیم، همه نوشتن‌های آن دسته را از دست می‌دهیم.

-- Instead of 1000 separate inserts:
INSERT INTO likes (post_id, user_id) VALUES
  (1, 101), (1, 102), ..., (1, 1000);

۳. واگذاری نوشتن: بعضی نوشتن‌ها لازم نیست هم‌زمان انجام شوند. مثلاً داده‌های analytics، لاگ فعالیت یا متریک‌های تجمیعی اغلب می‌توانند ناهمزمان پردازش شوند. به‌جای نوشتن مستقیم در PostgreSQL، می‌توانیم:

• نوشتن را به یک Message Queue (مثل Kafka) بفرستیم.
• از workerهای پس‌زمینه بخواهیم این نوشتن‌های صف‌شده را دسته‌ای پردازش کنند.
• در صورت نیاز یک پایگاه داده analytics جداگانه نگه داریم.

این الگو برای لاگ فعالیت، رویدادهای analytics، تجمیع متریک و به‌روزرسانی‌های غیرحیاتی مثل timestamp «آخرین بازدید» عالی کار می‌کند. این نوع نوشتن‌ها لازم نیست فوراً انجام شوند و می‌توانند در پس‌زمینه، بدون اثر روی تجربه کاربری اصلی، پردازش شوند.

۴. پارتیشن‌بندی جدول: برای جدول‌های بزرگ، پارتیشن‌بندی می‌تواند با تقسیم داده بین چند جدول فیزیکی، کارایی خواندن و نوشتن را بهتر کند. رایج‌ترین کاربرد پارتیشن‌بندی مبتنی بر زمان است. به مثال شبکه اجتماعی برگردیم و فرض کنیم جدول پست‌هایی داریم که ماهی میلیون‌ها سطر بزرگ می‌شود:

CREATE TABLE posts (
    id SERIAL,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Create partitions by month
CREATE TABLE posts_2024_01 PARTITION OF posts
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

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

به‌راحتی، به خواندن هم کمک می‌کند. وقتی کاربران پست‌های اخیر را می‌بینند، PostgreSQL فقط باید partitionهای اخیر را اسکن کند. لازم نیست سال‌ها داده تاریخی را بررسی کند.

الگوی رایج این است که partitionهای اخیر را روی ذخیره‌ساز سریع (مثل NVMe) نگه دارید و partitionهای قدیمی‌تر را به ذخیره‌ساز ارزان‌تر منتقل کنید. کاربران به داده اخیر، که معمولاً بیشتر به آن اهمیت می‌دهند، دسترسی سریع دارند. این استراتژی عالی برای داده‌هایی است که چرخه حیات طبیعی دارند، مثل پست‌های شبکه اجتماعی که پست‌های جدیدتر مرتبط‌ترند.

۵. Sharding: این رایج‌ترین راه‌حل در مصاحبه است. وقتی یک گره کافی نیست، Sharding اجازه می‌دهد نوشتن‌ها را بین چند نمونه PostgreSQL توزیع کنید. فقط باید روشن باشید که روی چه چیزی Shard می‌کنید و داده را چطور توزیع می‌کنید.

مثلاً ممکن است پست‌هایمان را با user_id Shard کنیم. به این ترتیب همه داده یک کاربر روی یک shard زندگی می‌کند. این مهم است، چون وقتی سراغ خواندن داده می‌رویم، می‌خواهیم از کوئری‌های cross-shard که باید داده را از چند shard جمع کنند پرهیز کنیم.

معمولاً می‌خواهید روی ستونی Shard کنید که اغلب با آن کوئری می‌زنید. پس اگر معمولاً همه پست‌های یک کاربر مشخص را کوئری می‌کنیم، با user_id Shard می‌کنیم.

Sharding پیچیدگی اضافه می‌کند. باید کوئری‌های cross-shard را مدیریت کنید، schema یکسان بین shardها نگه دارید و چند پایگاه داده را مدیریت کنید. فقط وقتی معرفی‌اش کنید که بهینه‌سازی‌های ساده‌تر کافی نیستند.

برخلاف DynamoDB، PostgreSQL راه‌حل Sharding داخلی ندارد. باید Sharding را به‌صورت دستی پیاده کنید که ممکن است کمی چالش‌برانگیز باشد. از طرف دیگر، می‌توانید از سرویس‌هایی مثل Citus استفاده کنید که بسیاری از پیچیدگی‌های Sharding را برایتان برطرف می‌کند.

Replication

در حالی که درباره بهینه‌سازی کارایی نوشتن روی یک گره بحث کردیم، بیشتر استقرارهای دنیای واقعی برای دو هدف کلیدی از replication استفاده می‌کنند:

1. مقیاس دادن به خواندن با توزیع کوئری بین replicaها.
2. فراهم کردن دسترس‌پذیری بالا در صورت خرابی گره.

Replication فرایند کپی کردن داده از یک پایگاه داده به یک یا چند پایگاه داده دیگر است. بخش کلیدی داستان مقیاس‌پذیری و دسترس‌پذیری PostgreSQL است.

PostgreSQL از دو نوع اصلی replication پشتیبانی می‌کند: هم‌زمان (synchronous) و ناهمزمان (asynchronous). در replication هم‌زمان، primary قبل از تأیید نوشتن برای کلاینت منتظر تأیید replicaها می‌ماند. در replication ناهمزمان، primary بلافاصله نوشتن را برای کلاینت تأیید می‌کند و تغییرات را در پس‌زمینه روی replicaها تکثیر می‌کند. با اینکه ممکن است جزئیات فنی در مصاحبه لازم نشود، فهمیدن این مصالحه مهم است: replication هم‌زمان سازگاری قوی‌تر اما تأخیر بیشتر می‌دهد، در حالی که replication ناهمزمان کارایی بهتر اما ناسازگاری بالقوه بین replicaها دارد.

بسیاری از سازمان‌ها رویکرد ترکیبی دارند: نگه داشتن تعداد کمی replica هم‌زمان برای سازگاری بیشتر، و حفظ replicaهای ناهمزمان اضافه برای مقیاس دادن به خواندن. PostgreSQL اجازه می‌دهد مشخص کنید کدام replicaها هم‌زمان باشند.

مقیاس دادن به خواندن

رایج‌ترین کاربرد replication مقیاس دادن به کارایی خواندن است. با ساختن read replica می‌توانید کوئری‌های خواندن را بین چند نمونه پایگاه داده توزیع کنید و همه نوشتن‌ها را به primary بفرستید. این به‌خصوص مؤثر است، چون بیشتر اپلیکیشن‌ها خواندن‌سنگین هستند.

به مثال شبکه اجتماعی برگردیم. وقتی کاربران فیدشان را مرور می‌کنند یا پروفایل می‌بینند، همه این‌ها عملیات خواندن‌اند که هر replicaای می‌تواند انجامشان دهد. فقط وقتی پست می‌سازند یا پروفایل را به‌روز می‌کنند، باید از primary استفاده کنیم. حالا throughput خواندن را N برابر کرده‌ایم، که N تعداد replicaهاست.

یک هشدار کلیدی درباره read replica هست: replication lag. اگر کاربری تغییری بدهد و فوراً بخواهد آن را بخواند، ممکن است تغییر خودش را نبیند، اگر به replicaای برخورد کند که هنوز به آن نرسیده است. این مسئله به‌عنوان سازگاری «read-your-writes» شناخته می‌شود.

دسترس‌پذیری بالا

دومین مزیت اصلی replication دسترس‌پذیری بالاست. با نگه داشتن کپی داده در چند گره، می‌توانید خرابی سخت‌افزار را بدون downtime مدیریت کنید. اگر گره primary شما از کار بیفتد، یکی از replicaها می‌تواند به primary جدید ارتقا پیدا کند.

این فرایند failover معمولاً شامل این مراحل است:

1. تشخیص این‌که primary از کار افتاده است.
2. ارتقای یک replica به primary.
3. به‌روزرسانی اطلاعات اتصال.
4. هدایت دوباره اپلیکیشن‌ها به primary جدید.

در مصاحبه تأکید کنید که replication فقط برای مقیاس نیست، بلکه برای قابلیت اطمینان هم هست. ممکن است بگویید: «از replication نه فقط برای توزیع بار خواندن، بلکه برای اطمینان از اینکه سرویس ما حتی اگر یک گره پایگاه داده را از دست بدهیم در دسترس می‌ماند استفاده می‌کنیم.»

بیشتر تیم‌ها از سرویس‌های مدیریت‌شده PostgreSQL (مثل AWS RDS یا GCP Cloud SQL) استفاده می‌کنند که پیچیدگی failover را خودکار مدیریت می‌کنند. در مصاحبه کافی است بدانید failover ممکن است و تقریباً چطور کار می‌کند. لازم نیست وارد جزئیات پیکربندی دستی آن شوید.

سازگاری داده

اگر در الزامات غیرعملکردی (Non-Functional Requirements) سازگاری را بر دسترس‌پذیری اولویت داده‌اید، PostgreSQL انتخاب قوی‌ای است. از پایه ساخته شده تا تضمین‌های سازگاری قوی را از طریق تراکنش‌های ACID بدهد. با این حال، فقط انتخاب PostgreSQL کافی نیست. باید بدانید چطور به سازگاری موردنیاز سیستم‌تان برسید.

اشتباه رایج در مصاحبه این است که بگوییم «از PostgreSQL استفاده می‌کنیم چون ACID-compliant است»، بدون این‌که بتوانیم توضیح دهیم واقعاً چطور از آن ویژگی‌های ACID برای حل الزامات سازگاری‌مان استفاده می‌کنید.

تراکنش‌ها

یکی از رایج‌ترین موضوع‌های بحث در مصاحبه‌ها تراکنش است. تراکنش مجموعه‌ای از عملیات است که با هم اجرا می‌شوند و باید همگی با هم موفق شوند یا همگی با هم شکست بخورند. این پایه تضمین سازگاری در PostgreSQL است.

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

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

این تراکنش اتمیک بودن را تضمین می‌کند: یا هر دو به‌روزرسانی انجام می‌شود یا هیچ‌کدام. اما تراکنش‌ها به‌تنهایی سازگاری را در همه سناریوها تضمین نمی‌کنند، به‌خصوص وقتی چند تراکنش هم‌زمان رخ می‌دهند.

تراکنش‌ها و عملیات هم‌زمان

تراکنش‌ها سازگاری دنباله‌ای از عملیات را تضمین می‌کنند، اما وقتی چند تراکنش هم‌زمان اجرا می‌شوند، اوضاع پیچیده‌تر می‌شود. یادتان باشد در بیشتر اپلیکیشن‌های واقعی چند کاربر یا سرویس دارید که هم‌زمان می‌خواهند داده را بخوانند و تغییر دهند.

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

سیستم مزایده را مثال بزنیم. کاربران برای آیتم‌ها bid می‌دهند و ما bid را فقط وقتی می‌پذیریم که از بیشترین bid فعلی بالاتر باشد. یک تراکنش می‌تواند تضمین کند چک کردن bid فعلی و ثبت bid جدید به‌صورت اتمیک انجام شود، اما وقتی دو کاربر هم‌زمان bid می‌دهند چه می‌شود؟

BEGIN;
-- Get current max bid for item 123
SELECT maxBid from Auction where id = 123;

-- Place new bid if it's higher
INSERT INTO bids (item_id, user_id, amount)
VALUES (123, 456, 100);

-- Update the max bid
UPDATE Auction SET maxBid = 100 WHERE id = 123;
COMMIT;

حتی با اینکه این در یک تراکنش است، با سطح ایزولاسیون پیش‌فرض PostgreSQL (Read Committed) اگر دو کاربر هم‌زمان bid بدهند، همچنان می‌توانیم مشکل سازگاری داشته باشیم. هر دو تراکنش می‌توانند بیشترین bid را قبل از commit شدن هرکدام بخوانند.

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

1. تراکنش کاربر A بیشترین bid فعلی را می‌خواند: ۹۰ دلار.
2. تراکنش کاربر B بیشترین bid فعلی را می‌خواند: ۹۰ دلار.
3. کاربر A یک bid ۱۰۰ دلاری ثبت می‌کند.
4. کاربر A commit می‌کند.
5. کاربر B یک bid ۹۵ دلاری ثبت می‌کند.
6. کاربر B commit می‌کند.

حالا وضعیت نامعتبر داریم: bid ۹۵ دلاری بعد از bid ۱۰۰ دلاری پذیرفته شده است!

دو راه اصلی برای حل این مشکل هم‌زمانی وجود دارد.

۱. قفل کردن سطح سطر (Row-Level Locking): ساده‌ترین راه‌حل این است که سطر مزایده را موقع چک کردن و به‌روزرسانی bidها قفل کنیم. با FOR UPDATE به PostgreSQL می‌گوییم سطرهایی را که می‌خوانیم قفل کند. تراکنش‌های دیگری که بخواهند این سطرها را با FOR UPDATE بخوانند باید صبر کنند تا تراکنش ما کامل شود. این تضمین می‌کند هنگام اعمال تغییرات دید ثابتی از داده داریم.

BEGIN;
-- Lock the item and get current max bid
SELECT maxBid FROM Auction WHERE id = 123 FOR UPDATE;

-- Place new bid if it's higher
INSERT INTO bids (item_id, user_id, amount)
VALUES (123, 456, 100);

-- Update the max bid
UPDATE Auction SET maxBid = 100 WHERE id = 123;
COMMIT;

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

۲. سطح ایزولاسیون بالاتر: از طرف دیگر، می‌توانیم از سطح ایزولاسیون سخت‌گیرانه‌تر استفاده کنیم:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Same code as before...

COMMIT;

با اینکه ایزولاسیون Serializable جلوی همه ناهنجاری‌های سازگاری را می‌گیرد، هزینه دارد: اگر دو تراکنش با هم تعارض داشته باشند، یکی rollback می‌شود و باید دوباره امتحان شود. اپلیکیشن شما باید برای مدیریت این سناریوهای retry آماده باشد.

PostgreSQL سه سطح ایزولاسیون را پشتیبانی می‌کند که هرکدام تضمین‌های سازگاری متفاوتی دارند.

• Read Committed (پیش‌فرض): سطح ایزولاسیون پیش‌فرض PostgreSQL است که فقط داده‌ای را می‌بیند که قبل از شروع کوئری commit شده است. در حین اجرای تراکنش‌ها، هر کوئری داخل یک تراکنش می‌تواند commitهای جدید تراکنش‌های دیگر را ببیند که بعد از شروع تراکنش تمام شده‌اند. این کارایی خوبی می‌دهد، اما می‌تواند به nonrepeatable read برسد، یعنی همان کوئری داخل یک تراکنش نتیجه متفاوتی برگرداند.
• Repeatable Read: در PostgreSQL تضمین‌های قوی‌تری از استاندارد SQL می‌دهد. یک snapshot ثابت از داده از شروع تراکنش می‌سازد، و برخلاف بعضی پایگاه‌های داده دیگر، پیاده‌سازی PostgreSQL جلوی nonrepeatable read و phantom read را می‌گیرد. یعنی نه تنها همان کوئری نتیجه یکسانی داخل تراکنش برمی‌گرداند، بلکه هیچ سطر جدیدی که با شرط کوئری شما بخواند ظاهر نمی‌شود، حتی اگر تراکنش‌های دیگر چنین سطرهایی را commit کنند.
• Serializable: قوی‌ترین سطح ایزولاسیون است که باعث می‌شود تراکنش‌ها طوری رفتار کنند انگار پشت سر هم اجرا شده‌اند. جلوی همه نوع ناهنجاری هم‌زمانی را می‌گیرد، اما نیاز به منطق retry در اپلیکیشن شما برای مدیریت تعارض تراکنش‌ها دارد.

پیاده‌سازی Repeatable Read در PostgreSQL به‌طور قابل‌توجهی قوی‌تر از چیزی است که استاندارد SQL لازم دارد. با اینکه پایگاه‌های داده دیگر ممکن است در این سطح ایزولاسیون اجازه phantom read بدهند، PostgreSQL جلویش را می‌گیرد. یعنی ممکن است در مواردی که در پایگاه‌های داده دیگر به ایزولاسیون Serializable نیاز دارید، اینجا نیاز نداشته باشید.

پس کی باید از قفل سطر استفاده کرد و کی از سطح ایزولاسیون بالاتر؟

جنبه	ایزولاسیون Serializable	قفل سطح سطر (Row-Level Locking)
هم‌زمانی	تراکنش‌ها ممکن است به‌خاطر تعارض نیاز به retry داشته باشند	بالاتر، فقط وقتی سطرهای مشترک را لمس کنند تعارض دارند
کارایی	سربار بیشتر، چون باید همه وابستگی‌های خواندن و نوشتن ردیابی شود	سربار کمتر، چون فقط سطرها قفل می‌شوند
زمان استفاده	تراکنش‌های پیچیده که تشخیص این‌که چه چیزی را قفل کنیم سخت است	وقتی دقیقاً می‌دانید کدام سطرها باید اتمیک به‌روز شوند
پیچیدگی	پیاده‌سازی ساده است، اما منطق retry لازم دارد	در کد روشن‌تر است، اما retry لازم ندارد
مدیریت خطا	باید serialization failure را مدیریت کرد	باید سناریوهای Deadlock را مدیریت کرد
مثال	محاسبات مالی پیچیده، به‌روزرسانی چند جدول	به‌روزرسانی مزایده، انتقال بین دو حساب
مصرف حافظه	بالاتر، چون تاریخچه تراکنش‌ها را ردیابی می‌کند	پایین‌تر، چون فقط قفل‌ها را ردیابی می‌کند
مقیاس‌پذیری	با تراکنش‌های هم‌زمان زیاد بدتر مقیاس می‌گیرد	وقتی تعارض نادر است، مقیاس‌پذیری بهتری دارد

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

چه زمانی از PostgreSQL استفاده کنیم (و چه زمانی نه)

توصیه من این است: در مصاحبه طراحی سیستم، PostgreSQL باید انتخاب پیش‌فرض شما باشد، مگر دلیل مشخصی برای استفاده از چیز دیگری داشته باشید. چرا؟ چون PostgreSQL:

• تضمین‌های قوی ACID می‌دهد و هم‌زمان با replication و پارتیشن‌بندی مؤثر مقیاس می‌گیرد.
• داده ساختاریافته و بدون ساختار را از طریق پشتیبانی JSONB مدیریت می‌کند.
• راه‌حل‌های داخلی برای نیازهای رایج مثل جستجوی متن کامل و کوئری مکانی دارد.
• می‌تواند از طریق replication خواندن را مؤثر مقیاس بدهد.
• ابزار عالی و اکوسیستم بالغ دارد.

با PostgreSQL شروع کنید و بعد توجیه کنید چرا ممکن است لازم باشد از آن منحرف شوید. این خیلی قوی‌تر از شروع با راه‌حل مشخص و تلاش برای توجیه این است که چرا از PostgreSQL بهتر است.

PostgreSQL وقتی می‌درخشد که به این‌ها نیاز دارید:

• روابط پیچیده بین داده‌ها.
• سازگاری قوی تضمین‌شده.
• قابلیت جستجوی غنی.
• ترکیب داده ساختاریافته و بدون ساختار (JSONB).
• جستجوی متن کامل داخلی.
• کوئری جغرافیایی.

مثلاً برای:

• پلتفرم‌های تجارت الکترونیک (موجودی، سفارش، داده کاربر).
• سیستم‌های مالی (تراکنش، حساب، audit log).
• سیستم‌های مدیریت محتوا (پست، نظر، کاربر).
• پلتفرم‌های تحلیل (تا مقیاس معقول).

چه زمانی گزینه‌های جایگزین را در نظر بگیریم

گفتنی است که ما اینجا افراطی نیستیم. دلایل موجهی هست که فراتر از PostgreSQL را نگاه کنید.

۱. throughput نوشتن فوق‌العاده: اگر باید میلیون‌ها نوشتن در ثانیه را مدیریت کنید، PostgreSQL به مشکل می‌خورد، چون هر نوشتن یک ورودی WAL و به‌روزرسانی ایندکس می‌خواهد و حتی با سریع‌ترین ذخیره‌ساز گلوگاه I/O می‌سازد. حتی با Sharding، هماهنگ کردن نوشتن‌ها بین گره‌های زیاد PostgreSQL پیچیدگی و تأخیر اضافه می‌کند. در این موارد ممکن است این‌ها را در نظر بگیرید:

• پایگاه‌های داده NoSQL (مثل Cassandra) برای استریم رویداد.
• ذخیره‌گاه‌های کلید-مقدار (مثل Redis) برای شمارنده‌های هم‌زمان.

۲. الزامات چند-ریجنی جهانی: وقتی به استقرار active-active بین ریجن‌ها نیاز دارید (جایی که چند ریجن هم‌زمان نوشتن می‌پذیرند)، PostgreSQL با محدودیت‌های بنیادین روبه‌رو است. معماری تک-primary آن یعنی یک ریجن باید به‌عنوان نویسنده اصلی تعیین شود، در حالی که بقیه نقش read replica دارند. تلاش برای استقرار واقعاً active-active چالش‌های مهمی درباره سازگاری داده و حل تعارض می‌سازد، چون PostgreSQL برای مدیریت نوشتن هم‌زمان از چند primary طراحی نشده است. replication هم‌زمان لازم بین ریجن‌ها هم تأخیر قابل‌توجه می‌سازد، چون تغییرات باید قبل از commit شدن توسط replicaهای دور تأیید شوند. برای این سناریوها این‌ها را در نظر بگیرید:

• CockroachDB برای ACID جهانی.
• Cassandra برای سازگاری نهایی در مقیاس جهانی.
• DynamoDB برای Global Tableهای مدیریت‌شده.

۳. الگوهای ساده دسترسی کلید-مقدار: اگر الگوهای دسترسی شما واقعاً کلید-مقدار هستند (یعنی فقط مقدارها را با کلید ذخیره و بازیابی می‌کنید، بدون Join یا کوئری پیچیده)، PostgreSQL زیادی است. معماری MVCC، WAL logging و query planner پیچیده سربار اضافه می‌کنند که لازم ندارید. در این موارد این‌ها را در نظر بگیرید:

• Redis برای کارایی درون‌حافظه‌ای.
• DynamoDB برای مقیاس‌پذیری مدیریت‌شده.
• Cassandra برای بارهای نوشتن‌سنگین.

مقیاس‌پذیری به‌تنهایی دلیل خوبی برای انتخاب جایگزین PostgreSQL نیست. PostgreSQL با طراحی درست می‌تواند مقیاس قابل‌توجهی را مدیریت کند.

جمع‌بندی

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

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

مجموعه ویژگی‌های غنی PostgreSQL اغلب نیاز به سیستم‌های اضافه در معماری شما را از بین می‌برد. قابلیت‌های جستجوی متن کامل آن ممکن است جای Elasticsearch را بگیرد، پشتیبانی JSONB می‌تواند نیاز به MongoDB را برطرف کند و PostGIS نیازهای مکانی را که در غیر این صورت ممکن بود پایگاه داده تخصصی بخواهند پوشش می‌دهد. replication داخلی اغلب قابلیت مقیاس‌دهی کافی برای بسیاری از کاربردها فراهم می‌کند. با این حال، تشخیص این‌که PostgreSQL کی ممکن است بهترین گزینه نباشد به همان اندازه مهم است، مثل مواردی که مقیاس‌دهی شدید نوشتن یا توزیع جهانی لازم دارند، که در آن پایگاه‌هایی مثل Cassandra یا CockroachDB ممکن است مناسب‌تر باشند.

پیوست، مفاهیم پایه SQL

قبل از ورود به ویژگی‌های مشخص PostgreSQL، نحوه سازمان‌دهی داده در پایگاه‌های داده SQL را مرور کنیم. این مفاهیم اصلی برای هر پایگاه داده SQL، نه فقط PostgreSQL، پایه ضروری‌ای هستند که در طول این بررسی عمیق روی آن‌ها بنا کرده‌ایم.

اصول پایگاه داده رابطه‌ای

PostgreSQL در هسته‌اش داده را در جدول‌ها (که relation هم نامیده می‌شوند) ذخیره می‌کند. جدول را مثل صفحه‌گسترده با سطر و ستون ببینید. هر ستون نوع داده مشخصی دارد (مثل متن، عدد یا تاریخ) و هر سطر یک رکورد کامل را نشان می‌دهد.

مثال مشخصی ببینیم. فرض کنید پلتفرم شبکه اجتماعی طراحی می‌کنیم. ممکن است جدول کاربری داشته باشیم که این‌طور باشد:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

این دستور جدول زیر را می‌سازد (داده فقط برای مثال است):

id	username	email	created_at
1	johndoe	[email protected]	2024-01-01 10:00:00
2	janedoe	[email protected]	2024-01-01 10:05:00
3	bobsmith	[email protected]	2024-01-01 10:10:00

وقتی کاربر جدید ثبت‌نام می‌کند، سطر جدیدی در این جدول می‌سازیم. هر کاربر شناسه یکتا می‌گیرد (همان کلید اصلی) و مطمئن می‌شویم هیچ دو کاربری نام کاربری یا ایمیل یکسان ندارند (کاری که UNIQUE می‌کند).

اما کاربران به‌تنهایی چندان جالب نیستند. باید بتوانند محتوا پست کنند. اینجا بخش «رابطه‌ای» پایگاه‌های داده رابطه‌ای وارد می‌شود. می‌توانیم جدول پست‌هایی بسازیم که به کاربرانمان وصل است:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

id	user_id	content	created_at
1	1	Hello, World!	2024-01-01 10:00:00
2	1	My first post	2024-01-01 10:05:00
3	2	Another post	2024-01-01 10:10:00

REFERENCES users(id) را می‌بینید؟ این کلید خارجی (foreign key) نام دارد و رابطه‌ای بین پست‌ها و کاربران می‌سازد. هر پست باید متعلق به کاربر معتبری باشد و PostgreSQL این را برای ما اعمال می‌کند. این یکی از نقاط قوت کلیدی پایگاه‌های داده رابطه‌ای است: با اعمال این روابط به حفظ یکپارچگی داده کمک می‌کنند.

در مصاحبه، توانایی توضیح این روابط خیلی مهم است. سه نوع اصلی هست:

• یک به یک: کاربران و تنظیمات پروفایلشان.
• یک به چند: کاربران و پست‌هایشان (یک کاربر می‌تواند پست‌های زیادی داشته باشد).
• چند به چند: کاربران و پست‌هایی که لایک می‌کنند (که در ادامه می‌بینیم).

حالا اگر بخواهیم کاربران بتوانند پست‌ها را لایک کنند چه؟ این یک رابطه چند به چند معرفی می‌کند. یک کاربر می‌تواند پست‌های زیادی را لایک کند و یک پست می‌تواند توسط کاربران زیادی لایک شود. این را با چیزی به نام join table مدیریت می‌کنیم:

CREATE TABLE likes (
    user_id INTEGER REFERENCES users(id),
    post_id INTEGER REFERENCES posts(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

این ساختار، که در آن داده را به جدول‌های جدا تقسیم می‌کنیم و از طریق روابط به هم وصلشان می‌کنیم، «normalization» نامیده می‌شود. به ما کمک می‌کند:

• از تکرار داده پرهیز کنیم (اطلاعات کاربر را در هر پست ذخیره نمی‌کنیم).
• یکپارچگی داده را حفظ کنیم (اگر کاربر نام کاربری‌اش را عوض کند، همه‌جا به‌روز می‌شود).
• مدل داده را انعطاف‌پذیر نگه داریم (می‌توانیم attributeهای جدید کاربر را بدون دست زدن به پست‌ها اضافه کنیم).

با اینکه normalization به‌طور کلی خوب است، گاهی عمداً داده را برای کارایی denormalize می‌کنیم. مثلاً ممکن است تعداد لایک یک پست را مستقیم در جدول پست‌ها ذخیره کنیم، حتی اگر بتوانیم آن را از جدول لایک‌ها حساب کنیم. این مصالحه بین سازگاری داده و کارایی کوئری دقیقاً چیزی است که باید در مصاحبه درباره‌اش صحبت کنید!

فهمیدن این اصول، یعنی جدول‌ها، روابط و normalization، مهم است. همین‌ها پایگاه‌های داده SQL مثل PostgreSQL را برای اپلیکیشن‌هایی که باید روابط پیچیده بین انواع مختلف داده را نگه دارند خیلی قدرتمند می‌کنند. در مصاحبه، این‌که بتوانید نه فقط توضیح دهید چطور از این مفاهیم استفاده می‌کنید، بلکه کی و چرا (یا کی آن‌ها را می‌شکنید)، شما را متمایز می‌کند.

ویژگی‌های ACID

یکی از بزرگ‌ترین نقاط قوت PostgreSQL پایبندی دقیق آن به ویژگی‌های ACID (Atomicity، Consistency، Isolation و Durability) است. اگر از پایگاه‌هایی مثل MongoDB یا Cassandra استفاده کرده‌اید، با تضمین‌های سازگاری نهایی یا تراکنش‌های ساده‌شده، که مصالحه‌های رایج در پایگاه‌های داده NoSQL هستند، آشنایید. PostgreSQL رویکرد متفاوتی دارد: تضمین می‌کند داده‌های شما همیشه از همه قواعد و محدودیت‌های تعریف‌شده (مثل کلید خارجی، قیدهای یکتایی و بررسی‌های سفارشی) پیروی می‌کنند و همه تراکنش‌ها یا کامل انجام می‌شوند یا اصلاً انجام نمی‌شوند، حتی اگر به معنای فدا کردن مقداری کارایی باشد.

معنای ACID را با یک مثال واقعی بشکافیم: انتقال پول بین حساب‌های بانکی.

Atomicity (همه یا هیچ)

تصور کنید ۱۰۰ دلار از حساب پس‌انداز خود به حساب جاری‌تان منتقل می‌کنید. این دو عملیات دارد:

1. ۱۰۰ دلار از پس‌انداز کم کن.
2. ۱۰۰ دلار به حساب جاری اضافه کن.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'savings';
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'checking';
COMMIT;

اتمیک بودن تضمین می‌کند یا هر دو عملیات موفق شوند یا هیچ‌کدام. اگر سیستم بعد از کم کردن از پس‌انداز اما قبل از اضافه کردن به حساب جاری crash کند، PostgreSQL کل تراکنش را rollback می‌کند. پول شما هرگز در هوا ناپدید نمی‌شود.

در مصاحبه تأکید کنید اتمیک بودن چطور جلوی شکست جزئی را می‌گیرد. بدون آن، سیستم‌های توزیع‌شده می‌توانند به وضعیت‌های ناسازگاری برسند که بازیابی از آن‌ها بسیار سخت است.

Consistency (یکپارچگی داده)

Consistency تضمین می‌کند تراکنش‌ها فقط می‌توانند پایگاه داده را از یک state معتبر به state معتبر دیگر ببرند. مثلاً فرض کنید قاعده‌ای داریم که موجودی حساب نمی‌تواند منفی شود:

CREATE TABLE accounts (
    account_id TEXT PRIMARY KEY,
    balance DECIMAL CHECK (balance >= 0),
    owner_id INTEGER REFERENCES users(id)
);

اگر تراکنشی موجودی شما را منفی کند، PostgreSQL کل تراکنش را رد می‌کند. این با پایگاه‌های داده NoSQL فرق دارد که اغلب باید این قواعد را در کد اپلیکیشن اعمال کنید.

به شکل گیج‌کننده‌ای، Consistency در ACID معنای کمی متفاوت از Consistency در قضیه CAP دارد. در ACID، Consistency یعنی پایگاه داده همیشه از همه قواعد و محدودیت‌های تعریف‌شده پیروی می‌کند. در قضیه CAP، Consistency یعنی پایگاه داده همیشه نتیجه درست را برمی‌گرداند، حتی اگر به معنای فدا کردن دسترس‌پذیری یا تحمل پارتیشن باشد.

Isolation (تراکنش‌های هم‌زمان)

سطوح ایزولاسیون تعیین می‌کنند تراکنش‌ها چطور می‌توانند با داده‌ای که تراکنش‌های هم‌زمان دیگر تغییر می‌دهند تعامل کنند. PostgreSQL از چهار سطح ایزولاسیون پشتیبانی می‌کند که هرکدام جلوی انواع مختلف پدیده‌ها را می‌گیرند:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Default level
-- or REPEATABLE READ
-- or SERIALIZABLE
COMMIT;

سطح پیش‌فرض «Read Committed» در PostgreSQL فقط جلوی dirty read (خواندن تغییرات commit نشده) را می‌گیرد. nonrepeatable read و phantom read را اجازه می‌دهد. برای جزئیات ناهنجاری‌هایی که هر سطح از آن‌ها جلوگیری می‌کند، مستندات PostgreSQL را ببینید.

با اینکه استاندارد SQL چهار سطح ایزولاسیون تعریف می‌کند، PostgreSQL داخلاً فقط سه سطح متمایز را پیاده می‌کند. به‌طور مشخص، Read Uncommitted در PostgreSQL مثل Read Committed رفتار می‌کند. این انتخاب طراحی با معماری کنترل هم‌زمانی چندنسخه‌ای (MVCC) در PostgreSQL هم‌راستاست، که همیشه ایزولاسیون snapshot می‌دهد و خواندن داده commit نشده را غیرممکن می‌کند.

سطح ایزولاسیون	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read Uncommitted	مجاز، اما در PG نه	ممکن	ممکن	ممکن
Read Committed	ممکن نیست	ممکن	ممکن	ممکن
Repeatable Read	ممکن نیست	ممکن نیست	مجاز، اما در PG نه	ممکن
Serializable	ممکن نیست	ممکن نیست	ممکن نیست	ممکن نیست

Durability (ذخیره‌سازی ماندگار)

وقتی PostgreSQL می‌گوید تراکنشی commit شده است، تضمین می‌شود آن داده روی دیسک نوشته و sync شده است و در برابر crash یا قطع برق محافظت می‌شود. این از طریق Write-Ahead Logging (WAL) به دست می‌آید:

1. تغییرات اول در یک log نوشته می‌شوند.
2. log روی دیسک flush می‌شود.
3. فقط بعد از آن، تراکنش commit می‌شود.

با اینکه دوام تضمین شده است، هزینه کارایی هم دارد. بعضی اپلیکیشن‌ها ممکن است برای سرعت از دوام بگذرند (مثلاً با تنظیم synchronous_commit = off)، یعنی بعضی نوشتن‌هایی که روی دیسک نوشته نشده‌اند ممکن است در صورت قطع برق یا crash از بین بروند.

چرا ACID مهم است

در مصاحبه اغلب باید بین انواع مختلف پایگاه داده تصمیم بگیرید. سازگاری با ACID عامل مهمی در این تصمیم است. این سناریوها را در نظر بگیرید:

• تراکنش‌های مالی: برای جلوگیری از گم شدن یا دوبار خرج شدن پول، کاملاً به ویژگی‌های ACID نیاز دارید.
• لایک شبکه اجتماعی: ممکن است با سازگاری نهایی مشکلی نداشته باشید.
• احراز هویت کاربر: احتمالاً ACID را برای جلوگیری از مشکلات امنیتی می‌خواهید.
• داده analytics: ممکن است کارایی را بر یکپارچگی دقیق ترجیح دهید.

پایبندی دقیق PostgreSQL به ACID آن را برای سیستم‌هایی که سازگاری داده در آن‌ها حیاتی است انتخاب عالی می‌کند. با اینکه امروز مصالحه‌های کارایی جزئی‌اند، هنوز ارزش دارد از آن‌ها آگاه باشید.

زبان SQL

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

انواع دستورات SQL

دستورات SQL به چهار دسته اصلی تقسیم می‌شوند.

DDL (Data Definition Language): ساختار پایگاه داده را می‌سازد و تغییر می‌دهد. مثال‌ها: CREATE TABLE، ALTER TABLE، DROP TABLE.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

ALTER TABLE users ADD COLUMN username TEXT;

DML (Data Manipulation Language): داده داخل جدول‌ها را مدیریت می‌کند. مثال‌ها: SELECT، INSERT، UPDATE، DELETE.

-- Find all users who joined in the last week
SELECT * FROM users
WHERE created_at > NOW() - INTERVAL '7 days';

-- Update a user's email
UPDATE users SET email = '[email protected]'
WHERE id = 123;

DCL (Data Control Language): مجوزهای دسترسی را کنترل می‌کند. مثال‌ها: GRANT، REVOKE.

-- Give read access to a specific user
GRANT SELECT ON users TO read_only_user;

TCL (Transaction Control Language): تراکنش‌ها را مدیریت می‌کند. مثال‌ها: BEGIN، COMMIT، ROLLBACK.

BEGIN;
  -- Multiple operations...
COMMIT;

در مصاحبه ممکن است به‌جای کوئری‌های مشخص، درباره الگوهای دسترسی به پایگاه داده از شما بپرسند. مثلاً «این داده را چطور کارآمد کوئری می‌کنید؟» یا «چه ایندکس‌هایی می‌سازید؟». این سوال‌ها درک شما از مفاهیم پایگاه داده را می‌سنجند، نه سینتکس SQL را.