PostgreSQL ইনডেক্সিং SaaS অ্যাপের জন্য: ফিল্টার, সার্চ, JSONB এবং অ্যারে মতো বাস্তব কুয়েরি শেপ ব্যবহার করে btree, GIN, GiST এর মধ্যে নির্বাচন করুন।

tenant_id = ?, status = 'active', created_at \u003e= ?, users.id = orders.user_id দিয়ে join, বা ORDER BY created_at DESC দিয়ে “latest first” দেখানো।\n\n### GIN এবং GiST: যখন একটি সারিতে অনেক সার্চযোগ্য মান থাকে\n\nGIN (Generalized Inverted Index) ভালো লাগে যখন একটা কলামে অনেক এলিমেন্ট থাকে এবং আপনার জিজ্ঞাসা হয়, “এটা কি X ধারণ করে?”—এটা JSONB কী, অ্যারে উপাদান বা ফুল-টেক্সট ভেক্টরের সঙ্গে সাধারণ।\n\nউদাহরণ শেপ: JSONB তে metadata @\u003e {'plan':'pro'}, tags @\u003e ARRAY['urgent'], বা to_tsvector(body) @@ plainto_tsquery('reset password')।\n\nGiST (Generalized Search Tree) বেশি মানায় দূরত্ব বা ওভারল্যাপ-স্টাইল প্রশ্নের সাথে, যেখানে মানগুলো রেঞ্জ বা শেইপের মতো আচরণ করে। এটি রেঞ্জ টাইপ, জ্যামিতিক ডেটা এবং কিছু “ক্লোজেস্ট ম্যাচ” সার্চে ব্যবহৃত হয়।\n\nএকটি ব্যবহারিক নির্বাচন বিধি:\n\n- যদি আপনি সাধারণ কলাম দিয়ে ফিল্টার বা sort করেন, B-tree দিয়ে শুরু করুন।\n- যদি আপনি containment বা membership চেক করেন, GIN দেখুন।\n- যদি আপনি জিজ্ঞাসা করেন “কীটা কাছাকাছি” বা “এটি কি ওভারল্যাপ করে?”, GiST প্রায়ই উপযুক্ত।\n- যদি একটি কুয়েরি দুর্লভ বা টেবিল ছোট হয়, নতুন ইনডেক্স লাগবে না।\n- যদি আপনি কুয়েরি শেপ বর্ণনা করতে না পারেন, প্রথমে মাপুন (EXPLAIN) তারপর বাড়ান।\n\nইনডেক্সগুলো পড়া দ্রুত করে, কিন্তু লেখার সময় এবং ডিস্ক খরচ বাড়ায়। SaaS-এ এই ট্রেড-অফ সবচেয়ে বেশি গুরুত্ব পায় হট টেবিলগুলোতে যেমন events, sessions, এবং activity logs।\n\n## ফিল্টার, সার্টিং, এবং পেজিনেশনের জন্য B-tree প্যাটার্নগুলি\n\nঅধিকাংশ SaaS তালিকা স্ক্রিন একই শেপ শেয়ার করে: একটি tenant boundary, কয়েকটি ফিল্টার, এবং একটি পূর্বানুমানযোগ্য sort। এখানে B-tree ইনডেক্স ডিফল্ট পছন্দ এবং সাধারণভাবে রক্ষণাবেক্ষণে সস্তা।\n\nএকটি সাধারণ প্যাটার্ন হলো WHERE tenant_id = ? প্লাস status = ?, user_id = ? এবং একটি time range যেমন created_at \u003e= ?। কম্পোজিট B-tree ইনডেক্সে equality ফিল্টারগুলো প্রথমে রাখুন (যেগুলো আপনি = দিয়ে মিলান), তারপর আপনি যে কলাম দিয়ে sort করতে চান সেটা রাখুন।\n\nকয়েকটি নিয়ম যা বেশিরভাগ অ্যাপে ভালো কাজ করে:\n\n- যদি প্রতিটি কুয়েরি tenant-স্কোপড হয় তাহলে tenant_id দিয়ে শুরু করুন।\n- তারপর = ফিল্টারগুলো রাখুন (সাধারণত status, user_id)।\n- ORDER BY কলামটি শেষ রাখুন (সাধারণত created_at বা id)।\n- তালিকা পেজ কভার করার জন্য INCLUDE ব্যবহার করুন যাতে key বড় না হয়।\n- পেজগুলি গভীর হলে offset এর পরিবর্তে seek (keyset) pagination পছন্দ করুন।\n\nএকটি বাস্তবশায়ী উদাহরণ: Tickets পেজ newest প্রথম দেখায়, status দিয়ে ফিল্টার করা।\n\n```sql-- Query SELECT id, status, created_at, title FROM tickets WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 50;
-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);
\n\nএই ইনডেক্সটি ফিল্টার এবং sort উভয়কেই সাপোর্ট করে, তাই Postgres বড় রেজাল্ট সেটকে আলাদা করে sort না করেই কাজ করতে পারে। `INCLUDE (title)` অংশটি তালিকা পেজকে কম টেবিল পেজ টাচ করে সাহায্য করে, একই সময়ে ইনডেক্স কী-গুলো ফিল্টারিং ও অর্ডারিং-এ ফোকাস রাখে।\n\nটাইম রেঞ্জের জন্য একই ধারণা প্রযোজ্য:\n\nsql
SELECT id, created_at
FROM events
WHERE tenant_id = $1
AND created_at \u003e= $2
AND created_at \u003c $3
ORDER BY created_at DESC
LIMIT 100;
CREATE INDEX events_tenant_created_at_idx ON events (tenant_id, created_at DESC); sql SELECT id, created_at FROM tickets WHERE tenant_id = $1 AND created_at \u003c $2 ORDER BY created_at DESC LIMIT 50; sql -- Query shape: containment SELECT id FROM accounts WHERE tenant_id = $1 AND metadata @\u003e '{"region":"eu","plan":"pro"}';
-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);
\n\nযদি আপনার JSON স্ট্রাকচার পূর্বানুমানযোগ্য এবং আপনি প্রায়শই top-level কী-গুলোর উপর `@\u003e` ব্যবহার করেন, `jsonb_path_ops` ছোট এবং দ্রুত হতে পারে, কিন্তু এটি কম অপারেটর টাইপ সাপোর্ট করে।\n\n### কখন এক্সপ্রেশন ইনডেক্স ভালো\n\nযদি আপনার UI বারবার একটি নির্দিষ্ট ফিল্ড (যেমন plan) দিয়ে ফিল্টার করে, সেই ফিল্ড উদ্ধার করে এটিতে ইনডেক্স করা প্রায়শই বড় GIN-এর চেয়ে দ্রুত এবং সস্তা।\n\nsql
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata-\u003e\u003e'plan' = 'pro';
CREATE INDEX accounts_plan_expr
ON accounts ((metadata-\u003e\u003e'plan'));
\n\nএকটি ব্যবহারিক নিয়ম: JSONB রাখুন নমনীয়, কম ব্যবহৃত অ্যাট্রিবিউটগুলির জন্য, কিন্তু স্থিতিশীল, উচ্চ-ব্যবহারের ফিল্ডগুলো (plan, status, created_at) সত্যিকারের কলামে তুলে নিন। আপনি যদি দ্রুত প্রোটোটাইপ বানাচ্ছেন, একটি সহজ স্কিমা টুইক হতে পারে একবার আপনি দেখেন কোন ফিল্টারগুলো প্রতিটি পৃষ্ঠায় বারবার আসে।\n\nউদাহরণ: যদি আপনি `{\"tags\":[\"beta\",\"finance\"],\"region\":\"us\"}` JSONB-তে স্টোর করেন, attribute bundle-গুলোর (`@\u003e`) জন্য GIN ব্যবহার করুন, এবং যেসব কীগুলো তালিকা ভিউ চালায় তাদের জন্য এক্সপ্রেশন ইনডেক্স যোগ করুন (plan, region)।\n\n## অ্যারে ইনডেক্সিং: যেখানে GIN আলো জ্বালায়\n\nঅ্যাসেসগুলি সংরক্ষণ করা সহজ এবং পড়তেও সহজ বলে আকর্ষণীয়। একটি `users.roles text[]` কলাম বা `projects.labels text[]` কলাম ভাল কাজ করতে পারে যখন আপনি প্রধানত একটাই প্রশ্ন করেন: এই সারিটি কি কোনো মান ধারণ করে? সেটাই GIN-কে সাহায্য করে।\n\nGIN হল array membership কুয়েরির জন্য প্রথম পছন্দ। এটি অ্যারেকে পৃথক আইটেমে ভেঙে দ্রুত লুকআপ তৈরি করে যে সারিগুলো সেগুলো ধারণ করে।\n\nঅ্যারে কুয়েরি শেপ যা প্রায়ই উপকার পায়:\n\n- একটি মান বা সেট ধারণ করে: `@\u003e` (array contains)\n- সেটের সাথে ওভারল্যাপ: `&&` (array shares any items)\n- মাঝে মাঝে: `= ANY(...)`, কিন্তু `@\u003e` প্রায়ই বেশি_predictable\n\nএকটি টিপিক্যাল উদাহরণ: role দিয়ে ইউজার ফিল্টার করা:\n\nsql
-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @\u003e ARRAY['admin'];
CREATE INDEX users_roles_gin ON users USING GIN (roles);
\n\nএবং label সেট দিয়ে প্রকল্প ফিল্টার করা (দু’টো লেবেল উভয়ই থাকতে হবে):\n\nsql
SELECT id, name
FROM projects
WHERE labels @\u003e ARRAY['billing', 'urgent'];
CREATE INDEX projects_labels_gin ON projects USING GIN (labels);
\n\nযেখানে লোকেরা অবাক হয়: কিছু প্যাটার্ন ইনডেক্সটিকে আপনার প্রত্যাশা মতো ব্যবহার করে না। যদি আপনি অ্যারেকে স্ট্রিং-এ রূপান্তর করে (`array_to_string(labels, ',')`) তারপর `LIKE` চালান, GIN ইনডেক্স সাহায্য করবে না। এছাড়াও, যদি আপনি “starts with” বা fuzzy মিল চান লেবেলে, তাহলে আপনি টেক্সট সার্চ দিকেই যাবেন, অ্যারে-মেম্বারশিপ নয়।\n\nঅ্যারে তখন বজায় রাখা কঠিন হয়ে যেতে পারে যখন তা একটি ছোট ডেটাবেস হয়ে ওঠে: ফ্রিকোয়েন্ট আপডেট, প্রতিটি আইটেমের জন্য মেটাডেটা (কে লেবেল অ্যাড করলো, কখন, কেন) বা লেবেল-প্রতি অ্যানালিটিক্সের প্রয়োজন পড়ে। তখন একটি join টেবিল যেমন `project_labels(project_id, label)` সাধারণত বেশি সহজে যাচাইযোগ্য, কুয়েরি-যোগ্য এবং উন্নয়নশীল।\n\n## সার্চ ইনডেক্সিং: ফুল-টেক্সট এবং ফাজি মিল (GIN ও GiST)\n\nসার্চ বক্সগুলোর জন্য দুইটি প্যাটার্ন বারবার দেখা যায়: ফুল-টেক্সট সার্চ (কোনো টপিক সম্পর্কে রেকর্ড খুঁজুন) এবং ফাজি মিল (টাইপো, অংশিক নাম, ILIKE প্যাটার্ন হ্যান্ডেল করা)। সঠিক ইনডেক্সই পার্থক্য করে “তাত্ক্ষণিক” এবং “10k ইউজারে টাইমআউট” এর মধ্যে।\n\n### ফুল-টেক্সট সার্চ: tsvector + GIN\n\nযখন ব্যবহারকারীরা প্রকৃত শব্দ টাইপ করে এবং আপনি relevance ক্রমানুসারে ফলাফল চান, তখন ফুল-টেক্সট সার্চ ব্যবহার করুন, যেমন টিকিট সাবজেক্ট ও বর্ণনা সার্চ করা। সাধারণ সেটআপ হল `tsvector` (অften একটি generated column) সংরক্ষণ করা এবং GIN দিয়ে ইনডেক্স করা। আপনি `@@` এবং একটি `tsquery` দিয়ে সার্চ করবেন।\n\nsql
-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX tickets_search_vec_gin ON tickets USING GIN (search_vec);
-- Query SELECT id, subject FROM tickets WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');
-- Customers: fuzzy name search using trigrams CREATE INDEX customers_name_trgm ON customers USING GIN (name gin_trgm_ops);
SELECT id, name FROM customers WHERE name ILIKE '%jon smth%'; sql SELECT id, created_at, email FROM customers WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 50; sql CREATE INDEX ON customers (tenant_id, status, created_at DESC); sql -- 1) List page with tenant + status filter + sort SELECT id, email, last_active_at FROM users WHERE tenant_id = $1 AND status = $2 ORDER BY last_active_at DESC LIMIT 50;
-- 2) Search box (full-text) SELECT id, email FROM users WHERE tenant_id = $1 AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2) ORDER BY last_active_at DESC LIMIT 50;
-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
AND metadata @\u003e '{"plan":"pro"}'::jsonb;
```\n\nএই স্ক্রিনের জন্য ছোট কিন্তু লক্ষ্যভিত্তিক ইনডেক্স সেটঃ\n\n- তালিকা ভিউ-এর জন্য B-tree কম্পোজিট: (tenant_id, status, last_active_at DESC)।\n- সার্চের জন্য GIN: একটি generated tsvector কলাম এবং তার GIN ইনডেক্স।\n- JSONB ইনডেক্সিং ব্যবহারের ওপর নির্ভর করে: যখন আপনি @\u003e বহুগুণভাবে ব্যবহার করেন তখন , অথবা যখন আপনি প্রধানত একটি কী দিয়ে ফিল্টার করেন তখন এক্সপ্রেশন B-tree ।\n\nমিশ্র চাহিদা স্বাভাবিক। যদি একটি পেজ filters + search + JSON সব করে, সবকিছু একত্রে এক বড় ইনডেক্সে চাপাবেন না। B-tree রাখুন sorting/pagination-এর জন্য, তারপর একটি বিশেষায়িত ইনডেক্স (সাধারণত GIN) যোগ করুন ব্যয়বহুল অংশের জন্য।\n\nপরবর্তী ধাপ: একটি ধীর স্ক্রিন বেছে নিন, তার শীর্ষ 2-3 কুয়েরি শেপ লিখে নিন, এবং প্রতিটি ইনডেক্সকে উদ্দেশ্য অনুসারে পর্যালোচনা করুন (filter, sort, search, JSON)। যদি কোনো ইনডেক্স স্পষ্টভাবে একটি বাস্তব কুয়েরির সাথে মিল না খায়, সেটিকে পরিকল্পনা থেকে বাদ দিন। আপনি যদি তাড়াতাড়ি Koder.ai-তে ইটারেট করেন, প্রতিবার নতুন স্ক্রিন যোগ করার সময় এই রিভিউ করাটা ইনডেক্স স্প্রলকে রোধ করতে সাহায্য করবে যখন আপনার স্কিমা এখনও বদলাচ্ছে।
An index lets PostgreSQL find matching rows without reading most of the table. For common SaaS screens like lists, dashboards, and search, the right index can turn a slow sequential scan into a fast lookup that scales better as the table grows.
Start with B-tree for most app queries because it’s best for = filters, range filters, joins, and ORDER BY. If your query is mainly about containment (JSONB, arrays) or text search, then GIN is usually the next thing to consider; GiST is more for overlap and “nearest/closest” style queries.
Put the columns you filter with = first, then put the column you sort by last. That order matches how the planner can walk the index efficiently, so it can both filter and return rows in the right order without an extra sort.
If every query is scoped by tenant_id, putting tenant_id first keeps each tenant’s rows grouped together inside the index. That usually reduces the amount of index and table data PostgreSQL has to touch for everyday list pages.
INCLUDE lets you add extra columns to support index-only reads for list pages without making the index key wider. It’s most useful when you filter and sort by a few columns but you also display a couple of extra fields on the screen.
Use a partial index when you only care about a subset of rows, like “not deleted” or “active only.” It keeps the index smaller and cheaper to maintain, which matters on hot tables that get lots of inserts and updates.
Use a GIN index on the JSONB column when you frequently query with containment like metadata @\u003e '{"plan":"pro"}'. If you mostly filter on one or two specific JSON keys, an expression B-tree index on (metadata-\u003e\u003e'plan') is often smaller and faster.
GIN is a great fit when your main question is “does this array contain X?” using operators like @\u003e or &&. If you need per-item metadata, frequent edits, or analytics per label/role, a join table is usually easier to maintain and index well.
For full-text search, store a tsvector (often as a generated column) and index it with GIN, then query with @@ for relevance-style search. For fuzzy matching like ILIKE '%name%' and typo tolerance, trigram indexes (often GIN) are typically the right tool.
Copy the exact SQL your app runs and run EXPLAIN (ANALYZE, BUFFERS) to see where time is spent and whether you’re scanning, sorting, or doing expensive filters. Add the smallest index that matches the query’s operators and sort order, then rerun the same EXPLAIN to confirm it’s actually used and improves the plan.
\n\nপেজিনেশনই অনেক SaaS অ্যাপকে ধীর করে দেয়। Offset pagination (`OFFSET 50000`) ডাটাবেসকে বহু সারি পার্শ করা জোর করে। Seek pagination শেষ দেখা sort কী ব্যবহার করে দ্রুত থাকে:\n\n\n\nসঠিক B-tree ইনডেক্স থাকলে, এইভাবে টেবিল বাড়লেও কুয়েরি দ্রুত থাকে।\n\n## টেন্যান্ট-ফ্রেন্ডলি ইনডেক্সিং, বেশি ইনডেক্স না করে\n\nঅধিকাংশ SaaS অ্যাপ মাল্টি-টেন্যান্ট: প্রতিটি কুয়েরি একটি টেন্যান্টের মধ্যেই সীমাবদ্ধ। যদি আপনার ইনডেক্সগুলোতে `tenant_id` না থাকে, Postgres তখনও দ্রুত সারি পেতে পারে, কিন্তু প্রায়ই অনেক বেশি ইনডেক্স এন্ট্রি স্ক্যান করতে হয়। টেন্যান্ট-অ্যাওয়ার ইনডেক্সগুলো ইনডেক্সের ভিতরে প্রতিটি টেন্যান্টের ডেটাকে ক্লাস্টার করে রাখে তাই সাধারণ স্ক্রিনগুলো দ্রুত এবং পূর্বানুমানযোগ্য থাকে।\n\nএকটি সহজ নিয়ম: যদি কুয়েরি সবসময় টেন্যান্ট দিয়ে ফিল্টার করে, ইনডেক্সে `tenant_id` প্রথমে রাখুন। তারপর সেই কলাম যোগ করুন যাকে আপনি সবচেয়ে বেশি ফিল্টার বা sort করেন।\n\nউচ্চ-ইমপ্যাক্ট, নিখুঁত কিন্তু বোরিং ইনডেক্সগুলো সাধারণত এরকম দেখায়:\n\n- `(tenant_id, created_at)` সাম্প্রতিক আইটেম তালিকা এবং কার্সর পেজিনেশনের জন্য\n- `(tenant_id, status)` status filter-এর জন্য (Open, Paid, Failed)\n- `(tenant_id, user_id)` “এই ইউজারের মালিকানাধীন আইটেম” স্ক্রিনগুলোর জন্য\n- `(tenant_id, updated_at)` “ইতিমধ্যেই পরিবর্তিত” অ্যাডমিন ভিউগুলোর জন্য\n- `(tenant_id, external_id)` webhook বা ইমপোর্ট থেকেই lookup-এর জন্য\n\nওভার-ইনডেক্সিং ঘটে যখন আপনি প্রতিটি সামান্য ভিন্ন স্ক্রিনের জন্য নতুন ইনডেক্স যোগ করেন। নতুন ইনডেক্স যোগ করার আগে চেক করুন آیا একটি বিদ্যমান কম্পোজিট ইনডেক্স বাম-পক্ষের কলামগুলো কভার করে কি না। উদাহরণস্বরূপ, যদি আপনার কাছে `(tenant_id, created_at)` থাকে, সাধারণত আপনাকে আর `(tenant_id, created_at, id)` লাগবে না যতক্ষণ না আপনি সত্যিই `id` দিয়ে ফিল্টার করেন।\n\nPartial ইনডেক্স সাইজ এবং রাইট খরচ কমাতে সাহায্য করে যখন বেশিরভাগ সারি প্রাসঙ্গিক নয়। Soft deletes এবং “active only” ডেটার ক্ষেত্রে ভাল কাজ করে, উদাহরণস্বরূপ: শুধুমাত্র যেখানে `deleted_at IS NULL` বা যেখানে `status = 'active'` সেখানে ইন্ডেক্স করুন।\n\nপ্রতিটি অতিরিক্ত ইনডেক্স লেখাগুলোকে ভারী করে তোলে। Inserts প্রতিটি ইনডেক্স আপডেট করতে হবে, এবং updates তখনও বহু ইনডেক্স স্পর্শ করতে পারে যখন আপনি এক কলাম পরিবর্তন করেন। যদি আপনার অ্যাপ প্রচুর ইভেন্ট নিবেশ করে (এমনকি দ্রুত তৈরি করা অ্যাপগুলোও Koder.ai দিয়ে), ইনডেক্সগুলোকে শুধুমাত্র সেই কয়েকটি কুয়েরি শেপে সীমাবদ্ধ রাখুন যেগুলো প্রতিদিন ব্যবহারকারীরা চালায়।\n\n## JSONB ইনডেক্সিং: GIN এবং টার্গেটেড এক্সপ্রেশন ইনডেক্স\n\nJSONB সুবিধাজনক যখন আপনার অ্যাপে নমনীয় এক্সট্রা ফিল্ড দরকার যেমন feature flags, user attributes, বা per-tenant settings। কিন্তু সমস্যা হলো বিভিন্ন JSONB অপারেটর আলাদাভাবে আচরণ করে, তাই সবচেয়ে ভাল ইনডেক্স নির্ভর করে আপনি কীভাবে কুয়েরি করেন।\n\nদুইটি শেপ সবচেয়ে বেশি গুরুত্বপূর্ণ:\n\n- Containment: “এই JSON কি এই কী-ভ্যালু ধরে?”—`@\u003e` ব্যবহার করে।\n- Path extraction: “এই নির্দিষ্ট ফিল্ডের মান কী?”—`-\u003e` / `-\u003e\u003e` ব্যবহার করে (প্রায়ই `=` সঙ্গে তুলনা করা হয়)।\n\n### কখন GIN ইনডেক্স ঠিক থাকবে\n\nআপনি যদি প্রায়শই `@\u003e` দিয়ে ফিল্টার করেন, তাহলে JSONB কলামে GIN ইনডেক্স সাধারণত কার্যকর।\n\n\n\nকী স্টোর করবেন ভেক্টরে: কেবল সেই ফিল্ডগুলোই রাখুন যেগুলো আপনি প্রকৃতপক্ষে সার্চ করেন। সবকিছু (নোটস, আভ্যন্তরিন লগ) অন্তর্ভুক্ত করলে ইনডেক্স সাইজ ও রাইট খরচ বেড়ে যায়।\n\n### ফাজি মিল: ট্রিগ্রাম GIN বা GiST-এ\n\nযখন ব্যবহারকারীরা নাম, ইমেল বা ছোট ফ্রেজ সার্চ করে এবং অংশিক মিল বা টাইপো টলারেন্স চান, ট্রিগ্রাম সিমিলারিটি ব্যবহার করুন। ট্রিগ্রামগুলো `ILIKE '%term%'` এবং সাদৃশ্য অপারেটরগুলোর সঙ্গে কাজ করে। GIN সাধারণত “ম্যাচ করে কি না?” লুকআপে দ্রুত; GiST ভাল হতে পারে যখন আপনি সাদৃশ্য অনুযায়ী সাজানোর কথা ভাবেন।\n\nনিয়ম-অফ-থাম্ব:\n\n- Relevance-ভিত্তিক টেক্সট সার্চের জন্য GIN + `tsvector` ব্যবহার করুন।\n- ILIKE এবং টাইপো-টলারেন্ট নাম সার্চের জন্য ট্রিগ্রাম ব্যবহার করুন।\n\nদ্রষ্টব্য:\n\n- লিডিং ওয়াইল্ডকার্ড ছাড়া (`ILIKE '%abc'`) scans বাধ্য করে যদি ট্রিগ্রাম না থাকে।\n- খুব ছোট সার্চ টার্ম (1-2 ক্যারেক্টার) ট্রিগ্রাম ভালো ব্যবহার করতে পারেনা।\n- স্টপ-ওয়ার্ড এবং স্টেমিং ফুল-টেক্সট ফলাফলকে ব্যবহারকারীর কাছে অবাক করতে পারে, তাই এমন কনফিগারেশন বেছে নিন যা আপনার প্রোডাক্ট ভাষার সঙ্গে মেলে।\n\nআপনি যদি দ্রুত সার্চ স্ক্রিন চালু করছেন, ইনডেক্সকে ফিচারের অংশ হিসেবে বিবেচনা করুন: সার্চ UX এবং ইনডেক্সের পছন্দ একসাথে ডিজাইন করা উচিত।\n\n## ধাপে ধাপে: ধীর কুয়েরি থেকে সঠিক ইনডেক্স পর্যন্ত\n\nনিজের অ্যাপ যে নির্দিষ্ট কুয়েরিটি চালায় সেটি নিয়ে শুরু করুন, অনুমান নয়। একটি “ধীর স্ক্রিন” সাধারণত একটি SQL বিবৃতি যা একটি সুনির্দিষ্ট WHERE এবং ORDER BY আছে। লগ, ORM ডিবাগ আউটপুট বা যেকোনো কুয়েরি ক্যাপচার থেকে সেটি কপি করুন।\n\nএকটি কার্যপ্রবাহ যা বাস্তবে ধরে:\n\n- পূর্ণ SQL কপি করুন, যেখানে WHERE, ORDER BY, এবং LIMIT স্পষ্ট আছে।\n- একই কুয়েরি নিয়ে `EXPLAIN (ANALYZE, BUFFERS)` চালান।\n- কাজ যা হচ্ছে সেই অপারেটরগুলোর দিকে মনোযোগ দিন (`=`, `\u003e=`, `LIKE`, `@\u003e`, `@@`), শুধু কলাম নাম নয়।\n- সবচেয়ে ছোট ইনডেক্সটি যোগ করুন যা সেই অপারেটরগুলোকে মেলে।\n- বাস্তব ডাটা ভলিউম নিয়ে `EXPLAIN (ANALYZE, BUFFERS)` পুনরায় চালান।\n\nএকটি কনক্রিট উদাহরণ: একটি Customers পেজ tenant এবং status দিয়ে ফিল্টার করে, newest দিয়ে sort করে, এবং paginate করে:\n\n\n\nযদি `EXPLAIN` দেখায় sequential scan এবং sort, তাহলে ফিল্টার এবং sort মেলার একটি B-tree ইনডেক্স সাধারণত সেটি ঠিক করে:\n\n\n\nযদি ধীর অংশটি JSONB ফিল্টারিং হয় যেমন `metadata @\u003e '{"plan":"pro"}'`, তাহলে সেটি GIN নির্দেশ করে। যদি এটি ফুল-টেক্সট সার্চ হয় যেমন `to_tsvector(...) @@ plainto_tsquery(...)`, সেটিও GIN-ব্যাকড সার্চ ইনডেক্স নির্দেশ করে। যদি এটি “ক্লোজেস্ট ম্যাচ” বা ওভারল্যাপ-স্টাইল অপারেটর সেট হয়, তাহলে GiST প্রায়ই উপযুক্ত।\n\nইনডেক্স যোগ করার পরে ট্রেড-অফ মাপুন। ইনডেক্স সাইজ, ইনসার্ট ও আপডেট টাইম এবং এটি কি শীর্ষ কয়েকটি ধীর কুয়েরিকে সাহায্য করছে কিনা দেখুন বা শুধু একটি এজ কেস কিনা তা পরিমাপ করুন। দ্রুত পরিবর্তনশীল প্রকল্পে (Koder.ai-সহ) এই পুনরায় চেক আপনাকে অপ্রয়োজনীয় ইনডেক্স জমা হওয়া থেকে রোধ করবে।\n\n## সাধারণ ইনডেক্সিং ত্রুটি যা সময় ও টাকা নষ্ট করে\n\nঅধিকাংশ ইনডেক্স সমস্যা B-tree বনাম GIN বনাম GiST বাছাই সম্পর্কে নয়। বরং সমস্যা হল আপনি এমন একটি ইনডেক্স বানান যা দেখতে ঠিক, কিন্তু অ্যাপ যেভাবে টেবিল কুয়েরি করে তাতে মেলে না।\n\nযে ত্রুটিগুলো সবচেয়ে ক্ষতিকর:\n\n- কখনও ব্যবহার না হওয়া ইনডেক্স। কুয়েরিটি ভিন্ন অপারেটর ব্যবহার করে যা ইনডেক্স সাপোর্ট করে না, অথবা একটি কম্পোজিট ইনডেক্সের কলাম অর্ডার ভুল। যদি আপনার WHERE ধারা `tenant_id` এবং `created_at` দিয়ে শুরু করে, কিন্তু ইনডেক্স `created_at` দিয়ে শুরু করে, প্ল্যানার এটি এড়িয়ে যেতে পারে।\n- নিম্ন-কার্ডিনালিটি কলাম আলাদাভাবে ইনডেক্স করা। `status`, `is_active`, বা একটি boolean-এ আলাদা ইনডেক্স সাধারণত কম কাজে লাগে কারণ এগুলো অনেক সারিকে ম্যাচ করে। এটিকে একটি নির্বাচনী কলামের সাথে জোড়া দিন (যেমন `tenant_id` বা `created_at`) বা এড়িয়ে চলুন।\n- ওভারল্যাপিং ইনডেক্স যা ব্লোট করে এবং লেখাগুলো ধীর করে। একই টেবিলে মিল-ধরনের ইনডেক্সগুলো স্টোরেজ দ্বিগুণ করে দিতে পারে এবং inserts/updates ধীর করে।\n- Pagination blind spots। কেবল OFFSET সমর্থন করার জন্য ইনডেক্স করা একটি সাধারণ ফাঁদ। যদি আপনি keyset pagination ব্যবহার করেন, আপনাকে সেই sort এবং last-seen ফিল্টারের সাথে মেলে এমন ইনডেক্স দরকার।\n- স্টেল স্ট্যাটস এবং টেবিল ঝামেলা। যদি autovacuum ব্যাকলগ হয়, বা `ANALYZE` সম্প্রতি না চালানো হয়ে থাকে, প্ল্যানার খারাপ প্ল্যান বেছে নিতে পারে এমনকি সঠিক ইনডেক্স থাকলেও।\n\nএকটি কনক্রিট উদাহরণ: আপনার Invoices স্ক্রিন `tenant_id` এবং `status` দিয়ে ফিল্টার করে, তারপর `created_at DESC` দিয়ে sort করে। কেবল `status`-এর উপর ইনডেক্স খুব কম সাহায্য করবে। একটি ভাল মিল হল একটি কম্পোজিট ইনডেক্স যা `tenant_id` দিয়ে শুরু করে, তারপর `status`, তারপর `created_at` (প্রথমে filter, শেষে sort)। একক পরিবর্তনটি প্রায়ই তিনটি আলাদা ইনডেক্স যোগ করার চেয়ে ভাল।\n\nপ্রতিটি ইনডেক্সকে একটি খরচ হিসেবে বিবেচনা করুন। এটি বাস্তব কুয়েরিগুলোতে নিজের উপকার করতে হবে, কেবল তত্ত্বে নয়।\n\n## ইনডেক্স পরিবর্তন শিপ করার আগে দ্রুত চেকলিস্ট\n\nইনডেক্স পরিবর্তন শিপ করা সহজ কিন্তু তা undo করা বিরক্তিকর হতে পারে যদি এগুলো লেখার খরচ বাড়ায় বা একটি ব্যস্ত টেবিল লক করে। মের্জ করার আগে এটিকে একটি ছোট রিলিজের মতো বিবেচনা করুন।\n\nশুরু করুন কি অপটিমাইজ করছেন তা ঠিক করে। লগ বা মনিটরিং থেকে দুটি ছোট র্যাঙ্কিং টানুন: সবচেয়ে বেশি চলমান কুয়েরি, এবং সবচেয়ে বেশি latency থাকা কুয়েরি। প্রতিটির জন্য নির্দিষ্ট শেপ লিখে রাখুন: filter কলাম, sort order, joins, এবং ব্যবহৃত অপারেটরগুলো (`=`, range, IN, ILIKE, JSONB operators, array contains)। এটি অনুমান রোধ করে এবং আপনাকে সঠিক ইনডেক্স টাইপ বেছে নিতে সাহায্য করে।\n\nপ্রি-শিপ চেকলিস্ট:\n\n- কনফার্ম কুয়েরি টেন্যান্ট-স্কোপড কি না যেখানে তা প্রয়োজন।\n- ইনডেক্সকে অপারেটরের সাথে মিলান: B-tree equality/range/sort-এর জন্য, GIN membership (arrays, JSONB, full text) জন্য, GiST overlap বা distance-style কেসের জন্য।\n- সাধারণ filter + sort কভার করার জন্য একটি কম্পোজিট ইনডেক্স পছন্দ করুন, একাধিক single-column ইনডেক্সের পরিবর্তে।\n- ইনডেক্স পাতলা রাখুন: কেবল সেই কলামগুলো অন্তর্ভুক্ত করুন যেগুলো কুয়েরি প্রকৃতপক্ষে ব্যবহার করে।\n- রোলআউট প্ল্যান করুন: ইনডেক্স তৈরি লেখাকে ব্লক করবে কি, এবং এটি কি অফ-পিক সময়ে শিডিউল করা দরকার?\n\nইনডেক্স যোগ করার পরে, যাচাই করুন এটি বাস্তবে সহায়ক হয়েছে কি না। একই কুয়েরি নিয়ে `EXPLAIN (ANALYZE, BUFFERS)` চালান এবং আগে বনাম পরে তুলনা করুন। তারপর এক দিন প্রোডাকশন আচরণ দেখুন:\n\n- টার্গেট স্ক্রিনগুলোর জন্য পড়ার latency কমেছে কি?\n- লেখার latency (inserts/updates) বেড়েছে কি?\n- CPU বা স্টোরেজ ইনডেক্সের কারণে স্পাইক করেছে কি?\n- ইনডেক্সটি কি প্রকৃতপক্ষে ব্যবহৃত হচ্ছে, নাকি এটি ডেড ওয়েট?\n\nআপনি যদি Koder.ai দিয়ে তৈরি করছেন, তৈরি করা SQL দুইটি ধীর স্ক্রিনের পাশে রাখতে পারেন যাতে ইনডেক্সটি প্রকৃতপক্ষে অ্যাপ যা চালায় তার সঙ্গে মেলে।\n\n## উদাহরণ: একটি সাধারণ SaaS অ্যাপ ওয়ার্কফ্লো ইনডেক্সিং + পরবর্তী ধাপ\n\nএকটি সাধারণ অ্যাডমিন স্ক্রিন কল্পনা করুন: Users তালিকা যেখানে টেন্যান্ট স্কোপ, কয়েকটি ফিল্টার, last active দিয়ে sort, এবং একটি সার্চ বক্স আছে। এখানে ইনডেক্স থিওরি থেমে বাস্তব সময় বাঁচায়।\n\nআপনি সাধারণত তিনটি কুয়েরি শেপ দেখবেন:\n\nGIN (metadata)((metadata-\u003e\u003e'plan'))