遊標分頁可能漏掉資料：JavaScript Date 與 PostgreSQL 微秒精度差異

Keyset (cursor) pagination is the standard fix for slow OFFSET queries. You sort by created_at plus id, encode the last row into a cursor, and ask for "rows older than this" on the next page.

There's a bug in the most common way people build that cursor in Node, and it doesn't fail loudly. Rows just quietly go missing. In the worst case, pagination ends after the first page.

The usual implementation

const { rows } = await db.query(
  `SELECT * FROM events
    WHERE ($2::timestamptz IS NULL OR (created_at, id) < ($2::timestamptz, $3))
    ORDER BY created_at DESC, id DESC
    LIMIT $1`,
  [first + 1, afterTs, afterId],
);

const last = rows[first - 1];
const endCursor = Buffer.from(`${last.created_at.toISOString()}|${last.id}`).toString('base64url');

It looks right. There's a tiebreaker on id, the sort direction matches the comparison, and the cursor is opaque. It passes a quick manual test.

The problem

Postgres timestamptz stores microseconds. node-postgres parses it into a JavaScript Date, which stores milliseconds.

new Date('2026-10-29 02:30:24.156216+05').toISOString()
// '2026-10-28T21:30:24.156Z'   <- the 216 microseconds are gone

So the cursor says …24.156, but the row it came from is at …24.156216. That row, and every other row inside the same millisecond, is now newer than the cursor. The next page asks for rows older than …24.156 and skips all of them.

How bad it gets

If rows land in different milliseconds, you lose the occasional row: annoying, and very hard to notice. But look at what happens with a normal batch insert, where every row gets the same now() because now() is fixed for the whole transaction:

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  title text
);

BEGIN;
INSERT INTO events (title) SELECT 'event ' || g FROM generate_series(1, 10) g;
COMMIT;

All 10 rows have created_at = 2026-10-29 02:30:24.156216+05. Page 1 (LIMIT 3) returns ids 10, 9 and 8. Now page 2:

-- cursor built from a JS Date (millisecond precision)
SELECT count(*) FROM events
 WHERE (created_at, id) < (date_trunc('milliseconds', '2026-10-29 02:30:24.156216+05'::timestamptz), 8);
-- 0

-- cursor built from the exact Postgres value
SELECT count(*) FROM events
 WHERE (created_at, id) < ('2026-10-29 02:30:24.156216+05'::timestamptz, 8);
-- 7

Zero rows. The client sees an empty page with hasNextPage: false, and 7 of the 10 events are unreachable. No error, nothing in the logs.

Imports, seed scripts, fan-out jobs that create a notification for every user: anything that writes many rows in one transaction triggers it.

The fix

Never round-trip the timestamp through a JS Date. Ask Postgres for the exact text and put that in the cursor:

const { rows } = await db.query(
  `SELECT *, created_at::text AS cursor_ts
     FROM events
    WHERE ($2::timestamptz IS NULL OR (created_at, id) < ($2::timestamptz, $3))
    ORDER BY created_at DESC, id DESC
    LIMIT $1`,
  [first + 1, afterTs, afterId],
);

const encodeCursor = (r: { cursor_ts: string; id: string }) =>
  Buffer.from(`${r.cursor_ts}|${r.id}`).toString('base64url');

created_at::text keeps full precision and casts back to the identical timestamptz, so the comparison is exact.

Other options, depending on your setup:

• Make the column millisecond precision: timestamptz(3). Rows in the same millisecond will then tie, so the id tiebreaker handles it correctly. This needs a migration on an existing table.
• Paginate on id alone if ids are monotonic (bigserial, UUIDv7) and "newest first" by id is acceptable.
• Parse timestamps as strings in node-postgres (types.setTypeParser(1184, v => v)). This works, but it changes the type everywhere, which is a bigger change than it looks.

Catch it with a test

This is the test that found it for me. Insert more rows than one page, walk every page, and check nothing is missing or repeated:

it('pages through every row exactly once', async () => {
  for (let n = 1; n <= 5; n++) {
    await db.query('INSERT INTO events (title) VALUES ($1)', [`event ${n}`]);
  }

  const seen: string[] = [];
  let after: string | null = null;
  do {
    const page = await listEvents({ first: 2, after });
    seen.push(...page.nodes.map((n) => n.title));
    after = page.pageInfo.hasNextPage ? page.pageInfo.endCursor : null;
  } while (after);

  expect(seen).toHaveLength(5);
  expect(new Set(seen).size).toBe(5);
});

Against a real Postgres, this failed for me with only 3 of 5 rows returned, even though every insert was a separate statement. Inserts that run quickly one after another regularly land in the same millisecond. A mocked database would never have caught it, because the mock would happily keep millisecond timestamps.

Checklist for keyset pagination

• Sort by your field plus a unique tiebreaker (created_at DESC, id DESC)
• An index that matches that exact order: (filter_col, created_at DESC, id DESC)
• A cursor that holds the exact database value, not a JS Date
• first capped (100 is typical) and invalid cursors rejected as a client error, not a 500
• A test that walks every page against a real database

I hit this while building a set of Claude Code skills for TypeScript + GraphQL + Postgres backends, tested against a real starter backend in CI. The pagination skill now bakes this fix in, so Claude writes it correctly the first time. Two of the skills are free on GitHub.