Pagination, filter আর sort — offset বনাম cursor

১ · সব একসাথে কেন নয়

বাজারঘরের গুদামে এখন ১০ লাখ পণ্যের হিসাব। এক ক্রেতা ফোনে “সব পণ্য” পাতা খুললেন। সবচেয়ে সরল API হলো SELECT * চালিয়ে পুরো তালিকা JSON করে পাঠানো। ছোট ডেমোতে এটি চলে, তাই অনেক প্রকল্প এভাবেই শুরু হয়। ডেটা বাড়লে একই কোড সবার আগে ভাঙে।

একটি মুদি দোকানের কথা ভাবুন। ক্রেতা জিজ্ঞেস করলেন, “কী কী আছে?” দোকানি পুরো গুদামের খাতা তাঁর হাতে ধরিয়ে দেন না। তিনি প্রথম পাতাটি দেখান, আর বলেন, “আরও লাগলে বলবেন।” Pagination ঠিক এই কাজ: বড় তালিকাকে ছোট পাতায় ভাগ করে দেওয়া, প্রতি অনুরোধে একটি পাতা।

কথাটি সংখ্যায় দেখা যাক। নিচের বাস্তব Express 5 API-তে ১০ লাখ সারির একটি SQLite টেবিল আছে। একবার পুরো তালিকা, একবার ২০টির একটি পাতা চাওয়া হলো।

সত্যিকারের run · একটি local কম্পিউটারে একবার মাপা · 127.0.0.1 · Node.js 24.15, Express 5.3.0, better-sqlite3 13.0.3 (SQLite 3.53.4) · ~/bazarghar

GET /api/products-all     200   103.9 MB   1046 ms
GET /api/products?limit=20  200   2264 byte   2.6 ms

পার্থক্য প্রায় ৪৫,৯০০ গুণ, আর এটি শুধু একই কম্পিউটারের ভেতরের হিসাব, যেখানে network নেই। বাস্তবে আরও চারটি খরচ যোগ হয়। Server-এ পুরো ফল মেমরিতে আসে। Event loop JSON বানাতে আটকে থাকে, ফলে সেই সময় অন্য অনুরোধ অপেক্ষা করে। মোবাইল ডেটায় ক্রেতার টাকা খরচ হয়। Browser-কে ১০ লাখ সারি parse করতে হয়। আর rate limit না থাকলে কেউ এই URL বারবার ডেকেই server ব্যস্ত করে ফেলতে পারেন।

পদ্ধতি মূলত দুটি। Offset: “প্রথম ৪০টি বাদ দিয়ে পরের ২০টি দিন।” আর cursor: “৯৯৯৯৯৭ নম্বর সারির পর থেকে ২০টি দিন।” দুটি বাক্য কাছাকাছি শোনায়, কিন্তু ডেটাবেসের কাজে তাদের ফারাক বিশাল। REST নকশার সাধারণ দিকটি REST API নকশা পর্বে আছে। এখানে আমরা বাজারঘরের আসল Express কোড আর মাপা সংখ্যা নিয়ে কাজ করব।

২ · Offset pagination — সবচেয়ে সহজ পথ

Offset pagination-এ client বলে কোন পাতা চাই। Server একটি সরল সূত্রে সেটি SQL-এ বদলায়:

SELECT id, name, category, price, created_at
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

এর সুবিধাগুলো সত্যিই বড়। Query যে কেউ বোঝেন। “পাতা ৭-এ যান” সহজে করা যায়। পাতার নম্বর URL-এ থাকে, তাই link শেয়ার করা যায়। ছোট admin তালিকার জন্য এটি প্রায়ই সঠিক পছন্দ। ORDER BY কিন্তু বাদ দেওয়া চলবে না। PostgreSQL-এর docs স্পষ্ট বলে, অনন্য ক্রম ছাড়া LIMIT “an unpredictable subset” দিতে পারে, আর এটি bug নয়, SQL-এর স্বভাব। এই নিয়মটির নাম stable order, ৫ নম্বর অংশে আবার আসবে।

আমাদের API-র বাস্তব উত্তর। limit=2, পাতা ৩:

সত্যিকারের run · 127.0.0.1 · উত্তর ছাঁটা

GET /api/products?limit=2&page=3
200 application/json; charset=utf-8
Link: </api/products?limit=2&page=4>; rel="next", </api/products?limit=2&page=2>; rel="prev"
{"data":[{"id":999996,"name":"Product 999996","category":"sabun","price":475,…},
         {"id":999995,"name":"Product 999995","category":"sabun","price":385,…}],
 "page":{"page":3,"limit":2,"hasMore":true}}

হিসাব মিলছে। সব মিলিয়ে id 1000000 থেকে শুরু, পাতা ১-এ দুটি, পাতা ২-এ দুটি, তাই পাতা ৩ শুরু হলো 999996 থেকে। Link header নিয়ে ৬ নম্বর অংশে কথা হবে।

৩ · গভীর পাতা কেন ধীর — মাপা ফল

ধরুন আপনি খাতার ৯ লাখ নম্বর লাইনে যেতে চান, কিন্তু খাতায় পাতার নম্বর নেই। তাহলে প্রথম থেকে এক এক করে গুনতে হবে। OFFSET-এর কাজও তাই। Index সারিগুলো সাজিয়ে রাখে, কিন্তু “৯ লাখতম সারি কোনটি” সরাসরি বলে না। ডেটাবেস সারি পেরিয়ে যায়, গোনে আর ফেলে দেয়। PostgreSQL-এর docs-এর ভাষায়, বাদ দেওয়া সারি “still have to be computed inside the server”. SQLite-এর docs-ও বলে, OFFSET-এ সময় লাগে offset-এর সমানুপাতিক।

নিচের টেবিলে একই ১০ লাখ সারি, একই index, পাতার আকার ২০. PostgreSQL 18.6-ও একটি বহনযোগ্য build (npm-এর embedded-postgres) দিয়ে সত্যিই চালানো হয়েছে।

সময় ms-এ · সত্যিকারের run, একটি local কম্পিউটারে একবার · প্রতিটি query ৯ বার চালিয়ে মধ্যমা · সব ডেটা মেমরিতে · এটি সাধারণ ফল নয়। SQLite চলেছে Node-এর ভেতরেই, আর PostgreSQL-এর সময়ে driver ও loopback যুক্ত, তাই দুই ডেটাবেস তুলনা করবেন না

তিনটি জিনিস লক্ষ করুন। প্রথম, offset-এর সময় সারির সাথে প্রায় সরলরেখায় বাড়ে। দ্বিতীয়, cursor-এর সংখ্যা প্রায় নড়ে না। তৃতীয়, একদম শুরুতে offset সামান্য এগিয়ে, আর ১০,০০০ সারি পর্যন্ত সময় এক ms-এর নিচে। তাই ছোট তালিকায় offset-কে শত্রু ভাবার দরকার নেই।

কারণটি EXPLAIN সরাসরি দেখায়। PostgreSQL-এ EXPLAIN (ANALYZE, BUFFERS) query চালিয়ে আসল সারি আর পড়া page গোনে।

সত্যিকারের run · PostgreSQL 18.6 · COSTS OFF · cursor (100001) ঠিক ৯ লাখ সারি পরের অবস্থান

> SELECT id,name FROM products ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 900000
Limit (actual time=97.022..97.024 rows=20.00 loops=1)
  Buffers: shared hit=11863
  ->  Index Scan using idx_new on products (actual time=0.008..79.833 rows=900020.00 loops=1)
Execution Time: 97.034 ms

> SELECT id,name FROM products WHERE (created_at,id) < ('2024-01-24T03:33:00.000Z',100001)
  ORDER BY created_at DESC, id DESC LIMIT 20
Limit (actual time=0.009..0.011 rows=20.00 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_new on products (actual time=0.008..0.010 rows=20.00 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2024-01-24 09:33:00+06'::timestamp with time zone, 100001))
Execution Time: 0.030 ms

দুটি query একই ২০টি সারি দেয়। প্রথমটি ৯,০০,০২০টি সারি পড়ে, ১১,৮৬৩টি page ছুঁয়ে। দ্বিতীয়টি ২০টি সারি পড়ে, ৪টি page ছুঁয়ে। (EXPLAIN ANALYZE-এর নিজের মাপার খরচ আছে, তাই ৯৭ ms টেবিলের ৬৮.৬৭ ms-এর চেয়ে বেশি।) SQLite-এর EXPLAIN QUERY PLAN একই গল্প বলে, শুধু সংক্ষেপে: offset-এ SCAN products USING INDEX idx_new, আর cursor-এ SEARCH products USING INDEX idx_new (created_at<?). SCAN মানে হাঁটা, SEARCH মানে সরাসরি পৌঁছানো।

পুরো তালিকা ঘুরে দেখার খরচ — একটি পূর্ণ numerical

একটি export job পুরো তালিকা পাতায় পাতায় পড়ছে। ধরুন মোট সারি N, পাতার আকার p, মোট পাতা P = N/p. k-তম পাতার জন্য offset-এ পড়তে হয় (k − 1)p + p = kp টি সারি। সব পাতা যোগ করলে:

N = ১০,০০,০০০ আর p = ২০ হলে P = ৫০,০০০. মোট পড়া = ২০ × ৫০,০০০ × ৫০,০০১ / ২ = ২৫,০০,০৫,০০,০০০ সারি, মানে প্রায় ২,৫০০ কোটি। Cursor-এ প্রতি পাতায় B-tree-র উচ্চতা h (ধরা যাক ৪) আর ২০টি সারি। মোট ≈ ৫০,০০০ × ২৪ = ১২,০০,০০০. অনুপাত প্রায় ২০,৮০০ গুণ। সূত্রে N² থাকায় ডেটা দ্বিগুণ হলে offset-এর কাজ চারগুণ, cursor-এর প্রায় দ্বিগুণ।

পুরো ১০ লাখ হাঁটা অনেক সময় নিত, তাই প্রথম ১,০০,০০০ সারি সত্যিই হাঁটা হলো। সেখানে সূত্র বলে offset পড়বে ২০ × ৫,০০০ × ৫,০০১ / ২ = ২৫,০০,৫০,০০০টি সারি।

সত্যিকারের run · একটি local কম্পিউটারে একবার · প্রথম ১,০০,০০০ সারি, পাতা ২০

SQLite      offset walk   3663 ms    cursor walk   121 ms
PostgreSQL  offset walk  19889 ms    cursor walk   769 ms

সময়ের অনুপাত এখানে প্রায় ৩০ আর ২৬ গুণ, সারির অনুপাতের চেয়ে অনেক কম। কেন? কারণ প্রতিটি query-র একটি স্থির খরচ আছে (parse, প্রস্তুতি, driver), আর একটি সারি পেরোনো খুব সস্তা। ১ লাখের হাঁটায় সারিপ্রতি প্রায় ১৪.৬ ns পড়ে। টেবিলের গভীরতম সারিতে পড়ে প্রায় ৩২.৭ ns. সেই হারে পুরো ১০ লাখের হাঁটা হতো মোটামুটি ৬ থেকে ১৪ মিনিট, এটি হিসাবের অনুমান, মাপা ফল নয়। এই ডেটা মেমরিতে ছিল। ডিস্ক থেকে পড়তে হলে সারি পেরোনো অনেক দামি হতে পারে।

৪ · পড়ার মাঝে সারি সরে যায়

গতির চেয়েও সূক্ষ্ম একটি সমস্যা আছে। কিউতে দাঁড়ানো মানুষের কথা ভাবুন। আপনি বললেন, “প্রথম ৬ জনের পরের লোকটিকে ডাকুন।” এর মধ্যে সামনে একজন ঢুকে পড়লে “৬ জনের পরের লোক” আর সেই মানুষটি নেই। Offset জায়গার হিসাব রাখে, মানুষের নয়। নতুন সারি ঢুকলে বা পুরোনো সারি মুছলে পাতার সীমা সরে যায়।

নিচে ১২টি পণ্য, নতুন আগে, প্রতি পাতায় ৩টি। একজন পাঠক পাতা ১ আর ২ পড়েছেন। “পরে” চেপে দেখুন পাতা ৩-এর আগে কী ঘটে।

1SELECT id FROM products2  ORDER BY created_at DESC, id DESC3  LIMIT 3 OFFSET :offset;45INSERT INTO products … ; -- id 136DELETE FROM products WHERE id = 11;

নিচের ছোট Node প্রোগ্রাম একই পরিস্থিতি মেপে দেখে। প্রতিটি পদ্ধতি পাতা ১ পড়ার পরে তালিকা বদলায়, তারপর বাকি পাতা পড়ে। শেষে হিসাব: কী দুবার এলো, কী হারাল।

// Products newest first, 3 per page. The list changes after page 1.
const SIZE = 3;

function walk(useCursor, change) {
  let rows = [9, 8, 7, 6, 5, 4, 3, 2, 1];
  const original = [...rows];
  const seen = [];
  let page = 1, last = null;

  while (true) {
    const batch = useCursor
      ? rows.filter((id) => last === null || id < last).slice(0, SIZE)
      : rows.slice((page - 1) * SIZE, page * SIZE);
    if (batch.length === 0) break;
    seen.push(...batch);
    last = batch[batch.length - 1];
    if (page === 1) rows = change(rows);
    page++;
  }
  const twice = seen.filter((id, i) => seen.indexOf(id) !== i);
  const lost = original.filter((id) => !seen.includes(id) && rows.includes(id));
  return `seen ${seen.join(' ')} | twice [${twice}] | lost [${lost}]`;
}

const addTop = (r) => [10, ...r];
const remove8 = (r) => r.filter((id) => id !== 8);

console.log('insert, offset:', walk(false, addTop));
console.log('insert, cursor:', walk(true, addTop));
console.log('delete, offset:', walk(false, remove8));
console.log('delete, cursor:', walk(true, remove8));

insert, offset: seen 9 8 7 7 6 5 4 3 2 1 | twice [7] | lost []
insert, cursor: seen 9 8 7 6 5 4 3 2 1 | twice [] | lost []
delete, offset: seen 9 8 7 5 4 3 2 1 | twice [] | lost [6]
delete, cursor: seen 9 8 7 6 5 4 3 2 1 | twice [] | lost []

এটি শুধু সিমুলেশনের ফল নয়। বাস্তব API-তে পাতা ১ পড়ার পরে একটি পণ্য যোগ করে পাতা ২ আনা হলো, পরে একটি পণ্য মুছে আবার:

সত্যিকারের run · Express 5.3.0 · limit=3

before insert: page 1 = 1000000 999999 999998 | cursor page 1 = 1000000 999999 999998
inserted { id: 1000001 }
after insert: offset page 2 = 999998 999997 999996 | cursor page 2 = 999997 999996 999995

page1 1000000 999999 999998  deleted 1000000
offset page 2 = 999996 999995 999994 | cursor page 2 = 999997 999996 999995

Insert-এ offset 999998 আবার দিল। Delete-এ offset 999997 ফেলে গেল। দ্বিতীয় ক্ষেত্রে cursor-এর উত্তর একই থাকল, যদিও সে যে সারি থেকে শুরু, তার আগের সারি মুছে গেছে। মনে রাখুন, cursor সারিটিকে খোঁজে না, শুধু মানের সাথে তুলনা করে। তাই cursor-এর সারিটিই মুছে গেলেও কাজ চলে।

৫ · Cursor pagination — যেখানে থেমেছিলেন, সেখান থেকে

বইয়ে বুকমার্ক রাখলে আপনি পরের দিন ২০১ পৃষ্ঠা গুনে আসেন না, সোজা সেই জায়গায় খোলেন। Keyset pagination-এ সেই বুকমার্কের নাম cursor: আগের পাতার শেষ সারির sort কলামের মান। পরের query “এই মানের পরের সারি দিন” বলে WHERE-তে, আর index সেখানে সোজা নিয়ে যায়।

বাজারঘরের সহজ cursor query (নতুন আগে) দেখতে এমন:

SELECT id, name, price, created_at
FROM products
WHERE (created_at, id) < (?, ?)      -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 21;                            -- limit + 1, see section 6

(created_at, id) < (?, ?)-কে বলে row value তুলনা। এটি অভিধানের মতো বাঁ থেকে ডানে মেলায়: আগে created_at, সেটি সমান হলে id. দীর্ঘ রূপটি created_at < ? OR (created_at = ? AND id < ?)-এর সমান। SQLite ৩.১৫ (২০১৬) থেকে এই গঠন আছে, PostgreSQL-এও চলে।

Tie-breaker কেন বাধ্যতামূলক

শুধু created_at দিয়ে cursor বানালে কী হয়? বাজারঘরে একই মিনিটে অনেক পণ্য যোগ হয়। আগের পাতা যদি ০৯:২০-এর একটি পণ্যে থামে, created_at < '09:20' সেই মিনিটের বাকি সব পণ্য ফেলে দেবে। এজন্য ক্রমের শেষে একটি অনন্য কলাম লাগে, যাকে বলে tie-breaker. সাধারণত id. দামে sort করলে এটি আরও জরুরি, কারণ একই দামের হাজারো পণ্য থাকে। আমাদের ডেটায় ৫০ টাকার পণ্যই ৪,৯২৬টি। সত্যিকারের ফল:

সত্যিকারের run · SQLite 3.53.4 · ১০ লাখ সারি · দামে sort, পাতার আকার ৩, প্রথম পাতার শেষ সারি (price 50, id 240)

page 1            id 55, 130, 240          price 50 50 50
price > 50        id 11, 466, 562          price 55 55 55   // 4,923 rows skipped
(price,id) > ...  id 836, 845, 920         price 50 50 50   // correct

৪,৯২৩টি পণ্য কোনো error ছাড়াই হারাল। এবার নিচের স্টেপারে একটি cursor-এর পুরো যাত্রা দেখুন। ১২টি পণ্য, আর সময়ে ইচ্ছা করেই সমান মান রাখা আছে: ১২, ১১ → ০৯:৩০. ১০, ৯, ৮ → ০৯:২০. ৭, ৬, ৫ → ০৯:১০. ৪, ৩, ২, ১ → ০৯:০০.

1const where = c ? '(created_at, id) < (?, ?)' : '';2const rows = query(where, c?.k, limit + 1);3const hasMore = rows.length > limit;4const page = rows.slice(0, limit);5const last = page.at(-1);6const next = hasMore ? encode(last) : null;7// meanwhile: INSERT id 13

এবার নিজে চালিয়ে দেখুন। নিচে ৫০,০০০ পণ্যের একটি টেবিল আপনার browser-এই তৈরি হয়। প্রথমে একই গভীর পাতা দুই পথে, তারপর দামের cursor-এ tie-breaker না থাকার ফল। Query বদলে পরীক্ষাও করতে পারেন।

-- 1) Offset: walk 40,000 rows to reach a deep page
EXPLAIN QUERY PLAN
SELECT id FROM products
ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 40000;
SELECT group_concat(id, ' ') AS offset_page FROM (SELECT id FROM products
  ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 40000);

-- 2) Keyset: the last row of the previous page is the cursor
SELECT id, created_at FROM products
ORDER BY created_at DESC, id DESC LIMIT 1 OFFSET 39999;
EXPLAIN QUERY PLAN
SELECT id FROM products
WHERE (created_at, id) < ('2024-01-03 07:33:00', 10001)
ORDER BY created_at DESC, id DESC LIMIT 3;
SELECT group_concat(id, ' ') AS keyset_page FROM (SELECT id FROM products
  WHERE (created_at, id) < ('2024-01-03 07:33:00', 10001)
  ORDER BY created_at DESC, id DESC LIMIT 3);

-- 3) Price cursor: with and without the tie-breaker
SELECT group_concat(id, ' ') AS page_1 FROM (SELECT id FROM products
  ORDER BY price, id LIMIT 3);
SELECT group_concat(id || ':' || price, ' ') AS no_tie_breaker FROM (
  SELECT id, price FROM products WHERE price > 50 ORDER BY price, id LIMIT 3);
SELECT group_concat(id || ':' || price, ' ') AS with_tie_breaker FROM (
  SELECT id, price FROM products WHERE (price, id) > (50, 600)
  ORDER BY price, id LIMIT 3);

page_1            200 400 600
no_tie_breaker    79:55 279:55 479:55
with_tie_breaker  800:50 1000:50 1200:50

তিনটি সীমাবদ্ধতা আর ফাঁদ

• পাতা ৭-এ লাফ নেই। Cursor শুধু “পরের” আর “আগের” জানে। “মোট কত পাতা” বা “পাতা ৭” চাইলে offset লাগবে।
• NULL: (1, 2) < (1, NULL)-এর ফল সত্য নয়, unknown. তাই NULL থাকা sort কলামে সারি চুপচাপ বাদ পড়তে পারে। Sort কলাম NOT NULL রাখা সবচেয়ে সরল।
• মিশ্র দিক: ORDER BY price DESC, id ASC-এ row value তুলনা চলে না, কারণ দুই কলামের দিক উল্টো। সবচেয়ে সহজ পথ হলো দুই কলামের দিক এক রাখা।
• সময়ের সূক্ষ্মতা: PostgreSQL-এর timestamptz microsecond রাখে, কিন্তু JavaScript-এর Date শুধু millisecond. Date-এ বদলানো সময় দিয়ে cursor বানালে সমান সময়ের কাছাকাছি সারি ভুল হতে পারে। Cursor-এ সময় text হিসেবে রাখুন। আমাদের ডেটা পুরো মিনিটে, তাই run-এ এটি ধরা পড়েনি।

৬ · Opaque cursor, next, prev, Link আর মোট সংখ্যা

Cursor কে পড়বে? কেউ না

?after_id=10&after_time=… লিখলেও চলে, কিন্তু তখন client সেই গঠনের ওপর নির্ভর করে ফেলে। কাল sort বদলালে API ভাঙবে। তাই সাধারণ অভ্যাস হলো cursor-কে opaque রাখা: একটি ছোট JSON-কে base64url করে দেওয়া, আর client-কে বলা, এটি শুধু ফেরত পাঠান। এটি Base64-এর URL-নিরাপদ রূপ। পার্সেলের সিল করা কাগজের মতো ভাবুন: কুরিয়ারের ছেলে সেটি বহন করেন, পড়ে কিছু ঠিক করেন না।

const { createHmac, timingSafeEqual } = require('node:crypto');
const KEY = 'example-secret';
const sign = (b) => createHmac('sha256', KEY).update(b).digest('base64url').slice(0, 16);

function encode(obj) {
  const body = Buffer.from(JSON.stringify(obj)).toString('base64url');
  return `${body}.${sign(body)}`;
}
function decode(text) {
  const [body, mac] = String(text).split('.');
  if (!body || !mac) return null;
  const a = Buffer.from(mac), b = Buffer.from(sign(body));
  if (a.length !== b.length || !timingSafeEqual(a, b)) return null;
  try {
    const c = JSON.parse(Buffer.from(body, 'base64url').toString());
    return Array.isArray(c.k) && c.k.length === 2 ? c : null;
  } catch { return null; }
}

const cursor = encode({ v: 1, s: 'price_asc', k: [100, 1966] });
console.log('cursor :', cursor);
console.log('decode :', JSON.stringify(decode(cursor)));

const body = cursor.split('.')[0];
console.log('peek   :', Buffer.from(body, 'base64url').toString());

const forged = Buffer.from('{"v":1,"s":"price_asc","k":[0,1]}').toString('base64url');
console.log('forged :', decode(`${forged}.${cursor.split('.')[1]}`));
console.log('junk   :', decode('hello'));

cursor : eyJ2IjoxLCJzIjoicHJpY2VfYXNjIiwiayI6WzEwMCwxOTY2XX0.Tp7tyvm9kSu7_UV_
decode : {"v":1,"s":"price_asc","k":[100,1966]}
peek   : {"v":1,"s":"price_asc","k":[100,1966]}
forged : null
junk   : null

Cursor-এ v (গঠনের version) আর s (কোন sort) রাখা হয়েছে। অন্য sort-এর সাথে পুরোনো cursor পাঠালে মান অর্থহীন হয়, তাই server সেটি ফেরায়। ভুল cursor-কে 500 নয়, পরিষ্কার client error বানান। পর্ব ৩৩ (Status code আর error format) এই ছাঁচ নিয়েই কথা বলে।

next আর prev

“আগের পাতা” মানে উল্টো হাঁটা। Cursor-এ দিক (d) রাখা হয়। পেছনে যেতে তুলনার চিহ্ন আর ORDER BY দুটিই উল্টে যায়, আর ফলের সারি আবার উল্টে দেখাতে হয়। আমাদের API দুই দিকের cursor-ই দেয়:

সত্যিকারের run · Express 5.3.0 · ছোট একটি fetch script · cursor ও body ছাঁটা

GET /api/products?limit=2
200
Link: </api/products?limit=2&cursor=eyJ2IjoxLCJz…23fwDMqAzUfbP1cB>; rel="next"
{"data":[{"id":1000000,…},{"id":999999,…}],
 "page":{"limit":2,"hasMore":true,"nextCursor":"eyJ2…","prevCursor":null}}

GET /api/products?limit=2&cursor=eyJ2IjoxLCJz…   (next)
Link: <…cursor=eyJ2…3WlF5lbUQ7ekSMw4>; rel="next", <…cursor=eyJ2…csqDnQ1Mpxlj5ajt>; rel="prev"

p1 1000000 999999 | p2 999998 999997 | back to 1000000 999999

“পেছনে” গিয়ে ঠিক পাতা ১ ফিরে এসেছে, আর সেখানে prevCursor আবার null. React-এ এই cursor সরাসরি infinite query-তে বসে: পর্ব ১৯-এর (Server state) getNextPageParam-এ lastPage.page.nextCursor ফেরালেই হয়, null হলে “আরও দেখুন” থেমে যায়।

Link header

Body-র বদলে বা পাশাপাশি পরের URL HTTP header-এও দেওয়া যায়। Link header-এর নিয়ম RFC 8288-এ (অক্টোবর ২০১৭) লেখা, আর next, prev, first, last IANA-র link relation তালিকায় আছে। Express-এ এর জন্য res.links() আছে, যা ওপরের header বানিয়েছে। সুবিধা হলো body-র গঠন ছাড়াই যেকোনো client পরের পাতা পায়। অসুবিধা হলো browser-এ অন্য origin থেকে পড়তে Access-Control-Expose-Headers লাগে (পর্ব ২৬-এর CORS). বহু দল তাই body-তেই দেয়, দুটিই রাখলেও ক্ষতি নেই।

hasMore বনাম মোট সংখ্যা

“মোট ১,২৫,২৫৫টি পণ্য” দেখাতে ভালো লাগে, কিন্তু এর দাম আছে। সংখ্যাটি জানতে ডেটাবেসকে মেলা সব সারি গুনতে হয়, যেখানে পাতাটি আনতে শুধু প্রথম ২০টি লাগে। “আরও আছে কি না” জানতে গোনার দরকারই নেই: limit + 1টি সারি আনুন। ২১তম সারি এলে hasMore সত্য, আর সেটি কেটে বাদ দিন।

সময় ms-এ · সত্যিকারের run, একটি local কম্পিউটারে একবার · ১০ লাখ সারি · ৯ বারের মধ্যমা

Filter-সহ গোনা এখানে SQLite-এ LIMIT 21-এর চেয়ে প্রায় ৪,৫০০ গুণ, PostgreSQL-এ প্রায় ৯৪ গুণ ধীর। পুরো টেবিলের ক্ষেত্রে SQLite-এর ০.১১ ms দেখে ভুল শিক্ষা নেবেন না। Filter ছাড়া গোনার জন্য তার সংক্ষিপ্ত পথ আছে। PostgreSQL-এর plan-এ একই কাজে Parallel Seq Scan আর ৯,৩৪৬টি page পড়া দেখা গেছে, কারণ MVCC-তে কোন সারি কোন transaction-এ দৃশ্যমান, তা দেখে গুনতে হয়।

বাস্তবের চারটি সিদ্ধান্ত: (ক) hasMore-ই যথেষ্ট, যা feed-এ চলে। (খ) মোট সংখ্যা ঐচ্ছিক, যেমন আমাদের ?total=true. (গ) সীমা দিয়ে গোনা, “১০,০০০+” দেখানো। (ঘ) আনুমানিক মান বা cache করা সংখ্যা। PostgreSQL-এর pg_class.reltuples-এ একটি অনুমান থাকে (run-এ 0.086 ms), তবে এটি শেষ ANALYZE-এর মতোই তাজা। Cache নিয়ে পর্ব ৪৪-এ কথা হবে।

৭ · কোন query-র জন্য কোন index

Cursor-এর গতি জাদু নয়, index-এর দান। Sort-এর ক্রম আর index-এর ক্রম মিললে তবেই ডেটাবেস সোজা নামে। Index-এর তত্ত্ব আবার শেখাব না, সেটি Indexing আর Index ডিজাইন পর্বের কাজ। মনে রাখার নিয়ম একটি: composite index-এ আগে সমতার কলাম, তারপর sort-এর কলাম, শেষে tie-breaker. আমাদের তিনটি index:

CREATE INDEX idx_new     ON products(created_at DESC, id DESC);
CREATE INDEX idx_cat_new ON products(category, created_at DESC, id DESC);
CREATE INDEX idx_price   ON products(price, id);

সত্যিকারের run, একটি local কম্পিউটারে একবার · SQLite 3.53.4 · ১০ লাখ সারি · ৯ বা ৫ বারের মধ্যমা

চতুর্থ সারিটি দেখুন। Index আছে, কিন্তু ক্রম ভুল। সব dal (১,২৫,২৫৫টি) তুলে আলাদা sort করতে হলো, তাই সময় ৫৮ ms. (category, price, id) index যোগ করলে এই সমস্যা যায়। TEMP B-TREE শব্দটিই ধরার চিহ্ন: ডেটাবেস ক্রম পায়নি, নিজে sort করছে। এই অবস্থায় LIMIT 20-ও ছোট কাজ নয়।

দ্বিতীয় সারিটি একটি সূক্ষ্ম কথা বলে। Index ক্রম দিচ্ছে, কিন্তু দামের শর্ত index-এ নেই, তাই মেলা সারি পড়ে পড়ে বাদ দেওয়া হচ্ছে। PostgreSQL-এর plan-এ এটি Rows Removed by Filter: 169 হিসেবে দেখা গেছে। শর্তটি বিরল হলে এই বাদ দেওয়ার সংখ্যা অনেক বেড়ে যেতে পারে। সব সম্ভাব্য filter আর sort-এর জন্য index বানানোও ঠিক নয়, কারণ প্রতিটি index লেখাকে ধীর করে। তাই পরের দুটি অংশে আমরা ঠিক করব API কোন filter আর sort দেবে।

৮ · Filter — query parameter-এর নিয়ম আর নিরাপদ query

Filter মানে দোকানে বলা, “শুধু ১০০ থেকে ১২০ টাকার ডাল আর তেল দেখান।” তালিকার resource একই থাকে, তাই এগুলো path নয়, query string-এ বসে (পর্ব ২৫: Express route আর query). এর কোনো একক মানদণ্ড নেই, তাই একটি রীতি বেছে সব endpoint-এ একই রাখুন।

শেষ সারিটি AI-এর লেখা কোডে প্রায়ই পাবেন। পর্ব ২৭-এ দেখেছেন, Express 5-এর ডিফল্ট simple query parser-এ price[gte] একটি সাধারণ key মাত্র। সবচেয়ে খারাপ হলো filter উপেক্ষা করে চুপচাপ সব ফল ফেরানো, তাই আমাদের schema এমন অজানা key ফিরিয়ে দেয়।

Allowlist: কোন ঘর filter করা যাবে

বিপজ্জনক লোভটি হলো req.query-র প্রতিটি key-কে কলাম ধরে নেওয়া। এতে দুটি সমস্যা। Key-এর নাম SQL-এ ঢুকলে SQL injection-এর পথ খোলে। আর ক্রেতা লুকানো ঘরেও filter করতে পারেন। ধরুন cost_price কলাম উত্তরে দেখানো হয় না, কিন্তু filter খোলা। তাহলে cost_price ৫০০-র কম, ২৫০-এর কম, এভাবে ভাগ করে করে যে কেউ গোপন মান বের করে ফেলতে পারেন। উত্তরে ঘরটি না থাকলেও তথ্য ফাঁস হয়। তাই allowlist: API-তে যেটুকু লেখা, সেটুকুই চলবে।

import { z } from 'zod';

export const SORTS = {
  new:        { col: 'created_at', dir: 'DESC', sql: 'created_at DESC, id DESC' },
  price_asc:  { col: 'price',      dir: 'ASC',  sql: 'price ASC, id ASC' },
  price_desc: { col: 'price',      dir: 'DESC', sql: 'price DESC, id DESC' },
};
const CATS = ['chal', 'dal', 'tel', 'mosla', 'chini', 'lobon', 'ata', 'sabun'];

export const listQuery = z.strictObject({            // unknown keys rejected
  limit: z.coerce.number().int().min(1).max(100).default(20),
  page: z.coerce.number().int().min(1).max(1000).optional(),
  cursor: z.string().max(200).optional(),
  sort: z.enum(Object.keys(SORTS)).default('new'),
  category: z.union([z.enum(CATS), z.array(z.enum(CATS)).max(8)]).optional()
    .transform((v) => (v === undefined ? [] : [v].flat())),
  minPrice: z.coerce.number().int().min(0).optional(),
  maxPrice: z.coerce.number().int().min(0).optional(),
}).refine((q) => !(q.page && q.cursor), { message: 'page আর cursor একসাথে নয়' })
  .refine((q) => q.minPrice === undefined || q.maxPrice === undefined
    || q.minPrice <= q.maxPrice, { message: 'minPrice <= maxPrice হতে হবে' });

z.strictObject অজানা key ফেরায়, z.enum তালিকার বাইরের মান ফেরায়, আর max(8) বিশাল IN তালিকা ঠেকায়। লেখার সময়ে Zod 4.6.7 দিয়ে চালানো সত্যিকারের ফল:

সত্যিকারের run · Express 5.3.0, Zod 4.6.7

GET /api/products?limit=2&category=sona
422 {"code":"VALIDATION_FAILED","errors":[{"path":"category","message":"Invalid input"}]}

GET /api/products?limit=2&costPrice=10
422 {"code":"VALIDATION_FAILED","errors":[{"path":"","message":"Unrecognized key: \"costPrice\""}]}

GET /api/products?limit=2&price[gte]=100
422 {"code":"VALIDATION_FAILED","errors":[{"path":"","message":"Unrecognized key: \"price[gte]\""}]}

GET /api/products?limit=2&minPrice=300&maxPrice=100
422 {"code":"VALIDATION_FAILED","errors":[{"path":"","message":"minPrice <= maxPrice হতে হবে"}]}

GET /api/products?limit=2&page=2&cursor=abc
422 {"code":"VALIDATION_FAILED","errors":[{"path":"","message":"page আর cursor একসাথে নয়"}]}

মনে রাখুন, filter-এ কিছু না মিললে উত্তর 200 আর data: []. “কিছু পাওয়া যায়নি” তালিকার জন্য ভুল নয়, তাই 404 নয়। এবার query তৈরির ধাপগুলো দেখুন। নিয়ম একটাই: SQL-এর লেখা শুধু আমাদের নিজের string থেকে, মান শুধু placeholder দিয়ে। ধরুন অনুরোধটি ?limit=2&category=dal&category=tel&minPrice=100&maxPrice=120&sort=price_asc.

1const where = [], params = [];2if (q.category.length) {3  where.push(`category IN (${marks})`);4  params.push(...q.category);5}6if (q.minPrice !== undefined) { where.push('price >= ?'); params.push(q.minPrice); }7if (q.maxPrice !== undefined) { where.push('price <= ?'); params.push(q.maxPrice); }8const order = SORTS[q.sort].sql;9const sql = `SELECT … WHERE ${where.join(' AND ')} ORDER BY ${order} LIMIT ?`;10db.prepare(sql).all(...params, q.limit + 1);

৯ · Sort, ORDER BY injection আর খোঁজার কথা

Sort-এও একই নীতি, শুধু ফাঁদটি আলাদা। নতুনদের সহজ চিন্তা: “Parameterized query তো শিখেছি, ORDER BY ? লিখি।” চালিয়ে দেখা যাক। প্রত্যাশা, stock অনুযায়ী সাজানো ক্রম।

সত্যিকারের run · একই ৪টি সারি · ORDER BY ?-তে মান 'stock'

SQLite      ORDER BY ?      id 1, 4, 3, 2     stock 271, 371, 298, 56
PostgreSQL  ORDER BY $1     id 1, 2, 3, 4     stock 271, 56, 298, 371
দুটিতেই     ORDER BY stock  id 2, 1, 3, 4     stock 56, 271, 298, 371

কোনো error নেই, কিন্তু ফল sort হয়নি। Placeholder মান বসায়, কলামের নাম নয়। ORDER BY 'stock' মানে প্রতিটি সারিতে একই ধ্রুবক লেখা ধরে sort করা, যা কিছুই করে না। তাই লোকে string জোড়া দেয়, আর সেখানেই বিপদ। ল্যাবের একটি ইচ্ছাকৃত ভুল route (শুধু 127.0.0.1-এ):

// WRONG: lab only. Never ship this.
const sort = String(req.query.sort ?? 'id');
db.prepare(`SELECT id, price FROM products WHERE id <= 5 ORDER BY ${sort}`).all();

সত্যিকারের run · 127.0.0.1 · ইচ্ছাকৃত ভুল route

sort=price                    200 id 1, 4, 5, 3, 2
sort=price DESC               200 id 2, 3, 5, 4, 1     // the client wrote SQL
sort=costPrice                500 {"thrown":"no such column: costPrice"}   // schema leaks
sort=(SELECT 1) * price * -1  200 id 2, 3, 5, 4, 1     // a subquery ran

শেষ লাইনটি নিরীহ দেখালেও মূল সত্য বলে: client-এর লেখা একটি subquery ডেটাবেসে চলেছে। একটি শর্তের উত্তর অনুযায়ী ক্রম বদলালে উত্তরের ক্রম থেকেই গোপন তথ্যের হ্যাঁ বা না জানা যায়। আক্রমণের বিস্তার আর প্রতিরক্ষা Injection প্রতিরক্ষা পর্বে আছে। আমাদের কাজ হলো পথটিই বন্ধ করা: client একটি নাম পাঠাবে, আর আমরা নিজের লেখা SQL টুকরো বেছে নেব।

// Client sends a NAME. We pick our own SQL. The name is never in the SQL.
const SORTS = new Map([
  ['new',        'created_at DESC, id DESC'],
  ['price_asc',  'price ASC, id ASC'],
  ['price_desc', 'price DESC, id DESC'],
]);

function orderBy(name = 'new') {
  const sql = SORTS.get(name);
  if (!sql) return { status: 422, error: `unknown sort: ${name}` };
  return { status: 200, sql: `ORDER BY ${sql}` };
}

const asObject = { new: 'created_at DESC, id DESC' };

for (const input of [undefined, 'price_asc', 'stock', 'price; DROP TABLE products',
  '(SELECT 1)', 'constructor', '__proto__']) {
  console.log(JSON.stringify(input ?? null).padEnd(30), JSON.stringify(orderBy(input)));
}

console.log('\nplain object lookup:');
console.log('constructor ->', typeof asObject['constructor']);
console.log('__proto__   ->', typeof asObject['__proto__']);
console.log('hasOwn      ->', Object.hasOwn(asObject, 'constructor'));

null                           {"status":200,"sql":"ORDER BY created_at DESC, id DESC"}
"price_asc"                    {"status":200,"sql":"ORDER BY price ASC, id ASC"}
"stock"                        {"status":422,"error":"unknown sort: stock"}
"price; DROP TABLE products"   {"status":422,"error":"unknown sort: price; DROP TABLE products"}
"(SELECT 1)"                   {"status":422,"error":"unknown sort: (SELECT 1)"}
"constructor"                  {"status":422,"error":"unknown sort: constructor"}
"__proto__"                    {"status":422,"error":"unknown sort: __proto__"}

plain object lookup:
constructor -> function
__proto__   -> object
hasOwn      -> false

Sort-এর নিয়ম চারটি: নাম allowlist-এ, ক্রমের শেষে অনন্য id, শুধু সেই sort দিন যার index আছে বা টেবিল ছোট, আর ডিফল্ট sort একটি থাকবে। sort=stock আমাদের API-তে সত্যিই ফিরল: 422 … expected one of "new"|"price_asc"|"price_desc". একটি সাধারণ রীতি হলো ?sort=-price, যেখানে মাইনাস মানে উল্টো ক্রম। আমরা স্পষ্ট নাম রেখেছি, কারণ এতে প্রতিটি sort আলাদা করে ভাবা ও index করা হয়।

Search: একটি ছোট নোট

Filter নির্দিষ্ট ঘরে মেলায়, আর search ক্রেতার লেখা শব্দ খোঁজে। name LIKE '%চাল%' লিখলে সামনে % থাকায় index কাজে লাগে না, plan-এ দেখা গেল SCAN products. বড় টেবিলে দরকার full-text search: ডেটাবেসের নিজের ব্যবস্থা, অথবা আলাদা search engine (Meilisearch, OpenSearch এমন কয়েকটি উদাহরণ)।

১০ · AI-এর যুগে pagination

“Express-এ pagination লিখে দাও” বললে AI কয়েক সেকেন্ডে চলে এমন কোড দেয়। সমস্যা হলো এই কোডের ভুল ছোট ডেটায় ধরা পড়ে না। ২০ সারির টেবিলে সব ঠিক থাকে, আর ক্ষতি হয় ১০ লাখে পৌঁছে। তাই যাচাইটি আপনার।

AI-এর লেখা pagination হাতে পেলে — যাচাইয়ের চেকলিস্ট

• Diff-এ দেখেছি, client-এর কোনো string SQL-এর লেখায় যাচ্ছে না, শুধু placeholder-এ
• নকল ডেটা দিয়ে অন্তত কয়েক লাখ সারিতে চালিয়েছি, আর গভীর পাতার সময় দেখেছি
• EXPLAIN পড়েছি: SCAN বা TEMP B-TREE নেই, অথবা কারণ জানি
• পুরো তালিকা হেঁটে test করেছি: প্রতিটি id ঠিক একবার এসেছে
• ?limit=0, ?limit=1000, ?cursor=hello, অজানা sort পাঠিয়ে 500 পাইনি
• Library-র API আমার version-এর docs-এ মিলিয়েছি, আর কোনো গোপন key কোডে নেই