পাঠ ০৮ · ২৯-এর মধ্যে · মডিউল ২

Home / AI Courses / Data Engineering / PostgreSQL

PostgreSQL — open-source DB

PostgreSQL — the data engineer's favourite database

৭ মিনিট পড়া মাঝারি · Intermediate SQL কোডসহ

এই পাঠে যা শিখবেন

PostgreSQL কী, MySQL/Oracle থেকে কীভাবে আলাদা — DE-রা কেন এটি বেছে নেন
ACID, MVCC, WAL — যে তিনটি ধারণা PostgreSQL-কে production-ready বানায়
psql দিয়ে connect, JSONB ও extension (PostGIS, pg_partman) ব্যবহার
Index types ও EXPLAIN ANALYZE — slow query কীভাবে fix করবেন

১ · PostgreSQL কী, কেন বিশেষ

PostgreSQL — প্রায়ই "Postgres" — একটি open-sourceOpen-sourceসফটওয়্যারের source code সবার জন্য উন্মুক্ত — যে কেউ পড়তে, পরিবর্তন করতে, distribute করতে পারে। PostgreSQL-এর license: PostgreSQL License (BSD-সদৃশ, business-friendly)। RDBMSRDBMSRelational Database Management System — table-ভিত্তিক ডেটাবেস, যেখানে সম্পর্ক (relations) ও SQL দিয়ে কাজ হয়। PostgreSQL, MySQL, Oracle, SQL Server — সবই RDBMS। যা ১৯৮৬ সাল থেকে UC Berkeley-তে শুরু হয়ে আজ ৩০+ বছর ধরে ক্রমাগত উন্নত হচ্ছে। MySQL-এর মতো জনপ্রিয়, কিন্তু feature-এ Oracle-এর কাছাকাছি — আর সম্পূর্ণ ফ্রি।

কেন DE-রা Postgres বাছেন

১) ACID — transaction guarantee, ব্যাংকিং-grade।
২) SQL standard compliance — window function, CTE, recursive query — সব আছে।
৩) JSONB — relational + document model একসাথে।
৪) Extension ecosystem — PostGIS, TimescaleDB, pgvector (AI), pg_partman।
৫) License-friendly — vendor lock-in নেই, AWS RDS/GCP Cloud SQL/Azure সবখানে চলে।

বাংলাদেশে bKash, Pathao, Chaldal, ও বেশিরভাগ fintech startup PostgreSQL-এ চলে। কারণ — money transfer-এ ACID ছাড়া বিকল্প নেই, আর JSON support API-driven যুগে অপরিহার্য।

২ · ACID — কেন এটাই ভিত্তি

ACIDACIDAtomicity, Consistency, Isolation, Durability — চারটি transaction property যা ১৯৮৩-তে Theo Härder ও Andreas Reuter দ্বারা সংজ্ঞায়িত। financial system-এর backbone। হলো চারটি transaction property:

Atomicity: transaction পুরোটা হবে, না হলে কিছুই হবে না। bKash-এ "A থেকে B-কে ৫০০ টাকা" — দু'টি update একসাথে commit হবে, না হলে rollback।
Consistency: constraint কখনো ভাঙবে না। balance ঋণাত্মক হতে পারবে না — যদি check constraint থাকে।
Isolation: একই সময়ে চলা transaction পরস্পরকে বিভ্রান্ত করবে না।
Durability: commit হয়ে গেলে — disk crash হলেও ডেটা থাকবে। PostgreSQL এটা করে WALWAL — Write-Ahead Logপ্রতিটি change প্রথমে log-এ লেখা হয়, তারপর data file-এ। crash হলে log replay করে recover। MySQL InnoDB-এও একই principle (redo log)। দিয়ে।

ভাবুন Pathao রাইডে gateway থেকে driver-কে টাকা দেওয়া হচ্ছে। যদি Pathao-র account থেকে ৩০০ টাকা কাটল, কিন্তু driver-এর account-এ যোগ হওয়ার আগে server crash — atomicity ছাড়া driver টাকা পাবে না, অথচ Pathao-র wallet থেকে কেটে গেছে। ACID guarantee দেয় — দু'টোই হবে, না দু'টোই হবে না।

৩ · MVCC — Postgres-এর গুপ্ত অস্ত্র

MVCCMVCC — Multi-Version Concurrency Controlপ্রতিটি row-এর একাধিক version রাখা হয় — তাই reader কখনো writer-কে block করে না। PostgreSQL, Oracle, SQL Server (snapshot isolation), MongoDB — সবাই MVCC-base। মানে — একই row একসাথে অনেকে পড়লে কেউ block হবে না। PostgreSQL প্রতিটি row-এর বিভিন্ন "version" রাখে; আপনি যখন SELECT করেন, আপনার transaction-এর start time অনুযায়ী যে version তখন valid ছিল — সেটাই দেখবেন।

পরিণাম: Daraz-এর product page-এ লক্ষ user একই সময়ে browse করছেন। backend admin price update করছে। MVCC-র জন্য — pricing update reader-দের block না করে চলে, আর reader-রা inconsistent দাম দেখেন না।

কিন্তু MVCC-র খরচও আছে — পুরোনো row-version গুলো এক সময় পরিষ্কার করতে হয়। এই কাজ VACUUM ও autovacuum daemon করে। DE-দের জন্য এটা monitor করা জরুরি — না হলে table "bloat" হয়ে slow হয়ে যায়।

৪ · psql দিয়ে শুরু — connect ও basic query

psql হলো PostgreSQL-এর command-line client। প্রতিটি DE-র প্রতিদিনের সঙ্গী। নিচে একটি bKash-style transaction table তৈরি ও query।

SQL · PostgreSQL

-- টার্মিনাল থেকে: psql -h localhost -U postgres -d bkash_demo
-- টেবিল তৈরি
CREATE TABLE transactions (
  txn_id      BIGSERIAL PRIMARY KEY,
  sender      VARCHAR(11) NOT NULL,         -- 01XXXXXXXXX
  receiver    VARCHAR(11) NOT NULL,
  amount      NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
  txn_type    VARCHAR(20) NOT NULL,         -- 'send_money', 'cash_in'
  status      VARCHAR(15) DEFAULT 'pending',
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  metadata    JSONB                          -- device, location, app version
);

-- কিছু sample data
INSERT INTO transactions (sender, receiver, amount, txn_type, metadata)
VALUES
  ('01711000001', '01911000002', 500.00, 'send_money',
   '{"device":"android","app":"v9.2","district":"Dhaka"}'),
  ('01811000003', '01711000004', 1500.00, 'cash_in',
   '{"agent_id":"AG-2241","district":"Chattogram"}');

-- ঢাকার সব send_money গত ২৪ ঘণ্টায়
SELECT sender, receiver, amount, created_at
FROM transactions
WHERE txn_type = 'send_money'
  AND metadata->>'district' = 'Dhaka'
  AND created_at >= NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

BIGSERIAL auto-increment ৬৪-bit integer। NUMERIC(12,2) — financial amount-এ float ব্যবহার করবেন না (rounding error)। JSONB — binary JSON, indexable। metadata->>'district' — JSON field থেকে text বের করার operator।

৫ · JSONB — relational ও document একসাথে

২০১৪ সালে PostgreSQL ৯.৪-এ JSONB যুক্ত হলো — game-changer। এখন আপনি একটি column-এ flexible nested data রাখতে পারেন এবং তাতে index দিতে পারেন। API থেকে আসা variable শেপের data-এর জন্য আদর্শ।

SQL · PostgreSQL

-- JSONB-তে index — dramatic speedup
CREATE INDEX idx_metadata_gin ON transactions USING GIN (metadata);

-- নির্দিষ্ট field-এ index (district দিয়ে যদি প্রায়ই query করেন)
CREATE INDEX idx_district
  ON transactions ((metadata->>'district'));

-- JSON containment query
SELECT COUNT(*) FROM transactions
WHERE metadata @> '{"district":"Dhaka"}';

-- nested update — শুধু একটি field বদলান
UPDATE transactions
SET metadata = jsonb_set(metadata, '{app}', '"v9.3"')
WHERE txn_id = 1;

GIN (Generalized Inverted Index) — JSONB-র জন্য বিশেষ designed। @> "contains" operator। jsonb_set() — immutable update; পুরো document overwrite না করে।

৬ · Index types ও কখন কোনটি

B-tree (default): equality ও range query — WHERE id = 5, WHERE created_at > ...।
Hash: শুধু equality — খুব নির্দিষ্ট use-case।
GIN: JSONB, array, full-text search। অনেক value-এ একই row map হলে।
GiST: geometric data, range types, full-text।
BRIN: বিশাল sequential table (timestamp-ordered logs) — কম জায়গায় বড় coverage।
Partial index: WHERE status = 'pending' — শুধু একটি condition-এর জন্য, ছোট ও দ্রুত।

Index ফ্রি না — প্রতিটি INSERT/UPDATE-এ index update হয়। লক্ষ row-এর OLTP table-এ ১০টি index দিলে write throughput অর্ধেক হতে পারে। DE-রা read pattern বুঝে index দেন — অন্ধভাবে নয়।

প্রতিটি query এই পথে যায়। Planner-ই সবচেয়ে গুরুত্বপূর্ণ — খারাপ plan মানেই slow query।

৭ · EXPLAIN ANALYZE — slow query-র ময়নাতদন্ত

EXPLAIN ANALYZE Postgres-এর সবচেয়ে শক্তিশালী tool। query কীভাবে চলছে, কত সময় লাগছে, কোন step-এ — সব দেখায়।

SQL · PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT sender, SUM(amount) AS total
FROM transactions
WHERE created_at >= '2026-05-01'
  AND status = 'success'
GROUP BY sender
ORDER BY total DESC
LIMIT 10;

/* সম্ভাব্য output:
Limit  (cost=12450.21..12450.23 rows=10 width=22)
  ->  Sort  (cost=12450.21..12500.05 rows=19936 ...)
        Sort Key: (sum(amount)) DESC
        ->  HashAggregate (cost=11800.10..12000.45 ...)
              ->  Seq Scan on transactions (cost=0.00..9500.00 rows=480000)
                    Filter: (created_at >= '2026-05-01' AND status='success')
Planning Time: 0.42 ms
Execution Time: 842.18 ms
*/

Seq Scan দেখলেই সতর্ক — পুরো table read হচ্ছে। সমাধান: CREATE INDEX ON transactions (created_at, status); — তখন Index Scan বা Bitmap Heap Scan-এ পরিবর্তিত হবে।

৮ · Extension ecosystem — Postgres-এর superpower

PostGIS: geographic queries — Pathao-তে "৫ কিমির মধ্যে কত driver" বের করা।
TimescaleDB: time-series data — IoT sensor, BTRC traffic logs।
pgvector: embedding store — RAG ও semantic search-এর জন্য (২০২৩+ AI boom-এ বিখ্যাত)।
pg_partman: automatic partition management — ১০০ কোটি+ row table-এ অপরিহার্য।
pg_stat_statements: কোন query কতবার চলেছে, গড়ে কত সময় — DBA-র dashboard।

AI boom-এ pgvector Postgres-কে নতুন জীবন দিয়েছে — আজ অনেক RAG application Pinecone/Weaviate বাদ দিয়ে পুরোনো Postgres-এই vector search চালাচ্ছে। একই DB-তে structured + vector — operational simplicity বিশাল।

ভাবনার প্রশ্ন

প্রতিটি প্রশ্ন নিজে কিছুক্ষণ ভাবুন — তারপর "→ উত্তর" চাপুন।

প্র ০১ bKash-এর মতো একটি system-এ — যেখানে সেকেন্ডে হাজার হাজার transaction হয় — PostgreSQL কি single instance-এ চলবে? কখন replication, sharding, বা migration দরকার?

এই প্রশ্ন প্রতি startup CTO-র sleepless রাত। উত্তর nuanced — "কখন migrate করব" ঠিক হলে scale-cliff এড়ানো যায়।

Single-instance Postgres কতদূর যায়:

আধুনিক hardware (৬৪-core, ১TB RAM, NVMe SSD)-এ — দিনে ১০-২০ কোটি transaction সম্ভব।
bKash পাবলিকলি বলেছে — peak load ঈদের সময়। তখনও তাদের core platform-এ Postgres/Oracle-class DB চলছে।
বেশিরভাগ Bangladeshi fintech (Nagad, Upay) ১-২ বছর single primary + read replica-এই survive করে।

স্কেলিং step-by-step:

Vertical scaling: CPU/RAM/SSD upgrade — সবচেয়ে সহজ। AWS db.r6i.32xlarge পর্যন্ত যান।
Read replicas: reporting query, analytics replica-এ পাঠান। primary-তে শুধু write। PostgreSQL streaming replication।
Connection pooling: PgBouncer ছাড়া ১০০০+ connection মানে স্রেফ মৃত্যু।
Partitioning: transactions table monthly partition — গত মাসের data কম access হলে dramatically দ্রুত।
Caching: Redis / Memcached — repeat read DB-তে আনাই উচিত নয়।
Sharding: Citus extension বা application-level (sender-এর first 2 digit দিয়ে shard)। complex।
NewSQL migration: CockroachDB, YugabyteDB — Postgres wire-compatible, horizontal scale।

কখন non-relational DB-তে যাবেন? — শুধু যখন access pattern relational না (event log, time-series, graph)। transaction history relational থাকা উচিত।

মূল উপলব্ধি: "Postgres scale করে না" — myth। Instagram ৩০০ মিলিয়ন user নিয়ে বহু বছর Postgres-এই চলেছে। Architecture ও tuning-ই আসল — DB switch নয়।

প্র ০২ JSONB column ব্যবহার করা সহজ — কিন্তু কখন এটা anti-pattern? "EAV mistake"-এ কীভাবে পড়ে যায় এবং কেন এটা long-term maintenance ভোগায়?

JSONB একটি ক্ষুরধার অস্ত্র — সঠিকভাবে ব্যবহার করলে অসাধারণ, ভুলভাবে ব্যবহার করলে আপনার schema "JSON soup"-এ পরিণত হয়।

JSONB কখন উপযুক্ত:

Truly variable শেপের data — webhook payload, third-party API response।
Optional metadata — যা প্রায়ই query করা লাগে না।
Sparse fields — ১০০টি possible field-এর মধ্যে কোনো row-তে ৫টি, কোনোটিতে ১০টি।
Audit log, raw event capture — শেপ পরে স্থির হবে।

কখন JSONB ভুল:

EAV anti-pattern — Entity-Attribute-Value। সব কিছু {"field":"value"}-তে ফেললে — type safety, foreign key, constraint কিছুই নেই।
Frequent JOIN দরকার — JSONB JOIN inefficient।
Aggregation প্রতিদিন (SUM, AVG) — column-store-এর মতো optimization পাবেন না।
Strict schema দরকার (compliance, audit)।

Long-term ব্যথা:

Schema documentation নেই — নতুন developer guess করে কী field থাকতে পারে।
Type confusion — কখনো "123" string, কখনো 123 number।
Migration কঠিন — পুরোনো data-এ field rename করা painful।
BI tool (Tableau, Metabase) JSONB ভাল handle করে না।

Best practice:

"Hot" fields যা প্রায়ই query করেন — column করুন।
"Cold" optional metadata — JSONB।
JSONB-এ schema validation করুন application layer-এ (Pydantic, Zod)।
JSON schema ডকুমেন্ট লিখে রাখুন repo-তে।

মূল কথা: JSONB MongoDB-র বিকল্প না — relational schema-র extension। flexibility ও discipline দু'টোই দরকার।

প্র ০৩ একটি query আগে ১০০ms-এ চলত, আজ ৮ সেকেন্ড নিচ্ছে — table বা code কিছু বদলায়নি। Postgres-এ কী কী কারণ হতে পারে এবং কীভাবে diagnose করবেন?

এই scenario প্রতিটি DE-র career-এ আসে। সমস্যা প্রায়ই subtle — debugging methodical হতে হবে।

সম্ভাব্য কারণ:

Statistics outdated: Postgres planner statistics ব্যবহার করে। ANALYZE অনেকদিন না চললে — পুরোনো cardinality estimate, খারাপ plan। Solution: ANALYZE transactions;
Plan flip: data distribution বদলালে planner হঠাৎ Index Scan ছেড়ে Seq Scan বাছতে পারে। EXPLAIN ANALYZE দিয়ে compare।
Table bloat: অনেক UPDATE/DELETE হলে dead row জমে — VACUUM না হলে table আকারে বড়, scan slow। SELECT * FROM pg_stat_user_tables WHERE relname='transactions'; দেখুন n_dead_tup।
Index bloat: index-ও bloated হয়। REINDEX CONCURRENTLY।
Connection pool exhaustion: server actually fast — কিন্তু connection wait। PgBouncer metrics দেখুন।
Lock contention: অন্য transaction lock ধরে আছে। pg_stat_activity, pg_locks।
Disk full বা I/O saturated: iostat, AWS CloudWatch।
Cache eviction: অন্য query buffer cache ভরে ফেলেছে; এই table আর memory-তে নেই। shared_buffers tune।
Cron job interference: একই সময়ে heavy reporting job চলছে কি?
Hardware degradation: SSD failing, RAID rebuild।

Diagnostic playbook:

প্রথমে EXPLAIN (ANALYZE, BUFFERS) — plan বদলেছে কিনা দেখুন।
pg_stat_statements — সবচেয়ে slow query top ১০।
pg_stat_activity — runtime এ কী চলছে, কী wait করছে।
Server metrics — CPU, IO wait, swap usage।
Application log — connection reset, timeout pattern।

মূল কথা: "code change হয়নি" মানে "কিছু change হয়নি" নয় — data, statistics, hardware, neighbour query — সব observable। DE-র সবচেয়ে বড় skill — methodical observability।

প্র ০৪ Daraz-এর e-commerce platform-এ আপনি DE। MySQL থেকে PostgreSQL-এ migration plan করতে বললে — কী step, কী risk, কী rollback strategy?

বড় production database migration — DE careers-এর সবচেয়ে high-stakes কাজ। ভুল হলে — পুরো business down।

কেন migrate করতে চাইতে পারেন:

JSONB, advanced indexing, CTE — MySQL-এ যেগুলো দুর্বল।
pgvector (recommendation embedding)।
Better SQL standard compliance — analytics team-এর জন্য।
License (MySQL Oracle-এর হাতে; কেউ কেউ MariaDB-তে যান)।

Migration steps:

Audit: MySQL-এর কোন feature ব্যবহার হচ্ছে — stored procedure, trigger, AUTO_INCREMENT, ENUM? প্রতিটির Postgres equivalent ম্যাপ।
Schema conversion: pgloader বা AWS DMS schema convert। ENUM → check constraint, AUTO_INCREMENT → SERIAL, TINYINT(1) → BOOLEAN।
Application code: ORM ভাল হলে minimal change। raw SQL হলে — date function, LIMIT/OFFSET, string concat — সব check।
Dual-write phase: production-এ application দু'টি DB-তে write করুক — synchronization verify।
Backfill: historical data Postgres-এ copy। AWS DMS / pgloader।
Read traffic shift: ১% → ১০% → ৫০% → ১০০% read Postgres-এ।
Write cutover: short maintenance window — write Postgres-এ। MySQL read-only।
Stabilization: ৭-১৪ দিন MySQL hot standby।
Decommission: MySQL backup নিয়ে retire।

Risk:

Data type mismatch: MySQL-এর "0000-00-00" date Postgres reject করে।
Performance surprise: MySQL-এ দ্রুত query Postgres-এ slow — different query planner।
Tooling gap: existing dashboard, alert, backup script সব update দরকার।
Team skill: Postgres ops MySQL থেকে আলাদা — VACUUM, autovacuum tuning শেখা লাগবে।

Rollback strategy:

Cutover-এর আগে MySQL একদম sync — প্রয়োজনে ১ ঘণ্টায় ফেরত।
Cutover-এর পর CDC দিয়ে Postgres → MySQL replicate — ৭২ ঘণ্টা।
Beyond ৭২h — replay অসম্ভব হয়; forward-only।

মূল কথা: বড় DB migration — ৩-৬ মাসের project, dedicated team, প্রচুর testing। "Big bang cutover" production-এ কখনো না — incremental, observable, reversible।

অনুশীলন

Schema লিখুন: Pathao-এর জন্য একটি rides table design করুন — driver, rider, pickup/drop GPS, fare, status, rating। Postgres syntax-এ।

CREATE TABLE rides (
  ride_id     BIGSERIAL PRIMARY KEY,
  driver_id   BIGINT NOT NULL REFERENCES drivers(id),
  rider_id    BIGINT NOT NULL REFERENCES riders(id),
  pickup      POINT NOT NULL,        -- (lon, lat)
  dropoff     POINT NOT NULL,
  distance_km NUMERIC(6,2) CHECK (distance_km >= 0),
  fare        NUMERIC(10,2) NOT NULL CHECK (fare >= 0),
  status      VARCHAR(20) NOT NULL DEFAULT 'requested',
  rating      SMALLINT CHECK (rating BETWEEN 1 AND 5),
  started_at  TIMESTAMPTZ,
  ended_at    TIMESTAMPTZ,
  metadata    JSONB,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX ON rides (created_at);
CREATE INDEX ON rides (status) WHERE status IN ('requested','ongoing');
CREATE INDEX ON rides USING GIST (pickup);  -- with PostGIS

Notice — partial index (status), spatial index (PostGIS), JSONB metadata। Production-grade।

Index বাছাই: এই query-র জন্য কী index উপযুক্ত? SELECT * FROM transactions WHERE metadata->>'app' = 'v9.2' ORDER BY created_at DESC LIMIT 100;
দু'টি options:
- Composite expression index: CREATE INDEX ON transactions ((metadata->>'app'), created_at DESC); — exact match-এর জন্য সেরা।
- GIN + B-tree: USING GIN (metadata) + (created_at DESC) — যদি অনেক ভিন্ন JSON query থাকে।
চলমান query pattern একটাই হলে — প্রথমটা ছোট ও দ্রুত। EXPLAIN ANALYZE দিয়ে compare করুন।
VACUUM ব্যাখ্যা: কেন PostgreSQL-এ VACUUM দরকার, VACUUM FULL কখন বিপজ্জনক?

VACUUM dead row (MVCC update/delete-এ জমা) চিহ্নিত করে — স্থান reuse করার জন্য। Statistics-ও update হয়। autovacuum daemon background-এ চালায়।

VACUUM FULL পুরো table rewrite করে disk space ফেরত দেয় — কিন্তু exclusive lock নেয়। মানে production-এ চলাকালীন কেউ read/write করতে পারবে না। বড় table-এ ঘণ্টাব্যাপী outage।

Production-এ FULL-এর বিকল্প: pg_repack extension — lock ছাড়াই compaction।

এই পাঠে যা শিখবেন

১ · PostgreSQL কী, কেন বিশেষ

২ · ACID — কেন এটাই ভিত্তি

৩ · MVCC — Postgres-এর গুপ্ত অস্ত্র

৪ · psql দিয়ে শুরু — connect ও basic query

৫ · JSONB — relational ও document একসাথে

৬ · Index types ও কখন কোনটি

৭ · EXPLAIN ANALYZE — slow query-র ময়নাতদন্ত

৮ · Extension ecosystem — Postgres-এর superpower

ভাবনার প্রশ্ন

অনুশীলন

আরও পড়ুন · ABCL TECH-এ আপনার পরবর্তী পদক্ষেপ