0Pricing
SQL Academy · Lesson

LIMIT, OFFSET and Pagination Basics

Paginate large result sets with LIMIT and OFFSET, understand performance trade-offs, and use keyset pagination for stable scrolling.

LIMIT, OFFSET and Pagination Basics is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

LIMIT: Cap the Result

LIMIT n returns at most n rows:

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10;

OFFSET: Skip the First N

OFFSET k skips the first k rows:

-- Rows 11–20 (the second page of 10):
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

Pagination = LIMIT + OFFSET

The classic page-number pattern:

-- Page p (1-based), page size s:
SELECT * FROM articles
ORDER BY published_at DESC
LIMIT s OFFSET (p - 1) * s;

Why OFFSET Gets Slow

OFFSET still reads every skipped row internally:

  • Page 1 of 10 → reads 10 rows
  • Page 1000 of 10 → reads 10,000 rows
  • Page 1,000,000 → reads ten million

OFFSET is O(offset). For deep pages, this is unusable.

Keyset Pagination ("seek method")

Page forward by remembering the last seen sort key:

-- First page:
SELECT * FROM articles
ORDER BY published_at DESC, id DESC
LIMIT 20;

-- Next page — pass the last seen (published_at, id):
SELECT * FROM articles
WHERE (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Why Keyset Is Faster

Keyset uses an index range scan: jump straight to the row after the last seen one, read N rows, done. Cost is O(page size), independent of how deep you are.

LIMIT Without ORDER BY Is Non-Deterministic

Rule of thumb: always pair LIMIT with ORDER BY. Otherwise the database may return different rows each call.

-- Wrong — results may vary between runs:
SELECT * FROM events LIMIT 10;

-- Right:
SELECT * FROM events ORDER BY id DESC LIMIT 10;

Total Counts and Pagination

UI sometimes needs the total row count. A separate COUNT(*) query is the simplest, but on big tables it can be expensive:

SELECT COUNT(*) FROM articles WHERE author_id = $1;

-- Alternative for huge tables: estimate from pg_class.reltuples or use approximate counts.

FETCH FIRST (Standard SQL)

Standard SQL syntax for LIMIT:

SELECT * FROM products
ORDER BY id
FETCH FIRST 10 ROWS ONLY;

-- With offset:
-- OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Cursors for Massive Result Sets

For exporting millions of rows, use a DB cursor or streaming driver instead of LIMIT/OFFSET loops:

BEGIN;
DECLARE big_cursor CURSOR FOR
  SELECT * FROM events ORDER BY id;
FETCH 1000 FROM big_cursor;
-- ...
CLOSE big_cursor;
COMMIT;

When OFFSET Is OK

OFFSET is fine for shallow pages (page 1–10 of a UI), or for small tables. For deep pagination, infinite scroll, or APIs, use keyset.

Recap

LIMIT and OFFSET paginate result sets.

  • Always combine with ORDER BY
  • OFFSET is slow at depth — switch to keyset for big pages
  • FETCH FIRST is the standard equivalent

Quick Check

Which is the most efficient way to paginate deep into a large table?

Frequently asked questions

Is the “LIMIT, OFFSET and Pagination Basics” lesson free?

Yes — the full text of “LIMIT, OFFSET and Pagination Basics” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “LIMIT, OFFSET and Pagination Basics”?

Paginate large result sets with LIMIT and OFFSET, understand performance trade-offs, and use keyset pagination for stable scrolling. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LIMIT, OFFSET and Pagination Basics” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Selecting Columns and Expressions
  2. WHERE Filters: Comparison and Logic Operators
  3. ORDER BY Single and Multiple Columns
  4. LIMIT, OFFSET and Pagination Basics
← Back to SQL Academy