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

Home / AI Courses / ডেটা ইঞ্জিনিয়ারিং / OLTP বনাম OLAP

OLTP বনাম OLAP

OLTP vs OLAP — transactional vs analytical workloads

৬ মিনিট পড়া শুরু · Beginner Workload তুলনা

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

OLTP ও OLAP-এর মৌলিক ব্যবহার ও query pattern
Row-store vs column-store — কেন একই query দু'টিতে আলাদা সময় নেয়
Normalization (3NF) ও denormalization (star schema) — কখন কোনটি
BD-র বাস্তব উদাহরণে — bKash, Daraz-এর সিদ্ধান্ত কাঠামো

১ · দু'টি ভিন্ন জগতের ডেটা কাজ

কল্পনা করুন একটি bKash transaction। User "Send Money" চাপ দিল — এক সেকেন্ডের মধ্যে balance check, debit, credit, log। এটি একটি OLTP ক্যোয়েরি — ছোট, দ্রুত, এক-দু'টি row নিয়ে কাজ।

এখন কল্পনা করুন বছরের শেষে CFO চান — "গত ১২ মাসে প্রতি জেলায় গড় cash-in size, top 5 agent কে?" এই query-তে কোটি কোটি row scan হবে, কয়েকটি column-এ aggregate। এটি OLAP।

একই database এই দু'টি কাজ ভালো করতে পারে না — কারণ তাদের performance trade-off সম্পূর্ণ ভিন্ন। তাই data engineer-এর প্রথম কাজ — এই দু'টি জগৎ আলাদা রাখা।

এক বাক্যে পার্থক্য

OLTP: অনেক ছোট transaction, হাজার-লাখ user concurrent, <১০ ms latency। কাজ — চালানো।
OLAP: কম query, প্রতিটি বিশাল scan, কয়েক সেকেন্ড-মিনিট latency ঠিক আছে। কাজ — বোঝা।

২ · OLTP — দ্রুত transaction

OLTPOLTP — Online Transaction Processingএকটি database workload pattern যেখানে অনেকে একসাথে ছোট ছোট পরিবর্তন (insert/update/delete) করেন। ACID guarantee, low latency, high concurrency — main goal। PostgreSQL, MySQL, Oracle traditional choice। ডেটাবেসের কাজ — ব্যবসা পরিচালনা করা। প্রতিটি query সাধারণত:

একটি বা কয়েকটি row পড়ে/লেখে।
সব column বা অধিকাংশ column টানে।
ACIDACIDAtomicity, Consistency, Isolation, Durability — transaction-এর চারটি guarantee। OLTP-র মূল ভিত্তি, যাতে money transfer-এ "অর্ধেক হলো" এমন না হয়। দিতে হয় — transaction-এ "অর্ধেক" ঘটে না।
Concurrency বেশি — হাজার user একসাথে।

উদাহরণ query (bKash transaction):

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE user_id = 'B';
INSERT INTO tx_log (sender, receiver, amount) VALUES ('A','B',500);
COMMIT;

এই pattern-এর জন্য row-store ভাল — একটি row-এর সব column disk-এ পাশাপাশি, একবার seek-এ পুরো row পাওয়া যায়। PostgreSQL, MySQL, Oracle, SQL Server, MongoDB — সব OLTP-optimized।

৩ · OLAP — বিশাল analysis

OLAPOLAP — Online Analytical ProcessingAggregate, group-by, time-series analysis-এর জন্য optimized workload। কোটি row scan, কম column, response time সেকেন্ড-মিনিট। Snowflake, BigQuery, Redshift, ClickHouse, DuckDB। ডেটাবেসের কাজ — বুঝতে সাহায্য করা। সাধারণ query:

কোটি row scan করে।
মাত্র ২-৫টি column টানে (SELECT region, SUM(amount))।
Concurrency কম — কয়েকজন analyst।
Latency — ২-৬০ সেকেন্ড গ্রহণযোগ্য।
Read-only বলতে গেলে — bulk load + query।

উদাহরণ query (Daraz BI):

SELECT
    DATE_TRUNC('month', order_date) AS month,
    delivery_district,
    SUM(amount_bdt)                  AS revenue,
    COUNT(DISTINCT user_id)          AS buyers
FROM fact_orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

এই query-তে শুধু ৪টি column লাগে (date, district, amount, user_id) — অথচ table-এ ৫০টি column আছে। Row-store হলে disk থেকে সব ৫০টি pull করতে হয় — অপচয়। Column-store শুধু দরকারি ৪টি column scan করে — ১০x-১০০x দ্রুত।

৪ · Row-store বনাম Column-store — ছবি

একই ডেটা disk-এ কীভাবে রাখা থাকে — এটাই দু'টি system-এর গোপন পার্থক্য।

একই ৩-row table — row-store row-by-row লেখে; column-store column-by-column। OLTP-তে row-store দ্রুত, OLAP-তে column-store ১০-১০০x দ্রুত।

ভাবুন একটি ছাত্রদের attendance খাতা। Row-store = প্রতিটি ছাত্রের জন্য একটি সম্পূর্ণ পৃষ্ঠা — নাম, রোল, প্রতিদিনের attendance। "রহিম-এর সব তথ্য" দেখতে ১ পৃষ্ঠা যথেষ্ট।
Column-store = একটি পাতায় সবার নাম, পরের পাতায় সবার রোল, পরের পাতায় ১লা জানুয়ারি কে এসেছিল। "১৫ই মার্চ ক্লাসে কতজন এসেছিল?" — শুধু একটি পৃষ্ঠা পড়লেই উত্তর।

৫ · Normalization (3NF) বনাম Denormalization (Star Schema)

OLTP ও OLAP-এর schema design ও আলাদা।

OLTP → Normalized (3NF):

প্রতিটি fact একবার রাখা — redundancy নেই।
users, orders, products, cities — আলাদা table, foreign key দিয়ে যুক্ত।
Update সহজ — user-এর address বদলালে এক জায়গায় বদল হয়।
কিন্তু query-তে অনেক JOIN — analytics-এ ধীর।

OLAP → Denormalized (Star Schema):

একটি বড় fact_orders table — প্রতিটি row-এ সব দরকারি column (city, product category, payment method)।
চারপাশে কিছু ছোট dim_ table (dim_user, dim_product, dim_date)।
JOIN কম — query দ্রুত।
কিন্তু redundant — "Dhaka" শব্দটি কোটি row-এ। (Column-store compression-এ এই খরচ negligible।)

Star schema নাম এসেছে — মাঝে fact table, চারপাশে dimension — দেখতে star-এর মতো। এই pattern Ralph Kimball ১৯৯০-এ propose করেন — আজও modern warehouse design-এর ভিত্তি।

৬ · বাস্তব উদাহরণ — SQL দিয়ে দেখা

একই ব্যবসা, দু'টি ভিন্ন query — দু'টি ভিন্ন database-এ চললে কেমন।

SQL · OLTP query

-- OLTP (PostgreSQL on bKash production)
-- লক্ষ্য: এক user-এর সর্বশেষ ১০ transaction দেখা
-- Pattern: point-lookup, index দ্রুত করে

SELECT tx_id, amount, peer_id, status, created_at
FROM transactions
WHERE user_id = 'U-12345'
ORDER BY created_at DESC
LIMIT 10;

-- প্রত্যাশিত: < 5 ms (B-tree index on user_id, created_at)
-- কোটি row-এর মধ্যে শুধু ১০টি touch হয়

এই query একটি index-এ "go to the right page" করে — row-store-এ super-fast। OLAP warehouse-এ এই query চালালে — ৩-৫ সেকেন্ড লাগবে (overhead high), কারণ column-store point-lookup-এ optimized না।

SQL · OLAP query

-- OLAP (Snowflake / BigQuery / ClickHouse)
-- লক্ষ্য: বিভাগওয়ারী মাসিক revenue trend
-- Pattern: full scan, aggregate, low column count

SELECT
    DATE_TRUNC('month', tx_date)   AS month,
    division,
    SUM(amount)                    AS gross,
    COUNT(DISTINCT user_id)        AS active_users,
    AVG(amount)                    AS avg_ticket
FROM fact_transactions
WHERE tx_date >= DATEADD(year, -1, CURRENT_DATE)
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- প্রত্যাশিত: 2-15 সেকেন্ড (১০ কোটি row scan, ৪ column)
-- Row-store হলে: ১৫-৬০ সেকেন্ড + production load

Column-store এই query-তে ভাল কারণ — (১) শুধু ৪ column scan, (২) "Dhaka" বারবার একই value — RLE compressionRun-Length Encoding (RLE)একই value পরপর থাকলে — "Dhaka × ১০০০ বার" এমন compact representation। Column-store-এ category column-এ ১০০x compression সাধারণ।-এ ১০-১০০x ছোট, (৩) modern engines vectorized SIMD ব্যবহার করে।

৭ · কখন কোনটি — তুলনা table

সাধারণ guide:

OLTP (PostgreSQL/MySQL): live application backend — user signup, order placement, payment, inventory check।
OLAP (Snowflake/BigQuery/ClickHouse): dashboard, BI, ML feature, ad-hoc analyst query, monthly report।
Hybrid (HTAP): SingleStore, TiDB, AlloyDB — দু'টি workload একসাথে। নতুন কিন্তু এখনো specialized OLAP-এর চেয়ে কম দ্রুত।

Production OLTP-তে সরাসরি analyst query দিলে — user-facing latency বেড়ে যাবে, lock contention বাড়বে, এমনকি outage হতে পারে। এই কারণেই data engineer "একটি আলাদা warehouse" তৈরি করেন — এটিই data engineering-এর প্রথম raison d'être।

৮ · Python-এ একটি ছোট benchmark

Python · pandas vs DuckDB

import pandas as pd
import time

# একটি ১০ লাখ row dummy fact table
N = 1_000_000
df = pd.DataFrame({
    "user_id":  range(N),
    "city":     ["Dhaka","Ctg","Sylhet","Khulna"] * (N // 4),
    "amount":   [100 + (i % 1000) for i in range(N)],
    "status":   ["ok"] * N,
})

# OLAP-style aggregate
t0 = time.time()
result = df.groupby("city")["amount"].agg(["sum", "mean", "count"])
print(result)
print(f"\nAggregate time: {(time.time()-t0)*1000:.1f} ms on {N:,} rows")

Pandas একটি in-memory column-store হিসেবে কাজ করে — এই aggregate কয়েক ms-এ চলে। PostgreSQL-এ (row-store) একই কাজ অনেক ধীর হবে। Production warehouse (Snowflake) ১০ কোটি row-এ একই query সেকেন্ডে চালায়।

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

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

প্র ০১ একটি analyst সকাল ১০টায় production PostgreSQL-এ SELECT SUM(amount) FROM transactions WHERE created_at > '2020-01-01' চালালেন। ১০ মিনিট পর bKash-এর mobile app slow হয়ে গেল। কেন? কী হয়েছে এবং কীভাবে আটকাবেন?

এই scenario বাস্তবে অসংখ্যবার ঘটেছে — এটিই production-এ analyst-কে সরাসরি access না দেওয়ার classic পাঠ।

কী ঘটল technically:

Full table scan: ৪ বছরের transactions = কোটি কোটি row। Index দিয়ে এই range filter আংশিকভাবে কাজ করলেও — pages cache-এ আনতে disk I/O ব্যাপক বাড়ল।
Buffer pool eviction: Hot pages (current users) cache থেকে evict হলো। Mobile app-এর user lookup (which was hitting cache) এখন disk-এ যেতে হচ্ছে — latency 1ms থেকে 50ms।
Lock contention: MVCC কিছুটা isolation দেয়, কিন্তু long-running query "vacuum" delay করে; dead tuple জমা হয়।
CPU saturation: Aggregate কাজ row-by-row, single CPU thread। Read replica না থাকলে — primary CPU 90% busy।
Replication lag: যদি read replica থাকেও, এই query সেখানে চললে replica lag বাড়বে।

তাৎক্ষণিক সমাধান:

pg_stat_activity-এ query খুঁজে pg_cancel_backend() বা pg_terminate_backend()।
App connection pool reset।
Postmortem — কীভাবে analyst এই access পেল।

স্থায়ী prevention:

Read replica: Streaming replica শুধু analyst-দের জন্য। Production-এ কোনো প্রভাব নেই।
Warehouse: Daily/hourly CDC pipeline → Snowflake/BigQuery। Analyst সেখানে query করুন।
Statement timeout: Analyst role-এ SET statement_timeout = '30s'।
Workload manager: Resource queue — analytics queries low priority।
Quota: প্রতি analyst-এর জন্য daily query budget।
Code review: "WHERE created_at > '2020-01-01'" এমন overly-broad filter PR-এ block।

BD context: bKash, Nagad, রকেট — প্রতিটির এমন একটি incident ছিল প্রথম দিকে। এই কারণেই তারা এখন দু'টি স্বতন্ত্র environment চালায়।

মূল উপলব্ধি: Production OLTP "everyone's database" না — এটি single-purpose। যিনি এই boundary রক্ষা করেন — তিনি good data engineer।

প্র ০২ "আমাদের startup ছোট — PostgreSQL সব করছে, OLAP warehouse এখনও দরকার নেই" — কখন এই strategy ভেঙে পড়ে? কোন signal দেখলে warehouse migration শুরু করবেন?

ছোট startup-এ overengineering ভুল — কিন্তু খুব দেরি করলে scaling পেইন। মোটামুটি seven signal।

(১) Query time bleeding:

Analyst-এর কোনো query > ১ মিনিট হলেই — PostgreSQL OLAP কাজে breaking point।
Read replica-তে চালানোও যদি ৩-৫ মিনিট লাগে — warehouse সময়।

(২) Production impact incident:

একটি BI query mobile app slow করেছে — এক বার ঘটলে যথেষ্ট। দু'বার মানে boundary নেই।

(৩) Multiple data source:

"PostgreSQL + Stripe API + Google Analytics + Salesforce" থেকে data মিলাতে হলে — PostgreSQL এ JOIN অসম্ভব। Warehouse অপরিহার্য।

(৪) Historical data বাড়ছে:

Production DB-তে "last 5 years tx history" রাখা ব্যয়বহুল — SSD costly, backup slow।
Warehouse-এ S3-backed storage — ৫-১০x সস্তা।

(৫) Multiple analyst:

২-৩ জন analyst একসাথে query করলে production resource shared।
Warehouse-এ each analyst-এর আলাদা warehouse compute (Snowflake style)।

(৬) ML model deployment:

"Last 90 days user behavior" feature compute — production DB-তে চাপ।
Warehouse → feature store pattern অনিবার্য।

(৭) Dashboard freshness mismatch:

CEO চায় daily dashboard 9 AM-এ; query slow হলে fail। Pipeline-এ pre-compute দরকার।

BD context — কখন migration:

SaaS, ১-১০ কর্মী: PostgreSQL + Metabase যথেষ্ট।
১০-৫০ কর্মী, ১+ analyst: Read replica + scheduled CSV export to BigQuery। Cost ১০-৫০ ডলার/মাস।
৫০+ কর্মী, কোটি transaction: Full warehouse + Airflow + dbt stack।
১০০০+ কর্মী, regulatory: Multi-region, governance, lineage।

Migration কীভাবে শুরু:

Snowflake / BigQuery free tier (~$300 credit)।
Fivetran / Airbyte CDC connector।
dbt-এ ২-৩টি core mart মডেল।
Metabase/Looker-এ rewire dashboard।
২-৪ সপ্তাহে minimal stack চালু।

মূল উপলব্ধি: "প্রস্তুত হওয়া" warehouse-এর সিদ্ধান্ত — technical signal-এর উপর, intuition-এর উপর না। উপরের signal দেখা মাত্র — ছোট pilot শুরু করুন।

প্র ০৩ Snowflake বা BigQuery columnar এবং scale করে — তাহলে PostgreSQL-এর প্রয়োজন কেন রয়েই গেল? OLTP-এর বিকল্প কেন column-store হয় না?

Excellent question — অনেকে ভাবেন "modern technology সব কিছুর সমাধান"। বাস্তবে workload-specific optimization-এর কারণে দু'টি system বেঁচে আছে এবং থাকবে।

Column-store-এর OLTP-তে চারটি দুর্বলতা:

(১) Single-row write expensive:

Column-store-এ একটি row insert করতে হলে — প্রতিটি column-এর file/block-এ separate write।
৫০ column-এ ৫০টি I/O। Row-store-এ একটি সিরিয়াল I/O।
bKash-এর প্রতি সেকেন্ডে ১০,০০০ insert — column-store-এ unmanageable।

(২) Update inefficient:

Column-store সাধারণত append-only বা micro-batched।
"User-এর balance বদলাও" — column-store-এ rewrite or tombstone। Row-store-এ in-place update।

(৩) Strong ACID isolation hard:

Column-store সাধারণত eventual consistency বা snapshot isolation।
Money transfer-এ "exactly once" guarantee — row-store-এর MVCC সরাসরি দেয়।
Snowflake-এও ACID আছে, কিন্তু latency & concurrency level OLTP-র না।

(৪) Latency:

Snowflake/BigQuery query startup overhead 200ms+।
Mobile app-এর "send money" ১ সেকেন্ডের মধ্যে দরকার — এই overhead unacceptable।
PostgreSQL local network-এ <1ms দিতে পারে।

Row-store-এর OLAP-তে দুর্বলতা (যা OLAP-এ column-এর জিতের কারণ):

I/O wasteful: ৫টি column-এর query-তে ৫০টি column disk-এ আনতে হয়।
Compression কম: পাশাপাশি ভিন্ন data type — gzip ৩x, column-এ ১০-১০০x।
Vectorization কঠিন: Modern CPU SIMD column-array-এ ভাল চলে।

HTAP — কেন এখনো mainstream না:

SingleStore, TiDB, AlloyDB — দু'টি engine একই system-এ।
"Best of both" বললেও — specialized OLAP-এর tuning level পায় না, OLTP-তে latency higher।
Cost & complexity বেশি।

BD-র বাস্তব stack:

bKash: PostgreSQL OLTP + (rumored) Snowflake/BigQuery analytics।
Daraz: MySQL + ClickHouse + Snowflake।
Pathao: PostgreSQL + Redis + BigQuery।

ভবিষ্যৎ:

LakehouseLakehouseData lake-এর storage flexibility + warehouse-এর ACID/SQL — এক জায়গায়। Delta Lake, Apache Iceberg, Apache Hudi এই pattern-এর উদাহরণ। পাঠ ০৩-এ বিস্তারিত। — storage এক জায়গায়, কিন্তু compute layer ভিন্ন (transactional ও analytical)।
HTAP ভবিষ্যতে আরও ভাল হবে — কিন্তু "এক system সব" আশা realistic না।

মূল উপলব্ধি: Engineering সবসময় trade-off। "একটি tool সব কাজের" idealist-দের ধারণা; production-এ workload-specific tool বাঁচে। Data engineer-এর কাজ — সঠিক tool সঠিক জায়গায় বসানো।

প্র ০৪ আপনি একটি e-commerce-এর schema design করছেন। OLTP-তে কোন ৫টি table থাকবে এবং OLAP warehouse-এ একই ব্যবসার জন্য কোন ৩টি fact/dim table তৈরি করবেন?

এটি data engineer-এর সবচেয়ে সাধারণ design exercise। OLTP-র normalized form ও OLAP-র star schema পাশাপাশি দেখলে কাঠামো পরিষ্কার হয়।

OLTP — Normalized (PostgreSQL):

users: (user_id PK, name, phone, email, created_at)। Phone-এ unique index।
addresses: (addr_id PK, user_id FK, city, district, full_text, is_default)। One user → many addresses।
products: (product_id PK, name, category_id FK, price_bdt, stock)।
orders: (order_id PK, user_id FK, addr_id FK, status, total_bdt, created_at)। Status enum: pending/paid/shipped/delivered/cancelled।
order_items: (item_id PK, order_id FK, product_id FK, qty, unit_price_bdt)। One order → many items।

৩NF properties: Customer-এর phone শুধু users-এ; product price শুধু products-এ। কোথাও duplicate নেই।

OLAP — Star schema (Snowflake/BigQuery):

Fact table (একটি বড় denormalized):

fact_order_items — প্রতিটি row একটি order_item, কিন্তু সব দরকারি column embedded:
- order_id, item_id, user_id, product_id, date_id (FK to dim)।
- qty, unit_price_bdt, total_bdt, discount_bdt (measures)।
- city, district, division (denormalized from addresses)।
- category, subcategory, brand (denormalized from products)।
- order_status, payment_method।

Dimension tables (ছোট, lookup):

dim_user — (user_id PK, signup_date, age_bucket, gender, lifetime_value, is_premium)। SCD type-2 history।
dim_product — (product_id PK, name, category, brand, supplier, list_price_history)।
dim_date — (date_id PK, year, quarter, month, week, day_name, is_holiday, is_eid_period, fiscal_quarter)।

কেন এই difference:

Dashboard: "প্রতি মাসে Sylhet-এ electronics revenue" — fact_order_items-এ এক query (no JOIN)।
OLTP-তে এই query — ৪টি JOIN (orders + addresses + products + categories)।

Trade-offs:

Storage: "Dhaka" শব্দটি কোটি row-এ — কিন্তু column-store compression-এ ১০০x ছোট। Net storage বেশি না।
Update: User city বদলালে — OLTP-তে এক row update; OLAP-তে nightly pipeline সব history fact-এ propagate করে। Slower but acceptable for analytics।
Consistency: Warehouse always lags OLTP by minutes-hours। Realtime দরকার হলে — CDC + streaming pipeline।

BD-specific dimension idea:

is_eid_period, is_pohela_boishakh — seasonal effect।
division (8 divisions of BD) — geographic aggregation।
delivery_payment (COD, bKash, Nagad, card) — channel mix।

মূল উপলব্ধি: Schema design ব্যবসার প্রশ্নকে আকার দেয়। Star schema মানে — "যে query আসবে তা আগে থেকে সহজ করা।" এটি Kimball-এর ৩০ বছরের wisdom।

অনুশীলন

চিনুন: নিচের তিনটি query — প্রতিটি OLTP না OLAP? কেন?
- (ক) SELECT * FROM users WHERE phone = '+8801711...'
- (খ) SELECT category, AVG(price) FROM products GROUP BY category
- (গ) UPDATE accounts SET balance = balance + 100 WHERE id = 42
- (ক) OLTP: single user lookup, equality on indexed column। PostgreSQL ms-এ দেবে।
- (খ) OLAP: full-table scan, GROUP BY aggregate, কোনো filter নেই। Warehouse-এ pre-aggregate বা column-store optimal।
- (গ) OLTP: single-row in-place update। ACID guarantee দরকার।
হিসাব করুন: একটি table-এ ৫০ column, ১০ কোটি row। গড়ে প্রতিটি column ৮ bytes। একটি query শুধু ৫টি column scan করে। Row-store ও column-store-এ কত data disk থেকে পড়তে হবে?
মোট table size = $50 \times 8 \times 10^8 = 4 \times 10^{10}$ bytes = 40 GB।
- Row-store: পুরো table scan = ৪০ GB।
- Column-store: $5 \times 8 \times 10^8 = 4 \times 10^9$ bytes = ৪ GB।
- Compression after: column-এ ৪x compression normal — ১ GB।
৪০ GB বনাম ১ GB — ৪০x পার্থক্য। এই কারণেই OLAP-এ column-store।
ডিজাইন করুন: একটি school management system-এর জন্য (১) OLTP-তে ৩টি core table, (২) OLAP-এ ১টি fact + ২টি dim table লিখুন।
OLTP:
- students(student_id, name, class_id, dob, guardian_phone)
- attendance(att_id, student_id, date, status)
- exam_results(result_id, student_id, subject_id, score, exam_date)
OLAP:
- fact_student_day(student_id, date_id, class_name, attendance_status, total_marks_today)
- dim_student(student_id, gender, age, class, section, district)
- dim_date(date_id, week, month, term, is_exam_day, is_holiday)
OLAP fact-এ "কোন class-এ attendance-rate কত" এক query-তে।