PostgreSQL LIMIT and OFFSET

What is LIMIT?

The LIMIT clause restricts the number of rows returned by a query. It's commonly used for pagination, getting top results, or sampling data.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Basic LIMIT Examples

Example 1: Get First N Rows

-- Get first 10 customers
SELECT * FROM customers
LIMIT 10;

-- Get top 5 highest-paid employees
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Example 2: Sample Data

-- Get a sample of 100 records for testing
SELECT * FROM large_table
LIMIT 100;

-- Get random sample
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 20;

OFFSET Clause

OFFSET skips a specified number of rows before returning results. It's used with LIMIT for pagination.

Basic OFFSET Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number OFFSET number;

OFFSET Examples

-- Skip first 10 rows, return next 10
SELECT * FROM customers
LIMIT 10 OFFSET 10;

-- Get page 3 (rows 21-30)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Pagination Patterns

Pattern 1: Basic Pagination

-- Page 1 (rows 1-10)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Pattern 2: Dynamic Pagination

-- Calculate offset based on page number
-- page_size = 20, page_number = 3
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET (3 - 1) * 20;  -- OFFSET 40

Pattern 3: Pagination with Total Count

-- Get paginated results with total count
WITH paginated_data AS (
    SELECT *,
           COUNT(*) OVER() as total_count
    FROM products
    WHERE category = 'Electronics'
    ORDER BY price DESC
)
SELECT *
FROM paginated_data
LIMIT 10 OFFSET 0;

LIMIT with ORDER BY

LIMIT should almost always be used with ORDER BY to ensure consistent results.

-- Get top 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- Get 5 most recent orders
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Get oldest 10 customers
SELECT customer_name, registration_date
FROM customers
ORDER BY registration_date ASC
LIMIT 10;

Advanced LIMIT Usage

Example 1: Top N per Group

-- Get top 3 products per category
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
    FROM products
) ranked
WHERE rn <= 3;

Example 2: LIMIT with Subqueries

-- Get customers who placed the most orders (top 10)
SELECT c.customer_id, c.customer_name, COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count DESC
LIMIT 10;

Example 3: LIMIT with DISTINCT

-- Get 20 unique product categories
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 20;

LIMIT ALL

LIMIT ALL returns all rows (equivalent to omitting LIMIT):

-- Returns all rows
SELECT * FROM customers
LIMIT ALL;

-- Useful in dynamic queries where limit might be optional
SELECT * FROM customers
LIMIT CASE WHEN @apply_limit THEN 10 ELSE NULL END;

Performance Considerations

1. Use Indexes with ORDER BY

-- Create index for better performance
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- Query will be faster
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

2. Avoid Large OFFSET Values

-- Slow: Large offset scans many rows
SELECT * FROM large_table
ORDER BY id
LIMIT 10 OFFSET 1000000;

-- Better: Use keyset pagination
SELECT * FROM large_table
WHERE id > 1000000
ORDER BY id
LIMIT 10;

3. Keyset Pagination (Seek Method)

More efficient than OFFSET for large datasets:

-- First page
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

-- Next page (using last order_id from previous page)
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND (order_date, order_id) < ('2024-01-15', 12345)
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

Common Patterns

Pattern 1: Top N Analysis

-- Top 10 customers by revenue
SELECT customer_id,
       customer_name,
       SUM(order_amount) as total_revenue
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id, customer_name
ORDER BY total_revenue DESC
LIMIT 10;

Pattern 2: Recent Records

-- Get 50 most recent log entries
SELECT * FROM application_logs
ORDER BY created_at DESC
LIMIT 50;

Pattern 3: Random Sampling

-- Get random 100 products for A/B testing
SELECT * FROM products
WHERE status = 'active'
ORDER BY RANDOM()
LIMIT 100;

LIMIT with JOINs

-- Get top 5 customers with their latest order
SELECT DISTINCT ON (c.customer_id)
       c.customer_id,
       c.customer_name,
       o.order_id,
       o.order_date,
       o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date DESC
LIMIT 5;

LIMIT in Subqueries

-- Get products that are in top 10 by sales
SELECT *
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 10
);

Practical Examples

Example 1: Dashboard Top Items

-- Dashboard: Top 5 selling products this month
SELECT p.product_name,
       SUM(oi.quantity) as units_sold,
       SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 5;

Example 2: Leaderboard

-- User leaderboard: Top 20 by points
SELECT user_id,
       username,
       total_points,
       RANK() OVER (ORDER BY total_points DESC) as rank
FROM users
WHERE status = 'active'
ORDER BY total_points DESC
LIMIT 20;

Example 3: Preview Data

-- Preview first 10 rows of imported data
SELECT * FROM imported_data
ORDER BY import_timestamp DESC
LIMIT 10;

FETCH Alternative

PostgreSQL also supports the SQL standard FETCH clause:

-- LIMIT syntax
SELECT * FROM customers
LIMIT 10 OFFSET 5;

-- FETCH syntax (SQL standard)
SELECT * FROM customers
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;

-- FETCH NEXT is equivalent to FETCH FIRST
SELECT * FROM customers
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

Common Mistakes and Solutions

Mistake 1: LIMIT Without ORDER BY

-- Bad: Results are unpredictable
SELECT * FROM products
LIMIT 10;

-- Good: Consistent results
SELECT * FROM products
ORDER BY product_id
LIMIT 10;

Mistake 2: Using LIMIT for Existence Check

-- Inefficient
SELECT * FROM orders
WHERE customer_id = 123
LIMIT 1;

-- Better: Use EXISTS
SELECT EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = 123
);

Mistake 3: Large OFFSET Performance

-- Slow for large offsets
SELECT * FROM orders
ORDER BY order_date
LIMIT 10 OFFSET 100000;

-- Better: Use WHERE clause
SELECT * FROM orders
WHERE order_date < '2023-01-01'
ORDER BY order_date DESC
LIMIT 10;

Best Practices

1. Always Use ORDER BY: Ensure consistent results across queries
2. Index ORDER BY Columns: Improve query performance
3. Avoid Large OFFSETs: Use keyset pagination for better performance
4. Use LIMIT for Testing: Sample data during development
5. Consider Total Count: Include total count for pagination UI
6. Use FETCH for Portability: FETCH is SQL standard, LIMIT is PostgreSQL-specific

Summary

LIMIT and OFFSET are essential for result set control:

• LIMIT restricts the number of rows returned
• OFFSET skips rows before returning results
• Combine with ORDER BY for consistent results
• Use for pagination, top-N queries, and sampling
• Consider performance with large offsets
• Use keyset pagination for large datasets
• FETCH is the SQL standard alternative to LIMIT

Mastering LIMIT and OFFSET is crucial for building efficient paginated applications and data analysis queries.