এই বইয়ের ব্যাখ্যা বাংলায়, কিন্তু technical term গুলো ইংরেজিতেই রাখা হয়েছে। কারণ চাকরির ইন্টারভিউ, ডকুমেন্টেশন, Stack Overflow — সব জায়গায় আপনি foreign key, index, transaction এই শব্দগুলোই পাবেন। এগুলোকে "বিদেশি চাবি" বা "সূচক" বানিয়ে দিলে শেখাটা উল্টো কঠিন হয়ে যাবে।

পড়ার নিয়ম মাত্র একটাই — প্রতিটা query নিজের হাতে টাইপ করে চালাবেন। কপি-পেস্ট করলে চোখে পড়বে, মাথায় ঢুকবে না। ভুল করবেন, error দেখবেন, তারপর ঠিক করবেন — শেখাটা ঠিক ওখানেই হয়।

প্রতিটা অধ্যায়ের শেষে একটা করে অনুশীলনী (Practice) আছে। উত্তর মেলানোর আগে অন্তত ২০ মিনিট নিজে চেষ্টা করবেন।

১.১ Database জিনিসটা আসলে কী

ধরুন আপনি একটা ছোট দোকান চালান। শুরুতে খাতায় লিখে রাখেন — কে কী কিনল, কত টাকা বাকি। দোকান বড় হলো, এখন Excel ফাইলে রাখেন। আরও বড় হলো — তিনজন কর্মচারী একসাথে একই ফাইল খুলতে চায়, কিন্তু Excel তখন হাত তুলে দেয়।

ঠিক এই সমস্যার সমাধানই Database Management System (DBMS)। এটা এমন একটা সফটওয়্যার যা:

• ডাটা গুছিয়ে রাখে (structured storage)
• একসাথে অনেকজনকে কাজ করতে দেয় (concurrency)
• ভুল ডাটা ঢুকতে বাধা দেয় (constraints)
• বিদ্যুৎ চলে গেলেও ডাটা হারায় না (durability)
• বিশাল ডাটার মধ্যে থেকেও মিলিসেকেন্ডে খুঁজে বের করে (indexing)

PostgreSQL (উচ্চারণ: পোস্ট-গ্রেস-কিউ-এল, সংক্ষেপে সবাই বলে Postgres) হলো এমনই একটা DBMS — যেটা open source, সম্পূর্ণ ফ্রি, এবং গত ৩০+ বছর ধরে ক্রমাগত উন্নত হচ্ছে।

১.২ একটু ইতিহাস

১৯৮৬ সালে University of California, Berkeley-তে Michael Stonebraker নামের একজন গবেষক POSTGRES নামে একটা প্রজেক্ট শুরু করেন। উদ্দেশ্য ছিল তাঁরই আগের প্রজেক্ট Ingres-এর সীমাবদ্ধতাগুলো দূর করা। নামটাই এসেছে "Post-Ingres" থেকে।

১৯৯৪ সালে এতে SQL সাপোর্ট যোগ হলে নাম হয় Postgres95, আর ১৯৯৬ সালে নাম দাঁড়ায় PostgreSQL। তখন থেকেই এটা একটা global open-source community দ্বারা পরিচালিত — কোনো একক কোম্পানির মালিকানা নেই।

এই "কোনো কোম্পানির মালিকানা নেই" ব্যাপারটা গুরুত্বপূর্ণ। MySQL-এর মালিক Oracle, MongoDB-র মালিক MongoDB Inc. — তারা চাইলে লাইসেন্স বদলে দিতে পারে (এবং বাস্তবে দিয়েছেও)। PostgreSQL-এর ক্ষেত্রে সেই ঝুঁকি নেই।

১.৩ কেন PostgreSQL বেছে নেবেন

১.৪ PostgreSQL vs অন্যান্য

PostgreSQL vs MySQL

MySQL শিখতে একটু সহজ, ছোট read-heavy অ্যাপে দ্রুত। কিন্তু PostgreSQL বেশি feature-rich এবং data integrity-তে কড়া। যেমন — MySQL-এ ভুল করে 'abc' একটা integer কলামে ঢোকালে পুরনো ভার্সনে সেটা 0 হয়ে যেত। PostgreSQL সরাসরি error দেবে। ডাটার সঠিকতা যেখানে গুরুত্বপূর্ণ, সেখানে এই কড়াকড়িটাই আশীর্বাদ।

PostgreSQL vs MongoDB

MongoDB document-based, schema নেই বললেই চলে — শুরুতে দ্রুত। কিন্তু প্রজেক্ট বড় হলে "কোন ফিল্ডে কী আছে" এই বিশৃঙ্খলা ভোগায়। PostgreSQL-এর jsonb দিয়ে আপনি structured আর unstructured — দুটোই একসাথে পাবেন।

PostgreSQL vs SQLite

SQLite একটা ফাইল, কোনো server লাগে না — মোবাইল অ্যাপ বা ছোট desktop টুলের জন্য চমৎকার। কিন্তু একাধিক ইউজার একসাথে লিখতে চাইলে SQLite আটকে যায়। Web application-এর জন্য PostgreSQL।

১.৫ Version নিয়ে কথা

PostgreSQL প্রতি বছর সেপ্টেম্বর-অক্টোবরে একটা করে major version ছাড়ে (১৪, ১৫, ১৬, ১৭...)। প্রতিটা major version ৫ বছর সাপোর্ট পায়।

পরামর্শ: নতুন প্রজেক্টে সর্বশেষ stable version-এর ঠিক আগেরটা নিন। যেমন সর্বশেষ যদি ১৭ হয়, ১৬ নিন — bug গুলো ততদিনে ধরা পড়ে গেছে।

এই বইয়ের সব উদাহরণ PostgreSQL 14 বা তার পরের যেকোনো version-এ কাজ করবে।

২.১ Windows-এ ইনস্টল

1. postgresql.org/download/windows থেকে EDB installer নামান
2. Installer চালান। যেসব component চাইবে — PostgreSQL Server, pgAdmin 4, Command Line Tools — তিনটাই রাখুন। Stack Builder দরকার নেই
3. superuser password চাইবে। এটা postgres ইউজারের পাসওয়ার্ড — লিখে রাখুন, ভুলে গেলে ঝামেলা
4. Port: 5432 (default রাখুন)
5. Locale: default

ইনস্টল শেষে Start menu-তে SQL Shell (psql) খুঁজে পাবেন।

২.২ macOS-এ ইনস্টল

সবচেয়ে সহজ Homebrew দিয়ে:

brew install postgresql@16
brew services start postgresql@16

# PATH-এ যোগ করুন
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

GUI পছন্দ করলে Postgres.app (postgresapp.com) — ডাউনলোড করে Applications-এ রাখলেই চলে।

২.৩ Linux (Ubuntu/Debian)-এ ইনস্টল

sudo apt update
sudo apt install postgresql postgresql-contrib

# service চালু আছে কিনা দেখুন
sudo systemctl status postgresql

# postgres ইউজার হিসেবে ঢুকুন
sudo -u postgres psql

postgresql-contrib প্যাকেজটা বাদ দেবেন না — এতে দরকারি extension গুলো থাকে।

২.৪ Docker দিয়ে (সবচেয়ে পরিষ্কার উপায়)

আপনার মেশিনে কিছু ইনস্টল না করে চালাতে চাইলে:

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecret \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=learning \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

লাইনগুলোর মানে: - --name — কন্টেইনারের নাম - -e — environment variable (পাসওয়ার্ড, ইউজার, ডাটাবেজ নাম) - -p 5432:5432 — হোস্টের 5432 পোর্ট কন্টেইনারের 5432-এ যাবে - -v pgdata:... — এটা খুব জরুরি। না দিলে কন্টেইনার মুছলে সব ডাটা উধাও - -d — background-এ চলবে

ভেতরে ঢুকতে:

docker exec -it my-postgres psql -U postgres -d learning

২.৫ psql — আপনার প্রধান হাতিয়ার

psql হলো PostgreSQL-এর command line client। GUI ভালো লাগলেও psql শিখে রাখা বাধ্যতামূলক — সার্ভারে SSH করে ঢুকলে GUI পাবেন না।

কানেক্ট করা:

psql -h localhost -p 5432 -U postgres -d learning

• -h host, -p port, -U user, -d database

সফল হলে প্রম্পট দেখাবে:

learning=#

# মানে আপনি superuser, > হলে সাধারণ ইউজার।

২.৬ psql-এর জাদু কমান্ড (Meta-commands)

এগুলো SQL নয়, psql-এর নিজস্ব কমান্ড। ব্যাকস্ল্যাশ দিয়ে শুরু, সেমিকোলন লাগে না।

\l              -- সব database এর তালিকা (list)
\c dbname       -- অন্য database এ যাওয়া (connect)
\dt             -- current schema এর সব table
\dt *.*         -- সব schema এর table
\d tablename    -- একটা table এর গঠন (describe)
\d+ tablename   -- আরও বিস্তারিত, size সহ
\dn             -- সব schema
\du             -- সব user/role
\di             -- সব index
\df             -- সব function
\dv             -- সব view
\x              -- expanded display চালু/বন্ধ (চওড়া রেজাল্টের জন্য দারুণ)
\timing         -- query কত সময় নিল দেখাবে
\e              -- editor খুলে query লিখতে দেবে
\i file.sql     -- ফাইল থেকে SQL চালাবে
\o out.txt      -- আউটপুট ফাইলে পাঠাবে
\conninfo       -- এখন কোথায় কানেক্টেড
\h CREATE TABLE -- SQL কমান্ডের সিনট্যাক্স help
\?              -- সব meta-command এর তালিকা
\q              -- বের হওয়া (quit)

নতুনদের জন্য টিপ: \x কমান্ডটা মনে রাখবেন। অনেক কলামওয়ালা টেবিল select করলে টার্মিনালে সব লেপ্টে যায়। \x চালু করলে প্রতিটা row উলম্বভাবে দেখাবে — পড়তে অনেক আরাম।

২.৭ GUI টুল

• pgAdmin 4 — অফিসিয়াল, ফ্রি, ফিচার-ভর্তি, তবে একটু ভারী
• DBeaver — ফ্রি, একাধিক database সাপোর্ট করে, খুব জনপ্রিয়
• TablePlus — সুন্দর ও দ্রুত, ফ্রি ভার্সনে সীমাবদ্ধতা আছে
• DataGrip — JetBrains-এর, পেইড, শক্তিশালী

শুরুতে DBeaver সাজেস্ট করব — হালকা, ফ্রি এবং শেখার জন্য যথেষ্ট।

২.৮ প্রথম কমান্ড

psql-এ ঢুকে চালান:

SELECT version();

আউটপুট হবে অনেকটা এরকম:

PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc ...

অভিনন্দন — আপনার PostgreSQL চলছে।

আরও কিছু:

SELECT current_database();   -- এখন কোন DB তে আছি
SELECT current_user;         -- আমি কে
SELECT now();                -- এখন কয়টা বাজে
SELECT 2 + 3;                -- হ্যাঁ, ক্যালকুলেটর হিসেবেও চলে

লক্ষ করুন — SQL-এ FROM ছাড়াও SELECT চালানো যায়। এটা PostgreSQL-এর সুবিধা।

৩.১ স্তরবিন্যাস (Hierarchy)

PostgreSQL-এর গঠনটা এভাবে সাজানো:

PostgreSQL Server (Cluster)
│
├── Database: shop
│   ├── Schema: public
│   │   ├── Table: customers
│   │   ├── Table: orders
│   │   └── View: monthly_sales
│   └── Schema: analytics
│       └── Table: daily_report
│
└── Database: blog
    └── Schema: public
        └── Table: posts

একটা তুলনা দিয়ে বোঝা যাক: - Server = পুরো অফিস বিল্ডিং - Database = একেকটা তলা - Schema = তলার একেকটা রুম - Table = রুমের একেকটা ফাইল ক্যাবিনেট - Row = ক্যাবিনেটের একেকটা ফাইল

গুরুত্বপূর্ণ নিয়ম: এক database থেকে সরাসরি অন্য database-এর টেবিল query করা যায় না (MySQL-এ যায়)। দরকার হলে dblink বা postgres_fdw extension লাগবে। তাই সম্পর্কিত সব টেবিল একই database-এর ভেতরে আলাদা schema-তে রাখাই বুদ্ধিমানের কাজ।

৩.২ Database তৈরি ও ব্যবস্থাপনা

-- তৈরি
CREATE DATABASE shop;

-- বিস্তারিত অপশন সহ
CREATE DATABASE shop
    WITH OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- নাম বদলানো
ALTER DATABASE shop RENAME TO shop_v2;

-- মুছে ফেলা (সাবধান! ফেরত পাবেন না)
DROP DATABASE shop_v2;

-- না থাকলে error দেবে না
DROP DATABASE IF EXISTS shop_v2;

বাংলা টেক্সট রাখতে চাইলে ENCODING = 'UTF8' অবশ্যই দরকার — যদিও আধুনিক PostgreSQL-এ এটাই default।

৩.৩ Schema

Schema হলো টেবিলের namespace বা "ফোল্ডার"। এর দুটো বড় উপকার:

1. নাম সংঘর্ষ এড়ানো — sales.orders আর hr.orders দুটো আলাদা টেবিল হতে পারে
2. অনুমতি নিয়ন্ত্রণ — একটা schema-র উপর একজন ইউজারকে access দেওয়া, অন্যটায় না

CREATE SCHEMA sales;
CREATE SCHEMA hr AUTHORIZATION hr_manager;

-- schema সহ টেবিল তৈরি
CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10,2)
);

-- schema মোছা
DROP SCHEMA sales;              -- খালি থাকলেই কেবল মুছবে
DROP SCHEMA sales CASCADE;      -- ভেতরের সবকিছু সহ মুছবে

৩.৪ search_path — একটা গুরুত্বপূর্ণ ধারণা

আপনি যখন লেখেন SELECT * FROM orders, PostgreSQL কীভাবে জানে কোন schema-র orders?

উত্তর: search_path। এটা একটা তালিকা, PostgreSQL উপর থেকে নিচে খুঁজতে থাকে।

SHOW search_path;
-- "$user", public

-- বদলানো (শুধু এই session এর জন্য)
SET search_path TO sales, public;

-- স্থায়ীভাবে (এই database এর জন্য)
ALTER DATABASE shop SET search_path TO sales, public;

-- একজন ইউজারের জন্য
ALTER ROLE analyst SET search_path TO analytics, public;

"$user" মানে — আপনার ইউজারনেমের নামে যদি কোনো schema থাকে, সেটা আগে দেখবে।

Schema না বললে টেবিল public-এ তৈরি হয় (default search_path অনুযায়ী)।

৩.৫ Table — প্রথম টেবিল

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE,
    age         INTEGER CHECK (age >= 5),
    department  VARCHAR(50) DEFAULT 'General',
    admitted_at DATE DEFAULT CURRENT_DATE,
    is_active   BOOLEAN DEFAULT TRUE
);

লাইন ধরে ধরে বুঝি:

টেবিল দেখুন:

\d students

৩.৬ SERIAL বনাম IDENTITY

SERIAL অনেক পুরনো লেখায় দেখবেন, কিন্তু এটা আসলে SQL standard নয় — PostgreSQL-এর নিজস্ব শর্টকাট। ভেতরে ভেতরে এটা একটা sequence তৈরি করে।

আধুনিক ও standard উপায় হলো IDENTITY:

CREATE TABLE students (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

GENERATED ALWAYS মানে আপনি হাতে id দিতে পারবেন না (OVERRIDING SYSTEM VALUE না লিখলে) — এটা ভুল ঠেকায়। GENERATED BY DEFAULT দিলে হাতে দেওয়াও যায়।

নতুন প্রজেক্টে IDENTITY ব্যবহার করুন। এই বইয়ে পুরনো কোড চেনার সুবিধার্থে দুটোই দেখানো হবে।

৩.৭ টেবিল কপি ও অস্থায়ী টেবিল

-- গঠন + ডাটা কপি
CREATE TABLE students_backup AS SELECT * FROM students;

-- শুধু গঠন কপি (ডাটা ছাড়া)
CREATE TABLE students_empty AS SELECT * FROM students WHERE FALSE;

-- constraint সহ পুরো গঠন কপি
CREATE TABLE students_clone (LIKE students INCLUDING ALL);

-- অস্থায়ী টেবিল — session শেষ হলে নিজে থেকে মুছে যাবে
CREATE TEMP TABLE temp_calc (id INT, total NUMERIC);

-- Unlogged table — WAL-এ লেখে না, তাই দ্রুত, কিন্তু crash হলে ডাটা যাবে
CREATE UNLOGGED TABLE cache_data (key TEXT, value TEXT);

CREATE TABLE ... AS SELECT (সংক্ষেপে CTAS) দিয়ে কপি করলে constraint, index, default কিছুই কপি হয় না — শুধু কলামের নাম ও type আসে। এটা নতুনদের একটা সাধারণ ফাঁদ।

৩.৮ অনুশীলনী

1. library নামে একটা database তৈরি করুন
2. তাতে catalog নামে একটা schema বানান
3. catalog.books টেবিল তৈরি করুন — id, title, author, published_year, price, in_stock কলাম সহ
4. \d catalog.books দিয়ে গঠন দেখুন
5. search_path এমনভাবে সেট করুন যাতে SELECT * FROM books কাজ করে

ঠিক data type বাছাই করা মানে — কম জায়গা, দ্রুত query, আর ভুল ডাটার বিরুদ্ধে প্রথম প্রতিরক্ষা।

৪.১ সংখ্যা (Numeric Types)

💰 টাকার হিসাবে NUMERIC কেন — এটা মুখস্থ করে ফেলুন

SELECT 0.1::REAL + 0.2::REAL;          -- 0.3 নয়, একটু এদিক-ওদিক
SELECT 0.1::NUMERIC + 0.2::NUMERIC;    -- 0.3 — একদম নির্ভুল

REAL আর DOUBLE PRECISION binary floating point ব্যবহার করে — দশমিক সংখ্যাকে হুবহু রাখতে পারে না। ব্যাংকিং সিস্টেমে এই সামান্য পার্থক্য জমতে জমতে বিরাট গরমিল হয়ে যায়।

নিয়ম: টাকা মানেই NUMERIC(precision, scale)।

price NUMERIC(10, 2)   -- মোট ১০ ডিজিট, দশমিকের পরে ২টা
                       -- সর্বোচ্চ 99,999,999.99

আরও নিরাপদ একটা কৌশল — টাকাকে পয়সায় BIGINT হিসেবে রাখা (price_in_paisa BIGINT)। এতে floating point-এর প্রশ্নই ওঠে না। Stripe সহ অনেক payment সিস্টেম এটাই করে।

MONEY নামে একটা type আছে, কিন্তু এটা locale-এর উপর নির্ভরশীল ও সীমিত — ব্যবহার করবেন না।

৪.২ টেক্সট (Character Types)

একটা ভুল ধারণা ভাঙা দরকার: অনেকে ভাবেন VARCHAR(50) লিখলে TEXT-এর চেয়ে দ্রুত হবে বা কম জায়গা নেবে। PostgreSQL-এ এটা সত্যি নয় — তিনটাই ভেতরে একই varlena স্ট্রাকচার ব্যবহার করে, performance এক।

তাহলে VARCHAR(n) কেন? — কেবল ব্যবসায়িক নিয়ম প্রয়োগ করতে। "মোবাইল নম্বর ১১ ডিজিটের বেশি নয়" — এটা একটা নিয়ম, তাই VARCHAR(11) যুক্তিসঙ্গত।

-- ভালো
name        TEXT NOT NULL,
bio         TEXT,
phone       VARCHAR(15),
country_code CHAR(2)      -- সবসময় ঠিক ২ অক্ষর: BD, US, IN

-- অকারণ সীমা
first_name VARCHAR(20)   -- কারও নাম ২১ অক্ষর হলে? অ্যাপ ক্র্যাশ

CHAR(n) প্রায় কখনোই দরকার হয় না — শুধু যখন মানটা সত্যিই fixed-length (দেশের কোড, currency কোড)।

৪.৩ তারিখ ও সময় (Date/Time)

🕐 সবসময় TIMESTAMPTZ ব্যবহার করুন

এটা এই বইয়ের অন্যতম গুরুত্বপূর্ণ পরামর্শ।

TIMESTAMP (timezone ছাড়া) মানটা যেমন-তেমন সংরক্ষণ করে — কোন timezone-এর সেটা মনে রাখে না। ঢাকার সার্ভার থেকে লন্ডনে migrate করলে সব সময় ৬ ঘণ্টা ভুল হয়ে যাবে।

TIMESTAMPTZ ভেতরে সবসময় UTC-তে রাখে, আর দেখানোর সময় আপনার timezone অনুযায়ী রূপান্তর করে দেয়।

SET timezone = 'Asia/Dhaka';
SELECT now();                    -- 2026-07-24 20:30:00+06

SET timezone = 'UTC';
SELECT now();                    -- 2026-07-24 14:30:00+00
-- একই মুহূর্ত, শুধু দেখার কোণ আলাদা

তারিখ নিয়ে কাজ:

SELECT CURRENT_DATE;                          -- আজকের তারিখ
SELECT CURRENT_TIME;                          -- এখনকার সময়
SELECT now();                                 -- timestamptz
SELECT CURRENT_DATE + INTERVAL '7 days';      -- এক সপ্তাহ পরে
SELECT CURRENT_DATE - DATE '2000-01-01';      -- কত দিন পার হলো
SELECT AGE(DATE '1995-03-15');                -- বয়স বছর-মাস-দিনে

SELECT EXTRACT(YEAR FROM now());              -- 2026
SELECT EXTRACT(DOW FROM now());               -- সপ্তাহের কোন দিন (0=রবি)

SELECT DATE_TRUNC('month', now());            -- মাসের প্রথম দিন
SELECT DATE_TRUNC('hour', now());             -- ঘণ্টার শুরু

SELECT TO_CHAR(now(), 'DD/MM/YYYY HH12:MI AM');
SELECT TO_DATE('24-07-2026', 'DD-MM-YYYY');

DATE_TRUNC মাসিক বা দৈনিক রিপোর্ট বানানোর সবচেয়ে কাজের হাতিয়ার — অধ্যায় ৯-এ আবার দেখব।

৪.৪ Boolean

is_active BOOLEAN DEFAULT TRUE

সত্য হিসেবে গ্রহণ করে: TRUE, 't', 'true', 'y', 'yes', 'on', 1 মিথ্যা: FALSE, 'f', 'false', 'n', 'no', 'off', 0

মনে রাখবেন — Boolean-এর তিনটা সম্ভাব্য মান: TRUE, FALSE, এবং NULL (অজানা)।

SELECT * FROM users WHERE is_active;           -- শুধু TRUE
SELECT * FROM users WHERE NOT is_active;       -- শুধু FALSE (NULL বাদ!)
SELECT * FROM users WHERE is_active IS NOT TRUE; -- FALSE এবং NULL দুটোই

৪.৫ UUID

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

gen_random_uuid() PostgreSQL 13 থেকে built-in, কোনো extension লাগে না।

UUID কখন ভালো: - একাধিক সার্ভার/ডিভাইস থেকে ID তৈরি হয় (offline-first অ্যাপ) - URL-এ ID দেখা গেলেও কেউ অনুমান করে অন্যের ডাটা দেখতে পারবে না (/order/1234 বদলে /order/1235 — এই ফাঁদটা বন্ধ হয়)

UUID-র খরচ: - ১৬ byte (INTEGER-এর ৪ byte-এর তুলনায় ৪ গুণ) - এলোমেলো হওয়ায় index-এ page split বেশি হয়, insert ধীর হয়

মাঝারি প্রজেক্টে BIGINT IDENTITY যথেষ্ট। নিরাপত্তা বা distributed system-এর দরকার হলে UUID।

৪.৬ ENUM

নির্দিষ্ট কিছু মানের মধ্যে সীমাবদ্ধ রাখতে:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- নতুন মান যোগ
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

ENUM-এর ক্রম সংরক্ষিত হয়, তাই ORDER BY status লিখলে ঘোষণার ক্রমে সাজাবে — বর্ণানুক্রমে নয়। এটা কাজের।

সীমাবদ্ধতা: ENUM থেকে মান মুছে ফেলা যায় না। ঘন ঘন পরিবর্তন হয় এমন তালিকার জন্য একটা আলাদা lookup টেবিল + foreign key বেশি নমনীয়।

৪.৭ Array

PostgreSQL-এর একটা বিশেষত্ব — যেকোনো type-এর array রাখা যায়।

CREATE TABLE posts (
    id      SERIAL PRIMARY KEY,
    title   TEXT,
    tags    TEXT[]
);

INSERT INTO posts (title, tags)
VALUES ('PostgreSQL শেখা', ARRAY['database', 'sql', 'tutorial']);

-- index ১ থেকে শুরু (0 নয়!)
SELECT tags[1] FROM posts;

-- একটা উপাদান আছে কিনা
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- array কে row তে ভাঙা
SELECT id, unnest(tags) AS tag FROM posts;

SELECT array_length(tags, 1) FROM posts;
SELECT array_append(tags, 'new') FROM posts;

Array সুবিধাজনক, কিন্তু অতিরিক্ত ব্যবহার করবেন না। ট্যাগগুলোর নিজস্ব তথ্য (বর্ণনা, ব্যবহারের সংখ্যা) লাগলে আলাদা টেবিল + junction table-ই সঠিক নকশা।

৪.৮ JSON ও JSONB

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    metadata JSONB
);

INSERT INTO products (name, metadata) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "ports": ["usb-c", "hdmi"]}');

SELECT metadata->>'brand' FROM products;       -- text হিসেবে
SELECT metadata->'ram' FROM products;          -- jsonb হিসেবে
SELECT metadata #>> '{ports,0}' FROM products; -- nested path

JSON বনাম JSONB:

প্রায় সবসময় JSONB নেবেন। বিস্তারিত অধ্যায় ১৭-তে।

৪.৯ অন্যান্য কাজের Type

-- নেটওয়ার্ক
ip_address  INET,          -- 192.168.1.1 বা IPv6
network     CIDR,          -- 192.168.1.0/24
mac_address MACADDR,

-- জ্যামিতিক
location    POINT,         -- (x, y)

-- Range (একটা সীমা)
booking     TSTZRANGE,     -- সময়ের পরিসর
valid_score INT4RANGE,     -- সংখ্যার পরিসর

-- Full-text search
search_vec  TSVECTOR,

-- বাইনারি ডাটা
file_data   BYTEA,

-- XML
config      XML

INET type-টা IP ঠিকানার জন্য TEXT-এর চেয়ে অনেক ভালো — এটা যাচাই করে, subnet মেলাতে পারে (<< operator), এবং কম জায়গা নেয়।

৪.১০ Type Casting

SELECT '123'::INTEGER;              -- PostgreSQL এর নিজস্ব সিনট্যাক্স
SELECT CAST('123' AS INTEGER);      -- SQL standard
SELECT '2026-07-24'::DATE;
SELECT 3.99::INTEGER;               -- 4 (round হয়)
SELECT 123::TEXT;

:: লিখতে সংক্ষিপ্ত, কিন্তু CAST() অন্য database-এও কাজ করে।

৪.১১ অনুশীলনী

নিচের প্রতিটার জন্য সেরা data type বাছুন এবং কারণ বলুন: 1. একজন কর্মচারীর মাসিক বেতন 2. পণ্যের বিবরণ (লম্বা লেখা) 3. অ্যাকাউন্ট তৈরির সময় 4. ব্লগ পোস্টের ট্যাগের তালিকা 5. অর্ডারের অবস্থা (৫টা নির্দিষ্ট মান) 6. ব্যবহারকারীর দেশের কোড 7. একটা IoT ডিভাইসের পরিবর্তনশীল sensor ডাটা

DDL = Data Definition Language। যেসব কমান্ড দিয়ে database-এর গঠন বদলানো হয়।

৫.১ CREATE TABLE — পূর্ণাঙ্গ রূপ

CREATE TABLE IF NOT EXISTS employees (
    -- Primary key
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    -- বাধ্যতামূলক ফিল্ড
    first_name   TEXT NOT NULL,
    last_name    TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE,

    -- ঐচ্ছিক
    phone        VARCHAR(15),

    -- শর্ত সহ
    salary       NUMERIC(12,2) CHECK (salary > 0),
    age          INTEGER CHECK (age BETWEEN 18 AND 70),

    -- ডিফল্ট মান
    hired_at     DATE DEFAULT CURRENT_DATE,
    is_active    BOOLEAN DEFAULT TRUE,
    created_at   TIMESTAMPTZ DEFAULT now(),

    -- অন্য টেবিলের সাথে সম্পর্ক
    department_id INTEGER REFERENCES departments(id),

    -- একাধিক কলাম মিলে শর্ত (table-level constraint)
    CONSTRAINT valid_name CHECK (length(first_name) >= 2)
);

৫.২ Generated Column

একটা কলামের মান অন্য কলাম থেকে স্বয়ংক্রিয়ভাবে হিসাব হবে:

CREATE TABLE products (
    id        SERIAL PRIMARY KEY,
    price     NUMERIC(10,2),
    tax_rate  NUMERIC(4,2) DEFAULT 15.00,
    total     NUMERIC(10,2) GENERATED ALWAYS AS
              (price * (1 + tax_rate/100)) STORED
);

STORED মানে মানটা ডিস্কে সংরক্ষিত হয়। PostgreSQL এখনও VIRTUAL generated column সাপোর্ট করে না (Postgres 17 পর্যন্ত)।

এই কলামে সরাসরি INSERT বা UPDATE করা যাবে না — PostgreSQL নিজেই হিসাব করবে।

৫.৩ ALTER TABLE

-- নতুন কলাম
ALTER TABLE employees ADD COLUMN address TEXT;
ALTER TABLE employees ADD COLUMN IF NOT EXISTS notes TEXT;

-- default সহ নতুন কলাম (PG 11+ এ এটা দ্রুত, টেবিল rewrite করে না)
ALTER TABLE employees ADD COLUMN status TEXT DEFAULT 'active';

-- কলাম মোছা
ALTER TABLE employees DROP COLUMN address;
ALTER TABLE employees DROP COLUMN address CASCADE;  -- নির্ভরশীল view সহ

-- নাম বদল
ALTER TABLE employees RENAME COLUMN phone TO mobile;
ALTER TABLE employees RENAME TO staff;

-- type বদল
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(14,2);

-- জটিল type বদলে USING লাগে
ALTER TABLE employees
  ALTER COLUMN age TYPE TEXT USING age::TEXT;

-- NOT NULL যোগ/বাদ
ALTER TABLE employees ALTER COLUMN phone SET NOT NULL;
ALTER TABLE employees ALTER COLUMN phone DROP NOT NULL;

-- default বদল
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT FALSE;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;

-- constraint যোগ
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 8000);
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);

-- constraint বাদ
ALTER TABLE employees DROP CONSTRAINT chk_salary;

⚠️ Production-এ ALTER TABLE নিয়ে সাবধানতা

কিছু ALTER TABLE অপারেশন পুরো টেবিলে ACCESS EXCLUSIVE lock নেয় — মানে ওই সময়ে কেউ read বা write করতে পারবে না। কোটি row-এর টেবিলে এটা কয়েক মিনিটের downtime।

দ্রুত (metadata-only) অপারেশন: - কলাম যোগ (volatile default ছাড়া) - কলাম নাম বদল - NOT NULL বাদ দেওয়া - constraint কে NOT VALID হিসেবে যোগ করা

ধীর (টেবিল rewrite করে): - কলামের type বদল (কিছু ক্ষেত্র ছাড়া) - SET NOT NULL (পুরো টেবিল স্ক্যান করে)

নিরাপদ কৌশল — constraint যোগ করার সময় দুই ধাপে:

-- ধাপ ১: শুধু নতুন ডাটার জন্য প্রযোজ্য, lock প্রায় নেই
ALTER TABLE orders ADD CONSTRAINT chk_amount
  CHECK (amount > 0) NOT VALID;

-- ধাপ ২: পুরনো ডাটা যাচাই, দুর্বল lock নেয়
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount;

৫.৪ DROP ও TRUNCATE

DROP TABLE employees;
DROP TABLE IF EXISTS employees;
DROP TABLE employees CASCADE;   -- foreign key দিয়ে যুক্ত সব মুছবে

-- সব row মোছা, টেবিল থাকবে
TRUNCATE TABLE employees;

-- sequence রিসেট সহ
TRUNCATE TABLE employees RESTART IDENTITY;

-- সম্পর্কিত টেবিল সহ
TRUNCATE TABLE employees, departments CASCADE;

TRUNCATE বনাম DELETE:

TRUNCATE PostgreSQL-এ transactional — BEGIN ... TRUNCATE ... ROLLBACK করলে ডাটা ফিরে আসবে। (MySQL-এ আসবে না।)

৫.৫ Comment যোগ করা

ডকুমেন্টেশনের জন্য অসাধারণ, অথচ প্রায় কেউ ব্যবহার করে না:

COMMENT ON TABLE employees IS 'কোম্পানির সব কর্মচারীর তথ্য';
COMMENT ON COLUMN employees.salary IS 'মাসিক মূল বেতন, ট্যাক্স ছাড়া (BDT)';

-- দেখুন
\d+ employees

Constraint হলো এমন নিয়ম যা database নিজে প্রয়োগ করে। অ্যাপ্লিকেশন কোডে validation লিখলেও database-level constraint অবশ্যই রাখবেন — কারণ ডাটা শুধু আপনার অ্যাপ দিয়ে ঢোকে না, কেউ psql দিয়েও ঢোকাতে পারে।

৬.১ NOT NULL

name TEXT NOT NULL

সবচেয়ে সহজ, অথচ সবচেয়ে কার্যকর constraint।

৬.২ UNIQUE

email TEXT UNIQUE

-- একাধিক কলাম মিলে অনন্য
CONSTRAINT uq_student_course UNIQUE (student_id, course_id)

একটা সূক্ষ্ম ব্যাপার: UNIQUE কলামে একাধিক NULL থাকতে পারে! কারণ SQL-এ NULL = NULL মিথ্যা নয়, অজানা। তাই দুটো NULL "সমান" নয়।

PostgreSQL 15 থেকে এটা নিয়ন্ত্রণ করা যায়:

CONSTRAINT uq_email UNIQUE NULLS NOT DISTINCT (email)
-- এখন শুধু একটাই NULL থাকতে পারবে

৬.৩ PRIMARY KEY

PRIMARY KEY = NOT NULL + UNIQUE + টেবিলের প্রধান পরিচয়।

-- একক কলাম
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- যৌগিক (composite) primary key
CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id  INT REFERENCES courses(id),
    grade      CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

প্রতি টেবিলে ঠিক একটাই primary key থাকতে পারে।

Natural key বনাম Surrogate key: - Natural key — বাস্তব অর্থবহ মান (NID নম্বর, email) - Surrogate key — কৃত্রিম, শুধু পরিচয়ের জন্য (auto-increment id)

Surrogate key ব্যবহার করাই নিরাপদ। কারণ natural key বদলে যেতে পারে — মানুষ email বদলায়, এমনকি সরকারও ID ফরম্যাট বদলায়।

৬.৪ FOREIGN KEY — সবচেয়ে গুরুত্বপূর্ণ

Foreign key নিশ্চিত করে যে একটা টেবিলের মান অন্য টেবিলে সত্যিই আছে। একেই বলে referential integrity।

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INTEGER REFERENCES departments(id)
);

এখন departments-এ id=5 না থাকলে employees-এ department_id = 5 ঢোকানো যাবে না।

ON DELETE / ON UPDATE আচরণ

মূল প্রশ্ন — parent row মুছে গেলে child row-এর কী হবে?

department_id INTEGER REFERENCES departments(id)
    ON DELETE RESTRICT     -- child থাকলে parent মুছতেই দেবে না (default অনেকটা এটাই)
    ON UPDATE CASCADE      -- parent এর id বদলালে child ও বদলাবে

বাস্তব উদাহরণে বাছাই:

-- অর্ডার মুছলে তার আইটেমগুলোও মুছে যাওয়াই যুক্তিসঙ্গত
CREATE TABLE order_items (
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    ...
);

-- ডিপার্টমেন্ট বন্ধ হলে কর্মচারী মুছে যাবে না, শুধু department_id খালি হবে
CREATE TABLE employees (
    department_id INT REFERENCES departments(id) ON DELETE SET NULL,
    ...
);

-- অর্ডার আছে এমন কাস্টমার কিছুতেই মোছা যাবে না
CREATE TABLE orders (
    customer_id INT REFERENCES customers(id) ON DELETE RESTRICT,
    ...
);

৬.৫ CHECK

-- কলাম-ভিত্তিক
age    INTEGER CHECK (age >= 18),
price  NUMERIC CHECK (price > 0),
email  TEXT CHECK (email LIKE '%@%.%'),
status TEXT CHECK (status IN ('active', 'inactive', 'banned')),

-- টেবিল-ভিত্তিক (একাধিক কলাম জড়িত)
CONSTRAINT valid_dates CHECK (end_date > start_date),
CONSTRAINT discount_logic CHECK (discount_price IS NULL OR discount_price < price)

CHECK-এ NULL নিয়ে সতর্কতা: CHECK (age >= 18) — যদি age হয় NULL, তাহলে শর্তটার ফল NULL, আর PostgreSQL NULL কে পাস ধরে নেয়। তাই NULL ঢুকে যাবে! আটকাতে হলে NOT NULL আলাদা করে দিতে হবে।

৬.৬ EXCLUSION Constraint (অ্যাডভান্স)

একটা অনন্য ফিচার — "একই সময়ে একই রুম দুবার বুক করা যাবে না" ধরনের নিয়ম।

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    id       SERIAL PRIMARY KEY,
    room_id  INTEGER NOT NULL,
    period   TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        period WITH &&        -- && মানে overlap
    )
);

এখন একই room_id-এর জন্য সময় overlap করলে PostgreSQL নিজেই আটকে দেবে। অ্যাপ্লিকেশন কোডে race condition নিয়ে চিন্তা করতে হবে না — এটাই database-level constraint-এর শক্তি।

৬.৭ DEFERRABLE Constraint

কিছু ক্ষেত্রে transaction-এর মাঝখানে constraint সাময়িকভাবে ভাঙা দরকার হয় (যেমন circular reference):

ALTER TABLE employees
  ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id)
  REFERENCES employees(id)
  DEFERRABLE INITIALLY DEFERRED;

এতে যাচাই হবে COMMIT-এর সময়, প্রতিটা statement-এর পরে নয়।

৬.৮ অনুশীলনী

একটা লাইব্রেরি সিস্টেমের জন্য টেবিল ডিজাইন করুন: - members — সদস্য - books — বই - loans — কে কোন বই কবে নিল, কবে ফেরত দিল

শর্ত: - একই বই একই সময়ে দুজন নিতে পারবে না - ফেরতের তারিখ নেওয়ার তারিখের পরে হতে হবে - সদস্য মুছে ফেললে তার loan রেকর্ড থাকবে কিন্তু member_id NULL হবে - বই মোছার চেষ্টা করলে, যদি loan থাকে, আটকে দেবে

DML = Data Manipulation Language। গঠন নয়, ডাটা নিয়ে কাজ।

এই অধ্যায় থেকে আমরা একটা নমুনা database ব্যবহার করব। নিচের স্ক্রিপ্টটা চালিয়ে নিন — পুরো বই জুড়ে এটাই লাগবে।

৭.১ নমুনা Database তৈরি

CREATE DATABASE shop;
\c shop

CREATE TABLE categories (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE customers (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT UNIQUE NOT NULL,
    city       TEXT,
    phone      VARCHAR(15),
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE products (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    price       NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    status      TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','paid','shipped','delivered','cancelled'))
);

CREATE TABLE order_items (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL
);

-- Foreign key কলামে index (মনে আছে তো?)
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_customer   ON orders(customer_id);
CREATE INDEX idx_items_order       ON order_items(order_id);
CREATE INDEX idx_items_product     ON order_items(product_id);

নমুনা ডাটা:

INSERT INTO categories (name) VALUES
('Electronics'), ('Books'), ('Clothing'), ('Home');

INSERT INTO customers (name, email, city, phone) VALUES
('রহিম উদ্দিন',   'rahim@example.com',  'Dhaka',      '01711000001'),
('করিমা বেগম',    'karima@example.com', 'Chattogram', '01711000002'),
('জসিম মিয়া',     'jasim@example.com',  'Dhaka',      '01711000003'),
('নাসরিন আক্তার', 'nasrin@example.com', 'Sylhet',     '01711000004'),
('তানভীর হাসান',  'tanvir@example.com', 'Dhaka',      NULL),
('সুমাইয়া খাতুন', 'sumaiya@example.com','Rajshahi',   '01711000006');

INSERT INTO products (name, category_id, price, stock) VALUES
('Laptop Dell XPS',   1, 145000.00, 12),
('Wireless Mouse',    1,   1200.00, 150),
('Mechanical Keyboard',1,  6500.00, 45),
('SQL শেখার বই',      2,    450.00, 200),
('Data Science Book', 2,   1800.00, 60),
('Cotton T-Shirt',    3,    750.00, 300),
('Winter Jacket',     3,   3200.00, 40),
('Table Lamp',        4,    900.00, 80),
('Coffee Maker',      4,   5500.00, 25),
('USB-C Cable',       1,    350.00, 500);

INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2026-01-15', 'delivered'),
(1, '2026-02-20', 'delivered'),
(2, '2026-02-25', 'shipped'),
(3, '2026-03-01', 'paid'),
(1, '2026-03-10', 'pending'),
(4, '2026-03-12', 'delivered'),
(2, '2026-04-05', 'cancelled'),
(5, '2026-04-18', 'delivered');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 145000.00), (1, 2, 2, 1200.00),
(2, 4, 3, 450.00),
(3, 3, 1, 6500.00), (3, 10, 2, 350.00),
(4, 6, 5, 750.00), (4, 7, 1, 3200.00),
(5, 9, 1, 5500.00),
(6, 5, 2, 1800.00), (6, 4, 1, 450.00),
(7, 8, 3, 900.00),
(8, 1, 1, 145000.00), (8, 3, 1, 6500.00), (8, 2, 1, 1200.00);

৭.২ INSERT

-- একটা row
INSERT INTO customers (name, email, city)
VALUES ('আরিফ হোসেন', 'arif@example.com', 'Khulna');

-- একাধিক row একসাথে (অনেক দ্রুত!)
INSERT INTO customers (name, email, city) VALUES
('ইমরান আলী',  'imran@example.com',  'Barishal'),
('ফারজানা ইয়াসমিন', 'farzana@example.com', 'Dhaka');

-- সব কলামের মান দিলে কলামের নাম বাদ দেওয়া যায় (কিন্তু দেবেন না!)
-- INSERT INTO categories VALUES (5, 'Sports');

-- অন্য টেবিল থেকে
INSERT INTO customers_archive (name, email)
SELECT name, email FROM customers WHERE created_at < '2025-01-01';

৭.৩ RETURNING — PostgreSQL-এর দারুণ সুবিধা

INSERT/UPDATE/DELETE-এর পরে প্রভাবিত row ফেরত পাওয়া যায়। নতুন id জানতে আলাদা SELECT চালাতে হয় না।

INSERT INTO customers (name, email)
VALUES ('নতুন গ্রাহক', 'new@example.com')
RETURNING id, name, created_at;

UPDATE products SET price = price * 1.1
WHERE category_id = 1
RETURNING id, name, price;

DELETE FROM orders WHERE status = 'cancelled'
RETURNING *;

MySQL-এ এটা নেই — PostgreSQL-এর একটা বড় সুবিধা।

৭.৪ UPSERT — ON CONFLICT

"থাকলে আপডেট করো, না থাকলে ঢোকাও" — এই কাজটা।

-- সংঘর্ষ হলে চুপচাপ এড়িয়ে যাও
INSERT INTO customers (name, email, city)
VALUES ('রহিম উদ্দিন', 'rahim@example.com', 'Dhaka')
ON CONFLICT (email) DO NOTHING;

-- সংঘর্ষ হলে আপডেট করো
INSERT INTO customers (name, email, city)
VALUES ('রহিম উদ্দিন', 'rahim@example.com', 'Cumilla')
ON CONFLICT (email)
DO UPDATE SET
    city = EXCLUDED.city,
    name = EXCLUDED.name
RETURNING *;

EXCLUDED একটা বিশেষ ছদ্ম-টেবিল — যে row-টা ঢোকাতে চেয়েছিলেন সেটাকে বোঝায়।

শর্তসাপেক্ষ upsert:

INSERT INTO products (name, category_id, price, stock)
VALUES ('Wireless Mouse', 1, 1100.00, 50)
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price
WHERE products.price > EXCLUDED.price;   -- শুধু দাম কমলে আপডেট

মনে রাখবেন — ON CONFLICT কাজ করতে হলে ওই কলামে UNIQUE constraint বা unique index থাকতেই হবে।

৭.৫ UPDATE

-- সাধারণ
UPDATE products SET price = 1300 WHERE id = 2;

-- একাধিক কলাম
UPDATE customers
SET city = 'Dhaka', phone = '01799999999'
WHERE id = 5;

-- হিসাব করে
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET price = price * 1.05 WHERE category_id = 2;

-- অন্য টেবিলের ডাটা দিয়ে (UPDATE ... FROM — PostgreSQL এর নিজস্ব)
UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE oi.product_id = p.id
  AND oi.order_id = 5;

-- subquery দিয়ে
UPDATE customers
SET city = 'Unknown'
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');

BEGIN;
UPDATE products SET price = price * 2 WHERE category_id = 1;
SELECT id, name, price FROM products WHERE category_id = 1;
-- ঠিক আছে? → COMMIT;
-- ভুল হয়েছে? → ROLLBACK;

৭.৬ DELETE

DELETE FROM orders WHERE id = 7;
DELETE FROM orders WHERE status = 'cancelled';

-- অন্য টেবিলের সাথে মিলিয়ে (USING)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id AND o.status = 'cancelled';

-- সব মোছা (TRUNCATE বেশি দ্রুত)
DELETE FROM order_items;

Soft Delete — বাস্তব প্রজেক্টে পছন্দনীয়

সত্যিকারের DELETE-এর বদলে একটা কলামে চিহ্ন দিয়ে রাখা:

ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;

-- "মোছা"
UPDATE customers SET deleted_at = now() WHERE id = 5;

-- সক্রিয় গ্রাহক
SELECT * FROM customers WHERE deleted_at IS NULL;

-- সুবিধাজনক view
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;

সুবিধা: ভুল করে মুছলে ফেরানো যায়, ইতিহাস থাকে, রিপোর্টে পুরনো ডাটা ভাঙে না। অসুবিধা: প্রতিটা query-তে deleted_at IS NULL মনে রাখতে হয়, টেবিল বড় হতে থাকে।

৭.৭ MERGE (PostgreSQL 15+)

SQL standard-এর পূর্ণাঙ্গ upsert:

MERGE INTO products p
USING incoming_stock s ON p.id = s.product_id
WHEN MATCHED AND s.quantity = 0 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET stock = p.stock + s.quantity
WHEN NOT MATCHED THEN
    INSERT (name, price, stock) VALUES (s.name, s.price, s.quantity);

সাধারণ upsert-এর জন্য ON CONFLICT সহজ; জটিল, একাধিক শর্তওয়ালা ক্ষেত্রে MERGE।

৮.১ SELECT-এর গঠন ও কার্যক্রম

লেখার ক্রম:

SELECT   কলাম
FROM     টেবিল
WHERE    শর্ত
GROUP BY কলাম
HAVING   গ্রুপের শর্ত
ORDER BY কলাম
LIMIT    সংখ্যা
OFFSET   সংখ্যা;

কিন্তু PostgreSQL যে ক্রমে চালায় সেটা আলাদা — এটা বুঝলে অনেক বিভ্রান্তি কেটে যাবে:

১. FROM      → কোন টেবিল
২. JOIN      → জোড়া লাগানো
৩. WHERE     → row ছাঁকা
৪. GROUP BY  → দল বাঁধা
৫. HAVING    → দল ছাঁকা
৬. SELECT    → কলাম বাছাই ও alias তৈরি
৭. DISTINCT  → ডুপ্লিকেট বাদ
৮. ORDER BY  → সাজানো
৯. LIMIT     → সীমা

এখান থেকেই বোঝা যায়: - WHERE-এ SELECT-এ বানানো alias ব্যবহার করা যায় না (WHERE আগে চলে) - ORDER BY-তে alias ব্যবহার করা যায় (ORDER BY পরে চলে) - WHERE-এ aggregate function ব্যবহার করা যায় না — HAVING লাগে

-- ❌ ভুল
SELECT price * 1.15 AS total FROM products WHERE total > 1000;
-- ERROR: column "total" does not exist

-- ✅ ঠিক
SELECT price * 1.15 AS total FROM products WHERE price * 1.15 > 1000;

-- ✅ ORDER BY তে alias চলে
SELECT price * 1.15 AS total FROM products ORDER BY total DESC;

৮.২ মৌলিক SELECT

SELECT * FROM products;                    -- সব কলাম
SELECT name, price FROM products;          -- নির্দিষ্ট কলাম
SELECT name AS product_name FROM products; -- alias
SELECT name "Product Name" FROM products;  -- স্পেস থাকলে ডাবল কোট

-- হিসাব
SELECT name, price, price * 0.15 AS vat, price * 1.15 AS total
FROM products;

-- টেক্সট জোড়া
SELECT name || ' — ৳' || price AS label FROM products;
SELECT CONCAT(name, ' (', stock, ' টি স্টকে)') FROM products;

|| দিয়ে জোড়া লাগালে কোনো একটা মান NULL হলে পুরো ফলাফল NULL হয়ে যায়। CONCAT() NULL-কে খালি স্ট্রিং ধরে — বেশি নিরাপদ।

৮.৩ WHERE — শর্ত

-- তুলনা
SELECT * FROM products WHERE price > 1000;
SELECT * FROM products WHERE price BETWEEN 500 AND 5000;
SELECT * FROM products WHERE stock <> 0;         -- != ও চলে

-- যুক্তি
SELECT * FROM products WHERE price > 1000 AND stock > 10;
SELECT * FROM products WHERE category_id = 1 OR category_id = 2;
SELECT * FROM products WHERE NOT (stock = 0);

-- তালিকা
SELECT * FROM products WHERE category_id IN (1, 2);
SELECT * FROM products WHERE category_id NOT IN (3, 4);

-- NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

⚠️ NULL — যেখানে সবাই হোঁচট খায়

NULL মানে "শূন্য" নয়, "খালি স্ট্রিং" নয় — মানে "অজানা"।

SELECT NULL = NULL;      -- NULL (TRUE নয়!)
SELECT NULL <> NULL;     -- NULL
SELECT 5 + NULL;         -- NULL
SELECT 'abc' || NULL;    -- NULL
SELECT NULL IS NULL;     -- TRUE ← এভাবেই যাচাই করতে হয়

অজানা + যেকোনো কিছু = অজানা। এই যুক্তিটা ধরতে পারলে সব পরিষ্কার।

সবচেয়ে সাধারণ ফাঁদ:

-- ঢাকা ছাড়া বাকি সবাই চাই
SELECT * FROM customers WHERE city <> 'Dhaka';
-- যাদের city NULL, তারা আসবে না! কারণ NULL <> 'Dhaka' → NULL

সমাধান:

SELECT * FROM customers WHERE city IS DISTINCT FROM 'Dhaka';
-- অথবা
SELECT * FROM customers WHERE city <> 'Dhaka' OR city IS NULL;

IS DISTINCT FROM NULL-নিরাপদ তুলনা — এটা মনে রাখা মূল্যবান।

NULL সামলানোর ফাংশন:

SELECT COALESCE(phone, 'নম্বর নেই') FROM customers;   -- প্রথম non-NULL
SELECT COALESCE(a, b, c, 'default');                   -- যত খুশি আর্গুমেন্ট

SELECT NULLIF(stock, 0) FROM products;   -- stock 0 হলে NULL দেবে
-- ভাগ করার সময় শূন্য দিয়ে ভাগ ঠেকাতে দারুণ:
SELECT total / NULLIF(count, 0) FROM stats;

৮.৪ Pattern Matching

-- LIKE (case-sensitive)
SELECT * FROM products WHERE name LIKE 'W%';      -- W দিয়ে শুরু
SELECT * FROM products WHERE name LIKE '%Book';   -- Book দিয়ে শেষ
SELECT * FROM products WHERE name LIKE '%less%';  -- মাঝে less
SELECT * FROM products WHERE name LIKE '_ouse';   -- _ = ঠিক একটা অক্ষর

-- ILIKE (case-insensitive) — PostgreSQL এর নিজস্ব
SELECT * FROM products WHERE name ILIKE '%laptop%';

-- Regular Expression
SELECT * FROM customers WHERE email ~ '^[a-z]+@example\.com$';
SELECT * FROM customers WHERE email ~* 'EXAMPLE';   -- ~* = case-insensitive
SELECT * FROM customers WHERE email !~ 'gmail';     -- মেলে না এমন

Performance সতর্কতা: LIKE '%abc%' (শুরুতে %) সাধারণ B-tree index ব্যবহার করতে পারে না — পুরো টেবিল স্ক্যান করে। বড় টেবিলে এমন খোঁজার দরকার হলে pg_trgm extension + GIN index লাগবে (অধ্যায় ১৪)।

৮.৫ ORDER BY

SELECT * FROM products ORDER BY price;              -- ছোট থেকে বড় (ASC default)
SELECT * FROM products ORDER BY price DESC;         -- বড় থেকে ছোট
SELECT * FROM products ORDER BY category_id, price DESC;  -- একাধিক স্তর

-- NULL কোথায় বসবে
SELECT * FROM customers ORDER BY phone NULLS FIRST;
SELECT * FROM customers ORDER BY phone DESC NULLS LAST;

-- কলামের ক্রমিক নম্বর দিয়ে (পড়তে কষ্ট, এড়িয়ে চলুন)
SELECT name, price FROM products ORDER BY 2 DESC;

-- হিসাবের উপর
SELECT name, price, stock FROM products ORDER BY price * stock DESC;

-- এলোমেলো
SELECT * FROM products ORDER BY random() LIMIT 3;

Default: ASC-এ NULL শেষে, DESC-এ NULL শুরুতে।

৮.৬ LIMIT ও OFFSET

SELECT * FROM products ORDER BY price DESC LIMIT 5;         -- সবচেয়ে দামি ৫টা
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;      -- ৩য় পাতা

-- SQL standard সিনট্যাক্স
SELECT * FROM products ORDER BY id OFFSET 20 FETCH FIRST 10 ROWS ONLY;

OFFSET-এর সমস্যা ও Keyset Pagination

OFFSET 100000 মানে PostgreSQL প্রথম ১ লাখ row পড়বে এবং ফেলে দেবে, তারপর পরেরগুলো দেবে। যত গভীরে যাবেন, তত ধীর।

সমাধান — keyset (cursor) pagination:

-- প্রথম পাতা
SELECT * FROM products ORDER BY id LIMIT 20;
-- শেষ id ছিল ধরা যাক 20

-- পরের পাতা
SELECT * FROM products WHERE id > 20 ORDER BY id LIMIT 20;

এটা index ব্যবহার করে সরাসরি ঝাঁপ দেয় — যত গভীরেই যান, গতি একই থাকে। বড় ডাটাসেটে এটাই সঠিক পদ্ধতি।

৮.৭ DISTINCT

SELECT DISTINCT city FROM customers;
SELECT DISTINCT city, status FROM orders o JOIN customers c ON o.customer_id = c.id;

-- DISTINCT ON — PostgreSQL এর বিশেষ ক্ষমতা
-- প্রতিটা গ্রাহকের সর্বশেষ অর্ডার
SELECT DISTINCT ON (customer_id)
       customer_id, id, order_date, status
FROM orders
ORDER BY customer_id, order_date DESC;

DISTINCT ON-এ ORDER BY-র প্রথম কলামগুলো DISTINCT ON-এর কলামের সাথে মিলতেই হবে। এটা "প্রতি গ্রুপে প্রথমটা" বের করার সবচেয়ে সংক্ষিপ্ত উপায় — অন্য database-এ এর জন্য জটিল window function লিখতে হয়।

৮.৮ CASE — শর্তসাপেক্ষ মান

SELECT name, price,
    CASE
        WHEN price < 1000  THEN 'সস্তা'
        WHEN price < 10000 THEN 'মাঝারি'
        ELSE 'দামি'
    END AS price_range
FROM products;

-- সংক্ষিপ্ত রূপ (একটা মানের সাথে তুলনা)
SELECT name,
    CASE category_id
        WHEN 1 THEN 'ইলেকট্রনিক্স'
        WHEN 2 THEN 'বই'
        ELSE 'অন্যান্য'
    END AS category
FROM products;

-- ORDER BY তে CASE — কাস্টম ক্রম
SELECT * FROM orders
ORDER BY CASE status
    WHEN 'pending'   THEN 1
    WHEN 'paid'      THEN 2
    WHEN 'shipped'   THEN 3
    WHEN 'delivered' THEN 4
    ELSE 5
END;

CASE + aggregate একসাথে ব্যবহার করে pivot table বানানো যায় — অধ্যায় ৯-এ দেখব।

৮.৯ কাজের String Function

SELECT UPPER('hello'), LOWER('HELLO'), INITCAP('hello world');
SELECT LENGTH('বাংলাদেশ');                -- অক্ষর গণনা (৮)
SELECT TRIM('  space  '), LTRIM('xxabc','x'), RTRIM('abcxx','x');
SELECT SUBSTRING('PostgreSQL', 1, 8);      -- 'Postgres'
SELECT LEFT('PostgreSQL', 4), RIGHT('PostgreSQL', 3);
SELECT REPLACE('a-b-c', '-', '/');         -- 'a/b/c'
SELECT POSITION('SQL' IN 'PostgreSQL');    -- 8
SELECT SPLIT_PART('a,b,c', ',', 2);        -- 'b'
SELECT REPEAT('ab', 3);                    -- 'ababab'
SELECT LPAD('5', 3, '0');                  -- '005'
SELECT REVERSE('abc');
SELECT STRING_TO_ARRAY('a,b,c', ',');
SELECT MD5('password');

৮.১০ কাজের Math Function

SELECT ROUND(3.567, 2);      -- 3.57
SELECT CEIL(3.2), FLOOR(3.9);
SELECT ABS(-15), SIGN(-15);
SELECT POWER(2, 10), SQRT(144);
SELECT MOD(10, 3);           -- 1
SELECT TRUNC(3.99, 1);       -- 3.9 (কাটে, round করে না)
SELECT random();             -- 0 থেকে 1
SELECT FLOOR(random() * 100) + 1;   -- 1-100 এলোমেলো
SELECT GREATEST(3, 7, 2), LEAST(3, 7, 2);

৮.১১ অনুশীলনী

নমুনা database ব্যবহার করে: 1. ৫০০০ টাকার বেশি দামের সব পণ্য দাম অনুযায়ী সাজিয়ে দেখান 2. যাদের ফোন নম্বর নেই তাদের তালিকা 3. প্রতিটা পণ্যের নাম, দাম, এবং "মোট স্টক মূল্য" (দাম × স্টক) 4. ঢাকার বাইরের সব গ্রাহক (NULL সহ) 5. প্রতিটা গ্রাহকের সবচেয়ে সাম্প্রতিক অর্ডার (DISTINCT ON দিয়ে) 6. পণ্যের নামে "book" আছে এমন সব পণ্য (ছোট-বড় হাতের অক্ষর নির্বিশেষে)

৯.১ মৌলিক Aggregate

SELECT COUNT(*) FROM products;              -- মোট row
SELECT COUNT(phone) FROM customers;         -- NULL বাদে গণনা
SELECT COUNT(DISTINCT city) FROM customers; -- ভিন্ন শহরের সংখ্যা
SELECT SUM(stock) FROM products;
SELECT AVG(price) FROM products;
SELECT MIN(price), MAX(price) FROM products;
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

COUNT(*) বনাম COUNT(column) — এই পার্থক্যটা ইন্টারভিউতে খুব আসে: - COUNT(*) — সব row গোনে, NULL সহ - COUNT(column) — ওই কলামে NULL নেই এমন row গোনে

SELECT COUNT(*) AS total, COUNT(phone) AS with_phone FROM customers;
-- total = 6, with_phone = 5

৯.২ GROUP BY

-- প্রতি শহরে কতজন গ্রাহক
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;

-- প্রতি ক্যাটাগরিতে পণ্যের সংখ্যা ও গড় দাম
SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       MIN(p.price) AS cheapest,
       MAX(p.price) AS costliest
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

সোনালি নিয়ম: SELECT-এ যে কলাম aggregate function-এর ভেতরে নেই, সেটা অবশ্যই GROUP BY-তে থাকতে হবে।

-- ❌ ভুল
SELECT city, name, COUNT(*) FROM customers GROUP BY city;
-- ERROR: column "customers.name" must appear in the GROUP BY clause

-- ✅ ঠিক
SELECT city, COUNT(*) FROM customers GROUP BY city;

একটা ব্যতিক্রম: primary key দিয়ে group করলে ওই টেবিলের অন্য কলামও SELECT-এ দেওয়া যায় (functional dependency), যেমন উপরের উদাহরণে GROUP BY c.id, c.name-এ শুধু c.id লিখলেও চলত।

৯.৩ HAVING

WHERE row ছাঁকে (group হওয়ার আগে), HAVING group ছাঁকে (group হওয়ার পরে)।

-- একাধিক অর্ডার আছে এমন গ্রাহক
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- WHERE আর HAVING একসাথে
SELECT customer_id, COUNT(*) AS delivered_count
FROM orders
WHERE status = 'delivered'          -- আগে ছাঁকা: শুধু delivered
GROUP BY customer_id
HAVING COUNT(*) >= 2                -- পরে ছাঁকা: যাদের ২+
ORDER BY delivered_count DESC;

Performance টিপ: যা WHERE-এ ছাঁকা যায় তা HAVING-এ ছাঁকবেন না। WHERE আগে চলে বলে কম row নিয়ে group করতে হয় — অনেক দ্রুত।

৯.৪ শক্তিশালী Aggregate

-- টেক্সট জোড়া
SELECT city, STRING_AGG(name, ', ' ORDER BY name) AS customers
FROM customers
GROUP BY city;

-- array বানানো
SELECT category_id, ARRAY_AGG(name ORDER BY price DESC) AS products
FROM products
GROUP BY category_id;

-- JSON বানানো
SELECT category_id, JSON_AGG(json_build_object('name', name, 'price', price))
FROM products
GROUP BY category_id;

-- পরিসংখ্যান
SELECT
    AVG(price) AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY price) AS p95,
    STDDEV(price) AS std_deviation,
    MODE() WITHIN GROUP (ORDER BY category_id) AS most_common_category
FROM products;

-- Boolean aggregate
SELECT category_id,
       BOOL_AND(stock > 0) AS all_in_stock,
       BOOL_OR(price > 100000) AS has_expensive
FROM products
GROUP BY category_id;

STRING_AGG রিপোর্ট বানানোর জন্য দুর্দান্ত — "ঢাকার গ্রাহক: রহিম, জসিম, তানভীর"।

৯.৫ FILTER — শর্তসাপেক্ষ Aggregate

PostgreSQL-এর একটা মার্জিত ফিচার:

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
    COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

CASE দিয়েও একই কাজ হয়, কিন্তু FILTER অনেক পরিষ্কার:

-- পুরনো পদ্ধতি
SELECT COUNT(CASE WHEN status = 'delivered' THEN 1 END) FROM orders;

৯.৬ Pivot Table বানানো

SELECT
    c.city,
    COUNT(*) FILTER (WHERE o.status = 'delivered') AS delivered,
    COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending,
    COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
    COUNT(*) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total DESC;

ফলাফল:

city        | delivered | pending | cancelled | total
------------+-----------+---------+-----------+-------
Dhaka       |     3     |    1    |     0     |   4
Chattogram  |     0     |    0    |     1     |   2
...

৯.৭ GROUPING SETS, ROLLUP, CUBE

একাধিক স্তরে একসাথে group করা:

-- ROLLUP: উপ-মোট ও সর্বমোট
SELECT c.city, o.status, COUNT(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY ROLLUP (c.city, o.status);
-- দেবে: প্রতি (city, status), প্রতি city এর মোট, এবং সর্বমোট

-- CUBE: সব সম্ভাব্য সমন্বয়
GROUP BY CUBE (c.city, o.status);

-- GROUPING SETS: আপনি যেগুলো চান শুধু সেগুলো
GROUP BY GROUPING SETS ((c.city), (o.status), ());

-- মোট লাইন চেনার জন্য
SELECT c.city, o.status, COUNT(*),
       GROUPING(c.city) AS is_city_total
FROM ...

আর্থিক রিপোর্ট বানানোর সময় ROLLUP অসাধারণ কাজে দেয়।

৯.৮ সময়ভিত্তিক গ্রুপিং

-- মাসিক অর্ডার
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

-- সপ্তাহের কোন দিনে বেশি অর্ডার
SELECT TO_CHAR(order_date, 'Day') AS weekday,
       COUNT(*) AS orders
FROM orders
GROUP BY weekday, EXTRACT(DOW FROM order_date)
ORDER BY EXTRACT(DOW FROM order_date);

খালি মাসগুলোও দেখাতে চাইলে generate_series দিয়ে:

SELECT m.month, COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series('2026-01-01'::date, '2026-06-01'::date, '1 month') AS m(month)
LEFT JOIN orders o ON DATE_TRUNC('month', o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

৯.৯ অনুশীলনী

1. প্রতিটা অর্ডারের মোট মূল্য বের করুন (order_items থেকে)
2. সবচেয়ে বেশি টাকা খরচ করেছেন এমন ৩ জন গ্রাহক
3. প্রতিটা ক্যাটাগরিতে কত টাকার পণ্য বিক্রি হয়েছে
4. মাসভিত্তিক বিক্রির পরিমাণ
5. এমন পণ্য যেগুলো কোনো অর্ডারেই নেই

JOIN ছাড়া relational database অর্থহীন। এই অধ্যায়টা ভালো করে বুঝুন।

১০.১ ছয় ধরনের JOIN

INNER JOIN       → দুই টেবিলেই আছে এমন
LEFT JOIN        → বাম টেবিলের সব + ডানের মিল
RIGHT JOIN       → ডান টেবিলের সব + বামের মিল
FULL OUTER JOIN  → দুই দিকের সব
CROSS JOIN       → প্রতিটার সাথে প্রতিটা (কার্তেসীয় গুণফল)
SELF JOIN        → টেবিলের সাথে নিজেরই

১০.২ INNER JOIN

শুধু মিলে যাওয়া row।

SELECT p.name AS product, c.name AS category, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

INNER লেখাটা ঐচ্ছিক — শুধু JOIN লিখলেই INNER বোঝায়।

তিন বা তার বেশি টেবিল:

SELECT
    o.id AS order_id,
    cu.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers cu   ON cu.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
ORDER BY o.id;

১০.৩ LEFT JOIN

বাম টেবিলের সব row থাকবে; ডানে মিল না পেলে NULL।

-- সব গ্রাহক, অর্ডার থাকুক বা না থাকুক
SELECT c.name, o.id AS order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

সবচেয়ে দরকারি প্রয়োগ — "যাদের নেই" খুঁজে বের করা:

-- যারা কখনো অর্ডার করেনি
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- যেসব পণ্য কখনো বিক্রি হয়নি
SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

এই "LEFT JOIN + WHERE ... IS NULL" প্যাটার্নটা মুখস্থ করে ফেলুন — বাস্তব কাজে অসংখ্যবার লাগবে।

🔥 LEFT JOIN-এ ON বনাম WHERE — একটা মারাত্মক ফাঁদ

-- ✅ ঠিক: সব গ্রাহক দেখাবে, শুধু delivered অর্ডারগুলো সাথে
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered';

-- ❌ ভুল: এটা INNER JOIN হয়ে গেছে!
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered';

কেন? দ্বিতীয়টায় যাদের অর্ডার নেই তাদের o.status হয় NULL, আর NULL = 'delivered' মিথ্যা — তাই তারা বাদ পড়ে যায়।

নিয়ম: LEFT JOIN-এর ডান টেবিলের শর্ত ON-এ লিখুন, WHERE-এ নয়।

১০.৪ RIGHT JOIN ও FULL OUTER JOIN

-- RIGHT JOIN — LEFT JOIN এর উল্টো
SELECT c.name, o.id
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;

-- FULL OUTER JOIN — দুই দিকের সব
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

বাস্তবে RIGHT JOIN খুব কম ব্যবহৃত হয় — টেবিলের ক্রম উল্টে LEFT JOIN লিখলেই পড়তে সহজ হয়।

FULL OUTER JOIN-এর একটা কাজের প্রয়োগ — দুটো ডাটাসেটের পার্থক্য বের করা:

SELECT COALESCE(a.id, b.id) AS id,
       a.value AS old_value, b.value AS new_value
FROM old_data a
FULL OUTER JOIN new_data b ON a.id = b.id
WHERE a.value IS DISTINCT FROM b.value;

১০.৫ CROSS JOIN

প্রতিটা row-কে প্রতিটার সাথে মেলায়। ৫ × ৪ = ২০ row।

SELECT c.name, s.size
FROM colors c
CROSS JOIN sizes s;

দুর্ঘটনাবশত হয়ে গেলে বিপদ (JOIN লিখে ON ভুলে গেলে), কিন্তু ইচ্ছাকৃতভাবে দরকারি — যেমন সব সম্ভাব্য সমন্বয় তৈরি করা, বা প্রতিটা মাস × প্রতিটা পণ্যের গ্রিড বানানো।

১০.৬ SELF JOIN

একই টেবিলকে দুবার ব্যবহার।

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER REFERENCES employees(id)
);

-- কর্মচারী ও তার ম্যানেজার
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Alias (e, m) ছাড়া এটা লেখা অসম্ভব — PostgreSQL বুঝবে না কোন কপির কথা বলছেন।

১০.৭ LATERAL JOIN — শক্তিশালী ও কম পরিচিত

সাধারণ subquery বাইরের row-এর কলাম দেখতে পায় না। LATERAL সেটা সম্ভব করে।

-- প্রতিটা গ্রাহকের সর্বশেষ ২টা অর্ডার
SELECT c.name, o.id, o.order_date, o.status
FROM customers c
LEFT JOIN LATERAL (
    SELECT id, order_date, status
    FROM orders
    WHERE customer_id = c.id      -- ← বাইরের c কে দেখতে পাচ্ছে!
    ORDER BY order_date DESC
    LIMIT 2
) o ON TRUE;

"প্রতি গ্রুপে টপ N" সমস্যার সবচেয়ে পরিষ্কার সমাধান এটাই। LEFT JOIN LATERAL ... ON TRUE প্যাটার্নটা মনে রাখুন।

১০.৮ USING ও NATURAL JOIN

-- দুই টেবিলে কলামের নাম একই হলে
SELECT * FROM orders JOIN customers USING (customer_id);

-- NATURAL JOIN — একই নামের সব কলাম মেলায়
SELECT * FROM orders NATURAL JOIN customers;

NATURAL JOIN ব্যবহার করবেন না। ভবিষ্যতে কোনো টেবিলে একই নামের নতুন কলাম যোগ হলে (যেমন created_at) query নীরবে ভুল ফল দিতে শুরু করবে। সবসময় স্পষ্টভাবে ON লিখুন।

১০.৯ JOIN-এ Performance

-- ১. JOIN কলামে index থাকতেই হবে
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- ২. আগে ছাঁকুন, পরে join করুন
-- ধীর:
SELECT ... FROM big_table b JOIN small s ON ... WHERE b.date > '2026-01-01';
-- প্রায়শই দ্রুত (planner সাধারণত নিজেই করে, তবে জটিল ক্ষেত্রে সাহায্য লাগে):
SELECT ... FROM (SELECT * FROM big_table WHERE date > '2026-01-01') b JOIN small s ON ...;

-- ৩. প্রয়োজনীয় কলামই নিন
SELECT o.id, c.name FROM ...   -- SELECT * নয়

PostgreSQL তিন ধরনের JOIN কৌশল ব্যবহার করে: - Nested Loop — ছোট টেবিলে, index থাকলে - Hash Join — বড় টেবিলে, equality শর্তে - Merge Join — দুটোই সাজানো থাকলে

কোনটা বেছেছে দেখতে EXPLAIN (অধ্যায় ২৩)।

১০.১০ অনুশীলনী

1. প্রতিটা অর্ডারের গ্রাহকের নাম, তারিখ, এবং মোট টাকা
2. সবচেয়ে বেশি বিক্রি হওয়া ৫টা পণ্য (পরিমাণ অনুযায়ী)
3. যেসব ক্যাটাগরিতে একটাও পণ্য নেই
4. প্রতিটা শহরের মোট বিক্রি
5. LATERAL দিয়ে প্রতিটা ক্যাটাগরির সবচেয়ে দামি ২টা পণ্য

১১.১ Subquery-র ধরন

Scalar Subquery (একটা মান দেয়)

SELECT name, price,
       (SELECT ROUND(AVG(price),2) FROM products) AS avg_price,
       price - (SELECT AVG(price) FROM products) AS diff
FROM products;

WHERE-এ Subquery

-- গড়ের চেয়ে দামি পণ্য
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'delivered');

-- ANY / ALL
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 2);

EXISTS — সাধারণত সবচেয়ে ভালো

-- অর্ডার আছে এমন গ্রাহক
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- অর্ডার নেই এমন গ্রাহক
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

IN বনাম EXISTS বনাম JOIN:

⚠️ NOT IN-এর NULL ফাঁদ

-- ❌ ভয়ানক
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- যদি orders.customer_id এ একটাও NULL থাকে, ফলাফল হবে সম্পূর্ণ খালি!

কারণ id NOT IN (1, 2, NULL) মানে id <> 1 AND id <> 2 AND id <> NULL → শেষটা সবসময় NULL → পুরো শর্ত কখনো TRUE হয় না।

সবসময় NOT EXISTS ব্যবহার করুন — এটা NULL-নিরাপদ।

FROM-এ Subquery (Derived Table)

SELECT category, avg_price
FROM (
    SELECT category_id, AVG(price) AS avg_price
    FROM products GROUP BY category_id
) sub
JOIN categories c ON c.id = sub.category_id;

FROM-এ subquery দিলে alias বাধ্যতামূলক।

Correlated Subquery

বাইরের প্রতিটা row-এর জন্য আলাদাভাবে চলে — তাই ধীর।

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

ছোট ডাটায় ঠিক আছে, বড় ডাটায় LEFT JOIN + GROUP BY বা LATERAL ব্যবহার করুন।

১১.২ CTE — Common Table Expression

WITH দিয়ে অস্থায়ী নামওয়ালা ফলাফল তৈরি করা। জটিল query-কে ধাপে ধাপে ভাগ করে পড়ার উপযোগী করে তোলে।

WITH order_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT o.id, c.name, ot.total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_totals ot ON ot.order_id = o.id
ORDER BY ot.total DESC;

একাধিক CTE:

WITH order_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items GROUP BY order_id
),
customer_spending AS (
    SELECT o.customer_id, SUM(ot.total) AS lifetime_value
    FROM orders o JOIN order_totals ot ON ot.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY o.customer_id
),
ranked AS (
    SELECT cs.*, c.name, c.city,
           RANK() OVER (ORDER BY lifetime_value DESC) AS rank
    FROM customer_spending cs
    JOIN customers c ON c.id = cs.customer_id
)
SELECT * FROM ranked WHERE rank <= 3;

লক্ষ করুন — প্রতিটা CTE আগেরটাকে ব্যবহার করতে পারে। জটিল রিপোর্ট এভাবে লিখলে ৬ মাস পরেও নিজেই পড়ে বুঝতে পারবেন।

CTE ও Performance

PostgreSQL 12-এর আগে CTE সবসময় materialize হতো (আলাদাভাবে চলে ফলাফল জমা রাখত), তাই কখনো কখনো ধীর হতো। PostgreSQL 12 থেকে planner নিজেই সিদ্ধান্ত নেয়। আপনি জোর করতে পারেন:

WITH big_calc AS MATERIALIZED (...)      -- জোর করে আলাদাভাবে চালাও
WITH small_thing AS NOT MATERIALIZED (...) -- মূল query তে মিশিয়ে দাও

একই CTE একাধিকবার ব্যবহার হলে MATERIALIZED ভালো (একবারই হিসাব হবে)।

১১.৩ Recursive CTE

গাছের মতো (hierarchical) ডাটার জন্য — সাংগঠনিক কাঠামো, ক্যাটাগরির উপ-ক্যাটাগরি, কমেন্টের রিপ্লাই।

CREATE TABLE org (
    id        SERIAL PRIMARY KEY,
    name      TEXT,
    manager_id INTEGER REFERENCES org(id)
);

INSERT INTO org (name, manager_id) VALUES
('সিইও', NULL),
('সিটিও', 1), ('সিএফও', 1),
('ইঞ্জিনিয়ারিং ম্যানেজার', 2),
('সিনিয়র ডেভেলপার', 4),
('জুনিয়র ডেভেলপার', 5);

WITH RECURSIVE hierarchy AS (
    -- ভিত্তি (anchor): সবার উপরের জন
    SELECT id, name, manager_id, 1 AS level, name::TEXT AS path
    FROM org
    WHERE manager_id IS NULL

    UNION ALL

    -- পুনরাবৃত্তি: প্রতি স্তরে অধীনস্থদের যোগ
    SELECT o.id, o.name, o.manager_id, h.level + 1, h.path || ' → ' || o.name
    FROM org o
    JOIN hierarchy h ON o.manager_id = h.id
)
SELECT REPEAT('    ', level - 1) || name AS org_chart, level, path
FROM hierarchy
ORDER BY path;

গঠনটা সবসময় একই: 1. Anchor — শুরুর row 2. UNION ALL 3. Recursive part — CTE-র নিজের নাম ব্যবহার করে পরের স্তর

আরেকটা কাজের প্রয়োগ — সংখ্যা বা তারিখের ধারা:

WITH RECURSIVE dates AS (
    SELECT '2026-01-01'::DATE AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2026-01-31'
)
SELECT * FROM dates;
-- যদিও generate_series() এর জন্য সহজ

১১.৪ Data-Modifying CTE

PostgreSQL-এর অনন্য ক্ষমতা — CTE-র ভেতরেই INSERT/UPDATE/DELETE:

-- এক transaction এ সরানো (move)
WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled' AND order_date < '2026-01-01'
    RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

-- একসাথে দুই টেবিলে insert
WITH new_order AS (
    INSERT INTO orders (customer_id, status)
    VALUES (1, 'pending')
    RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT id, 2, 3, 1200.00 FROM new_order;

গুরুত্বপূর্ণ: একই statement-এর সব CTE একই snapshot দেখে। মানে deleted CTE-তে মোছা row অন্য CTE-তে "আছে" হিসেবেই দেখাবে। এটা মাথায় রাখতে হবে।

১১.৫ অনুশীলনী

1. CTE দিয়ে প্রতি গ্রাহকের মোট খরচ ও অর্ডার সংখ্যা বের করে গড় অর্ডার মূল্য দেখান
2. NOT EXISTS দিয়ে এমন পণ্য খুঁজুন যা কখনো বিক্রি হয়নি
3. Recursive CTE দিয়ে ক্যাটাগরির গাছ বানান (nested category)
4. এমন গ্রাহক যাদের খরচ সব গ্রাহকের গড় খরচের চেয়ে বেশি

দুটো query-র ফলাফল সেট হিসেবে মেলানো।

-- UNION — জোড়া লাগায়, ডুপ্লিকেট বাদ দেয়
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL — ডুপ্লিকেট রাখে, অনেক দ্রুত
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- INTERSECT — দুটোতেই আছে এমন
SELECT product_id FROM order_items WHERE order_id = 1
INTERSECT
SELECT product_id FROM order_items WHERE order_id = 8;

-- EXCEPT — প্রথমটায় আছে কিন্তু দ্বিতীয়টায় নেই
SELECT id FROM products
EXCEPT
SELECT DISTINCT product_id FROM order_items;

শর্ত: - দুই query-তে কলামের সংখ্যা সমান হতে হবে - একই ক্রমে সামঞ্জস্যপূর্ণ data type হতে হবে - কলামের নাম প্রথম query থেকে নেওয়া হয়

UNION বনাম UNION ALL: ডুপ্লিকেট বাদ দিতে UNION-কে পুরো ফলাফল sort বা hash করতে হয় — ব্যয়বহুল। ডুপ্লিকেট থাকার সম্ভাবনা না থাকলে বা সমস্যা না হলে সবসময় UNION ALL ব্যবহার করুন।

-- ORDER BY পুরো ফলাফলের উপর, শেষে একবার
(SELECT name, price FROM products WHERE category_id = 1 ORDER BY price DESC LIMIT 3)
UNION ALL
(SELECT name, price FROM products WHERE category_id = 2 ORDER BY price DESC LIMIT 3)
ORDER BY price DESC;

১৩.১ View

View একটা সংরক্ষিত query — টেবিলের মতো ব্যবহার করা যায়, কিন্তু ডাটা জমা রাখে না।

CREATE VIEW order_summary AS
SELECT
    o.id AS order_id,
    o.order_date,
    o.status,
    c.name AS customer,
    c.city,
    SUM(oi.quantity * oi.unit_price) AS total_amount,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date, o.status, c.name, c.city;

-- এখন সাধারণ টেবিলের মতো
SELECT * FROM order_summary WHERE city = 'Dhaka' AND total_amount > 5000;

View-এর উপকারিতা: - জটিল query একবার লিখে বারবার ব্যবহার - নিরাপত্তা — মূল টেবিলে access না দিয়ে view-তে দেওয়া (সংবেদনশীল কলাম লুকানো) - Abstraction — টেবিলের গঠন বদলালেও view একই রেখে অ্যাপকে বাঁচানো

CREATE OR REPLACE VIEW ... ;   -- বদলানো
DROP VIEW order_summary;

CREATE OR REPLACE দিয়ে কলাম যোগ করা যায়, কিন্তু বিদ্যমান কলাম মুছতে বা type বদলাতে পারবেন না — তখন DROP করে আবার বানাতে হবে।

Updatable View

সহজ view-তে (একটা টেবিল, aggregate/DISTINCT/GROUP BY নেই) সরাসরি INSERT/UPDATE করা যায়:

CREATE VIEW dhaka_customers AS
SELECT id, name, email, city FROM customers WHERE city = 'Dhaka'
WITH CHECK OPTION;

-- এটা কাজ করবে
UPDATE dhaka_customers SET name = 'নতুন নাম' WHERE id = 1;

-- এটা আটকে যাবে (CHECK OPTION এর কারণে)
INSERT INTO dhaka_customers (name, email, city)
VALUES ('কেউ', 'x@y.com', 'Sylhet');
-- ERROR: new row violates check option

জটিল view-তে লিখতে চাইলে INSTEAD OF trigger লাগবে (অধ্যায় ২১)।

১৩.২ Materialized View

সাধারণ view প্রতিবার query চালায়। Materialized view ফলাফল ডিস্কে জমা রাখে — পড়তে অনেক দ্রুত, কিন্তু ডাটা বাসি হয়।

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    c.city,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1, 2
WITH DATA;

-- রিফ্রেশ (পুরো টেবিল lock হয়ে যায়)
REFRESH MATERIALIZED VIEW monthly_sales;

-- CONCURRENTLY — রিফ্রেশের সময়ও পড়া যাবে
CREATE UNIQUE INDEX ON monthly_sales (month, city);  -- আগে unique index লাগবে
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Materialized view-তে index বানানো যায় — এটাই এর বড় শক্তি:

CREATE INDEX idx_ms_month ON monthly_sales(month);

কখন ব্যবহার করবেন: ভারী dashboard, রিপোর্ট, analytics — যেখানে ৫-১০ মিনিট পুরনো ডাটা মেনে নেওয়া যায়। রিফ্রেশ করতে হবে cron job বা pg_cron extension দিয়ে।

১৪.১ Index কী

একটা বইয়ের শেষে যে নির্ঘণ্ট (index) থাকে — "MVCC ... পৃষ্ঠা ২১৪" — সেটা না থাকলে আপনাকে পুরো বই পাতা উল্টে খুঁজতে হতো। Database index ঠিক একই জিনিস।

Index ছাড়া PostgreSQL Sequential Scan করে — টেবিলের প্রতিটা row পড়ে দেখে। ১ কোটি row-এ এটা কয়েক সেকেন্ড। Index থাকলে Index Scan — মিলিসেকেন্ড।

CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_date ON orders(order_date);
DROP INDEX idx_orders_date;

১৪.২ Index-এর দাম

Index ফ্রি নয়:

নিয়ম: যেখানে সত্যিই দরকার সেখানেই index। ১০টা কলামে ১০টা index বানিয়ে রাখলে write-heavy অ্যাপ ধীর হয়ে যাবে।

কোথায় index দেবেন: - WHERE-এ ঘন ঘন ব্যবহৃত কলাম - JOIN কলাম (বিশেষত foreign key) - ORDER BY কলাম - UNIQUE constraint (স্বয়ংক্রিয়ভাবে হয়)

কোথায় দেবেন না: - ছোট টেবিল (কয়েকশো row) — seq scan-ই দ্রুত - খুব কম ভিন্নতা (low cardinality) — যেমন gender কলামে মাত্র ২টা মান - খুব বেশি write হয় এমন কলাম

১৪.৩ Index-এর ধরন

B-tree (Default)

৯০% ক্ষেত্রে এটাই।

CREATE INDEX idx_price ON products(price);

সাপোর্ট করে: =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'abc%' (শুরুর দিকে wildcard না থাকলে), ORDER BY।

Hash

শুধু =-এর জন্য। B-tree-এর তুলনায় সুবিধা খুব সামান্য — বিশেষ কারণ ছাড়া ব্যবহার করবেন না।

CREATE INDEX idx_hash ON users USING HASH (session_token);

GIN — Generalized Inverted Index

একটা কলামে একাধিক মান থাকলে (array, jsonb, full-text)।

CREATE INDEX idx_tags ON posts USING GIN (tags);
CREATE INDEX idx_meta ON products USING GIN (metadata);
CREATE INDEX idx_meta_path ON products USING GIN (metadata jsonb_path_ops);  -- ছোট ও দ্রুত, কিন্তু কম operator
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('english', body));

GiST — Generalized Search Tree

জ্যামিতিক ডাটা, range type, nearest-neighbour খোঁজা।

CREATE INDEX idx_period ON bookings USING GIST (booking_period);
CREATE INDEX idx_loc ON places USING GIST (location);   -- PostGIS

BRIN — Block Range Index

বিশাল টেবিলে, যেখানে ডাটা স্বাভাবিকভাবে সাজানো (যেমন time-series log টেবিল যেখানে সময় অনুযায়ী row ঢোকে)।

CREATE INDEX idx_logs_time ON logs USING BRIN (created_at);

B-tree-এর তুলনায় জায়গা নেয় হাজার গুণ কম, কিন্তু কেবল সাজানো ডাটায় কার্যকর।

SP-GiST

Space-partitioned — ফোন নম্বর, IP প্রেফিক্স, অসম বণ্টনের ডাটা।

১৪.৪ বিশেষ Index কৌশল

Composite Index (একাধিক কলাম)

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

কলামের ক্রম অত্যন্ত গুরুত্বপূর্ণ। এই index ব্যবহার হবে: - ✅ WHERE customer_id = 1 - ✅ WHERE customer_id = 1 AND order_date > '2026-01-01' - ❌ WHERE order_date > '2026-01-01' (একা) — সাধারণত না

একে বলে leftmost prefix rule। ফোনবুকে নাম দিয়ে সাজানো থাকলে নাম দিয়ে খোঁজা যায়, কিন্তু শুধু পদবি দিয়ে খোঁজা যায় না।

ক্রম বাছাইয়ের নিয়ম: সবচেয়ে বেশি selective (যেটা সবচেয়ে বেশি row ছেঁকে ফেলে) কলাম আগে; equality শর্তের কলাম range শর্তের আগে।

Partial Index

শুধু কিছু row-এর জন্য index। জায়গা কম, দ্রুত।

-- শুধু সক্রিয় গ্রাহক
CREATE INDEX idx_active ON customers(email) WHERE deleted_at IS NULL;

-- শুধু অপেক্ষমাণ অর্ডার
CREATE INDEX idx_pending ON orders(order_date) WHERE status = 'pending';

Query-তে একই শর্ত থাকলেই তবে index ব্যবহার হবে।

খুব কাজের একটা প্রয়োগ — শর্তসাপেক্ষ uniqueness:

-- একজন ইউজারের শুধু একটাই primary ঠিকানা থাকতে পারবে
CREATE UNIQUE INDEX idx_one_primary ON addresses(user_id) WHERE is_primary;

Expression Index

হিসাবের উপর index।

CREATE INDEX idx_lower_email ON customers(LOWER(email));
-- এখন এটা index ব্যবহার করবে:
SELECT * FROM customers WHERE LOWER(email) = 'rahim@example.com';

CREATE INDEX idx_order_year ON orders(EXTRACT(YEAR FROM order_date));
CREATE INDEX idx_json_brand ON products((metadata->>'brand'));

গুরুত্বপূর্ণ: কলামের উপর function লাগালে সাধারণ index কাজ করে না। WHERE LOWER(email) = ... লিখলে email-এর index অকেজো — expression index লাগবে।

Covering Index (INCLUDE)

PostgreSQL 11+ — index-এ অতিরিক্ত কলাম রাখা, যাতে টেবিলে যেতেই না হয় (index-only scan)।

CREATE INDEX idx_cust_email ON customers(email) INCLUDE (name, city);

-- এই query শুধু index থেকেই উত্তর পাবে
SELECT email, name, city FROM customers WHERE email = 'rahim@example.com';

CONCURRENTLY — Production-এর জন্য অপরিহার্য

সাধারণ CREATE INDEX টেবিলে write lock নেয় — বড় টেবিলে কয়েক মিনিট সবাই আটকে থাকবে।

CREATE INDEX CONCURRENTLY idx_big ON huge_table(col);
DROP INDEX CONCURRENTLY idx_old;

ধীরে হবে (দুবার স্ক্যান করে), কিন্তু write আটকাবে না।

সীমাবদ্ধতা: transaction block-এর ভেতরে চালানো যায় না। ব্যর্থ হলে একটা INVALID index পড়ে থাকে — সেটা DROP করে আবার চেষ্টা করতে হয়।

১৪.৫ Index ব্যবহার হচ্ছে কিনা দেখা

EXPLAIN ANALYZE SELECT * FROM customers WHERE email = 'rahim@example.com';

আউটপুটে Index Scan using idx_customers_email দেখলে ভালো, Seq Scan দেখলে index হয় নেই, নয় planner ব্যবহার করছে না।

কোন index অব্যবহৃত পড়ে আছে:

SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

idx_scan = 0 মানে একবারও ব্যবহার হয়নি — মুছে ফেলার কথা ভাবুন (তবে আগে নিশ্চিত হোন যে পরিসংখ্যান যথেষ্ট লম্বা সময়ের)।

Index vs টেবিলের আকার:

SELECT relname,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

১৪.৬ কেন Index ব্যবহার হচ্ছে না — সাধারণ কারণ

1. টেবিল খুব ছোট — planner ঠিকই করছে, seq scan দ্রুত
2. কলামে function লাগানো — WHERE LOWER(x) = ... → expression index লাগবে
3. Data type মিলছে না — WHERE int_col = '123' (কখনো কখনো cast হয়ে যায়)
4. LIKE '%abc%' — শুরুতে wildcard → pg_trgm লাগবে
5. অনেক বেশি row মিলছে — টেবিলের ৩০%+ ফেরত দিলে seq scan-ই দ্রুত
6. পরিসংখ্যান পুরনো — ANALYZE tablename; চালান
7. OR শর্ত — কখনো কখনো UNION দিয়ে লিখলে ভালো হয়

১৪.৭ Text Search-এ index (pg_trgm)

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);

-- এখন এটাও index ব্যবহার করবে
SELECT * FROM products WHERE name ILIKE '%mouse%';

-- সাদৃশ্য অনুযায়ী খোঁজা (typo সহ্য করে)
SELECT name, similarity(name, 'wireles mous') AS sim
FROM products
WHERE name % 'wireles mous'
ORDER BY sim DESC;

pg_trgm "autocomplete" বা "did you mean" ধরনের ফিচার বানানোর সহজতম উপায়।

১৫.১ Transaction কী

একগুচ্ছ কাজ যা হয় পুরোটা হবে, নয় কিছুই হবে না।

ক্লাসিক উদাহরণ — টাকা পাঠানো:

BEGIN;
    UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

মাঝখানে সার্ভার ক্র্যাশ করলে? PostgreSQL পুরোটা বাতিল করে দেবে। টাকা "হাওয়া" হবে না।

BEGIN;       -- শুরু (START TRANSACTION ও চলে)
COMMIT;      -- স্থায়ীভাবে সংরক্ষণ
ROLLBACK;    -- সব বাতিল

১৫.২ ACID

Durability নিশ্চিত হয় WAL (Write-Ahead Log) দিয়ে — ডাটা ফাইলে লেখার আগেই পরিবর্তনটা log-এ লেখা হয়। ক্র্যাশের পরে PostgreSQL WAL পড়ে সব পুনর্গঠন করে।

১৫.৩ SAVEPOINT

Transaction-এর ভেতরে আংশিক rollback:

BEGIN;
    INSERT INTO customers (name, email) VALUES ('ক', 'k@x.com');
    SAVEPOINT sp1;
    INSERT INTO customers (name, email) VALUES ('খ', 'invalid');
    -- ভুল হলো
    ROLLBACK TO SAVEPOINT sp1;
    INSERT INTO customers (name, email) VALUES ('খ', 'kh@x.com');
COMMIT;
-- 'ক' এবং 'খ' দুজনই থাকবে

১৫.৪ Isolation Level

একসাথে অনেক transaction চললে যেসব সমস্যা হতে পারে:

PostgreSQL-এর চারটা level:

বিশেষ কথা: PostgreSQL-এ READ UNCOMMITTED আসলে READ COMMITTED-এর মতোই আচরণ করে — dirty read এখানে কখনোই হয় না। এটা PostgreSQL-এর একটা শক্তি।

BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- ...
COMMIT;

-- অথবা
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- পুরো session এর জন্য
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

কখন কোনটা: - READ COMMITTED — ৯৫% ক্ষেত্রে যথেষ্ট - REPEATABLE READ — লম্বা রিপোর্ট যেখানে ধারাবাহিক snapshot দরকার - SERIALIZABLE — আর্থিক লেনদেন, ইনভেন্টরি — যেখানে নির্ভুলতা সর্বাধিক

SERIALIZABLE ব্যবহার করলে অ্যাপ্লিকেশনে retry logic লিখতেই হবে — PostgreSQL serialization_failure (SQLSTATE 40001) error দিয়ে transaction বাতিল করতে পারে।

# Python এ retry এর নমুনা
for attempt in range(3):
    try:
        with conn.transaction():
            # ... কাজ
        break
    except psycopg.errors.SerializationFailure:
        if attempt == 2: raise
        time.sleep(0.1 * (2 ** attempt))

১৫.৫ MVCC — PostgreSQL-এর হৃদয়

Multi-Version Concurrency Control — PostgreSQL-এর সবচেয়ে গুরুত্বপূর্ণ অভ্যন্তরীণ নকশা।

মূল ধারণা: পড়া কখনো লেখাকে আটকায় না, লেখা কখনো পড়াকে আটকায় না।

কীভাবে? row আপডেট করলে PostgreSQL পুরনো row বদলায় না — নতুন একটা version তৈরি করে। প্রতিটা transaction-এর একটা snapshot থাকে, সে শুধু তার জন্য দৃশ্যমান version গুলোই দেখে।

প্রতিটা row-এ লুকানো কলাম থাকে: - xmin — কোন transaction এটা তৈরি করেছে - xmax — কোন transaction এটা মুছেছে/বদলেছে

SELECT xmin, xmax, * FROM products LIMIT 3;

পরিণাম: মুছে ফেলা বা পুরনো version গুলো ডিস্কে পড়ে থাকে — একে বলে dead tuple। এগুলো পরিষ্কার করার কাজটাই VACUUM (অধ্যায় ২৪)।

এজন্যই PostgreSQL-এ DELETE করলে সাথে সাথে ডিস্ক জায়গা খালি হয় না।

১৫.৬ Locking

Row-level lock

BEGIN;
-- row টা lock করে রাখুন, যতক্ষণ না commit
SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

অপেক্ষা না করে:

SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE NOWAIT;
-- lock না পেলে সাথে সাথে error

SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;
-- lock করা row গুলো এড়িয়ে যাবে

SKIP LOCKED দিয়ে queue বানানো যায় — একাধিক worker একই টেবিল থেকে কাজ নিলেও কেউ কারও কাজ নেবে না। খুব কাজের প্যাটার্ন:

-- Job queue worker
BEGIN;
SELECT id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- কাজ করুন
UPDATE job_queue SET status = 'done' WHERE id = ...;
COMMIT;

Deadlock

দুটো transaction পরস্পরের lock-এর জন্য অপেক্ষা করলে:

Transaction A: row 1 lock করে, row 2 চায়
Transaction B: row 2 lock করে, row 1 চায়

PostgreSQL এটা নিজে ধরতে পারে এবং একটাকে বাতিল করে দেয়:

ERROR: deadlock detected

প্রতিরোধ: সব জায়গায় একই ক্রমে row lock করুন (যেমন সবসময় id-র ছোট থেকে বড়)। Transaction ছোট রাখুন।

চলমান lock দেখা:

SELECT pid, state, wait_event_type, wait_event,
       age(now(), query_start) AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

-- আটকে থাকা query বাতিল করা
SELECT pg_cancel_backend(pid);      -- ভদ্রভাবে
SELECT pg_terminate_backend(pid);   -- জোর করে

১৫.৭ ব্যবহারিক পরামর্শ

1. Transaction যত ছোট রাখা যায় — লম্বা transaction lock ধরে রাখে ও VACUUM-কে আটকায়
2. Transaction-এর ভেতরে API কল করবেন না — নেটওয়ার্ক ধীর হলে database আটকে থাকবে
3. ব্যবহারকারীর ইনপুটের জন্য transaction খুলে অপেক্ষা করবেন না
4. Idle in transaction সবচেয়ে বিপজ্জনক অবস্থা — idle_in_transaction_session_timeout সেট করে রাখুন

SQL-এর সবচেয়ে শক্তিশালী, অথচ সবচেয়ে কম ব্যবহৃত ফিচার। এটা শিখলে আপনার SQL এক ধাপ উপরে উঠে যাবে।

১৬.১ মূল ধারণা

GROUP BY অনেক row-কে চেপে একটা row বানায়। Window function প্রতিটা row রেখে দেয়, কিন্তু সংশ্লিষ্ট row-গুলোর উপর হিসাব করে।

-- GROUP BY: ৪ টা ক্যাটাগরি = ৪ টা row
SELECT category_id, AVG(price) FROM products GROUP BY category_id;

-- Window: ১০ টা পণ্য = ১০ টা row, প্রতিটায় তার ক্যাটাগরির গড়
SELECT name, price, category_id,
       AVG(price) OVER (PARTITION BY category_id) AS category_avg
FROM products;

গঠন:

function() OVER (
    PARTITION BY কলাম    -- দল ভাগ (ঐচ্ছিক)
    ORDER BY কলাম        -- ক্রম (ঐচ্ছিক)
    ROWS/RANGE ...       -- সীমা (ঐচ্ছিক)
)

১৬.২ Ranking Function

SELECT name, category_id, price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num,
    RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dense_rank,
    PERCENT_RANK() OVER (ORDER BY price) AS pct_rank,
    NTILE(4)     OVER (ORDER BY price) AS quartile
FROM products;

তিনটার পার্থক্য (দাম: 100, 100, 90, 80 হলে):

• ROW_NUMBER — সবসময় অনন্য ক্রমিক
• RANK — টাই হলে একই, পরেরটা লাফ দেয়
• DENSE_RANK — টাই হলে একই, লাফ দেয় না

১৬.৩ "প্রতি গ্রুপে টপ N" — ক্লাসিক সমস্যা

WITH ranked AS (
    SELECT name, category_id, price,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 2;

গুরুত্বপূর্ণ: WHERE-এ সরাসরি window function ব্যবহার করা যায় না — কারণ WHERE চলে SELECT-এর আগে। তাই CTE বা subquery লাগবেই।

-- ❌ ভুল
SELECT * FROM products WHERE ROW_NUMBER() OVER (...) <= 2;
-- ERROR: window functions are not allowed in WHERE

১৬.৪ Offset Function — LAG ও LEAD

আগের বা পরের row-এর মান দেখা। বৃদ্ধির হার হিসাব করার প্রধান হাতিয়ার।

WITH monthly AS (
    SELECT DATE_TRUNC('month', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2
    ) AS growth_pct
FROM monthly
ORDER BY month;

LAG(col, n, default) — n ধাপ আগের মান, না থাকলে default।

LAG(revenue, 1, 0)      -- ১ ধাপ আগে, না থাকলে 0
LEAD(revenue, 3)        -- ৩ ধাপ পরে
FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price DESC)
LAST_VALUE(price)  OVER (PARTITION BY category_id ORDER BY price DESC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTH_VALUE(price, 2) OVER (...)

১৬.৫ Running Total ও Moving Average

SELECT
    order_date,
    daily_revenue,
    -- চলমান যোগফল
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
    -- ৩ দিনের চলমান গড়
    ROUND(AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3d,
    -- মোট বিক্রির শতাংশ
    ROUND(100.0 * daily_revenue / SUM(daily_revenue) OVER (), 2) AS pct_of_total
FROM daily_sales
ORDER BY order_date;

১৬.৬ Frame — ROWS vs RANGE

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    -- শুরু থেকে এখন পর্যন্ত
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING            -- আগে-পরে ২টা করে
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING    -- এখন থেকে শেষ পর্যন্ত
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- default (ORDER BY থাকলে)

• ROWS — শারীরিকভাবে কতটা row
• RANGE — মানের ভিত্তিতে (একই মানের সব row একসাথে ধরা হয়)

Default গুলো মনে রাখুন: - ORDER BY নেই → পুরো partition - ORDER BY আছে → RANGE UNBOUNDED PRECEDING AND CURRENT ROW

১৬.৭ WINDOW clause — পুনরাবৃত্তি এড়ানো

SELECT
    name, category_id, price,
    RANK()       OVER w AS rank,
    AVG(price)   OVER w AS cat_avg,
    price - AVG(price) OVER w AS diff_from_avg
FROM products
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

একই window definition বারবার লেখার বদলে একবার নাম দিয়ে দিন।

১৬.৮ বাস্তব উদাহরণ

গ্রাহকের ক্রয় ইতিহাস বিশ্লেষণ:

WITH order_totals AS (
    SELECT o.id, o.customer_id, o.order_date,
           SUM(oi.quantity * oi.unit_price) AS amount
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY o.id, o.customer_id, o.order_date
)
SELECT
    c.name,
    ot.order_date,
    ot.amount,
    ROW_NUMBER() OVER pc AS order_seq,
    SUM(ot.amount) OVER pc AS cumulative_spend,
    ot.order_date - LAG(ot.order_date) OVER pc AS days_since_last,
    ROUND(AVG(ot.amount) OVER pc, 2) AS avg_so_far
FROM order_totals ot
JOIN customers c ON c.id = ot.customer_id
WINDOW pc AS (PARTITION BY ot.customer_id ORDER BY ot.order_date)
ORDER BY c.name, ot.order_date;

এই একটা query থেকে পাওয়া গেল — কে কতবার কিনেছে, মোট কত খরচ, কত দিন পরপর কেনে, গড় অর্ডার মূল্য। সাধারণ SQL-এ এটা লিখতে গেলে কয়েকটা আলাদা query আর অ্যাপ্লিকেশন কোড লাগত।

১৬.৯ অনুশীলনী

1. প্রতিটা পণ্যের দাম ও তার ক্যাটাগরির সর্বোচ্চ দামের পার্থক্য
2. প্রতিটা গ্রাহকের প্রথম ও শেষ অর্ডারের তারিখ (window দিয়ে)
3. মাসিক বিক্রির running total ও আগের মাসের সাথে শতকরা পরিবর্তন
4. প্রতি ক্যাটাগরিতে দাম অনুযায়ী উপরের ২৫% পণ্য (NTILE)

১৭.১ কখন JSON ব্যবহার করবেন

উপযুক্ত: - প্রতিটা পণ্যের ভিন্ন ভিন্ন বৈশিষ্ট্য (ল্যাপটপের RAM, জামার সাইজ) - API-র কাঁচা response সংরক্ষণ - ইউজার সেটিংস/পছন্দ - Audit log-এ পুরনো-নতুন মান

অনুপযুক্ত: - যেসব ফিল্ড সবসময় থাকে ও query হয় — সেগুলো নিয়মিত কলাম হোক - সম্পর্ক (relationship) — foreign key ব্যবহার করুন - "schema লিখতে আলসেমি লাগছে" — এটা কোনো কারণ নয়

১৭.২ Operator

CREATE TABLE items (
    id   SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO items (data) VALUES
('{"name":"Laptop","specs":{"ram":16,"cpu":"i7"},"tags":["new","sale"],"price":145000}'),
('{"name":"Mouse","specs":{"dpi":1600},"tags":["sale"],"price":1200}');

SELECT data -> 'name'          FROM items;  -- "Laptop"  (jsonb)
SELECT data ->> 'name'         FROM items;  -- Laptop    (text)
SELECT data -> 'specs' ->> 'ram' FROM items;
SELECT data #> '{specs,cpu}'   FROM items;  -- path, jsonb
SELECT data #>> '{tags,0}'     FROM items;  -- path, text

মনে রাখার সহজ উপায়: একটা > মানে JSON ফেরত, দুটো >> মানে টেক্সট ফেরত।

->> না দিলে তুলনা করতে গিয়ে ঝামেলা হয়:

-- ❌ কাজ করবে না ঠিকমতো
WHERE data -> 'name' = 'Laptop'
-- ✅
WHERE data ->> 'name' = 'Laptop'
-- ✅ অথবা
WHERE data -> 'name' = '"Laptop"'::jsonb

১৭.৩ Containment ও Existence

-- @> : বামেরটা ডানেরটাকে ধারণ করে কিনা
SELECT * FROM items WHERE data @> '{"name":"Laptop"}';
SELECT * FROM items WHERE data @> '{"specs":{"ram":16}}';
SELECT * FROM items WHERE data -> 'tags' @> '["sale"]';

-- ? : key আছে কিনা
SELECT * FROM items WHERE data ? 'price';
SELECT * FROM items WHERE data -> 'specs' ? 'cpu';

-- ?| : যেকোনো একটা key
SELECT * FROM items WHERE data ?| ARRAY['price', 'discount'];

-- ?& : সব key
SELECT * FROM items WHERE data ?& ARRAY['name', 'price'];

@> operator-টাই GIN index ব্যবহার করে — তাই এটাই সবচেয়ে দ্রুত।

CREATE INDEX idx_items_data ON items USING GIN (data);

১৭.৪ JSONPath (PostgreSQL 12+)

SELECT jsonb_path_query(data, '$.specs.ram') FROM items;
SELECT jsonb_path_query_array(data, '$.tags[*]') FROM items;
SELECT * FROM items WHERE data @@ '$.price > 10000';
SELECT * FROM items WHERE jsonb_path_exists(data, '$.specs.cpu');

-- ফিল্টার সহ
SELECT jsonb_path_query(data, '$.tags[*] ? (@ == "sale")') FROM items;

১৭.৫ JSON তৈরি ও পরিবর্তন

-- তৈরি
SELECT jsonb_build_object('id', id, 'name', name, 'price', price) FROM products;
SELECT jsonb_build_array(1, 'two', TRUE, NULL);
SELECT to_jsonb(p) FROM products p;              -- পুরো row কে JSON
SELECT jsonb_agg(to_jsonb(p)) FROM products p;   -- সব row একটা array তে

-- পরিবর্তন
SELECT data || '{"discount": 10}'::jsonb FROM items;        -- যোগ/আপডেট
SELECT data - 'price' FROM items;                            -- key মোছা
SELECT data - ARRAY['price','tags'] FROM items;              -- একাধিক key
SELECT jsonb_set(data, '{specs,ram}', '32') FROM items;      -- nested আপডেট
SELECT jsonb_insert(data, '{tags,0}', '"hot"') FROM items;   -- array তে ঢোকানো

-- আসল টেবিলে আপডেট
UPDATE items SET data = jsonb_set(data, '{price}', '99999') WHERE id = 1;
UPDATE items SET data = data || '{"in_stock": true}'::jsonb;

১৭.৬ JSON কে টেবিলে রূপান্তর

-- key-value জোড়া
SELECT id, key, value FROM items, jsonb_each(data);
SELECT id, key, value FROM items, jsonb_each_text(data);

-- array কে row তে
SELECT id, tag FROM items, jsonb_array_elements_text(data -> 'tags') AS tag;

-- সব key
SELECT id, jsonb_object_keys(data) FROM items;

-- JSON কে structured টেবিলে
SELECT * FROM jsonb_to_record('{"a":1,"b":"text"}'::jsonb)
    AS x(a INT, b TEXT);

SELECT * FROM jsonb_to_recordset(
    '[{"id":1,"name":"ক"},{"id":2,"name":"খ"}]'::jsonb
) AS x(id INT, name TEXT);

jsonb_to_recordset API থেকে আসা JSON array-কে সরাসরি টেবিলে ঢোকানোর সবচেয়ে সহজ উপায়।

১৭.৭ বাস্তব প্যাটার্ন — মিশ্র নকশা

CREATE TABLE products_v2 (
    -- যা সবসময় থাকে ও query হয় → নিয়মিত কলাম
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    category_id INTEGER REFERENCES categories(id),

    -- যা পণ্যভেদে আলাদা → JSONB
    attributes  JSONB DEFAULT '{}',

    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_pv2_attrs ON products_v2 USING GIN (attributes);

-- ঘন ঘন খোঁজা হয় এমন JSON ফিল্ডে আলাদা expression index
CREATE INDEX idx_pv2_brand ON products_v2 ((attributes ->> 'brand'));

এটাই আদর্শ — structured আর flexible-এর সেরা মিশ্রণ। PostgreSQL-এ MongoDB-র নমনীয়তা আর SQL-এর নির্ভরযোগ্যতা একসাথে পাওয়ার এটাই উপায়।

১৮.১ Array আরও গভীরে

SELECT ARRAY[1,2,3];
SELECT '{1,2,3}'::INT[];
SELECT ARRAY(SELECT id FROM products WHERE price > 5000);

-- Operator
SELECT ARRAY[1,2] || ARRAY[3,4];        -- {1,2,3,4}
SELECT ARRAY[1,2,3] @> ARRAY[2];        -- ধারণ করে?
SELECT ARRAY[1,2] && ARRAY[2,3];        -- overlap আছে?
SELECT 2 = ANY(ARRAY[1,2,3]);           -- TRUE
SELECT 5 > ALL(ARRAY[1,2,3]);           -- TRUE

-- Function
SELECT array_length(ARRAY[1,2,3], 1);   -- 3
SELECT array_position(ARRAY['a','b'], 'b');
SELECT array_remove(ARRAY[1,2,2,3], 2);
SELECT array_to_string(ARRAY['a','b'], ', ');
SELECT unnest(ARRAY[1,2,3]);            -- row তে ভাঙা
SELECT array_agg(x ORDER BY x) FROM generate_series(1,5) x;

-- Index
CREATE INDEX idx_tags ON posts USING GIN (tags);

১৮.২ Range Type

-- ধরন: int4range, int8range, numrange, tsrange, tstzrange, daterange
SELECT int4range(1, 10);              -- [1,10) — শুরু অন্তর্ভুক্ত, শেষ নয়
SELECT '[1,10]'::int4range;           -- দুই প্রান্তই অন্তর্ভুক্ত
SELECT daterange('2026-01-01','2026-12-31');

-- Operator
SELECT int4range(1,10) @> 5;                          -- ধারণ করে
SELECT int4range(1,10) && int4range(5,15);            -- overlap
SELECT lower(int4range(1,10)), upper(int4range(1,10));
SELECT int4range(1,10) * int4range(5,15);             -- ছেদ

-- ব্যবহারিক প্রয়োগ
CREATE TABLE room_bookings (
    id      SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    period  TSTZRANGE NOT NULL,
    guest   TEXT,
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);

INSERT INTO room_bookings (room_id, period, guest)
VALUES (101, '[2026-08-01 14:00, 2026-08-05 12:00)', 'রহিম');

-- এটা আটকে যাবে — সময় overlap করছে
INSERT INTO room_bookings (room_id, period, guest)
VALUES (101, '[2026-08-03 14:00, 2026-08-07 12:00)', 'করিম');
-- ERROR: conflicting key value violates exclusion constraint

হোটেল বুকিং, রুম রিজার্ভেশন, শিফট সিডিউল — এসব সিস্টেমে এই একটা constraint আপনাকে শত লাইন অ্যাপ্লিকেশন কোড থেকে বাঁচাবে।

১৮.৩ Domain — নিজস্ব সীমাবদ্ধ Type

CREATE DOMAIN email_address AS TEXT
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN bd_phone AS VARCHAR(11)
    CHECK (VALUE ~ '^01[3-9][0-9]{8}$');

CREATE DOMAIN positive_money AS NUMERIC(12,2)
    CHECK (VALUE > 0);

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email email_address NOT NULL UNIQUE,
    phone bd_phone,
    balance positive_money DEFAULT 0
);

এখন প্রতিটা টেবিলে আলাদা করে CHECK লেখার দরকার নেই — নিয়মটা এক জায়গায় সংজ্ঞায়িত, সব জায়গায় প্রযোজ্য।

১৮.৪ Composite Type

CREATE TYPE address AS (
    street   TEXT,
    city     TEXT,
    postcode VARCHAR(10),
    country  CHAR(2)
);

CREATE TABLE contacts (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    home_addr address,
    work_addr address
);

INSERT INTO contacts (name, home_addr)
VALUES ('রহিম', ROW('১২/এ ধানমন্ডি', 'Dhaka', '1209', 'BD'));

SELECT name, (home_addr).city FROM contacts;
UPDATE contacts SET home_addr.city = 'Chattogram' WHERE id = 1;

বন্ধনী দরকার — (home_addr).city, নাহলে PostgreSQL ভাববে home_addr একটা টেবিলের নাম।

LIKE '%word%' দিয়ে খোঁজা ধীর এবং বোকা — এটা "running" খুঁজলে "run" পাবে না। Full-text search সেই সমস্যা সমাধান করে।

১৯.১ মূল ধারণা

-- টেক্সট কে tsvector এ রূপান্তর (শব্দমূলে ভাঙা)
SELECT to_tsvector('english', 'The quick brown foxes are jumping over lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2
-- লক্ষ করুন: foxes→fox, jumping→jump, stop word (the, are, over) বাদ

-- অনুসন্ধান
SELECT to_tsvector('english', 'The quick brown foxes')
       @@ to_tsquery('english', 'fox');       -- TRUE

@@ হলো ম্যাচিং operator।

১৯.২ Query-র ধরন

SELECT to_tsquery('english', 'fox & quick');      -- AND
SELECT to_tsquery('english', 'fox | dog');        -- OR
SELECT to_tsquery('english', 'fox & !dog');       -- NOT
SELECT to_tsquery('english', 'quick <-> brown');  -- পাশাপাশি শব্দ

SELECT plainto_tsquery('english', 'quick brown fox');    -- সব শব্দ AND
SELECT phraseto_tsquery('english', 'quick brown fox');   -- হুবহু বাক্যাংশ
SELECT websearch_to_tsquery('english', '"quick brown" -dog');  -- Google-এর মতো

ব্যবহারকারীর ইনপুট সরাসরি to_tsquery-তে দেবেন না — সিনট্যাক্স ভুল হলে error দেবে। websearch_to_tsquery বা plainto_tsquery ব্যবহার করুন — এগুলো যেকোনো ইনপুট নিরাপদে সামলায়।

১৯.৩ পূর্ণাঙ্গ বাস্তবায়ন

CREATE TABLE articles (
    id      SERIAL PRIMARY KEY,
    title   TEXT NOT NULL,
    body    TEXT NOT NULL,
    -- Generated column — নিজে থেকেই আপডেট হবে
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,'')),  'B')
    ) STORED
);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

setweight দিয়ে গুরুত্ব ঠিক করা যায় — A সবচেয়ে বেশি, D সবচেয়ে কম। শিরোনামে শব্দ পাওয়া গেলে সেটা বেশি প্রাসঙ্গিক।

অনুসন্ধান ও র‍্যাঙ্কিং:

SELECT
    id, title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', body, query,
                'StartSel=<mark>, StopSel=</mark>, MaxWords=30') AS snippet
FROM articles, websearch_to_tsquery('english', 'postgresql index') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

ts_headline অনুসন্ধানের ফলাফলে হাইলাইট করা অংশ দেখায় — ঠিক Google-এর মতো।

১৯.৪ বাংলা টেক্সট প্রসঙ্গে

PostgreSQL-এ বাংলার জন্য built-in text search configuration নেই। বিকল্প:

-- 'simple' configuration ব্যবহার — stemming হবে না, কিন্তু tokenize হবে
SELECT to_tsvector('simple', 'বাংলাদেশের রাজধানী ঢাকা');

simple configuration শব্দগুলোকে ভাঙে ও ছোট হাতের করে, কিন্তু শব্দমূলে নেয় না। বাংলা কনটেন্টে এটা যথেষ্ট কাজ দেয়। আরও ভালো ফলের জন্য pg_trgm দিয়ে সাদৃশ্য-ভিত্তিক খোঁজা মেশানো যায়:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_bn_title ON articles USING GIN (title gin_trgm_ops);

SELECT title, similarity(title, 'ঢাকা শহর') AS sim
FROM articles
WHERE title % 'ঢাকা শহর'
ORDER BY sim DESC;

১৯.৫ Synonym ও Dictionary

-- একটা কাস্টম configuration
CREATE TEXT SEARCH CONFIGURATION bn_search (COPY = simple);

উন্নত ব্যবহারে synonym, thesaurus, ispell dictionary যোগ করা যায় — কিন্তু সেসবের জন্য সার্ভারে ফাইল রাখতে হয়, তাই managed hosting-এ সীমাবদ্ধতা থাকতে পারে।

২০.১ সহজ SQL Function

CREATE OR REPLACE FUNCTION order_total(p_order_id INT)
RETURNS NUMERIC AS $$
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = p_order_id;
$$ LANGUAGE sql STABLE;

SELECT order_total(1);
SELECT id, order_total(id) FROM orders;

$$ ... $$ হলো dollar quoting — ভেতরে একক কোট ব্যবহার করতে সুবিধা।

Volatility শ্রেণি (planner-এর জন্য গুরুত্বপূর্ণ):

সঠিক শ্রেণি দিলে PostgreSQL অনেক optimization করতে পারে। Expression index-এ শুধু IMMUTABLE function ব্যবহার করা যায়।

২০.২ PL/pgSQL — Procedural Language

CREATE OR REPLACE FUNCTION apply_discount(
    p_product_id INT,
    p_percent    NUMERIC
)
RETURNS NUMERIC AS $$
DECLARE
    v_old_price NUMERIC;
    v_new_price NUMERIC;
BEGIN
    -- মান নেওয়া
    SELECT price INTO v_old_price FROM products WHERE id = p_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'পণ্য পাওয়া যায়নি: %', p_product_id;
    END IF;

    IF p_percent < 0 OR p_percent > 90 THEN
        RAISE EXCEPTION 'ছাড় ০ থেকে ৯০%% এর মধ্যে হতে হবে, পেয়েছি %', p_percent;
    END IF;

    v_new_price := ROUND(v_old_price * (1 - p_percent / 100), 2);

    UPDATE products SET price = v_new_price WHERE id = p_product_id;

    RAISE NOTICE 'দাম % থেকে % হলো', v_old_price, v_new_price;
    RETURN v_new_price;
END;
$$ LANGUAGE plpgsql;

SELECT apply_discount(1, 10);

২০.৩ নিয়ন্ত্রণ কাঠামো

-- IF
IF condition THEN
    ...
ELSIF condition THEN
    ...
ELSE
    ...
END IF;

-- CASE
CASE x
    WHEN 1 THEN ...
    WHEN 2, 3 THEN ...
    ELSE ...
END CASE;

-- LOOP
LOOP
    EXIT WHEN condition;
    CONTINUE WHEN condition;
END LOOP;

-- WHILE
WHILE condition LOOP
    ...
END LOOP;

-- FOR (সংখ্যা)
FOR i IN 1..10 LOOP
    RAISE NOTICE '%', i;
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP ... END LOOP;

-- FOR (query এর উপর)
FOR rec IN SELECT * FROM products WHERE stock < 10 LOOP
    RAISE NOTICE 'কম স্টক: % (%)', rec.name, rec.stock;
END LOOP;

২০.৪ Set ফেরত দেওয়া

-- RETURNS TABLE — সবচেয়ে পরিষ্কার
CREATE OR REPLACE FUNCTION top_customers(p_limit INT DEFAULT 5)
RETURNS TABLE (
    customer_name TEXT,
    order_count   BIGINT,
    total_spent   NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT c.name,
           COUNT(DISTINCT o.id),
           COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM customers c
    JOIN orders o       ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY c.id, c.name
    ORDER BY 3 DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM top_customers(3);

-- RETURN NEXT — একটা একটা করে
CREATE FUNCTION fib(n INT) RETURNS SETOF INT AS $$
DECLARE a INT := 0; b INT := 1; t INT;
BEGIN
    FOR i IN 1..n LOOP
        RETURN NEXT a;
        t := a + b; a := b; b := t;
    END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM fib(10);

২০.৫ Exception Handling

CREATE OR REPLACE FUNCTION safe_transfer(
    p_from INT, p_to INT, p_amount NUMERIC
) RETURNS TEXT AS $$
DECLARE
    v_balance NUMERIC;
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'পরিমাণ ধনাত্মক হতে হবে';
    END IF;

    SELECT balance INTO v_balance
    FROM accounts WHERE id = p_from FOR UPDATE;

    IF v_balance < p_amount THEN
        RAISE EXCEPTION 'পর্যাপ্ত ব্যালেন্স নেই। আছে %, দরকার %', v_balance, p_amount
              USING ERRCODE = 'insufficient_funds';
    END IF;

    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

    RETURN 'সফল';

EXCEPTION
    WHEN insufficient_funds THEN
        RETURN 'ব্যর্থ: ব্যালেন্স কম';
    WHEN division_by_zero THEN
        RETURN 'ব্যর্থ: শূন্য দিয়ে ভাগ';
    WHEN OTHERS THEN
        RAISE NOTICE 'অপ্রত্যাশিত ভুল: % (%)', SQLERRM, SQLSTATE;
        RETURN 'ব্যর্থ: ' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

SQLERRM = error বার্তা, SQLSTATE = error কোড।

২০.৬ Procedure (PostgreSQL 11+)

Function-এর সাথে মূল পার্থক্য — procedure-এর ভেতরে COMMIT/ROLLBACK করা যায়।

CREATE OR REPLACE PROCEDURE archive_old_orders(p_before DATE)
LANGUAGE plpgsql AS $$
DECLARE
    v_count INT := 0;
    rec RECORD;
BEGIN
    FOR rec IN SELECT id FROM orders WHERE order_date < p_before LOOP
        INSERT INTO orders_archive SELECT * FROM orders WHERE id = rec.id;
        DELETE FROM orders WHERE id = rec.id;
        v_count := v_count + 1;

        -- প্রতি ১০০০ টার পরে commit — লম্বা transaction এড়াতে
        IF v_count % 1000 = 0 THEN
            COMMIT;
            RAISE NOTICE '% টা সরানো হয়েছে', v_count;
        END IF;
    END LOOP;
    COMMIT;
END;
$$;

CALL archive_old_orders('2025-01-01');

Function ডাকা হয় SELECT দিয়ে, procedure ডাকা হয় CALL দিয়ে।

২০.৭ কখন Function ব্যবহার করবেন — আর কখন নয়

ভালো ব্যবহার: - জটিল ব্যবসায়িক যুক্তি যা ডাটার কাছাকাছি থাকা দরকার - একাধিক অ্যাপ্লিকেশন একই যুক্তি ব্যবহার করে - অনেক round-trip বাঁচে (নেটওয়ার্ক খরচ কমে) - Trigger

সতর্কতা: - Version control করা কঠিন (migration ফাইলে রাখুন) - Debug করা কঠিন - অ্যাপ্লিকেশন লজিক ছড়িয়ে যায় — নতুন ডেভেলপার খুঁজে পায় না - Database সার্ভার scale করা অ্যাপ সার্ভারের চেয়ে ব্যয়বহুল

ভারসাম্য: ডাটা-ঘনিষ্ঠ কাজ (aggregation, validation, trigger) database-এ; ব্যবসায়িক প্রবাহ অ্যাপ্লিকেশনে।

Trigger হলো এমন function যা কোনো ঘটনা ঘটলে স্বয়ংক্রিয়ভাবে চলে।

২১.১ গঠন

-- ধাপ ১: trigger function (RETURNS TRIGGER)
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ধাপ ২: trigger যুক্ত করা
CREATE TRIGGER trg_products_updated
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

২১.২ বিশেষ চলক

ফেরত মান: - BEFORE ... FOR EACH ROW — NEW ফেরত দিলে কাজ এগোবে; NULL দিলে অপারেশন বাতিল হবে - AFTER trigger-এর ফেরত মান উপেক্ষিত (RETURN NULL দিলেই চলে)

২১.৩ প্রকারভেদ

BEFORE / AFTER / INSTEAD OF
INSERT / UPDATE / DELETE / TRUNCATE
FOR EACH ROW / FOR EACH STATEMENT

• BEFORE — মান বদলানো, validation, বাতিল করা
• AFTER — log রাখা, অন্য টেবিল আপডেট করা, notification
• INSTEAD OF — শুধু view-তে

২১.৪ ব্যবহারিক উদাহরণ

Audit Log

CREATE TABLE audit_log (
    id         BIGSERIAL PRIMARY KEY,
    table_name TEXT,
    operation  TEXT,
    row_id     INT,
    old_data   JSONB,
    new_data   JSONB,
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, row_id, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSE
        INSERT INTO audit_log (table_name, operation, row_id, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

একটা function দিয়ে যেকোনো টেবিলে audit বসানো যাবে — খুব পুনঃব্যবহারযোগ্য।

স্টক নিয়ন্ত্রণ

CREATE OR REPLACE FUNCTION reduce_stock()
RETURNS TRIGGER AS $$
DECLARE
    v_stock INT;
BEGIN
    SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id FOR UPDATE;

    IF v_stock < NEW.quantity THEN
        RAISE EXCEPTION 'পর্যাপ্ত স্টক নেই। আছে %, চাওয়া হয়েছে %', v_stock, NEW.quantity;
    END IF;

    UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_reduce_stock
AFTER INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION reduce_stock();

শর্তসাপেক্ষ Trigger (WHEN)

CREATE TRIGGER trg_price_change
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)   -- শুধু দাম বদলালে
EXECUTE FUNCTION log_price_change();

WHEN ব্যবহার করলে অপ্রয়োজনীয়ভাবে function চলবে না — performance-এ বড় পার্থক্য।

২১.৫ INSTEAD OF Trigger (View-তে লেখা)

CREATE VIEW customer_orders AS
SELECT o.id, o.order_date, c.name AS customer_name, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id;

CREATE OR REPLACE FUNCTION insert_customer_order()
RETURNS TRIGGER AS $$
DECLARE v_cust_id INT;
BEGIN
    SELECT id INTO v_cust_id FROM customers WHERE email = NEW.email;
    IF NOT FOUND THEN
        INSERT INTO customers (name, email)
        VALUES (NEW.customer_name, NEW.email) RETURNING id INTO v_cust_id;
    END IF;
    INSERT INTO orders (customer_id, order_date)
    VALUES (v_cust_id, COALESCE(NEW.order_date, CURRENT_DATE));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_insert_co
INSTEAD OF INSERT ON customer_orders
FOR EACH ROW EXECUTE FUNCTION insert_customer_order();

২১.৬ Trigger ব্যবস্থাপনা

ALTER TABLE products DISABLE TRIGGER trg_audit_products;
ALTER TABLE products ENABLE TRIGGER trg_audit_products;
ALTER TABLE products DISABLE TRIGGER ALL;      -- bulk load এর সময়
DROP TRIGGER trg_audit_products ON products;

-- তালিকা দেখা
SELECT tgname, tgrelid::regclass AS table_name, tgenabled
FROM pg_trigger WHERE NOT tgisinternal;

২১.৭ Trigger নিয়ে সতর্কবাণী

Trigger শক্তিশালী, কিন্তু বিপজ্জনকও:

1. লুকানো আচরণ — কোড পড়ে বোঝা যায় না কেন ডাটা বদলে গেল
2. Performance — প্রতিটা row-তে চলে; bulk insert-এ ভয়াবহ ধীর
3. শৃঙ্খল প্রতিক্রিয়া — এক trigger আরেকটাকে ডাকে, ডিবাগ দুঃস্বপ্ন
4. Recursion — একই টেবিলে আপডেট করলে নিজেই নিজেকে ডাকতে পারে

ভালো ব্যবহার: updated_at আপডেট, audit log, denormalized count রক্ষণাবেক্ষণ। খারাপ ব্যবহার: ব্যবসায়িক প্রবাহ, ইমেইল পাঠানো, বাইরের API কল।

২২.১ কেন

একটা টেবিলে ৫০ কোটি row হয়ে গেলে: - Query ধীর - VACUUM অসম্ভব ধীর - Index বিশাল, RAM-এ আঁটে না - পুরনো ডাটা মোছা ভয়াবহ ব্যয়বহুল

Partitioning একটা বড় টেবিলকে ছোট ছোট শারীরিক টেবিলে ভাগ করে — কিন্তু আপনার কাছে মনে হবে একটাই টেবিল।

২২.২ Range Partitioning (সবচেয়ে প্রচলিত)

CREATE TABLE events (
    id         BIGSERIAL,
    user_id    INT NOT NULL,
    event_type TEXT NOT NULL,
    payload    JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Partition তৈরি
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- যেগুলো কোথাও পড়ে না
CREATE TABLE events_default PARTITION OF events DEFAULT;

ALTER TABLE events ADD PRIMARY KEY (id, created_at);

২২.৩ List ও Hash Partitioning

-- List — নির্দিষ্ট মান অনুযায়ী
CREATE TABLE sales (
    id SERIAL, region TEXT NOT NULL, amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE sales_dhaka PARTITION OF sales FOR VALUES IN ('Dhaka','Gazipur');
CREATE TABLE sales_ctg   PARTITION OF sales FOR VALUES IN ('Chattogram');
CREATE TABLE sales_other PARTITION OF sales DEFAULT;

-- Hash — সমানভাবে ছড়িয়ে দেওয়া
CREATE TABLE users (id INT, name TEXT) PARTITION BY HASH (id);
CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

২২.৪ Partition Pruning — আসল সুবিধা

EXPLAIN SELECT * FROM events WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
-- পরিকল্পনায় শুধু events_2026_02 দেখাবে — বাকিগুলো ছুঁয়েও দেখবে না

কিন্তু query-তে partition key না থাকলে সব partition স্ক্যান হবে — তখন partitioning উল্টো ক্ষতি করে। Partition key বাছাই সবচেয়ে গুরুত্বপূর্ণ সিদ্ধান্ত — আপনার সবচেয়ে সাধারণ query-তে যে কলাম দিয়ে ছাঁকা হয়, সেটাই নিন।

২২.৫ Partition ব্যবস্থাপনা

-- পুরনো ডাটা তাৎক্ষণিকভাবে মোছা (DELETE এর তুলনায় হাজার গুণ দ্রুত)
DROP TABLE events_2026_01;

-- অথবা আলাদা করে রাখা
ALTER TABLE events DETACH PARTITION events_2026_01;

-- বিদ্যমান টেবিলকে partition বানানো
ALTER TABLE events ATTACH PARTITION events_2026_04
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- partition গুলো দেখা
SELECT c.relname, pg_size_pretty(pg_relation_size(c.oid))
FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'events'::regclass;

পুরনো ডাটা মোছার জন্য DROP TABLE partition — এটাই partitioning-এর সবচেয়ে বড় ব্যবহারিক সুবিধা। ১০ কোটি row DELETE করতে ঘণ্টা লাগবে; partition drop করতে এক সেকেন্ড।

২২.৬ স্বয়ংক্রিয় Partition তৈরি

PostgreSQL নিজে থেকে ভবিষ্যতের partition বানায় না। তিনটা উপায়:

1. pg_partman extension — সবচেয়ে জনপ্রিয়, স্বয়ংক্রিয়ভাবে বানায় ও পুরনোগুলো মোছে
2. Cron job — মাসে একবার স্ক্রিপ্ট চালিয়ে পরের মাসের partition বানান
3. DEFAULT partition — নিরাপত্তা জাল হিসেবে রাখুন, কিন্তু নির্ভর করবেন না

২২.৭ কখন Partition করবেন

করুন যখন: - টেবিল ১০০ GB+ বা কয়েকশো কোটি row - ডাটার স্বাভাবিক সময়ভিত্তিক বিভাজন আছে - পুরনো ডাটা নিয়মিত মুছতে হয় - বেশিরভাগ query একটা নির্দিষ্ট পরিসরে সীমাবদ্ধ

করবেন না যখন: - টেবিল ছোট (index-ই যথেষ্ট) - Query-তে partition key প্রায়ই থাকে না - শুধু "শুনেছি এটা দ্রুত করে" — এটা কোনো কারণ নয়

২৩.১ EXPLAIN পড়তে শেখা

এটাই database performance-এর সবচেয়ে গুরুত্বপূর্ণ দক্ষতা।

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
-- শুধু পরিকল্পনা, query চালায় না

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
-- সত্যি চালায়, আসল সময় দেখায়

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT ... ;
-- সবচেয়ে বিস্তারিত

২৩.২ আউটপুট বোঝা

Seq Scan on orders  (cost=0.00..25.88 rows=6 width=44)
                    (actual time=0.015..0.021 rows=8 loops=1)
  Filter: (customer_id = 1)
  Rows Removed by Filter: 0
Planning Time: 0.089 ms
Execution Time: 0.041 ms

সবচেয়ে বড় সংকেত: অনুমান (rows=6) আর বাস্তবের (rows=8) মধ্যে বড় ফারাক (১০x বা বেশি)। এর মানে পরিসংখ্যান পুরনো — ANALYZE tablename; চালান।

loops বেশি হলে সংখ্যাগুলো প্রতি loop-এর — মোট সময় = actual time × loops।

২৩.৩ Scan-এর ধরন

সতর্ক সংকেত: - Sort Method: external merge Disk: 45000kB → work_mem বাড়ান - Rows Removed by Filter: 500000 → index দরকার - Nested Loop + বাইরের দিকে অনেক row → খুব ধীর হবে

২৩.৪ ধীর query খুঁজে বের করা

pg_stat_statements — অপরিহার্য

-- postgresql.conf এ:
-- shared_preload_libraries = 'pg_stat_statements'
-- তারপর সার্ভার restart

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- সবচেয়ে বেশি মোট সময় নেওয়া query
SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT pg_stat_statements_reset();   -- পরিসংখ্যান রিসেট

গুরুত্বপূর্ণ অন্তর্দৃষ্টি: যে query ৫ সেকেন্ড নেয় কিন্তু দিনে একবার চলে, সেটা তত বড় সমস্যা নয়। যে query ৫০ মিলিসেকেন্ড নেয় কিন্তু সেকেন্ডে ১০০০ বার চলে — সেটাই আপনার সার্ভার খাচ্ছে। তাই total_exec_time দিয়ে সাজান, mean_exec_time দিয়ে নয়।

চলমান ধীর query

SELECT pid, now() - query_start AS duration, state, LEFT(query, 100)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

slow query log

# postgresql.conf
log_min_duration_statement = 1000   # ১ সেকেন্ডের বেশি নিলে log করবে
log_line_prefix = '%t [%p] %u@%d '

২৩.৫ Configuration টিউনিং

মূল প্যারামিটারগুলো (একটা ১৬ GB RAM সার্ভারের জন্য নমুনা):

# মোট RAM এর ২৫%
shared_buffers = 4GB

# মোট RAM এর ৫০-৭৫% (planner কে ইঙ্গিত, বরাদ্দ করে না)
effective_cache_size = 12GB

# প্রতি sort/hash অপারেশনে। সাবধান: একটা query তে একাধিকবার লাগতে পারে
work_mem = 32MB

# VACUUM, CREATE INDEX এর জন্য
maintenance_work_mem = 1GB

# SSD হলে 1.1, HDD হলে 4.0
random_page_cost = 1.1

# SSD তে বেশি
effective_io_concurrency = 200

# সমান্তরাল কাজ
max_parallel_workers_per_gather = 4

# WAL
wal_buffers = 16MB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

pgtune.leopard.in.ua — এখানে আপনার হার্ডওয়্যার দিলে একটা ভালো শুরুর কনফিগ দেবে।

work_mem নিয়ে সাবধান — এটা প্রতি অপারেশন প্রতি connection। ১০০ connection × ৩টা sort × 32MB = ৯.৬ GB। বেশি দিলে সার্ভার OOM হয়ে যাবে।

২৩.৬ Query লেখার ভালো অভ্যাস

-- ১. প্রয়োজনীয় কলামই নিন
SELECT id, name FROM products;         -- SELECT * নয়

-- ২. WHERE এ কলামে function দেবেন না
WHERE created_at >= '2026-01-01'       -- ✅ index ব্যবহার হবে
WHERE EXTRACT(YEAR FROM created_at) = 2026   -- ❌ হবে না

-- ৩. NOT IN এর বদলে NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM ... )  -- ✅
WHERE id NOT IN (SELECT ...)           -- ❌ NULL ফাঁদ + ধীর

-- ৪. অপ্রয়োজনীয় DISTINCT বাদ দিন (এটা sort করায়)

-- ৫. Bulk operation একসাথে করুন
INSERT INTO t VALUES (1),(2),(3);      -- ✅ একটা statement
-- লুপে ১০০০ বার INSERT নয়

-- ৬. খুব বড় ডাটা লোডে COPY ব্যবহার করুন
COPY products FROM '/path/data.csv' WITH (FORMAT csv, HEADER);
\copy products FROM 'data.csv' WITH (FORMAT csv, HEADER)   -- client side

COPY সাধারণ INSERT-এর চেয়ে ১০-১০০ গুণ দ্রুত। লক্ষ লক্ষ row লোড করতে হলে এটাই একমাত্র যুক্তিসঙ্গত পথ।

২৩.৭ N+1 সমস্যা

ORM ব্যবহারকারীদের সবচেয়ে সাধারণ performance ঘাতক:

# ❌ ১ + N টা query
orders = db.query("SELECT * FROM orders")
for order in orders:                                  # ১০০ টা অর্ডার হলে
    customer = db.query("SELECT * FROM customers WHERE id = ?", order.customer_id)
    # ১০০ টা আলাদা query!

# ✅ ১ টা query
rows = db.query("""
    SELECT o.*, c.name, c.email
    FROM orders o JOIN customers c ON c.id = o.customer_id
""")

Django-তে select_related()/prefetch_related(), Rails-এ includes(), Prisma-তে include — প্রতিটা ORM-এ এর সমাধান আছে। ব্যবহার করুন।

২৩.৮ Bulk Load-এর কৌশল

কোটি row লোড করার সময়:

-- ১. index ও constraint সাময়িকভাবে বাদ
DROP INDEX idx_big_col;
ALTER TABLE big_table DROP CONSTRAINT fk_something;

-- ২. UNLOGGED করে নিন (WAL এড়ানো)
ALTER TABLE big_table SET UNLOGGED;

-- ৩. COPY দিয়ে লোড
\copy big_table FROM 'data.csv' CSV HEADER

-- ৪. সব ফিরিয়ে আনুন
ALTER TABLE big_table SET LOGGED;
CREATE INDEX idx_big_col ON big_table(col);
ALTER TABLE big_table ADD CONSTRAINT fk_something ...;

-- ৫. পরিসংখ্যান হালনাগাদ
ANALYZE big_table;

২৪.১ Bloat কেন হয়

মনে আছে MVCC? UPDATE করলে পুরনো version ডিস্কে থেকে যায় — dead tuple। DELETE করলেও তাই।

ফলাফল: টেবিলে ১০ লাখ live row, কিন্তু ডিস্কে ৫০ লাখ tuple-এর জায়গা দখল। Query ধীর হয়, ডিস্ক ভরে যায়।

-- Bloat পরিমাপ
SELECT relname,
       n_live_tup, n_dead_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

dead_pct ২০%-এর বেশি হলে মনোযোগ দিন।

২৪.২ VACUUM

VACUUM;                          -- সব টেবিল
VACUUM products;                 -- একটা টেবিল
VACUUM (VERBOSE, ANALYZE) products;

VACUUM FULL products;            -- ⚠️ দেখুন নিচে
ANALYZE products;                -- শুধু পরিসংখ্যান হালনাগাদ

VACUUM বনাম VACUUM FULL:

VACUUM FULL production-এ চালাবেন না (রক্ষণাবেক্ষণের নির্ধারিত সময় ছাড়া)। বিকল্প: pg_repack extension — একই কাজ করে কিন্তু lock ছাড়া।

২৪.৩ Autovacuum

PostgreSQL নিজে থেকেই VACUUM চালায়। কখনো বন্ধ করবেন না।

SHOW autovacuum;

-- বর্তমান সেটিংস
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';

মূল সেটিংস:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.2    # ২০% dead হলে vacuum
autovacuum_analyze_scale_factor = 0.1   # ১০% বদলালে analyze
autovacuum_max_workers = 3
autovacuum_naptime = 1min

বড় টেবিলে সমস্যা: ১ কোটি row-এর টেবিলে ২০% মানে ২০ লাখ dead tuple জমতে হবে vacuum চালু হওয়ার আগে। খুব দেরি।

সমাধান — প্রতি টেবিলে আলাদা সেটিং:

ALTER TABLE big_table SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- ১%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005
);

২৪.৪ Transaction ID Wraparound — নীরব ঘাতক

PostgreSQL-এর transaction ID ৩২-বিট — প্রায় ৪ বিলিয়নের পরে ঘুরে শুরু থেকে আসে। VACUUM এই ID গুলোকে "হিমায়িত" (freeze) করে সমস্যা এড়ায়।

যদি VACUUM যথেষ্ট না চলে, PostgreSQL সব write বন্ধ করে দেয় ডাটা রক্ষার জন্য। এটা বাস্তব দুর্ঘটনা — বড় কোম্পানিতেও ঘটেছে।

-- কতটা কাছে আছেন দেখুন
SELECT datname, age(datfrozenxid),
       2^31 - age(datfrozenxid) AS remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

age ২০০ মিলিয়নের কাছে গেলে সতর্ক হোন। ১.৫ বিলিয়ন হলে জরুরি অবস্থা।

প্রতিরোধ: autovacuum চালু রাখুন, খুব লম্বা transaction এড়ান, replication slot অব্যবহৃত পড়ে থাকতে দেবেন না।

২৪.৫ Index Bloat ও REINDEX

REINDEX INDEX idx_name;
REINDEX TABLE products;
REINDEX INDEX CONCURRENTLY idx_name;   -- PG 12+, lock ছাড়া

২৪.৬ নিয়মিত রক্ষণাবেক্ষণ চেকলিস্ট

দৈনিক: - Backup ঠিকমতো হচ্ছে কিনা - ডিস্ক জায়গা - Error log

সাপ্তাহিক: - ধীর query পর্যালোচনা (pg_stat_statements) - Bloat পরীক্ষা - Connection সংখ্যা

মাসিক: - অব্যবহৃত index মোছা - Transaction ID age পরীক্ষা - Backup থেকে restore করে দেখা (সবচেয়ে গুরুত্বপূর্ণ)

২৫.১ Role

PostgreSQL-এ "user" আর "group" আলাদা নয় — দুটোই role। পার্থক্য শুধু LOGIN অধিকার আছে কিনা।

-- লগইন করতে পারে এমন role (= user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_pass_here';

-- গ্রুপ role
CREATE ROLE readonly;
CREATE ROLE developer;

-- সদস্য বানানো
GRANT readonly TO app_user;

-- বিশেষ অধিকার
CREATE ROLE admin WITH LOGIN PASSWORD 'x' SUPERUSER CREATEDB CREATEROLE;

-- মেয়াদ
CREATE ROLE temp_user WITH LOGIN PASSWORD 'x' VALID UNTIL '2026-12-31';

-- সংযোগ সীমা
ALTER ROLE app_user CONNECTION LIMIT 20;

ALTER ROLE app_user WITH PASSWORD 'new_pass';
DROP ROLE app_user;

২৫.২ Privilege

-- Database
GRANT CONNECT ON DATABASE shop TO readonly;

-- Schema
GRANT USAGE ON SCHEMA public TO readonly;

-- Table
GRANT SELECT ON products TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO developer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer;

-- কলাম পর্যায়ে
GRANT SELECT (id, name, email) ON customers TO support_staff;
-- phone, address দেখতে পাবে না

-- Sequence (SERIAL ব্যবহার করলে লাগবে)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO developer;

-- ভবিষ্যতের টেবিলের জন্যও
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- ফিরিয়ে নেওয়া
REVOKE INSERT ON orders FROM developer;

ALTER DEFAULT PRIVILEGES অত্যন্ত কাজের — নইলে নতুন টেবিল বানানোর পর প্রতিবার হাতে GRANT করতে হয়।

২৫.৩ ন্যূনতম অধিকারের নীতি

একটা বাস্তব সেটআপ:

-- ১. অ্যাপ্লিকেশনের নিজস্ব role
CREATE ROLE shop_app WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE shop TO shop_app;
GRANT USAGE ON SCHEMA public TO shop_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO shop_app;

-- ২. রিপোর্টিং টুলের জন্য শুধু পড়ার role
CREATE ROLE reporting WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE shop TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

-- ৩. Migration এর জন্য আলাদা, বেশি ক্ষমতাসম্পন্ন role
CREATE ROLE migrator WITH LOGIN PASSWORD '...';
GRANT ALL ON DATABASE shop TO migrator;

কখনোই অ্যাপ্লিকেশনকে postgres superuser দিয়ে চালাবেন না। SQL injection হলে পুরো সার্ভার আক্রমণকারীর হাতে।

২৫.৪ Row Level Security (RLS)

Multi-tenant অ্যাপের জন্য অসাধারণ — একই টেবিলে সব ক্লায়েন্টের ডাটা, কিন্তু প্রত্যেকে শুধু নিজেরটা দেখবে।

CREATE TABLE documents (
    id       SERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    owner    TEXT NOT NULL,
    title    TEXT,
    content  TEXT
);

-- RLS চালু
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- নীতি: ইউজার শুধু নিজের document দেখবে
CREATE POLICY doc_owner_policy ON documents
    FOR ALL
    USING (owner = current_user)
    WITH CHECK (owner = current_user);

-- ম্যানেজাররা সব দেখবে
CREATE POLICY doc_manager_policy ON documents
    FOR SELECT
    TO manager_role
    USING (TRUE);

• USING — কোন row দেখা যাবে (SELECT, UPDATE, DELETE-এ)
• WITH CHECK — কোন row লেখা যাবে (INSERT, UPDATE-এ)

Session variable দিয়ে tenant আলাদা করা (SaaS অ্যাপের সাধারণ প্যাটার্ন):

CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- অ্যাপ্লিকেশন প্রতিটা connection এ সেট করবে
SET app.tenant_id = '42';
SELECT * FROM documents;   -- শুধু tenant 42 এর ডাটা

২৫.৫ SQL Injection প্রতিরোধ

# ❌ কখনোই না
query = f"SELECT * FROM users WHERE email = '{user_input}'"
# user_input = "' OR '1'='1" দিলে সব ডাটা বেরিয়ে যাবে

# ✅ Parameterized query
cur.execute("SELECT * FROM users WHERE email = %s", (user_input,))

// ❌
const q = `SELECT * FROM users WHERE id = ${id}`;

// ✅
await client.query('SELECT * FROM users WHERE id = $1', [id]);

নিয়ম: ব্যবহারকারীর ইনপুট কখনোই string concatenation দিয়ে SQL-এ ঢোকাবেন না। ORM ব্যবহার করলেও raw query লেখার সময় এই নিয়ম প্রযোজ্য।

২৫.৬ সংযোগ নিরাপত্তা

pg_hba.conf — কে কোথা থেকে কীভাবে সংযোগ করতে পারবে:

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
host    shop      shop_app  10.0.1.0/24     scram-sha-256
hostssl shop      reporting 0.0.0.0/0       scram-sha-256
host    all       all       0.0.0.0/0       reject

• scram-sha-256 ব্যবহার করুন, md5 নয় (পুরনো ও দুর্বল)
• trust কখনো নয় — এটা পাসওয়ার্ড ছাড়াই ঢুকতে দেয়
• ইন্টারনেট থেকে সরাসরি ৫৪৩২ পোর্ট খুলে রাখবেন না
• সম্ভব হলে SSL বাধ্যতামূলক করুন (hostssl)

পরিবর্তনের পরে:

SELECT pg_reload_conf();

২৫.৭ Encryption

• চলাচলে (in transit): SSL/TLS — ssl = on এবং sslmode=require ক্লায়েন্টে
• বিশ্রামে (at rest): ডিস্ক-স্তরে (LUKS, cloud provider-এর encryption)
• কলাম পর্যায়ে: pgcrypto extension

CREATE EXTENSION pgcrypto;

-- পাসওয়ার্ড hash
INSERT INTO users (email, password_hash)
VALUES ('a@b.com', crypt('mypassword', gen_salt('bf', 12)));

-- যাচাই
SELECT id FROM users
WHERE email = 'a@b.com'
  AND password_hash = crypt('mypassword', password_hash);

gen_salt('bf', 12) = bcrypt, ১২ রাউন্ড। যদিও আধুনিক অ্যাপে পাসওয়ার্ড hashing সাধারণত অ্যাপ্লিকেশন স্তরে (argon2) করা হয়।

২৬.১ Logical Backup — pg_dump

# একটা database
pg_dump -U postgres -d shop -f shop.sql

# সংকুচিত custom format (সুপারিশকৃত)
pg_dump -U postgres -d shop -Fc -f shop.dump

# শুধু গঠন
pg_dump -U postgres -d shop --schema-only -f schema.sql

# শুধু ডাটা
pg_dump -U postgres -d shop --data-only -f data.sql

# নির্দিষ্ট টেবিল
pg_dump -U postgres -d shop -t products -t categories -f partial.sql

# সমান্তরাল (দ্রুত, শুধু directory format এ)
pg_dump -U postgres -d shop -Fd -j 4 -f shop_dir/

# পুরো cluster (role, tablespace সহ)
pg_dumpall -U postgres -f full_cluster.sql

পুনরুদ্ধার:

# .sql ফাইল
psql -U postgres -d shop_new -f shop.sql

# .dump ফাইল
pg_restore -U postgres -d shop_new shop.dump

# সমান্তরাল restore
pg_restore -U postgres -d shop_new -j 4 shop.dump

# পরিষ্কার করে তারপর restore
pg_restore -U postgres -d shop_new --clean --if-exists shop.dump

# .dump থেকে নির্দিষ্ট টেবিল
pg_restore -U postgres -d shop_new -t products shop.dump

-Fc (custom format) ব্যবহারের সুবিধা: সংকুচিত, নির্বাচিতভাবে restore করা যায়, সমান্তরালে চলে।

২৬.২ Physical Backup — pg_basebackup

পুরো ডাটা ডিরেক্টরির বাইট-বাই-বাইট কপি।

pg_basebackup -U replicator -h primary_host \
  -D /var/lib/postgresql/backup \
  -Fp -Xs -P -R

• -Fp plain format, -Xs WAL stream সহ, -P অগ্রগতি, -R replication config বানাবে

Logical বনাম Physical:

২৬.৩ Point-in-Time Recovery (PITR)

WAL archive রাখলে যেকোনো মুহূর্তে ফিরে যাওয়া যায় — "গতকাল সন্ধ্যা ৭টা ১৫ মিনিটে ফিরে চলো"।

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

পুনরুদ্ধারের সময়:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-07-23 19:15:00+06'

বাস্তবে pgBackRest বা WAL-G ব্যবহার করুন — এরা compression, encryption, cloud storage, retention সব সামলায়।

২৬.৪ Replication

Streaming Replication (Physical)

Primary-র WAL সরাসরি standby-তে পাঠানো। পুরো cluster হুবহু কপি হয়।

Primary-তে:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '...';

Standby-তে:

pg_basebackup -h primary -U replicator -D /var/lib/postgresql/data -R -Xs -P
# standby.signal ফাইল তৈরি হবে

অবস্থা দেখা:

-- primary তে
SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- standby তে
SELECT pg_is_in_recovery();                -- true
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Synchronous vs Asynchronous:

synchronous_commit = on
synchronous_standby_names = 'standby1'

Synchronous — ডাটা হারানোর ঝুঁকি শূন্য, কিন্তু প্রতিটা commit ধীর। Asynchronous (default) — দ্রুত, কিন্তু primary হঠাৎ মরলে শেষ কয়েকটা transaction হারাতে পারে।

Logical Replication

নির্দিষ্ট টেবিল, ভিন্ন version-এর মধ্যেও চলে।

-- Publisher এ
CREATE PUBLICATION my_pub FOR TABLE products, orders;
-- অথবা সব টেবিল
CREATE PUBLICATION all_pub FOR ALL TABLES;

-- Subscriber এ
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher dbname=shop user=replicator password=...'
  PUBLICATION my_pub;

প্রধান ব্যবহার: downtime প্রায় ছাড়াই major version upgrade, নির্দিষ্ট ডাটা analytics সার্ভারে পাঠানো, ভিন্ন ভিন্ন সিস্টেমের মধ্যে sync।

২৬.৫ High Availability

• Patroni — সবচেয়ে জনপ্রিয়, স্বয়ংক্রিয় failover, etcd/Consul ব্যবহার করে
• repmgr — সহজতর, কিন্তু কম স্বয়ংক্রিয়
• pgpool-II — connection pooling + load balancing + failover
• Managed service — AWS RDS/Aurora, Google Cloud SQL, Azure, Supabase, Neon

ছোট দলের জন্য managed service প্রায় সবসময়ই সঠিক সিদ্ধান্ত। নিজে HA সেটআপ চালানো একটা পূর্ণকালীন কাজ।

২৬.৬ সোনালি নিয়ম

মাসে অন্তত একবার backup থেকে একটা আলাদা সার্ভারে restore করে দেখুন সব ঠিক আছে কিনা।

3-2-1 নিয়ম: ৩টা কপি, ২ ধরনের মাধ্যমে, ১টা ভিন্ন স্থানে।

PostgreSQL-এর সবচেয়ে বড় শক্তি — প্লাগইন দিয়ে ক্ষমতা বাড়ানো।

SELECT * FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS extension_name;
SELECT * FROM pg_extension;
DROP EXTENSION extension_name;

গুরুত্বপূর্ণ Extension

নমুনা ব্যবহার

citext:

CREATE EXTENSION citext;
CREATE TABLE users (email CITEXT UNIQUE);
INSERT INTO users VALUES ('Rahim@Example.com');
SELECT * FROM users WHERE email = 'rahim@example.com';   -- মিলবে

pg_cron:

CREATE EXTENSION pg_cron;

-- প্রতিদিন রাত ৩টায় materialized view রিফ্রেশ
SELECT cron.schedule('refresh-sales', '0 3 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales');

SELECT * FROM cron.job;
SELECT cron.unschedule('refresh-sales');

pgvector (AI অ্যাপের জন্য):

CREATE EXTENSION vector;

CREATE TABLE documents (
    id        SERIAL PRIMARY KEY,
    content   TEXT,
    embedding VECTOR(1536)
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- সবচেয়ে কাছের ৫টা
SELECT content, 1 - (embedding <=> '[...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;

postgres_fdw:

CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'other-server', dbname 'analytics', port '5432');

CREATE USER MAPPING FOR current_user SERVER remote_db
    OPTIONS (user 'reader', password '...');

IMPORT FOREIGN SCHEMA public LIMIT TO (sales_summary)
    FROM SERVER remote_db INTO remote;

SELECT * FROM remote.sales_summary;   -- অন্য সার্ভার থেকে আসছে!

২৮.১ Connection String

postgresql://user:password@host:5432/dbname?sslmode=require
postgres://user:password@host:5432/dbname          # একই জিনিস

Environment variable-এ রাখুন, কখনোই কোডে হার্ডকোড করবেন না:

DATABASE_URL=postgresql://shop_app:secret@localhost:5432/shop?sslmode=require

২৮.২ Node.js (node-postgres)

import pg from 'pg';
const { Pool } = pg;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                       // সর্বোচ্চ connection
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Parameterized query — SQL injection থেকে নিরাপদ
async function getCustomerOrders(customerId) {
  const { rows } = await pool.query(
    `SELECT o.id, o.order_date, o.status,
            SUM(oi.quantity * oi.unit_price) AS total
     FROM orders o
     JOIN order_items oi ON oi.order_id = o.id
     WHERE o.customer_id = $1
     GROUP BY o.id
     ORDER BY o.order_date DESC`,
    [customerId]
  );
  return rows;
}

// Transaction
async function createOrder(customerId, items) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const { rows } = await client.query(
      'INSERT INTO orders (customer_id) VALUES ($1) RETURNING id',
      [customerId]
    );
    const orderId = rows[0].id;

    for (const item of items) {
      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, unit_price)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productId, item.quantity, item.price]
      );
    }

    await client.query('COMMIT');
    return orderId;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();     // ← এটা ভুলবেন না, নইলে pool শেষ হয়ে যাবে
  }
}

২৮.৩ Python (psycopg 3)

import psycopg
from psycopg_pool import ConnectionPool
from psycopg.rows import dict_row
import os

pool = ConnectionPool(os.environ["DATABASE_URL"], min_size=2, max_size=20)

def get_products(min_price):
    with pool.connection() as conn:
        with conn.cursor(row_factory=dict_row) as cur:
            cur.execute(
                "SELECT id, name, price FROM products WHERE price >= %s ORDER BY price",
                (min_price,)
            )
            return cur.fetchall()

def create_order(customer_id, items):
    with pool.connection() as conn:      # context manager নিজেই commit/rollback করবে
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO orders (customer_id) VALUES (%s) RETURNING id",
                (customer_id,)
            )
            order_id = cur.fetchone()[0]

            cur.executemany(
                """INSERT INTO order_items (order_id, product_id, quantity, unit_price)
                   VALUES (%s, %s, %s, %s)""",
                [(order_id, i["product_id"], i["qty"], i["price"]) for i in items]
            )
            return order_id

২৮.৪ Connection Pooling

প্রতিটা PostgreSQL connection একটা আলাদা OS process — প্রায় ৫-১০ MB RAM। ১০০০ connection মানে সার্ভার শেষ।

দুই স্তরের pooling:

1. অ্যাপ্লিকেশন স্তরে — উপরের Pool/ConnectionPool
2. বাইরের pooler — PgBouncer (সুপারিশকৃত, বিশেষত serverless বা multi-instance হলে)

# pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

pool_mode: - session — connection ছাড়া পর্যন্ত ধরে রাখে (সবচেয়ে নিরাপদ, কম কার্যকর) - transaction — প্রতি transaction শেষে ছেড়ে দেয় (সবচেয়ে ব্যবহৃত) - statement — প্রতি statement-এ (transaction ব্যবহার করা যাবে না)

transaction mode-এ prepared statement ও session-level SET কাজ করে না — ORM কনফিগে এটা মাথায় রাখুন।

২৮.৫ Migration ব্যবস্থাপনা

Database-এর গঠন পরিবর্তন অবশ্যই version control-এ থাকতে হবে।

migrations/
  001_create_customers.sql
  002_create_products.sql
  003_add_index_orders.sql

জনপ্রিয় টুল: - Flyway, Liquibase — ভাষা-নিরপেক্ষ - Alembic (Python), Prisma Migrate / Knex (Node), Rails Migrations, golang-migrate

নিরাপদ migration-এর নিয়ম:

1. প্রতিটা migration-এর একটা rollback থাকা উচিত
2. Production-এ চালানোর আগে staging-এ চালান
3. বড় টেবিলে index বানাতে CONCURRENTLY ব্যবহার করুন
4. কলাম মোছা দুই ধাপে করুন: প্রথমে অ্যাপ থেকে ব্যবহার বন্ধ করে deploy করুন, পরের release-এ কলাম মুছুন
5. NOT NULL যোগ করার আগে সব row পূরণ করে নিন

২৮.৬ ORM নাকি Raw SQL

ORM ভালো: সাধারণ CRUD, দ্রুত ডেভেলপমেন্ট, type safety, migration ব্যবস্থাপনা।

Raw SQL ভালো: জটিল রিপোর্ট, window function, CTE, performance-সংবেদনশীল query।

ব্যবহারিক পরামর্শ: ORM ব্যবহার করুন, কিন্তু SQL শিখে রাখুন — কারণ ORM-এর তৈরি query দেখে বুঝতে পারতে হবে কেন ধীর। বেশিরভাগ ORM raw SQL চালানোর সুযোগ দেয়; জটিল জায়গায় সেটাই ব্যবহার করুন।

২৯.১ Normalization

1NF (প্রথম স্বাভাবিক রূপ) — প্রতিটা ঘরে একটাই মান, পুনরাবৃত্ত গ্রুপ নেই।

-- ❌ লঙ্ঘন
customers(id, name, phones)   -- phones = '0171...,0182...'

-- ✅
customers(id, name)
customer_phones(id, customer_id, phone)

2NF — 1NF + প্রতিটা non-key কলাম পূর্ণ primary key-এর উপর নির্ভরশীল (যৌগিক key-এর ক্ষেত্রে প্রাসঙ্গিক)।

-- ❌ order_items(order_id, product_id, quantity, product_name)
--    product_name শুধু product_id এর উপর নির্ভরশীল, পুরো key এর নয়

-- ✅ order_items(order_id, product_id, quantity)
--    products(id, name)

3NF — 2NF + কোনো non-key কলাম অন্য non-key কলামের উপর নির্ভরশীল নয়।

-- ❌ employees(id, name, dept_id, dept_name)
--    dept_name নির্ভর করে dept_id এর উপর

-- ✅ employees(id, name, dept_id)
--    departments(id, name)

ব্যবহারিক নিয়ম: 3NF পর্যন্ত যান। BCNF, 4NF, 5NF তাত্ত্বিকভাবে আছে, কিন্তু বাস্তব প্রজেক্টে খুব কমই দরকার হয়।

২৯.২ Denormalization — নিয়ম ভাঙা কখন ঠিক

কখনো কখনো ইচ্ছাকৃতভাবে ডাটার পুনরাবৃত্তি রাখা যুক্তিসঙ্গত:

-- অর্ডারের সময়ের দাম সংরক্ষণ — এটা denormalization নয়, এটা ঐতিহাসিক সত্য
order_items(order_id, product_id, quantity, unit_price)
-- পণ্যের দাম পরে বদলালেও পুরনো অর্ডারের হিসাব ঠিক থাকবে

-- হিসাব করা count জমিয়ে রাখা
posts(id, title, comment_count)   -- প্রতিবার COUNT(*) করার বদলে

শর্ত: denormalize করলে ডাটা সমন্বিত রাখার দায়িত্ব আপনার — trigger বা অ্যাপ্লিকেশন কোড দিয়ে।

নিয়ম: আগে normalize করুন। পরিমাপ করে যদি সমস্যা পান, তবেই denormalize করুন। অনুমানের ভিত্তিতে নয়।

২৯.৩ সম্পর্কের ধরন

One-to-One:

users(id, email, password_hash)
user_profiles(user_id PK REFERENCES users(id), bio, avatar_url)

One-to-Many (সবচেয়ে সাধারণ):

customers(id, name)
orders(id, customer_id REFERENCES customers(id), ...)

Many-to-Many (junction table লাগে):

students(id, name)
courses(id, title)
enrollments(
    student_id REFERENCES students(id),
    course_id  REFERENCES courses(id),
    enrolled_at DATE,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Junction table-এ অতিরিক্ত কলাম (enrolled_at, grade) রাখা যায় — এটাই এর সৌন্দর্য।

২৯.৪ নামকরণের রীতি

ধারাবাহিকতাই আসল কথা, কিন্তু PostgreSQL সমাজে প্রচলিত রীতি:

-- টেবিল: বহুবচন, snake_case
customers, order_items, product_categories

-- কলাম: একবচন, snake_case
first_name, created_at, is_active

-- Primary key
id

-- Foreign key: <টেবিলের একবচন>_id
customer_id, product_id

-- Boolean: is_ / has_ দিয়ে শুরু
is_active, has_discount

-- সময়: _at দিয়ে শেষ
created_at, updated_at, deleted_at

-- Index: idx_<টেবিল>_<কলাম>
idx_orders_customer_id

-- Constraint
pk_orders, fk_orders_customer, uq_customers_email, chk_products_price

সবসময় lowercase ব্যবহার করুন। PostgreSQL-এ unquoted identifier স্বয়ংক্রিয়ভাবে lowercase হয়ে যায়, কিন্তু "CamelCase" লিখলে সেটা কোট সহ ব্যবহার করতে হয় সারাজীবন — অসহনীয়।

২৯.৫ প্রতিটা টেবিলে যা রাখা উচিত

CREATE TABLE something (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- ... আপনার কলাম ...
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

updated_at স্বয়ংক্রিয় রাখতে একটা সাধারণ trigger:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_something_updated
BEFORE UPDATE ON something
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

২৯.৬ সাধারণ নকশাগত ভুল

সব শেখা একসাথে প্রয়োগ করে দেখা যাক।

৩০.১ পূর্ণাঙ্গ Schema

CREATE DATABASE ecommerce;
\c ecommerce

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- ===== Type =====
CREATE TYPE order_status AS ENUM
    ('pending','confirmed','packed','shipped','delivered','cancelled','returned');
CREATE TYPE payment_status AS ENUM ('unpaid','paid','partially_refunded','refunded');

CREATE DOMAIN email_addr AS CITEXT
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');
CREATE DOMAIN money_amount AS NUMERIC(12,2) CHECK (VALUE >= 0);

-- ===== ব্যবহারকারী =====
CREATE TABLE users (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         email_addr NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    full_name     TEXT NOT NULL,
    phone         VARCHAR(15),
    is_verified   BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at    TIMESTAMPTZ
);

CREATE TABLE addresses (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    label       TEXT,
    line1       TEXT NOT NULL,
    line2       TEXT,
    city        TEXT NOT NULL,
    district    TEXT,
    postcode    VARCHAR(10),
    country     CHAR(2) NOT NULL DEFAULT 'BD',
    is_default  BOOLEAN NOT NULL DEFAULT FALSE
);
-- প্রতি ইউজারের একটাই default ঠিকানা
CREATE UNIQUE INDEX uq_default_address ON addresses(user_id) WHERE is_default;

-- ===== ক্যাটালগ =====
CREATE TABLE categories (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    name      TEXT NOT NULL,
    slug      TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku          TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL,
    slug         TEXT NOT NULL UNIQUE,
    description  TEXT,
    category_id  INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    price        money_amount NOT NULL,
    sale_price   money_amount,
    stock        INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    attributes   JSONB NOT NULL DEFAULT '{}',
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    search_vec   TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name,'')), 'A') ||
        setweight(to_tsvector('simple', coalesce(description,'')), 'B')
    ) STORED,
    CONSTRAINT chk_sale_price CHECK (sale_price IS NULL OR sale_price < price)
);

-- ===== অর্ডার =====
CREATE TABLE orders (
    id             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_number   TEXT NOT NULL UNIQUE,
    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    address_id     INTEGER REFERENCES addresses(id) ON DELETE SET NULL,
    status         order_status NOT NULL DEFAULT 'pending',
    payment_status payment_status NOT NULL DEFAULT 'unpaid',
    subtotal       money_amount NOT NULL DEFAULT 0,
    shipping_fee   money_amount NOT NULL DEFAULT 0,
    discount       money_amount NOT NULL DEFAULT 0,
    total          money_amount NOT NULL DEFAULT 0,
    placed_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    delivered_at   TIMESTAMPTZ,
    notes          TEXT
);

CREATE TABLE order_items (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id     INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id   INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    product_name TEXT NOT NULL,          -- ঐতিহাসিক স্ন্যাপশট
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   money_amount NOT NULL,  -- অর্ডারের সময়ের দাম
    line_total   money_amount GENERATED ALWAYS AS (quantity * unit_price) STORED,
    UNIQUE (order_id, product_id)
);

-- ===== রিভিউ =====
CREATE TABLE reviews (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating     SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title      TEXT,
    body       TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (product_id, user_id)     -- একজন একটাই রিভিউ
);

-- ===== Index =====
CREATE INDEX idx_addresses_user     ON addresses(user_id);
CREATE INDEX idx_products_category  ON products(category_id) WHERE is_active;
CREATE INDEX idx_products_search    ON products USING GIN (search_vec);
CREATE INDEX idx_products_attrs     ON products USING GIN (attributes);
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
CREATE INDEX idx_orders_user_date   ON orders(user_id, placed_at DESC);
CREATE INDEX idx_orders_status      ON orders(status) WHERE status <> 'delivered';
CREATE INDEX idx_items_order        ON order_items(order_id);
CREATE INDEX idx_items_product      ON order_items(product_id);
CREATE INDEX idx_reviews_product    ON reviews(product_id);

৩০.২ ব্যবসায়িক যুক্তি

-- অর্ডার নম্বর তৈরি
CREATE SEQUENCE order_number_seq START 1000;

CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.order_number IS NULL THEN
        NEW.order_number := 'ORD-' || TO_CHAR(now(), 'YYYYMM') || '-'
                            || LPAD(nextval('order_number_seq')::TEXT, 6, '0');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_number
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION generate_order_number();

-- অর্ডারের মোট হিসাব স্বয়ংক্রিয়ভাবে হালনাগাদ
CREATE OR REPLACE FUNCTION recalc_order_total()
RETURNS TRIGGER AS $$
DECLARE v_order_id INT;
BEGIN
    v_order_id := COALESCE(NEW.order_id, OLD.order_id);

    UPDATE orders o
    SET subtotal = COALESCE(s.sum_total, 0),
        total    = COALESCE(s.sum_total, 0) + o.shipping_fee - o.discount
    FROM (
        SELECT SUM(line_total) AS sum_total
        FROM order_items WHERE order_id = v_order_id
    ) s
    WHERE o.id = v_order_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_recalc_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION recalc_order_total();

-- সম্পূর্ণ অর্ডার তৈরির function
CREATE OR REPLACE FUNCTION place_order(
    p_user_id    UUID,
    p_address_id INT,
    p_items      JSONB     -- [{"product_id":1,"quantity":2}, ...]
) RETURNS INT AS $$
DECLARE
    v_order_id INT;
    v_item     JSONB;
    v_product  RECORD;
BEGIN
    INSERT INTO orders (user_id, address_id, shipping_fee)
    VALUES (p_user_id, p_address_id, 60)
    RETURNING id INTO v_order_id;

    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        SELECT id, name, COALESCE(sale_price, price) AS eff_price, stock
        INTO v_product
        FROM products
        WHERE id = (v_item->>'product_id')::INT AND is_active
        FOR UPDATE;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'পণ্য পাওয়া যায়নি: %', v_item->>'product_id';
        END IF;

        IF v_product.stock < (v_item->>'quantity')::INT THEN
            RAISE EXCEPTION 'পর্যাপ্ত স্টক নেই: % (আছে %)',
                  v_product.name, v_product.stock;
        END IF;

        INSERT INTO order_items (order_id, product_id, product_name, quantity, unit_price)
        VALUES (v_order_id, v_product.id, v_product.name,
                (v_item->>'quantity')::INT, v_product.eff_price);

        UPDATE products
        SET stock = stock - (v_item->>'quantity')::INT
        WHERE id = v_product.id;
    END LOOP;

    RETURN v_order_id;
END;
$$ LANGUAGE plpgsql;

ব্যবহার:

SELECT place_order(
    (SELECT id FROM users LIMIT 1),
    1,
    '[{"product_id":1,"quantity":2},{"product_id":3,"quantity":1}]'::jsonb
);

৩০.৩ ব্যবসায়িক প্রশ্নের উত্তর

১. মাসিক আয়ের প্রতিবেদন ও বৃদ্ধির হার

WITH monthly AS (
    SELECT DATE_TRUNC('month', placed_at) AS month,
           COUNT(*) AS orders,
           SUM(total) AS revenue,
           ROUND(AVG(total), 2) AS avg_order_value
    FROM orders
    WHERE status NOT IN ('cancelled','returned')
    GROUP BY 1
)
SELECT
    TO_CHAR(month, 'YYYY Mon') AS period,
    orders, revenue, avg_order_value,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct,
    SUM(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly
ORDER BY month;

২. সেরা গ্রাহক (RFM বিশ্লেষণ)

WITH rfm AS (
    SELECT
        u.id, u.full_name, u.email,
        CURRENT_DATE - MAX(o.placed_at)::DATE AS recency_days,
        COUNT(o.id) AS frequency,
        SUM(o.total) AS monetary
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status NOT IN ('cancelled','returned')
    GROUP BY u.id, u.full_name, u.email
)
SELECT full_name, email, recency_days, frequency, monetary,
       NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
       NTILE(5) OVER (ORDER BY frequency)         AS f_score,
       NTILE(5) OVER (ORDER BY monetary)          AS m_score
FROM rfm
ORDER BY monetary DESC;

৩. পণ্যের কর্মক্ষমতা

SELECT
    p.name, p.sku, p.stock,
    COALESCE(SUM(oi.quantity), 0) AS units_sold,
    COALESCE(SUM(oi.line_total), 0) AS revenue,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(DISTINCT r.id) AS review_count,
    RANK() OVER (ORDER BY COALESCE(SUM(oi.line_total),0) DESC) AS revenue_rank
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN orders o       ON o.id = oi.order_id AND o.status NOT IN ('cancelled','returned')
LEFT JOIN reviews r      ON r.product_id = p.id
GROUP BY p.id, p.name, p.sku, p.stock
ORDER BY revenue DESC;

৪. একসাথে কেনা পণ্য (Market Basket)

SELECT
    p1.name AS product_a,
    p2.name AS product_b,
    COUNT(*) AS bought_together
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id
                     AND oi1.product_id < oi2.product_id
JOIN products p1 ON p1.id = oi1.product_id
JOIN products p2 ON p2.id = oi2.product_id
GROUP BY p1.name, p2.name
HAVING COUNT(*) >= 2
ORDER BY bought_together DESC
LIMIT 20;

oi1.product_id < oi2.product_id শর্তটা একই জোড়া দুবার আসা ঠেকায়।

৫. কার্ট পরিত্যাগ ও নিষ্ক্রিয় গ্রাহক

-- ৯০ দিন কিছু কেনেননি এমন গ্রাহক
SELECT u.full_name, u.email,
       MAX(o.placed_at)::DATE AS last_order,
       CURRENT_DATE - MAX(o.placed_at)::DATE AS days_inactive,
       SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.full_name, u.email
HAVING MAX(o.placed_at) < now() - INTERVAL '90 days'
ORDER BY lifetime_value DESC;

৬. অনুসন্ধান

-- Full-text
SELECT p.name, ts_rank(p.search_vec, q) AS rank
FROM products p, websearch_to_tsquery('simple', 'ওয়্যারলেস মাউস') q
WHERE p.search_vec @@ q AND p.is_active
ORDER BY rank DESC LIMIT 20;

-- Fuzzy (বানান ভুল সহ্য করে)
SELECT name, similarity(name, 'wireles mous') AS sim
FROM products
WHERE name % 'wireles mous'
ORDER BY sim DESC LIMIT 10;

-- JSONB attribute দিয়ে ফিল্টার
SELECT name, price, attributes
FROM products
WHERE attributes @> '{"color":"black"}'
  AND (attributes->>'ram')::INT >= 16;

৩০.৪ Dashboard-এর জন্য Materialized View

CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT
    DATE_TRUNC('day', placed_at)::DATE AS day,
    COUNT(*) AS orders,
    COUNT(DISTINCT user_id) AS unique_customers,
    SUM(total) AS revenue,
    ROUND(AVG(total), 2) AS avg_order_value,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON mv_daily_stats (day);

-- pg_cron দিয়ে প্রতি ঘণ্টায় রিফ্রেশ
SELECT cron.schedule('refresh-daily-stats', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats');

এই অধ্যায়টা রেফারেন্স হিসেবে রাখার জন্য। প্রতিটা রেসিপি একটা বাস্তব সমস্যার সমাধান — কাজ করতে গিয়ে আটকে গেলে এখানে খুঁজুন। সব উদাহরণ অধ্যায় ৭-এর নমুনা shop database-এ চলবে।

ক. ছাঁকা ও তারিখ

১. আজ, গতকাল, এই সপ্তাহ, এই মাস

SELECT * FROM orders WHERE order_date = CURRENT_DATE;
SELECT * FROM orders WHERE order_date = CURRENT_DATE - 1;
SELECT * FROM orders WHERE order_date >= DATE_TRUNC('week', CURRENT_DATE);
SELECT * FROM orders WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE);

২. গত ৩০ দিন (index-বান্ধব উপায়)

-- ✅ কলামে function নেই, index ব্যবহার হবে
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- ❌ এটা index ব্যবহার করবে না
SELECT * FROM orders WHERE CURRENT_DATE - order_date <= 30;

৩. নির্দিষ্ট মাসের ডাটা

SELECT * FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';
-- BETWEEN '2026-03-01' AND '2026-03-31' লিখলে timestamp কলামে
-- ৩১ তারিখের বিকেলের ডাটা বাদ পড়ে যায় — এই ফাঁদটা মনে রাখুন

৪. বয়স ও সময়ের ব্যবধান

SELECT name,
       AGE(CURRENT_DATE, created_at::DATE) AS customer_age,
       EXTRACT(DAY FROM now() - created_at) AS days_since_signup
FROM customers;

৫. কর্মদিবস বাদ দিয়ে গোনা

SELECT COUNT(*) FILTER (WHERE EXTRACT(DOW FROM order_date) NOT IN (5, 6)) AS weekday_orders,
       COUNT(*) FILTER (WHERE EXTRACT(DOW FROM order_date) IN (5, 6))     AS weekend_orders
FROM orders;
-- বাংলাদেশে সাপ্তাহিক ছুটি শুক্র (5) ও শনি (6)

৬. একাধিক ঐচ্ছিক ফিল্টার (dynamic search)

-- অ্যাপ থেকে NULL পাঠালে ওই ফিল্টার উপেক্ষিত হবে
SELECT * FROM products
WHERE ($1::INT  IS NULL OR category_id = $1)
  AND ($2::NUMERIC IS NULL OR price >= $2)
  AND ($3::TEXT IS NULL OR name ILIKE '%' || $3 || '%');

৭. একাধিক মানের যেকোনোটা

SELECT * FROM orders WHERE status = ANY(ARRAY['pending','paid']);
SELECT * FROM orders WHERE status = ANY($1::TEXT[]);   -- অ্যাপ থেকে array পাঠানো

খ. সমষ্টি ও রিপোর্ট

৮. প্রতি অর্ডারের মোট

SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date;

৯. শতাংশ হিসাব

SELECT status,
       COUNT(*) AS cnt,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders GROUP BY status ORDER BY cnt DESC;

১০. খালি মাসসহ সম্পূর্ণ সময়রেখা

SELECT
    TO_CHAR(m.month, 'YYYY-MM') AS month,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM generate_series(
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months',
        DATE_TRUNC('month', CURRENT_DATE),
        INTERVAL '1 month'
     ) AS m(month)
LEFT JOIN orders o       ON DATE_TRUNC('month', o.order_date) = m.month
                        AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY m.month ORDER BY m.month;

১১. Pivot — মাস বনাম ক্যাটাগরি

SELECT
    TO_CHAR(o.order_date, 'YYYY-MM') AS month,
    SUM(oi.quantity * oi.unit_price) FILTER (WHERE c.name = 'Electronics') AS electronics,
    SUM(oi.quantity * oi.unit_price) FILTER (WHERE c.name = 'Books')       AS books,
    SUM(oi.quantity * oi.unit_price) FILTER (WHERE c.name = 'Clothing')    AS clothing,
    SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories c   ON c.id = p.category_id
GROUP BY 1 ORDER BY 1;

১২. উপ-মোট ও সর্বমোট একসাথে

SELECT COALESCE(c.city, '— সর্বমোট —') AS city,
       COALESCE(o.status::TEXT, 'সব') AS status,
       COUNT(*) AS orders
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY ROLLUP (c.city, o.status)
ORDER BY c.city NULLS LAST, o.status NULLS LAST;

১৩. গড়, মধ্যক ও ৯৫তম পার্সেন্টাইল

SELECT
    ROUND(AVG(price), 2) AS mean,
    PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY price) AS median,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY price) AS p95
FROM products;

১৪. কম স্টকের সতর্কতা

SELECT p.name, p.stock,
       COALESCE(SUM(oi.quantity), 0) AS sold_last_30d,
       CASE WHEN COALESCE(SUM(oi.quantity),0) = 0 THEN NULL
            ELSE ROUND(p.stock / (SUM(oi.quantity) / 30.0), 1)
       END AS days_of_stock_left
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN orders o ON o.id = oi.order_id AND o.order_date >= CURRENT_DATE - 30
GROUP BY p.id, p.name, p.stock
HAVING p.stock < 50
ORDER BY days_of_stock_left NULLS LAST;

গ. র‍্যাঙ্কিং ও Window

১৫. প্রতি ক্যাটাগরিতে সবচেয়ে দামি পণ্য

SELECT DISTINCT ON (category_id) category_id, name, price
FROM products ORDER BY category_id, price DESC;

১৬. প্রতি ক্যাটাগরিতে টপ ৩

SELECT * FROM (
    SELECT name, category_id, price,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
    FROM products
) t WHERE rn <= 3;

১৭. চলমান যোগফল ও ৭ দিনের চলমান গড়

WITH daily AS (
    SELECT o.order_date AS d, SUM(oi.quantity * oi.unit_price) AS rev
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    GROUP BY 1
)
SELECT d, rev,
       SUM(rev) OVER (ORDER BY d) AS running_total,
       ROUND(AVG(rev) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7
FROM daily ORDER BY d;

১৮. আগের সারির সাথে তুলনা

SELECT d, rev,
       LAG(rev) OVER (ORDER BY d) AS prev,
       rev - LAG(rev) OVER (ORDER BY d) AS diff
FROM daily_revenue;

১৯. প্রথম ও শেষ মান

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       MAX(order_date) - MIN(order_date) AS lifespan_days,
       COUNT(*) AS total_orders
FROM orders GROUP BY customer_id;

২০. পুনরাবৃত্ত গ্রাহক চিহ্নিত করা

SELECT customer_id,
       COUNT(*) AS orders,
       CASE WHEN COUNT(*) = 1 THEN 'একবারের ক্রেতা'
            WHEN COUNT(*) <= 3 THEN 'নিয়মিত'
            ELSE 'অনুগত' END AS segment
FROM orders WHERE status <> 'cancelled'
GROUP BY customer_id;

ঘ. ডুপ্লিকেট ও ডাটা পরিষ্কার

২১. ডুপ্লিকেট খুঁজে বের করা

SELECT email, COUNT(*), ARRAY_AGG(id ORDER BY id) AS ids
FROM customers GROUP BY email HAVING COUNT(*) > 1;

২২. ডুপ্লিকেট মোছা (সবচেয়ে পুরনোটা রেখে)

DELETE FROM customers a
USING customers b
WHERE a.email = b.email AND a.id > b.id;

২৩. window দিয়ে ডুপ্লিকেট মোছা (আরও নিয়ন্ত্রিত)

WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM customers
)
DELETE FROM customers WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

২৪. খালি স্ট্রিংকে NULL বানানো

UPDATE customers SET phone = NULL WHERE TRIM(phone) = '';
UPDATE customers SET city = NULLIF(TRIM(city), '');

২৫. টেক্সট পরিষ্কার করা

UPDATE customers SET
    name  = INITCAP(TRIM(REGEXP_REPLACE(name, '\s+', ' ', 'g'))),
    email = LOWER(TRIM(email)),
    phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g');

২৬. অসঙ্গত ডাটা খুঁজে বের করা

-- অনাথ রেকর্ড (FK ছাড়া টেবিলে)
SELECT oi.* FROM order_items oi
LEFT JOIN products p ON p.id = oi.product_id
WHERE p.id IS NULL;

-- ভুল ইমেইল
SELECT * FROM customers WHERE email !~ '^[^@\s]+@[^@\s]+\.[^@\s]+$';

-- ভবিষ্যতের তারিখ
SELECT * FROM orders WHERE order_date > CURRENT_DATE;

ঙ. Gaps and Islands (ধারাবাহিকতা বিশ্লেষণ)

২৭. অনুপস্থিত তারিখ খুঁজে বের করা

SELECT d::DATE AS missing_date
FROM generate_series('2026-01-01'::DATE, '2026-03-31'::DATE, '1 day') d
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_date = d::DATE);

২৮. অনুপস্থিত ID খুঁজে বের করা

SELECT s.i AS missing_id
FROM generate_series(1, (SELECT MAX(id) FROM products)) s(i)
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.id = s.i);

২৯. টানা কতদিন বিক্রি হয়েছে (islands)

WITH d AS (
    SELECT DISTINCT order_date FROM orders
),
grouped AS (
    SELECT order_date,
           order_date - (ROW_NUMBER() OVER (ORDER BY order_date))::INT AS grp
    FROM d
)
SELECT MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*) AS days
FROM grouped GROUP BY grp
ORDER BY days DESC;

তারিখ থেকে ক্রমিক নম্বর বিয়োগ করলে ধারাবাহিক তারিখগুলোর জন্য একই মান পাওয়া যায় — এটাই "islands" কৌশলের মূল চাল।

চ. স্তরবিন্যাস ও গাছ

৩০. নেস্টেড ক্যাটাগরি সম্পূর্ণ পথসহ

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 1 AS depth, name::TEXT AS path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' / ' || c.name
    FROM categories c JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 10
)
SELECT REPEAT('— ', depth - 1) || name AS tree, path, depth
FROM tree ORDER BY path;

৩১. একটা ক্যাটাগরি ও তার সব উপ-ক্যাটাগরির পণ্য

WITH RECURSIVE sub AS (
    SELECT id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id FROM categories c JOIN sub s ON c.parent_id = s.id
)
SELECT p.* FROM products p WHERE p.category_id IN (SELECT id FROM sub);

ছ. JSON ও Text

৩২. JSON থেকে রিপোর্ট

SELECT
    attributes ->> 'brand' AS brand,
    COUNT(*) AS products,
    ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE attributes ? 'brand'
GROUP BY 1 ORDER BY products DESC;

৩৩. JSON array-র ভেতর খোঁজা

SELECT * FROM products WHERE attributes -> 'tags' @> '["sale"]';
SELECT * FROM products WHERE attributes @> '{"specs": {"ram": 16}}';

৩৪. Row কে JSON API response বানানো

SELECT jsonb_build_object(
    'id', o.id,
    'date', o.order_date,
    'customer', jsonb_build_object('name', c.name, 'email', c.email),
    'items', (
        SELECT jsonb_agg(jsonb_build_object(
            'product', p.name, 'qty', oi.quantity, 'price', oi.unit_price))
        FROM order_items oi JOIN products p ON p.id = oi.product_id
        WHERE oi.order_id = o.id
    )
) AS order_json
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.id = 1;

এই একটা query দিয়ে সম্পূর্ণ nested API response তৈরি — অ্যাপ্লিকেশনে আর জোড়া লাগাতে হবে না।

৩৫. টেক্সট থেকে সংখ্যা বের করা

SELECT REGEXP_REPLACE('অর্ডার #১২৩৪ মোট ৳৫০০', '[^0-9]', '', 'g');
SELECT (REGEXP_MATCH('SKU-4521-BLK', '(\d+)'))[1]::INT;

৩৬. কমা-আলাদা তালিকা ভাঙা

SELECT id, TRIM(tag) AS tag
FROM posts, UNNEST(STRING_TO_ARRAY(tag_csv, ',')) AS tag;

৩৭. বানান ভুল সহ্য করে খোঁজা

SELECT name, similarity(name, 'labtop del') AS score
FROM products
WHERE name % 'labtop del'
ORDER BY score DESC LIMIT 5;

জ. আপডেট ও ব্যাচ অপারেশন

৩৮. অন্য টেবিল থেকে আপডেট

UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE oi.product_id = p.id AND oi.order_id IN (SELECT id FROM orders WHERE status='pending');

৩৯. একসাথে অনেক row ভিন্ন ভিন্ন মানে আপডেট

UPDATE products p
SET stock = v.new_stock
FROM (VALUES (1, 50), (2, 200), (3, 75)) AS v(id, new_stock)
WHERE p.id = v.id;

৪০. ব্যাচে মোছা (lock ছোট রাখতে)

-- একবারে ১০ লাখ মোছার বদলে ধাপে ধাপে
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < now() - INTERVAL '1 year' LIMIT 10000);
-- এটা লুপে চালান যতক্ষণ না ০ row প্রভাবিত হয়

৪১. দুই টেবিল সমন্বয় (upsert bulk)

INSERT INTO products (sku, name, price, stock)
SELECT sku, name, price, stock FROM staging_products
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

ঝ. প্রশাসনিক

৪২. সবচেয়ে বড় টেবিল

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS data,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes,
       n_live_tup AS rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

৪৩. অব্যবহৃত index

SELECT indexrelname, relname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint WHERE contype IN ('p','u')
)
ORDER BY pg_relation_size(indexrelid) DESC;

৪৪. অনুপস্থিত foreign key index

SELECT c.conrelid::regclass AS table_name, a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
  );

এই query-টা যেকোনো নতুন প্রজেক্টে একবার চালিয়ে দেখুন — প্রায় সবসময়ই কিছু না কিছু পাওয়া যায়।

৪৫. দীর্ঘতম চলমান query

SELECT pid, usename, state,
       now() - query_start AS duration,
       LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

৪৬. lock-এ আটকে থাকা query

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

৪৭. Cache hit ratio (৯৯%-এর নিচে হলে RAM বাড়ানোর কথা ভাবুন)

SELECT
    ROUND(100.0 * SUM(heap_blks_hit) /
          NULLIF(SUM(heap_blks_hit) + SUM(heap_blks_read), 0), 2) AS cache_hit_pct
FROM pg_statio_user_tables;

৪৮. টেবিলের সব constraint দেখা

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint WHERE conrelid = 'orders'::regclass;

৪৯. Sequence পুনঃস্থাপন (ডাটা import-এর পরে অপরিহার্য)

SELECT setval(
    pg_get_serial_sequence('customers', 'id'),
    COALESCE((SELECT MAX(id) FROM customers), 1)
);

INSERT করার সময় হাতে id দিলে sequence পিছিয়ে থাকে — পরে "duplicate key" error আসে। Import-এর পরে সব টেবিলে এটা চালিয়ে নিন।

৫০. টেবিলের সম্পূর্ণ DDL বের করা

pg_dump -U postgres -d shop --schema-only -t products

এই অধ্যায় থেকে আমরা চারটা পূর্ণাঙ্গ প্রজেক্ট বানাব। প্রতিটার জন্য একই ভিত্তি লাগবে, তাই সেটা একবারে সেট করে নিই।

৩২.১ Docker Compose

docker-compose.yml:

services:
  db:
    image: postgres:16-alpine
    container_name: pg_dev
    restart: unless-stopped
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: apppass
      POSTGRES_DB: appdb
      # বাংলা টেক্সটের জন্য
      LANG: C.UTF-8
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      # এই ফোল্ডারের .sql ফাইল প্রথমবার চালু হওয়ার সময় নিজে থেকেই চলবে
      - ./db/init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser -d appdb"]
      interval: 5s
      timeout: 5s
      retries: 5

  adminer:
    image: adminer:latest
    restart: unless-stopped
    ports:
      - "8080:8080"
    depends_on:
      db:
        condition: service_healthy

volumes:
  pgdata:

docker compose up -d
docker compose logs -f db
docker compose exec db psql -U appuser -d appdb

http://localhost:8080 খুললে Adminer — হালকা ওজনের database GUI।

৩২.২ Migration ফাইল ব্যবস্থাপনা

প্রতিটা schema পরিবর্তন একটা করে নম্বরযুক্ত ফাইলে থাকবে, এবং কোনদিন কোনটা চলেছে সেটা database নিজেই মনে রাখবে।

db/migrations/001_init.sql:

-- migrate:up
CREATE TABLE schema_migrations (
    version    TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;

একটা সহজ migration runner (Node.js):

db/migrate.js:

import fs from 'node:fs';
import path from 'node:path';
import pg from 'pg';

const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

await client.query(`
  CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
  )`);

const { rows } = await client.query('SELECT version FROM schema_migrations');
const applied = new Set(rows.map(r => r.version));

const dir = path.join(process.cwd(), 'db/migrations');
const files = fs.readdirSync(dir).filter(f => f.endsWith('.sql')).sort();

for (const file of files) {
  if (applied.has(file)) continue;

  const sql = fs.readFileSync(path.join(dir, file), 'utf8');
  console.log(`▶  চালাচ্ছি ${file}`);

  try {
    await client.query('BEGIN');
    await client.query(sql);
    await client.query('INSERT INTO schema_migrations (version) VALUES ($1)', [file]);
    await client.query('COMMIT');
    console.log(`✔  সম্পন্ন ${file}`);
  } catch (err) {
    await client.query('ROLLBACK');
    console.error(`✘  ব্যর্থ ${file}: ${err.message}`);
    process.exit(1);
  }
}

await client.end();
console.log('সব migration হালনাগাদ।');

প্রতিটা migration একটা transaction-এ চলছে — মাঝপথে ব্যর্থ হলে অর্ধেক প্রয়োগ হয়ে থাকবে না। এটাই সবচেয়ে গুরুত্বপূর্ণ অংশ।

node db/migrate.js

বাস্তব প্রজেক্টে node-pg-migrate, Flyway, dbmate বা Alembic ব্যবহার করুন — এগুলো rollback, checksum যাচাই, dry-run সব দেয়।

৩২.৩ Seed ডাটা

db/seed.sql:

BEGIN;

TRUNCATE order_items, orders, products, categories, users RESTART IDENTITY CASCADE;

INSERT INTO categories (name, slug) VALUES
('ইলেকট্রনিক্স', 'electronics'),
('বই', 'books'),
('পোশাক', 'clothing');

-- generate_series দিয়ে দ্রুত অনেক নমুনা ডাটা
INSERT INTO products (sku, name, category_id, price, stock)
SELECT
    'SKU-' || LPAD(i::TEXT, 5, '0'),
    (ARRAY['ল্যাপটপ','মাউস','কিবোর্ড','মনিটর','হেডফোন'])[1 + (i % 5)] || ' মডেল ' || i,
    1 + (i % 3),
    ROUND((random() * 20000 + 500)::NUMERIC, 2),
    (random() * 200)::INT
FROM generate_series(1, 500) AS i;

COMMIT;
ANALYZE;

generate_series দিয়ে হাজার হাজার নমুনা row বানানো যায় — performance পরীক্ষার জন্য অপরিহার্য। ১০ row-এর টেবিলে index পরীক্ষা করে কোনো লাভ নেই।

১০ লাখ row বানিয়ে index পরীক্ষা:

INSERT INTO events (user_id, event_type, created_at)
SELECT
    (random() * 10000)::INT,
    (ARRAY['view','click','purchase'])[1 + (random()*2)::INT],
    now() - (random() * 365 || ' days')::INTERVAL
FROM generate_series(1, 1000000);

ANALYZE events;

EXPLAIN ANALYZE SELECT * FROM events WHERE user_id = 42;
-- index ছাড়া: Seq Scan, ~২০০ ms
CREATE INDEX idx_events_user ON events(user_id);
-- index সহ: Index Scan, ~০.৫ ms

এই পার্থক্যটা নিজের চোখে দেখা জরুরি — তখনই index-এর গুরুত্ব সত্যিকারভাবে বোঝা যায়।

একটা কাজ ব্যবস্থাপনা (task manager) API বানাব — ইউজার, প্রজেক্ট, টাস্ক, কমেন্ট।

৩৩.১ প্রজেক্টের গঠন

task-api/
├── docker-compose.yml
├── .env
├── package.json
├── db/
│   ├── migrations/
│   │   ├── 001_init.sql
│   │   ├── 002_tasks.sql
│   │   └── 003_indexes.sql
│   ├── migrate.js
│   └── seed.sql
└── src/
    ├── db.js
    ├── server.js
    ├── routes/
    │   ├── projects.js
    │   └── tasks.js
    └── middleware/
        └── errors.js

mkdir task-api && cd task-api
npm init -y
npm install express pg dotenv zod
npm install --save-dev nodemon

package.json-এ যোগ করুন:

{
  "type": "module",
  "scripts": {
    "dev": "nodemon src/server.js",
    "migrate": "node db/migrate.js",
    "seed": "psql $DATABASE_URL -f db/seed.sql"
  }
}

.env:

DATABASE_URL=postgresql://appuser:apppass@localhost:5432/appdb
PORT=3000

৩৩.২ Schema

db/migrations/002_tasks.sql:

CREATE TYPE task_status   AS ENUM ('todo','in_progress','review','done','archived');
CREATE TYPE task_priority AS ENUM ('low','medium','high','urgent');

CREATE TABLE users (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         CITEXT NOT NULL UNIQUE,
    full_name     TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT,
    is_archived BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (owner_id, name)
);

CREATE TABLE tasks (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id   INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    assignee_id  UUID REFERENCES users(id) ON DELETE SET NULL,
    title        TEXT NOT NULL CHECK (length(trim(title)) > 0),
    description  TEXT,
    status       task_status   NOT NULL DEFAULT 'todo',
    priority     task_priority NOT NULL DEFAULT 'medium',
    due_date     DATE,
    position     INTEGER NOT NULL DEFAULT 0,
    labels       TEXT[] NOT NULL DEFAULT '{}',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ,
    CONSTRAINT chk_completed CHECK (
        (status = 'done' AND completed_at IS NOT NULL) OR
        (status <> 'done' AND completed_at IS NULL)
    )
);

CREATE TABLE comments (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_id    INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id  UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

শেষ CHECK constraint-টা লক্ষ করুন — এটা নিশ্চিত করে যে status = 'done' আর completed_at কখনোই অসঙ্গত থাকতে পারবে না। অ্যাপ্লিকেশনে বাগ থাকলেও database ভুল অবস্থা মেনে নেবে না।

db/migrations/003_indexes.sql:

-- Foreign key index
CREATE INDEX idx_projects_owner   ON projects(owner_id);
CREATE INDEX idx_tasks_project    ON tasks(project_id);
CREATE INDEX idx_tasks_assignee   ON tasks(assignee_id);
CREATE INDEX idx_comments_task    ON comments(task_id);

-- সবচেয়ে সাধারণ query: একটা প্রজেক্টের অসমাপ্ত টাস্ক, ক্রম অনুযায়ী
CREATE INDEX idx_tasks_board ON tasks(project_id, status, position)
    WHERE status <> 'archived';

-- বকেয়া টাস্ক
CREATE INDEX idx_tasks_due ON tasks(due_date)
    WHERE status NOT IN ('done','archived') AND due_date IS NOT NULL;

-- Label দিয়ে খোঁজা
CREATE INDEX idx_tasks_labels ON tasks USING GIN (labels);

-- শিরোনামে fuzzy search
CREATE INDEX idx_tasks_title_trgm ON tasks USING GIN (title gin_trgm_ops);

-- updated_at স্বয়ংক্রিয়
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_tasks_updated BEFORE UPDATE ON tasks
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER trg_projects_updated BEFORE UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- status বদলালে completed_at ঠিক করা
CREATE OR REPLACE FUNCTION sync_completed_at() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'done' AND OLD.status <> 'done' THEN
        NEW.completed_at := now();
    ELSIF NEW.status <> 'done' THEN
        NEW.completed_at := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_tasks_completed BEFORE UPDATE ON tasks
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION sync_completed_at();

৩৩.৩ Database স্তর

src/db.js:

import pg from 'pg';
import 'dotenv/config';

// NUMERIC কে JS এ string হিসেবে আসে (নির্ভুলতা রক্ষার জন্য)।
// টাকার হিসাবে এটাই ঠিক — কিন্তু জানা থাকা দরকার।
pg.types.setTypeParser(pg.types.builtins.NUMERIC, (v) => v === null ? null : Number(v));

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
  // প্রতিটা connection এ statement timeout — আটকে থাকা query ঠেকায়
  statement_timeout: 10_000,
});

pool.on('error', (err) => {
  console.error('Pool এ অপ্রত্যাশিত ভুল:', err);
});

// ছোট helper — query + সময় মাপা
export async function query(text, params) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const ms = Date.now() - start;
  if (ms > 200) console.warn(`ধীর query (${ms}ms):`, text.slice(0, 80));
  return res;
}

// Transaction helper — সবচেয়ে বেশি ব্যবহৃত প্যাটার্ন
export async function withTransaction(fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

withTransaction helper-টা খুব গুরুত্বপূর্ণ — client.release() finally-তে থাকায় ভুল হলেও connection ফাঁস হবে না। এই একটা ভুলে বহু production অ্যাপ মাঝরাতে বন্ধ হয়ে গেছে।

৩৩.৪ Route — Task

src/routes/tasks.js:

import { Router } from 'express';
import { z } from 'zod';
import { query, withTransaction } from '../db.js';

export const tasks = Router();

const CreateTask = z.object({
  project_id: z.number().int().positive(),
  title: z.string().min(1).max(200),
  description: z.string().optional(),
  priority: z.enum(['low','medium','high','urgent']).default('medium'),
  due_date: z.string().date().optional(),
  labels: z.array(z.string()).default([]),
  assignee_id: z.string().uuid().optional(),
});

// ── তালিকা: ফিল্টার + keyset pagination ──────────────────────
tasks.get('/', async (req, res, next) => {
  try {
    const { project_id, status, label, search, cursor, limit = 20 } = req.query;

    const { rows } = await query(
      `SELECT
         t.id, t.title, t.status, t.priority, t.due_date, t.labels,
         t.created_at,
         u.full_name AS assignee_name,
         COUNT(c.id) AS comment_count
       FROM tasks t
       LEFT JOIN users u    ON u.id = t.assignee_id
       LEFT JOIN comments c ON c.task_id = t.id
       WHERE t.status <> 'archived'
         AND ($1::INT  IS NULL OR t.project_id = $1)
         AND ($2::TEXT IS NULL OR t.status::TEXT = $2)
         AND ($3::TEXT IS NULL OR t.labels @> ARRAY[$3])
         AND ($4::TEXT IS NULL OR t.title ILIKE '%' || $4 || '%')
         AND ($5::INT  IS NULL OR t.id < $5)
       GROUP BY t.id, u.full_name
       ORDER BY t.id DESC
       LIMIT $6`,
      [project_id ?? null, status ?? null, label ?? null,
       search ?? null, cursor ?? null, Math.min(Number(limit), 100)]
    );

    res.json({
      data: rows,
      next_cursor: rows.length ? rows[rows.length - 1].id : null,
    });
  } catch (err) { next(err); }
});

লক্ষ করুন — ঐচ্ছিক ফিল্টারগুলো $1::INT IS NULL OR ... প্যাটার্নে। এতে একটাই prepared statement লাগে, string concatenation করতে হয় না, তাই SQL injection-এর সুযোগও নেই।

Pagination OFFSET নয়, id < cursor — keyset pagination। ১০ লাখ টাস্কেও গতি একই থাকবে।

// ── একটা টাস্ক, কমেন্টসহ (একটাই query) ────────────────────────
tasks.get('/:id', async (req, res, next) => {
  try {
    const { rows } = await query(
      `SELECT
         t.*,
         jsonb_build_object('id', u.id, 'name', u.full_name) AS assignee,
         COALESCE((
           SELECT jsonb_agg(jsonb_build_object(
                    'id', c.id, 'body', c.body,
                    'author', a.full_name, 'created_at', c.created_at)
                  ORDER BY c.created_at)
           FROM comments c JOIN users a ON a.id = c.author_id
           WHERE c.task_id = t.id
         ), '[]'::jsonb) AS comments
       FROM tasks t
       LEFT JOIN users u ON u.id = t.assignee_id
       WHERE t.id = $1`,
      [req.params.id]
    );

    if (!rows.length) return res.status(404).json({ error: 'টাস্ক পাওয়া যায়নি' });
    res.json(rows[0]);
  } catch (err) { next(err); }
});

jsonb_agg দিয়ে nested কমেন্ট একই query-তে আসছে — N+1 সমস্যা সম্পূর্ণ এড়ানো গেল।

// ── তৈরি ─────────────────────────────────────────────────────
tasks.post('/', async (req, res, next) => {
  try {
    const input = CreateTask.parse(req.body);

    const { rows } = await query(
      `INSERT INTO tasks
         (project_id, title, description, priority, due_date, labels, assignee_id, position)
       VALUES ($1, $2, $3, $4, $5, $6, $7,
               COALESCE((SELECT MAX(position) + 1 FROM tasks WHERE project_id = $1), 0))
       RETURNING *`,
      [input.project_id, input.title, input.description ?? null,
       input.priority, input.due_date ?? null, input.labels,
       input.assignee_id ?? null]
    );

    res.status(201).json(rows[0]);
  } catch (err) { next(err); }
});

// ── আংশিক আপডেট (PATCH) ──────────────────────────────────────
tasks.patch('/:id', async (req, res, next) => {
  try {
    const allowed = ['title','description','status','priority','due_date','labels','assignee_id'];
    const fields = Object.keys(req.body).filter(k => allowed.includes(k));
    if (!fields.length) return res.status(400).json({ error: 'আপডেট করার মতো কিছু নেই' });

    // $2, $3... করে গতিশীলভাবে SET তৈরি — নামগুলো whitelist থেকে আসছে
    const setClause = fields.map((f, i) => `${f} = $${i + 2}`).join(', ');
    const values = fields.map(f => req.body[f]);

    const { rows } = await query(
      `UPDATE tasks SET ${setClause} WHERE id = $1 RETURNING *`,
      [req.params.id, ...values]
    );

    if (!rows.length) return res.status(404).json({ error: 'টাস্ক পাওয়া যায়নি' });
    res.json(rows[0]);
  } catch (err) { next(err); }
});

কলামের নাম whitelist (allowed) থেকে নেওয়া হচ্ছে — ব্যবহারকারীর ইনপুট সরাসরি SQL-এ যাচ্ছে না। মান সবসময় parameter হিসেবে যাচ্ছে। এই দুটো নিয়ম মানলে dynamic SQL নিরাপদ।

// ── Kanban board এ টাস্ক সরানো (transaction) ─────────────────
tasks.post('/:id/move', async (req, res, next) => {
  try {
    const { status, position } = req.body;

    const result = await withTransaction(async (client) => {
      // পুরনো অবস্থা লক করে নিন
      const { rows: [task] } = await client.query(
        'SELECT project_id, status, position FROM tasks WHERE id = $1 FOR UPDATE',
        [req.params.id]
      );
      if (!task) return null;

      // পুরনো কলামে ফাঁক পূরণ
      await client.query(
        `UPDATE tasks SET position = position - 1
         WHERE project_id = $1 AND status = $2 AND position > $3`,
        [task.project_id, task.status, task.position]
      );

      // নতুন কলামে জায়গা তৈরি
      await client.query(
        `UPDATE tasks SET position = position + 1
         WHERE project_id = $1 AND status = $2 AND position >= $3`,
        [task.project_id, status, position]
      );

      const { rows } = await client.query(
        'UPDATE tasks SET status = $2, position = $3 WHERE id = $1 RETURNING *',
        [req.params.id, status, position]
      );
      return rows[0];
    });

    if (!result) return res.status(404).json({ error: 'টাস্ক পাওয়া যায়নি' });
    res.json(result);
  } catch (err) { next(err); }
});

তিনটা UPDATE একসাথে সফল হবে, নয়তো একটাও না। মাঝপথে সার্ভার ক্র্যাশ করলেও board-এর ক্রম এলোমেলো হবে না।

৩৩.৫ পরিসংখ্যান Endpoint

tasks.get('/stats/:projectId', async (req, res, next) => {
  try {
    const { rows: [stats] } = await query(
      `SELECT
         COUNT(*)                                        AS total,
         COUNT(*) FILTER (WHERE status = 'done')         AS done,
         COUNT(*) FILTER (WHERE status = 'in_progress')  AS in_progress,
         COUNT(*) FILTER (WHERE status = 'todo')         AS todo,
         COUNT(*) FILTER (WHERE due_date < CURRENT_DATE
                           AND status <> 'done')         AS overdue,
         ROUND(AVG(EXTRACT(EPOCH FROM (completed_at - created_at)) / 86400)
               FILTER (WHERE status = 'done'), 1)        AS avg_days_to_complete,
         COUNT(DISTINCT assignee_id)                     AS contributors
       FROM tasks
       WHERE project_id = $1 AND status <> 'archived'`,
      [req.params.projectId]
    );

    const { rows: burndown } = await query(
      `SELECT d::DATE AS day,
              COUNT(t.id) FILTER (WHERE t.completed_at::DATE = d::DATE) AS completed
       FROM generate_series(CURRENT_DATE - 29, CURRENT_DATE, '1 day') d
       LEFT JOIN tasks t ON t.project_id = $1
       GROUP BY d ORDER BY d`,
      [req.params.projectId]
    );

    res.json({ stats, burndown });
  } catch (err) { next(err); }
});

৩৩.৬ Error সামলানো

src/middleware/errors.js:

import { ZodError } from 'zod';

// PostgreSQL error কোড → HTTP status + বাংলা বার্তা
const PG_ERRORS = {
  '23505': { status: 409, message: 'এই মান আগে থেকেই আছে' },              // unique_violation
  '23503': { status: 400, message: 'সম্পর্কিত রেকর্ড পাওয়া যায়নি' },      // foreign_key_violation
  '23502': { status: 400, message: 'আবশ্যক ফিল্ড খালি রাখা যাবে না' },     // not_null_violation
  '23514': { status: 400, message: 'ডাটা বৈধতার শর্ত পূরণ করছে না' },      // check_violation
  '22P02': { status: 400, message: 'ইনপুটের ধরন ভুল' },                    // invalid_text_representation
  '40001': { status: 409, message: 'একসাথে দুটি পরিবর্তনে সংঘর্ষ, আবার চেষ্টা করুন' },
  '40P01': { status: 409, message: 'Deadlock, আবার চেষ্টা করুন' },
  '57014': { status: 504, message: 'অনুরোধে সময় বেশি লাগছে' },            // query_canceled
};

export function errorHandler(err, req, res, next) {
  if (err instanceof ZodError) {
    return res.status(400).json({ error: 'ইনপুট বৈধ নয়', details: err.issues });
  }

  const mapped = PG_ERRORS[err.code];
  if (mapped) {
    return res.status(mapped.status).json({
      error: mapped.message,
      // constraint এর নাম দিলে ক্লায়েন্ট বুঝতে পারে কোন ফিল্ডে সমস্যা
      constraint: err.constraint,
    });
  }

  console.error(err);
  res.status(500).json({ error: 'সার্ভারে সমস্যা হয়েছে' });
}

PostgreSQL-এর error কোডগুলো standard ও স্থায়ী — বার্তার টেক্সট পড়ে সিদ্ধান্ত নেওয়ার বদলে কোড দেখে সিদ্ধান্ত নিন। কোডের পূর্ণ তালিকা অফিসিয়াল ডকুমেন্টেশনের "PostgreSQL Error Codes" অধ্যায়ে।

৩৩.৭ Server

src/server.js:

import express from 'express';
import 'dotenv/config';
import { pool } from './db.js';
import { tasks } from './routes/tasks.js';
import { errorHandler } from './middleware/errors.js';

const app = express();
app.use(express.json());

app.get('/health', async (req, res) => {
  try {
    await pool.query('SELECT 1');
    res.json({ status: 'ok', db: 'connected' });
  } catch {
    res.status(503).json({ status: 'error', db: 'disconnected' });
  }
});

app.use('/api/tasks', tasks);
app.use(errorHandler);

const server = app.listen(process.env.PORT ?? 3000, () =>
  console.log(`API চলছে http://localhost:${process.env.PORT ?? 3000}`)
);

// সুশৃঙ্খলভাবে বন্ধ করা — চলমান request শেষ হতে দেওয়া
for (const signal of ['SIGTERM', 'SIGINT']) {
  process.on(signal, async () => {
    console.log('বন্ধ করা হচ্ছে...');
    server.close(async () => {
      await pool.end();
      process.exit(0);
    });
  });
}

pool.end() ছাড়া প্রসেস বন্ধ করলে PostgreSQL-এ connection ঝুলে থাকে। Container পরিবেশে (Kubernetes, ECS) এটা বিশেষভাবে গুরুত্বপূর্ণ।

৩৩.৮ পরীক্ষা

curl -X POST http://localhost:3000/api/tasks \
  -H 'Content-Type: application/json' \
  -d '{"project_id":1,"title":"Database schema ডিজাইন","priority":"high","labels":["backend","db"]}'

curl 'http://localhost:3000/api/tasks?project_id=1&status=todo&limit=10'
curl 'http://localhost:3000/api/tasks?label=backend'
curl -X PATCH http://localhost:3000/api/tasks/1 \
  -H 'Content-Type: application/json' -d '{"status":"done"}'
curl http://localhost:3000/api/tasks/stats/1

একটা গুদাম ব্যবস্থাপনা API — পণ্য, স্টক চলাচল, সরবরাহকারী, কম-স্টক সতর্কতা।

৩৪.১ সেটআপ

mkdir inventory-api && cd inventory-api
python3 -m venv .venv && source .venv/bin/activate
pip install "fastapi[standard]" "psycopg[binary,pool]" pydantic-settings

inventory-api/
├── .env
├── db/
│   ├── 001_schema.sql
│   └── 002_functions.sql
└── app/
    ├── config.py
    ├── database.py
    ├── models.py
    ├── main.py
    └── routers/
        ├── products.py
        └── stock.py

৩৪.২ Schema — Event Sourcing ধাঁচে স্টক

স্টকের বর্তমান পরিমাণ একটা কলামে রাখার বদলে প্রতিটা চলাচল রেকর্ড করব। এতে পূর্ণ ইতিহাস থাকে এবং "স্টক কেন কমল" প্রশ্নের উত্তর পাওয়া যায়।

db/001_schema.sql:

CREATE TYPE movement_type AS ENUM
    ('purchase','sale','return','adjustment','damage','transfer');

CREATE TABLE suppliers (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL UNIQUE,
    contact    TEXT,
    phone      VARCHAR(15),
    email      CITEXT,
    is_active  BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE warehouses (
    id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code    VARCHAR(10) NOT NULL UNIQUE,
    name    TEXT NOT NULL,
    address TEXT
);

CREATE TABLE products (
    id             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku            TEXT NOT NULL UNIQUE,
    name           TEXT NOT NULL,
    supplier_id    INTEGER REFERENCES suppliers(id) ON DELETE SET NULL,
    unit           TEXT NOT NULL DEFAULT 'pcs',
    cost_price     NUMERIC(12,2) NOT NULL CHECK (cost_price >= 0),
    selling_price  NUMERIC(12,2) NOT NULL CHECK (selling_price >= 0),
    reorder_level  INTEGER NOT NULL DEFAULT 10,
    attributes     JSONB NOT NULL DEFAULT '{}',
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_margin CHECK (selling_price >= cost_price)
);

-- প্রতিটা স্টক পরিবর্তনের অপরিবর্তনীয় রেকর্ড
CREATE TABLE stock_movements (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    warehouse_id  INTEGER NOT NULL REFERENCES warehouses(id) ON DELETE RESTRICT,
    movement_type movement_type NOT NULL,
    -- ধনাত্মক = ঢুকছে, ঋণাত্মক = বেরোচ্ছে
    quantity      INTEGER NOT NULL CHECK (quantity <> 0),
    unit_cost     NUMERIC(12,2),
    reference     TEXT,
    note          TEXT,
    created_by    TEXT NOT NULL DEFAULT current_user,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_movements_product   ON stock_movements(product_id, created_at DESC);
CREATE INDEX idx_movements_warehouse ON stock_movements(warehouse_id);
CREATE INDEX idx_movements_date      ON stock_movements(created_at DESC);
CREATE INDEX idx_products_supplier   ON products(supplier_id);
CREATE INDEX idx_products_sku_trgm   ON products USING GIN (sku gin_trgm_ops);

৩৪.৩ বর্তমান স্টকের View

CREATE VIEW current_stock AS
SELECT
    p.id            AS product_id,
    p.sku,
    p.name,
    w.id            AS warehouse_id,
    w.code          AS warehouse,
    COALESCE(SUM(m.quantity), 0) AS on_hand,
    p.reorder_level,
    COALESCE(SUM(m.quantity), 0) <= p.reorder_level AS needs_reorder,
    p.cost_price,
    COALESCE(SUM(m.quantity), 0) * p.cost_price AS stock_value
FROM products p
CROSS JOIN warehouses w
LEFT JOIN stock_movements m
       ON m.product_id = p.id AND m.warehouse_id = w.id
WHERE p.is_active
GROUP BY p.id, p.sku, p.name, w.id, w.code, p.reorder_level, p.cost_price;

CROSS JOIN warehouses দেওয়ায় যে গুদামে কোনো চলাচল হয়নি সেটাও ০ নিয়ে দেখাবে — নইলে "এই গুদামে এই পণ্য নেই" আর "শূন্য আছে" আলাদা করা যেত না।

ডাটা বাড়লে materialized view:

CREATE MATERIALIZED VIEW mv_current_stock AS SELECT * FROM current_stock;
CREATE UNIQUE INDEX ON mv_current_stock (product_id, warehouse_id);
CREATE INDEX ON mv_current_stock (needs_reorder) WHERE needs_reorder;

৩৪.৪ স্টক চলাচলের Function

db/002_functions.sql:

CREATE OR REPLACE FUNCTION record_movement(
    p_product_id   INT,
    p_warehouse_id INT,
    p_type         movement_type,
    p_quantity     INT,
    p_unit_cost    NUMERIC DEFAULT NULL,
    p_reference    TEXT DEFAULT NULL
) RETURNS TABLE (movement_id BIGINT, new_balance INT) AS $$
DECLARE
    v_current INT;
    v_id      BIGINT;
BEGIN
    -- একই পণ্যে সমান্তরাল চলাচল ঠেকাতে advisory lock
    PERFORM pg_advisory_xact_lock(p_product_id::BIGINT, p_warehouse_id::BIGINT);

    SELECT COALESCE(SUM(quantity), 0) INTO v_current
    FROM stock_movements
    WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;

    IF v_current + p_quantity < 0 THEN
        RAISE EXCEPTION 'স্টক ঋণাত্মক হতে পারবে না। বর্তমান %, চাওয়া হয়েছে %',
              v_current, p_quantity
        USING ERRCODE = 'check_violation';
    END IF;

    INSERT INTO stock_movements
        (product_id, warehouse_id, movement_type, quantity, unit_cost, reference)
    VALUES (p_product_id, p_warehouse_id, p_type, p_quantity, p_unit_cost, p_reference)
    RETURNING id INTO v_id;

    RETURN QUERY SELECT v_id, v_current + p_quantity;
END;
$$ LANGUAGE plpgsql;

Advisory lock এখানে গুরুত্বপূর্ণ। দুজন একসাথে একই পণ্যের শেষ ৫টা বিক্রি করলে row lock দিয়ে ঠেকানো যায় না (কারণ নতুন row ঢুকছে, পুরনোটা বদলাচ্ছে না)। pg_advisory_xact_lock একটা কাল্পনিক তালা দেয় যা transaction শেষে নিজে থেকেই খুলে যায়।

গুদামের মধ্যে স্থানান্তর:

CREATE OR REPLACE PROCEDURE transfer_stock(
    p_product_id INT, p_from INT, p_to INT, p_qty INT, p_ref TEXT DEFAULT NULL
) LANGUAGE plpgsql AS $$
BEGIN
    PERFORM record_movement(p_product_id, p_from, 'transfer', -p_qty, NULL, p_ref);
    PERFORM record_movement(p_product_id, p_to,   'transfer',  p_qty, NULL, p_ref);
END;
$$;

CALL transfer_stock(1, 1, 2, 25, 'TRF-2026-001');

দুটো চলাচল একই transaction-এ — একটা সফল আর একটা ব্যর্থ হওয়া অসম্ভব।

৩৪.৫ Database সংযোগ

app/database.py:

from contextlib import contextmanager
import os
from psycopg_pool import ConnectionPool
from psycopg.rows import dict_row

pool = ConnectionPool(
    conninfo=os.environ["DATABASE_URL"],
    min_size=2,
    max_size=20,
    max_idle=300,
    kwargs={"row_factory": dict_row, "options": "-c statement_timeout=10000"},
    open=False,
)

@contextmanager
def get_conn():
    """Transaction সহ connection। ব্যতিক্রম হলে নিজে থেকেই rollback।"""
    with pool.connection() as conn:
        yield conn

def fetch_all(sql: str, params: tuple = ()) -> list[dict]:
    with pool.connection() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()

def fetch_one(sql: str, params: tuple = ()) -> dict | None:
    with pool.connection() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchone()

psycopg-র pool.connection() context manager ব্লক শেষ হলে নিজেই COMMIT করে, ব্যতিক্রম হলে ROLLBACK — আলাদা করে লিখতে হয় না।

৩৪.৬ API

app/routers/stock.py:

from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel, Field
from typing import Literal
import psycopg
from app.database import fetch_all, fetch_one, get_conn

router = APIRouter(prefix="/stock", tags=["stock"])

class MovementIn(BaseModel):
    product_id: int
    warehouse_id: int
    movement_type: Literal["purchase","sale","return","adjustment","damage","transfer"]
    quantity: int = Field(..., ne=0)
    unit_cost: float | None = None
    reference: str | None = None


@router.get("/levels")
def stock_levels(
    warehouse_id: int | None = None,
    needs_reorder: bool | None = None,
    search: str | None = None,
    limit: int = Query(50, le=200),
):
    return fetch_all(
        """
        SELECT * FROM current_stock
        WHERE (%s::INT  IS NULL OR warehouse_id = %s)
          AND (%s::BOOL IS NULL OR needs_reorder = %s)
          AND (%s::TEXT IS NULL OR name ILIKE '%%' || %s || '%%' OR sku ILIKE '%%' || %s || '%%')
        ORDER BY needs_reorder DESC, on_hand ASC
        LIMIT %s
        """,
        (warehouse_id, warehouse_id, needs_reorder, needs_reorder,
         search, search, search, limit),
    )


@router.post("/movements", status_code=201)
def create_movement(m: MovementIn):
    try:
        with get_conn() as conn, conn.cursor() as cur:
            cur.execute(
                "SELECT * FROM record_movement(%s, %s, %s, %s, %s, %s)",
                (m.product_id, m.warehouse_id, m.movement_type,
                 m.quantity, m.unit_cost, m.reference),
            )
            return cur.fetchone()
    except psycopg.errors.CheckViolation as e:
        raise HTTPException(400, str(e).split("\n")[0])
    except psycopg.errors.ForeignKeyViolation:
        raise HTTPException(400, "পণ্য বা গুদাম পাওয়া যায়নি")


@router.get("/movements/{product_id}")
def movement_history(product_id: int, limit: int = Query(100, le=500)):
    """চলমান ব্যালেন্সসহ ইতিহাস"""
    return fetch_all(
        """
        SELECT
            m.id, m.movement_type, m.quantity, m.unit_cost,
            m.reference, m.created_at, w.code AS warehouse,
            SUM(m.quantity) OVER (
                PARTITION BY m.warehouse_id ORDER BY m.created_at, m.id
            ) AS running_balance
        FROM stock_movements m
        JOIN warehouses w ON w.id = m.warehouse_id
        WHERE m.product_id = %s
        ORDER BY m.created_at DESC, m.id DESC
        LIMIT %s
        """,
        (product_id, limit),
    )

running_balance — window function দিয়ে প্রতিটা চলাচলের পরে স্টক কত ছিল সেটা দেখাচ্ছে। ব্যাংকের স্টেটমেন্টের মতো। এটা অ্যাপ্লিকেশন কোডে হিসাব করতে গেলে অনেক জটিল হতো।

৩৪.৭ বিশ্লেষণী Endpoint

@router.get("/analytics/abc")
def abc_analysis():
    """ABC শ্রেণিবিন্যাস — কোন পণ্যে টাকা আটকে আছে"""
    return fetch_all("""
        WITH value AS (
            SELECT p.id, p.sku, p.name,
                   COALESCE(SUM(m.quantity), 0) * p.cost_price AS stock_value
            FROM products p
            LEFT JOIN stock_movements m ON m.product_id = p.id
            WHERE p.is_active
            GROUP BY p.id, p.sku, p.name, p.cost_price
        ),
        ranked AS (
            SELECT *,
                   SUM(stock_value) OVER (ORDER BY stock_value DESC) AS cumulative,
                   SUM(stock_value) OVER () AS grand_total
            FROM value WHERE stock_value > 0
        )
        SELECT sku, name, stock_value,
               ROUND(100.0 * cumulative / NULLIF(grand_total, 0), 1) AS cumulative_pct,
               CASE
                   WHEN cumulative / NULLIF(grand_total,0) <= 0.80 THEN 'A'
                   WHEN cumulative / NULLIF(grand_total,0) <= 0.95 THEN 'B'
                   ELSE 'C'
               END AS abc_class
        FROM ranked
        ORDER BY stock_value DESC
    """)


@router.get("/analytics/velocity")
def stock_velocity(days: int = 30):
    """কোন পণ্য দ্রুত বিক্রি হচ্ছে, কোনটা পড়ে আছে"""
    return fetch_all("""
        WITH sales AS (
            SELECT product_id, SUM(-quantity) AS sold
            FROM stock_movements
            WHERE movement_type = 'sale'
              AND created_at >= now() - (%s || ' days')::INTERVAL
            GROUP BY product_id
        ),
        stock AS (
            SELECT product_id, SUM(quantity) AS on_hand
            FROM stock_movements GROUP BY product_id
        )
        SELECT p.sku, p.name,
               COALESCE(s.sold, 0) AS units_sold,
               COALESCE(st.on_hand, 0) AS on_hand,
               ROUND(COALESCE(s.sold, 0) / %s::NUMERIC, 2) AS daily_velocity,
               CASE WHEN COALESCE(s.sold, 0) = 0 THEN NULL
                    ELSE ROUND(st.on_hand / (s.sold / %s::NUMERIC), 0)
               END AS days_until_stockout
        FROM products p
        LEFT JOIN sales s  ON s.product_id = p.id
        LEFT JOIN stock st ON st.product_id = p.id
        WHERE p.is_active
        ORDER BY days_until_stockout NULLS LAST
    """, (days, days, days))

৩৪.৮ চালানো

app/main.py:

from contextlib import asynccontextmanager
from fastapi import FastAPI
from app.database import pool
from app.routers import stock, products

@asynccontextmanager
async def lifespan(app: FastAPI):
    pool.open()
    pool.wait()
    yield
    pool.close()

app = FastAPI(title="ইনভেন্টরি API", lifespan=lifespan)
app.include_router(stock.router)
app.include_router(products.router)

@app.get("/health")
def health():
    with pool.connection() as conn:
        conn.execute("SELECT 1")
    return {"status": "ok"}

fastapi dev app/main.py
# http://localhost:8000/docs — স্বয়ংক্রিয় API ডকুমেন্টেশন

এই প্রজেক্টে PostgreSQL-এর full-text search, array, recursive CTE একসাথে ব্যবহার হবে।

৩৫.১ Schema

CREATE TABLE authors (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug     TEXT NOT NULL UNIQUE,
    name     TEXT NOT NULL,
    bio      TEXT,
    avatar   TEXT
);

CREATE TABLE posts (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
    slug         TEXT NOT NULL UNIQUE,
    title        TEXT NOT NULL,
    excerpt      TEXT,
    body         TEXT NOT NULL,
    tags         TEXT[] NOT NULL DEFAULT '{}',
    status       TEXT NOT NULL DEFAULT 'draft'
                 CHECK (status IN ('draft','published','archived')),
    view_count   INTEGER NOT NULL DEFAULT 0,
    published_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

    search_vec TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(title, '')),   'A') ||
        setweight(to_tsvector('simple', coalesce(excerpt, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(body, '')),    'C') ||
        setweight(to_tsvector('simple', array_to_string(tags, ' ')), 'A')
    ) STORED,

    CONSTRAINT chk_published CHECK (
        status <> 'published' OR published_at IS NOT NULL
    )
);

-- নেস্টেড কমেন্ট
CREATE TABLE comments (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id    INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    parent_id  INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    author_name TEXT NOT NULL,
    body       TEXT NOT NULL,
    is_approved BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_posts_search    ON posts USING GIN (search_vec);
CREATE INDEX idx_posts_tags      ON posts USING GIN (tags);
CREATE INDEX idx_posts_published ON posts(published_at DESC) WHERE status = 'published';
CREATE INDEX idx_posts_author    ON posts(author_id);
CREATE INDEX idx_comments_post   ON comments(post_id) WHERE is_approved;
CREATE INDEX idx_comments_parent ON comments(parent_id);

৩৫.২ অনুসন্ধান

-- র‍্যাঙ্কিং ও হাইলাইট সহ
SELECT
    p.slug, p.title, p.excerpt,
    a.name AS author,
    p.published_at,
    ts_rank(p.search_vec, q) AS rank,
    ts_headline('simple', p.body, q,
        'StartSel=<mark>, StopSel=</mark>, MaxWords=40, MinWords=20') AS snippet
FROM posts p
JOIN authors a ON a.id = p.author_id,
     websearch_to_tsquery('simple', $1) q
WHERE p.status = 'published' AND p.search_vec @@ q
ORDER BY rank DESC, p.published_at DESC
LIMIT 20;

অনুসন্ধানে কিছু না পেলে বিকল্প — বানান-সহনশীল খোঁজা:

SELECT slug, title, similarity(title, $1) AS score
FROM posts
WHERE status = 'published' AND title % $1
ORDER BY score DESC LIMIT 5;

দুটো মিলিয়ে একটা UNION করলে Google-এর "showing results for..." অভিজ্ঞতা তৈরি হয়।

৩৫.৩ ট্যাগ

-- সব ট্যাগ ও তাদের গণনা (tag cloud)
SELECT tag, COUNT(*) AS post_count
FROM posts, UNNEST(tags) AS tag
WHERE status = 'published'
GROUP BY tag
ORDER BY post_count DESC;

-- একটা ট্যাগের পোস্ট
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'] AND status = 'published';

-- একাধিক ট্যাগের যেকোনোটা
SELECT * FROM posts WHERE tags && ARRAY['sql','database'];

-- সম্পর্কিত পোস্ট — কতগুলো ট্যাগ মেলে তার ভিত্তিতে
SELECT p2.slug, p2.title,
       cardinality(ARRAY(SELECT UNNEST(p1.tags) INTERSECT SELECT UNNEST(p2.tags))) AS shared
FROM posts p1
JOIN posts p2 ON p2.id <> p1.id AND p2.tags && p1.tags
WHERE p1.slug = $1 AND p2.status = 'published'
ORDER BY shared DESC, p2.published_at DESC
LIMIT 5;

৩৫.৪ নেস্টেড কমেন্ট

WITH RECURSIVE comment_tree AS (
    SELECT id, parent_id, author_name, body, created_at,
           0 AS depth,
           ARRAY[id] AS path
    FROM comments
    WHERE post_id = $1 AND parent_id IS NULL AND is_approved

    UNION ALL

    SELECT c.id, c.parent_id, c.author_name, c.body, c.created_at,
           ct.depth + 1,
           ct.path || c.id
    FROM comments c
    JOIN comment_tree ct ON c.parent_id = ct.id
    WHERE c.is_approved AND ct.depth < 5
)
SELECT id, parent_id, depth, author_name, body, created_at
FROM comment_tree
ORDER BY path;

path array ধরে ORDER BY করায় কমেন্টগুলো ঠিক গাছের ক্রমে আসছে — অ্যাপ্লিকেশনে আর সাজাতে হবে না, শুধু depth অনুযায়ী ইন্ডেন্ট করলেই হলো।

৩৫.৫ পাঠসংখ্যা গোনা — একটা সাধারণ ফাঁদ

-- ❌ প্রতিটা page view এ এটা চালালে row lock এ প্রচণ্ড contention হবে
UPDATE posts SET view_count = view_count + 1 WHERE id = $1;

জনপ্রিয় পোস্টে সেকেন্ডে ১০০ জন ঢুকলে সবাই একই row-এর জন্য লাইনে দাঁড়াবে।

ভালো সমাধান — আলাদা টেবিলে জমা করে পর্যায়ক্রমে যোগ করা:

CREATE UNLOGGED TABLE post_views (
    post_id    INTEGER NOT NULL,
    viewed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- এখানে কোনো lock contention নেই, শুধু append
INSERT INTO post_views (post_id) VALUES ($1);

-- প্রতি ৫ মিনিটে একবার (pg_cron দিয়ে)
WITH agg AS (
    DELETE FROM post_views RETURNING post_id
)
UPDATE posts p
SET view_count = p.view_count + c.cnt
FROM (SELECT post_id, COUNT(*) AS cnt FROM agg GROUP BY post_id) c
WHERE p.id = c.post_id;

UNLOGGED টেবিল WAL-এ লেখে না, তাই insert খুব দ্রুত — এবং সার্ভার ক্র্যাশে কিছু view হারানো গ্রহণযোগ্য।

CSV/API থেকে ডাটা এনে PostgreSQL-এ রেখে বিশ্লেষণ করার সম্পূর্ণ প্রবাহ।

৩৬.১ Staging → Transform → Fact

কাঁচা ডাটা সরাসরি মূল টেবিলে ঢোকাবেন না। তিন স্তরে কাজ করুন:

-- স্তর ১: Staging — সব TEXT, কোনো constraint নেই
CREATE UNLOGGED TABLE stg_sales (
    raw_date     TEXT,
    raw_store    TEXT,
    raw_product  TEXT,
    raw_qty      TEXT,
    raw_amount   TEXT,
    loaded_at    TIMESTAMPTZ DEFAULT now(),
    source_file  TEXT
);

-- স্তর ২: মাত্রা (dimension)
CREATE TABLE dim_store (
    id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code    TEXT NOT NULL UNIQUE,
    name    TEXT NOT NULL,
    city    TEXT,
    region  TEXT
);

CREATE TABLE dim_product (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku      TEXT NOT NULL UNIQUE,
    name     TEXT NOT NULL,
    category TEXT
);

-- স্তর ৩: ঘটনা (fact) — partitioned
CREATE TABLE fact_sales (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    sale_date   DATE NOT NULL,
    store_id    INTEGER NOT NULL REFERENCES dim_store(id),
    product_id  INTEGER NOT NULL REFERENCES dim_product(id),
    quantity    INTEGER NOT NULL,
    amount      NUMERIC(14,2) NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE fact_sales_2026q1 PARTITION OF fact_sales
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE fact_sales_2026q2 PARTITION OF fact_sales
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE fact_sales_default PARTITION OF fact_sales DEFAULT;

CREATE INDEX ON fact_sales (sale_date, store_id);
CREATE INDEX ON fact_sales (product_id);

৩৬.২ লোড

# COPY — সবচেয়ে দ্রুত পথ
psql $DATABASE_URL -c "\copy stg_sales(raw_date,raw_store,raw_product,raw_qty,raw_amount) \
  FROM 'sales_jan.csv' WITH (FORMAT csv, HEADER, NULL '')"

Python থেকে stream করে লোড (বড় ফাইলে RAM বাঁচে):

import psycopg

with psycopg.connect(DSN) as conn, conn.cursor() as cur:
    with open("sales_jan.csv", "rb") as f:
        with cur.copy(
            "COPY stg_sales (raw_date, raw_store, raw_product, raw_qty, raw_amount) "
            "FROM STDIN WITH (FORMAT csv, HEADER)"
        ) as copy:
            while chunk := f.read(65536):
                copy.write(chunk)
    conn.commit()

৩৬.৩ পরিষ্কার ও রূপান্তর

-- খারাপ row আলাদা করে রাখা
CREATE TABLE rejected_rows (
    stg_row  JSONB,
    reason   TEXT,
    rejected_at TIMESTAMPTZ DEFAULT now()
);

WITH parsed AS (
    SELECT
        ctid,
        -- একাধিক তারিখ ফরম্যাট সহ্য করা
        CASE
            WHEN raw_date ~ '^\d{4}-\d{2}-\d{2}$' THEN raw_date::DATE
            WHEN raw_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(raw_date, 'DD/MM/YYYY')
            ELSE NULL
        END AS sale_date,
        UPPER(TRIM(raw_store))   AS store_code,
        UPPER(TRIM(raw_product)) AS sku,
        NULLIF(REGEXP_REPLACE(raw_qty, '[^0-9-]', '', 'g'), '')::INT AS quantity,
        NULLIF(REGEXP_REPLACE(raw_amount, '[^0-9.-]', '', 'g'), '')::NUMERIC AS amount
    FROM stg_sales
),
valid AS (
    SELECT * FROM parsed
    WHERE sale_date IS NOT NULL AND quantity IS NOT NULL
      AND amount IS NOT NULL AND quantity > 0
)
INSERT INTO fact_sales (sale_date, store_id, product_id, quantity, amount)
SELECT v.sale_date, s.id, p.id, v.quantity, v.amount
FROM valid v
JOIN dim_store   s ON s.code = v.store_code
JOIN dim_product p ON p.sku  = v.sku;

-- যা ঢোকেনি সেগুলো লগ করুন
INSERT INTO rejected_rows (stg_row, reason)
SELECT to_jsonb(st), 'পার্স করা যায়নি বা মাত্রা মেলেনি'
FROM stg_sales st
WHERE NOT EXISTS (
    SELECT 1 FROM fact_sales f
    JOIN dim_store s ON s.id = f.store_id
    WHERE s.code = UPPER(TRIM(st.raw_store))
);

TRUNCATE stg_sales;

খারাপ ডাটা চুপচাপ ফেলে দেবেন না — rejected_rows-এ রেখে দিন। প্রতি সপ্তাহে এই টেবিলটা দেখলে উৎসের কোথায় সমস্যা তা ধরা পড়ে।

৩৬.৪ অ্যানালিটিক্স View

CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT
    f.sale_date,
    s.region, s.city, s.name AS store,
    p.category,
    SUM(f.quantity) AS units,
    SUM(f.amount)   AS revenue,
    COUNT(*)        AS transactions,
    ROUND(SUM(f.amount) / NULLIF(COUNT(*), 0), 2) AS avg_ticket
FROM fact_sales f
JOIN dim_store   s ON s.id = f.store_id
JOIN dim_product p ON p.id = f.product_id
GROUP BY 1,2,3,4,5;

CREATE UNIQUE INDEX ON mv_sales_daily (sale_date, region, city, store, category);
CREATE INDEX ON mv_sales_daily (sale_date DESC);

সময়ের সাথে তুলনা (year-over-year):

SELECT
    TO_CHAR(sale_date, 'YYYY-MM') AS month,
    SUM(revenue) AS revenue,
    LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)) AS same_month_last_year,
    ROUND(100.0 * (SUM(revenue) - LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)))
          / NULLIF(LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)), 0), 1) AS yoy_pct
FROM mv_sales_daily
GROUP BY DATE_TRUNC('month', sale_date), TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY 1;

Cohort বিশ্লেষণ — গ্রাহক ধরে রাখার হার:

WITH first_purchase AS (
    SELECT customer_id, DATE_TRUNC('month', MIN(sale_date)) AS cohort_month
    FROM sales GROUP BY customer_id
),
activity AS (
    SELECT s.customer_id, f.cohort_month,
           DATE_TRUNC('month', s.sale_date) AS active_month
    FROM sales s JOIN first_purchase f ON f.customer_id = s.customer_id
    GROUP BY 1,2,3
)
SELECT
    TO_CHAR(cohort_month, 'YYYY-MM') AS cohort,
    (EXTRACT(YEAR  FROM AGE(active_month, cohort_month)) * 12
   + EXTRACT(MONTH FROM AGE(active_month, cohort_month)))::INT AS month_number,
    COUNT(DISTINCT customer_id) AS customers
FROM activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

এই একটা query থেকে ক্লাসিক cohort retention টেবিল তৈরি হয় — মাস ০-তে ১০০ জন, মাস ১-এ ৪৫ জন, মাস ২-তে ৩০ জন — এভাবে।

৩৬.৫ স্বয়ংক্রিয়করণ

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- প্রতি রাত ২টায় materialized view রিফ্রেশ
SELECT cron.schedule('refresh-sales', '0 2 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_daily$$);

-- প্রতি মাসের ২৫ তারিখে পরের মাসের partition তৈরি
SELECT cron.schedule('create-partition', '0 0 25 * *', $$
  DO $do$
  DECLARE
      start_date DATE := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
      part_name  TEXT := 'fact_sales_' || TO_CHAR(start_date, 'YYYY_MM');
  BEGIN
      EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF fact_sales FOR VALUES FROM (%L) TO (%L)',
        part_name, start_date, start_date + INTERVAL '1 month');
  END
  $do$;
$$);

-- প্রতি রবিবার পুরনো staging পরিষ্কার
SELECT cron.schedule('cleanup', '0 3 * * 0',
  $$DELETE FROM rejected_rows WHERE rejected_at < now() - INTERVAL '90 days'$$);

format() ফাংশনের %I (identifier) ও %L (literal) — dynamic SQL লেখার নিরাপদ উপায়। কখনোই || দিয়ে টেবিলের নাম জোড়া লাগাবেন না।

চালু করার আগে এই তালিকাটা ধরে ধরে মিলিয়ে নিন।

নিরাপত্তা

• [ ] অ্যাপ্লিকেশন postgres superuser দিয়ে চলছে না
• [ ] প্রতিটা সেবার আলাদা role, ন্যূনতম privilege
• [ ] pg_hba.conf-এ trust নেই, scram-sha-256 ব্যবহৃত
• [ ] SSL চালু (sslmode=require অন্তত)
• [ ] Database ইন্টারনেট থেকে সরাসরি পৌঁছানো যায় না (VPC/firewall)
• [ ] সব query parameterized, কোথাও string concatenation নেই
• [ ] পাসওয়ার্ড ও connection string environment variable-এ, কোডে নয়
• [ ] Multi-tenant হলে RLS চালু ও FORCE

নির্ভরযোগ্যতা

• [ ] স্বয়ংক্রিয় দৈনিক backup চালু
• [ ] Backup থেকে restore করে পরীক্ষা করা হয়েছে
• [ ] WAL archiving / PITR চালু
• [ ] অন্তত একটা replica আছে
• [ ] Backup ভিন্ন অঞ্চলে/অ্যাকাউন্টে রাখা আছে
• [ ] Backup retention নীতি ঠিক করা (৭ দিন দৈনিক, ৪ সপ্তাহ সাপ্তাহিক, ১২ মাস মাসিক)

Performance

• [ ] সব foreign key কলামে index আছে
• [ ] pg_stat_statements চালু
• [ ] ধীর query log চালু (log_min_duration_statement)
• [ ] Connection pooling (PgBouncer বা অ্যাপ্লিকেশন pool)
• [ ] shared_buffers, work_mem, effective_cache_size টিউন করা
• [ ] statement_timeout ও idle_in_transaction_session_timeout সেট করা
• [ ] বড় টেবিলে autovacuum আক্রমণাত্মক করা

পর্যবেক্ষণ

• [ ] ডিস্ক ব্যবহারের সতর্কতা (৮০%-এ alert)
• [ ] Connection সংখ্যার সতর্কতা
• [ ] Replication lag পর্যবেক্ষণ
• [ ] Transaction ID age পর্যবেক্ষণ
• [ ] Cache hit ratio পর্যবেক্ষণ
• [ ] Deadlock ও error হার

Schema ও কোড

• [ ] সব schema পরিবর্তন migration ফাইলে, version control-এ
• [ ] Migration production-এর আগে staging-এ চলেছে
• [ ] বড় টেবিলে CREATE INDEX CONCURRENTLY
• [ ] টাকার কলামে NUMERIC, FLOAT নয়
• [ ] সময়ের কলামে TIMESTAMPTZ
• [ ] প্রতিটা টেবিলে created_at ও updated_at
• [ ] গুরুত্বপূর্ণ constraint database-এ প্রয়োগ করা (শুধু অ্যাপে নয়)

অ্যাপ্লিকেশন

• [ ] Connection সবসময় finally-তে release হচ্ছে
• [ ] Transaction যতটা সম্ভব ছোট
• [ ] Transaction-এর ভেতরে বাইরের API কল নেই
• [ ] SERIALIZABLE ব্যবহার করলে retry logic আছে
• [ ] সুশৃঙ্খল shutdown (pool.end())
• [ ] Health check endpoint database ছুঁয়ে দেখে

সংযোগ

psql -h host -p 5432 -U user -d dbname
psql "postgresql://user:pass@host:5432/db?sslmode=require"
psql -c "SELECT 1"                    # এক কমান্ড চালিয়ে বের হওয়া
psql -f script.sql -d dbname          # ফাইল চালানো
psql -At -c "SELECT ..." -d db        # স্ক্রিপ্টের জন্য পরিষ্কার আউটপুট

Meta-command

\l  \l+                সব database
\c dbname              database বদল
\dt  \dt+  \dt *.*     টেবিল
\d table  \d+ table    গঠন
\dn  \du  \di  \df     schema, role, index, function
\dv  \dm               view, materialized view
\x                     expanded display
\timing                সময় দেখানো
\e  \e file            editor
\i file.sql            ফাইল চালানো
\o file                আউটপুট ফাইলে
\copy t FROM 'f.csv' CSV HEADER
\watch 2               প্রতি ২ সেকেন্ডে query পুনরাবৃত্তি
\conninfo  \h  \?  \q

দরকারি সিস্টেম Query

-- আকার
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- সক্রিয় connection
SELECT pid, usename, state, age(now(), query_start) AS dur, LEFT(query,60)
FROM pg_stat_activity WHERE state <> 'idle';

-- Query বাতিল
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

-- Cache hit ratio (৯৯% এর উপরে থাকা উচিত)
SELECT ROUND(100.0 * SUM(heap_blks_hit) / NULLIF(SUM(heap_blks_hit + heap_blks_read),0), 2)
FROM pg_statio_user_tables;

-- সেটিংস
SHOW ALL;
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%';
SELECT pg_reload_conf();

-- Lock
SELECT * FROM pg_locks WHERE NOT granted;

১. DELETE, TRUNCATE, DROP-এর পার্থক্য? DELETE — row মোছে, WHERE দেওয়া যায়, trigger চলে, ধীর। TRUNCATE — সব row দ্রুত মোছে, টেবিল থাকে, row trigger চলে না। DROP — টেবিলটাই মুছে যায়। তিনটাই PostgreSQL-এ transactional।

২. WHERE আর HAVING-এর পার্থক্য? WHERE group করার আগে row ছাঁকে; HAVING group করার পরে group ছাঁকে। aggregate function HAVING-এ ব্যবহার করা যায়, WHERE-এ নয়।

৩. Index কীভাবে query দ্রুত করে? খারাপ দিক কী? Index একটা সাজানো ডাটা কাঠামো (সাধারণত B-tree) যা O(log n)-এ খুঁজে দেয়, sequential scan-এর O(n)-এর বদলে। খারাপ দিক: ডিস্ক জায়গা, প্রতি write-এ index আপডেট, maintenance খরচ।

৪. MVCC কী? Multi-Version Concurrency Control। UPDATE-এ পুরনো row বদলানোর বদলে নতুন version তৈরি হয়; প্রতিটা transaction তার snapshot অনুযায়ী দেখে। ফলে read কখনো write আটকায় না। এর মূল্য — dead tuple, যা VACUUM পরিষ্কার করে।

৫. JSON আর JSONB-এর পার্থক্য? JSON হুবহু টেক্সট রাখে (দ্রুত insert, ধীর query)। JSONB binary parsed রাখে (একটু ধীর insert, অনেক দ্রুত query, GIN index সমর্থন)। প্রায় সবসময় JSONB।

৬. UNION আর UNION ALL? UNION ডুপ্লিকেট বাদ দেয় (sort/hash করতে হয়, ব্যয়বহুল), UNION ALL রাখে (দ্রুত)। ডুপ্লিকেট নিয়ে সমস্যা না থাকলে UNION ALL।

৭. Window function আর GROUP BY? GROUP BY row সংকুচিত করে; window function সব row রেখে প্রতিটার জন্য গোষ্ঠীভিত্তিক হিসাব দেয়।

৮. NOT IN বিপজ্জনক কেন? Subquery-তে একটাও NULL থাকলে পুরো শর্ত কখনো TRUE হয় না — ফলাফল খালি আসে। NOT EXISTS ব্যবহার করা উচিত।

৯. ACID কী? Atomicity (সব বা কিছুই না), Consistency (constraint বজায়), Isolation (পারস্পরিক হস্তক্ষেপ নেই), Durability (commit হলে টিকে থাকে)।

১০. CHAR, VARCHAR, TEXT — performance পার্থক্য? PostgreSQL-এ কার্যত নেই; তিনটাই একই ভেতরের কাঠামো ব্যবহার করে। VARCHAR(n) শুধু ব্যবসায়িক নিয়ম প্রয়োগের জন্য।

১১. Deadlock কী, কীভাবে এড়াবেন? দুই transaction পরস্পরের lock-এর জন্য অপেক্ষা করলে। PostgreSQL ধরে ফেলে একটাকে বাতিল করে। এড়াতে — সবসময় একই ক্রমে resource lock করুন, transaction ছোট রাখুন।

১২. LEFT JOIN-এ শর্ত ON-এ না WHERE-এ দেবেন? ডান টেবিলের শর্ত ON-এ। WHERE-এ দিলে NULL row বাদ পড়ে LEFT JOIN কার্যত INNER JOIN হয়ে যায়।

১৩. Partitioning কখন করবেন? টেবিল খুব বড় (১০০ GB+), ডাটার স্বাভাবিক বিভাজন আছে (সাধারণত সময়), পুরনো ডাটা নিয়মিত মুছতে হয়, এবং query-তে partition key প্রায় সবসময় থাকে।

১৪. Transaction ID wraparound কী? ৩২-বিট transaction ID ঘুরে আসার সমস্যা। VACUUM freeze করে এটা ঠেকায়। না ঠেকালে PostgreSQL সব write বন্ধ করে দেয়।

১৫. TIMESTAMP না TIMESTAMPTZ? প্রায় সবসময় TIMESTAMPTZ — এটা UTC-তে রাখে ও timezone অনুযায়ী দেখায়। TIMESTAMP timezone তথ্য হারিয়ে ফেলে।

Query কাজ করছে না? এই ক্রমে দেখুন: 1. \d tablename — কলামের নাম ও type ঠিক আছে? 2. SELECT দিয়ে ছোট করে চালিয়ে দেখুন কোন অংশে সমস্যা 3. EXPLAIN — planner কী ভাবছে? 4. Error বার্তা পুরোটা পড়ুন — PostgreSQL-এর error বার্তা অত্যন্ত সহায়ক, প্রায়ই HINT: লাইনে সমাধান লেখা থাকে

৩০ দিনের অনুশীলন পরিকল্পনা

সপ্তাহ ১ — ভিত্তি ইনস্টল, psql আয়ত্ত, টেবিল তৈরি, CRUD, SELECT-এর সব clause, NULL-এর আচরণ।

সপ্তাহ ২ — সম্পর্ক সব ধরনের JOIN, aggregate ও GROUP BY, subquery, CTE, constraint দিয়ে সম্পর্ক তৈরি।

সপ্তাহ ৩ — গভীরতা Window function, index ও EXPLAIN, transaction ও isolation, JSONB, view।

সপ্তাহ ৪ — বাস্তব প্রয়োগ একটা পূর্ণ প্রজেক্ট বানান (ব্লগ, ইনভেন্টরি, বা টিকিটিং সিস্টেম), function ও trigger লিখুন, backup-restore অনুশীলন, performance টিউনিং।

নিজে বানানোর মতো প্রজেক্ট

1. ব্যক্তিগত অর্থ ব্যবস্থাপক — লেনদেন, ক্যাটাগরি, বাজেট, মাসিক রিপোর্ট
2. লাইব্রেরি সিস্টেম — বই, সদস্য, ধার, জরিমানা হিসাব, overdue রিপোর্ট
3. URL শর্টনার — analytics সহ, ক্লিক ট্র্যাকিং, সময়ভিত্তিক রিপোর্ট
4. ব্লগ প্ল্যাটফর্ম — full-text search, ট্যাগ (array), নেস্টেড কমেন্ট (recursive CTE)
5. হোটেল বুকিং — range type ও exclusion constraint দিয়ে ওভারল্যাপ প্রতিরোধ

প্রতিটাতে চেষ্টা করুন: সঠিক normalization, প্রয়োজনীয় index, অন্তত একটা view ও একটা trigger, এবং EXPLAIN দিয়ে query যাচাই।

শেখার সম্পদ

• অফিসিয়াল ডকুমেন্টেশন — postgresql.org/docs (অসাধারণ মানের, পড়ার অভ্যাস করুন)
• PostgreSQL Exercises — pgexercises.com (হাতে-কলমে অনুশীলন)
• Use The Index, Luke! — use-the-index-luke.com (index নিয়ে সেরা ব্যাখ্যা)
• PGTune — pgtune.leopard.in.ua (configuration সহায়ক)
• Explain visualizer — explain.dalibo.com (query plan সহজে বোঝা)
• pgMustard, PostgreSQL Weekly newsletter — নিয়মিত আপডেট

তিনটি শেষ পরামর্শ

PostgreSQL শেখা একটা দীর্ঘ পথ — এই বই আপনাকে শুরু থেকে অ্যাডভান্স পর্যন্ত নিয়ে এসেছে, কিন্তু আসল শেখা হবে যখন আপনি নিজের প্রজেক্টে সমস্যায় পড়বেন এবং সমাধান খুঁজবেন।

তিনটা পরামর্শ মনে রাখবেন:

১. পরিমাপ করুন, অনুমান করবেন না। "এটা দ্রুত হবে মনে হয়" — এর কোনো মূল্য নেই। EXPLAIN ANALYZE চালান।

২. Database-কে কাজ করতে দিন। Constraint, transaction, aggregate — এগুলো PostgreSQL অ্যাপ্লিকেশন কোডের চেয়ে ভালো করে। ডাটা যাচাই database-এই করান।

৩. সরল থেকে শুরু করুন। Partition, replication, sharding — এসব সমস্যা হলে তবেই আনুন। বেশিরভাগ অ্যাপে একটা ভালো-index করা PostgreSQL সার্ভারই যথেষ্ট, বহু বছর ধরে।

শুভকামনা। 🐘

এই বইয়ের প্রতিটা query আপনার নিজের হাতে চালানো পর্যন্ত এগুলো কেবল টেক্সট। আজই docker compose up -d চালান, অধ্যায় ৩২-এর seed স্ক্রিপ্ট দিয়ে ৫ লাখ row বানান, তারপর EXPLAIN ANALYZE চালিয়ে দেখুন index থাকা আর না থাকার পার্থক্য।

ওই মুহূর্তটাই — যখন ২০০ মিলিসেকেন্ড হঠাৎ ০.৩ মিলিসেকেন্ড হয়ে যায় — PostgreSQL শেখার আসল শুরু।

🐘

লেখক ও সংকলক: Foysal Ahmed Pranto · যোগাযোগ: 01313-990906