مقایسه Postgres و MySQL

بررسی تفاوت‌های کلیدی همراه با مثال‌های کاربردی

یکی از دانشجویان دوره‌ام در udemy درباره تفاوت بین Postgres و MySQL سؤالی پرسید. پاسخم آن‌قدر مفصل شد که تصمیم گرفتم آن را در قالب یک پست وبلاگی کامل با شما به اشتراک بگذارم.

در یک کلام، تفاوت اصلی میان این دو پایگاه داده در نهایت به نحوه پیاده‌سازی ایندکس‌های اصلی (Primary) و ثانویه (Secondary) و شیوه ذخیره‌سازی و به‌روزرسانی داده‌ها برمی‌گردد.

بیایید این موضوع را دقیق‌تر و با جزئیات بیشتر بررسی کنیم.

اما پیش از هر چیز... مفاهیم پایه‌ای

ایندکس یک ساختار داده (عمدتاً درخت B+Tree) است که امکان جستجوی کلیدها را از میان لایه‌های مختلف گره‌ها — که دیتابیس‌ها آن‌ها را در قالب Page پیاده‌سازی می‌کنند — فراهم می‌سازد. با پیمایش این درخت، صفحاتی که حاوی دادهٔ موردنظر نیستند کنار گذاشته می‌شوند و دایره جستجو به صفحاتی که پاسخ در آن‌هاست محدودتر می‌شود؛ این فرایند تا رسیدن به صفحه برگ (Leaf Page) که کلید اصلی در آن قرار دارد ادامه می‌یابد.

گره‌ها یا صفحات برگ حاوی فهرستی از کلیدهای مرتب‌شده به همراه مقادیر آن‌ها هستند. وقتی کلید پیدا شد، مقدار متناظرش در اختیارتان قرار می‌گیرد و آن صفحه نیز در Shared Buffers دیتابیس کش (Cache) می‌شود، با این امید که کوئری‌های بعدی شاید به کلیدهای موجود در همین صفحه نیاز داشته باشند.

درک عمیق همین جمله آخر، عصاره و بنیاد تمام مباحث مهندسی، مدیریت، برنامه‌نویسی و مدل‌سازی پایگاه داده است. اگر بدانید کوئری‌های شما به کلیدهایی که در یک صفحه کنار هم قرار گرفته‌اند دسترسی دارند، تعداد عملیات ورودی/خروجی (I/O) به حداقل رسیده و کارایی سیستم به شکل چشمگیری افزایش می‌یابد.

کلیدها در ایندکس‌های B+Tree همان ستون یا ستون‌هایی از جدول هستند که ایندکس روی آن‌ها ساخته شده است، اما مفهوم «مقدار» (Value) چیزی است که پایگاه‌های داده آن را به شکل متفاوتی پیاده‌سازی می‌کنند. بیایید ببینیم این مقدار در Postgres و MySQL چه فرقی با هم دارد.

پایگاه داده MySQL

در یک Primary Index، مقدار در واقع کل آبجکت سطر به همراه تمام ویژگی‌ها (Attributeها)* است. به همین دلیل به ایندکس‌های اصلی، Clustered Index یا به تعبیری که من ترجیح می‌دهم، Index-Organized Table می‌گویند؛ یعنی خودِ ایندکس اصلی، همان جدول است.

*توجه داشته باشید که این موضوع در مدل‌های Row-Store صدق می‌کند؛ دیتابیس‌ها ممکن است از مدل‌های ذخیره‌سازی دیگری مانند Column-Store، گراف یا سند (Document) استفاده کنند که ساختار مقادیر در آن‌ها متفاوت خواهد بود.

اگر به دنبال کلیدی در ایندکس اصلی بگردید، صفحه‌ای که کلید در آن قرار دارد و مقدار متناظر آن (که کل سطرِ آن کلید است) را پیدا می‌کنید و برای واکشی سایر ستون‌ها دیگر نیازی به I/O بیشتر نخواهد بود.

در مقابل، در ایندکس‌های ثانویه (Secondary Index)، کلید همان ستون یا ستون‌هایی است که ایندکس کرده‌اید و مقدارِ متناظر آن، کلید اصلی (Primary Key) است. بنابراین برای خواندن سایر ویژگی‌ها و واکشی سطر کامل، باید با استفاده از کلید اصلی به‌دست‌آمده از اسکن اولیه، یک Index Scan دیگر روی ایندکس اصلی انجام شود.

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

پایگاه داده Postgres

از نظر فنی در Postgres چیزی به نام Primary Index وجود ندارد؛ همهٔ ایندکس‌ها ثانویه محسوب می‌شوند و همگی به شناسه تاپل‌ها (Tuple IDها) که توسط سیستم در صفحات دادهٔ Heap مدیریت می‌شوند، اشاره می‌کنند. برخلاف صفحات برگ در ایندکس اصلی که مرتب هستند، داده‌های جدول در Heap نامرتب‌اند. بنابراین، اگر سطرهای ۱ تا ۱۰۰ را وارد کنید و همگی در یک صفحه قرار بگیرند، اما بعداً سطرهای ۱ تا ۲۰ را آپدیت کنید، ممکن است آن ۲۰ سطر به صفحه دیگری منتقل شوند و ترتیبشان به هم بریزد؛ در حالی که در یک Clustered Primary Index، درج داده حتماً باید در صفحه‌ای انجام شود که ترتیب کلید را حفظ کند. به همین دلیل است که جدول‌های Postgres را به جای «Index-Organized» اغلب «Heap-Organized Tables» می‌نامند.

نکته مهم اینجاست که در Postgres، عملیات Update و Delete در واقع همان Insert هستند! هر به‌روزرسانی یا حذف، یک Tuple ID جدید ایجاد می‌کند و شناسه تاپل قبلی به دلایل مربوط به MVCC نگه داشته می‌شود؛ موضوعی که در ادامه پست بیشتر به آن خواهم پرداخت.

واقعیت این است که صرفاً داشتن TID (شناسه تاپل) کافی نیست، بلکه هم به شناسه تاپل و هم به شماره صفحه نیاز داریم؛ مفهومی که به عنوان c_tid شناخته می‌شود. اگر دقت کنید، دانستن شناسه تاپل به تنهایی فایده‌ای ندارد، بلکه باید بدانیم تاپل در کدام صفحه قرار دارد. در MySQL نیازی به این کار نداشتیم چون مستقیماً برای یافتن صفحه کلید اصلی یک Lookup انجام می‌دادیم، در حالی که در Postgres برای واکشی سطر کامل، مستقیماً یک عملیات I/O انجام می‌دهیم.

هزینه اجرای کوئری‌ها (Queries Cost)

برای درک بهتر مثال‌های زیر، این جدول فرضی را در نظر بگیرید:

بیایید ببینیم در سناریوهای مختلف چه اتفاقی در MySQL در مقایسه با Postgres می‌افتد:

اجرای چنین کوئری‌ای در MySQL مستلزم دو بار Lookup در درخت B+Tree خواهد بود*. ابتدا باید با استفاده از ایندکس ثانویه به دنبال x2 بگردیم تا کلید اصلی آن یعنی عدد ۱ را پیدا کنیم، سپس یک جستجوی دیگر روی ایندکس اصلی برای کلید ۱ انجام دهیم تا سطر کامل واکشی شده و تمام ستون‌ها (همان عملگر *) بازگردانده شوند.

*شاید فکر کنید این فرایند فقط دو عملیات I/O است، اما در واقعیت این‌طور نیست؛ یک جستجو در B+Tree پیچیدگی O(logN) دارد و بسته به اندازه درخت، ممکن است به چندین I/O منجر شود. گرچه بیشتر این I/Oها منطقی هستند (یعنی از صفحات کش‌شده در Shared Buffers خوانده می‌شوند)، اما درک تفاوت آنها بسیار کلیدی است.

در Postgres، جستجو در هر ایندکس ثانویه‌ای تنها به یک بار جستجوی ایندکس نیاز دارد و پس از آن با یک I/O ثابت و تکی به Heap، صفحه‌ای که سطر کامل در آن قرار دارد واکشی می‌شود. مشخصاً یک بار پیمایش B+Tree بسیار کم‌هزینه‌تر از دو بار پیمایش است.

برای جذاب‌تر شدن ماجرا، فرض کنید ستون C2 یکتا (Unique) نباشد و چندین ردیف با مقدار x2 داشته باشیم؛ در این حالت با انبوهی از TIDها (یا کلیدهای اصلی در MySQL) مواجه می‌شویم که با x2 همخوانی دارند. مشکل اینجاست که این شناسه‌ها در صفحات مختلف پراکنده‌اند و منجر به Random Read می‌شوند. در MySQL این مسئله باعث چندین Index Lookup پیاپی می‌شود (یا شاید بر اساس حجم کلیدها، Planner تصمیم به انجام یک index scan به جای Seek بگیرد)، اما در نهایت هر دو دیتابیس با تعداد زیادی I/O تصادفی مواجه خواهند شد.

پایگاه داده Postgres سعی می‌کند با استفاده از Bitmap Index Scan، اثر منفی Random Readها را به حداقل برساند؛ به این صورت که نتایج را به جای تاپل‌ها بر اساس صفحات گروه‌بندی کرده و صفحات را با کمترین تعداد I/O ممکن از Heap واکشی می‌کند. سپس فیلترهای تکمیلی اعمال می‌شوند تا سطرهای نهایی استخراج گردند.

حالا بیایید کوئری متفاوتی را بررسی کنیم.

به عقیده من در کوئری‌های بازه‌ای (Range Queries) روی ایندکس کلید اصلی، برنده بی‌چون‌وچرا MySQL است؛ چرا که با یک Lookup ساده کلید اول پیدا می‌شود و سپس با پیمایش صفحات برگ متصل به هم در B+Tree، کلیدهای مجاور و همزمان سطر کامل آن‌ها استخراج می‌شوند.

اما به نظرم Postgres در این زمینه به چالش می‌خورد؛ درست است که جستجوی ایندکس ثانویه همان پیمایش برگ‌های B+Tree را انجام داده و کلیدها را پیدا می‌کند، اما حاصل کار صرفاً جمع‌آوری TIDها و صفحات است و کار هنوز تمام نشده! Postgres همچنان باید برای واکشی سطرهای کامل دست به Random Readهای متعدد در Heap بزند؛ سطرهایی که ممکن است در سراسر Heap پراکنده باشند و کنار هم قرار نگرفته باشند (به‌ویژه اگر ردیف‌ها قبلاً آپدیت شده باشند). بارهای کاریِ پر از عملیات Update دشمن خونی Postgres هستند؛ پس حواستان باشد که یک FillFactor مناسب برای جدولتان انتخاب کنید.

خب، حالا بیایید یک سناریوی آپدیت (Update) را بررسی کنیم.

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

اما در Postgres، به‌روزرسانی ستونی که ایندکس هم نشده یک تاپل جدید می‌سازد و ممکن است* لازم باشد «تمام» ایندکس‌های ثانویه با Tuple ID جدید آپدیت شوند؛ چون آن‌ها فقط شناسه تاپل قبلی را می‌شناسند. این موضوع منجر به حجم زیادی از عملیات Write I/O می‌شود. شرکت Uber در سال ۲۰۱۶ اصلاً از این قضیه دلِ خوشی نداشت و اتفاقاً همین رفتار یکی از دلایل اصلی مهاجرت آن‌ها از Postgres به MySQL بود.

اینکه گفتم «ممکن است»، به این خاطر است که در Postgres بهینه‌سازی خاصی به نام HOT (مخفف Heap Only Tuple — که نباید آن را با Heap-Organized Table اشتباه گرفت) وجود دارد؛ در این حالت شناسه تاپل قبلی در ایندکس‌های ثانویه حفظ می‌شود اما در هدر صفحه Heap پیوندی قرار می‌گیرد که تاپل قدیمی را به تاپل جدید متصل می‌کند.

نوع داده‌ها (Data Types) اهمیت زیادی دارد

در MySQL، انتخاب نوع داده برای کلید اصلی حیاتی است، زیرا این کلید در تک‌تک ایندکس‌های ثانویه تکرار می‌شود. برای مثال، استفاده از UUID به عنوان کلید اصلی، حجم تمام ایندکس‌های ثانویه را متورم کرده و فضای ذخیره‌سازی و Read I/O را به شدت افزایش می‌دهد.

در Postgres اما اندازه Tuple ID همیشه ۴ بایت ثابت است؛ بنابراین ایندکس‌های ثانویه حاوی مقادیر طولانی UUID نخواهند بود و صرفاً به TIDهایی اشاره می‌کنند که به Heap وصل هستند.

لاگ‌های بازگردانی (Undo Logs)

تمام پایگاه‌های داده مدرن از قابلیت کنترل همروندی چندنسخه‌ای (MVCC) پشتیبانی می‌کنند. در سطح ایزولاسیون ساده Read Committed، اگر یک تراکنش (tx1) سطری را آپدیت کند اما هنوز Commit نکرده باشد، و همزمان تراکنش دیگری (tx2) بخواهد همان سطر را بخواند، «باید» نسخه قبلی سطر را بخواند نه نسخه به‌روزرسانی‌شده را. اغلب دیتابیس‌ها (از جمله MySQL) این قابلیت را به کمک Undo Logها پیاده‌سازی می‌کنند.

وقتی یک تراکنش تغییری در سطر ایجاد می‌کند، این تغییر در صفحهٔ مربوطه در Shared Buffer Pool نوشته می‌شود تا آن صفحه همیشه حاوی تازه‌ترین داده باشد. سپس تراکنش اطلاعات لازم برای بازگردانی (Undo) این تغییرات — یعنی داده‌های کافی برای بازسازی وضعیت قبلی — را در یک Undo Log ثبت می‌کند. بدین ترتیب، تراکنش‌های همزمانی که طبق سطح ایزولاسیون خود به داده‌های قبلی نیاز دارند، به سراغ Undo Log رفته و وضعیت پیشین سطر را بازسازی می‌کنند.

شاید با خود بگویید آیا نوشتن تغییرات کامیت‌نشده در صفحه کار درستی است؟ اگر یک پروسس پس‌زمینه صفحه را روی دیسک بنویسد و درست قبل از کامیت شدن تراکنش، دیتابیس کرش کند چه می‌شود؟ اینجاست که اهمیت حیاتی Undo Log مشخص می‌شود؛ بلافاصله پس از Crash و با راه‌اندازی مجدد دیتابیس، این تغییرات کامیت‌نشده با استفاده از Undo Logها به حالت اول بازگردانده می‌شوند*.

نمی‌توان هزینه و بار سنگینی را که Undo Logهای تراکنش‌های طولانی‌مدت (Long-Running Transactions) بر دوش سایر تراکنش‌ها می‌گذارند نادیده گرفت؛ در این وضعیت برای بازسازی حالات قبلی به I/O بیشتری نیاز است و حتی احتمال دارد با پر شدن حجم Undo Log، تراکنش با خطا مواجه شود.

شخصاً سیستمی را دیده‌ام که پس از اجرای یک تراکنش طولانی و کامیت‌نشدهٔ ۳ ساعته دچار کرش شد و بازیابی (Recovery) آن بیش از یک ساعت طول کشید! بنابراین به هر قیمتی که شده از تراکنش‌های طولانی دوری کنید.

سیستم Postgres این فرایند را به شکلی کاملاً متفاوت مدیریت می‌کند؛ در هر Update، Insert و Delete، یک نسخه جدید از سطر با یک شناسه تاپل تازه ساخته می‌شود که اطلاعاتی درباره شناسه تراکنش سازنده و حذف‌کننده آن به همراه دارد. به همین دلیل Postgres می‌تواند با خیال راحت تغییرات را در صفحات داده بنویسد و تراکنش‌های همزمان نیز بر اساس شناسه تراکنش خود، تاپل‌های قدیمی یا جدید را بخوانند؛ یک معماری فوق‌العاده هوشمندانه.

البته هیچ راه‌حلی بی‌عیب‌ونقص نیست. پیش‌تر درباره هزینه ساخت Tuple IDهای جدید در ایندکس‌های ثانویه صحبت کردیم؛ علاوه بر آن، Postgres باید تاپل‌های قدیمی و بلااستفاده‌ای را که شناسه همه تراکنش‌های فعال از شناسه تراکنشِ حذف‌کننده آن‌ها بزرگ‌تر است پاک‌سازی کند؛ وظیفه‌ای که فرایند Vacuum به عهده دارد.

مقایسه Purge و Vacuum

هر دو دیتابیس MySQL و Postgres به یک فرایند پاک‌سازی برای حذف داده‌های مرده و اضافه نیاز دارند. MySQL فرایندی به نام «purge» را اجرا می‌کند، در حالی که PostgreSQL از «vacuum» بهره می‌برد.

فرایند Purge لاگ‌های بازگردانی بدون استفاده‌ای را که متعلق به تراکنش‌های کامیت‌شده یا تراکنش‌های Rollback شده (در اثر کرش) هستند و دیگر نیازی به آن‌ها نیست پاک می‌کند؛ چرا که انباشته شدن بیش از حد Undo Logها می‌تواند سرعت خواندن داده‌ها را کاهش دهد.

در سوی دیگر، فرایند Vacuum در Postgres (که در واقع شامل چندین فرایند است) برای آزادسازی فضای اشغال‌شده توسط «تاپل‌های مرده» (Dead Tuples) طراحی شده است. همان‌طور که گفتیم، هنگام آپدیت یا حذف یک سطر در Postgres، نسخه قبلی بلافاصله از روی دیسک حذف نمی‌شود، بلکه به عنوان «مرده» علامت‌گذاری می‌گردد. فرایند Vacuum این تاپل‌های مرده را پاک‌سازی می‌کند تا جا برای داده‌های جدید باز شود و از متورم شدن جدول (Table Bloat) جلوگیری به عمل آید. علاوه بر این، Vacuum نقشه دسترسی (Visibility Map) را به‌روزرسانی می‌کند که نقش مهمی در بهینه‌سازی کوئری‌ها دارد و در صورت نیاز، با تحلیل پایگاه داده، آمارهای مربوط به Query Planner را به‌روز می‌سازد.

پروسس‌ها در برابر تردها (Processes vs Threads)

موتور MySQL از Threadها استفاده می‌کند در حالی که Postgres مبتنی بر Processهاست. هر کدام از این رویکردها مزایا و معایب خود را دارند که در یک پست مجزا در اینجا به تفصیل درباره‌شان صحبت کرده‌ام.

شخصاً در سیستم‌های پایگاه داده، Threadها را به Processها ترجیح می‌دهم؛ صرفاً به این دلیل که سبک‌تر هستند و فضای آدرس حافظه مجازی پروسس والد خود را به اشتراک می‌گذارند. در مقابل، پروسس‌ها بار اضافه ناشی از حافظه مجازی اختصاصی و بلوک کنترل بزرگ‌تر (PCB) را در مقایسه با بلوک کنترل کوچک‌تر ترد (TCB) به همراه دارند.

هنگامی که سوییچ زمینه (Context Switch) بین دو پروسس رخ می‌دهد، باید TLB (مخفف Translation Lookaside Buffer) که کش نگاشت حافظه مجازی به فیزیکی را نگه می‌دارد باطل (Invalidate) شود؛ چرا که نگاشت‌های پروسس جدید با پروسس قبلی متفاوت است. بنابراین با خالی شدن TLB، هسته پردازنده ناچار است به Page Table در حافظه رجوع کرده و این نگاشت‌ها را دوباره بخواند که باعث کند شدن فرایند جستجو می‌شود. اما در جابه‌جایی بین تردها، می‌توان به راحتی TLB را به اشتراک گذاشت، چرا که آدرس مجازی میان ترد و پروسس والد مشترک است؛ به عبارت دیگر، از لحاظ حافظه در همان پروسس قرار داریم و صرفاً باید شمارنده برنامه (Program Counter)، اشاره‌گرهای پشته (Stack Pointers) و رجیسترها به‌روزرسانی شوند که همین موضوع سوئیچ تردها را بسیار سریع‌تر می‌کند.

وقتی در نهایت قرار است حافظه را به اشتراک بگذاریم و با Mutexها و Semaphoreها سر و کله بزنیم، چرا از همان اول سراغ Thread نرویم؟ البته این صرفاً نظر شخصی من است.

جمع‌بندی

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

اینجا هیچ انتخاب مطلقاً درست یا غلطی وجود ندارد.

اگر این مطلب برایتان مفید بود، پیشنهاد می‌کنم دوره fundamentals of database engineering من را هم مشاهده کنید.