PostgreSQL Internals Lab

কেন এই প্রজেক্ট

buffer-pool, mvcc, query-optimization লেসনে আপনি mechanism শিখেছেন; এই lab-এ সেগুলোর অপরাধস্থল ঘুরবেন। PostgreSQL সুন্দরভাবে নিজের ভেতরটা খুলে দেয় — buffer-এ কী আছে, page-এ কোন tuple কার xmin-এ জন্ম, planner কেন ভুল রাস্তা নিলো — শুধু জিজ্ঞেস করতে জানলেই হয়।

আসল লক্ষ্য দক্ষতা দুটো: এক, evidence থেকে কারণ বের করা — “slow” বললে হয় না, BUFFERS-এর সংখ্যা পড়ে বলতে হবে কেটে কোথায়; দুই, planner-কে অবাক করা — statistics কীভাবে মিথ্যা বলানো যায় আর তার ফল কেমন, নিজে ঘটিয়ে দেখা। এই দুটোই production debugging-এর রোজকার কায়দা।

ধাপে ধাপে

১. Lab environment — docker-compose

services:
  pg:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: lab
    command: >
      postgres -c shared_buffers=256MB
               -c track_io_timing=on
               -c autovacuum_naptime=1s
               -c log_autovacuum_min_duration=0
    ports: ["5433:5432"]
    volumes: ["./lab:/lab"]

autovacuum_naptime=1s ইচ্ছাকৃত — observation window-তে vacuum কাজ করতে দেখতে হবে, production-এর ধৈর্য ধরার দরকার নেই। track_io_timing=on দিলে EXPLAIN-এ I/O time-ও আসে। প্রথম কাজ: seed script চালিয়ে দুটো table বানান — orders (১M rows) আর churn (update-heavy ছোট table)।

২. Guided tour — pg_buffercache আর pg_stat views

CREATE EXTENSION pg_buffercache;

SELECT c.relname,
       count(*) AS buffers,
       round(count(*) * 8 / 1024.0, 1) AS mb,
       round(avg(b.usagecount), 2) AS avg_usage
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY 1 ORDER BY 2 DESC LIMIT 10;

Worksheet প্রশ্ন: orders-কে seq scan করার আগে-পরে buffer count কেমন বদলায়? shared_buffers ছোট করে (64MB) ১M-row scan চালালে eviction চেইন দেখা যায় কি? pg_statio_user_tables-এ heap_blks_hit vs read — কোন table সবসময় ডিস্কে ছুটে যায়? উত্তরগুলো সংখ্যাসহ নোটে রাখুন।

৩. EXPLAIN (ANALYZE, BUFFERS) forensics

পাঁচটা exercise, প্রতিটার plan save করে নিজের diagnosis লিখুন:

#	Setup	খুঁজে বের করুন
1	WHERE status='X' matches 2%	Planner index নিলো কেন? rows estimate কত ছিল?
2	Same, but matches 60%	Bitmap heap scan-এ ফিরে গেল কেন?
3	Sort on 1M rows	Sort Method কী? external merge-এ গেল memory bump-এ কী হয়?
4	Nested loop with bad estimate	Rows Removed by Filter — misestimate কোথা থেকে?
5	Index-only scan	Heap fetches=0 কেন? visibility map all-visible bit দেখুন

EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;

Reading habit গড়ুন: actual vs estimated rows-এর ratio ৩× ছাড়ালে সেখানেই গল্প আছে; Buffers: shared read মানে ডিস্ক, hit মানে cache — দুটোর অনুপাতই performance-এর প্রথম বাক্য।

৪. pageinspect — tuple autopsy

MVCC lesson-এর theory এখানে শবাদ হয়:

CREATE EXTENSION pageinspect;

UPDATE churn SET balance = balance + 1 WHERE id = 7;

SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask::bit(16) AS flags
FROM heap_page_items(get_raw_page('churn', 0))
WHERE lp <> 0;

autopsy checklist: একই logical row-এর দুই version পাশাপাশি দেখবেন — পুরনোটার t_xmax set, নতুনটার t_ctid self-pointing। t_infomask-এ bit decode করুন (HEAP_XMAX_COMMITTED, HEAP_HOT_UPDATED)। VACUUM চালিয়ে আবার পড়ুন — dead line গুলো lp reuse হয়ে গেছে।

Checkpoint: HOT chain খুঁজুন — same-page update-এ new tuple-এ HEAP_HOT_UPDATED flag, index entry না বাড়ার প্রমাণ pg_relation_size index-এর উপরে।

৫. Dead-tuple tracking + autovacuum window

CREATE TABLE churn(id int primary key, balance numeric);

-- ১ সেকেন্ড অন্তর স্ন্যাপশট লুপ:
SELECT n_live_tup, n_dead_tup,
       n_tup_upd, n_tup_hot_upd, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'churn';

Update burst চালান আর প্রতি স্ন্যাপশট CSV-তে append করুন। Graph এঁকে দেখুন: n_dead_tup পাহাড়ে ওঠে, autovacuum threshold ছুঁলে নামে, last_autovacuum টাইমস্ট্যাম্প লাফায়। এবার ALTER TABLE churn SET (autovacuum_vacuum_scale_factor = 0.02); — trigger point কমলো, graph-এ কী বদলালো? এই একটা observation window-ই autovacuum tuning-এর পুরো mental model দেয়।

৬. Planner traps — findings template

Trap 1 — correlated columns: city='Dhaka' AND zip IN (...) — আলাদা statistics দুটো selectivity গুণ করে ১ row ভাবে, বাস্তবে ৫০k:

CREATE STATISTICS s_city_zip (dependencies) ON city, zip FROM customers;
ANALYZE customers;

Trap 2 — cost constants: SSD-জাতীয় storage-এ random_page_cost 4→1.1 নামালে একই query-র plan flip করে কি না দেখুন:

SET random_page_cost = 1.1;
EXPLAIN SELECT ... ;   -- আগের plan-এর সঙ্গে diff

Findings template (প্রতি trap-এ এক file, repo-তে commit করুন):

## Trap: correlated columns
- Hypothesis: ...
- Experiment: ... (setup, queries)
- Evidence: estimated=X actual=Y, plan before/after
- Conclusion: extended stats কী ঠিক করলো, কী করলো না
- Production echo: কোন symptom-এ এই trap সন্দেহ করব

নিজেকে চ্যালেঞ্জ করুন

1. pg_stat_statements দিয়ে workload-level top-5 query খুঁজে প্রতিটার plan fix
2. Bloat measurement — pgstattuple দিয়ে dead-tuple percentage বনাম নিজের pg_stat graph
3. Freeze anatomy — very old xmin force-freeze করে infomask bit দেখুন
4. VACUUM (ANALYZE) vs autovacuum-এর statistics difference মাপুন
5. Lock queue observation — pg_locks-এ ACCESS EXCLUSIVE wait chain বানিয়ে দেখুন
6. Replication slot leak demo — abandoned slot-এ WAL retention কেমন বাড়ে (lab-only)

যা এখানে শিখলেন, পরে যেখানে লাগবে

এখানে	পরে
EXPLAIN (ANALYZE, BUFFERS) reading	প্রতিটা slow-query ticket-এর প্রথম অস্ত্র
Tuple autopsy skill	MVCC bug report-এ evidence জমানো, bloat বোঝা
Autovacuum mental model	Production tuning — scale factor আর naptime আর মুখস্থ নয়
Statistics distrust	Level 9 distributed planner-এ misestimate-এর একই গল্প, বড় মঞ্চে
Findings template	Postmortem culture — hypothesis/evidence/conclusion লেখার অভ্যাস