The LIMIT Trap: Don’t Rely on Implicit Row Ordering in YugabyteDB

Jim Knicely

A new index can make a query faster. It can also change which rows come back first.

That matters a lot when application logic uses LIMIT, especially with OFFSET, and assumes rows will always come back in the same order.

This is not a YugabyteDB defect. It is standard SQL behavior. Unless a query has an explicit ORDER BY, the database is free to return matching rows in any order. The order can change when statistics change, when a plan changes, when a new index is added, or when the query starts using a different access path.

⚠️ Important: If your query uses LIMIT or OFFSET without a unique ORDER BY, you are not asking for a deterministic set of rows. You are asking for “some rows,” and the database gets to decide which rows are easiest to return.

The Real-World Pattern

A nightly batch job was processing work items from a queue table.

The query looked conceptually like this:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
LIMIT 1 OFFSET 1000;

There was no ORDER BY.

The application used this pattern to walk through the result set one row at a time:

				
					LIMIT 1 OFFSET N

That worked for a while, but only by accident. The planner happened to use an existing index whose natural order matched what the application expected.

Then the access path changed:

  • ● New region-specific covering indexes were added to improve other queries on the same table.
  • ● The planner started using one of those new indexes for the batch query.
  • ● The new index had a different key order than the previous index.
  • ● Because the query had no ORDER BY, the implicit row order changed.

The result was subtle but serious: the same OFFSET values started landing on different rows. Some rows were skipped, and others could be visited more than once. The batch job completed, but fewer rows were updated than expected.

The bug was not that the new index was wrong. The bug was that the application depended on an implicit row order that was never guaranteed.

Why This Happens

YugabyteDB indexes are not just lookup helpers. Their key structure matters.

For example, this existing index might return qualifying rows in account_id order:

				
					CREATE INDEX idx_work_queue_ready_account
ON app_work_queue (
  queue_status,
  process_date,
  account_id ASC
)
INCLUDE (
  region_code,
  priority_score
);

Later, a new region-focused covering index might be added:

				
					CREATE INDEX idx_work_queue_geo_cover_a
ON app_work_queue (
  queue_status,
  process_date,
  region_code ASC,
  priority_score DESC,
  account_id ASC
)
INCLUDE (
  assigned_team,
  last_update_ts
);

Both indexes may be valid ways to satisfy this query:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
LIMIT 1 OFFSET 1000;

But they do not naturally walk rows in the same order.

The first index is effectively ordered like this for matching rows:

				
					queue_status, process_date, account_id

The second index is effectively ordered like this for matching rows:

				
					queue_status, process_date, region_code, priority_score DESC, account_id

Without an ORDER BY, both access paths are legal. If the planner switches from one index to the other, the query may return a different row for the same OFFSET.

Don’t confuse “repeatable in my test” with “guaranteed.” A query without ORDER BY may appear stable for months. That only means the planner kept choosing a plan that happened to return rows in the same order. A new index, updated statistics, different bind values, or a version change can expose the bug.

Simple Demo

Create a sample queue table:

				
					DROP TABLE IF EXISTS app_work_queue;

CREATE TABLE app_work_queue (
  account_id       BIGINT PRIMARY KEY,
  region_code      TEXT NOT NULL,
  queue_status     TEXT NOT NULL,
  process_date     DATE NOT NULL,
  priority_score   INT NOT NULL,
  assigned_team    TEXT,
  last_update_ts   TIMESTAMPTZ DEFAULT now()
);

Load some sample data:

				
					INSERT INTO app_work_queue
(account_id, region_code, queue_status, process_date, priority_score, assigned_team)
VALUES
  (1001, 'east',    'READY', CURRENT_DATE, 20, 'team_a'),
  (1002, 'central', 'READY', CURRENT_DATE, 90, 'team_b'),
  (1003, 'west',    'READY', CURRENT_DATE, 10, 'team_c'),
  (1004, 'east',    'READY', CURRENT_DATE, 80, 'team_a'),
  (1005, 'central', 'READY', CURRENT_DATE, 30, 'team_b'),
  (1006, 'west',    'READY', CURRENT_DATE, 70, 'team_c'),
  (1007, 'east',    'READY', CURRENT_DATE, 40, 'team_a'),
  (1008, 'central', 'READY', CURRENT_DATE, 60, 'team_b'),
  (1009, 'west',    'READY', CURRENT_DATE, 50, 'team_c');

Create the original index:

				
					CREATE INDEX idx_work_queue_ready_account
ON app_work_queue (
  queue_status,
  process_date,
  account_id ASC
)
INCLUDE (
  region_code,
  priority_score
);

Because this demo table is tiny, YugabyteDB may reasonably choose a sequential scan. For the demo, temporarily discourage sequential scans so the index access path is easier to see:

				
					SET enable_seqscan = off;

Now run the unsafe query:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
LIMIT 1 OFFSET 4;

You should now see the query use the original index:

				
					Index Only Scan using idx_work_queue_ready_account on app_work_queue

With that index, the fifth matching row follows the index order:

				
					queue_status, process_date, account_id

So OFFSET 4 points to:

				
					account_id = 1005

Now add a new covering index with a different key order:

				
					CREATE INDEX idx_work_queue_geo_cover_a
ON app_work_queue (
  queue_status,
  process_date,
  region_code ASC,
  priority_score DESC,
  account_id ASC
)
INCLUDE (
  assigned_team,
  last_update_ts
);

Run the same unsafe query again:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
LIMIT 1 OFFSET 4;

The planner will now choose the newer covering index:

				
					Index Only Scan using idx_work_queue_geo_cover_a on app_work_queue

The query text did not change. The table data did not change. But the access path changed.

The new index walks matching rows in this order:

				
					queue_status, process_date, region_code, priority_score DESC, account_id

So the same OFFSET 4 may now land on a different row.

When the demo is finished, reset the planner setting:

				
					RESET enable_seqscan;
Demo-only setting: SET enable_seqscan = off is used here only to make the index access path easier to demonstrate on a tiny table. It is not the application fix. The fix is to add a deterministic ORDER BY or use a safer pagination pattern.

The Fix: Add a Unique ORDER BY

The fix is to make the row order explicit.

If the batch must process accounts in account order, say so in the SQL:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1 OFFSET 1000;

Now the database has a clear rule for what “first,” “next,” and “offset 1000” mean.

The ORDER BY should be deterministic. In practice, that means ordering by a unique column, or adding a unique column as a tiebreaker.

This is deterministic if account_id is unique:

				
					ORDER BY account_id

This is not deterministic if many rows can have the same priority:

				
					ORDER BY priority_score

This is better because it adds a unique tiebreaker:

				
					ORDER BY priority_score DESC, account_id

The same idea applies to timestamps. This may not be safe if multiple rows can have the same timestamp:

				
					ORDER BY last_update_ts

This is safer:

				
					ORDER BY last_update_ts, account_id
Rule of thumb: If a query uses LIMIT and the result matters, add an ORDER BY. If ties are possible, add a unique tiebreaker.

Also consider creating an index that supports the desired order:

				
					CREATE INDEX idx_work_queue_ready_account_order
ON app_work_queue (
  queue_status,
  process_date,
  account_id
);

That gives the planner an efficient path for this safer query:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1000;

Better Fix: Use Keyset Pagination

For large batch jobs, OFFSET is usually the wrong tool.

OFFSET gets slower as the offset grows because the database still has to walk past the skipped rows. It is also fragile when the underlying result set changes while the batch is running.

A better pattern is keyset pagination, also called seek pagination.

Instead of saying:

				
					LIMIT 1000 OFFSET 50000

say:

				
					WHERE account_id > last_seen_account_id
ORDER BY account_id
LIMIT 1000

For example, the first page looks like this:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1000;

The application remembers the last account_id returned from that page.

Then the next page uses that value:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
  AND account_id > 245000
ORDER BY account_id
LIMIT 1000;

Each page continues from the last known key instead of counting forward from the beginning of the result set again.

This is usually easier for the database to optimize, and it is safer for the application to reason about.

Why keyset pagination helps: The query has a stable starting point. Instead of asking for “the next 1,000 rows after skipping 50,000,” it asks for “the next 1,000 rows after this known key.”

For descending order, flip the comparison:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
  AND account_id < 245000
ORDER BY account_id DESC
LIMIT 1000;

If the order uses multiple columns, the keyset condition should use the same ordering columns.

For example:

				
					SELECT account_id,
       priority_score
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY priority_score DESC,
         account_id
LIMIT 1000;

The next page should continue from the last priority_score and account_id returned:

				
					SELECT account_id,
       priority_score
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
  AND (
       priority_score < 70
       OR (
            priority_score = 70
            AND account_id > 245000
          )
      )
ORDER BY priority_score DESC,
         account_id
LIMIT 1000;

The important rule is that the WHERE clause and the ORDER BY must work together. The query should continue from the last row returned by the previous page.

Best Batch Pattern: Create a Stable Work List

For batch jobs that update the same table they are reading from, keyset pagination may still not be enough.

Why? Because the batch can change the result set as it runs.

For example, this query finds rows that are ready to process:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1000;

Then the batch updates those rows:

				
					UPDATE app_work_queue
SET queue_status = 'PROCESSED',
    last_update_ts = now()
WHERE account_id = 1001;

After that update, the row no longer matches queue_status = 'READY'.

That means each page is being selected from a moving target.

A safer pattern is to create a stable work list at the beginning of the batch:

				
					CREATE TEMP TABLE batch_work_ids AS
SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id;

Now the batch has a fixed list of rows to process.

You can process from the temporary work table in chunks:

				
					SELECT account_id
FROM batch_work_ids
ORDER BY account_id
LIMIT 1000;

Or update a chunk of rows by joining back to the base table:

				
					UPDATE app_work_queue q
SET queue_status = 'PROCESSED',
    last_update_ts = now()
WHERE q.account_id IN (
  SELECT account_id
  FROM batch_work_ids
  ORDER BY account_id
  LIMIT 1000
);

After each chunk, remove the processed IDs from the temporary work table:

				
					DELETE FROM batch_work_ids
WHERE account_id IN (
  SELECT account_id
  FROM batch_work_ids
  ORDER BY account_id
  LIMIT 1000
);

Then repeat until the work table is empty:

				
					SELECT count(*)
FROM batch_work_ids;

This makes the batch much easier to reason about. The list of target rows is captured once, and the batch processes that list until it is complete.

Why this pattern helps: The batch no longer depends on repeatedly scanning a changing result set. It creates a stable list of IDs first, then processes that list in manageable chunks.

For critical jobs, it is also worth capturing the expected row count before processing starts:

				
					SELECT count(*)
FROM batch_work_ids;

At the end of the job, compare that number with the number of rows successfully processed. That kind of validation can catch skipped rows before the issue escapes into the next business cycle.

What to Check Before Adding Indexes

Adding an index can change the plan for queries that were not the original target of the index.

That is especially important for large queue tables, batch tables, or operational tables where application logic depends on processing rows in a predictable sequence.

Before adding a new index, check the critical queries that read from the same table:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1000;

Also check the unsafe version of the query, if it still exists in the application:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
LIMIT 1000;

You are looking for plan changes like this:

Before:

				
					Index Only Scan using idx_work_queue_ready_account

After:

				
					Index Only Scan using idx_work_queue_geo_cover_a

A plan change is not automatically bad. The new plan may be faster and perfectly valid.

The risk is when application logic was depending on the old plan’s accidental row order.

Index safety check: After adding an index, do not only test the query the index was created for. Also test critical queries on the same table, especially queries using LIMIT, OFFSET, batch processing, or queue-draining logic.

You can also look for risky query patterns in pg_stat_statements:

				
					SELECT query,
       calls,
       total_time,
       mean_time
FROM pg_stat_statements
WHERE query ILIKE '%limit%'
  AND query NOT ILIKE '%order by%'
ORDER BY total_time DESC
LIMIT 50;

This query is only a starting point. It can return false positives, and it can miss queries where the text is normalized or generated differently by the application.

But it is still useful for finding places to review.

For each query it finds, ask:

  • ● Does this query use LIMIT?
  • ● Does it use OFFSET?
  • ● Does the application care which rows are returned?
  • ● Does the query have an explicit ORDER BY?
  • ● If it has an ORDER BY, is the ordering unique?

If the application cares which rows are returned, the query needs a deterministic order.

Emergency Mitigation

In the real-world case that inspired this tip, the immediate goal was to stop the planner from using the newly added indexes so the batch could complete using the previous plan.

One emergency-only option is to mark the new index invalid:

				
					UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_work_queue_geo_cover_a'::regclass;

If there is more than one index to disable:

				
					UPDATE pg_index
SET indisvalid = false
WHERE indexrelid IN (
  'idx_work_queue_geo_cover_a'::regclass,
  'idx_work_queue_geo_cover_b'::regclass
);

You can verify the index status with:

				
					SELECT
  indexrelid::regclass AS index_name,
  indrelid::regclass AS table_name,
  indisvalid,
  indisready
FROM pg_index
WHERE indrelid = 'app_work_queue'::regclass
ORDER BY index_name;

When the application query has been fixed and tested, the index can be marked valid again:

				
					UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = 'idx_work_queue_geo_cover_a'::regclass;

Or for multiple indexes:

				
					UPDATE pg_index
SET indisvalid = true
WHERE indexrelid IN (
  'idx_work_queue_geo_cover_a'::regclass,
  'idx_work_queue_geo_cover_b'::regclass
);
Emergency-only warning: Updating pg_index is not normal application maintenance. Use this only with appropriate operational review or support guidance. The long-term fix is to correct the SQL by adding a deterministic ORDER BY, redesigning pagination, or changing the indexes through normal DDL.

This kind of mitigation can be useful when you need to unblock a production batch quickly without dropping the index definition. But it should not become the permanent fix.

The permanent fix is still in the application query:

				
					SELECT account_id
FROM app_work_queue
WHERE queue_status = 'READY'
  AND process_date = CURRENT_DATE
ORDER BY account_id
LIMIT 1 OFFSET 1000;

Summary Table

Pattern Risk Better Option
LIMIT without ORDER BY The database can return any matching rows in any order. Add a deterministic ORDER BY.
LIMIT/OFFSET pagination Rows can be skipped or repeated when the plan, order, or result set changes. Prefer keyset pagination.
ORDER BY on a non-unique column Tied rows can still move around. Add a unique tiebreaker, such as account_id.
Batch reads and updates the same qualifying rows The batch can change the result set while it is processing. Create a stable work list first.
Adding a new covering index The planner may choose a new access path with a different natural row order. Run EXPLAIN on critical queries before and after DDL.

Conclusion

LIMIT does not mean “give me the first rows” unless the query defines what “first” means.

Without an explicit ORDER BY, the database is free to return matching rows in any order. That order may appear stable for a long time, but it is not guaranteed. A new index, updated statistics, a version change, or a different execution plan can change which rows are returned first.

That is exactly why LIMIT/OFFSET pagination without ORDER BY is risky for batch jobs. The job may complete successfully from the application’s point of view, but still skip rows or process some rows more than once.

For critical batch processing:

  • ● Always use a deterministic ORDER BY.
  • ● Add a unique tiebreaker when the ordering column is not unique.
  • ● Prefer keyset pagination over large OFFSET pagination.
  • ● Consider creating a stable work list before updating rows.
  • ● Validate expected row counts against processed row counts.
  • ● Run EXPLAIN on critical queries before and after adding indexes.
Final takeaway: The query may be fast. The plan may look good. But without a deterministic ORDER BY, the row order is not yours. If the application depends on which rows come back first, make that order explicit.

Have Fun!

Still continuing the theme of parting ways with things to make the move to Dallas a little easier…

These are my old ball gloves. The softball glove on the left is from college, and the baseball glove on the right is from Little League, which was just a few years ago. Okay… maybe more than a few years ago.

Amazing how fast they sold on Facebook Marketplace. Hopefully they still have a few good innings left in ’em! ⚾🥎