ইন্টারঅ্যাকটিভ ইঞ্জিনিয়ারিং টুল
PostgreSQL ইনডেক্স ও কুয়েরি লেটেন্সি অ্যাডভাইজর
টেবিলের রো সংখ্যা, ফিল্টার ধরন, রাইট চাপ এবং সর্ট প্যাটার্ন বেছে নিন। প্রোডাকশন-রেডি CREATE INDEX SQL, EXPLAIN কুয়েরি, মেমোরি/VACUUM টিউনিং নোট এবং ৫ কোটি রো মাইগ্রেশনের বাস্তব বেঞ্চমার্ক তাৎক্ষণিকভাবে পান।
প্রোডাকশন ওয়ার্কলোড প্রিসেট লোড করুন
ইনডেক্সবিহীন Seq Scan
42s – 65s
প্রত্যাশিত ইনডেক্সড p95
65ms – 200ms
আনুমানিক আকার
~12% – 22% of heap
রাইট ওভারহেড
উচ্চ (pending list / vacuum টিউন করুন)
-- 1. Persist a weighted tsvector so queries never recompute to_tsvector() per row
ALTER TABLE property_records
ADD COLUMN IF NOT EXISTS search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('simple', coalesce(owner_and_address, ''))) STORED;
-- 2. Build the GIN inverted index without blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_property_records_search_vec_gin
ON property_records USING gin (search_vector)
WHERE archived_at IS NULL;ইঞ্জিনিয়ারিং ট্রেড-অফ
- কোটি কোটি রো-এর মধ্যে মাল্টি-ওয়ার্ড সার্চ, স্টেমিং এবং websearch_to_tsquery ২০০ms-এর নিচে সম্পন্ন করে।
- GIN নিজে থেকে timestamp অনুযায়ী সর্ট করতে পারে না; সাধারণ শব্দের সাথে ORDER BY থাকলে btree_gin বা tenant ফিল্টার যোগ করা উত্তম।
- প্রতিটি INSERT/UPDATE একাধিক টোকেন লেখায় WAL ও pending-list ফ্লাশ খরচ বাড়ায়।
মেমোরি, WAL ও VACUUM টিউনিং নোট
- রাইট স্পাইক এড়াতে gin_pending_list_limit (যেমন 4MB) এবং নিয়মিত autovacuum টিউন করুন।
- tsvector কলামটি GENERATED ALWAYS AS (...) STORED হিসেবে রাখুন যাতে কুয়েরির সময় পুনরায় হিসাব না হয়।
- ৫ কোটি+ রো-তে CREATE INDEX CONCURRENTLY চালানোর আগে maintenance_work_mem >= 1GB দিন।
প্রোডাকশন প্রমাণ · ৫ কোটি+ রেকর্ড
৬০ সেকেন্ড → ২০০ মিলিসেকেন্ড: লিগ্যাসি SQL Server থেকে PostgreSQL মাইগ্রেশনের বাস্তব ফলাফল
এই টুলের প্রতিটি নিয়ম তৈরি হয়েছে ৫ কোটি+ প্রপার্টি ও কাউন্টি-ক্লার্ক রেকর্ডসমৃদ্ধ Classic ASP/MSSQL প্ল্যাটফর্মকে NestJS, PostgreSQL এবং AWS RDS-এ রূপান্তরের বাস্তব অভিজ্ঞতা থেকে, যা প্রতি মিনিটে ৩০,০০০+ রিকোয়েস্ট সার্ভ করে।
60s+ → 200ms
p95 সার্চ লেটেন্সি
ধীরগতির LIKE স্ক্যান বাদ দিয়ে স্টোরড tsvector + GIN এবং কম্পোজিট B-Tree প্রয়োগ।
50M+ রো
জিরো-ডাউনটাইম মাইগ্রেশন
চেকসাম যাচাই, ডুয়াল-রিড ভ্যালিডেশন এবং CONCURRENTLY ইনডেক্স বিল্ড।
30K req/min
স্থিতিশীল থ্রুপুট
NestJS কানেকশন পুলিং, পার্শিয়াল অ্যাক্টিভ ইনডেক্স এবং Redis হট-কুয়েরি ক্যাশিং।
প্রোডাকশনে যে সিদ্ধান্তগুলো সবচেয়ে বড় পরিবর্তন এনেছিল
- STORED জেনারেটেড tsvector কলাম ব্যবহার করায় প্রতিটি কুয়েরিতে পুনরায় টেক্সট টোকেনাইজেশনের CPU খরচ পুরোপুরি দূর হয়।
- পার্শিয়াল WHERE ইনডেক্সের মাধ্যমে সক্রিয় রেকর্ড ও পুরনো আর্কাইভ আলাদা করায় B-Tree-এর RAM ব্যবহার ৬৫%-এর বেশি কমে যায়।
- বিল্ড সেশনে maintenance_work_mem ১.৫ গিগাবাইটে উন্নীত করায় রাইট লক ছাড়াই দ্রুত GIN ইনডেক্স তৈরি সম্ভব হয়।
আর্কিটেকচার রেফারেন্স
PostgreSQL ইনডেক্স পরিবারগুলোর তুলনামূলক গাইড
কখন অর্ডারড B-Tree, হিপ-মুক্ত INCLUDE কাভারিং ইনডেক্স, ইনভার্টেড GIN, কিংবা হালকা BRIN বেছে নেবেন তার দ্রুত তুলনা।
| ইনডেক্স ধরন | সেরা কুয়েরি প্যাটার্ন | ডিস্ক / RAM আকার | রাইট খরচ | ORDER BY সাপোর্ট | গুরুত্বপূর্ণ PostgreSQL সেটিং |
|---|---|---|---|---|---|
| B-Tree (Composite) | = , IN , BETWEEN , >= , প্রিফিক্স 'abc%' | হিপের ১৪% – ২৪% | কম–মাঝারি | হ্যাঁ (ASC / DESC) | maintenance_work_mem, deduplicate_items |
| B-Tree + INCLUDE | উচ্চ QPS ফিডে হিপ-মুক্ত Index-Only Scan | হিপের ২২% – ৩৬% | মাঝারি (INCLUDE কলামে HOT বন্ধ) | হ্যাঁ (Visibility Map পরিষ্কার থাকলে) | autovacuum_vacuum_scale_factor |
| Partial Index (WHERE) | যেখানে <২৫% রো সক্রিয়/ওপেন অবস্থায় থাকে | হিপের ৪% – ১২% | আর্কাইভ রো-তে শূন্য | মূল ইনডেক্স অনুযায়ী | ORM কুয়েরিতে হুবহু একই WHERE শর্ত |
| GIN + Stored tsvector | মাল্টি-ওয়ার্ড ফুল-টেক্সট সার্চ (@@) | হিপের ২৪% – ৪২% | উচ্চ (একাধিক টোকেন এন্ট্রি) | না (বিটম্যাপ স্ক্যান + সর্ট) | gin_pending_list_limit, STORED কলাম |
| GIN + pg_trgm | সাবস্ট্রিং ILIKE '%term%' ও ফাজি সার্চ | হিপের ৩৮% – ৭০% | উচ্চ (৩-গ্রাম ফ্যান-আউট) | না (KNN-এর জন্য GiST) | work_mem (Lossy ব্লক প্রতিরোধে) |
| GIN (jsonb_path_ops) | JSONB কনটেইনমেন্ট (payload @> '{...}') | হিপের ১৫% – ২৮% | মাঝারি (jsonb_ops-এর অর্ধেক) | না | fastupdate = on, নেস্টেড অ্যারে সীমিতকরণ |
| BRIN | ৫ কোটি+ অ্যাপেন্ড-অনলি টাইমস্ট্যাম্প টেবিল | হিপের ~০.০৫% – ০.২% | প্রায় শূন্য | না (ব্লক রেঞ্জ ছাঁটাই করে) | pages_per_range (32–64), correlation |
প্রশ্নোত্তর
প্রোডাকশন PostgreSQL ইনডেক্সিং নিয়ে সচরাচর জিজ্ঞাসা
GIN বনাম B-Tree, ILIKE স্ক্যান, লক-মুক্ত CONCURRENTLY ইনডেক্স, BRIN এবং কাভারিং ইনডেক্সের সরাসরি উত্তর।
PostgreSQL-এ কখন B-Tree-এর বদলে GIN ইনডেক্স ব্যবহার করা উচিত?
যখন প্রতিটি রো-তে একটি একক স্কেলার মান থাকে (=, IN, BETWEEN, >=, ORDER BY), তখন B-Tree সেরা। কিন্তু যখন একটি কলামের ভেতরে একাধিক সার্চযোগ্য টোকেন বা উপাদান থাকে, যেমন ফুল-টেক্সট লেক্সিম (tsvector @@), ILIKE '%substring%'-এর ট্রাইগ্রাম (pg_trgm), অ্যারে এলিমেন্ট (&& / @>), বা নেস্টেড JSONB পাথ, তখন GIN ইনভার্টেড ইনডেক্স ব্যবহার করা উচিত।
ILIKE '%search%' কুয়েরিতে সাধারণ B-Tree ইনডেক্স কাজ করে না কেন?
B-Tree স্ট্রিংয়ের বাম দিকের প্রথম অক্ষর থেকে সাজানো থাকে, তাই শুরুতে '%' ওয়াইল্ডকার্ড থাকলে ট্রি-তে শুরুর অবস্থান নির্ধারণ করা যায় না এবং পুরো টেবিল Seq Scan করতে হয়। শুরুতে ছাড়া সাবস্ট্রিং সার্চ দ্রুত করতে pg_trgm এক্সটেনশন চালু করে GIN (col gin_trgm_ops) ইনডেক্স তৈরি করুন।
৫ কোটি+ রো-এর টেবিলে রাইট লক না করে কীভাবে ইনডেক্স তৈরি করব?
ট্রানজ্যাকশন ব্লকের বাইরে CREATE INDEX CONCURRENTLY চালান এবং ঐ সেশনে maintenance_work_mem (যেমন 1536MB) বাড়িয়ে নিন যাতে ডিস্ক সর্ট না হয়। কোনো কারণে বিল্ড ব্যর্থ হলে pg_indexes-এ INVALID ইনডেক্স চেক করে DROP INDEX CONCURRENTLY দিয়ে মুছে পুনরায় চালান।
বড় টেবিলে কখন BRIN ইনডেক্স B-Tree-এর চেয়ে ভালো ফল দেয়?
৫ কোটি+ রো-এর অ্যাপেন্ড-অনলি টেবিলে (অডিট লগ, টেলিমেট্রি, লেজার ইভেন্ট) যেখানে নতুন রো সময়ের ক্রমানুসারে যোগ হয় (pg_stats.correlation > 0.95), সেখানে BRIN ইনডেক্স B-Tree-এর তুলনায় প্রায় ৯৯% ছোট হয় এবং ন্যূনতম রাইট খরচে অপ্রয়োজনীয় ডিস্ক ব্লক বাদ দিতে পারে।
কম্পোজিট কী কলাম এবং INCLUDE কলামের পার্থক্য কী?
কম্পোজিট কী কলাম ট্রির ব্রাঞ্চ ও লিফ উভয় স্তরে থেকে ফিল্টার ও সর্টিং করে। অন্যদিকে INCLUDE কলাম কেবল লিফ পেজে পেলোড হিসেবে থাকে, যাতে Visibility Map পরিষ্কার থাকলে PostgreSQL হিপ পেজ না পড়েই Index-Only Scan-এর মাধ্যমে SELECT কুয়েরির উত্তর দিতে পারে।
PostgreSQL-এর পারফরম্যান্স
যে কোয়েরিতে এখন এক মিনিট লাগে, তা মিলিসেকেন্ডেই শেষ হতে পারে
প্রোডাকশনের কোনো কোয়েরি যদি পুরো প্রোডাক্টকে আটকে রাখে, ৫ কোটি রো-র সিস্টেমে কাজের অভিজ্ঞতা থেকে ইনডেক্সিং, ক্যাশিং আর স্কিমা নিয়ে সাহায্য করতে পারি। সমস্যাটা লিখে ইমেইল করুন।
ফুল-টাইম ও পার্ট-টাইম কাজের জন্য উন্মুক্ত (রিমোট বা অনসাইট)
Toptal-এও আছি। তবে কথা শুরু করতে ইমেইলই সবচেয়ে সহজ উপায়।