PostgreSQL আর্কিটেকচার ও অভ্যন্তরীণ মেকানিজম: একটি SQL কোয়েরির সম্পূর্ণ জার্নি (Parser, Planner, MVCC ও WAL)

ডাটাবেসের পেছনের অদৃশ্য দুনিয়া: ধরুন, আপনি একটি আধুনিক ই-কমার্স ওয়েবসাইট তৈরি করেছেন। একজন গ্রাহক কিছু পণ্য কার্টে যুক্ত করলেন, চেকআউট বাটনে চাপ দিলেন, পেমেন্ট সফল হলো এবং আপনার ব্যাকএন্ড ফ্রেমওয়ার্ক (Node.js, Django বা Laravel) একটি এসকিউএল (SQL) ইনসার্ট কোয়েরি পাঠিয়ে দিল পোস্টগ্রেসকিউএল (PostgreSQL) ডেটাবেসে। বাইরে থেকে দেখলে ঘটনাটি একেবারেই সাধারণ—অর্ডার টেবিলে একটি নতুন রো যোগ হয়ে গেল। কিন্তু এই ছোট্ট একটা কোয়েরি যখন পোস্টগ্রেস সার্ভারের নেটওয়ার্ক সকেটে প্রবেশ করে, তখন তার ভেতরে আসলে কী ঘটে? ডেটাবেস কি কেবল একটি এক্সেল শিটের মতো রো লিখে রেখে দেয়, নাকি এর পেছনে কোটি কোটি ডলারের এন্টারপ্রাইজ সিস্টেমের মতো এক বিশাল স্বয়ংক্রিয় শহর কাজ করে?

অধিকাংশ ডেভেলপার ডেটাবেস শেখার সময় টেবিল, কলাম, রো, সিলেক্ট, ইনসার্ট, আপডেট এবং ডিলিটের মতো বেসিক সিনট্যাক্সেই আটকে থাকেন। কিন্তু একটি প্রোডাকশন-গ্রেড এন্টারপ্রাইজ সিস্টেমে পোস্টগ্রেসকিউএল কেবল ডেটা রাখার স্টোরেজ বক্স নয়। এটি ডেটার নির্ভুলতা রক্ষা করে, ভুল ডেটা ভেতরে ঢোকা প্রতিরোধ করে, হাজার হাজার ইউজারের কনকারেন্ট রিড-রাইট সামলায়, সার্ভার ক্র্যাশ বা বিদ্যুৎ চলে গেলেও ডেটা অক্ষত রাখে এবং কোয়েরি চালানোর আগেই কোন রাস্তা দিয়ে গেলে সর্বনিম্ন রিসোর্স খরচ হবে তা পুঙ্খানুপুঙ্খভাবে হিসাব করে। এই আর্টিকেলে আমরা জটিল কোনো এসকিউএল সিনট্যাক্স মুখস্থ না করে, একটি কোয়েরির জীবনের প্রতিটি পদক্ষেপ অনুসরণ করে পোস্টগ্রেসকিউএলের ভেতরের প্রতিটি ইঞ্জিন কম্পোনেন্ট আবিষ্কার করব।

১. ভূমি রেজিস্ট্রি অফিস ও ডেটাবেস সিটির মেটাফোর

পোস্টগ্রেসকিউএলের মূল দর্শন বুঝতে একটি বড় সরকারি ভূমি রেজিস্ট্রি অফিসের উদাহরণ বিবেচনা করা যাক। মানুষ সেখানে নিয়মিত জমিজমার দলিল জমা দিতে আসে, মালিকানা পরিবর্তন করায় কিংবা শতবর্ষের পুরোনো রেকর্ড খুঁজে দেখতে চায়। এখন এই রেজিস্ট্রি অফিস যদি সবকিছু সাধারণ একটি খাতায় কোনো নিয়ম ছাড়াই লিখে রাখত, তবে কী হতো? কেউ ভুল তথ্য লিখে দিত, একজন দুর্নীতিবাজ ব্যক্তি একই জমি দুবার রেজিস্ট্রি করে ফেলত, কাজ করতে করতে খাতা ফেলে পালিয়ে যেত, বিদ্যুৎ বা আগুন লাগলে রেকর্ড চিরতরে পুড়ে ছাই হয়ে যেত এবং একসাথে হাজার মানুষ এসে খাতা নিয়ে কাড়াকাড়ি করত।

একটি দায়িত্বশীল সরকারি রেজিস্ট্রি অফিস কখনো কেবল খাতা রাখে না। তাদের কঠোর ভেরিফিকেশন রুলস, দ্রুত খোঁজার জন্য ইনডেক্স খাতা, সিকুয়েনশিয়াল অডিট লগবুক, পুরোনো ভার্সন দেখার আর্কাইভ সিস্টেম এবং ব্যাকআপ ভল্ট থাকে। পোস্টগ্রেসকিউএল ঠিক এই অফিসের মতোই একটি আর্কিটেকচারাল সিটি:

• কোয়েরি (Query): রেজিস্ট্রি অফিসে আসা সেবাগ্রহীতা বা ভিজিটর।
• পার্সার (Parser): গেটের সিকিউরিটি চেকার, যে ফাইলের ভাষা ও গ্রামার পরীক্ষা করে।
• প্ল্যানার (Planner): অভিজ্ঞ রুট প্ল্যানার, যে কাজের সবচেয়ে সস্তা ও দ্রুত পথ বেছে নেয়।
• এক্সিকিউটার (Executor): দক্ষ ওয়ার্কার টিম, যারা ফাইলের কাজ সম্পন্ন করে।
• ইন্ডেক্স (Index): লাইব্রেরির বর্ণানুক্রমিক ক্যাটালগ বা শর্টকাট ম্যাপ।
• ট্রানজেকশন (Transaction): সরকারি আইনি চুক্তিপত্র (হলে পুরোটা হবে, নয়তো বাতিল)।
• এমভিসিসি (MVCC): ডকুমেন্টের টাইম-মেশিন হিস্ট্রি ও স্ন্যাপশট কন্ট্রোল।
• ওয়াল (WAL): ব্ল্যাকবক্স বা ইমারজেন্সি ডায়েরি, যা ধ্বংসস্তূপ থেকেও সত্য রিকভার করতে পারে।
• ভ্যাকিউম (VACUUM): অফিসের নাইট ক্লিনার, যে অপ্রয়োজনীয় ময়লা কাগজ ড্রাস্টবিনে ফেলে আলমারির জায়গা খালি করে।

২. একটি এসকিউএল কোয়েরির অভ্যন্তরীণ ৩-ধাপের জার্নি

আপনার ব্যাকএন্ড অ্যাপ্লিকেশন যখন টিসিপি (TCP) সকেট দিয়ে পোস্টগ্রেসকিউএল সার্ভারে একটি এসকিউএল কোয়েরি পাঠায়, তখন সেটি সরাসরি ডিস্ক ফাইলে গিয়ে লেখা হয় না। শুরুতে কোয়েরিটি কেবল সাধারণ প্লেইন টেক্সট। এই টেক্সটকে তিনটি অত্যন্ত শক্তিশালী ধাপ অতিক্রম করতে হয়:

SQL Text ➔ [Parser & Analyzer] ➔ [Planner / Optimizer] ➔ [Executor] ➔ Storage Engine

ক. পার্সার ও অ্যানালাইজার (Parser & Analyzer)

পার্সারের প্রথম দায়িত্ব হলো কোয়েরির ভাষা ও সিনট্যাক্স পরীক্ষা করা। আপনি এসকিউএল ভাষার ব্যাকরণ ঠিকঠাক অনুসরণ করেছেন কিনা, বন্ধনী মিস করেছেন কিনা বা কোনো কি-ওয়ার্ড ভুল লিখেছেন কিনা—তা যাচাই করে। এরপর অ্যানালাইজার ক্যাটালগের সাথে মিলিয়ে দেখে যে আপনি যে টেবিল বা কলামের নাম দিয়েছেন, তা ডাটাবেসের সিস্টেম ক্যাটালগে সত্যি সত্যি বিদ্যমান কিনা। কোনো ভুল থাকলে পোস্টগ্রেস অনুমান করে কাজ করার চেষ্টা করে না; সাথে সাথে এরর ছুড়ে দেয়। কারণ ডাটাবেসের দায়িত্ব অনুমান করা নয়, শতভাগ নিশ্চয়তা দেওয়া। সফল হলে পার্সার একটি কোয়েরি ট্রি (Query Tree) তৈরি করে প্ল্যানারের হাতে তুলে দেয়।

খ. কস্ট-বেসড প্ল্যানার ও অপ্টিমাইজার (Cost-based Planner / Optimizer)

প্ল্যানার হলো পোস্টগ্রেসকিউএলের আসল মস্তিষ্ক। এসকিউএল একটি ডিক্লারেটিভ ভাষা (Declarative Language)—অর্থাৎ আপনি বলেন "আমাকে এই টেবিল থেকে এই শর্তের ডেটা দাও", কিন্তু আপনি বলেন না কীভাবে দেবে। যেমন রেস্তোরাঁয় গিয়ে আপনি বলেন "আমার বিরিয়ানি লাগবে", কিচেনের বাবুর্চিকে বলেন না কোন তাক থেকে চাল নামাবে বা কোন হাঁড়িতে তেল দেবে। প্ল্যানার হলো সেই কিচেন ম্যানেজার।

প্ল্যানার হিসাব করে দেখে: টেবিলে কত রো আছে? কোন ইন্ডেক্স আছে? ডেটা ডিস্ট্রিবিউশন কেমন? টেবিল জয়েন করতে হলে কোন টেবিলটি আগে মেমোরিতে তোলা লাভজনক? এই বিকল্প পথগুলোর মধ্যে প্ল্যানার একটি আনুমানিক কাজের খরচ (Estimated Cost) হিসাব করে। কস্ট মানে টাকা নয়—কতগুলো ডিস্ক পেজ রিড করতে হতে পারে, কত মেমোরি ও সিপিইউ চক্র লাগতে পারে তার গাণিতিক একক। সর্বনিম্ন খরচের পথটি বেছে নিয়ে প্ল্যানার একটি এক্সিকিউশন প্ল্যান প্রস্তুত করে।

গ. এক্সিকিউটার (Executor)

প্ল্যানার যে ব্লুপ্রিন্ট তৈরি করেছে, এক্সিকিউটার ঠিক সেই অনুযায়ী কাজ চালায়। এটি বাফার ম্যানেজার থেকে পেজ রিড করে, ইন্ডেক্স স্ক্যান চালায়, ফিল্টারিং শর্ত প্রয়োগ করে, রো ইনসার্ট বা ডিলিট করে এবং ফলাফল ক্লায়েন্টের কাছে রিটার্ন করে।

৩. ইন্ডেক্স বনাম সিকোয়েনশিয়াল স্ক্যানের আসল বাস্তবতা

অনেকেই মনে করেন টেবিলে ইন্ডেক্স তৈরি করলেই সব কোয়েরি রকেটের গতিতে চলবে। এটি ডাটাবেস ইঞ্জিনিয়ারিংয়ের একটি বড় ভুল ধারণা। বইয়ের পেছনের ইনডেক্স যেমন ১,০০০ পৃষ্ঠার বই থেকে একটি শব্দ এক সেকেন্ডে বের করতে সাহায্য করে, তেমনি ডাটাবেসের বি-ট্রি (B-Tree) ইন্ডেক্স লাখ লাখ রো-এর মধ্য থেকে সরাসরি কাঙ্ক্ষিত রো-এর পয়েন্টারে পৌঁছে দেয়। কিন্তু ইন্ডেক্সের একটি বড় রক্ষণাবেক্ষণ খরচ (Maintenance Cost) রয়েছে:

• প্রতিবার নতুন রো ইনসার্ট বা ডিলিট হলে টেবিলে লেখার পাশাপাশি প্রতিটি ইন্ডেক্স ট্রি নতুন করে ব্যালান্স ও আপডেট করতে হয়।
• অপ্রয়োজনীয় অতিরিক্ত ইন্ডেক্স থাকলে রাইট অপারেশন অনেক ধীরগতির (Write-Heavy Bottleneck) হয়ে পড়ে এবং অতিরিক্ত ডিস্ক স্পেস অপচয় হয়।
• যে কলামে ভ্যালু ডাইভার্সিটি বা কার্ডিনালিটি অত্যন্ত কম (যেমন is_active কলামে ৯৮% ইউজারের মানই true), সেখানে ইন্ডেক্স বসালেও প্ল্যানার সেই ইন্ডেক্স ব্যবহার করবে না। কারণ পুরো টেবিল এক টানে পড়া (Sequential Scan) ইন্ডেক্স দেখে দেখে জাম্প করার চেয়ে অনেক বেশি সস্তা।

প্রো-টিপ: আপনার কোয়েরি কীভাবে এক্সিকিউট হচ্ছে তা দেখার জন্য পোস্টগ্রেসে EXPLAIN ANALYZE SELECT ... কমান্ড ব্যবহার করতে হয়। এটি প্ল্যানারের আনুমানিক কস্ট এবং বাস্তবে কত মিলিসেকেন্ড সময় ব্যয় হয়েছে তা হুবহু স্ক্রিনে তুলে ধরে।

৪. ট্রানজেকশন এবং এসিড (ACID) গ্যারান্টি

একটি অনলাইন স্টোরে অর্ডার হওয়ার অর্থ কেবল একটি সাধারণ রো ইনসার্ট হওয়া নয়। সেখানে কুপন ব্যবহার, স্টক কাউন্ট কমানো, পেমেন্ট গেটওয়ের স্ট্যাটাস আপডেট এবং অ্যাকাউন্টিং লেজার একযোগে পরিবর্তিত হতে হয়। এর মধ্যে কোনো একটি কাজ ব্যর্থ হলে অর্ধেক কাজ সম্পন্ন থাকা চলবে না। ট্রানজেকশন হলো ডাটাবেসের এমন একটি লজিক্যাল বাউন্ডারি, যা চারটি পবিত্র গ্যারান্টি প্রদান করে:

৫. MVCC (Multi-Version Concurrency Control) এবং ডেড টাপল

যদি একই সময়ে ১০০ জন ইউজার ডেটা পড়েন এবং ২০ জন ইউজার আপডেট করেন, তবে পোস্টগ্রেসকিউএল সবাইকে লাইনে দাঁড় করিয়ে এক এক জন করে কাজ করায় না। আবার সবাইকে একসাথে ছেড়ে দিয়ে রক্তারক্তিও বাঁধায় না। এর জাদুকরী সমাধান হলো MVCC (মাল্টি-ভার্সন কনকারেন্সি কন্ট্রোল)।

গুগল ডক্সের ভার্সন হিস্ট্রির মতো, পোস্টগ্রেসকিউএলে যখন কোনো রো আপডেট করা হয়, তখন পুরোনো রো-টিকে সঙ্গে সঙ্গে মুছে ফেলা বা ওভাররাইট করা হয় না। বরং পুরোনো রো ভার্সনটি রেখে দিয়ে একটি নতুন রো ভার্সন তৈরি করা হয়। পুরোনো ট্রানজেকশন তার স্ন্যাপশট অনুযায়ী পুরোনো ভার্সন দেখতে থাকে এবং নতুন ট্রানজেকশন নতুন ভার্সন দেখতে পায়। এর ফলে রিডাররা কখনো রাইটারদের আটকে রাখে না এবং রাইটাররা কখনো রিডারদের আটকে রাখে না।

ডেড টাপল (Dead Tuples) কী? সময়ের ব্যবধানে যখন কোনো পুরোনো ট্রানজেকশনের আর পুরোনো ভার্সনটি দেখার প্রয়োজন থাকে না, তখন সেই অব্যবহৃত রো ভার্সনটি মেমোরি ও ডিস্কে ডেড টাপল হিসেবে পড়ে থাকে।

৬. ভ্যাকুয়াম (VACUUM) ও অটো-ভ্যাকুয়াম: ডাটাবেসের ক্লিনিং সিস্টেম

এমভিসিসির কারণে টেবিলগুলোতে প্রতিনিয়ত ডেড টাপল জমতে থাকে। যদি এই আবর্জনা পরিষ্কার না করা হয়, তবে টেবিল অপ্রয়োজনীয়ভাবে বিশাল আকার (Table Bloat) ধারণ করবে এবং কোয়েরি মারাত্মক ধীরগতির হয়ে পড়বে। এখানেই আসে VACUUM।

• ভ্যাকুয়াম আপনার কাজের কোনো জীবন্ত ডেটা ডিলিট করে না; এটি কেবল ডেড টাপলগুলোকে মেমোরি থেকে পরিষ্কার করে ভবিষ্যতের নতুন রাইটের জন্য জায়গাটি পুনর্ব্যবহারযোগ্য (Mark Space as Reusable) হিসেবে চিহ্নিত করে।
• পোস্টগ্রেসকিউএলে Auto-Vacuum ব্যাকগ্রাউন্ডে সার্বক্ষণিক সক্রিয় থাকে। এটি টেবিল মনিটর করে যখনই নির্দিষ্ট পরিমাণ ডেড টাপল জমে, স্বয়ংক্রিয়ভাবে ক্লিনিং এবং ANALYZE চালিয়ে প্ল্যানারের স্ট্যাটিস্টিক্স আপডেট করে দেয়।

৭. WAL (Write-Ahead Logging): ক্র্যাশ রিকভারির ব্ল্যাকবক্স

সার্ভার হঠাৎ বন্ধ হয়ে গেলেও পোস্টগ্রেসকিউএল কীভাবে গ্যারান্টি দেয় যে কমিট করা ডেটা হারাবে না? এই রহস্যের নাম WAL (রাইট-অ্যাহেড লগিং)।

মেমোরিতে ডেটা পরিবর্তন হওয়ার পর সেই ডেটা ডিস্কের মূল টেবিল ফাইলে গিয়ে নিখুঁতভাবে লেখা অনেক সময়সাপেক্ষ ব্যাপার, কারণ ডিস্ক আই/ও তুলনামূলক ধীরগতির। তাই পোস্টগ্রেসকিউএলের স্বর্ণালী নিয়ম হলো—মূল টেবিল ফাইলে লেখার আগেই সিকুয়েনশিয়াল একটি লগ ফাইলে (WAL Log) পরিবর্তনের বিস্তারিত বিবরণ লিখে ডিস্কে ফ্ল্যাশ করতে হবে।

এটি অনেকটা বিমানের ব্ল্যাকবক্সের মতো। যদি মূল ডেটা ফাইলে লেখার আগেই বিদ্যুৎ চলে যায় বা ওএস ক্র্যাশ করে, তবে সার্ভার রিস্টার্ট হওয়ার সময় পোস্টগ্রেসকিউএল এই WAL লগটি নতুন করে রিড করে (WAL Replay) অসমাপ্ত কাজ সম্পন্ন করে ডেটাবেসকে শতভাগ অক্ষত অবস্থায় ফিরিয়ে আনে। শুধু তাই নয়, প্রোডাকশনে প্রাইমারি সার্ভার থেকে স্ট্যান্ডবাই সার্ভারে রিয়েল-টাইম ডেটা রেপ্লিকেশন করার জন্যও এই WAL লগ স্ট্রিম আকারে ব্যবহৃত হয়।

৮. প্রোডাকশন রিয়েলিটি: কানেকশন পুলিং (PgBouncer) ও ব্যাকআপ

প্রোডাকশন গ্রেড পোস্টগ্রেস সার্ভার পরিচালনা করতে আরও দুটি বাস্তব বিষয় লক্ষ্য রাখা জরুরি:

1. কানেকশন পুলিং (Connection Pooling): পোস্টগ্রেসকিউএল প্রতিটি ক্লায়েন্ট কানেকশনের জন্য আলাদা ওএস প্রসেস তৈরি করে, যা যথেষ্ট মেমোরি কনজিউম করে। তাই হাজার হাজার কনকারেন্ট কানেকশন সরাসরি ডাটাবেসে না পাঠিয়ে মাঝখানে PgBouncer-এর মতো কানেকশন পুলার ব্যবহার করা শিল্পের আদর্শ মানদণ্ড।
2. রেপ্লিকেশন ব্যাকআপ নয়: রেপ্লিকেশন ভুল ডেটাও হুবহু নকল করে। আপনি ভুলবশত একটি টেবিল ড্রপ করলে স্ট্যান্ডবাই সার্ভার থেকেও তা মুহূর্তে মুছে যাবে। তাই ডেটা সুরক্ষায় নিয়মিত Point-in-Time Recovery (PITR) ও ফিজিক্যাল ব্যাকআপ গ্রহণ করা বাধ্যতামূলক।

সাধারণ জিজ্ঞাসা (FAQ)

প্রশ্ন: MVCC থাকলে কি PostgreSQL-এ কোনো লক (Lock) ব্যবহৃত হয় না?
উত্তর: এটি একটি ভুল ধারণা। পোস্টগ্রেসকিউএল লক অবশ্যই ব্যবহার করে। টেবিল স্কিমা অল্টার করা, একই রো-তে কনফ্লিক্টিং আপডেট ইত্যাদি জায়গায় কড়া লক প্রযোজ্য হয়। তবে MVCC সাধারণ রিড এবং রাইট অপারেশনের মাঝের অপ্রয়োজনীয় অপেক্ষাকে নাটকীয়ভাবে কমিয়ে দেয়।

প্রশ্ন: VACUUM FULL এবং সাধারণ VACUUM-এর মধ্যে পার্থক্য কী?
উত্তর: সাধারণ VACUUM টেবিলের ডেড টাপল মুছে ভেতরের ফাঁকা স্পেস নতুন রো লেখার জন্য প্রস্তুত করে, কিন্তু অপারেটিং সিস্টেমকে ডিস্ক ফেরত দেয় না। অন্যদিকে VACUUM FULL পুরো টেবিলটি নতুন করে রি-রাইট করে অতিরিক্ত ডিস্ক স্পেস অপারেটিং সিস্টেমকে ফিরিয়ে দেয়, তবে এটি পুরো টেবিলে এক্সক্লুসিভ লক বসিয়ে সব রিড-রাইট সাময়িক বন্ধ করে দেয়।

প্রশ্ন: রিয়েল অ্যাপ্লিকেশনে কীভাবে বুঝব কোয়েরি অপ্টিমাইজড হয়েছে কিনা?
উত্তর: কোয়েরির পূর্বে EXPLAIN (ANALYZE, BUFFERS) লিখে রান করলে প্ল্যানারের এক্সিকিউশন টাইম, ব্যবহৃত ইন্ডেক্স এবং ডিস্ক বাফার হিট অনুপাত পরিষ্কার দেখা যায়।

অফিশিয়াল তথ্যসূত্র ও ডকুমেন্টেশন

• পোস্টগ্রেসকিউএল অফিশিয়াল ডকুমেন্টেশন: postgresql.org/docs
• পোস্টগ্রেসকিউএল ইন্টারনালস ম্যানুয়াল: interdb.jp/pg
• ব্রুস মমজিয়ান (Bruce Momjian) পোস্টগ্রেস আর্কিটেকচার পেপারস: momjian.us/writings/pgsql
• এসিএম সিগমড ডেটাবেস সিস্টেমস রিসার্চ: sigmod.org