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.
Two small keywords behind every "Next page" button — and two traps that bite at scale.
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.
SQL tables are unordered sets. Without an explicit ORDER BY, the database may return rows in any order it finds convenient — and that order can change between runs, after an update, or when the query plan changes.
So "the first 10 rows" is not a defined concept without ordering. Select "no ORDER BY" in this lab and press "Re-run the same query" a few times: the same query returns different rows. In production this shows up as items mysteriously appearing on two pages, or never appearing at all.
Always pair pagination with a deterministic ORDER BY — and if the sort column has ties, add a unique tiebreaker such as the primary key.
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.
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.
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.