Chapter 22: PostgreSQL LIMIT

Part 1: What is the LIMIT Clause?

The LIMIT clause restricts the number of rows returned by a query. It’s typically used with ORDER BY to get meaningful subsets like “top 10 bestsellers” or “most recent 5 orders.”

The Basic Syntax

The row_count specifies the maximum number of rows to return.

Part 2: Setting Up Our Example Data

Let’s create a comprehensive dataset to explore all the ways LIMIT can be used:

Part 3: Basic LIMIT Usage

1. Simple LIMIT – First N Rows

The most basic use – get the first 5 products from the table:

Result:

Important: Without ORDER BY, the rows are returned in whatever order PostgreSQL finds them (usually insertion order, but not guaranteed). This is rarely what you want!

2. LIMIT with ORDER BY – Getting Meaningful Results

This is the real power of LIMIT – combine it with ORDER BY to get top or bottom results:

Result:

Result:

3. LIMIT with WHERE – Filtered Top Results

Result:

Part 4: LIMIT with OFFSET – Pagination

This is one of the most common real-world uses of LIMIT – implementing pagination. The OFFSET clause skips a specified number of rows before starting to return rows.

Basic Syntax with OFFSET

1. Simple Pagination

Show products 11-20 (page 2 with 10 items per page):

Result (rows 11-20):

2. Building a Pagination System

Here’s how you’d typically implement pagination in an application:

The formula is simple: OFFSET = (page_number - 1) * page_size

3. Pagination with Custom Sorting

4. Getting Total Count for Pagination

When implementing pagination, you usually need to know the total number of pages:

Part 5: LIMIT with Different Data Types

1. LIMIT with Dates – Most Recent Items

Result:

2. LIMIT with Aggregates – Top Categories

Result:

(Many categories have 5 products in our sample)

3. LIMIT with NULL Values

Result:

Part 6: LIMIT with DISTINCT

When using DISTINCT, LIMIT applies after duplicates are removed:

Result:

Important: ORDER BY with DISTINCT

When using DISTINCT, the ORDER BY columns must appear in the SELECT list:

Part 7: LIMIT with JOINS

LIMIT works beautifully with joined tables. Let’s create an orders table for more examples:

Result:

Part 8: Advanced LIMIT Techniques

1. LIMIT with Window Functions

Get top 3 products by rating in each category:

Result (partial):

This is more powerful than simple LIMIT because it gives you top N per group.

2. Random Sampling with LIMIT

Get 5 random products:

Each time you run this, you get a different set of 5 products.

3. LIMIT with Subqueries

Find products that are more expensive than the 5th most expensive product:

4. LIMIT with UNION

Get top 3 from two different categories:

Part 9: Performance Considerations

1. LIMIT with Indexes

LIMIT can be extremely fast when combined with appropriate indexes:

2. How PostgreSQL Optimizes LIMIT

When you use ORDER BY ... LIMIT n, PostgreSQL tries to avoid sorting the entire table. If an index provides the ordered data, it can just scan the index until it finds n rows:

Look for “Limit” in the plan – it might show that PostgreSQL only needed to scan a small portion of an index.

3. Large OFFSET Performance

Large offsets can be slow because PostgreSQL still has to count through all the skipped rows:

For large offsets, consider:

• Using keyset pagination (WHERE price < last_seen_price)

• Materialized views

• Caching

4. Keyset Pagination (Better for Large Offsets)

Instead of OFFSET, use a WHERE clause based on the last seen value:

This scales much better than OFFSET for large datasets.

Part 10: Real-World Examples

Example 1: E-commerce Product Listing Page

Example 2: Admin Dashboard – Recent Orders with Issues

Example 3: Best Sellers Report

Example 4: Inventory Alerts

Example 5: Customer Purchase History

Part 11: Common Mistakes and How to Avoid Them

Mistake 1: Using LIMIT Without ORDER BY

Mistake 2: Assuming OFFSET is Zero-Based

Mistake 3: Large OFFSET Values

Mistake 4: Forgetting NULL Handling

Mistake 5: Using LIMIT in Subqueries Incorrectly

Summary: The LIMIT Philosophy

The LIMIT clause is your tool for controlling data volume. Master these concepts:

1. Basic LIMIT – Get first N rows

2. LIMIT with ORDER BY – Get meaningful top/bottom results

3. Pagination with OFFSET – Navigate through data page by page

4. Performance awareness – Indexes and large offset considerations

5. Advanced techniques – Window functions for “top N per group”

Remember the query execution order:

1. FROM/JOIN – Get all possible rows

2. WHERE – Filter rows

3. GROUP BY – Group rows

4. HAVING – Filter groups

5. SELECT – Compute expressions

6. ORDER BY – Sort results

7. LIMIT/OFFSET – Trim results – HERE!

This means LIMIT works on the final, sorted result set. It’s the very last operation.

Key takeaways:

• Always use ORDER BY with LIMIT unless you truly don’t care about order

• OFFSET is great for pagination but watch performance with large offsets

• Index the columns you sort by for fast LIMIT queries

• Consider keyset pagination for better performance with large datasets

• Use window functions when you need “top N per group”

The LIMIT clause transforms your database from a firehose of data into a controlled faucet, letting you take exactly what you need, when you need it. Whether you’re building user interfaces, generating reports, or analyzing data, LIMIT is essential for creating responsive, efficient applications.

Would you like me to elaborate on any specific aspect of LIMIT, such as more complex pagination strategies, performance optimization, or integration with other SQL features?