B-tree বনাম GIN বনাম GiST ইনডেক্স: একটি ব্যবহারিক PostgreSQL গাইড

আপনি আসলে কী বেছে নেন যখন ইনডেক্স বাছেন

অধিকাংশ PostgreSQL ইনডেক্স সমস্যার শুরু একই রকম: একটি লিস্ট ভিউ 1,000 সারিতে দ্রুত মনে হয়, কিন্তু 1,000,000 এ ধীর হয়ে পড়ে। অথবা একটি সার্চ বক্স যেটা টেস্টে ঠিক ছিল, প্রোডাকশনে সেকেন্ড-দীর্ঘ দেরি করে। তখন লোভ করে প্রশ্ন করা হয়, “কোন ইনডেক্সটি সেরা?” একটি ভাল প্রশ্ন হলো: “এই স্ক্রীন ডাটাবেসকে কী করতে বলছে?”

একই টেবিলের জন্য বিভিন্ন স্ক্রীনে ভিন্ন ইনডেক্স প্রয়োজন হতে পারে কারণ স্ক্রীনগুলো ডেটা পড়ে ভিন্নভাবে। এক ভিউ একটি সিঙ্গেল স্ট্যাটাস দিয়ে ফিল্টার করে এবং created_at দিয়ে সোর্ট করে। অন্যটি ফুল-টেক্সট সার্চ করে। আরেকটি চেক করে JSON ফিল্ড কি কী আছে। আরেকটি ম্যাপের একটি পয়েন্টের কাছাকাছি আইটেম খোঁজে। এগুলো আলাদা অ্যাকসেস প্যাটার্ন—তাই একটাই ইনডেক্স সবখানেই জিতবে না।

ইনডেক্স বাছার সময় আপনি যা বেছে নিচ্ছেন তা হলো: অ্যাপ ডেটা কীভাবে অ্যাক্সেস করে। আপনি মূলত এক্স্যাক্ট ম্যাচ, রেঞ্জ, এবং সর্টিং করছেন? নথি বা অ্যারে ভিতরে সার্চ করছেন? আপনি জিজ্ঞাসা করছেন “এই লোকেশনটির কাছে কী আছে” না “এই রেঞ্জ ওভারল্যাপ করে কি”? উত্তর নির্ধারণ করে যে B-tree, GIN, না GiST কোনটা উপযুক্ত।

সহজ ভাষায় B-tree, GIN, এবং GiST

ইনডেক্স বাছাই কলামের টাইপের চেয়ে বেশি নির্ভর করে আপনার কুয়েরিগুলোর উপর। PostgreSQL অপারেটর–=, >, @>, বা @@—ভিত্তিকভাবে ইনডেক্স বেছে নেয়, কলাম টাইপের উপর নয়। এ কারণেই একই ফিল্ড বিভিন্ন স্ক্রীনে ভিন্ন ইনডেক্স চাইতে পারে।

B-tree: অর্ডারড লুকআপে দ্রুত

B-tree ডিফল্ট এবং সবচেয়ে সাধারণ। এটি তখন জ্বলে যখন আপনি এক্স্যাক্ট ভ্যালু দিয়ে ফিল্টার করেন, রেঞ্জ দিয়ে ফিল্টার করেন, বা নির্দিষ্ট অর্ডারে রেজাল্ট চান।

সাধারণ উদাহরণ: একটি অ্যাডমিন লিস্ট স্ট্যাটাস দিয়ে ফিল্টার করে এবং created_at অনুযায়ী সাজায়। (status, created_at) উপর B-tree ইনডেক্স ফিল্টার এবং সোর্ট উভয়েরই সাহায্য করতে পারে। B-tree ইউনিক কনস্ট্রেন্টের জন্যও সাধারণ টুল।

GIN: প্রতিটি সারিতে অনেক সার্চেবল কী থাকলে দ্রুত

GIN “এই সারি কি এই টার্ম/ভ্যালু ধারণ করে?” ধরনের প্রশ্নের জন্য বানানো, যেখানে এক সারি অনেক কী-এ ম্যাচ করতে পারে। সাধারণ উদাহরণগুলো হল ফুল-টেক্সট সার্চ (ডকুমেন্টে শব্দ আছে কিনা) এবং JSONB/অ্যারে মেম্বারশিপ (JSON কী/ভ্যালু আছে কিনা)।

একটি কাস্টমার রেকর্ডে JSONB preferences অবজেক্ট আছে এবং একটি স্ক্রীন ব্যবহারকারীদের ফিল্টার করে যেখানে preferences { "newsletter": true } আছে—এটাই একটি GIN-শৈলীর লুকআপ।

GiST: রেঞ্জ, জিও, এবং সিমিলারিটির জন্য ফ্লেক্সিবল

GiST এমন ডাটাটাইপকে সমর্থন করে যা সরল অর্ডারে ফিট করে না। এটি রেঞ্জ (ওভারল্যাপ, কনটেইন), জিওমেট্রি/জিওগ্রাফিক কুয়েরি (near, within), এবং কিছু সিমিলারিটি সার্চের জন্য প্রাকৃতিক ফিট।

B-tree বনাম GIN বনাম GiST বেছে নেবার সময় প্রথমে আপনার ব্যস্ত স্ক্রীনগুলো কোন অপারেটর ব্যবহার করে তা লিখে নিন। সঠিক ইনডেক্স সাধারণত সেই তালিকা দেখে স্পষ্ট হয়ে যায়।

সাধারণ স্ক্রীনগুলোর জন্য সিদ্ধান্ত টেবিল (ফিল্টার, সার্চ, JSON, জিও)

অধিকাংশ অ্যাপ কেবল কয়েকটি ইনডেক্স প্যাটার্নই প্রয়োজন। কলা হল স্ক্রীনের আচরণকে আপনার কুয়েরির অপারেটরের সাথে মিলানো।

যখন endpoints আলাদা হয়, দুটি ইনডেক্স একসাথে থাকতে পারে। উদাহরণ: একটি অ্যাডমিন লিস্ট (tenant_id, created_at) উপর B-tree চাইতে পারে দ্রুত সোর্টের জন্য, আর একটি সার্চ পেজ @@ এর জন্য GIN চাইবে। যদি উভয় কুয়েরি প্রচলিত হয়, উভয় রাখুন।

নিশ্চিত না হলে প্রথমে অপারেটরগুলো দেখুন। ইনডেক্স কার্যকর যখন ডাটাবেস খুব বড় অংশ স্কিপ করে পাড়ে।

ফিল্টার ও সোর্টিং: যেখানে সাধারণত B-tree মাতিয়ে দেয়

প্রতিদিনের বেশিরভাগ স্ক্রীনের জন্য B-tree নির্ভরযোগ্য পছন্দ। আপনার কুয়েরি যদি “কোন কলাম একটি মানের সমান, সম্ভবত সেগুলো সোর্ট করা, তারপর পেজ দেখানো” ধরনের হয়, তাহলে B-tree সাধারণত প্রথমে চেষ্টা করার মতো।

Equality ফিল্টার ক্লাসিক কেস। status, user_id, account_id, type, বা tenant_id এর মতো কলামগুলো ড্যাশবোর্ড ও অ্যাডমিন প্যানেলে বারবার আসে। B-tree ইনডেক্স সরাসরি মিল করা ভ্যালুতে যেতে পারে।

রেঞ্জ ফিল্টারও B-tree-কে ভাল খায়। সময় বা সংখ্যার রেঞ্জে ফিল্টার করলে ordered structure সাহায্য করে: created_at >= ..., price BETWEEN ..., id > ...। UI-র “Last 7 days” বা “$50 to $100” ধাঁচে B-tree ঠিক কাজ করে।

সোর্টিং ও পেজিনেশন এমন জায়গা যেখানে B-tree আপনাকে অনেক কাজ বাঁচাতে পারে। যদি ইনডেক্সের অর্ডার আপনার ORDER BY-এর সঙ্গে মিলেছে, PostgreSQL প্রায়ই সারি গুলোকে আগেই সাজানো অবস্থায় রিটার্ন করতে পারে, বড় সেট মেমোরিতে সাজানোর বদলে।

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Composite ইনডেক্সগুলোর একটি সহজ নিয়ম আছে: PostgreSQL কেবল ইনডেক্সের লিডিং অংশটি দক্ষভাবে ব্যবহার করতে পারে। ভাবুন “বাম থেকে ডান”। (user_id, status, created_at)-এ user_id দ্বারা ফিল্টার করা (এবং ঐচ্ছিকভাবে status) করলে সুবিধা হবে। কেবল status দিয়ে ফিল্টার করলে সাধারণত কম লাভ হবে।

Partial ইনডেক্স তখনই বড় সুবিধা যখন আপনার স্ক্রীন কেবল ডাটা-র একটি স্লাইস দেখতে চায়। সাধারণ স্লাইসগুলো: “only active rows”, “not soft-deleted”, অথবা “recent activity.” এগুলো ইনডেক্সকে ছোট ও দ্রুত রাখে।

উচ্চ-কার্ডিনালিটি কলাম ও অতিরিক্ত ইনডেক্সের খরচ

উচ্চ-কার্ডিনালিটি কলানগুলো অনেক ইউনিক মান রাখে, যেমন user_id, order_id, email, বা সেকেন্ড পর্যন্ত created_at। এখানে ইনডেক্স খুব কাজের হয় কারণ ফিল্টার টেবিলকে খুব ছোট অংশে নামিয়ে আনে।

কম-কার্ডিনালিটি কলাম—বুলিয়ান বা ছোট এনাম—এর বিপরীত। এইগুলোর উপর ইনডেক্স প্রায়ই হতাশ করে কারণ প্রতিটি মান অনেক সারি মেলে। PostgreSQL ঠিকই সিকুয়েন্সিয়াল স্ক্যান বেছে নিতে পারে, কারণ ইনডেক্সে জাম্প করে হলেও অনেক টেবিল পেজ পড়তে হবে।

আরেকটি সূক্ষ্ম খরচ হলো রো ফেচ করা। ইনডেক্স যদি ম্যাচিং আইডি দ্রুত খুঁজে দেয়, ডাটাবেসকে টেবিলও ভিজিট করতে হতে পারে বাকী কলামগুলো পেতে। যদি আপনার কুয়েরি কেবল কয়েকটি ফিল্ড চান, একটি কভারিং ইনডেক্স সাহায্য করতে পারে, কিন্তু তা ইনডেক্সকে বড় ও মেইনটেইন করতে ব্যয়বহুল করে।

প্রতিটি অতিরিক্ত ইনডেক্সের লেখার খরচ আছে। ইনসার্ট হলে প্রতিটি ইনডেক্সে লেখতে হবে। ইনডেক্স করা কলাম বদলে গেলে আপডেটগুলোকেও ইনডেক্স আপডেট করতে হবে। “হতে পারে” হিসেবে ইনডেক্স যোগ করলে পুরো অ্যাপ ধীর হতে পারে, কেবল একটি স্ক্রীন নয়।

বাস্তবধর্মী নির্দেশ:

• প্রতিটি ব্যস্ত টেবিলের জন্য 1–2 ওয়ার্কহার্স ইনডেক্স দিয়ে শুরু করুন, বাস্তব ফিল্টার ও সোর্টের উপর ভিত্তি করে।
• WHERE ও ORDER BY-এ ব্যবহৃত উচ্চ-কার্ডিনালিটি কলামকে অগ্রাধিকার দিন।
• বুলিয়ান ও ছোট এনাম ইনডেক্স করার ক্ষেত্রে সাবধান থাকুন যদি না তারা অন্য সিলেকটিভ কলামের সঙ্গে মিলিত হয়।
• কোন ইনডেক্স যোগ করবেন তা নির্দিষ্টভাবে বলে দিতে পারেন এমন কুয়েরি না থাকলে যোগ করবেন না।

উদাহরণ: সাপোর্ট টিকিট লিস্ট যেখানে assignee_id (উচ্চ-কার্ডিনালিটি) দিয়ে ফিল্টার করলে ইনডেক্স উপকারে আনে, কিন্তু is_archived = false একাই প্রায়ই কাজে আসে না।

সার্চ স্ক্রীন: ফুল-টেক্সট, প্রিফিক্স, এবং “কনটেইনস”

সার্চ বক্সগুলো সহজ দেখালেও ব্যবহারকারী বেশি আশা করে: একাধিক শব্দ, ভিন্ন শব্দরূপ, এবং যুক্তিযুক্ত র‍্যাঙ্কিং। PostgreSQL-এ সেটা সাধারণত ফুল-টেক্সট সার্চ: আপনি একটি tsvector (প্রসেসড টেক্সট) স্টোর করেন এবং সেটিকে tsquery-র সঙ্গে কুয়েরি করেন (ইউজার কী টাইপ করেছে সেটা টার্মগুলোতে ভাঙা)।

ফুল-টেক্সট সার্চের জন্য GIN সাধারণ ডিফল্ট কারণ এটি দ্রুত “এই ডকুমেন্টে এই টার্মগুলো আছে কি?” জিজ্ঞেসের উত্তর দেয়। ট্রেডঅফ হল লেখার খরচ বেশি: ইনসার্ট/আপডেটে ওজন বাড়ে।

GiST ও ফুল-টেক্সট-এ কাজ করতে পারে। এটি প্রায়ই ছোট এবং আপডেট করতে কম খরচ করে, কিন্তু রিডে সাধারণত GIN-এ ধীরে। আপনার ডাটা যদি বারবার বদলে, যেমন ইভেন্ট-স্টাইল টেবিল, তাহলে এই রিড-রাইট ব্যালান্স গুরুত্বপূর্ণ।

প্রিফিক্স সার্চ ফুল-টেক্সট নয়

প্রিফিক্স সার্চ মানে “starts with”, যেমন ইমেইল প্রিফিক্স দিয়ে গ্রাহক খোঁজা। এটা ফুল-টেক্সট সার্চের উদ্দেশ্যে নয়। প্রিফিক্স প্যাটার্নের জন্য B-tree ইনডেক্স সাহায্য করতে পারে (সঠিক operator class-সহ) কারণ এটা স্ট্রিং অর্ডারের সঙ্গে মেলে।

“Contains” সার্চ যেমন ILIKE '%error%'-এ B-tree সাধারণত সাহায্য করে না। সেখানে ট্রিগ্রাম ইনডেক্সিং বা আলাদা সার্চ পন্থা প্রাসঙ্গিক।

যখন ব্যবহারকারী সার্চের পাশাপাশি ফিল্টার চান

বেশিরভাগ বাস্তব স্ক্রীন সার্চের সঙ্গে ফিল্টারও করে: status, assignee, date range, tenant ইত্যাদি। একটি ব্যবহারিক সেটআপ হল:

• tsvector কলামের জন্য GIN (কখনো GiST)।
• সবচেয়ে সিলেকটিভ ফিল্টারের জন্য B-tree (যেমন account_id, status, created_at)।
• “কম রাখুন” নীতি—একাধিক ইনডেক্স লেখাকে ধীর করে দেয়।

উদাহরণ: সাপোর্ট টিকিট স্ক্রীন যা “refund delayed” সার্চ করে এবং status = 'open' ও নির্দিষ্ট account_id দিয়ে ফিল্টার করে। ফুল-টেক্সট আপনাকে প্রাসঙ্গিক সারিগুলো দেবে, আর B-tree PostgreSQL-কে সঠিক অ্যাকাউন্ট ও স্ট্যাটাস দ্রুত সংকুচিত করতে সাহায্য করবে।

JSONB ফিল্ড: GIN বনাম টার্গেটেড B-tree কীভাবে নির্বাচন করবেন

JSONB নমনীয় ডেটার জন্য দারুণ, কিন্তু যদি আপনি এটাকে সাধারণ কলামের মতো ব্যবহার করেন তা হলে ধীরে যাওয়া কুয়েরি হতে পারে। মূল সিদ্ধান্ত সহজ: আপনি কি “এই JSON-এ যেকোনো জায়গায় খোঁজ করছেন” না আপনি কি কয়েকটি নির্দিষ্ট পাথ নিয়মিত ফিল্টার করেন?

metadata @> '{"plan":"pro"}' মতো কনটেইনমেন্ট কুয়েরির জন্য GIN সাধারণত প্রথম পছন্দ। এটি “এই ডকুমেন্টে এই শেপ আছে কি?” জিজ্ঞাসার জন্য বানানো এবং ?, ?|, ?& ধরনের কী-এক্সিস্টেন্স চেকও সমর্থন করে।

আপনার অ্যাপ যদি মূলত এক বা দুইটি JSON ফিল্ড দিয়ে ফিল্টার করে, তখন টার্গেটেড B-tree expression ইনডেক্স প্রায়ই দ্রুত ও ছোট হয়। এটা সোর্টিং বা সংখ্যাগত তুলনা লাগলে সাহায্য করে।

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

সাধারণ নিয়ম:

• যখন ব্যবহারকারী বিভিন্ন কী, ট্যাগ বা নেস্টেড স্ট্রাকচারে সার্চ করেন, তখন GIN ব্যবহার করুন।
• যখন ব্যবহারকারী কয়েকটি স্থির পাথ নিয়মিত ফিল্টার করে, তখন B-tree expression ইনডেক্স ব্যবহার করুন।
• বাস্তব স্ক্রীনগুলোতে যা দেখা যায় সেটাই ইনডেক্স করুন, সবকিছু নয়।
• যদি পারফরম্যান্স কয়েকটি JSON কী-র ওপর নির্ভর করে, সেগুলো বাস্তব কলামে উত্তোলনের কথা বিবেচনা করুন।

উদাহরণ: সাপোর্ট স্ক্রীন যদি metadata->>'priority' দিয়ে ফিল্টার করে এবং created_at দিয়ে সোর্ট করে, তাহলে JSON priority পাথটি এবং created_at কলাম উভয়ের ইনডেক্স রাখুন। যদি ব্যবহারকারী ট্যাগ বা নেস্টেড অ্যাট্রিবিউটও সার্চ করে, তখন বিস্তৃত GIN ইনডেক্স বিবেচনা করুন।

জিও ও রেঞ্জ কুয়েরি: GiST যে জায়গায় ভাল

জিও ও রেঞ্জ স্ক্রীনে GiST প্রায়ই স্পষ্টভাবে উপযুক্ত। GiST “এটি ওভারল্যাপ করে, কনটেইন করে, বা কাছাকাছি আছে কি?” ধাঁচের প্রশ্ন দ্রুত করে—বিরোধপূর্ণ অর্ডারের বদলে।

জিও ডেটা সাধারণত পয়েন্ট (স্টোর লোকেশন), লাইন (রুট), বা পলিগন (ডেলিভারি জোন)। সাধারণ স্ক্রীন: “স্টোর আমার নিকটে”, “10 কিমি ভেতর চাকরি”, “এই ম্যাপ বক্সের ভেতর আইটেম দেখাও”, বা “এই ঠিকানা আমাদের সার্ভিস এলাকায় আছে কি?” GiST ইনডেক্স (PostGIS geometry/geography টাইপের মাধ্যমে) স্প্যাশিয়াল অপারেটরগুলোকে দ্রুত করে যাতে ডাটাবেস বেশিরভাগ সারি স্কিপ করতে পারে।

রেঞ্জগুলোও অনুরূপ। PostgreSQL-এ daterange ও int4range আছে, এবং সাধারণ প্রশ্ন হচ্ছে ওভারল্যাপ: “এই বুকিং কোলাইড করে কি?” GiST ওভারল্যাপ ও কনটেইনমেন্ট অপারেটরগুলো দক্ষভাবে সমর্থন করে, তাই ক্যালেন্ডার, শিডিউলিং এবং এভেইলেবিলিটি চেকে এটি প্রচলিত।

B-tree এখনো জিও-ধাঁচের স্ক্রীনে গুরুত্বপূর্ণ হতে পারে। অনেক পেজ প্রথমে tenant, status বা time দিয়ে ফিল্টার করে, তারপর স্প্যাশিয়াল কন্ডিশন, তারপর সোর্ট করে। উদাহরণ: “শুধু আমার কোম্পানির ডেলিভারি, গত 7 দিনে, নিকটতম প্রথম।” GiST স্প্যাশিয়াল অংশ হ্যান্ডেল করে; B-tree সিলেকটিভ ফিল্টার ও সোর্টে সাহায্য করে।

ধাপে ধাপে ইনডেক্স নির্বাচন কিভাবে করবেন

ইনডেক্স পছন্দ মূলত অপারেটরের ব্যাপার—কলামের নাম নয়। একই কলাম ভিন্ন ইনডেক্স চাইতে পারে যদি আপনি =, >, LIKE 'prefix%', ফুল-টেক্সট, JSON কনটেইনমেন্ট, বা জিও ডিস্ট্যান্স ব্যবহার করেন।

কুয়েরিটা চেকলিস্টের মতো পড়ুন: WHERE কোন সারি যোগ্য করে, JOIN টেবিলগুলো কীভাবে কানেক্ট করে, ORDER BY আউটপুট অর্ডার নির্ধারণ করে, আর LIMIT বলে কত সারি দরকার। সবচেয়ে ভাল ইনডেক্স প্রায়ই সেইটি যা প্রথম 20 সারি দ্রুত খুঁজে দিতে পারে।

একটি সাধারণ প্রক্রিয়া যা বেশিরভাগ অ্যাপ স্ক্রীনের জন্য কাজ করে:

1. আপনার স্ক্রীন যে নির্দিষ্ট অপারেটরগুলো ব্যবহার করে তা লিখুন (উদাহরণ: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. সবচেয়ে সিলেকটিভ ফিল্টার বা ডিফল্ট সোর্ট মিলানো ইনডেক্স দিয়ে শুরু করুন। স্ক্রীন যদি created_at DESC দিয়ে সোর্ট করে, সেখান থেকেই শুরু করুন।
3. composite ইনডেক্স তখন যোগ করুন যখন একই ফিল্টারগুলো একসাথে সবদা ব্যবহার হয়। equality কলামগুলো প্রথমে রাখুন, তারপর range কলাম, তারপর sort কি।
4. partial index ব্যবহার করুন যখন আপনি সবসময় একটি সাবসেটে ফিল্টার করেন (উদাহরণ: শুধু status = 'open')। expression index ব্যবহার করুন যখন আপনি ক্যালকুলেটেড ভ্যালু কুয়েরি করেন (উদাহরণ: case-insensitive lookup-এ lower(email))।
5. EXPLAIN ANALYZE দিয়ে যাচাই করুন। যদি এটি এক্সিকিউশন টাইম মোটামুটি কমায় এবং পড়া রো কমায়, রাখুন।

কনক্রিট উদাহরণ: একটি সাপোর্ট ড্যাশবোর্ড যা status দিয়ে ফিল্টার ও newest দ্বারা সোর্ট করে—(status, created_at DESC) উপর B-tree শক্তিশালী প্রথম চেষ্টায়। যদি একই স্ক্রীন JSONB ফ্ল্যাগ meta @> '{"vip": true}' দিয়ে ফিল্টার করে, সেটা আলাদা অপারেটর—সাধারণত আলাদা JSON-ফোকাসড ইনডেক্স প্রয়োজন।

সাধারণ ভুলগুলো যা সময় নষ্ট করে (এবং রাইট ধীর করে)

একটি সাধারণ ক্লাসিক ভুল হল ভুল অপারেটরের জন্য “সঠিক” ইনডেক্স টাইপ বাছাই করা। PostgreSQL কেবল তখন ইনডেক্স ব্যবহার করে যখন কুয়েরি সেই ইনডেক্স কি উত্তরের প্রশ্নের সঙ্গে মিলায়। যদি আপনার অ্যাপ ILIKE '%term%' ব্যবহার করে, টেইক্সট কলামের উপর একটি সাধারণ B-tree ইনডেক্স ব্যবহার হবে না—এবং আপনি তখনো টেবিল স্ক্যান করবেন।

আরেকটি ফাঁদ হল বিশাল multi-column ইনডেক্স বানানো “কেসে”—এসব দেখতে নিরাপদ মনে হয়, কিন্তু রক্ষণাবেক্ষণে ব্যয়বহুল এবং প্রায়ই বাস্তব কুয়েরি প্যাটার্নের সঙ্গে মেলে না। যদি ইনডেক্সের বামদিকে থাকা কলামগুলো ফিল্টারে ব্যবহার না করা হয়, ইনডেক্সের বাকি অংশগুলো সাহায্য নাও করতে পারে।

কম-সিলেকটিভ কলামেও খুব সহজে ওভার-ইনডেক্স করা যায়। is_active বা কিছুকটি ভ্যালুর স্ট্যাটাসের উপর B-tree প্রায় নৈরাশ্যকর হতে পারে—এমন অবস্থায় partial index ব্যবহার করা ভাল।

JSONB নিজেই কিছু গটচা নিয়ে আসে। বিস্তৃত GIN ইনডেক্স নমনীয় ফিল্টারের জন্য ভাল হতে পারে, কিন্তু অনেক JSONB পাথ-চেক expression ইনডেক্সে দ্রুত হয়। যদি আপনার স্ক্রীন সবসময় payload->>'customer_id' দিয়ে ফিল্টার করে, ঐ expression ইনডেক্স পুরো ডকুমেন্ট ইনডেক্স করার চেয়ে ছোট ও দ্রুত হবে।

অবশেষে, প্রতিটি অতিরিক্ত ইনডেক্স লেখাকে ট্যাক্স করে। ঘনশ: আপডেট বা ইনসার্ট অনেক ইনডেক্স আপডেট করাতে পারে।

ইনডেক্স যোগ করার আগে থামুন এবং চেক করুন:

• ইনডেক্স কি আপনার কুয়েরির নির্দিষ্ট অপারেটরের সাথে মেলে?
• আপনি কি একটি বড় multi-column ইনডেক্সকে এক বা দুইটি ফোকাসড ইনডেক্স দিয়ে প্রতিস্থাপন করতে পারেন?
• এটি কি partial index হওয়া উচিত যাতে কম-সিলেকটিভ প্রবলেম এড়ানো যায়?
• JSONB-র জন্য, expression index কি এই স্ক্রীনের জন্য ভাল হবে?
• টেবিল কি এতটাই write-heavy যে ইনডেক্স খরচ পাঠানোর উপকারিতা ছাপিয়ে যায়?

একটি দ্রুত চেকলিস্ট নতুন ইনডেক্স যোগ করার আগে

নতুন ইনডেক্স তৈরি করার আগে অ্যাপ আসলে কী করে তা নির্দিষ্ট করুন। “Nice to have” ইনডেক্স প্রায়ই লেখাকে ধীর করে এবং বেশি স্টোরেজ নেয় কিন্তু খুব কম লাভ দেয়।

আপনার শীর্ষ তিনটি স্ক্রীন (বা API এন্ডপয়েন্ট) নিয়ে তাদের সঠিক কুয়েরি ধরুন: ফিল্টার, সোর্ট অর্ডার, এবং ইউজার কী টাইপ করে। অনেক “ইনডেক্স প্রবলেম” আসলে “অস্পষ্ট কুয়েরি প্রবলেম” যখন মানুষ B-tree বনাম GIN বনাম GiST নিয়ে বিতর্ক করে অপারেটর না বলে।

একটি সহজ চেকলিস্ট:

• 3টি বাস্তব স্ক্রীন বেছে নিন এবং তাদের সঠিক WHERE ও ORDER BY প্যাটার্ন লিখুন (দিক ও NULL হ্যান্ডলিংসহ)।
• অপারেটর টাইপ নিশ্চিত করুন: equality (=), range (>, BETWEEN), prefix, contains, overlap, বা distance।
• প্রতিটি সাধারণ স্ক্রীন প্যাটার্নে একটি ইনডেক্স নির্বাচন করুন, টেস্ট করুন, এবং কেবল সেইগুলো রাখুন যা সময় বা পড়া উল্লেখযোগ্যভাবে কমায়।
• যদি টেবিল write-heavy হয়, কড়া হন: অতিরিক্ত ইনডেক্স লেখার খরচ গুণিতকভাবে বাড়ে এবং vacuum প্রেসার বাড়ায়।
• ফিচার বদলালে পুনরায় পরীক্ষা করুন। নতুন ফিল্টার, নতুন ডিফল্ট সোর্ট, বা "starts with" থেকে "contains"-এ পরিবর্তন পুরনো ইনডেক্সকে অপ্রযোজ্য করে দিতে পারে।

উদাহরণ: একটি ড্যাশবোর্ডে নতুন ডিফল্ট সোর্ট last_activity DESC যোগ করলে যদি আপনি কেবল status-এ ইনডেক্স করে থাকেন, তাহলে ফিল্টার দ্রুত থাকতে পারে কিন্তু সোর্ট এখন অতিরিক্ত কাজ চাপিয়ে দেবে।

উদাহরণ: বাস্তব অ্যাপ স্ক্রীনগুলোকে সঠিক ইনডেক্সের সাথে ম্যাপ করা

একটি সিদ্ধান্ত টেবিল তখনই কার্যকর যখন আপনি এটাকে আপনার রিল-স্ক্রীনে ম্যাপ করতে পারেন। নিচে তিনটি সাধারণ স্ক্রীন ও তাদের উপযোগী ইনডেক্স দেওয়া হল।

এটি প্রয়োগ করার বাস্তব উপায় হলো UI থেকে শুরু করা:

• প্রতিটি কন্ট্রোল তালিকাভুক্ত করুন যা ফলাফল সংকুচিত করে (ফিল্টার)।
• ডিফল্ট সোর্ট অর্ডার নোট করুন।
• সার্চ আচরণ সম্পর্কে স্পষ্ট হন (ফুল-টেক্সট বনাম starts-with বনাম contains)।
• “বিশেষ” ক্ষেত্রগুলো চিহ্নিত করুন (JSONB, জিও, রেঞ্জ)।

পরবর্তী ধাপ: ইনডেক্সিংকে আপনার বিল্ড প্রসেসের অংশ বানান

ভাল ইনডেক্সগুলো আপনার স্ক্রীনগুলো অনুসরন করে: ব্যবহারকারীরা যেসব ফিল্টার ক্লিক করে, যেই সোর্ট তারা আশা করে, এবং তারা যে সার্চ বক্স ব্যবহার করে। ডেভেলপমেন্টে ইনডেক্সিংকে অভ্যাসে পরিণত করুন এতে ভবিষ্যতে বেশিরভাগ পারফরম্যান্স সারপ্রাইজ এড়ানো যাবে।

এটি রিপিটেবল রাখুন: প্রতিটি স্ক্রীনের 1–3 কুয়েরি নির্দিষ্ট করুন, সেইগুলোর জন্য সবচেয়ে ছোট ইনডেক্স যোগ করুন, বাস্তবধর্মী ডেটায় টেস্ট করুন, তারপর যা কাজ করে না তা রিমুভ করুন।

আপনি যদি একটি ইন্টারনাল টুল বা কাস্টমার পোর্টাল বানান, ইনডেক্স চাহিদা শুরুতেই পরিকল্পনা করুন কারণ এই ধরনের অ্যাপ প্রায়ই আরও ফিল্টার ও আরও লিস্ট স্ক্রীন যোগ করে। যদি আপনি AppMaster (appmaster.io) দিয়ে তৈরি করেন, প্রতিটি স্ক্রীনের ফিল্টার ও সোর্ট কনফিগারেশনকে একটি কনক্রিট কুয়েরি প্যাটার্ন হিসেবে বিবেচনা করুন এবং কেবল সেই ইনডেক্সগুলো যোগ করুন যা বাস্তবে ওই ক্লিকগুলোকে সমর্থন করে।