Database Query Optimization: Indexes, Execution Plans & Partitioning

ایک گمشدہ انڈیکس 2 ملی سیکنڈ کے استفسار کو 20-سیکنڈ کے ٹیبل اسکین میں بدل سکتا ہے۔ جیسا کہ آپ کا ڈیٹا بیس ہزاروں سے لاکھوں قطاروں تک بڑھتا ہے، ایک اصلاح شدہ اور غیر آپٹمائزڈ استفسار کے درمیان فرق ایک ریسپانسیو ایپلیکیشن اور بوجھ کے نیچے ختم ہونے والی درخواست کے درمیان فرق ہے۔ ڈیٹا بیس کی اصلاح آپ کے کسی بھی کارکردگی کے کام کے انجینئرنگ کے وقت پر سب سے زیادہ منافع فراہم کرتی ہے۔

اہم ٹیک ویز

• وضاحت کریں تجزیہ آپ کا سب سے طاقتور تشخیصی ٹول ہے -- کسی بھی چیز کو بہتر بنانے سے پہلے عملدرآمد کے منصوبوں کو پڑھنا سیکھیں
• انڈیکس کی اقسام کو حکمت عملی سے منتخب کریں: برابری اور رینج کے لیے B-tree، مکمل متن کے لیے GIN اور JSONB، فلٹر شدہ ذیلی سیٹوں کے لیے جزوی اشاریہ جات
• N+1 استفسارات ORM پر مبنی ایپلی کیشنز میں کارکردگی کا سب سے عام قاتل ہیں -- استفسار لاگنگ کے ساتھ ان کا جلد پتہ لگائیں۔
• جدول کی تقسیم ضروری ہو جاتی ہے جب میزیں 10-50 ملین قطاروں سے زیادہ ہوتی ہیں، استفسار کی منصوبہ بندی کے وقت کو کم کرتی ہے اور ڈیٹا لائف سائیکل مینجمنٹ کو فعال کرتی ہے۔

---

وضاحتی تجزیہ کے ساتھ عملدرآمد کے منصوبوں کو پڑھنا

کسی بھی استفسار کو بہتر بنانے سے پہلے، آپ کو یہ سمجھنا چاہیے کہ PostgreSQL فی الحال اسے کیسے انجام دیتا ہے۔ EXPLAIN NALYZE استفسار کو چلاتا ہے اور حقیقی وقت کے ڈیٹا کے ساتھ عمل درآمد کا اصل منصوبہ دکھاتا ہے۔

ایک بنیادی وضاحتی تجزیہ آؤٹ پٹ آپ کو منصوبہ ساز کی منتخب کردہ حکمت عملی، تخمینہ بمقابلہ اصل قطار کی گنتی، اور ہر قدم پر گزارا ہوا وقت دکھاتا ہے۔ توجہ مرکوز کرنے کے لئے کلیدی میٹرکس ہیں:

• Seq Scan -- ڈیٹا بیس ٹیبل کی ہر قطار کو پڑھتا ہے۔ چھوٹی میزوں کے لیے قابل قبول (10,000 قطاروں سے کم) لیکن بڑی میزوں کے لیے سرخ پرچم۔
• انڈیکس اسکین -- ڈیٹا بیس مماثل قطاروں کو مؤثر طریقے سے تلاش کرنے کے لیے انڈیکس کا استعمال کرتا ہے۔ یہ وہی ہے جو آپ بڑی میزوں پر فلٹر شدہ سوالات کے لیے چاہتے ہیں۔
• انڈیکس صرف اسکین -- ڈیٹا بیس ٹیبل کو چھوئے بغیر پوری طرح سے انڈیکس سے سوال کا جواب دیتا ہے۔ سب سے تیز اسکین قسم۔
• نیسٹڈ لوپ -- بیرونی ٹیبل میں ہر قطار میں ایک بار اندرونی میز کو اسکین کرکے ٹیبلز میں شامل ہوتا ہے۔ جب اندرونی اسکین انڈیکس کا استعمال کرتا ہے تو موثر۔
• Hash Join -- جوائن کے ایک طرف سے ایک ہیش ٹیبل بناتا ہے، پھر دوسرے کے ساتھ اس کی تحقیقات کرتا ہے۔ بڑے رزلٹ سیٹ کے لیے موثر۔
• ** ترتیب دیں** -- ایک واضح ترتیب والا مرحلہ، اکثر آرڈر کے لیے۔ ڈسک پر پھیلنے والی قسموں کو دیکھیں ("طریقہ ترتیب: بیرونی انضمام" کے ذریعہ اشارہ کیا گیا ہے)۔

کیا تلاش کرنا ہے۔

عمل درآمد کے منصوبے میں سب سے اہم اشارہ تخمینہ اور حقیقی قطاروں کے درمیان فرق ہے۔ جب PostgreSQL 10 قطاروں کا تخمینہ لگاتا ہے لیکن 100,000 تلاش کرتا ہے، تو اس نے غلط پلان کا انتخاب کیا۔ ایسا تب ہوتا ہے جب ٹیبل کے اعدادوشمار باسی ہوتے ہیں -- انہیں اپ ڈیٹ کرنے کے لیے ٹیبل پر ANALYZE چلائیں۔

بڑی میزوں پر ترتیب وار اسکینز، انڈیکس کے بغیر ترتیب، اور اندرونی میز پر ترتیب وار اسکینوں کے ساتھ نیسٹڈ لوپس دیکھیں۔ ان نمونوں میں سے ہر ایک گمشدہ اشاریہ یا ایک سوال کی نشاندہی کرتا ہے جسے دوبارہ لکھنے کی ضرورت ہے۔

---

انڈیکس کی اقسام اور انہیں کب استعمال کرنا ہے۔

PostgreSQL انڈیکس کی کئی اقسام پیش کرتا ہے، ہر ایک مختلف سوال کے نمونوں کے لیے موزوں ہے۔ صحیح قسم کا انتخاب کرنا بہت ضروری ہے -- ایک کالم پر ایک GIN انڈیکس جس کو صرف مساوات کی ضرورت ہوتی ہے وہ ذخیرہ کو ضائع کرتا ہے اور پڑھنے کو بہتر بنائے بغیر لکھنے کو سست کرتا ہے۔

بی ٹری انڈیکس

B-tree پہلے سے طے شدہ اور سب سے زیادہ ورسٹائل انڈیکس کی قسم ہے۔ یہ مساوات (=)، رینج (<, >, BETWEEN)، چھانٹنا (ORDER BY)، اور سابقہ ​​پیٹرن میچنگ (LIKE 'abc%') کی حمایت کرتا ہے۔ WHERE، JOIN، اور ORDER BY شقوں میں زیادہ تر کالموں کے لیے، B-tree انڈیکس صحیح انتخاب ہے۔

کمپوزٹ انڈیکس ایک بی ٹری میں متعدد کالموں کو یکجا کرتے ہیں۔ کالم آرڈر کی اہمیت: (status,created_at) پر انڈیکس اکیلے اسٹیٹس پر یا اسٹیٹس اور create_at دونوں پر سوالات کو فلٹر کرنے کی مؤثر طریقے سے حمایت کرتا ہے، لیکن اکیلے create_at پر نہیں۔ سب سے زیادہ منتخب کالم کو پہلے رکھیں اور رینج فلٹرنگ کے لیے استعمال ہونے والے کالم کو آخر میں رکھیں۔

GIN اشاریہ جات

GIN اشاریہ جات جامع اقدار کے اندر تلاش کرنے پر سبقت لے جاتے ہیں۔ وہ مکمل متن کی تلاش (tsvector کالم)، JSONB کنٹینمنٹ سوالات (@>،؟)، اور سرنی اوورلیپ سوالات (&&, @>) کے لیے ضروری ہیں۔ GIN اشاریہ جات B-tree اشاریہ جات کے مقابلے میں اپ ڈیٹ کرنے میں بڑے اور سست ہوتے ہیں، لہٰذا انہیں صرف وہاں استعمال کریں جہاں B-tree استفسار کا نمونہ پیش نہ کر سکے۔

JSONB کالموں کے لیے جو لچکدار اوصاف کو ذخیرہ کرتے ہیں، پورے کالم پر ایک GIN انڈیکس کلیدی پر مبنی کسی بھی سوال کو سپورٹ کرتا ہے۔ ان کالموں کے لیے جہاں آپ صرف مخصوص کلیدوں سے استفسار کرتے ہیں، جنریٹڈ کالم یا اظہار پر B-tree انڈیکس زیادہ موثر ہوتا ہے۔

جزوی اشاریہ جات

جزوی اشاریہ جات صرف WHERE شرط سے مماثل قطاروں کی فہرست بناتے ہیں۔ وہ میزوں کے لیے طاقتور ہیں جہاں سوالات ڈیٹا کے چھوٹے سب سیٹ کے لیے مستقل طور پر فلٹر ہوتے ہیں۔

مثال کے طور پر، اگر آپ کے آرڈرز کے ٹیبل میں 10 ملین قطاریں ہیں لیکن آپ تقریباً خصوصی طور پر فعال آرڈرز (ٹیبل کا 5%)، ایک جزوی انڈیکس (customer_id, created_at) پر استفسار کرتے ہیں جہاں اسٹیٹس = 'فعال' مکمل انڈیکس سے 20 گنا چھوٹا ہے اور آپ کے اصل سوالات کے لیے اتنا ہی تیز ہے۔

---

N+1 سوالات کا پتہ لگانا اور درست کرنا

N+1 استفسار کا مسئلہ ORMs استعمال کرنے والی ایپلیکیشنز میں کارکردگی کا سب سے عام مسئلہ ہے۔ یہ اس وقت ہوتا ہے جب کوڈ N ریکارڈز کی فہرست لوڈ کرتا ہے، پھر متعلقہ ڈیٹا لوڈ کرنے کے لیے فی ریکارڈ ایک اضافی استفسار کرتا ہے، جس کے نتیجے میں 1-2 کے بجائے N+1 کل سوالات ہوتے ہیں۔

N+1 سوالات کیسے ہوتے ہیں۔

ان کے گاہک کے ناموں کے ساتھ آرڈرز کی فہرست لوڈ کرنے پر غور کریں۔ ایک سادہ عمل درآمد آرڈرز کی فہرست (1 استفسار) کو لوڈ کرتا ہے، پھر ہر آرڈر کے لیے، گاہک (N سوالات) کو لوڈ کرتا ہے۔ 100 آرڈرز کے ساتھ، یہ 101 ڈیٹا بیس راؤنڈ ٹرپس تیار کرتا ہے۔ 1ms فی استفسار پر، یعنی 101ms -- لیکن کنکشن پول کے تنازعہ کے ساتھ ہم آہنگی کے بوجھ کے تحت، یہ آسانی سے 500ms یا اس سے زیادہ بن سکتا ہے۔

پتہ لگانے کے طریقے

1. سوال لاگنگ -- پوسٹگری ایس کیو ایل استفسار لاگنگ کو عارضی طور پر فعال کریں اور مختلف پیرامیٹر اقدار کے ساتھ بار بار ایک جیسے سوالات تلاش کریں۔
2. ORM-سطح کی لاگنگ -- بوندا باندی ORM، Prisma، اور TypeORM سبھی سوال لاگنگ کو سپورٹ کرتے ہیں جو ہر ایس کیو ایل سٹیٹمنٹ کو ظاہر کرتا ہے
3. APM ٹولز -- Datadog، New Relic، اور Sentry سوالات کو اختتامی نقطہ کے مطابق گروپ کر سکتے ہیں اور N+1 پیٹرن کو خود بخود نمایاں کر سکتے ہیں۔
4. pg_stat_statements -- یہ PostgreSQL ایکسٹینشن استفسار پر عمل درآمد کے اعداد و شمار کو ٹریک کرتا ہے اور کثرت سے عمل میں آنے والے ایک جیسے سوال کے سانچوں کو ظاہر کرتا ہے۔

N+1 سوالات کو درست کرنا

درستگی آپ کے ORM اور استفسار کے پیٹرن پر منحصر ہے:

• لوڈنگ کے شوقین -- ORM سے کہو کہ JOINs کا استعمال کرتے ہوئے ابتدائی استفسار میں متعلقہ ڈیٹا لوڈ کرے۔ بوندا باندی میں، استفسار کرنے والوں میں with اختیار استعمال کریں۔
• بیچ لوڈنگ -- تمام غیر ملکی کلیدی IDs کو جمع کریں، پھر متعلقہ ریکارڈز کو واحد WHERE id IN (...) استفسار میں لوڈ کریں۔ یہ ڈیٹا لوڈر پیٹرن ہے۔
• ڈی نارملائزیشن -- پڑھنے والے بھاری استعمال کے معاملات کے لیے، متعلقہ ڈیٹا کو براہ راست پیرنٹ ریکارڈ میں اسٹور کریں۔ پڑھنے کی کارکردگی کے لیے ٹریڈ تحریری پیچیدگی۔

---

دوبارہ لکھنے کی تکنیکوں سے استفسار کریں۔

بعض اوقات استفسار کو خود تنظیم نو کی ضرورت ہوتی ہے، نہ صرف بہتر اشاریہ جات۔

جوائن کنورژن کے لیے سبکیوری

باہم منسلک ذیلی سوالات بیرونی استفسار میں فی قطار میں ایک بار انجام دیتے ہیں۔ انہیں JOINs میں تبدیل کرنا PostgreSQL کو مزید موثر جوائن کی حکمت عملیوں کو استعمال کرنے کی اجازت دیتا ہے۔

ایک ذیلی سوال کے ساتھ آرڈرز کو منتخب کرنے کے بجائے جو فی گاہک کے آرڈر کی تازہ ترین تاریخ کو دیکھتا ہے، اسے اخذ کردہ ٹیبل یا ونڈو فنکشن کے ساتھ JOIN کے طور پر دوبارہ لکھیں۔ JOIN ورژن پوسٹگری ایس کیو ایل کو ڈیٹا کی تقسیم کی بنیاد پر نیسٹڈ لوپ، ہیش جوائن، اور ضم جوائن کے درمیان انتخاب کرنے کی اجازت دیتا ہے۔

کامن ٹیبل ایکسپریشنز (CTEs)

PostgreSQL 12 اور بعد میں، CTEs بذریعہ ڈیفالٹ ان لائن ہوتے ہیں، یعنی آپٹیمائزر ان میں پیشین گوئیاں ڈال سکتا ہے۔ کارکردگی کی باڑ کی فکر کیے بغیر پڑھنے کے قابل ہونے کے لیے CTEs کا استعمال کریں۔ ایسی صورتوں کے لیے جہاں آپ واضح طور پر مادّہ کاری چاہتے ہیں (مہنگے ذیلی سوالات کے دوبارہ عمل کو روکنے کے لیے)، MATERIALIZED کلیدی لفظ شامل کریں۔

ونڈو فنکشنز بمقابلہ گروپ BY

جب آپ کو تفصیلی قطاروں اور مجموعوں دونوں کی ضرورت ہوتی ہے، تو ونڈو فنکشنز خود شامل ہونے یا ذیلی سوالات کی ضرورت سے گریز کرتے ہیں۔ رننگ کل کا حساب لگانا، گروپوں کے اندر درجہ بندی کرنا، یا ہر قطار کا گروپ اوسط سے موازنہ کرنا ونڈو فنکشنز کے ساتھ متعلقہ ذیلی سوالات کے مقابلے میں زیادہ کارآمد ہیں۔

---

ٹیبل تقسیم کرنے کی حکمت عملی

جب میزیں 10-50 ملین قطاروں سے بڑھ جاتی ہیں، یہاں تک کہ اچھی طرح سے انڈیکس کیے گئے سوالات اشاریہ کی گہرائی، ویکیوم اوور ہیڈ، اور منصوبہ ساز کی پیچیدگی کی وجہ سے سست ہو جاتے ہیں۔ ایک منطقی ٹیبل انٹرفیس کو برقرار رکھتے ہوئے پارٹیشننگ ایک بڑی میز کو چھوٹے جسمانی حصوں میں تقسیم کرتی ہے۔

تقسیم کی اقسام

تاریخ کے لحاظ سے رینج کی تقسیم

سب سے عام نمونہ ٹائم اسٹیمپ کالم کے ذریعہ ماہانہ تقسیم کرنا ہے۔ ہر مہینے کا ڈیٹا اس کی اپنی تقسیم میں رہتا ہے۔ وہ سوالات جو تاریخ کے لحاظ سے فلٹر کرتے ہیں خود بخود صرف متعلقہ پارٹیشنز (پارٹیشن کی کٹائی) کو اسکین کرتے ہیں۔

وقت کی بنیاد پر تقسیم کے فوائد:

• استفسار کی کارکردگی -- حالیہ ڈیٹا کے سوالات صرف حالیہ پارٹیشنز کو اسکین کرتے ہیں۔
• مینٹیننس -- ویکیوم اور اینالائز چھوٹے پارٹیشنز پر تیزی سے چلتے ہیں
• ڈیٹا لائف سائیکل -- لاکھوں قطاروں کو حذف کرنے کے مقابلے پرانے پارٹیشنز کو چھوڑنا فوری ہے
• بیک اپ کی کارکردگی -- پوائنٹ ان ٹائم ریکوری کے لیے صرف حالیہ پارٹیشنز کا بیک اپ لیں

تقسیم کے تحفظات

تقسیم کرنے سے پیچیدگی بڑھ جاتی ہے۔ ہر استفسار میں پارٹیشن کی کو اپنی WHERE شق میں شامل کرنا چاہیے تاکہ پارٹیشن کی کٹائی کام کر سکے۔ منفرد رکاوٹوں میں تقسیم کی کلید شامل ہونی چاہیے۔ تقسیم شدہ جدولوں کا حوالہ دینے والی غیر ملکی چابیاں کی حدود ہیں۔ تقسیم صرف اس وقت شروع کریں جب آپ نے اس ٹیبل کے سائز کی پیمائش کی ہو جو کارکردگی میں کمی کا باعث بن رہی ہے۔

---

PostgreSQL کنفیگریشن ٹیوننگ

ڈیفالٹ پوسٹگری ایس کیو ایل کنفیگریشن قدامت پسند ہے، جسے کم سے کم ہارڈ ویئر پر چلانے کے لیے ڈیزائن کیا گیا ہے۔ پیداواری کام کے بوجھ کو کلیدی پیرامیٹرز کو ٹیون کرنے سے فائدہ ہوتا ہے۔

ان ترتیبات کو آپ کے مخصوص ہارڈ ویئر اور کام کے بوجھ کے لیے ٹیون کیا جانا چاہیے۔ pg_stat_bgwriter، pg_stat_activity، اور pg_stat_user_tables کی توثیق کرنے کے لیے نگرانی کریں کہ تبدیلیاں کارکردگی کو بہتر کرتی ہیں۔

---

اکثر پوچھے گئے سوالات

ایک ٹیبل میں کتنے انڈیکس ہونے چاہئیں؟

کوئی مقررہ حد نہیں ہے، لیکن ہر انڈیکس INSERT، UPDATE، اور DELETE آپریشنز کو سست کر دیتا ہے کیونکہ انڈیکس کو برقرار رکھنا ضروری ہے۔ انگوٹھے کا ایک اچھا اصول یہ ہے کہ ان کالموں کے لیے اشاریہ جات بنائیں جو آپ کے اکثر سوالات کی شقوں کے مطابق WHERE، JOIN ON، اور ORDER BY میں ظاہر ہوتے ہیں۔ غیر استعمال شدہ اشاریہ جات تلاش کرنے کے لیے pg_stat_user_indexes استعمال کریں جنہیں چھوڑا جا سکتا ہے۔

کیا مجھے کارکردگی کے لیے UUID یا انٹیجر پرائمری کیز استعمال کرنی چاہیے؟

انٹیجر پرائمری کیز (BIGSERIAL) جوائن کرنے اور انڈیکس کرنے کے لیے تیز ہیں کیونکہ وہ چھوٹی ہیں (8 بائٹس بمقابلہ 16 بائٹس) اور قدرتی طور پر ترتیب دی گئی ہیں۔ UUIDs ہم آہنگی کے بغیر عالمی انفرادیت فراہم کرتے ہیں، جو تقسیم شدہ نظاموں کے لیے اہم ہے۔ زیادہ تر ایپلی کیشنز کے لیے، بیرونی چہرے والے شناخت کنندگان کے لیے UUIDs اور اندرونی جوائن کرنے کے لیے انٹیجرز استعمال کریں۔

نقل پڑھنے کے لیے مجھے ایک ڈیٹا بیس سے کب تبدیل کرنا چاہیے؟

جب آپ کے پڑھے ہوئے کام کا بوجھ آپ کے ڈیٹا بیس کی صلاحیت کے 70-80% سے زیادہ ہو جائے، یا سوالات کی رپورٹنگ کرتے وقت وسائل کے لیے لین دین کے سوالات کا مقابلہ کریں۔ پڑھنے کی نقلیں پڑھنے کے بوجھ کو سنبھالتی ہیں جبکہ بنیادی توجہ لکھنے پر مرکوز ہوتی ہے۔ عام طور پر ایک عام ویب ایپلیکیشن کے لیے اس کی ضرورت 5,000-10,000 ایک ساتھ استعمال کنندگان پر ہوتی ہے۔

میں بغیر وقت کے پیداوار میں سست سوالات کو کیسے ہینڈل کروں؟

ٹیبل کو لاک کرنے سے بچنے کے لیے ہم آہنگ آپشن کے ساتھ اشاریہ جات بنائیں۔ سست ترین سوالات کی شناخت کے لیے pg_stat_statements استعمال کریں۔ خصوصیت کے جھنڈوں کے پیچھے استفسار کی اصلاح کو متعین کریں۔ اسکیما تبدیلیوں کے لیے جو میزوں کو دوبارہ لکھتے ہیں، pg_repack جیسے ٹولز کو لاک کیے بغیر ٹیبلز کو دوبارہ ترتیب دینے کے لیے استعمال کریں۔

---

آگے کیا ہے۔

ڈیٹا بیس کی اصلاح پلیٹ فارم کی کارکردگی کی بنیاد ہے۔ pg_stat_statements کو فعال کرکے شروع کریں، اپنے سست ترین سوالات کی نشاندہی کریں، اور وضاحتی تجزیہ کے ساتھ منظم طریقے سے ان پر کام کریں۔ گمشدہ اشاریہ جات شامل کریں، N+1 پیٹرن درست کریں، اور اپنے سب سے بڑے ٹیبلز کے لیے تقسیم پر غور کریں۔

کارکردگی کی وسیع تر تصویر کے لیے، اپنے کاروباری پلیٹ فارم کو اسٹارٹ اپ سے انٹرپرائز تک اسکیل کرنے پر ہماری ستون گائیڈ دیکھیں۔ اصلاح کی اگلی پرت کے بارے میں جاننے کے لیے، Redis، CDN، اور HTTP کیشنگ کے ساتھ کیشنگ کی حکمت عملی پر ہماری گائیڈ پڑھیں۔

ECOSIRE پوسٹگری ایس کیو ایل کے حمایت یافتہ پلیٹ فارمز بشمول Odoo ERP اور حسب ضرورت ایپلی کیشنز کے لیے ماہر ڈیٹا بیس کی اصلاح فراہم کرتا ہے۔ ڈیٹا بیس پرفارمنس آڈٹ کے لیے ہم سے رابطہ کریں۔

---

شائع کردہ بذریعہ ECOSIRE — کاروباروں کو Odoo ERP، Shopify eCommerce، اور OpenClaw AI میں AI سے چلنے والے حل کے ساتھ پیمانے میں مدد کرنا۔