Keyset Pagination

Traditional OFFSET and LIMIT pagination becomes extremely slow on large datasets because the database must scan and count all previous rows. pg-smart-search v1.2+ introduces Keyset Pagination to solve this.

How Keyset Pagination Works

Instead of skipping rows, keyset pagination uses a cursor (typically the ID of the last retrieved item) to fetch the next batch.

-- Instead of this (Slow)
SELECT * FROM products WHERE name % laptop OFFSET 10000 LIMIT 20;

-- Do this (Fast)
SELECT * FROM products WHERE name % laptop AND id > last_seen_id LIMIT 20;

This guarantees constant O(log N) lookup time regardless of how deep you are in the pagination.

Implementation

Pass the cursor parameter to the search method:

// First page
const firstPage = await engine.search({
  query: "laptop",
  limit: 20,
});

// Next page
const nextPage = await engine.search({
  query: "laptop",
  limit: 20,
  cursor: firstPage.pagination.nextCursor, // Derived from the last result
});

Optimizing Total Count Queries (skipTotalCount)

In datasets with millions of rows, calculating the exact total number of matching records (COUNT(*) OVER()) under standard SQL window functions is extremely expensive and slows down deep page navigation.

pg-smart-search v1.3.1+ solves this by introducing the skipTotalCount search option.

When set to true, the total record count calculation is skipped for all pages after the first page, returning 0 as the total count. This reduces database workload on deep pages by up to 90%:

const nextPage = await engine.search({
  query: "laptop",
  limit: 20,
  cursor: lastCursor,
  skipTotalCount: true, // Speeds up deep pagination
});