Return a slice of a result set instead of all of it. Page through real data, then discover the two traps: pagination without ORDER BY, and why large OFFSETs get slower and slower.
Overview
Quick Context
LIMIT n returns at most n rows. OFFSET m throws away the first m rows before it starts counting. Together they cut a window out of a result set, which is how page 3 of a product listing gets built.
Syntax varies: PostgreSQL, MySQL and SQLite use LIMIT ... OFFSET ...; SQL Server uses OFFSET ... FETCH NEXT ... ROWS ONLY; older Oracle used ROWNUM. The idea is identical.
LIMIT and OFFSET: Serving Data One Page at a Time
Two small keywords behind every "Next page" button — and two traps that bite at scale.
Trap 1: LIMIT without ORDER BY is meaningless
SELECT * FROM articles LIMIT 10; -- ten arbitrary articles
Without ORDER BY, the database is free to return any ten rows, and which ten can change between runs as the plan, the cache or the storage layout changes. On a small test table this looks stable and deterministic, which is exactly why it reaches production before failing.
If you want the newest, say so. If you want a sample, say that too — ORDER BY RANDOM() on small tables, or a TABLESAMPLE clause on large ones.
Trap 2: Deep OFFSET Gets Slower and Slower
The database cannot jump straight to row 100,000. It must generate and discard every row before the offset. Watch the "rows scanned" figure in this lab climb while "rows returned" stays fixed — the red portion of the bar is pure wasted work.
At page 5 nobody notices. At OFFSET 500000 the query is reading half a million rows to hand back ten. This is why deep pagination is a classic source of slow queries.
The fix is keyset pagination (also called seek pagination): instead of counting rows to skip, remember the last value you saw and filter on it.
Keyset stays fast at any depth, and it does not skip or duplicate rows when data changes underneath you. The trade-off is that you can only move forward and backward, not jump to an arbitrary page number.
Cutting the result down
LIMIT caps how many rows come back. OFFSET skips some first. Together they are how nearly every "page 3 of results" in the world is implemented.
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20; -- newest 20
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 40; -- page 3
The dialects differ more than you would expect for something this simple:
Engine
Syntax
PostgreSQL, MySQL, SQLite
LIMIT 20 OFFSET 40
MySQL (older shorthand)
LIMIT 40, 20 — offset first
SQL Server, Oracle 12c+
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
Standard SQL
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY
Note the reversed argument order in MySQL's comma form — a reliable source of off-by-a-page bugs when translating between dialects.
LIMIT runs last, after ORDER BY, which is what makes "the top 10" meaningful. It also means the database may have had to produce and sort every row before discarding all but ten — unless an index lets it stop early.
Trap 2: ties make pagination unstable
Even with ORDER BY, duplicates in the sort key break paging:
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 20;
If fifty articles share a publication date, the engine picks arbitrarily among them for each page. Page 1 and page 2 can both include the same article, and another article can be missed entirely — a bug that looks like data loss and is actually a sorting detail.
The fix is a unique tiebreaker as the final sort key:
ORDER BY published_at DESC, id DESC
Now the order is total, and the same query returns the same page every time.
Trap 3: deep OFFSET gets slower and slower
OFFSET 100000 does not skip cheaply. The engine must generate the first 100,000 rows in order, then throw them away. Page 1 is instant; page 5,000 is a table scan.
Keyset pagination avoids the problem by remembering the last row seen instead of counting from the start:
-- page 1
SELECT * FROM articles ORDER BY published_at DESC, id DESC LIMIT 20;
-- next page: pass back the last row's sort values
SELECT * FROM articles
WHERE (published_at, id) < ('2026-08-01', 5312)
ORDER BY published_at DESC, id DESC
LIMIT 20;
With an index on (published_at DESC, id DESC) the engine seeks directly to that position, so page 5,000 costs the same as page 1. Rows inserted between requests no longer shift the window either, so nothing is duplicated or skipped.
The limitation is that you can only step forward and backward, not jump to an arbitrary page number. For feeds, timelines and infinite scroll that is not a limitation at all. Keep OFFSET for admin tables where someone genuinely clicks "page 47" and the table is small.
Exploration guide
Set LIMIT to 4 and step the page buttons. The highlighted window slides down the full result set — OFFSET is just where the window starts.
Push OFFSET high and watch the red "skipped" portion of the cost bar grow while the green returned portion stays the same size.
Switch to "no ORDER BY" and re-run repeatedly. The returned rows change even though the query text does not.
Set OFFSET beyond the table size. You get zero rows — not an error. A common cause of mysteriously blank final pages.
Paging, and the bug that ships with it
LIMIT and OFFSET look like the obvious way to page through results, and they carry two problems that only appear in production: pages that skip or repeat rows, and a cost that grows with the page number.
query.sqlSQLite
-- Page 1, then page 2, of orders newest first.
SELECT id, placed, status FROM orders ORDER BY placed DESC LIMIT 3 OFFSET 0;
SELECT id, placed, status FROM orders ORDER BY placed DESC LIMIT 3 OFFSET 3;
-- Now the same without ORDER BY. The result happens to look sensible on
-- six rows in a fresh database, and it is not promised: LIMIT without
-- ORDER BY takes an arbitrary slice of an arbitrary order, so page 2 can
-- repeat a row from page 1 or skip one entirely.
SELECT id, placed, status FROM orders LIMIT 3 OFFSET 3;
-- The second problem is cost. OFFSET does not skip work, it does the
-- work and throws the rows away, so OFFSET 100000 reads a hundred
-- thousand rows to return ten. Paging deep into a large table gets
-- slower the further you go.
--
-- The fix is keyset paging: remember the last row you showed and ask for
-- what comes after it. Constant cost at any depth, and immune to rows
-- being inserted between pages.
SELECT id, placed, status
FROM orders
WHERE placed < '2024-03-01'
ORDER BY placed DESC
LIMIT 3;
Result
Worth remembering
LIMIT and OFFSET slice a result set, but only after ORDER BY has made that set deterministic. Offset pagination is fine for the first few pages and quietly quadratic beyond them — reach for keyset pagination when the data gets big.
Getting a total count without paying twice
Pagination interfaces usually want "showing 21–40 of 1,203", which means a second query:
SELECT COUNT(*) FROM articles WHERE status = 'published';
On a large table that count can cost more than the page itself, because it has to touch every matching row. Three ways out:
Do not show an exact total. "Showing 21–40" with a next button is usually enough, and it is what most large sites do.
Use an approximate count. PostgreSQL's reltuples in pg_class, or EXPLAIN's row estimate, is instant and close enough for "about 1,200 results".
Cache it. Store the count and refresh it periodically, or maintain it with a trigger if it must be exact.
A fourth option in some engines is a window function that returns the total alongside each page row — COUNT(*) OVER () — which computes the full set once rather than twice. It is convenient, but it still processes every matching row.
Top-N per group, the query LIMIT cannot do
LIMIT applies to the whole result, not per group. "The three newest articles in each category" therefore needs a window function:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category_id
ORDER BY published_at DESC, id DESC) AS rn
FROM articles
)
SELECT * FROM ranked WHERE rn <= 3;
PostgreSQL offers a neater alternative with LATERAL, which runs a small limited query per group and can use an index efficiently:
SELECT c.name, a.*
FROM categories c
CROSS JOIN LATERAL (
SELECT * FROM articles
WHERE category_id = c.id
ORDER BY published_at DESC LIMIT 3
) a;
Questions people ask
Does LIMIT speed up a query? It can, substantially, when an index supplies the order and the engine can stop early. When a sort or aggregation must complete first, it only reduces what is sent back.
Is LIMIT 1 the right way to get one row? With ORDER BY, yes. Without it, you are asking for an arbitrary row.
Can I use LIMIT in a subquery? Yes, and it is common for "the latest row per something" patterns — though the outer query may reorder the results, so keep the final ORDER BY outside.
Why does page 2 repeat a row from page 1? Ties in the sort key, or rows inserted between the two requests. A unique tiebreaker fixes the first; keyset pagination fixes both.
What is a good page size? 20–50 for user interfaces; larger for API consumers who will page through everything anyway. Very large pages defeat the purpose and stress memory on both sides.
Does OFFSET 0 cost anything? No, though in some older PostgreSQL versions OFFSET 0 was used deliberately as an optimisation fence to stop the planner inlining a subquery.
Recap in one screen
LIMIT caps rows, OFFSET skips them, and both run last.
LIMIT without ORDER BY returns arbitrary rows, however stable it looks in testing.
Ties in the sort key make pages overlap — always add a unique tiebreaker.
Deep OFFSET reads and discards everything before it; keyset pagination is O(1) per page.
Exact total counts are expensive; approximate or cache them.
Predict, then reveal
About to run: set OFFSET (rows skipped) to its maximum (20). Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What is meant by “Do not show an exact total” here?
"Showing 21–40" with a next button is usually enough, and it is what most large sites do.
What is meant by “Use an approximate count” here?
PostgreSQL's reltuples in pg_class , or EXPLAIN 's row estimate, is instant and close enough for "about 1,200 results".
What is meant by “Cache it” here?
Store the count and refresh it periodically, or maintain it with a trigger if it must be exact.
Cheat sheet
Limit and Offset in SQL
Return a slice of a result set instead of all of it. Page through real data, then discover the two traps: pagination without ORDER BY, and why large OFFSETs get slower and slower.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.