OFFSET in SQL: Syntax, Pagination & Performance Optimization

In web development, reporting APIs, and database querying, OFFSET in SQL is the standard clause used to divide vast query results into digestible pages. Whenever a user clicks through pages 1, 2, or 3 on an e-commerce catalog or analytics dashboard, an OFFSET clause tells the database engine which batch of records to serve.

However, using OFFSET in SQL naively on massive tables creates hidden database bottlenecks. In this guide, connecting to our foundation in what is SQL and SQL window functions, we cover standard syntax across all major database engines, evaluate the deep pagination trap, and examine keyset pagination for high-scale applications.

What is OFFSET in SQL?

When querying a relational database, returning 500,000 records in a single payload crashes browser memory and degrades network bandwidth. OFFSET in SQL instructs the query engine to ignore a designated count of initial rows and return only the requested slice:

-- Retrieve Page 3 with 10 records per page (Skipping first 20 rows)
SELECT customer_id, full_name, created_at
FROM customers
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Syntax and Dialect Differences for OFFSET in SQL

Different database management systems implement pagination through distinct syntax dialects:

1. PostgreSQL, MySQL, SQLite, and MariaDB

The most widespread syntax uses LIMIT and OFFSET:

SELECT product_id, product_name, price
FROM products
ORDER BY price ASC
LIMIT 25 OFFSET 50;  -- Skips 50 rows, returns next 25

Formula for applications: OFFSET = (page_number - 1) * page_size

2. ANSI Standard / SQL Server & Oracle Database

Microsoft SQL Server (2012+) and Oracle (12c+) adhere to the ANSI standard OFFSET ... FETCH:

-- ANSI Standard Syntax (SQL Server & Oracle)
SELECT product_id, product_name, price
FROM products
ORDER BY price ASC
OFFSET 50 ROWS
FETCH NEXT 25 ROWS ONLY;

The Performance Problem with OFFSET in SQL (Deep Pagination)

While OFFSET in SQL works seamlessly for pages 1 through 10, it triggers severe latency when users or scrapers paginate deep into the table (e.g., page 5,000):

-- Deep Pagination Query:
SELECT * FROM transactions 
ORDER BY transaction_date DESC 
LIMIT 20 OFFSET 1000000;

Why Deep OFFSET is Inefficient:

To satisfy OFFSET 1000000 LIMIT 20, the database engine does not magically teleport to record 1,000,001. Instead, the storage engine must:

1. Scan, sort, and materialize all 1,000,020 rows from the disk or index.
2. Discard the first 1,000,000 rows in memory.
3. Transmit only the final 20 rows to the client.

As the offset integer increases, query latency grows linearly ($O(N)$ time complexity), consuming CPU cycles and evicting cached pages from database buffer pools.

OFFSET in SQL: Analytical Nth-Value Extraction and Sampling Scenarios

While pagination is the primary use case for offset in sql, analysts also utilize offset concepts for deterministic percentile sampling, finding the Nth highest transaction, and building custom cohort partitions. Learn more in our SQL Tutorials hub.

Extracting the Nth Highest Record Without Window Functions

In technical interviews and quick ad-hoc analysis, pairing ORDER BY ... DESC with LIMIT 1 OFFSET (N-1) is the classic solution for pinpointing arbitrary rankings:

-- Finding the 5th highest transaction amount
SELECT transaction_id, customer_id, amount
FROM transactions
ORDER BY amount DESC
LIMIT 1 OFFSET 4;

• OFFSET 4 skips the top 4 highest records.
• LIMIT 1 selects the immediately following record (the 5th highest).
• Caveat: If multiple records share tied amounts, this naive approach returns an arbitrary tied row. For tie-aware ranking, analysts employ DENSE_RANK() as outlined in our SQL Rank Function guide.

Windowed Alternatives: OFFSET via LEAD and LAG Functions

In analytical reporting, rather than skipping rows in the final result set, analysts frequently need to compare current rows with values offset by $N$ periods:

SELECT 
    report_date,
    daily_active_users,
    -- Value from 7 rows prior (7-day week-over-week offset)
    LAG(daily_active_users, 7) OVER (ORDER BY report_date) AS dau_7_days_ago,
    ROUND(
        (daily_active_users - LAG(daily_active_users, 7) OVER (ORDER BY report_date))::NUMERIC / 
        LAG(daily_active_users, 7) OVER (ORDER BY report_date) * 100, 
        2
    ) AS wow_growth_pct
FROM daily_metrics;

Here, the second argument in LAG(col, offset, default) defines the relative offset jump, enabling clean time-series deltas without multiple self-joins.

Keyset Pagination: The High-Performance Alternative to OFFSET in SQL

For high-throughput systems, API feeds, and mobile infinite scroll feeds, replace OFFSET in SQL with Keyset Pagination (also known as Seek Pagination or Cursor-based Pagination).

Instead of skipping rows by number, keyset pagination uses a WHERE condition on an indexed column (such as id or created_at):

-- Page 1: Initial query fetching first 20 records
SELECT id, title, created_at 
FROM articles
ORDER BY id DESC
LIMIT 20;
 
-- Client remembers the last seen ID from Page 1 (e.g., id = 48201)
 
-- Page 2: Keyset seek query using index lookup (O(1) time complexity)
SELECT id, title, created_at 
FROM articles
WHERE id < 48201
ORDER BY id DESC
LIMIT 20;

Handling Composite Keys in Keyset Pagination

When ordering by a non-unique column such as created_at, break ties using the primary key id:

-- Keyset seek on composite ordering (created_at DESC, id DESC)
SELECT id, user_id, amount, created_at
FROM payments
WHERE (created_at, id) < ('2026-09-08 14:30:00', 98412)
ORDER BY created_at DESC, id DESC
LIMIT 20;

To see how the query planner prioritizes the evaluation of clauses, read our deep dive on order of execution in SQL.

Summary Checklist for OFFSET in SQL

• Always pair OFFSET with an explicit, unambiguous ORDER BY clause.
• Use LIMIT page_size OFFSET (page - 1) * page_size for basic UI tables.
• Profile query latency using EXPLAIN ANALYZE as offsets exceed 10,000 rows.
• Switch to Keyset / Seek pagination for mobile infinite scroll or large public APIs.
• Review our SQL cheat sheet for quick syntax patterns.

OFFSET in SQL: Architectural Trade-Offs in REST API Pagination

When designing backend APIs for dashboards and mobile apps, deciding between offset pagination and keyset pagination impacts both server infrastructure and user experience:

• Where OFFSET in SQL Shines: Low-scale internal admin panels where users need to jump directly to page 47 out of 50, and dataset sizes rarely exceed 5,000 rows.
• Where OFFSET in SQL Breaks: Infinite-scroll mobile social feeds with millions of rows. If 20 new posts are published while a user scrolls from page 1 to page 2, OFFSET 20 causes duplicate items to appear on the user's screen. Keyset pagination with WHERE post_id < last_seen_id guarantees consistent, drift-free pagination.

Learn more in our SQL Tutorials hub and practice query design on Topfolio Free SQL Course.

Deep Pagination Performance Benchmark: OFFSET vs Keyset

In benchmarks on PostgreSQL with 5,000,000 indexed transaction rows:

• LIMIT 20 OFFSET 0 (Page 1): Executes in 0.08 milliseconds (Index Scan).
• LIMIT 20 OFFSET 50,000 (Page 2,500): Executes in 85 milliseconds (Scans 50,020 index entries).
• LIMIT 20 OFFSET 500,000 (Page 25,000): Executes in 1,240 milliseconds (Engine reads half a million rows before returning 20).
• Keyset WHERE id > 500000 LIMIT 20: Executes in 0.12 milliseconds regardless of page depth because the B-tree traverses directly to the target record.

Related SQL Tutorials

• What Is SQL? The Complete Beginner to Pro Guide
• SQL Joins Explained with Practical Examples
• SQL Window Functions Guide
• SQL Cheat Sheet for Analysts
• Explore All Guides in the SQL Tutorials Hub