Limit and Offset for Pagination

Ever stared at a 10,000-row table and screamed (internally) when your API dumped every single row into a JSON response? That's the pain this lesson kills. When your application grows beyond a handful of records, fetching everything at once is slow, wasteful, and a one-way ticket to a sluggish frontend. The remedy is pagination — splitting results into bite-sized pages — and the workhorses of pagination in PostgreSQL are the LIMIT and OFFSET clauses. By the end of this lesson, you'll slice query results like a pro, page through data with confidence, and know exactly when to reach for a more advanced tool.

The problem this lesson solves

Let's set the scene. You have a products table with 50,000 rows. Your app's home page needs to show the latest 20 products. If you run a plain SELECT * FROM products, PostgreSQL sends all 50,000 rows to your client — including data the user never sees. That's a waste of memory, bandwidth, and user patience.

Real-world symptoms of missing pagination:

• Slow API responses — the database churns out thousands of rows, most of which get discarded.
• Frontend lag — rendering 50,000 DOM elements freezes the browser.
• Database load spikes — every request that fetches everything spikes CPU and I/O.

Pagination solves this by fetching only the slice you need. Instead of one giant query, you run many small ones, each returning a page of results. The core tools for that in PostgreSQL are LIMIT (how many rows to return) and OFFSET (how many rows to skip). Simple, yes — but full of subtle traps that can silently degrade performance or, worse, show the user the wrong data.

Core concept / mental model

Think of a book. You don't read all 1,000 pages at once; you flip to a chapter, read a page or two (that's your LIMIT), and if you need earlier or later content, you skip ahead (that's your OFFSET). In SQL, the table is the book, and the result set is its pages.

Here's the official syntax, part of the SELECT statement's tail end:

SELECT column_list
FROM table_name
ORDER BY sort_columns
LIMIT row_count OFFSET skip_count;

Two critical details:

• LIMIT tells PostgreSQL the maximum number of rows to return. LIMIT 10 returns up to 10 rows.
• OFFSET tells PostgreSQL to discard the first skip_count rows before applying LIMIT. OFFSET 20 LIMIT 10 returns rows 21–30.

The mental model in one sentence: OFFSET positions the cursor, LIMIT sets the window size. Together they define a page: page number n with page size s becomes OFFSET (n-1)*s LIMIT s.

Pro tip: OFFSET 0 is optional — it's the default. Writing LIMIT 10 is the same as LIMIT 10 OFFSET 0.

An important nuance: without an ORDER BY, PostgreSQL returns rows in an unpredictable order — the same query can produce different pages on different runs. Always pair LIMIT/OFFSET with a deterministic ORDER BY column (like an id or a timestamp).

How it works step by step

Let's break down exactly what PostgreSQL does under the hood when you run:

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

1. Full scan (or index scan): PostgreSQL reads the products table, typically using the primary key index for the ORDER BY id.
2. Sort: rows are sorted by id (if not already in index order).
3. Discard OFFSET rows: the first 20 rows are dropped from the result set.
4. Take LIMIT rows: the next 10 sorted rows are returned to the client.

The crucial fact: PostgreSQL still reads (and often sorts) all rows before discarding the offset. This is the classic performance gotcha — see the Troubleshooting section.

Now, how do you translate page requests from your app? If a user asks for page 3 with 25 items per page:

• LIMIT = 25
• OFFSET = (3 - 1) * 25 = 50

So the query becomes:

SELECT * FROM products ORDER BY id LIMIT 25 OFFSET 50;

That's the entire core logic. Simple, right? Now let's get our hands dirty.

Hands-on walkthrough

We'll use a toy products table. Open psql and run:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2)
);

INSERT INTO products (name, price) VALUES
    ('Widget', 9.99),
    ('Gadget', 19.99),
    ('Gizmo', 29.99),
    ('Contraption', 39.99),
    ('Doohickey', 49.99),
    ('Thingamajig', 59.99),
    ('Whatsit', 69.99),
    ('Doodad', 79.99),
    ('Flux Capacitor', 89.99),
    ('Hoverboard', 99.99),
    ('Lightsaber', 109.99),
    ('Warp Core', 119.99);

Example 1: Basic LIMIT and OFFSET

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5;

Expected output:

 id |     name      | price
----+---------------+-------
  1 | Widget        |  9.99
  2 | Gadget        | 19.99
  3 | Gizmo         | 29.99
  4 | Contraption   | 39.99
  5 | Doohickey     | 49.99
(5 rows)

Now fetch the next 5 rows:

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5 OFFSET 5;

Expected output:

 id |    name     | price
----+-------------+-------
  6 | Thingamajig | 59.99
  7 | Whatsit     | 69.99
  8 | Doodad      | 79.99
  9 | Flux Capacitor | 89.99
 10 | Hoverboard  | 99.99
(5 rows)

Example 2: Pagination with page number and size

Let's write a query that returns page 3, with 4 items per page:

-- Page 3, 4 items per page → OFFSET = (3-1)*4 = 8, LIMIT = 4
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 4 OFFSET 8;

Expected output:

 id |    name     | price
----+-------------+-------
  9 | Flux Capacitor | 89.99
 10 | Hoverboard  | 99.99
 11 | Lightsaber  | 109.99
 12 | Warp Core   | 119.99
(4 rows)

Example 3: Combine with WHERE

Pagination works with filters. Suppose you want the cheapest products, 3 per page:

SELECT id, name, price
FROM products
WHERE price < 100
ORDER BY price, id
LIMIT 3 OFFSET 3;

Expected output:

 id |    name     | price
----+-------------+-------
  4 | Contraption | 39.99
  5 | Doohickey   | 49.99
  6 | Thingamajig | 59.99
(3 rows)

Notice we added id as a tiebreaker in ORDER BY — essential when price values repeat.

Example 4: Get the total count for pagination UI

Most apps need to show “Page 3 of 7”. That requires a count query:

SELECT count(*) FROM products WHERE price < 100;

Expected output:

 count
-------
     8
(1 row)

With count = 8 and page size = 3, you compute total pages as ceil(8/3) = 3.

Pro tip: Never use LIMIT/OFFSET without ORDER BY for pagination — page contents become nondeterministic and can show duplicate or missing rows when data changes between requests.

Compare options / when to choose what

LIMIT/OFFSET isn't the only way to page in PostgreSQL. Here's a comparison with two popular alternatives:

When to choose what:

• Choose LIMIT/OFFSET when your dataset is small or you need direct page-number navigation (like a classic “Page 2 of 10” widget).
• Switch to keyset pagination when you hit thousands of pages — query time starts to degrade linearly as OFFSET grows.
• For public-facing APIs, consider cursor-based — it's more robust and hides complexity.

Troubleshooting & edge cases

1. Large OFFSET is slow

Symptom: Queries with OFFSET 100000 take ages. Why: PostgreSQL still reads and sorts 100,000 rows before discarding them. Fix: Switch to keyset pagination for deep pages, or limit pages with a “load more” pattern.

2. Negative or zero values

LIMIT 0 returns no rows (intentional). OFFSET with a negative number raises an error:

SELECT * FROM products LIMIT -1;

Error:

ERROR:  LIMIT must not be negative

Always clamp your page number to >= 1 in application code.

3. Missing ORDER BY causes inconsistent pages

Without ORDER BY, PostgreSQL may return rows in any order (often insertion, but not guaranteed). If the table is vacuumed or updated, the same OFFSET can yield different rows. Always pair LIMIT/OFFSET with a deterministic ORDER BY.

4. Data changes between requests

If a row is inserted or deleted while the user flips pages, they might see duplicates or miss an item. For example, page 1 returns rows 1–10, then row 5 is deleted; page 2 (OFFSET 10) now skips 11 rows, so the user misses row 11. This is inherent to OFFSET — a keyset or cursor approach solves it if data stability matters.

5. OFFSET beyond the table size

OFFSET 1000 LIMIT 10 on a 100-row table simply returns an empty set — no error. That's fine, but make sure your app handles an empty page gracefully (show “no more results”).

What you learned & what's next

You now hold the key to efficient data fetching: LIMIT and OFFSET let you control exactly how many rows a query returns and where the cursor starts. You've seen how to combine them with ORDER BY and WHERE, how to compute page offsets from user input, and how to count results for pagination UI. You've also learned the critical caveat — large offsets degrade performance — and met the modern alternative, keyset pagination.

Next in the PostgreSQL Tutorial, you'll dive into indexes (or whatever the next lesson is) to make those ORDER BY and WHERE clauses lightning-fast. With pagination and indexing in your belt, you're well on your way to building queries that scale to millions of rows.