Introduction
When implementing a paginated list screen in a business application, the first idea that comes to mind is usually "fetch rows with OFFSET/LIMIT."
But once you actually run it, the problems pile up fast: "The same row appeared again on the next page," "A record I saw earlier is gone," "Page 1000 takes forever to load." OFFSET/LIMIT alone can't solve all of these.
I've been adding pagination support to a Java framework I'm building from scratch, which prompted me to revisit and organize the options. This article walks through four pagination strategies I've actually used in enterprise projects, along with their trade-offs.
What Pagination Needs to Get Right
Before diving in, let's define what a solid pagination implementation should provide:
- Correct data — no missing rows, no duplicates
- Consistency — minimal interference from other users' updates
- Acceptable DB load
- User-controlled sort order
Two phenomena are worth calling out specifically:
- Row shift — When another user inserts or deletes a row between page loads, rows that should appear on the next page are skipped, or rows from the previous page appear again.
- Stale read — The list keeps showing snapshot data from the initial query and doesn't reflect updates made by other users during navigation.
Here's an example of row shift. You're viewing page 1 of 10 rows per page, then another user deletes ID:03:
Normal
--- When page 1 loads --- → --- When page 2 loads ---
ID: 01 (sort rank 1) ID: 11 (sort rank 11)
ID: 02 ID: 12
ID: 03 ID: 13
... ...
ID: 10 (sort rank 10) ID: 20
↓ Another user deletes ID:03 before page 2 loads:
--- When page 2 loads ---
ID: 12 ← ID:11 moved to page 1 due to the deletion; not shown on page 2
ID: 13
ID: 14
...
ID: 21
No single strategy perfectly satisfies every requirement — you'll always be choosing a trade-off based on your project's priorities.
When baking this into a framework, two additional constraints also matter:
- Implementation transparency — simple enough to use without ceremony
- DBMS portability — works across different database engines
Strategy 1: Per-Request DB Access — Row Number (OFFSET)
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 20 -- page 3 (10 rows per page)
The simplest approach. Each page load tells the DB "give me M rows starting from row N."
Drawbacks
-
DBMS-specific syntax. Oracle uses
FETCH FIRST N ROWS ONLY, MySQL usesLIMIT ... OFFSET, SQL Server usesOFFSET ... FETCH NEXT, and so on. - High DB load at large offsets. The DB must scan and skip all rows before the offset — the further you page, the more work it does.
- Row shift occurs due to concurrent insertions or deletions. (See the diagram above.)
Strategy 2: Per-Request DB Access — Application-Side Scrolling
Instead of delegating the offset to the DB, the application reads from the beginning of the ResultSet and skips rows itself until it reaches the desired page.
Java implementation sketch (JDBC):
try (PreparedStatement ps = con.prepareStatement("SELECT * FROM orders ORDER BY id");
ResultSet rs = ps.executeQuery()) {
// Skip the first N rows in the application
for (int i = 0; i < n; i++) {
if (!rs.next()) {
break; // No more data
}
}
// Collect M rows and return
...
}
Like Strategy 1, this queries the DB on every page load. But because no OFFSET is sent to the DB, the same code works across any DBMS.
Drawbacks
- App-side processing cost grows with page depth. Viewing page 100 means skipping 990 rows in the application.
- Row shift happens in the same way as Strategy 1.
Variation: Key-based cursor
If you store the last row's key value from the previous page and filter withWHERE id > :lastId, you can reduce the number of skipped rows and also mitigate row shift. The downside: changing sort order or jumping directly to an arbitrary page becomes difficult, and it's hard to encapsulate inside a framework.
Strategies 1 and 2 are in the "query the DB on every request" group. Strategies 3 and 4 belong to a different group: "query the DB once, then cache the result."
Strategy 3: Single DB Query — Work File Cache
On the first search, fetch all matching rows from the DB and write them to a file. Subsequent page loads read from that file.
[First load] DB → fetch all rows → write to file → display page 1
[Later loads] read from file → display page N
Benefits
- No DB access after the first load — low DB load for navigation
- Consistency is guaranteed during navigation (no row shift)
Drawbacks
- Stale reads occur. The list shows a snapshot from the initial query; updates by other users are not reflected during navigation.
- Multi-server deployments are problematic. Files written on one web server aren't visible on others — shared or object storage is needed.
- When do you delete the files? You need a cleanup strategy: on session end, on logout, after a timeout, etc.
- Files multiply quickly. Each concurrent user creates their own file.
- Large first-load queries can be heavy on memory and I/O (capping the result size helps).
Strategy 4: Single DB Query — Work Table Cache
On the first search, fetch all matching rows and INSERT them into a dedicated work table. Subsequent page loads SELECT from that table.
[First load] DB → fetch all rows → INSERT into work table → SELECT from work table → display page 1
[Later loads] SELECT from work table → display page N
This is Strategy 3 with files replaced by a database table. The mechanics are essentially the same.
Benefits
- Same consistency guarantee as Strategy 3 — no row shift
- Works fine in multi-server setups. No need to worry about where files land.
- Pagination is just INSERT + SELECT
- Row numbers can be assigned at INSERT time, making direct page jumps straightforward
Drawbacks
- Stale reads occur, just like Strategy 3.
- When do you purge the work table? A periodic cleanup job or session-lifecycle integration is required.
- Heavy concurrent usage bloats the work table. Each active user adds their own rows.
- Each search type needs its own work table schema.
Comparison Summary
Per-Request DB Access (Strategies 1 & 2)
Other users' updates are reflected in real time (no stale reads), but row shift can occur.
| Strategy 1: OFFSET | Strategy 2: App-side scrolling | |
|---|---|---|
| Load location | DB (OFFSET scan) | App (skip loop) |
| DBMS portability | Syntax varies by DBMS | Works with any DBMS |
| Row shift | Yes | Yes |
| Stale read | No | No |
Single DB Query + Cache (Strategies 3 & 4)
The snapshot from the first query is preserved, so no row shift. But stale reads do occur.
| Strategy 3: Work file | Strategy 4: Work table | |
|---|---|---|
| Pagination mechanism | File I/O | INSERT / SELECT |
| Multi-server support | Needs shared storage | No issue |
| Cleanup design needed | Yes | Yes |
| Row shift | No | No |
| Stale read | Yes | Yes |
Which Strategy Fits Which Situation?
| Situation | Recommended Strategy |
|---|---|
| Rarely updated reference data (master lists, etc.) | Strategy 1 (OFFSET) — simplest option |
| Workflows where minor row shift is acceptable | Strategy 1 or 2 |
| DBMS without OFFSET support, or DBMS-agnostic requirement | Strategy 2 (app-side scrolling) |
| Consistency is critical (approval flows, reports, etc.) | Strategy 3 or 4 |
| Multi-server deployment | Strategy 4 (work table) |
Work file and work table strategies win on consistency, but the cleanup design adds non-trivial operational complexity.
Wrapping Up
That's a rundown of four pagination strategies. There's no universally "correct" answer — the right choice depends on your project's characteristics.
For my own framework, DBMS portability is a priority, so I'm leaning toward Strategy 2 (app-side scrolling) as the base implementation. But honestly, I'm still mulling it over. If you've handled pagination in a real project — or know of an approach I missed — I'd love to hear about it in the comments!
SIcore Framework Links
My custom framework is published here.
- HP: https://onepg.com
- GitHub: https://github.com/sugaiketadao/sicore
- How to verify sample screens (VS Code): https://github.com/sugaiketadao/sicore#%EF%B8%8F-how-to-verify-sample-screens---vs-code
- Getting started with AI development: https://github.com/sugaiketadao/sicore#-getting-started-with-ai-development
Related Articles
Check out the other articles in this series!
- Why I Built a Framework for SI Projects
- What I Learned Developing a Custom Framework with Generative AI
- When Is It OK to Build a System with AI Alone?
Thank you for reading!
I'd appreciate it if you could give it a ❤️!
Top comments (0)