Why deep offset pages can become expensive
Orders, events and messages are commonly displayed in pages. Offset-based pagination is convenient, but deep pages can require substantial work discarding earlier rows. When visitors normally continue from the last result, a cursor can provide a more useful boundary. This tutorial implements continuation after the last visible key and supplies a local experiment, keeping the behavior inspectable before making any performance claim about a production database.
Ordering must be deterministic. Several events can share the same timestamp or sequence number, so that column alone does not identify a precise page boundary. The example orders by sequence and unique identifier together and creates a corresponding index. Its cursor carries those two values only. Encoding the values is not signing them; applications needing tamper resistance or opaque authorization claims require an additional design rather than treating base64 as protection.
Define a cursor and an unambiguous ordering
The cursor parser checks length, structure and data types. Values reach the database as query parameters, and page size has a defined upper bound. The fixed comparison clause is not assembled from arbitrary user SQL. Real authorization predicates must still remain in the product query. A valid cursor must not grant access to another organization or user's records. This isolated example tests pagination behavior rather than implementing an application's entire access-control boundary.
Save the four files and run python -m unittest test_pages.py, then python demo_pages.py to inspect three groups of four events. An in-memory SQLite database makes the experiment executable without installing a database server. PostgreSQL supports the relevant row-comparison concept too, but that does not imply identical plans or timings. Measure the actual engine, indexes, data distribution and query predicates before applying the technique to an existing service.
The test covers duplicate sort values and an earlier row inserted between two requests. Previously delivered records are not repeated in that scenario, but cursor pagination does not create a permanent snapshot of the whole database. Updating sort keys or deleting rows can alter what is observed later. Audit reports requiring a fixed view need a separate transaction and isolation strategy, including an explicit decision about how long that view is retained.
Complete executable files
The four files are complete. The article provides prerequisites, command order and verification; code is neither shortened nor hidden.
1. Complete schema.sql
Save this entire file beside the others using the exact filename.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
sequence INTEGER NOT NULL,
label TEXT NOT NULL
);
CREATE INDEX events_cursor_idx ON events(sequence, id);The execution section explains this file’s command, expected outcome and limits.
2. Complete pages.py
Save this entire file beside the others using the exact filename.
import base64
import json
def encode_cursor(row):
data = json.dumps([row[1], row[0]], separators=(',', ':')).encode()
return base64.urlsafe_b64encode(data).decode()
def decode_cursor(cursor):
if len(cursor) > 128:
raise ValueError('Cursor too long')
try:
values = json.loads(base64.b64decode(cursor, altchars=b'-_', validate=True))
except (ValueError, TypeError) as error:
raise ValueError('Invalid cursor') from error
if not isinstance(values, list) or len(values) != 2 or any(type(v) is not int or v < 0 for v in values):
raise ValueError('Cursor requires two nonnegative integers')
return values
def page(connection, after=None, size=20):
if type(size) is not int or not 1 <= size <= 100:
raise ValueError('Page size must be 1..100')
params = []
where = ''
if after is not None:
where = 'WHERE (sequence, id) > (?, ?)'
params.extend(decode_cursor(after))
rows = connection.execute(f'SELECT id, sequence, label FROM events {where} ORDER BY sequence, id LIMIT ?', [*params, size]).fetchall()
return {'rows': rows, 'next': encode_cursor(rows[-1]) if rows else None}The execution section explains this file’s command, expected outcome and limits.
3. Complete demo_pages.py
Save this entire file beside the others using the exact filename.
from pathlib import Path
import sqlite3
from pages import page
if __name__ == '__main__':
with sqlite3.connect(':memory:') as connection:
connection.executescript(Path(__file__).with_name('schema.sql').read_text())
connection.executemany('INSERT INTO events VALUES (?, ?, ?)', [(i, i // 3, f'event-{i}') for i in range(1, 13)])
cursor = None
while True:
result = page(connection, cursor, size=4)
if not result['rows']:
break
print(result)
cursor = result['next']The execution section explains this file’s command, expected outcome and limits.
4. Complete test_pages.py
Save this entire file beside the others using the exact filename.
from pathlib import Path
import sqlite3
import unittest
from pages import decode_cursor, page
class PaginationTests(unittest.TestCase):
def test_duplicate_sort_keys_and_concurrent_insert(self):
with sqlite3.connect(':memory:') as connection:
connection.executescript(Path(__file__).with_name('schema.sql').read_text())
connection.executemany('INSERT INTO events VALUES (?, 1, ?)', [(i, str(i)) for i in range(1, 7)])
first = page(connection, size=3)
connection.execute("INSERT INTO events VALUES (0, 0, 'earlier')")
second = page(connection, first['next'], size=3)
self.assertEqual([r[0] for r in first['rows'] + second['rows']], list(range(1, 7)))
self.assertEqual(page(connection, second['next'])['rows'], [])
def test_invalid_cursor_and_size(self):
with self.assertRaises(ValueError):
decode_cursor('not-json')
with self.assertRaises(ValueError):
page(None, size=0)
if __name__ == '__main__':
unittest.main()The execution section explains this file’s command, expected outcome and limits.
Run the example and match the product requirement
A cursor supports continuation, but does not automatically provide a direct jump to page one thousand or an exact total count. This demonstration may make one final empty request to discover the end. Define product requirements before choosing the approach, then examine plans, response times and concurrent writes. A possible efficiency gain should not arrive with unexplained changes to ordering or missing results that visitors reasonably expected to see.
Implementation checklist
- Complete ordering with a unique key.
- Validate cursors and page size.
- Preserve real authorization predicates.
- Test concurrent writes and snapshot requirements separately.
References
Editorial guidance: Liyan Knowledge.
