Using LIMIT and OFFSET in PostgreSQL: Best Practices, Performance Impacts, and Alternatives
This guide explains how LIMIT and OFFSET work in PostgreSQL, their use cases for pagination, why large OFFSET values cause performance issues, and when to switch to cursor-based pagination. It includes syntax examples, mandatory ORDER BY requirements, common pitfalls, and alternative methods aligned with PostgreSQL and GitHub REST API standards.
On this page
The short answer
This content covers core functionality, use cases, performance, and best practices for LIMIT/OFFSET in PostgreSQL, with references to official documentation and industry pagination standards.
Basic Functionality of LIMIT and OFFSET in PostgreSQL
LIMIT and OFFSET are PostgreSQL clauses designed to retrieve a subset of query results. The core syntax is: SELECT select_list FROM table_expression [ORDER BY ...] [LIMIT {count | ALL}] [OFFSET start]. LIMIT specifies the maximum number of rows to return, while OFFSET skips the first N rows before starting to fetch results. For example, the query retrieves 20 blog posts, skipping the first 100 entries ordered by creation date, per PostgreSQL’s official documentation.
SELECT id, title, created_at FROM blog_posts ORDER BY created_at DESC LIMIT 20 OFFSET 100;Use Cases for LIMIT/OFFSET in Paginated Workflows
LIMIT/OFFSET is primarily used for paginated data retrieval, a common pattern in APIs and UI displays where large datasets are split into manageable chunks. For instance, the GitHub REST API uses pagination to return subsets of issues (e.g., 30 per page) to avoid overwhelming servers and clients, as noted in their pagination guide. This approach works well for small to medium datasets where page numbers are intuitive for users.
Mandatory ORDER BY for Consistent Results
PostgreSQL requires an ORDER BY clause when using LIMIT/OFFSET to ensure consistent, predictable results. Without ORDER BY, the database returns rows in an arbitrary order, so skipping OFFSET rows will lead to inconsistent subsets across requests. The PostgreSQL documentation explains that the query optimizer may generate different execution plans for varying LIMIT/OFFSET values, which can change row order without explicit sorting, making unordered LIMIT/OFFSET unreliable.
Performance Impact of Large OFFSET Values
Large OFFSET values significantly slow queries because PostgreSQL must compute and discard all skipped rows before applying the LIMIT clause. For example, OFFSET 10,000 requires reading and processing 10,000 rows that are never returned, increasing I/O and CPU usage. The PostgreSQL documentation explicitly states that rows skipped by OFFSET are fully computed inside the server, making deep OFFSET inefficient for large datasets.
Alternative Pagination: Cursor-Based Methods
Cursor-based pagination is a more efficient alternative to deep OFFSET, especially for large or frequently updated datasets. Instead of skipping rows, it uses a unique, ordered value (like a timestamp or primary key ID) to fetch the next set of results. GitHub’s REST API uses this approach with parameters like 'before' or 'after' to navigate pages, avoiding the overhead of counting and skipping rows. This method is preferred for APIs and datasets where deep pagination is needed.
Best Practices for Safe LIMIT/OFFSET Usage
To use LIMIT/OFFSET safely: 1) Always include an ORDER BY clause with a unique, indexed column (e.g., ID, created_at) to ensure consistent results and speed up sorting. 2) Keep OFFSET values small (avoid OFFSET > ~1000) to minimize performance overhead. 3) Validate pagination parameters (e.g., enforce a maximum LIMIT) to prevent excessive data retrieval. 4) Use the same ORDER BY column across all pages to avoid shifting results.
Common Pitfalls to Avoid
Key mistakes when using LIMIT/OFFSET include: 1) Omitting ORDER BY, leading to unpredictable row subsets. 2) Using unindexed columns in ORDER BY, which slows sorting for large datasets. 3) Relying on OFFSET for deep pagination, which causes significant performance degradation. 4) Assuming OFFSET works correctly with concurrent inserts, as new rows inserted between page requests can shift the subset of results, leading to skipped or duplicated entries.
When to Replace LIMIT/OFFSET with Other Methods
Replace LIMIT/OFFSET with cursor-based pagination when: 1) You need deep pagination (OFFSET > ~1000). 2) The dataset has frequent inserts or updates, as OFFSET can skip or duplicate rows. 3) You need consistent, efficient pagination for large datasets. Cursor-based methods align with modern API standards (like GitHub’s) and avoid the performance overhead of deep OFFSET, making them better suited for most production use cases.
Things to check
- Query includes ORDER BY when using LIMIT/OFFSET to ensure consistent results
- OFFSET values are not excessively large (avoid deep pagination)
- Cursor-based pagination is used for datasets with frequent inserts/updates
- ORDER BY columns are indexed to optimize query performance
Where this applies
LIMIT/OFFSET is inefficient for deep pagination (OFFSET > ~1000) due to row computation overhead; requires an ORDER BY clause to ensure consistent, predictable results; may skip or duplicate rows in datasets with concurrent inserts or updates; performs poorly with unindexed ORDER BY columns; does not scale well for large datasets and is not aligned with modern API standards like GitHub's cursor-based pagination