نحوه ایجاد ایندکس یکتا در PostgreSQL

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

آنچه در این مقاله می‌خوانید:

• چرا باید یک ایندکس منحصربه‌فرد اضافه کنیم؟
• ایجاد یک unique index
• ایجاد ایندکس یکتا به‌صورت هم‌زمان
• ساخت مجدد ایندکس به‌صورت دستی
• جمع‌بندی
• سوالات متداول

چرا باید یک ایندکس منحصربه‌فرد اضافه کنیم؟

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

برای مثال فرض کنید یک جدول واسط با ساختار many-to-many به نام reference_count(document_id, object_id, count) دارید. اگر بخواهید به‌صورت مکرر رکوردهای این جدول را upsert کنید؛ یعنی اگر رکورد وجود دارد، مقدار count را به‌روزرسانی کنید و اگر وجود ندارد، رکورد جدید درج شود.

به عنوان مثال:

INSERT INTO 
	reference_count (document_id, object_id, count) 
VALUES 
	(...) 
ON CONFLICT (document_id, object_id) DO UPDATE;

در اینجا، باید راهی وجود داشته باشد که تکراری بودن ترکیب document_id و object_id را تشخیص دهد.

در اینجا، یک ایندکس منحصربه‌فرد روی این دو ستون نه‌تنها از ورود داده‌های تکراری جلوگیری می‌کند، بلکه امکان استفاده از دستور ON CONFLICT در upsert را فراهم می‌کند. این کار باعث می‌شود منطق ثبت داده ساده‌تر، سریع‌تر و مطمئن‌تر اجرا شود.

upsert قابلیتی در پایگاه داده است که امکان درج یک ردیف جدید یا به‌روزرسانی یک ردیف موجود را بر اساس یک شناسه یکتا فراهم می‌کند؛ این شناسه می‌تواند شامل یک یا چند ستون باشد. در PostgreSQL این کار با استفاده از عبارت ON CONFLICT DO UPDATE انجام می‌شود، که نیازمند وجود یک ایندکس یکتا مطابق با آن شناسه است. برای اطلاعات بیشتر، به مستندات PostgreSQL در این زمینه مراجعه کنید.

تا اینجا متوجه شدیم چرا اضافه کردن ایندکس یکتا اهمیت دارد؛ در ادامه، به روش‌های مختلف ایجاد ایندکس یکتا می‌پردازیم.

با پایگاه داده PostgreSQL لیارا، در کمترین زمان، یک دیتابیس قدرتمند، پرسرعت و امن راه‌اندازی کنید.
✅ پشتیبان‌گیری خودکار ✅ امنیت بالا ✅ عملکرد بهینه
خرید هاست ابری PostgreSQL لیارا

ایجاد یک unique index

با نگاهی به مستندات PostgreSQL، به‌نظر می‌رسد که می‌توان با یک دستور ساده، یک ایندکس منحصربه‌فرد ایجاد کرد:

CREATE UNIQUE INDEX reference_count_unique_index 
	ON reference_count (document_id, object_id);

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

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

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

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

ایجاد ایندکس یکتا به‌صورت هم‌زمان

این روش برخلاف حالت معمول، جدول را قفل نمی‌کند و در نتیجه باعث افزایش تاخیر در پاسخ‌دهی یا ایجاد بن‌بست نمی‌شود. به همین دلیل برای محیط‌های واقعی (Production) بسیار مناسب و کاربردی است. البته ساخت ایندکس به‌صورت هم‌زمان نیز بدون محدودیت نیست. در طول اجرای این عملیات، PostgreSQL، یک قفل سبک‌تر به نام SHARE UPDATE EXCLUSIVE روی جداول اعمال می‌کند. اگرچه این قفل، مانع خواندن و نوشتن داده‌ها نمی‌شود، اما اجازه نمی‌دهد هم‌زمان تغییری در ساختار جدول ایجاد شود.

برای مثال:

CREATE UNIQUE INDEX CONCURRENTLY reference_count_unique_index 
	ON reference_count (document_id, object_id);

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

ALTER TABLE reference_count
	ADD CONSTRAINT reference_count_unique UNIQUE 
	USING INDEX reference_count_unique_index;

ERROR: [FAILED] index "reference_count_unique_index" is not valid

همان‌طور که در کد بالا مشاهده می‌کنید،شما تلاش می‌کنید که یک محدودیت یکتا (UNIQUE constraint) به جدول reference_count اضافه کنید و از ایندکس موجود به نام reference_count_unique_index استفاده کنید. اما اروری که دریافت می‌کنید به این معنی است که ایندکس reference_count_unique_index به‌عنوان ایندکس معتبر شناخته نمی‌شود.

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

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

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

اولین مورد، بن‌بست‌ها (deadlocks) هستند؛ این بن‌بست‌ها می‌توانند در هرمرحله از مهاجرت اتفاق بیافتند و باعث متوقف شدن عملیات شوند.

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

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

نحوه استقرار یک برنامه NestJS با Postgres در سرور مجازی اوبونتو را در مقاله زیر مطالعه کنید.
استقرار یک برنامه NestJS با Postgres

جلوگیری از نقض محدودیت یکتایی داده‌ها

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

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

آموزش کامل بکاپ گیری از PostgreSQL در اوبونتو 24.04 را در مقاله زیر بخوانید.
بکاپ گیری از PostgreSQL 

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

اگر هیچ‌کدام از روش‌های قبلی جواب نداد، می‌توانید با اجرای دستی کوئری‌ زیر، ایندکس را مجبور کنید که دوباره محاسبه و بازسازی شود:

REINDEX INDEX [CONCURRENTLY] reference_count_unique_index;

همچنین شما می‌توانید، این کوئری را در ساعات کم‌ترافیک اجرا کنید و جدول را به‌صورت هم‌زمان دوباره ایندکس کنید؛ البته این‌کار همچنان قفل نوشتاری را خواهد گرفت و ممکن است روی تجربه کاربران تاثیر بگذارد. خوشبختانه PostgreSQL از بازسازی هم‌زمان ایندکس (concurrent reindexing) هم پشتیبانی می‌کند. با اینکه این روش کند است اما در بعضی شرایط تنها گزینه‌ای است که ایمن و قابل اتکا است.

برای جزئیات بیشتر درباره‌ی reindex به‌صورت هم‌زمان، می‌توانید به مستندات رسمی PostgreSQL مراجعه کنید.

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

SELECT relname 
FROM 
	pg_class, pg_index 
WHERE 
	pg_index.indisvalid = false 
	AND pg_index.indexrelid = pg_class.oid;

-- Returns the list of invalid indexes, in this case:
-- 'reference_count_unique_index'

اکنون، شما می‌دانید چطور بدون به خطر انداختن عملکرد سیستم، یک ایندکس یکتا بسازید.

نحوه استفاده از Fast API با دیتابیس رابطه ای در سرور مجازی اوبونتو Ubuntu را در مقاله زیر بخوانید.
استفاده از Fast API با دیتابیس رابطه ای

جمع‌بندی

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

سوالات متداول

چرا باید ایندکس یکتای جدید به جدول اضافه کنم؟

ایندکس یکتا به شما کمک می‌کند تا از تکراری بودن داده‌ها جلوگیری کنید و عملکرد کوئری‌ها را بهبود دهید.

آیا ایندکس یکتا به‌طور خودکار از داده‌های تکراری جلوگیری می‌کند؟

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

آیا می‌توانم ایندکس یکتای جدید را به‌صورت هم‌زمان روی جدول‌های بزرگ ایجاد کنم؟

بله، شما می‌توانید از روش ساخت ایندکس به‌صورت هم‌زمان (concurrently) استفاده کنید.