PostgreSQL LIMIT/OFFSET: Efficient Pagination & Keyset Pagination Guide

PostgreSQL LIMIT and OFFSET: Pagination Techniques & Best Practices

LIMIT retrieves a specific number of rows. OFFSET skips rows before retrieving. Together, they paginate through results. But there’s a critical performance consideration most developers miss.

What You’ll Learn

This guide covers:

LIMIT and OFFSET for basic pagination
Performance implications of OFFSET
Keyset pagination for efficient large-dataset browsing
Pagination patterns used by real applications

Basic LIMIT and OFFSET

LIMIT Alone

Retrieve the first 5 employees:

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

Result:

 first_name | salary
------------+----------
 David      | 105000.00
 Jennifer   | 102000.00
 Susan      | 99000.00
 Richard    | 98000.00
 Joseph     | 96000.00
(5 rows)

LIMIT 5 returns exactly 5 rows.

OFFSET to Skip Rows

Retrieve the second page (rows 6-10):

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

Breaking it down:

LIMIT 5 — Return 5 rows
OFFSET 5 — Skip the first 5 rows

Result:

 first_name | salary
------------+----------
 Barbara    | 95000.00
 Charles    | 92000.00
 Robert     | 98000.00
 Mary       | 78000.00
 Patricia   | 76000.00
(5 rows)

This is how typical pagination works. Page 1 = LIMIT 5 OFFSET 0. Page 2 = LIMIT 5 OFFSET 5. Page 3 = LIMIT 5 OFFSET 10.

Pagination Formula

To get page N with page_size results:

LIMIT page_size OFFSET (N-1) * page_size

For page 3 with 10 results per page:

LIMIT 10 OFFSET (3-1) * 10  -- LIMIT 10 OFFSET 20

The OFFSET Performance Problem

OFFSET seems simple, but it has a hidden cost. To understand it, you need to know what PostgreSQL does internally.

How OFFSET Works Internally

When you ask for OFFSET 100000 LIMIT 10, PostgreSQL:

Scans the table
Sorts the entire result (or uses an index)
Skips the first 100,000 rows
Returns rows 100,001-100,010

That’s expensive. PostgreSQL still processes the 100,000 skipped rows.

Performance Comparison

Let me show you with actual examples using our employee database (30 rows—imagine this is a 1 million-row table):

Page 1 (fast):

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 10;

This is fast—returns immediately.

Page 100 (slow):

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 990;

This still scans the same way as page 1, just discards 990 rows before returning 10. On a million-row table, this is noticeably slower.

Page 10,000 (very slow):

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 99990;

Now you’re telling PostgreSQL to skip 99,990 rows. That’s expensive.

Keyset Pagination: The Better Approach

Instead of skipping rows, keyset pagination uses the last value from the previous page as a marker. It’s what large applications (Google, Twitter, etc.) use.

How Keyset Pagination Works

User requests page 1 — return rows 1-10
Last row has salary=75,000
For page 2, query: “Give me 10 rows with salary less than 75,000”
No skipping, no expensive scanning

Keyset Pagination Example

Get the first page (highest-paid employees):

SELECT emp_id, first_name, salary FROM employees
ORDER BY salary DESC, emp_id DESC
LIMIT 10;

Result (first page):

 emp_id | first_name | salary
--------+------------+----------
      7 | David      | 105000.00
      8 | Jennifer   | 102000.00
     12 | Susan      | 99000.00
      9 | Richard    | 98000.00
     10 | Joseph     | 96000.00
      4 | Patricia   | 76000.00
      1 | James      | 75000.00
      2 | Mary       | 78000.00
      3 | Robert     | 72000.00
      5 | Michael    | 70000.00
(10 rows)

The last row has salary=70,000 and emp_id=5.

For the next page, query:

SELECT emp_id, first_name, salary FROM employees
WHERE (salary, emp_id) < (70000, 5)
ORDER BY salary DESC, emp_id DESC
LIMIT 10;

Breaking it down:

WHERE (salary, emp_id) < (70000, 5) — Get rows after the last one from page 1
PostgreSQL can use an index on (salary DESC, emp_id DESC) directly without scanning skipped rows

Result (second page):

 emp_id | first_name | salary
--------+------------+----------
      6 | Linda      | 73000.00
     17 | Karen      | 80000.00
     11 | Charles    | 92000.00
     16 | Lisa       | 79000.00
     15 | Thomas     | 82000.00
     18 | Christopher| 78000.00
     19 | Nancy      | 75000.00
     13 | Jennifer   | 102000.00
     14 | Barbara    | 95000.00
     20 | Daniel     | 77000.00
(10 rows)

No OFFSET needed. PostgreSQL uses the (salary, emp_id) condition to seek directly to the right place.

Why Keyset Pagination Is Better

Aspect	OFFSET Pagination	Keyset Pagination
Page 1	Fast	Fast
Page 10	Fast	Fast
Page 100	Slow	Fast
Page 1000	Very slow	Fast
Index usage	Limited	Full use of index
Consistent ordering	Required	Required

On tables with millions of rows, keyset pagination is dramatically faster for deep pagination.

When to Use Each Approach

Use OFFSET Pagination When:

You have small result sets (< 10,000 total rows)
You’re building a simple web interface
Users rarely go beyond page 2-3
Simplicity matters more than performance

Example: Blog post comments (usually 50-200 total)

Use Keyset Pagination When:

You have large result sets (> 100,000 rows)
Users might browse deep into results
Performance is critical
You’re building an API

Example: Twitter feed, Google search results

Practical LIMIT/OFFSET Examples

Get Top 10 Highest-Paid Employees

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 10;

Simple and fast—no OFFSET.

Paginate Through All Employees

Page 1:

SELECT emp_id, first_name, salary FROM employees
ORDER BY emp_id
LIMIT 10 OFFSET 0;

Page 2:

SELECT emp_id, first_name, salary FROM employees
ORDER BY emp_id
LIMIT 10 OFFSET 10;

Page N:

SELECT emp_id, first_name, salary FROM employees
ORDER BY emp_id
LIMIT 10 OFFSET ((page_num - 1) * 10);

In your application, substitute page_num with the actual page number.

Get a Random Sample

SELECT first_name, salary FROM employees
ORDER BY RANDOM()
LIMIT 10;

This returns 10 random employees. Note: RANDOM() forces a full table scan (can be slow on large tables).

Get Recent Hires

SELECT first_name, hire_date FROM employees
ORDER BY hire_date DESC
LIMIT 5;

Result:

 first_name |  hire_date
------------+------------
 Jessica    | 2022-06-01
 Ashley     | 2022-06-01
 Mike       | 2022-06-01
 Margaret   | 2022-05-15
 Anthony    | 2022-03-01
(5 rows)

The 5 most recent hires.

Common Errors & Solutions

Error: “LIMIT requires a non-negative number”

Problem:

SELECT * FROM employees LIMIT -10;

LIMIT can’t be negative.

Solution:

Use a positive number:

SELECT * FROM employees LIMIT 10;

Error: “Inconsistent results when using OFFSET”

Problem:

SELECT * FROM employees LIMIT 10 OFFSET 0;
-- Run again, get different results

Without ORDER BY, rows can appear in any order. Different queries might return rows in different orders.

Solution:

Always use ORDER BY with pagination:

SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 0;

Error: “OFFSET is too large, no rows returned”

Problem:

SELECT * FROM employees LIMIT 10 OFFSET 1000;

If the offset exceeds the total number of rows, you get zero results.

Solution:

This isn’t an error—it’s expected. Your application should handle this gracefully (show “No more results” message).

Performance Tips

1. Always Use ORDER BY with LIMIT/OFFSET

Without ORDER BY, results are unpredictable across queries:

-- Bad: unpredictable order
SELECT * FROM employees LIMIT 10;

-- Good: consistent order
SELECT * FROM employees ORDER BY emp_id LIMIT 10;

2. Consider Keyset Pagination for Deep Pagination

-- For page 10,000, OFFSET is slow
SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 99990;

-- Keyset pagination is faster
SELECT * FROM employees WHERE emp_id > last_emp_id
ORDER BY emp_id LIMIT 10;

3. Index Your ORDER BY Column

CREATE INDEX idx_employees_emp_id ON employees(emp_id);

This speeds up pagination.

4. Limit Your Page Size

Even with OFFSET, large page sizes are inefficient:

-- Better: small page size
LIMIT 50 OFFSET 0

-- Worse: large page size (PostgreSQL processes more rows)
LIMIT 10000 OFFSET 0

5. Use LIMIT Even Without Pagination

When developing, always use LIMIT to avoid accidentally returning millions of rows:

SELECT * FROM employees LIMIT 1000;

FAQ

Q: What’s the maximum OFFSET value? A: Technically unlimited, but practically it becomes slow around OFFSET 100,000 on large tables.

Q: Can I use LIMIT without ORDER BY? A: Technically yes, but you shouldn’t. Without ORDER BY, results are unpredictable across queries.

Q: How do I get the last page? A: You need to know the total row count:

SELECT CEILING(COUNT(*) / 10.0) as total_pages FROM employees;

Then use that page number. Or use keyset pagination for better performance.

Q: Is LIMIT 1 the same as TOP 1 in SQL Server? A: Yes, LIMIT 1 returns the first row. PostgreSQL uses LIMIT, SQL Server uses TOP.

Q: Can I use LIMIT with UPDATE or DELETE? A: Yes, but it’s less common:

DELETE FROM employees WHERE dept_id = 99 LIMIT 5;

This deletes 5 employees from department 99.

Q: What’s the performance difference between OFFSET 0 and no OFFSET? A: None. OFFSET 0 is the same as no OFFSET clause.

Next Steps

You now understand pagination strategies. Your next learning paths:

PostgreSQL JOINs Complete Guide - Combine data from multiple tables
Subqueries in SELECT - Nest queries for complex logic
Back to the SELECT Guide - Review fundamentals

Pagination is essential for web applications. Whether you choose OFFSET or keyset pagination depends on your scale, but understanding both makes you a better engineer!