الـ Pagination بالـ Keyset: ليه OFFSET بيبطّأ وبيكرر

CREATE INDEX orders_created_id ON orders (created_at DESC, id DESC);

-- صفحة 1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 0;
-- صفحة 9500
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 190000;

$ psql -q -f seed.sql -f queries.sql
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan using orders_created_id on orders (actual rows=20 loops=1)
         Heap Fetches: 20
(3 rows)

                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan using orders_created_id on orders (actual rows=190020 loops=1)
         Heap Fetches: 190020
(3 rows)

-- صفحة 1 (3 في الصفحة)
SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 0;
-- order جديد وصل وإنتَ بتقرا
INSERT INTO orders (user_id, status, total_cents, created_at) VALUES (1, 'paid', 100, '2027-01-01');
-- صفحة 2
SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 3;

$ psql -q -f seed.sql -f queries.sql
   id   
--------
 200000
 199999
 199998
(3 rows)

   id   
--------
 199998
 199997
 199996
(3 rows)

CREATE INDEX orders_created_id ON orders (created_at DESC, id DESC);

-- keyset: «هات اللي بعد آخر واحد شفته»
-- آخر صف في الصفحة اللي فاتت: created_at = '2026-05-12 22:40', id = 190000
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at FROM orders
WHERE (created_at, id) < ('2026-05-12 22:40+00', 190000)
ORDER BY created_at DESC, id DESC
LIMIT 3;

$ psql -q -f seed.sql -f queries.sql
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Only Scan using orders_created_id on orders (actual rows=3 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-05-12 22:40:00+00'::timestamp with time zone, 190000))
         Heap Fetches: 3
(4 rows)

import express from "express";
import pg from "pg";
import fs from "node:fs";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
await pool.query(fs.readFileSync("seed.sql", "utf8"));
await pool.query("CREATE INDEX orders_created_id ON orders (created_at DESC, id DESC)");

// الـ cursor: آخر (created_at, id) شفناه، متغلّف base64url
const encode = (row) => Buffer.from(JSON.stringify([row.created_at, row.id])).toString("base64url");
const decode = (s) => JSON.parse(Buffer.from(s, "base64url").toString());

const app = express();
app.get("/orders", async (req, res) => {
  const limit = 3;
  const params = [limit + 1];
  let where = "";
  if (req.query.cursor) {
    const [at, id] = decode(req.query.cursor);
    params.push(at, id);
    where = "WHERE (created_at, id) < ($2, $3)";
  }
  const { rows } = await pool.query(
    `SELECT id, created_at FROM orders ${where} ORDER BY created_at DESC, id DESC LIMIT $1`,
    params,
  );
  const page = rows.slice(0, limit);
  const hasMore = rows.length > limit;
  res.json({ items: page.map((r) => r.id), nextCursor: hasMore ? encode(page.at(-1)) : null });
});

app.listen(3000, () => console.log("http://localhost:3000"));

$ node server.js
http://localhost:3000

→ GET /orders
← 200 OK
{
  "items": [
    "200000",
    "199999",
    "199998"
  ],
  "nextCursor": "WyIyMDI2LTA1LTE5VDIxOjE4OjAwLjAwMFoiLCIxOTk5OTgiXQ"
}

→ GET /orders?cursor=WyIyMDI2LTA1LTE5VDIxOjE4OjAwLjAwMFoiLCIxOTk5OTgiXQ
← 200 OK
{
  "items": [
    "199997",
    "199996",
    "199995"
  ],
  "nextCursor": "WyIyMDI2LTA1LTE5VDIxOjE1OjAwLjAwMFoiLCIxOTk5OTUiXQ"
}

import express from "express";
import pg from "pg";
import fs from "node:fs";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
await pool.query(fs.readFileSync("seed.sql", "utf8"));
await pool.query("CREATE INDEX orders_user_total ON orders (user_id, total_cents DESC, id DESC)");

const encode = (r) => Buffer.from(JSON.stringify([r.total_cents, Number(r.id)])).toString("base64url");
function decode(s) {
  try {
    const c = JSON.parse(Buffer.from(s, "base64url").toString());
    return Array.isArray(c) && c.length === 2 && c.every(Number.isInteger) ? c : null;
  } catch {
    return null;
  }
}

const app = express();
app.get("/users/:id/orders", async (req, res) => {
  const limit = Number(req.query.limit ?? 5);
  if (!Number.isInteger(limit) || limit < 1 || limit > 50) {
    return res.status(400).json({ error: "limit من 1 لـ 50" });
  }
  const params = [Number(req.params.id), limit + 1];
  let where = "user_id = $1";
  if (req.query.cursor) {
    const c = decode(req.query.cursor);
    if (!c) return res.status(400).json({ error: "cursor غلط" });
    params.push(...c);
    where += " AND (total_cents, id) < ($3, $4)";
  }
  const { rows } = await pool.query(
    `SELECT id, total_cents FROM orders WHERE ${where} ORDER BY total_cents DESC, id DESC LIMIT $2`,
    params,
  );
  const page = rows.slice(0, limit);
  res.json({
    items: page.map((r) => ({ id: Number(r.id), total: r.total_cents })),
    nextCursor: rows.length > limit ? encode(page.at(-1)) : null,
  });
});

app.listen(3000, () => console.log("http://localhost:3000"));

$ node server.js
http://localhost:3000

→ GET /users/7/orders?limit=2
← 200 OK
{
  "items": [
    {
      "id": 185006,
      "total": 45222
    },
    {
      "id": 135006,
      "total": 45222
    }
  ],
  "nextCursor": "WzQ1MjIyLDEzNTAwNl0"
}

→ GET /users/7/orders?limit=2&cursor=WzQ1MjIyLDEzNTAwNl0
← 200 OK
{
  "items": [
    {
      "id": 85006,
      "total": 45222
    },
    {
      "id": 35006,
      "total": 45222
    }
  ],
  "nextCursor": "WzQ1MjIyLDM1MDA2XQ"
}

→ GET /users/7/orders?cursor=abc
← 400 Bad Request
{
  "error": "cursor غلط"
}

→ GET /users/7/orders?limit=500
← 400 Bad Request
{
  "error": "limit من 1 لـ 50"
}