Second Page of Newest Posts

SQLPaginationJuniorMidUpdated Nov 11, 2026

The blog shows 10 posts per page, newest first. Return page 2, which is posts 11 through 20.

Schema

CREATE TABLE posts (
  id            BIGINT PRIMARY KEY,
  title         TEXT NOT NULL,
  published_at  TIMESTAMPTZ NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (c)

SELECT id, title, published_at
FROM posts
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 10;

OFFSET skips (page - 1) * page_size rows, so page 2 skips 10 and LIMIT takes the next 10. (a) skips 20 and returns page 3. (b) has no ORDER BY, and without one the database may return rows in any order, which can differ between runs. (d) sorts oldest first. The id DESC tiebreaker in (c) gives posts with the same published_at a fixed order, so none of them repeats or disappears between pages.

Going Deeper

OFFSET still reads every row it skips, so page 500 costs as much as reading 5,000 rows, and a post published while someone is paging shifts every later page by one. Keyset pagination avoids both: remember the last row's (published_at, id) and ask for WHERE (published_at, id) < ($1, $2) ORDER BY published_at DESC, id DESC LIMIT 10. An index on (published_at, id) lets the database jump straight to that point.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT id, title, published_at
FROM posts
ORDER BY published_at DESC
LIMIT 10 OFFSET 20;

Option (b)

SELECT id, title, published_at
FROM posts
LIMIT 10 OFFSET 10;

Option (d)

SELECT id, title, published_at
FROM posts
ORDER BY published_at ASC
LIMIT 10 OFFSET 10;