মূল কনটেন্টে চলে যান

ইন্টারঅ্যাকটিভ ইঞ্জিনিয়ারিং টুল

PostgreSQL ইনডেক্স ও কুয়েরি লেটেন্সি অ্যাডভাইজর

টেবিলের রো সংখ্যা, ফিল্টার ধরন, রাইট চাপ এবং সর্ট প্যাটার্ন বেছে নিন। প্রোডাকশন-রেডি CREATE INDEX SQL, EXPLAIN কুয়েরি, মেমোরি/VACUUM টিউনিং নোট এবং ৫ কোটি রো মাইগ্রেশনের বাস্তব বেঞ্চমার্ক তাৎক্ষণিকভাবে পান।

হালনাগাদ 2026-11-22 · PostgreSQL 13–17

প্রোডাকশন ওয়ার্কলোড প্রিসেট লোড করুন

১. টেবিলের রো সংখ্যা
২. প্রধান WHERE ফিল্টার
৩. রাইট অ্যামপ্লিফিকেশন সংবেদনশীলতা
৪. সর্ট ও প্রজেকশন প্যাটার্ন
প্রস্তাবিত ইনডেক্স কৌশলGIN + tsvectorCONCURRENTLY (রাইট লক মুক্ত)ইনডেক্সড কলামে HOT বন্ধ
স্টোরড tsvector + GIN ইনভার্টেড ইনডেক্স
রাইট করার সময় টেক্সট টোকেনাইজ করে স্টোরড tsvector কলামে রাখে এবং GIN ইনডেক্স তৈরি করে, যা আমাদের ৫ কোটি রো মাইগ্রেশনে ৬০s → ২০০ms অর্জনের মূল কৌশল।

ইনডেক্সবিহীন 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-এর পারফরম্যান্স

যে কোয়েরিতে এখন এক মিনিট লাগে, তা মিলিসেকেন্ডেই শেষ হতে পারে

প্রোডাকশনের কোনো কোয়েরি যদি পুরো প্রোডাক্টকে আটকে রাখে, ৫ কোটি রো-র সিস্টেমে কাজের অভিজ্ঞতা থেকে ইনডেক্সিং, ক্যাশিং আর স্কিমা নিয়ে সাহায্য করতে পারি। সমস্যাটা লিখে ইমেইল করুন।

ফুল-টাইম ও পার্ট-টাইম কাজের জন্য উন্মুক্ত (রিমোট বা অনসাইট)

কোয়েরি নিয়ে কথা বলুনGitHubLinkedIn

Toptal-এও আছি। তবে কথা শুরু করতে ইমেইলই সবচেয়ে সহজ উপায়।