Query Optimization Tips - Learning Module

Loading content...

0/241

Limiting Results

The Unbounded Query Crisis

One of the most dangerous patterns in production databases is the unbounded query—a SELECT statement with no practical limit on how many rows it can return. In development with small datasets, these queries work fine. In production with millions of rows, they become ticking time bombs.

A single unbounded query can:

Exhaust database memory forcing emergency swapping
Saturate network connections blocking other traffic
Timeout application requests triggering cascading failures
Lock table resources preventing writes for minutes
Crash application servers attempting to process infinite results

Mastering result limiting techniques is essential for building robust, production-grade database applications that perform consistently regardless of data growth.

What You Will Learn

By the end of this page, you'll master LIMIT/OFFSET pagination and its limitations, understand keyset (cursor-based) pagination for high-performance scenarios, learn TOP-N query optimization patterns, and develop strategies for safely processing large result sets.

The Cost of Unbounded Queries

Before diving into solutions, let's understand exactly what happens when a query returns more data than systems can handle.

Memory Allocation Chain:

When you execute SELECT * FROM large_table (no LIMIT), the following sequence occurs:

Resource Consumption Cascade

•Database Buffer Allocation: The database allocates memory to buffer all matching rows before starting transfer. For 10 million rows at 1KB each: 10 GB required.
•Network Buffer Saturation: Network send buffers fill with serialized data. TCP windows expand, consuming kernel memory.
•Driver Buffer Accumulation: Database drivers often buffer the entire result set before returning control to your application.
•Application Object Creation: Your code instantiates objects for each row—10 million objects consuming heap space.
•Garbage Collection Pressure: Memory pressure triggers GC, pausing your application while cleaning up partially-processed results.

Real-World Failure Scenario:

Consider an admin dashboard that displays 'recent orders' using this query:

Unbounded Query Example
SQL
1
2
3
4
5
6
7
8
9
10
11
-- Innocent-looking query
SELECT order_id, customer_name, total, order_date
FROM orders
ORDER BY order_date DESC;
 
-- Day 1: 100 orders returned - works perfectly
-- Month 6: 50,000 orders - starts feeling slow
-- Year 2: 500,000 orders - application timeouts
-- Year 5: 5,000,000 orders - cascading system failure
 
-- The query never changed - but the data kept growing

Unbounded Query Growth Impact
Time Frame	Row Count	Response Time	Memory Usage	User Experience
Launch	100	50 ms	10 KB	Instant
6 months	50,000	2 seconds	5 MB	Noticeable delay
1 year	200,000	12 seconds	20 MB	Frustrating
2 years	500,000	45 seconds	50 MB	Timeouts begin
5 years	5,000,000	Timeout/Crash	500 MB+	System failure

The Silent Killer

Unbounded queries are especially dangerous because they 'work' during development and early production. The failure is gradual and often attributed to 'the database being slow' rather than the actual cause: queries that scale linearly with data volume.

LIMIT Clause Fundamentals

The LIMIT clause is the first line of defense against unbounded queries. It caps the number of rows returned regardless of how many match the WHERE clause.

Standard Syntax Variations:

Different database systems use different syntax for the same concept:

LIMIT Syntax Across Databases
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL, MySQL, SQLite: LIMIT syntax
SELECT order_id, total FROM orders
ORDER BY order_date DESC
LIMIT 100;
 
-- SQL Server: TOP syntax
SELECT TOP 100 order_id, total FROM orders
ORDER BY order_date DESC;
 
-- Oracle 12c+: FETCH FIRST syntax (ANSI SQL:2008)
SELECT order_id, total FROM orders
ORDER BY order_date DESC
FETCH FIRST 100 ROWS ONLY;
 
-- Oracle (traditional): ROWNUM in subquery
SELECT * FROM (
    SELECT order_id, total FROM orders
    ORDER BY order_date DESC
) WHERE ROWNUM <= 100;
 
-- DB2: FETCH FIRST syntax
SELECT order_id, total FROM orders
ORDER BY order_date DESC
FETCH FIRST 100 ROWS ONLY;

How LIMIT Optimizes Query Execution:

When the optimizer sees a LIMIT clause, it can apply several optimizations:

Early Termination: Stop scanning after finding enough rows
Sort Optimization: Use top-N sort algorithm instead of full sort
Index Utilization: Leverage ordered indexes to avoid sorting entirely
Memory Reduction: Allocate buffer space for limited rows only

LIMIT Optimization Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Without LIMIT: Full table scan + complete sort
-- Complexity: O(n) scan + O(n log n) sort
SELECT order_id, total FROM orders
ORDER BY order_date DESC;
 
-- With LIMIT: Can use index + early termination
-- Complexity: O(k) where k is limit, if index exists on order_date
SELECT order_id, total FROM orders
ORDER BY order_date DESC
LIMIT 10;
 
-- Execution plan difference:
-- Without LIMIT: "Sort (cost=50000) -> Seq Scan (cost=40000)"
-- With LIMIT: "Limit -> Index Scan Backward (cost=0.5)"

Index Alignment

For LIMIT to provide maximum optimization, ensure the ORDER BY columns have a matching index. An index on (order_date DESC) allows the database to return the top N rows by simply reading the first N index entries—no sorting required.

OFFSET Pagination Patterns

The most common pagination approach combines LIMIT with OFFSET, allowing users to navigate through result pages. While straightforward to implement, this approach has significant performance implications at scale.

Basic OFFSET Pagination:

OFFSET Pagination
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Page 1: Get first 20 results
SELECT order_id, customer_name, total
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 0;
 
-- Page 2: Skip first 20, get next 20
SELECT order_id, customer_name, total
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 20;
 
-- Page 50: Skip first 980, get next 20
SELECT order_id, customer_name, total
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 980;
 
-- Page 5000: Skip first 99,980 rows!
SELECT order_id, customer_name, total
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 99980;

The OFFSET Performance Problem:

Here's the critical issue: the database must still process all skipped rows. OFFSET 99980 doesn't magically jump to row 99,981—it reads and discards 99,980 rows first.

OFFSET Performance Degradation
Page Number	OFFSET Value	Rows Processed	Rows Returned	Efficiency
1	0	20	20	100%
10	180	200	20	10%
100	1,980	2,000	20	1%
1,000	19,980	20,000	20	0.1%
10,000	199,980	200,000	20	0.01%

Visualization of the Problem:

Think of OFFSET like reading a book to find page 500. You don't have a table of contents (index for the specific page), so you must flip through 499 pages to reach your destination. Every 'next page' request requires flipping from page 1 again.

Performance Measurement:

Measuring OFFSET Impact
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- PostgreSQL: Measure execution time at different offsets
EXPLAIN ANALYZE 
SELECT * FROM orders ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;
-- Execution Time: 0.5 ms
 
EXPLAIN ANALYZE 
SELECT * FROM orders ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;
-- Execution Time: 25 ms
 
EXPLAIN ANALYZE 
SELECT * FROM orders ORDER BY created_at DESC 
LIMIT 20 OFFSET 100000;
-- Execution Time: 250 ms
 
EXPLAIN ANALYZE 
SELECT * FROM orders ORDER BY created_at DESC 
LIMIT 20 OFFSET 1000000;
-- Execution Time: 2500 ms (2.5 seconds!)

OFFSET Scales Linearly

Query time with OFFSET grows linearly with the offset value. Page 10,000 takes 10,000x longer than page 1. For large datasets with deep pagination, OFFSET-based pagination becomes unusable and can cause database resource exhaustion.

Keyset (Cursor) Pagination

Keyset pagination (also called cursor-based or seek pagination) solves the OFFSET performance problem by using values from the last seen row to fetch the next page. This approach maintains consistent O(1) performance regardless of how deep into the dataset you navigate.

The Keyset Concept:

Instead of saying 'skip N rows,' you say 'get rows after this value.'

Keyset Pagination
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Setup: Orders table with order_id as unique, monotonically increasing
-- Page 1: Get first 20 orders
SELECT order_id, customer_name, total, order_date
FROM orders
ORDER BY order_id DESC
LIMIT 20;
-- Returns order_ids 1000, 999, 998, ... , 981
-- Remember last_seen_id = 981
 
-- Page 2: Get next 20 orders after id 981
SELECT order_id, customer_name, total, order_date
FROM orders
WHERE order_id < 981  -- Keyset condition
ORDER BY order_id DESC
LIMIT 20;
-- Returns order_ids 980, 979, 978, ... , 961
-- Remember last_seen_id = 961
 
-- Page 5000: Still instant! Just different keyset value
SELECT order_id, customer_name, total, order_date
FROM orders
WHERE order_id < 123  -- Last seen id from page 4999
ORDER BY order_id DESC
LIMIT 20;
-- Execution time: Same as page 1! (~0.5 ms)

Why Keyset Pagination is O(1):

With OFFSET, the database:

Finds all matching rows
Sorts them
Counts through OFFSET rows
Returns LIMIT rows

With Keyset, the database:

Seeks directly to the keyset position in the index
Reads LIMIT rows forward
Returns immediately

No rows are skipped—the index provides direct access to the starting position.

OFFSET Pagination

•Performance degrades with page depth
•Page 10,000 is 10,000x slower than page 1
•Vulnerable to skipping/duplicating rows on concurrent inserts
•Simple to implement
•Supports 'jump to page N' directly

Keyset Pagination

•Constant performance at any depth
•Page 10,000 is same speed as page 1
•Consistent results during concurrent modifications
•Slightly more complex to implement
•Requires sequential navigation (prev/next)

Compound Keyset for Non-Unique Columns:

When paginating by a non-unique column (like order_date), use a compound keyset with a unique tiebreaker:

Compound Keyset
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Problem: Multiple orders can have the same order_date
-- Solution: Use (order_date, order_id) as compound keyset
 
-- Page 1
SELECT order_id, customer_name, total, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20;
-- Last row: order_date='2024-01-15', order_id=5432
 
-- Page 2: Row comparison for compound keyset
SELECT order_id, customer_name, total, order_date
FROM orders
WHERE (order_date, order_id) < ('2024-01-15', 5432)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;
 
-- Alternative syntax (more compatible):
SELECT order_id, customer_name, total, order_date
FROM orders
WHERE order_date < '2024-01-15'
   OR (order_date = '2024-01-15' AND order_id < 5432)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

Index Support for Keyset

For optimal keyset pagination performance, create a compound index matching your ORDER BY: CREATE INDEX idx_orders_date_id ON orders(order_date DESC, order_id DESC). This allows pure index-only navigation with no table access.

TOP-N Query Optimization

A common pattern is retrieving the 'top N' records by some criteria—highest revenue customers, most recent orders, best-rated products. These queries are highly optimizable when structured correctly.

Top-N Sort Optimization:

Modern query optimizers recognize TOP-N patterns and use specialized algorithms:

Top-N Sort
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Find top 10 highest-value orders
SELECT order_id, customer_name, total
FROM orders
ORDER BY total DESC
LIMIT 10;
 
-- Without optimization: Full sort O(n log n)
-- 1. Scan all 10 million rows
-- 2. Sort all 10 million by total
-- 3. Return first 10
 
-- With Top-N optimization: Heap-based O(n log k) where k=10
-- 1. Maintain a min-heap of size 10
-- 2. Scan rows, each taking O(log 10) to maintain heap
-- 3. Return heap contents
 
-- For 10 million rows:
-- Full sort: 10M * log(10M) = 230 million comparisons
-- Top-N heap: 10M * log(10) = 33 million comparisons (7x faster)

Index-Backed Top-N:

When an index exists on the ORDER BY column, Top-N becomes even more efficient:

Index-Backed Top-N
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Assuming index: CREATE INDEX idx_orders_total ON orders(total DESC)
 
SELECT order_id, total
FROM orders
ORDER BY total DESC
LIMIT 10;
 
-- Execution plan: "Limit -> Index Scan idx_orders_total"
-- The database reads exactly 10 index entries and stops
-- Complexity: O(k) where k is the limit - constant time!
 
-- Compare to WHERE-filtered Top-N:
SELECT order_id, total
FROM orders
WHERE status = 'completed'
ORDER BY total DESC
LIMIT 10;
 
-- Now needs: Index on (status, total DESC) for optimal performance
-- CREATE INDEX idx_orders_status_total ON orders(status, total DESC)

Top-N Per Group (A Common Challenge):

Retrieving top N items for each category requires special techniques:

Top-N Per Group
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Goal: Top 3 highest-value orders per customer
 
-- Method 1: Window Functions (most elegant)
SELECT customer_id, order_id, total
FROM (
    SELECT 
        customer_id,
        order_id,
        total,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY total DESC
        ) as rn
    FROM orders
) ranked
WHERE rn <= 3;
 
-- Method 2: LATERAL JOIN (PostgreSQL, SQL Server)
SELECT c.customer_id, top_orders.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, total
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY total DESC
    LIMIT 3
) top_orders;
 
-- Method 3: Correlated subquery with EXISTS
SELECT o1.customer_id, o1.order_id, o1.total
FROM orders o1
WHERE (
    SELECT COUNT(*) FROM orders o2
    WHERE o2.customer_id = o1.customer_id
    AND o2.total > o1.total
) < 3;

Method Selection

Window functions are typically most efficient for Top-N per group on modern databases. LATERAL JOIN excels when each group is accessed via an index. Test all approaches with your specific data distribution—performance varies significantly based on group sizes and cardinality.

Defensive Limiting Strategies

Beyond pagination, result limiting is a defensive programming technique to protect your systems from unexpected data volumes.

Always-Limit Pattern:

Establish a maximum result limit for all queries, even those not explicitly paginated:

Defensive Limits
JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
// Application-level defensive limits
const MAX_RESULTS = 10000;
 
async function findOrders(filters) {
    const query = buildQuery(filters);
    
    // Always append a safety limit
    const safeQuery = query + ` LIMIT ${MAX_RESULTS + 1}`;
    const results = await db.execute(safeQuery);
    
    // Detect if we hit the limit (indicating unbounded data)
    if (results.length > MAX_RESULTS) {
        logger.warn('Query returned more than MAX_RESULTS', { 
            filters, 
            rowCount: results.length 
        });
        
        // Option 1: Return truncated results with warning
        return {
            data: results.slice(0, MAX_RESULTS),
            truncated: true,
            message: 'Results limited to 10,000 records'
        };
        
        // Option 2: Throw error requiring more specific filters
        // throw new TooManyResultsError('Please refine your search');
    }
    
    return { data: results, truncated: false };
}

Database-Level Safeguards:

Some databases support configuration-level limits:

Database Level Limits
SQL
1
2
3
4
5
6
7
8
9
10
11
12
-- MySQL: Set max rows in results
SET GLOBAL sql_select_limit = 10000;
 
-- PostgreSQL: Use statement timeout as safety net
SET statement_timeout = '30s';
 
-- SQL Server: Row goal hint (influences optimizer, not hard limit)
SELECT order_id, total FROM orders
OPTION (FAST 100);  -- Optimize for returning first 100 quickly
 
-- Oracle: Row limit in session
ALTER SESSION SET SQL_SELECT_LIMIT = 10000;

Streaming and Chunked Processing:

For batch jobs that must process all rows, use streaming or chunked approaches:

Chunked Processing
Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# Bad: Load all rows into memory
def process_all_orders_dangerous():
    orders = db.execute("SELECT * FROM orders")  # 10 million rows!
    for order in orders:  # Memory exhausted before loop starts
        process(order)
 
# Good: Stream with server-side cursor
def process_all_orders_streaming():
    with db.cursor(name='order_cursor') as cursor:
        cursor.execute("SELECT * FROM orders")
        while True:
            batch = cursor.fetchmany(1000)  # Fetch 1000 at a time
            if not batch:
                break
            for order in batch:
                process(order)
 
# Good: Keyset-based chunking
def process_all_orders_keyset():
    last_id = 0
    while True:
        batch = db.execute("""
            SELECT * FROM orders 
            WHERE order_id > %s 
            ORDER BY order_id 
            LIMIT 1000
        """, [last_id])
        
        if not batch:
            break
            
        for order in batch:
            process(order)
        
        last_id = batch[-1]['order_id']

Cursor vs. Keyset Chunking

Server-side cursors maintain state on the database, consuming resources. Keyset chunking is stateless but requires an indexed ordering column. For long-running batch jobs, keyset chunking is more resilient to connection failures and can be easily parallelized.

Common Pitfalls and Solutions

Even with good intentions, result limiting has common pitfalls. Here are patterns to avoid and their solutions.

Pitfall 1: Incorrect Total Count with LIMIT:

Total Count Pitfall
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Wrong: Getting total count and page in separate queries (race condition)
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 5000
SELECT * FROM orders WHERE status = 'pending' LIMIT 20; -- Might be different!
 
-- Better: Use window function for total
SELECT 
    order_id, 
    total,
    COUNT(*) OVER() as total_count  -- Includes total in each row
FROM orders 
WHERE status = 'pending'
LIMIT 20;
 
-- Best for large tables: Approximate count
-- PostgreSQL
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';
 
-- With filters, use EXPLAIN to get row estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Read "rows=" from output for estimate

Pitfall 2: LIMIT Without ORDER BY:

LIMIT Without ORDER BY
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Dangerous: No ORDER BY means unpredictable results
SELECT order_id, total FROM orders LIMIT 10;
 
-- Which 10 orders? Could be any 10!
-- Results may differ between:
-- - Different executions
-- - Different database replicas  
-- - Before and after VACUUM/optimize
 
-- Always specify ORDER BY with LIMIT
SELECT order_id, total FROM orders 
ORDER BY order_id  -- Deterministic, repeatable
LIMIT 10;
 
-- Exception: When you truly don't care which rows (sampling)
SELECT * FROM orders TABLESAMPLE SYSTEM(0.1);  -- Random 0.1%

Pitfall 3: Duplicate/Missing Rows During Pagination:

Pagination Consistency
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Scenario: User is on page 5, new order inserted
-- With OFFSET pagination:
-- Page 5: OFFSET 80 LIMIT 20 returns rows 81-100
-- New row inserted, pushes all rows down
-- User clicks "Next"  
-- Page 6: OFFSET 100 LIMIT 20 - row 100 appears again (duplicate!)
 
-- Solution 1: Keyset pagination (inherently consistent)
SELECT * FROM orders 
WHERE order_id > :last_seen_id 
ORDER BY order_id 
LIMIT 20;
 
-- Solution 2: Read consistency with timestamp snapshot
SELECT * FROM orders 
WHERE created_at < :query_start_time  -- Freeze point
ORDER BY created_at DESC
LIMIT 20 OFFSET 80;
 
-- Solution 3: Explicit transaction isolation (expensive)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 0;
-- Keep transaction open for all pages (not recommended)

Test at Scale

Pagination bugs often only manifest at scale. Test with production-size data and realistic concurrent write load. A pagination system that works perfectly with 1,000 rows may break with 1,000,000 rows and 100 writes/second.

Advanced Result Limiting Patterns

For complex scenarios, these advanced patterns provide efficient result limiting.

Deferred Join Pattern:

When selecting many columns but filtering on few, fetch IDs first:

Deferred Join
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Slow: Fetches all columns during pagination scan
SELECT o.*, c.name, c.email, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 10000;
 
-- Fast: Deferred join - pagination on IDs only
SELECT o.*, c.name, c.email, p.title
FROM (
    SELECT order_id
    FROM orders
    WHERE status = 'pending'
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 10000
) page
JOIN orders o ON o.order_id = page.order_id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;
 
-- Why faster: Inner query scans small index, outer query 
-- touches only 20 rows across all tables

Materialized Pagination:

For frequently-accessed paginated views, pre-compute page assignments:

Materialized Pagination
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Create a materialized view with page numbers
CREATE MATERIALIZED VIEW orders_paginated AS
SELECT 
    order_id,
    customer_id,
    total,
    order_date,
    (ROW_NUMBER() OVER (ORDER BY order_date DESC) - 1) / 20 + 1 as page_num
FROM orders
WHERE status = 'active';
 
-- Create index on page number
CREATE INDEX idx_orders_page ON orders_paginated(page_num);
 
-- Fetching any page is now O(1)
SELECT order_id, customer_id, total, order_date
FROM orders_paginated
WHERE page_num = 5000;  -- Instant!
 
-- Refresh periodically
REFRESH MATERIALIZED VIEW orders_paginated;

Bidirectional Keyset Navigation:

Enabling both 'previous' and 'next' page navigation with keyset:

Bidirectional Keyset
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Current page centered on order_id = 500
-- Display ordering: newest first (ORDER BY order_id DESC)
 
-- Next page (older orders)
SELECT order_id, total, order_date
FROM orders
WHERE order_id < 481  -- Last id from current page
ORDER BY order_id DESC
LIMIT 20;
 
-- Previous page (newer orders)
-- Reverse the query and re-reverse results
SELECT * FROM (
    SELECT order_id, total, order_date
    FROM orders  
    WHERE order_id > 500  -- First id from current page
    ORDER BY order_id ASC  -- Opposite direction
    LIMIT 20
) prev
ORDER BY order_id DESC;  -- Restore display order

API Design Tip

Return cursor tokens in your API response: { data: [...], nextCursor: 'eyJpZCI6NDgxfQ==', prevCursor: 'eyJpZCI6NTAxfQ==' }. Base64-encode the keyset values. This hides implementation details and prevents cursor manipulation while enabling efficient navigation.

Summary: Result Limiting Mastery

Result limiting is fundamental to building database applications that remain performant as data grows. Let's consolidate the key principles:

Key Takeaways

•Unbounded queries are time bombs — they work during development but fail catastrophically as data grows, with no code change to trigger the failure.
•LIMIT provides essential protection — always cap result sizes, even when not implementing pagination.
•OFFSET pagination degrades linearly — page 10,000 is 10,000x slower than page 1, making deep pagination unusable.
•Keyset pagination provides constant-time performance — regardless of depth, every page takes the same time to fetch.
•Top-N queries are heavily optimized — with proper indexes, the database can return top K results in O(K) time.
•Defensive limiting protects production — implement maximum result limits at the application layer as a safety net.

What's next:

The next page explores efficient join techniques. You'll learn how join order affects performance, strategies for optimizing multi-table queries, and when to restructure queries to leverage different join algorithms.

Page Complete

You now understand result limiting as both a feature (pagination) and a defensive practice (resource protection). These techniques ensure your queries remain performant at any scale, protecting both user experience and system stability.