কেন জানবো?
তুমি INSERT INTO লিখলে
ভেতরে আসলে কি ঘটে?
একটা line code লেখো — PostgreSQL এর ভেতরে চলে ৭টা step। এগুলো না জানলে performance,
crash, data loss বুঝতে পারবে না।
👤
Client
→
🔍
Parser
→
🗂
Buffer
→
📝
WAL
→
👁
MVCC
→
💾
Disk
→
✅
Done
📌 আজকে শিখবো:
প্রতিটা ধাপ কি করে, কেন করে, এটা না থাকলে কি হতো — সব উদাহরণ সহ।
WAL কি? Shared Buffer কি? MVCC কি? Checkpoint কি? Snapshot কি? — সব এখানে।
Step 1
Client Connect করে — তারপর কি?
তুমি query পাঠালে প্রথমে PostgreSQL সেটা "পড়ে" বোঝে।
🔌 Connection কিভাবে হয়
① Client → TCP/IP দিয়ে connect
② Postmaster process দেখে
③ নতুন Backend Process fork করে
④ শুধু সেই process তোমার সাথে কথা বলে
প্রতি connection = আলাদা OS process
🔍 Parser কি করে
তুমি লিখলে:
SELECT * FROM
users WHERE id=5
Parser এটাকে ভাঙে:
SELECT → Operation
users → Table
id=5 → Condition
→ Parse Tree বানায়
❌ Parser না থাকলে?
Database raw text বুঝতো না। "SELECT" মানে কি, "WHERE" মানে কি — কিছুই জানতো
না। তুমি যা লিখতে তাই করতো — SQL Injection paradise! 😱
🗺 Planner/Optimizer — সবচেয়ে চালাক অংশ
"১০ লাখ row-এর মধ্যে id=5 খুঁজবো কিভাবে?"
Option A: সব row scan করো (Seq Scan) — ধীর
Option B: Index ব্যবহার করো (Index Scan) — দ্রুত
Planner statistics দেখে best plan বেছে নেয় → Query Plan বানায়
Step 2 — সবচেয়ে গুরুত্বপূর্ণ
Shared Buffer — PostgreSQL-এর RAM
Disk অনেক ধীর। তাই PostgreSQL প্রথমে RAM-এ data রাখে। এটাই Shared Buffer।
📚 Library analogy: Library-তে লক্ষ বই আছে। তুমি একটা বই চাইলে
Library Assistant shelf থেকে আনে (Disk read — ধীর)। কিন্তু আগে একবার আনা বই সে table-এ রেখে দেয়
(Buffer — দ্রুত)। পরে কেউ চাইলে table থেকেই দেয়।
Shared Buffer Pool — 8KB "Page" এ ভরা:
P.1
Clean
P.2
Dirty
P.3
Clean
P.4
Pinned
P.5
Dirty
—
Empty
P.6
Clean
—
Empty
■ Clean = Disk-এর মতোই
■ Dirty = পালটানো হয়েছে, disk-এ যায়নি
■ Pinned = এখন ব্যবহার হচ্ছে
■ Empty = ফাঁকা
❌ Buffer ছাড়া (প্রতিটা query disk থেকে):
Disk read: ~10ms
Buffer read: ~0.01ms
পার্থক্য: ১০০০ গুণ ধীর!
১০০০ user একসাথে → disk-এ ১০০০ I/O → server dead 💀
✅ Buffer সহ (Cache Hit):
Buffer hit rate 95%+ হলে
→ ১০০ query তে ৯৫টা RAM থেকে
→ শুধু ৫টা disk থেকে
→ ১০ লাখ user handle সম্ভব!
Step 3 — Crash Protection
WAL
— Write-Ahead Log
Database-এর সবচেয়ে গুরুত্বপূর্ণ নিরাপত্তা ব্যবস্থা।
"আগে Log লেখো, তারপর Data
পালটাও।"
📓 ডায়েরি analogy:
একজন accountant সব কাজের আগে ডায়েরিতে লেখেন: "৩টায় রহিমের account থেকে ৳৫,০০০ কাটবো।"
তারপর আসল ledger-এ কাটেন।
বিদ্যুৎ গেলে? ডায়েরি দেখে সব recover করেন।
WAL = সেই ডায়েরি।
WAL File Structure:
-- WAL Record এর মতো (simplified):
LSN: 0/1A2B3C4D ← Log Sequence Number (address)
xid: 748392 ←
কোন transaction এর কাজ
type: 'INSERT' ← কি করা হলো
relation: 'users' ← কোন table
data: { id:5, name:'রহিম' } ← কি data
prev_lsn: 0/1A2B3C40 ← আগের record এর link
❌ WAL ছাড়া crash হলে:
Buffer-এ data পালটানো হলো ✓
Disk-এ write হওয়ার আগে server crash 💥
Restart করলে — data গেছে ❌
→ বাংলাদেশ Bank থেকে সব transaction হারিয়ে গেলো!
✅ WAL সহ crash হলে:
WAL disk-এ আগেই লেখা ✓
Crash হলো 💥
Restart → WAL পড়ো → Replay করো ✓
→ কোনো data হারায়নি! দেশ বাঁচলো!
WAL এর ভেতরে
LSN
— WAL এর ঠিকানা ব্যবস্থা
LSN = Log Sequence Number। WAL file-এর প্রতিটা record-এর unique address।
📮 Post Office analogy:
চিঠি পাঠানো হলে Tracking Number দেওয়া হয়: TK-001, TK-002, TK-003...
হারিয়ে গেলে tracking দেখে বলা যায় "TK-004 এর পর যা যা আসেনি সব দাও।"
LSN = সেই Tracking Number।
WAL Sequence (Timeline):
→
LSN
0/1020
INSERT
users row
→
→
→
🔄 Crash Recovery কিভাবে কাজ করে:
① Restart → WAL পড়া শুরু
② LSN 0/1040 পর্যন্ত COMMIT হয়েছে → Apply করো ✓
③ LSN 0/1060 BEGIN হয়েছে কিন্তু COMMIT নেই → Rollback করো
④ Database ready — কোনো data হারায়নি!
💡 Replication-এও LSN ব্যবহার হয়
Primary server LSN 0/5000 পর্যন্ত। Replica LSN 0/4800 পর্যন্ত পেয়েছে।
Replica বলবে "0/4800 থেকে বাকি দাও।" — Replication এভাবে কাজ করে!
Write Operation
INSERT হলে ভেতরে কি হয়?
INSERT INTO users VALUES (5, 'রহিম')
— সম্পূর্ণ যাত্রা।
①
Transaction শুরু → xid পাও
PostgreSQL একটা unique Transaction ID (xid) দেয়: xid=748392। এই ID
দিয়ে সব track হবে।
②
WAL-এ আগে লেখো (Write-Ahead!)
Disk-এ WAL file-এ record লেখা হয়: "xid 748392 INSERT করতে চায়।" এটা
sequential write — অনেক দ্রুত।
③
Shared Buffer-এ Page খোঁজো বা Load করো
users table-এর Page Buffer-এ আছে? → সরাসরি। নেই? → Disk থেকে RAM-এ Load
করো। তারপর সেই Page-এ নতুন row লেখো।
④
Page "Dirty" হয়ে গেলো
Buffer-এর Page পালটানো হয়েছে কিন্তু Disk-এ এখনো যায়নি → এটাকে বলে
Dirty Page। এখনই disk-এ write করা হয় না।
⑤
COMMIT → WAL-এ COMMIT record লেখো
User `COMMIT` করলে WAL-এ COMMIT record লেখা হয়। এটা হলেই data
"permanently saved" — disk-এ না গেলেও! কারণ WAL দিয়ে recover করা যাবে।
⚠️ কেন এখনই Disk-এ write করা হয়
না?
Disk write ধীর, random। WAL write দ্রুত কারণ sequential। Dirty pages পরে Checkpoint-এ batch করে write হয় — এটাই efficiency-র রহস্য!
Concurrency Magic
MVCC
— একসাথে লক্ষ user, কোনো fight নেই
Multi-Version Concurrency Control — PostgreSQL-এর সবচেয়ে চালাক feature।
📸 Photo Album analogy:
রহিম Album দেখছেন — পুরনো ছবি (version 1)।
একই সময় সাবিনা নতুন ছবি add করছেন (version 2)।
রহিম কি blocked? না! তিনি version 1 দেখতে থাকেন। সাবিনা version 2
যোগ করেন।
MVCC = একই data-র multiple version রাখো।
একটা Row-এর Multiple Versions:
-- users table ভেতরে আসলে এভাবে থাকে:
xmin xmax name salary
─────────────────────────────────────────
748 751 'রহিম' 30000 ← OLD version (xmax=751 মানে 751
delete করেছে)
751 ∞ 'রহিম' 35000 ← NEW version (xmax=∞ মানে এখনো
valid)
xmin = কে বানিয়েছে
xmax = কে delete/update করেছে
xmax=∞ = এখনো জীবিত
❌ MVCC ছাড়া (Traditional Locking):
রহিম read করছেন → Row lock 🔒
করিম write করতে চায় → WAIT...
সাবিনাও read করতে চায় → WAIT...
→ সবাই queue-তে। Server অচল!
✅ MVCC সহ:
রহিম পুরনো version read করেন ✓
করিম নতুন version লেখেন ✓
সাবিনাও পুরনো version read করেন ✓
→ সবাই একসাথে, কোনো block নেই!
MVCC এর ভেতরে
Snapshot
— "সেই মুহূর্তের ছবি"
Transaction শুরু হওয়ার মুহূর্তে Database কোন অবস্থায় ছিল — সেটার snapshot নেওয়া হয়।
📸 Camera Snapshot analogy:
সন্ধ্যা ৬টায় তুমি room-এর ছবি তুললে। ছবিতে ৩টা chair আছে।
রাতে কেউ আরেকটা chair রাখলো।
তোমার ছবিতে তবুও ৩টাই থাকবে।
Snapshot = সেই মুহূর্তের Database-এর ছবি।
Snapshot কি কি রাখে:
-- Transaction T1 শুরুতে Snapshot:
xmin: 748 ← এর
আগের সব committed transaction দেখতে পাবে
xmax: 752 ← এর
পরের কোনো transaction দেখবে না
xip_list: [749, 750]
← এগুলো চলছে, এদের data দেখবে না
🔍 কোন version দেখবে — Visibility Check:
Row-এর xmin < snapshot.xmin → দেখতে পাবে (committed)
Row-এর xmin in xip_list → দেখবে না (এখনো চলছে)
Row-এর xmax < snapshot.xmin → deleted, দেখবে না
Row-এর xmax = ∞ → দেখতে পাবে (live row)
⚠️ Snapshot ছাড়া কি হতো?
তুমি report generate করছো। মাঝখানে কেউ data update করলো। তোমার report-এ আধা পুরনো আধা নতুন data
যেতো — Inconsistent Read! Snapshot দিয়ে তুমি সবসময় সেই মুহূর্তের
consistent data দেখো।
Disk Flush
Checkpoint — Dirty Pages-কে Disk-এ পাঠানো
Dirty pages RAM-এ থাকে। কিন্তু চিরকাল থাকে না — Checkpoint-এ Disk-এ যায়।
🏦 Bank Ledger analogy:
Bank teller সারাদিন scratch paper-এ হিসাব রাখেন (Buffer)।
সন্ধ্যায় সব হিসাব main ledger-এ লেখেন (Checkpoint)।
রাতে যদি scratch paper পুড়ে যায়? Main ledger আছে!
Checkpoint = সেই সন্ধ্যার Ledger Transfer।
Checkpoint Process:
RAM Buffer
Dirty Pages:
P.2, P.5, P.8...
→
Background Writer
Dirty pages
→ Disk লেখো
→
WAL Checkpoint
Record
LSN এখানে পর্যন্ত
সব disk-এ আছে
→
Recovery
Fast!
এর আগের WAL
আর দরকার নেই
🕐 কখন Checkpoint হয়?
• প্রতি ৫ মিনিটে (checkpoint_timeout)
• WAL ১৬MB পূর্ণ হলে
• Manual: CHECKPOINT;
• Database shutdown হলে
❌ Checkpoint ছাড়া কি হতো?
Crash recovery-তে শুরু থেকে WAL replay করতে হতো।
১ সপ্তাহের WAL = ১ সপ্তাহ restart সময়!
→ Database কখনো restart করা যেতো না!
Read Operation
SELECT হলে কিভাবে data আসে?
SELECT * FROM users WHERE id=5
— এর পুরো যাত্রা।
①
Snapshot নাও
Transaction শুরুতে current snapshot নেওয়া হয়। এই snapshot-এর বাইরের
কোনো data দেখাবে না।
②
Buffer Check — Cache Hit?
Cache Hit (95%+ ক্ষেত্রে): Page Buffer-এ আছে → RAM থেকে সরাসরি।
দ্রুত! ⚡
Cache Miss (বাকি ক্ষেত্রে): Buffer-এ নেই → Disk থেকে Page load
করো Buffer-এ, তারপর read।
③
Visibility Check (MVCC)
Page-এ অনেক row version থাকতে পারে (পুরনো, নতুন, deleted)। Snapshot
দিয়ে check করো — কোনটা এই transaction-এর দেখার কথা।
④
Result Return
Visible row গুলো collect করে Client-এ পাঠানো হয়। Done!
📊 Index Scan vs Sequential Scan
Index Scan: ১০ লাখ row, B-Tree Index-এ যাও → সরাসরি id=5 row →
৩টা page read
Seq Scan: Index নেই → ১০ লাখ row সব পড়ো → ১০,০০০ page read
EXPLAIN ANALYZE দিয়ে দেখা যায় কোনটা হচ্ছে!
🔢 8KB Page = Data এর ইউনিট
PostgreSQL data পড়ে page (block) হিসেবে, row হিসেবে না।
১ page = 8,192 bytes
১ page-এ ~100-200 টা row থাকে
Disk থেকে সবসময় পুরো page আসে।
MVCC এর পার্শ্বপ্রতিক্রিয়া
Dead Tuples এবং VACUUM
MVCC পুরনো version রাখে — কিন্তু কেউ না দেখলে এগুলো "garbage"। VACUUM পরিষ্কার করে।
🗑️ Dead Tuple কিভাবে তৈরি হয়:
রহিমের salary ছিল ৳৩০,০০০ (xmin=748, xmax=∞)
UPDATE করা হলো → পুরনো row (xmin=748, xmax=751) = Dead Tuple
নতুন row (xmin=751, xmax=∞) = Live Tuple
Dead Tuple তবুও table-এ জায়গা নিচ্ছে — bloat হচ্ছে!
❌ VACUUM না চললে:
১ মিলিয়ন UPDATE = ১ মিলিয়ন Dead Tuple
Table size: ১ GB → ১০ GB (bloat!)
Index-ও বড় হয় → Query ধীর
→ একসময় Transaction ID Wraparound! Database বন্ধ!
✅ VACUUM চললে:
Dead Tuples খুঁজে বের করে
কেউ দেখছে না confirm করে
সেই space reuse-এর জন্য mark করে
VACUUM FULL = ফাঁকা জায়গা OS-এ ফেরত দেয়
🤖 AutoVACUUM — স্বয়ংক্রিয় পরিষ্কার
PostgreSQL নিজেই background-এ AutoVACUUM চালায়।
Table-এর ২০% row dead হলে → automatically VACUUM শুরু।
তুমি সাধারণত manually চালাতে হয় না — কিন্তু জানতে হবে কেন চলছে!
💡 Transaction ID Wraparound (সবচেয়ে ভয়ের জিনিস):
xid সর্বোচ্চ ~২ বিলিয়ন পর্যন্ত। তারপর আবার শুরু থেকে।
VACUUM না চললে পুরনো xid-এর row নতুন মনে করবে → সব data invisible! Database
read-only mode-এ!
বড় production crash এভাবে হয়।
সম্পূর্ণ চিত্র
একটা INSERT এর পুরো যাত্রা
সব components একসাথে — কে কার পর কাজ করে।
1
Client → Query পাঠায়
TCP connection → Postmaster → Backend Process fork
2
Parser + Planner
SQL parse → Parse Tree → Query Plan বানানো
3
xid পাওয়া + Snapshot নেওয়া
Transaction শুরু → unique ID → database-এর এই মুহূর্তের ছবি
4
WAL-এ আগে লেখো (Sequential Disk Write)
Crash protection সবার আগে — data হারাবে না guarantee
5
Shared Buffer-এ Dirty Page তৈরি
RAM-এ data লেখা → দ্রুত → disk-এ এখনই না
6
COMMIT → WAL-এ COMMIT record → Client-কে "OK"
এই মুহূর্তে data "durably saved" — WAL disk-এ আছে
7
Background: Checkpoint → Dirty Pages → Disk
পরে background-এ Checkpoint হলে data actual disk file-এ যায়
সব কিছু এক জায়গায়
PostgreSQL-এর ৬টা Core Mechanism
📦 Shared Buffer
RAM-এর cache। Disk ১০০০গুণ ধীর — তাই আগে RAM-এ।
8KB Page হিসেবে কাজ করে। Cache hit rate বেশি হলে database দ্রুত।
RAM Cache
📝 WAL
Write-Ahead Log। Data পালটানোর আগে log লেখো।
Crash হলে log থেকে recover। Sequential write = দ্রুত। LSN = প্রতিটা record-এর address।
Crash Safety
👁 MVCC
একই row-এর multiple version। Reader writer-কে
block করে না। xmin/xmax দিয়ে version track। Snapshot দিয়ে consistent read।
Concurrency
💾 Checkpoint
Dirty pages → Disk। নিয়মিত না হলে recovery
সময় infinite। WAL-এ Checkpoint record রাখে। এর আগের WAL delete করা যায়।
Disk Sync
📸 Snapshot
Transaction শুরুতে database-এর ছবি। সেই
মুহূর্তের data দেখায়। পরে কেউ পালটালেও তুমি পুরনোটা দেখো। Isolation guarantee।
Isolation
🧹 VACUUM
MVCC Dead Tuples তৈরি করে। VACUUM পরিষ্কার করে।
AutoVACUUM স্বয়ংক্রিয়। না চললে Table Bloat এবং xid Wraparound crash!
Cleanup
🎯 এগুলো জানলে কি পারবে?
Query ধীর কেন → Buffer hit rate, Seq Scan check করতে পারবে
Disk full কেন → VACUUM না চলার কারণ বুঝবে
Data হারালো কেন → WAL configuration বুঝবে
Concurrent users handle → MVCC + Isolation level জানবে