Query Builder Pagination Offset Limit Behavior Across Database Engines

Answer First

CodeIgniter’s limit() and offset() simply emit the database’s native LIMIT / OFFSET syntax. The framework does not impose a universal offset ceiling, nor does it ship a built‑in cursor‑based helper. In practice, deep offsets become expensive on MySQL, PostgreSQL, and SQL Server, and the cost depends on the table’s size, indexes, and the query’s ordering. The safest approach is to avoid large offsets for hot tables and to use keyset (seek) pagination when you need to page past the first few hundred rows.

Why a numeric ceiling is brittle

• MySQL can scan thousands of rows for OFFSET 5000 on a small indexed table but may still need to read the entire table for the same offset on a wide, unfiltered table.
• PostgreSQL’s planner may use a Bitmap Heap Scan for moderate offsets but switches to a full scan once the offset exceeds the number of rows that fit in the buffer pool.
• SQL Server’s OFFSET‑FETCH always walks the result set, so a high offset forces a full sort and scan regardless of indexes.

Keyset (cursor) pagination is the recommended alternative

1. Ensure you have a unique, non‑nullable, indexed column that can serve as the cursor (commonly an auto‑increment id).
2. Query using a deterministic order:

   SELECT * FROM users
   WHERE id > :last_seen_id
   ORDER BY id ASC
   LIMIT :page_size;

3. Store last_seen_id from the last row of the current page and pass it to the next request.
4. Because the cursor is part of the filter, the database can use the index to jump directly to the next page without scanning all preceding rows.

What the framework can and cannot do

• Documentation should note the symptom: “Deep OFFSET pages become slower; use keyset pagination for large tables.” It should not promise a fixed numeric limit.
• No first‑party cursor helper is currently shipped. Teams typically write their own where() + order_by() + limit() chain.
• Automatic rewriting of high‑offset queries into keyset form is risky. The framework can’t reliably infer the correct cursor column or preserve ordering when the query includes complex joins, aggregates, or non‑unique sort keys.

Practical steps to verify performance on your stack

1. Generate the SQL for a representative limit(100)->offset(5000) query and run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the number of rows examined versus returned.
2. Repeat with a keyset query using the same ORDER BY column and compare the logical reads and elapsed time.
3. If you see a large gap between rows examined and rows returned, that offset is a candidate for refactoring to keyset pagination.
4. Consider caching the first N pages if users rarely jump to deep offsets; this hides latency but does not reduce the underlying scan cost.

Follow‑up

If your application frequently pages past the first few hundred rows on a large, frequently updated table, please share the table’s primary key type and any filtering conditions. That information helps decide whether a simple keyset approach will work or whether a more complex cursor strategy is required.