Pagination — offset va cursor based

~20 daq · Express · API Arxitekturasi

Muammodan boshlaylik

Ma'lumotlar bazasida 500,000 ta mahsulot bor. Siz shunday yozdingiz:

app.get('/products', async (req, res) => {
  const products = await Product.find(); // ← HAMMASI
  res.json(products);
});

Nima bo'ladi? 500,000 ta yozuv xotiraga yuklanadi. So'rov 30-60 soniya ishlaydi. Server RAM tugaydi. Boshqa so'rovlar kutib qoladi. Brauzer 200 MB JSON ni qayta ishlaydi.

Pagination — ma'lumotni bo'laklarga bo'lib, birin-ketin berish.

1. Offset-based Pagination

Bu nima?

"Nechta o'tkazib yubor, nechtasini ber" — sahifa raqami asosida.

Hayotiy o'xshatma

Kitob kutubxonasi. Siz 201-250 kitobni ko'rmoqchisiz. Kutubxonachi 200 ta o'tkazib yuboradi, keyingi 50 tasini beradi. Har safar yangi sahifa so'rasangiz — shu qadar o'tkazib yuboradi.

// GET /products?page=3&limit=20
// → 3-sahifa, har sahifada 20 ta

router.get('/products', async (req, res, next) => {
  try {
    const page  = Math.max(1, parseInt(req.query.page)  || 1);  // ← manfiy bo'lmasin
    const limit = Math.min(100, parseInt(req.query.limit) || 10); // ← max 100
    const skip  = (page - 1) * limit;                            // ← nechta o'tkazish

    const [products, total] = await Promise.all([
      Product.find().skip(skip).limit(limit),
      Product.countDocuments(),                                   // ← umumiy son
    ]);

    const totalPages = Math.ceil(total / limit);

    res.json({
      success: true,
      data: products,
      meta: {
        page,
        limit,
        total,
        totalPages,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1,
      },
    });
  } catch (err) {
    next(err);
  }
});

So'rov misoli:

GET /products?page=1&limit=10   → 1-10
GET /products?page=2&limit=10   → 11-20
GET /products?page=5&limit=20   → 81-100

Offset-based ning kamchiliklari

Muammo 1: "Sahifa siljishi" (Page drift)

Siz 2-sahifani ko'ryapsiz (11-20 mahsulot). Shu payt biror narsa 5-mahsulotni o'chirdi. Endi siz 2-sahifani so'rasangiz — 12-21 keladi. 11-mahsulot yo'qoldi, 21-mahsulot ikki marta ko'rindi.

Muammo 2: Katta offset sekin ishlaydi

SELECT * FROM products SKIP 100000 LIMIT 10

Ma'lumotlar bazasi 100,000 yozuvni o'qib, tashlab ketadi — keyin 10 tasini beradi. Sahifa raqami kattalashgani sari so'rov sekinlashadi.

2. Cursor-based Pagination

Bu nima?

"Oxirgi ko'rgan elementimdan keyin bering" — ID yoki vaqt asosida.

Hayotiy o'xshatma

Instagram lentasi. Pastga aylantirganingizda yangi postlar yuklanadi. Instagram "52-postdan keyin 10 ta ber" demaydi. U "ID=xyz dan keyin yaratilgan 10 ta ber" deydi. Yangi post qo'shilsa yoki o'chirilsa — lenta to'g'ri ishlaydi.

// GET /products?cursor=64f1a2b3c4d5e6f7a8b9c0d1&limit=10
// cursor — oxirgi ko'rgan mahsulotning _id si

router.get('/products', async (req, res, next) => {
  try {
    const limit  = Math.min(100, parseInt(req.query.limit) || 10);
    const cursor = req.query.cursor; // ← oxirgi element ID si

    const filter = cursor
      ? { _id: { $gt: cursor } }    // ← shu ID dan KEYIN
      : {};                          // ← birinchi sahifa

    const products = await Product.find(filter)
      .sort({ _id: 1 })             // ← ID bo'yicha tartib (muhim!)
      .limit(limit + 1);            // ← 1 ta ko'proq olamiz (keyingi bor-yo'qligini bilish uchun)

    const hasNextPage = products.length > limit;
    const items = hasNextPage ? products.slice(0, limit) : products; // ← ortiqchasini olib tashlaymiz

    const nextCursor = hasNextPage
      ? items[items.length - 1]._id  // ← oxirgi elementning ID si
      : null;

    res.json({
      success: true,
      data: items,
      meta: {
        limit,
        hasNextPage,
        nextCursor,                  // ← frontend keyingi so'rovda shu ni yuboradi
      },
    });
  } catch (err) {
    next(err);
  }
});

So'rov ketma-ketligi:

1. GET /products?limit=10
   → data: [id:1..10], nextCursor: "id_10"

2. GET /products?cursor=id_10&limit=10
   → data: [id:11..20], nextCursor: "id_20"

3. GET /products?cursor=id_20&limit=10
   → data: [id:21..30], nextCursor: "id_30"

limit+1 hiylasi nima uchun?

Agar "keyingi sahifa bor-yo'q"ini bilmoqchi bo'lsak — ikki variant:

1. countDocuments() — qo'shimcha so'rov, sekin
2. limit+1 ta olib ko'rish — agar limit+1 ta kelsa, keyingi sahifa bor

Bitta so'rov bilan hal bo'ladi.

Vaqt asosida cursor (yangi lenta uchun)

Ko'pincha yangi postlar birinchi ko'rsatiladi — createdAt bo'yicha teskari tartib:

router.get('/posts', async (req, res, next) => {
  try {
    const limit  = Math.min(50, parseInt(req.query.limit) || 20);
    const cursor = req.query.cursor; // ← oxirgi post ning createdAt (ISO string)

    const filter = cursor
      ? { createdAt: { $lt: new Date(cursor) } } // ← shu vaqtdan OLDIN
      : {};

    const posts = await Post.find(filter)
      .sort({ createdAt: -1 })       // ← yangi → eski
      .limit(limit + 1)
      .populate('author', 'name avatar');

    const hasNextPage = posts.length > limit;
    const items = hasNextPage ? posts.slice(0, limit) : posts;

    const nextCursor = hasNextPage
      ? items[items.length - 1].createdAt.toISOString()
      : null;

    res.json({
      success: true,
      data: items,
      meta: { hasNextPage, nextCursor },
    });
  } catch (err) {
    next(err);
  }
});

Qaysi birini tanlash?

Offset — sahifa raqami ko'rsatiladigan joylar: admin panel, qidiruv natijalari.

Cursor — cheksiz aylantiriladigan joylar: ijtimoiy tarmoq lentasi, xabarlar tarixi, bildirishnomalar.

SQL (PostgreSQL) da pagination

const { Pool } = require('pg');
const pool = new Pool();

// Offset-based
router.get('/users', async (req, res, next) => {
  try {
    const page  = Math.max(1, parseInt(req.query.page) || 1);
    const limit = Math.min(100, parseInt(req.query.limit) || 10);
    const offset = (page - 1) * limit;

    const [dataResult, countResult] = await Promise.all([
      pool.query('SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2', [limit, offset]),
      pool.query('SELECT COUNT(*) FROM users'),
    ]);

    const total = parseInt(countResult.rows[0].count);

    res.json({
      success: true,
      data: dataResult.rows,
      meta: {
        page, limit, total,
        totalPages: Math.ceil(total / limit),
      },
    });
  } catch (err) {
    next(err);
  }
});

// Cursor-based (ID asosida)
router.get('/messages', async (req, res, next) => {
  try {
    const limit  = Math.min(50, parseInt(req.query.limit) || 20);
    const cursor = req.query.cursor;

    const query = cursor
      ? 'SELECT * FROM messages WHERE id > $1 ORDER BY id ASC LIMIT $2'
      : 'SELECT * FROM messages ORDER BY id ASC LIMIT $1';

    const params = cursor ? [cursor, limit + 1] : [limit + 1];
    const result = await pool.query(query, params);

    const hasNextPage = result.rows.length > limit;
    const items = hasNextPage ? result.rows.slice(0, limit) : result.rows;

    res.json({
      success: true,
      data: items,
      meta: {
        hasNextPage,
        nextCursor: hasNextPage ? items[items.length - 1].id : null,
      },
    });
  } catch (err) {
    next(err);
  }
});

Tez-tez uchraydigan xatolar

1. limit ni cheklamaslik

// ❌
const limit = parseInt(req.query.limit); // ← foydalanuvchi 999999 yuborsa?

// ✅
const limit = Math.min(100, parseInt(req.query.limit) || 10); // ← maksimal 100

Sababi: limit=1000000 yuborilsa — million yozuv xotiraga yuklanadi, server crash.

2. Cursor ni tekshirmaslik

// ❌ — cursor noto'g'ri ID bo'lsa Mongoose xato chiqaradi
const filter = { _id: { $gt: req.query.cursor } };

// ✅ — mongoose.Types.ObjectId bilan tekshirish
const mongoose = require('mongoose');

if (cursor && !mongoose.Types.ObjectId.isValid(cursor)) {
  return res.status(400).json({ error: 'Noto\'g\'ri cursor' });
}

3. Tartiblashsiz cursor pagination

// ❌ — sort yo'q
Product.find({ _id: { $gt: cursor } }).limit(10);
// Natija: tasodifiy tartibda, cursor ishlashiga kafolat yo'q

// ✅ — sort majburiy
Product.find({ _id: { $gt: cursor } }).sort({ _id: 1 }).limit(10);

Sababi: cursor pagination faqat tartibli ma'lumot bilan to'g'ri ishlaydi. Tartiblashsiz — bir element ikki marta, biri umuman ko'rinmasligi mumkin.

4. countDocuments ni cursor pagination da ishlatish

// ❌ — cursor pagination da umumiy son ma'nosiz
const total = await Product.countDocuments();
// "Umumiy 50,000 ta, lekin qaysi sahifadasiz?" — cursor da sahifa yo'q

Sababi: cursor pagination da "sahifa raqami" tushunchasi yo'q. hasNextPage va nextCursor yetarli.

Xulosa

• Offset — skip + limit, sahifa raqami bor, katta ma'lumotda sekin
• Cursor — oxirgi elementdan keyingisini ber, real-time lenta uchun ideal
• limit+1 hiyla — qo'shimcha countDocuments so'rovisiz hasNextPage aniqlanadi
• limit ni har doim cheklang — Math.min(100, limit)
• Cursor pagination da sort majburiy

Keyingi dars: 39-dars: Filtering va Sorting — query params bilan