Paging Large Result Sets in YugabyteDB (Without Re-Querying Everything)

When applications need to page through query results, a very common (but often inefficient) pattern shows up:

				
					SELECT some_columns
FROM some_table
ORDER BY some_column
LIMIT n OFFSET m;

This works… but at scale, especially in a distributed database like YugabyteDB, it can become increasingly expensive as OFFSET grows.

In this tip, we’ll walk through:

● Why LIMIT / OFFSET is problematic at scale
● How YSQL Connection Manager affects cursors
● The recommended paging pattern for YugabyteDB
● A simple Java demo that pages 10 rows at a time by pressing Enter

🧠 Why `LIMIT / OFFSET` Is Not Ideal

Here’s an example of the classic paging pattern:

				
					SELECT id, payload
FROM demo_paging
ORDER BY id
LIMIT 10 OFFSET 50;

Even though you only want 10 rows:

● The database must still identify the first 50 rows
● Discard them
● Then return the next 10

As OFFSET grows:

● More rows are scanned and skipped
● Latency increases
● The same work is repeated for every page

In a distributed system, this cost can span multiple nodes and replicas.

👉 OFFSET pagination is fine for small result sets or admin tooling, but should be avoided for large or user-facing pagination.

🔌 What About Cursors?

If you’re coming from a traditional PostgreSQL background, your instinct may be:

“Why not just use a cursor and fetch 10 rows at a time?”

Let’s look at how that works and where the gotchas are.

Cursor Basics (PostgreSQL / YSQL Semantics)

A basic cursor looks like this:

				
					BEGIN;

DECLARE demo_cursor CURSOR FOR
  SELECT id, payload
  FROM demo_paging
  ORDER BY id;

FETCH 10 FROM demo_cursor;
FETCH 10 FROM demo_cursor;

COMMIT;

The problem…

● Non-holdable cursors are tied to a single transaction
● Once the transaction commits, the cursor is closed
● With transaction pooling, you cannot rely on getting the same backend session

Connection Pooling Changes the Rules

YugabyteDB’s YSQL Connection Manager uses transaction pooling.

That means:

● Each transaction may be served by a different backend session
● When a transaction ends, session state is discarded
● Non-holdable cursors will not survive

Cursor behavior summary

Cursor type	Survives COMMIT?	Works with connection pooling?	Notes
CURSOR (default)	❌ No	❌ No	Tied to a single transaction; closed on COMMIT or connection reuse
CURSOR WITH HOLD	✅ Yes	✅ Yes	Result set is materialized and cached; must be closed promptly

Using `CURSOR WITH HOLD`

YugabyteDB does support holdable cursors, even with Connection Manager:

				
					BEGIN;

DECLARE demo_cursor CURSOR WITH HOLD FOR
  SELECT id, payload
  FROM demo_paging
  ORDER BY id;

COMMIT;

FETCH 10 FROM demo_cursor;
FETCH 10 FROM demo_cursor;

CLOSE demo_cursor;

Important considerations

● The result set is materialized and cached
● Server memory is consumed
● Cursors must be explicitly closed
● Best used for short-lived, controlled workflows

👉 This provides cursor-like semantics, but it is not streaming in the traditional sense.

✅ The Recommended Pattern: Keyset (Seek) Pagination

For scalable paging in YugabyteDB, the recommended approach is keyset pagination.

Instead of skipping rows, we remember the last row seen and continue from there.

				
					SELECT id, payload
FROM demo_paging
WHERE id > ?
ORDER BY id
LIMIT 10;

Why this works so well

● Uses a selective, index-friendly predicate
● No skipped rows
● Predictable performance at any depth
● Stateless and connection-pool friendly

This pattern works especially well when:

● Paging by primary key
● Paging by (created_at, id)
● Paging by any monotonically increasing key

🧪 Demo Setup

Create a simple table:

				
					CREATE TABLE demo_paging (
  id      BIGINT PRIMARY KEY,
  payload TEXT NOT NULL
);

Insert 1,000 rows:

				
					INSERT INTO demo_paging (id, payload)
SELECT i, 'row-' || i
FROM generate_series(1, 100) AS i;

☕ Java Paging Demo (Using the YugabyteDB JDBC Smart Driver)

This program:

● Pages 10 rows at a time
● Uses keyset pagination
● Works with YSQL Connection Manager
● Uses the YugabyteDB JDBC Smart Driver
● Pauses until you press Enter

📦 Get the YugabyteDB JDBC Smart Driver

Download from Maven Central:

				
					curl -O https://repo1.maven.org/maven2/com/yugabyte/jdbc-yugabytedb/42.7.3-yb-1/jdbc-yugabytedb-42.7.3-yb-1.jar

🧾 Java Code

				
					import java.sql.*;
import java.util.Scanner;

public class KeysetPagingDemo {

    private static final String URL =
        "jdbc:yugabytedb://localhost:5433/yugabyte";
    private static final String USER = "yugabyte";
    private static final String PASS = "yugabyte";
    private static final int PAGE_SIZE = 10;

    public static void main(String[] args) throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Scanner scanner = new Scanner(System.in)) {

            long lastId = 0;

            while (true) {
                int rowsFetched = fetchPage(conn, lastId);

                if (rowsFetched == 0) {
                    System.out.println("\n✅ End of result set.");
                    break;
                }

                System.out.print("\nPress ENTER for next page...");
                scanner.nextLine();

                lastId += rowsFetched;
            }
        }
    }

    private static int fetchPage(Connection conn, long lastId)
            throws SQLException {

        String sql =
            "SELECT id, payload " +
            "FROM demo_paging " +
            "WHERE id > ? " +
            "ORDER BY id " +
            "LIMIT ?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, lastId);
            ps.setInt(2, PAGE_SIZE);

            try (ResultSet rs = ps.executeQuery()) {
                int count = 0;

                while (rs.next()) {
                    long id = rs.getLong("id");
                    String payload = rs.getString("payload");

                    System.out.printf("id=%d payload=%s%n", id, payload);
                    count++;
                }

                return count;
            }
        }
    }
}

▶️ Compile and Run

Compile:

				
					javac -cp jdbc-yugabytedb-42.7.3-yb-1.jar KeysetPagingDemo.java

Run:

				
					java -cp .:jdbc-yugabytedb-42.7.3-yb-1.jar KeysetPagingDemo

🖥️ Example Output

				
					id=1 payload=row-1
id=2 payload=row-2
...
id=10 payload=row-10

Press ENTER for next page...

				
					id=11 payload=row-11
...
id=20 payload=row-20

				
					id=91 payload=row-91
...
id=100 payload=row-100

Press ENTER for next page...

✅ End of result set.

🏁 Summary

❌ LIMIT / OFFSET re-scans and re-skips data
⚠️ Regular cursors don’t survive transaction pooling
🧵 CURSOR WITH HOLD works but materializes results
✅ Keyset pagination is fast, scalable, and pool-friendly
🚀 Works unchanged with the YugabyteDB JDBC Smart Driver

If you’re paging through large or growing tables in YugabyteDB, keyset pagination should be your default choice.

Have Fun!