How do I design a paginated listing with stable ordering so pages don’t shift when rows change?
Use an explicit ORDER BY that uniquely orders rows, then choose between OFFSET and keyset pagination based on how often rows change and how far users navigate. PostgreSQL only guarantees stable pages when the sort order is deterministic.
A paginated list feels stable until the table changes under it. Then page 2 suddenly contains a row that used to be on page 1, or a row seems to vanish between clicks. That is not a rendering glitch; it is usually a query contract problem.
PostgreSQL gives you the pieces to make the contract explicit. ORDER BY defines the order, LIMIT chooses a slice, and OFFSET skips a slice. The important part is deciding whether your product promise is positional browsing or value-based continuation, because those two promises behave differently when rows are inserted or removed.
This article shows how to make the ordering deterministic, why a unique tie-breaker matters, where OFFSET + LIMIT becomes fragile, and why keyset pagination is often the better fit for a list that changes while people are navigating it.
Why the same page can show different rows after the table changes
A paginated list looks simple until you ask what “page 2” means after somebody inserts, deletes, or updates a row. If your query is only “give me the next 20 rows,” the database still needs a rule for which 20 rows belong in that slice. PostgreSQL’s documentation is explicit: without ORDER BY, the row order is unspecified, and using LIMIT without a unique ordering gives you an unpredictable subset of the result set. That is exactly why users can see items jump between pages even when the page number has not changed.
The important distinction is between the rows a query can produce and the order in which you are willing to consume them. LIMIT does not create a stable boundary by itself; it merely trims whatever order the rest of the query happened to produce. Once rows are added or removed ahead of your current offset, the meaning of “skip 20 and take 20” changes. The page number is still the same, but the underlying slice is no longer the same part of the list.
That is why pagination is not just a UI concern. It is a contract between the query and the navigation model. If the contract says “page 3 contains the third block of rows in a unique, deterministic order,” then the query must spell out that order completely. If the contract is looser, the user will eventually see duplicate rows, missing rows, or rows that seem to move backward and forward as the table changes.
// main.mjs
// Provides small, pure functions that demonstrate LIMIT/ORDER BY behavior for pagination
// Works on in-memory arrays of {id: number|string, created_at: ISOString, ...}
// No side effects on import.
// Sort helper: accepts an array of sort specifiers like [{key:'created_at', dir:'desc'}, {key:'id','dir':'asc'}]
// If no tie-breaker provided, the sort only uses the provided keys.
export function sortRows(rows, orderSpecs = [{key: 'created_at', dir: 'desc'}]) {
const cmp = (a, b) => {
for (const spec of orderSpecs) {
const {key, dir = 'asc'} = spec;
const av = a[key];
const bv = b[key];
if (av === bv) continue;
// handle ISO date strings naturally by string comparison (ISO sorts chronologically)
if (av == null) return dir === 'asc' ? -1 : 1;
if (bv == null) return dir === 'asc' ? 1 : -1;
if (av < bv) return dir === 'asc' ? -1 : 1;
return dir === 'asc' ? 1 : -1;
}
return 0; // equal on all provided keys
};
// slice to avoid mutating input
return rows.slice().sort(cmp);
}
// OFFSET + LIMIT pagination: mimic SQL's ORDER BY ... LIMIT ... OFFSET ...
// rows: array, orderSpecs: as above, limit: number, offset: number
export function paginateOffset(rows, orderSpecs, limit, offset) {
if (!Number.isInteger(limit) || limit < 0) throw new TypeError('limit must be non-negative integer');
if (!Number.isInteger(offset) || offset < 0) throw new TypeError('offset must be non-negative integer');
const sorted = sortRows(rows, orderSpecs);
return sorted.slice(offset, offset + limit);
}
// Keyset (seek) pagination: return the first `limit` rows strictly before/after a cursor value
// direction: 'after' for newer -> older navigation with created_at DESC; 'before' for the reverse
// cursor is an object with the same keys as the leading orderSpecs, e.g. {created_at: '2026-01-01T00:00:00Z', id: 42}
// This function assumes orderSpecs is non-empty and that cursor provides values for the leading keys.
// It returns {rows: [...], nextCursor: {...}|null}
export function paginateKeyset(rows, orderSpecs, limit, cursor = null, direction = 'after') {
if (!orderSpecs || orderSpecs.length === 0) throw new TypeError('orderSpecs must be provided');
if (!Number.isInteger(limit) || limit <= 0) throw new TypeError('limit must be positive integer');
// Build comparator for orderSpecs so we can test row vs cursor
const compareRowToCursor = (row, cur) => {
for (const spec of orderSpecs) {
const key = spec.key;
const dir = spec.dir || 'asc';
const rv = row[key];
const cv = cur[key];
if (rv === cv) continue;
if (rv == null) return dir === 'asc' ? -1 : 1;
if (cv == null) return dir === 'asc' ? 1 : -1;
if (rv < cv) return dir === 'asc' ? -1 : 1;
return dir === 'asc' ? 1 : -1;
}
return 0;
};
// For keyset, we must sort the whole set in the same ORDER BY direction
const sorted = sortRows(rows, orderSpecs);
let filtered;
if (!cursor) {
filtered = sorted;
} else {
if (direction === 'after') {
// For 'after', we want rows that come after the cursor in the sorted array.
// compareRowToCursor returns negative when row sorts before cursor, positive when after.
filtered = sorted.filter(r => compareRowToCursor(r, cursor) > 0);
} else {
// 'before' -> rows that come before cursor
filtered = sorted.filter(r => compareRowToCursor(r, cursor) < 0);
}
}
const page = filtered.slice(0, limit);
const nextCursor = page.length === 0 ? null : Object.fromEntries(orderSpecs.map(s => [s.key, page[page.length - 1][s.key]]));
return {rows: page, nextCursor};
}
Make the ordering deterministic before you talk about pages
A stable pagination pattern starts with a deterministic ORDER BY. In PostgreSQL, ORDER BY is the only mechanism that guarantees output order, and later sort expressions are used to break ties from earlier ones. That means the first sort key should express the main business order, and the later keys should remove ambiguity. If you sort by created_at DESC, then rows created at the same moment are still tied; if those rows can appear in different physical orders, the result can change even when the data has not changed in any meaningful way.
The usual repair is to add a unique tie-breaker, often the primary key. For example, ORDER BY created_at DESC, id DESC gives every row a unique position in the list as long as id is unique. That does not just make the result look sorted; it makes every page boundary reproducible. When a reader returns to the same page, the query can re-create the same slice from the same data snapshot, and when a row is inserted, the new row has an unambiguous place in the sequence.
This matters even if your current test data looks sorted already. Natural-looking row order is not a contract. A table scan, an index scan, or a planner change can produce rows in a different order unless the query fixes the order explicitly. A stable listing is therefore not “use ORDER BY if needed”; it is “always define a complete order that the application can explain and rely on.”
SourcesPostgreSQL documentation: LIMIT and OFFSET (opens a new tab)PostgreSQL documentation: Sorting Rows (ORDER BY) (opens a new tab)
Why OFFSET + LIMIT becomes fragile as navigation gets deeper
OFFSET pagination is easy to read and easy to implement: sort the rows, skip some, and take the next block. PostgreSQL documents that exact behavior, but it also warns that the skipped rows still have to be computed and that a large OFFSET can be inefficient. More importantly for product behavior, OFFSET counts positions, not identities. Once the list changes, those positions can shift under your feet.
Imagine a feed ordered by created_at DESC. The user opens page 1, then page 2, while a new row is inserted at the top. The new row pushes every later row down by one position. Page 1 now contains the new row; page 2 no longer begins where it used to begin. Depending on timing, a row can appear on both pages or be skipped entirely. The query is still valid SQL, but the user experience is no longer stable because the page boundary has moved between requests.
That instability is not a bug in PostgreSQL. It is the consequence of asking for “rows 21 through 40” in a changing list. If the data set is small and mostly static, OFFSET can still be acceptable. But once navigation becomes deep, frequent, or user-visible during concurrent inserts and deletes, the page number stops being a trustworthy cursor. At that point, OFFSET is a convenience, not a robust navigation model.
SourcesPostgreSQL documentation: LIMIT and OFFSET (opens a new tab)
What happens when new rows arrive between requests
The visible symptom most teams notice first is “a row moved to a different page.” That can happen even when the new row is not edited in place, because page boundaries are derived from ordering, not from stored page membership. If you sort by created_at DESC, a newly inserted row belongs near the front of the list. Every OFFSET-based page after that insertion is now counting from a different origin than the one the user saw a moment earlier.
That behavior is especially obvious when the sort key is coarse. If multiple rows share the same created_at value and you do not add a tie-breaker, the database is still allowed to choose any order among equal rows unless you fully constrain it. PostgreSQL says the later ORDER BY expressions are what sort rows that are equal according to the earlier ones. So the user-visible contract has to extend beyond the first column if you want a repeatable traversal.
The practical consequence is that “new content appears on page 1” is not enough detail. You also need to decide what should happen to page 2, page 3, and the user’s sense of place. A feed that tolerates some movement can stay on OFFSET. A feed that must not shift under active writes needs a different browsing model. The query shape is what makes that choice visible to the database and to the reader.
SourcesPostgreSQL documentation: Sorting Rows (ORDER BY) (opens a new tab)
When keyset pagination is the better fit
Keyset pagination, also called seek pagination, changes the question from “skip N rows” to “start after a known row.” Instead of depending on a moving offset, the client remembers the last row from the previous page and asks for the next rows in the same deterministic order. That is a better fit when rows are inserted or removed frequently, because the cursor is tied to actual row values rather than to a count that can drift.
With a stable sort such as ORDER BY created_at DESC, id DESC, the client can request rows that come after the last seen pair of values. The keyset boundary stays anchored to a concrete row in the ordering, so a newly inserted row at the top does not change what “next” means for the items already seen. The reader still sees the new row on a fresh visit to page 1, but page 2 can continue from the last known anchor without shifting under them.
That does not make keyset pagination universally superior. It works best when the sort key is monotonic enough to resume from, when the user is moving forward or backward through a list, and when “go to page 57” is not a requirement. It is also more exacting about the sort columns because the cursor must capture the same values that define the order. In exchange, it gives you a page boundary that survives inserts and deletes much better than OFFSET does.
SourcesPostgreSQL documentation: LIMIT and OFFSET (opens a new tab)PostgreSQL documentation: Sorting Rows (ORDER BY) (opens a new tab)
What LIMIT actually changes about the rows the planner returns
LIMIT does not merely trim the final output; PostgreSQL says the optimizer takes LIMIT into account when generating query plans. That means the chosen plan can differ depending on the requested limit and offset. The important design lesson is not that the planner is unpredictable, but that pagination is not just about a final slice. It is about how much of the result the server must produce in order to satisfy that slice, and what assumptions the planner can safely make while doing so.
This is another reason deterministic ORDER BY matters. Without a unique ordering, the planner is free to optimize for the requested limit while still returning a subset that satisfies the SQL contract. But if the application expects “the same logical page every time,” a nondeterministic subset is not acceptable. The database is doing exactly what you asked; the problem is that the request was underspecified.
The LIMIT/OFFSET cost also shapes the operational choice. Deep offsets ask the server to compute and discard many rows, which can become expensive even when the page result is small. Keyset pagination avoids that by using the ordered values themselves as the continuation point. In other words, the planner can still work with LIMIT, but the application gets more predictable results when the continuation rule is based on row identity instead of row count.
SourcesPostgreSQL documentation: LIMIT and OFFSET (opens a new tab)
A practical pattern that stays honest about its tradeoffs
A good default pattern is: choose a business order, add a unique tie-breaker, and then decide whether the UI needs page numbers or stable traversal. If the list is small, rarely changes, and users want random access to numbered pages, OFFSET with a complete ORDER BY can be perfectly reasonable. If the list changes often or users browse forward from the newest items, keyset pagination is usually the safer choice.
The rule of thumb is simple enough to document for the team. First, never paginate without ORDER BY. Second, never rely on a non-unique sort key alone when equal values are possible. Third, treat OFFSET as a positional convenience, not a stability guarantee. Fourth, prefer keyset navigation when row changes between requests would make page numbers misleading. Those rules are not style preferences; they come directly from how PostgreSQL defines ordering and slicing.
The hard part is making sure product expectations match the query model. A stable sort can still show new rows on earlier pages, because that is what descending recency means. What it should not do is change the membership of a page in a way the user cannot predict. By separating the sort contract from the navigation contract, you get a listing that is easier to explain, easier to test, and much less surprising when the underlying table changes.
SourcesPostgreSQL documentation: Sorting Rows (ORDER BY) (opens a new tab)PostgreSQL documentation: LIMIT and OFFSET (opens a new tab)
Choose the pagination model that matches the user promise
If you need the shortest possible answer, it is this: stable pagination starts with a complete ORDER BY, not with LIMIT. Once the sort is deterministic, OFFSET can return reproducible slices only as long as the underlying rows do not change in ways that move the slice boundary. The moment changing rows become part of normal operation, page numbers stop being a stable description of the content the user saw before.
A descending created_at list is a good example of the tension. It is the right order for “newest first,” but it will naturally put fresh rows onto earlier pages. That is acceptable if your product promise is “show me the newest items first.” It is not acceptable if your promise is “page 2 should keep the same membership while I browse.” In the second case, seek pagination is the better fit because the cursor describes a row boundary, not an offset in a moving list.
The final judgment is therefore not about which SQL clause is more fashionable. It is about the promise you want the listing to keep when the table changes. PostgreSQL gives you the tools to define that promise precisely: ORDER BY for determinism, LIMIT for slicing, OFFSET for positional browsing, and keyset logic for value-based continuation. Use the one that matches the contract you actually want to uphold.
These tests exercise an in-memory JavaScript fixture only. They do not test a live PostgreSQL server, planner choices, scan-order variation, transaction isolation, or concurrent writes. Verify those behaviors separately against your actual database and query plan.
// main.test.mjs
import test from 'node:test';
import assert from 'node:assert/strict';
import {paginateOffset, paginateKeyset, sortRows} from './main.mjs';
// Helper to make a row easily
const makeRow = (id, createdAt) => ({id, created_at: createdAt});
test('OFFSET+LIMIT is sensitive to inserts: page 2 shifts when a newer row is added (ORDER BY created_at DESC)', () => {
// Arrange: 7 rows with created_at spaced so newer have larger ISO strings
const rows = [
makeRow(1, '2026-01-07T00:00:00Z'),
makeRow(2, '2026-01-06T00:00:00Z'),
makeRow(3, '2026-01-05T00:00:00Z'),
makeRow(4, '2026-01-04T00:00:00Z'),
makeRow(5, '2026-01-03T00:00:00Z'),
makeRow(6, '2026-01-02T00:00:00Z'),
makeRow(7, '2026-01-01T00:00:00Z'),
];
// Pages of size 3 with ORDER BY created_at DESC
const order = [{key: 'created_at', dir: 'desc'}];
const page1 = paginateOffset(rows, order, 3, 0);
const page2Before = paginateOffset(rows, order, 3, 3);
// page2Before should contain ids 4,5,6 (the 4th..6th newest)
assert.deepEqual(page2Before.map(r => r.id), [4,5,6]);
// Insert a new row that is the newest (created_at later than all)
const newer = makeRow(100, '2026-01-08T00:00:00Z');
const rowsAfterInsert = [newer, ...rows];
const page1After = paginateOffset(rowsAfterInsert, order, 3, 0);
const page2After = paginateOffset(rowsAfterInsert, order, 3, 3);
// After inserting a new newest row, page1 gains that row and the content of page2 shifts.
assert.deepEqual(page1After.map(r => r.id), [100,1,2]);
// page2After is now what used to be (3,4,5) — note 4 moved to previous page
assert.deepEqual(page2After.map(r => r.id), [3,4,5]);
// This demonstrates that OFFSET+LIMIT with ORDER BY created_at DESC will produce different rows for the same page index
// when rows are inserted ahead of the page range.
});
test('Providing a deterministic ORDER BY with a tie-breaker makes ordering stable across different input orders (ORDER BY created_at DESC, id ASC)', () => {
// Arrange: two rows have identical created_at; we want the tie resolved by id
const a = makeRow(10, '2026-01-01T00:00:00Z');
const b = makeRow(11, '2026-01-01T00:00:00Z');
// Create two input orders to simulate the DB returning equal-created_at rows in an unspecified order
const input1 = [a, b];
const input2 = [b, a];
// If ORDER BY only created_at DESC is used, the sort has no defined tie-breaker.
// In our JS sortRows, ties keep input order, so results differ by input ordering — simulate DB unspecified behavior.
const orderCreatedOnly = [{key: 'created_at', dir: 'desc'}];
const pageFromInput1 = paginateOffset(input1, orderCreatedOnly, 2, 0);
const pageFromInput2 = paginateOffset(input2, orderCreatedOnly, 2, 0);
// If the ORDER BY doesn't include a unique tie-breaker, two otherwise-equal rows may appear in different orders
assert.notDeepEqual(pageFromInput1.map(r => r.id), pageFromInput2.map(r => r.id));
// Now include id as a secondary ORDER BY to make the order deterministic regardless of input order
const orderWithTie = [{key: 'created_at', dir: 'desc'}, {key: 'id', dir: 'asc'}];
const s1 = paginateOffset(input1, orderWithTie, 2, 0);
const s2 = paginateOffset(input2, orderWithTie, 2, 0);
assert.deepEqual(s1.map(r => r.id), [10,11]);
assert.deepEqual(s2.map(r => r.id), [10,11]);
// This demonstrates why ORDER BY should include a unique tie-breaker (for example a primary key)
// so LIMIT/OFFSET pages are deterministic for the same underlying dataset.
});
test('Keyset (seek) pagination avoids shifting/skipping of items already seen when a newer item is inserted', () => {
// Arrange: 6 rows newest to oldest
const rows = [
makeRow(1, '2026-01-06T00:00:00Z'),
makeRow(2, '2026-01-05T00:00:00Z'),
makeRow(3, '2026-01-04T00:00:00Z'),
makeRow(4, '2026-01-03T00:00:00Z'),
makeRow(5, '2026-01-02T00:00:00Z'),
makeRow(6, '2026-01-01T00:00:00Z'),
];
// Use ORDER BY created_at DESC, id ASC as a deterministic keyset ordering
const order = [{key: 'created_at', dir: 'desc'}, {key: 'id', dir: 'asc'}];
// Page size 2: fetch first page
const p1 = paginateKeyset(rows, order, 2, null, 'after');
assert.deepEqual(p1.rows.map(r => r.id), [1,2]);
// Now a new row is inserted as the newest (id 100). In OFFSET approach this would shift later pages.
const rowsAfterInsert = [makeRow(100, '2026-01-07T00:00:00Z'), ...rows];
// For keyset, we continue using the nextCursor from p1 to get the next page of older items.
const p2 = paginateKeyset(rowsAfterInsert, order, 2, p1.nextCursor, 'after');
// p2 should give the next two older rows after id 2 (which are ids 3 and 4) regardless of the new row at the front.
assert.deepEqual(p2.rows.map(r => r.id), [3,4]);
// If we used OFFSET to fetch page 2 with limit 2, offset 2, the insertion of the new item would cause overlap/skip.
const page2OffsetBefore = paginateOffset(rows, order, 2, 2).map(r => r.id);
const page2OffsetAfter = paginateOffset(rowsAfterInsert, order, 2, 2).map(r => r.id);
assert.deepEqual(page2OffsetBefore, [3,4]);
// after insert, offset 2 now returns [2,3] (different) demonstrating skip/duplicate risk
assert.deepEqual(page2OffsetAfter, [2,3]);
// This proves keyset pagination keeps the user's traversal anchored to the item values they saw
// rather than to a numeric offset that changes meaning when rows are inserted ahead of the cursor.
});
SourcesPostgreSQL documentation: LIMIT and OFFSET (opens a new tab)PostgreSQL documentation: Sorting Rows (ORDER BY) (opens a new tab)
