mukles.
All posts
  • PostgreSQL
  • Database
  • Backend

PostgreSQL Internals, Part 2: MVCC আর VACUUM

UPDATE কেন পুরোনো row overwrite করে না, xmin/xmax আর snapshot দিয়ে কোন version visible হয়, আর dead tuple, VACUUM, autovacuum ও HOT update কীভাবে কাজ করে, সেটা এই পর্বে বোঝা যাবে।

Md Mukles Ali13 min read

এটা PostgreSQL Internals সিরিজের Part 2। Part 1 · Part 2 · Part 3 · Part 4 · Part 5

Part 1-এ আমরা দেখেছি PostgreSQL-এর table আসলে একটা heap, যেটা 8 KB-র page দিয়ে তৈরি, আর প্রতিটা page-এর ভেতরে tuple থাকে। এই পর্বে প্রশ্নটা হলো: একটা row যখন UPDATE বা DELETE হয়, তখন ওই tuple-এর কী হয়?

উত্তরটা PostgreSQL-এর সবচেয়ে গুরুত্বপূর্ণ design decision-গুলোর একটা: MVCC। আর MVCC বুঝলে VACUUM কেন দরকার, সেটাও আপনা থেকেই পরিষ্কার হয়ে যায়।

সমস্যাটা: reader আর writer একসাথে

ধরা যাক একটা transaction users table থেকে একটা row পড়ছে, আর ঠিক একই সময়ে আরেকটা transaction সেই row-টা update করছে।

Transaction A                  Transaction B
-------------                  -------------
SELECT user (id = 1)           UPDATE user (id = 1)

Database যদি row-টাকে জায়গাতেই overwrite করত, তাহলে দুটো পথ থাকত। হয় reader-কে writer শেষ হওয়া পর্যন্ত অপেক্ষা করতে হবে (lock), নয়তো reader অর্ধেক-লেখা বা এখনো commit না হওয়া data দেখে ফেলবে। প্রথমটা throughput মেরে ফেলে, দ্বিতীয়টা ভুল ফল দেয়।

PostgreSQL তৃতীয় একটা পথ নেয়: row overwrite না করে নতুন একটা version তৈরি করে। তাহলে reader পুরোনো version পড়তে থাকে, writer নতুন version লেখে, কেউ কাউকে আটকায় না।

MVCC: একই row-এর একাধিক version

MVCC = Multi-Version Concurrency Control। নামটাই আসল কথা বলে দেয়: একই logical row-এর একাধিক physical version একসাথে থাকতে পারে।

UPDATE users
SET name = 'Mukles Ali'
WHERE id = 1;

এই UPDATE-এর পর page-এ যা থাকে:

Page

Tuple A (OLD)                  Tuple B (NEW)
┌─────────────────┐            ┌──────────────────┐
│ id: 1           │            │ id: 1            │
│ name: Mukles    │            │ name: Mukles Ali │
└─────────────────┘            └──────────────────┘

দুটো tuple-ই physically disk-এ আছে। Application-এর চোখে row একটাই (id = 1), কিন্তু storage-এ তার দুটো version।

Reader ───────→ পুরোনো visible version পড়ে
Writer ───────→ নতুন version তৈরি করে

এ কারণেই PostgreSQL নিয়ে একটা পরিচিত কথা আছে: readers don't block writers, and writers don't block readers। তবে দুটো writer যদি একই row update করতে চায়, তখন row-level lock লাগে; একজনকে অপেক্ষা করতে হয়। সেটা Part 5-এ দেখব।

এখন প্রশ্ন হলো, দুটো version-ই যদি page-এ থাকে, PostgreSQL কীভাবে জানে কাকে কোনটা দেখাবে? উত্তর লুকিয়ে আছে tuple header-এ।

xmin আর xmax: Created By, Ended By

প্রতিটা transaction-এর একটা transaction ID থাকে, যাকে বলে XID। আর প্রতিটা tuple-এর header-এ (আমাদের data-র আগে) কিছু metadata থাকে, যার মধ্যে দুটো field সবচেয়ে গুরুত্বপূর্ণ:

Tuple
┌─────────────────────────┐
│ Tuple Header            │
│   xmin = 100            │
│   xmax = 0              │
│   ...other metadata     │
├─────────────────────────┤
│ id   = 1                │
│ name = Mukles           │
└─────────────────────────┘

নাম দুটো মনে রাখার সহজ উপায়:

xmin → Created By  (কোন transaction এই version তৈরি করেছে)
xmax → Ended By    (কোন transaction এই version শেষ করেছে)

xmin হলো যে transaction tuple-টা বানিয়েছে, আর xmax হলো যে transaction এটাকে DELETE বা UPDATE করে শেষ করেছে। xmax = 0 মানে এখনো কেউ এই version-কে শেষ করেনি।

UPDATE-এ ঠিক কী ঘটে

ধরা যাক Transaction 100 row-টা insert করেছিল:

xmin = 100   ← Transaction 100 তৈরি করেছে
xmax = 0     ← এখনো কেউ শেষ করেনি
name = Mukles

এবার Transaction 200 UPDATE চালাল। PostgreSQL দুটো কাজ করে:

                 UPDATE Transaction #200
                          │
              ┌───────────┴───────────┐
              ↓                       ↓
        OLD Tuple                 NEW Tuple
      ┌─────────────┐          ┌──────────────┐
      │ xmin = 100  │          │ xmin = 200   │
      │ xmax = 200  │          │ xmax = 0     │
      │ Mukles      │          │ Mukles Ali   │
      └─────────────┘          └──────────────┘
        পুরোনো করল               তৈরি করল

একই transaction দুই জায়গায় দেখা যাচ্ছে: পুরোনো tuple-এর xmax-এ, আর নতুন tuple-এর xmin-এ। তাই UPDATE-কে এভাবে ভাবা যায়:

UPDATE  ≈  পুরোনো version শেষ করা  +  নতুন version INSERT করা

Transaction 300 যদি আবার Mukles Ali → Md Mukles Ali করে, তাহলে তৃতীয় একটা tuple আসবে: দ্বিতীয়টার xmax = 300, তৃতীয়টার xmin = 300, xmax = 0।

একটা জিনিস খুব precise করে মনে রাখা দরকার: xmin/xmax logical row-এর না, প্রতিটা physical tuple version-এর নিজস্ব metadata। id = 1 একটা row, কিন্তু তার প্রতিটা version-এর নিজের xmin/xmax আছে। এ কারণেই update হলে tuple-এর CTID-ও বদলে যায় (Part 1 দ্রষ্টব্য)।

চাইলে এগুলো নিজের চোখেই দেখা যায়, কারণ xmin, xmax, ctid হলো hidden system column:

SELECT ctid, xmin, xmax, id, name FROM users WHERE id = 1;

দুটো transaction একই সময়ে কী দেখে:

sequenceDiagram
  participant A as Txn A (reader)
  participant DB as Heap page
  participant B as Txn B (writer, XID 200)
  A->>DB: snapshot নেয়
  B->>DB: UPDATE name = 'Mukles Ali'
  Note over DB: old tuple: xmax = 200<br/>new tuple: xmin = 200
  A->>DB: SELECT
  DB-->>A: old version (200 তার snapshot-এ নেই)
  B->>DB: COMMIT
  A->>DB: SELECT (একই snapshot, REPEATABLE READ)
  DB-->>A: এখনও old version
  Note over A,B: নতুন snapshot নিলে তবেই new version দেখা যাবে

DELETE

DELETE আরও সরল। DELETE কোনো নতুন tuple বানায় না, শুধু বিদ্যমান tuple-এর xmax-এ নিজের XID বসিয়ে দেয়। Tuple-টা page-এ থেকেই যায়; শুধু তাকে "শেষ" হিসেবে চিহ্নিত করা হয়।

DELETE (Transaction 400)

xmin = 200
xmax = 400   ← 400 এটাকে শেষ করেছে
name = Mukles Ali

Snapshot: কে কোন version দেখবে

শুধু xmin/xmax দেখে সিদ্ধান্ত নেওয়া যায় না। xmax = 200 লেখা থাকলেই পুরোনো tuple অদৃশ্য হয়ে যায় না, কারণ Transaction 200 হয়তো এখনো চলছে, বা abort হয়ে গেছে। PostgreSQL-কে আরও দুটো জিনিস জানতে হয়।

Snapshot হলো একটা নির্দিষ্ট মুহূর্তে নেওয়া database-এর transaction-অবস্থার ছবি। এতে মূলত লেখা থাকে: কোন XID-গুলো তখন commit হয়ে গেছে, আর কোনগুলো তখনো চলছিল (in progress)। Snapshot নেওয়ার পরে যা commit হয়, তা ওই snapshot-এর কাছে "এখনো হয়নি" হিসেবেই গণ্য।

xmin / xmax
     +
Transaction status (commit / abort / running)
     +
Snapshot
     ↓
Tuple visible?

সরল করে নিয়মটা এরকম। একটা tuple কোনো snapshot-এর কাছে visible হবে যদি:

  1. তার xmin transaction commit হয়ে থাকে এবং snapshot-এর চোখে সেই commit আগেই ঘটে গিয়ে থাকে (অথবা tuple-টা নিজের transaction-ই বানিয়েছে), এবং
  2. তার xmax হয় 0, অথবা xmax transaction abort হয়েছে, অথবা snapshot-এর চোখে সেটা এখনো commit হয়নি।

অর্থাৎ যে version-এর জন্ম snapshot-এর আগে আর মৃত্যু snapshot-এর পরে (বা কখনো না), শুধু সেটাই visible।

Uncommitted change কেউ দেখে না

Transaction 200 UPDATE করেছে কিন্তু এখনো COMMIT করেনি। এই সময় Transaction 300 পড়ল:

SELECT name FROM users WHERE id = 1;
OLD Tuple                    NEW Tuple
xmin = 100 (committed)       xmin = 200 (running)
xmax = 200 (running)         xmax = 0
      ✅ visible                  ❌ not visible
         ↓
     "Mukles"

নতুন tuple-এর xmin এখনো commit হয়নি, তাই সেটা অদৃশ্য। পুরোনো tuple-এর xmax এখনো commit হয়নি, তাই সেটা জীবিত। ফলে Transaction 300 দেখে Mukles, কোনো lock-এর জন্য অপেক্ষা না করেই। Transaction 200 নিজে অবশ্য নিজের লেখা Mukles Ali-ই দেখে।

200 commit করার পর যে নতুন snapshot নেওয়া হবে, তার কাছে ছবিটা উল্টে যায়: পুরোনো ❌, নতুন ✅, ফল Mukles Ali।

আর 200 যদি ROLLBACK করে? তাহলে নতুন tuple-এর xmin aborted, তাই সেটা কারো কাছেই কখনো visible হবে না; পুরোনো tuple-এর xmax aborted, তাই সেটা আবার সবার কাছে জীবিত। Rollback-এর জন্য PostgreSQL-কে কোনো data "undo" করতে হয় না। এটা MVCC-এর একটা সুন্দর side effect।

একটা transaction-এর ভেতরে একই query দুবার চালালে দ্বিতীয়বার নতুন snapshot নেওয়া হবে কি না, সেটা নির্ভর করে isolation level-এর ওপর (READ COMMITTED বনাম REPEATABLE READ)। সেই আলোচনা Part 5-এ।

Dead tuple: পুরোনো version-এর কী হয়?

UPDATE commit হয়ে গেল। পুরোনো Mukles tuple-টা কি এখনই মুছে ফেলা যায়?

না। ধরা যাক Transaction A আগেই শুরু হয়েছিল এবং তার snapshot-এ Mukles দেখা যায়:

Transaction A (BEGIN, snapshot নিয়েছে)  → "Mukles" দেখছে
Transaction B: UPDATE + COMMIT          → "Mukles Ali" তৈরি

PostgreSQL যদি এখনই OLD tuple মুছে দেয়:
Transaction A আবার পড়তে গেলে → তার version নেই ❌

A তখন তার consistent view হারাবে। তাই পুরোনো version তখনই মোছা যায়, যখন নিশ্চিত হওয়া যায় যে কোনো চলমান transaction-এর snapshot-এর আর এটাকে দরকার নেই। সেই অবস্থায় পৌঁছানো tuple-কে বলে dead tuple।

কিন্তু dead হওয়ার সাথে সাথেও PostgreSQL tuple-টা physically সরায় না। Page-এ সে জায়গা দখল করে বসে থাকে:

Page 0 — 8KB
┌──────────────────────────────┐
│ Tuple A → Rahim         ✅   │
│ Tuple B → Mukles        DEAD │
│ Tuple C → Karim         ✅   │
│ Tuple D → Mukles Ali    ✅   │
│ Free Space                   │
└──────────────────────────────┘

একটা row যদি বারবার update হয় (ধরা যাক একটা balance column), তাহলে প্রতিবার একটা করে dead version জমে:

V1 DEAD → V2 DEAD → V3 DEAD → V4 ✅

কেউ পরিষ্কার না করলে dead tuple জায়গা খেতে থাকে, page ভরে যায়, নতুন page লাগে, table বড় হয়, আর প্রতিটা scan-এ অপ্রয়োজনীয় page পড়তে হয়। এটা RAM-এর সমস্যা না, disk আর I/O-র সমস্যা।

একটা tuple version-এর জীবনচক্র:

stateDiagram-v2
  [*] --> Live: INSERT / UPDATE (xmin সেট)
  Live --> Ended: UPDATE বা DELETE (xmax সেট)
  Ended --> Dead: কোনো active snapshot আর দেখতে পায় না
  Dead --> Reusable: VACUUM
  Reusable --> [*]: নতুন tuple এই জায়গা পায়
  Ended --> Live: xmax-এর transaction rollback হলে

Long-running transaction-এর বিপদ

এখান থেকে একটা খুব practical শিক্ষা আসে। "কোনো চলমান snapshot-এর আর দরকার নেই" শর্তটার মানে, একটা অনেকক্ষণ খোলা থাকা transaction (বা idle in transaction connection) পুরো database-এর cleanup আটকে রাখতে পারে। সেই transaction শুরু হওয়ার পরে যত tuple dead হয়েছে, সবগুলোকে PostgreSQL রেখে দিতে বাধ্য, কারণ ওই পুরোনো snapshot হয়তো সেগুলো দেখতে চাইবে। Production-এ bloat-এর একটা বড় কারণ এটাই।

VACUUM: dead tuple পরিষ্কার করা

এই সমস্যার সমাধান হলো VACUUM।

VACUUM users;

VACUUM table-এর page-গুলো ঘুরে এমন dead tuple খুঁজে বের করে যেগুলো আর কারো দরকার নেই, আর:

  • index থেকে ওই dead tuple-গুলোর দিকে point করা entry সরিয়ে দেয়,
  • heap page-এ সেগুলোর জায়গা ফাঁকা করে reusable হিসেবে চিহ্নিত করে,
  • Free Space Map (FSM) update করে, যাতে পরের INSERT/UPDATE জানে কোন page-এ জায়গা আছে,
  • Visibility Map (VM) update করে, যেখানে লেখা থাকে কোন page-এর সব tuple সবার কাছে visible (এটা Index Only Scan-এর জন্য জরুরি, Part 3 দ্রষ্টব্য),
  • পুরোনো tuple-গুলোকে "freeze" করে, যাতে 32-bit XID counter ঘুরে আসলে (wraparound) কোনো সমস্যা না হয়।
Before
[LIVE][DEAD][DEAD][LIVE]
          ↓  VACUUM
After
[LIVE][FREE][FREE][LIVE]

VACUUM যা করে না

এখানে সবচেয়ে বেশি ভুল বোঝাবুঝি হয়। Normal VACUUM সাধারণত table file ছোট করে না; জায়গাটা OS-কে ফেরত না দিয়ে PostgreSQL-এর নিজের পরবর্তী ব্যবহারের জন্য রেখে দেয়। (একটা ব্যতিক্রম: table-এর একদম শেষের দিকের page-গুলো পুরো ফাঁকা হলে VACUUM সেগুলো কেটে ফেলতে পারে।)

ধরা যাক অনেক DELETE-এর পর:

Before VACUUM                      After normal VACUUM
table file: 100 GB                 table file: ~100 GB
├── 30 GB LIVE                     ├── 30 GB LIVE
└── 70 GB DEAD                     └── 70 GB REUSABLE

Disk-এ file প্রায় একই আকারের থাকে, কিন্তু ভেতরের 70 GB এখন নতুন INSERT/UPDATE-এর জন্য খালি। Table আর বাড়বে না যতক্ষণ এই জায়গা ভরাট না হয়।

VACUUM-এর সাধারণ lock (SHARE UPDATE EXCLUSIVE) SELECT, INSERT, UPDATE, DELETE কোনোটাকে আটকায় না, তাই এটা চলমান production-এ নির্ভয়ে চালানো যায়।

Autovacuum

Production-এ প্রতি সেকেন্ডে হাজারো UPDATE/DELETE হয়; কেউ হাতে হাতে VACUUM চালায় না। এই কাজটা করে autovacuum: একটা launcher process background-এ table-গুলোর পরিবর্তন নজরে রাখে, আর দরকার হলে worker process পাঠিয়ে VACUUM ও ANALYZE চালায়।

UPDATE / DELETE বাড়ছে
        ↓
dead tuple সংখ্যা threshold পার হলো
        ↓
autovacuum worker → VACUUM (+ ANALYZE)
        ↓
জায়গা reusable, statistics fresh

Default নিয়মে, একটা table-এর dead tuple যখন মোটামুটি 50 + 20% × মোট row ছাড়ায় (autovacuum_vacuum_threshold আর autovacuum_vacuum_scale_factor), তখন autovacuum সেটায় হাত দেয়। খুব বড় বা খুব update-heavy table-এ এই মান per-table কমিয়ে দেওয়া একটা সাধারণ tuning।

ANALYZE অংশটাও কম গুরুত্বপূর্ণ নয়: এটা table-এর statistics update করে, যেটা দেখে planner ঠিক করে Seq Scan না Index Scan সস্তা (Part 4 দ্রষ্টব্য)। Autovacuum বন্ধ করা প্রায় সবসময়ই ভুল সিদ্ধান্ত।

কোন table-এ কত dead tuple জমেছে আর শেষ কবে autovacuum চলেছে, দেখতে:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Table bloat আর VACUUM FULL

Autovacuum যদি তাল রাখতে না পারে (খুব বেশি write, ভুল settings, বা কোনো long-running transaction), অথবা একবারে বিশাল DELETE হয়, তাহলে table-এর physical আকার তার আসল data-র চেয়ে অনেক বড় হয়ে যায়:

Actual useful data
████████

Physical table
████████░░░░░░░░░░░░░░░
        ↑ dead / ফাঁকা জায়গা

একেই বলে table bloat। (Index-এরও একইভাবে bloat হয়।) ফল: বেশি disk, বেশি page পড়া, ধীর query।

জায়গাটা যদি সত্যিই OS-কে ফেরত দিতে হয়, তাহলে আছে VACUUM FULL:

VACUUM FULL users;
Before                              After VACUUM FULL
Page 1  [LIVE][FREE][FREE]          Page 1  [LIVE][LIVE][LIVE]
Page 2  [FREE][LIVE][FREE]
Page 3  [FREE][FREE][LIVE]

VACUUM FULL পুরো table-টা নতুন একটা file-এ নতুন করে লেখে (index-সহ), আর পুরো সময় table-এ ACCESS EXCLUSIVE lock ধরে রাখে, অর্থাৎ ওই সময় SELECT পর্যন্ত চলবে না। তাছাড়া নতুন copy লেখার জন্য সাময়িকভাবে বাড়তি disk space লাগে। তাই busy production table-এ এটা হুট করে চালানো যায় না; maintenance window লাগে, বা pg_repack-এর মতো tool দিয়ে online-এ কাজটা করা হয়।

চারটা ধারণা আলাদা করে রাখা যাক:

Dead tuple   → পুরোনো version, যেটা আর কোনো snapshot-এর দরকার নেই
VACUUM       → dead tuple-এর জায়গা reusable করে, file সাধারণত ছোট হয় না
Autovacuum   → background-এ আপনা থেকে VACUUM/ANALYZE চালায়
VACUUM FULL  → table rewrite + compact, disk ফেরত দেয়, কিন্তু exclusive lock

HOT update: index-এর বাড়তি কাজ এড়ানো

MVCC-এর একটা লুকানো খরচ আছে। Index-এ থাকে tuple-এর location (CTID)। UPDATE নতুন tuple বানায়, নতুন CTID-তে। তাহলে কি প্রতিটা UPDATE-এ table-এর সব index-এ নতুন entry লিখতে হবে, এমনকি যে column-এর ওপর index সেটা না বদলালেও?

CREATE INDEX idx_users_email ON users(email);

UPDATE users SET name = 'Mukles Ali' WHERE id = 1;  -- email বদলায়নি
INDEX                          HEAP (Page 0)
mukles@gmail.com ──→ (0,2)     (0,2) OLD: Mukles
                               (0,4) NEW: Mukles Ali

Index এখনো পুরোনো tuple (0,2)-এর দিকে point করছে। শুধু এর জন্য index-এ mukles@gmail.com → (0,4) নতুন entry লেখা অপচয়, কারণ email তো একই আছে।

PostgreSQL এখানে একটা optimization করে: HOT (Heap-Only Tuple) update। নতুন index entry না লিখে পুরোনো tuple থেকে নতুন tuple পর্যন্ত page-এর ভেতরে একটা chain বানিয়ে দেয়:

B-Tree Index
email → (0,2)
          │
          ▼
       OLD tuple (0,2)
          │ HOT chain
          ▼
       NEW tuple (0,4)

Lookup করার সময় PostgreSQL index থেকে (0,2)-তে যায়, সেখান থেকে chain ধরে নতুন version-গুলোতে যায়, তারপর snapshot দিয়ে ঠিক করে কোন version visible:

Index      → heap-এ ঢোকার starting point দেয়
HOT chain  → একই row-এর version-গুলো জোড়ে
MVCC       → কোন version visible সেটা ঠিক করে

HOT নতুন tuple তৈরি হওয়া বন্ধ করে না; নতুন tuple-এর জন্য index maintenance এড়ায়। নামও সেখান থেকে: নতুন version শুধু heap-এ আছে, index-এ তার কোনো entry নেই।

PostgreSQL প্রতিটা UPDATE-এ এভাবে সিদ্ধান্ত নেয়:

flowchart TD
  U(["UPDATE"]) --> C1{"কোনো indexed column বদলেছে?"}
  C1 -->|হ্যাঁ| N["Normal update<br/>প্রতিটা index-এ নতুন entry"]
  C1 -->|না| C2{"একই page-এ জায়গা আছে?"}
  C2 -->|না| N
  C2 -->|হ্যাঁ| H["HOT update<br/>একই page-এ নতুন version,<br/>index ছোঁয়া হয় না"]

HOT কখন সম্ভব

দুটো শর্ত একসাথে পূরণ হতে হবে:

  1. UPDATE-এ কোনো indexed column-এর মান বদলানো যাবে না। email indexed হলে SET email = 'new@gmail.com' HOT হবে না, কারণ index-এ নতুন key লিখতেই হবে।
  2. নতুন tuple version-টা পুরোনোটার একই heap page-এ fit করতে হবে। HOT chain page-এর বাইরে যায় না। Page ভরা থাকলে নতুন tuple অন্য page-এ যায়, আর তখন indexed column না বদলালেও সব index-এ নতুন entry লাগে।

HOT হলে লাভ: কম index write, কম index bloat, কম WAL, দ্রুত UPDATE। আরেকটা সুবিধা হলো HOT chain-এর dead version-গুলো পরে কেউ ওই page পড়ার সময়ই ("page pruning") সাফ হয়ে যেতে পারে, পুরো VACUUM-এর অপেক্ষা না করে।

fillfactor: page-এ জায়গা রেখে দেওয়া

HOT-এর দ্বিতীয় শর্তটাই প্রায়ই বাধা হয়: page যদি INSERT-এর সময়েই 100% ভরে যায়, তাহলে পরের UPDATE-এর নতুন version-এর জন্য একই page-এ জায়গা থাকে না।

fillfactor বলে দেয় INSERT করার সময় PostgreSQL একটা page কত শতাংশ পর্যন্ত ভরবে। Table-এর default হলো 100।

CREATE TABLE users (
  id bigint,
  name text,
  email text
) WITH (fillfactor = 80);
8KB Heap Page (fillfactor = 80)
┌────────────────────────┐
│ ~80%                   │
│ INSERT-এর tuples       │
├────────────────────────┤
│ ~20% free space        │
│ future UPDATE-এর জন্য  │
│ → HOT-এর chance বাড়ে  │
└────────────────────────┘

বাকি 20% কোনো "HOT-only" partition নয়। এর মানে শুধু এটুকু যে নতুন INSERT ওই জায়গা ব্যবহার করবে না; UPDATE-এর নতুন version সেখানে বসতে পারবে।

Trade-off আছে:

fillfactor = 100 → ঘন packed page, কম disk, কিন্তু UPDATE-এর জায়গা নেই
fillfactor = 80  → HOT-এর সুযোগ বেশি, কিন্তু একই data-য় বেশি page, বেশি scan I/O

তাই update-heavy table-এ (যেমন counter, status, updated_at প্রায়ই বদলায় এমন table) 70–90-এর মতো fillfactor ভালো কাজ দেয়; প্রায় শুধু INSERT হয় এমন table-এ 100-ই ঠিক। ALTER TABLE ... SET (fillfactor = 80) শুধু পরে লেখা page-এ কাজ করে; পুরোনো page-এ প্রয়োগ করতে table rewrite লাগে। HOT কতটা হচ্ছে দেখা যায় pg_stat_user_tables-এর n_tup_hot_upd আর n_tup_upd তুলনা করে।

নিজেকে যাচাই করো

  1. Transaction 200 একটা row update করেছে কিন্তু commit করেনি। পুরোনো tuple-এর xmax আর নতুন tuple-এর xmin কত, আর অন্য একটা transaction কোন version দেখবে?
  2. 100 GB-র একটা table থেকে 70% row DELETE করে normal VACUUM চালানো হলো। Disk-এ file-এর আকার কত হবে?
  3. email-এ index আছে। UPDATE users SET name = ... কি সবসময় HOT update হবে?

উত্তর: (1) দুটোই 200; অন্য transaction পুরোনো committed version দেখবে, কারণ নতুন tuple-এর xmin এখনো commit হয়নি। (2) মোটামুটি 100 GB-ই থাকবে; ভেতরের ~70 GB reusable হবে, OS ফেরত পাবে শুধু VACUUM FULL (বা pg_repack) করলে। (3) না; indexed column না বদলালেও নতুন version একই page-এ fit না করলে HOT হবে না, আর name নিজে কোনো index-এ থাকলেও হবে না।

সারসংক্ষেপ আর পরের পর্বে

এক লাইনে পুরো গল্প: PostgreSQL UPDATE-এ পুরোনো tuple overwrite করে না, কারণ চলমান transaction-এর snapshot-এর জন্য পুরোনো version দরকার হতে পারে; নতুন version তৈরি করে, xmin/xmax আর snapshot দিয়ে ঠিক করে কে কোনটা দেখবে, আর পুরোনো version নিরাপদ হলে VACUUM তার জায়গা reusable করে। HOT আর fillfactor এই পুরো প্রক্রিয়াকে index-এর দিক থেকে সস্তা করে।

আমরা কয়েকবার index-এর নাম নিয়েছি: CTID, B-Tree, visibility map, Index Only Scan। Part 3-এ দেখব index ভেতরে কীভাবে কাজ করে, কখন Index Scan, কখন Bitmap Scan, আর composite index-এ column-এর ক্রম কেন গুরুত্বপূর্ণ।

Working on something similar?

I'm happy to talk through architecture, AI features or production issues.

Email me →More posts