In the previous article, we learned how to implement offset and cursor pagination using SQL and Knex. Now, we will benchmark these paging techniques to find out which one is better and discuss their pros and cons.

Benchmarks

For benchmarking, we are using the artist table from the MusicBrainz database. We are using Knex-Nest – pagination included Knex module for NestJS – for benchmarking.

Offset Pagination

 async getOffsetPagingBenchmarks(
    from = 1,
    to = 10,
    perPage = 50,
    total?: number
  ) {
    let avgQueryTimeOffset = 0;
    for (let index = from; index <= to; index += 1) {
      const tag = `Page ${index}`;
      const start = hrtime.bigint();
      const query = this.knexPg('artist')
        .select('id', 'name')
        .orderBy('id', 'asc');
      await KnexPagination.offsetPaginate({
        query: query,
        perPage: perPage,
        goToPage: index,
        count: total,
      });
      const end = hrtime.bigint();
      const latency = round(Number(end - start) / 1000000);
      avgQueryTimeOffset += latency;
      console.log(`${tag}: ${latency} ms`);
    }
    avgQueryTimeOffset = round(avgQueryTimeOffset / 10);
    console.log(`Average (offset): ${avgQueryTimeOffset} ms`);
    return avgQueryTimeOffset;
  }

Cursor Pagination

 async getCursorPagingBenchmarks(
    from = 'start',
    numOfPages = 10,
    perPage = 50
  ) {
    let avgQueryTimeCursor = 0;
    let cursor = null;
    let index = 0;
    while (numOfPages > 0) {
      const tag = `Page ${index++}`;
      const start = hrtime.bigint();
      const query = this.knexPg('artist').select('id', 'name');
      const result = await KnexPagination.cursorPaginate({
        query: query,
        perPage: perPage,
        cursor: {
          key: 'id',
          order: 'asc',
          value: cursor,
          direction: from === 'start' ? 'next' : 'prev',
        },
      });
      if (result.pagination.cursor) {
        cursor = result.pagination.cursor.next_cursor;
      }
      const end = hrtime.bigint();
      const latency = round(Number(end - start) / 1000000);
      avgQueryTimeCursor += latency;
      console.log(`${tag}: ${latency} ms`);
      numOfPages -= 1;
    }
    avgQueryTimeCursor = round(avgQueryTimeCursor / 10);
    console.log(`Average (cursor): ${avgQueryTimeCursor} ms`);
    return avgQueryTimeCursor;
  }

Let us fetch the first 10 pages with 50 records per page and see what results we get:

async getBenchmarks() {
    return {
      'Average Time (Offset)': (await this.getOffsetPagingBenchmarks()) + ' ms',
      'Average Time (Cursor)': (await this.getCursorPagingBenchmarks()) + ' ms',
    };
  }

{
    "Average Time (Offset)": "96.49 ms",
    "Average Time (Cursor)": "0.7 ms"
}

As you can see, offset pagination is almost 137x times slower than cursor pagination even when we are just fetching the first ten pages. The reason is that for each page a count query is executed and its cost is proportional to the number of records. As there are around 1.9 million entries in artist table, so we are seeing a lot of performance differences.

For a fair comparison, we need to fix that performance issue with offset pagination first. We can either store the row count in a separate table and update it whenever a row is added or deleted, or we can cache it somewhere. Some databases maintain row count internally, so refer to their docs for that.

async getBenchmarks() {
    const total = getArtistsCount();
    return {
      'Average Time (Offset)':
        (await this.getOffsetPagingBenchmarks(1, 10, 50, total)) + ' ms',
      'Average Time (Cursor)':
        (await this.getCursorPagingBenchmarks('start', 10, 50)) + ' ms',
    };
}

{
    "Average Time (Offset)": "0.79 ms",
    "Average Time (Cursor)": "0.76 ms"
}

Now, the performance difference is almost negligible after optimisation. For a very small table, you can ignore this optimisation but otherwise, you should always maintain a row count if you ever plan to use offset pagination.

We fetch the last 10 pages with 50 records per page

async getBenchmarks() {
    const total = getArtistsCount();
    return {
      'Average Time (Offset)':
        (await this.getOffsetPagingBenchmarks(38837, 38847, 50, total)) + ' ms',
      'Average Time (Cursor)':
        (await this.getCursorPagingBenchmarks('end', 10, 50)) + ' ms',
    };
}

{
    "Average Time (Offset)": "1734.32 ms",
    "Average Time (Cursor)": "0.78 ms"
}
  1. The time taken by cursor pagination remained the same, but the performance of the offset pagination dropped to a point where it became impractical and inefficient to use. As the page number increases, so does the time it takes to fetch that page because the OFFSET clause has to traverse through all the preceding pages to reach the current page. In the above example, when the last page was requested, the offset pagination traversed through all the 1.9 million records to get the last 50 records while the cursor pagination got the last 50 records directly. That’s the main reason why cursor pagination outperformed offset pagination by a huge margin.

Pros and Cons

Offset Pagination

  • Pros
    • Easier to implement
    • Can jump to a specific page
    • Can sort data by any column(s)
  • Cons
    • Not scalable
    • Not suitable for frequently changing data as page positions may shift when a row is added or deleted from the table

Cursor Pagination

  • Pros
    • Scalable to any database size
    • Works very well with frequency changing data
  • Cons
    • Can’t jump to a specific page
    • Sorting is very limited as it needs a unique sequential column as a cursor

Conclusion

As a rule – always use cursor pagination. It is scalable and efficient for any database size. But if you must use offset pagination then use it only when the dataset is very small such as search results, blog posts etc otherwise it would lead to degraded performance and slower page loads.

Categorized in:

Database

Tagged in:

, ,