OFFSET Pagination: 50ms to 45s — Keyset Fix

Every production database eventually hits the same wall: queries that ran fine on test data with 10,000 rows suddenly time out in production with 50 million. Dashboards freeze. APIs return 504s. On-call engineers get paged at 2 AM. The culprit is almost always a query the database is executing inefficiently.

SQL query optimisation is a disciplined process, not guesswork. The correct workflow: measure with EXPLAIN ANALYZE, identify the most expensive node, apply the targeted fix, measure again. This guide walks through the most common optimisation opportunities in order of impact — starting with the fixes that cost the least effort and deliver the most improvement.

The Optimisation Hierarchy — Always Measure First

Optimisation without measurement is guesswork. The correct workflow: run EXPLAIN ANALYZE on the slow query, identify the most expensive node, apply one targeted fix, run EXPLAIN ANALYZE again to verify the improvement. Never apply multiple changes simultaneously — you need to isolate which change had impact.

The optimisation hierarchy in order of impact and effort:

1. Indexes — adding the right index is the highest-impact, lowest-effort fix. A single index can reduce a 30-second query to 2 milliseconds. Always exhaust indexing options before rewriting queries.
2. Query rewrites — eliminate unnecessary work: replace SELECT * with column lists, eliminate correlated subqueries, rewrite N+1 patterns as single JOINs, switch OFFSET pagination to keyset pagination.
3. Schema changes — denormalisation, partitioning, archiving old data. Higher effort, sometimes necessary for extreme scale, but only after indexing and query rewrites are exhausted.

The tool for all three levels is EXPLAIN ANALYZE — it drives every decision.

-- STEP 1: Identify slow queries with pg_stat_statements
-- (requires pg_stat_statements extension)
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls             AS avg_rows
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 10;

-- STEP 2: Run EXPLAIN ANALYZE on the worst offender
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours';

-- STEP 3: Apply targeted fix based on plan
-- If Seq Scan on orders(status, created_at):
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at DESC)
    WHERE status = 'pending';  -- partial index: only pending rows

-- STEP 4: Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours';

Common Anti-Patterns and How to Fix Them

Several query patterns consistently cause performance problems at scale. Recognising them by sight lets you fix them immediately.

SELECT * fetches every column from the table, including large text or BLOB columns you may not need. It prevents covering indexes from working (the engine must fetch the heap to get columns not in the index), increases network transfer between database and application, and makes query plans harder to cache effectively. Always list the specific columns you need.

Functions on indexed columns in WHERE silently disable the index. WHERE YEAR(created_at) = 2024 cannot use an index on created_at — the function transforms the value before comparison. The fix is always a range: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.

The N+1 problem is the most damaging ORM-related pattern: fetch a list of customers (1 query), then loop through and fetch each customer's orders separately (N queries). On a list of 100 customers, that's 101 queries. On a list of 1000, it's 1001. Fix with a single JOIN or eager loading.

-- ANTI-PATTERN 1: SELECT *
-- BAD: fetches all columns including large blobs, breaks covering indexes
SELECT * FROM orders WHERE customer_id = 42;

-- GOOD: only the columns you need
SELECT order_id, total, status, created_at
FROM orders
WHERE customer_id = 42;

-- ANTI-PATTERN 2: Function on indexed column
-- BAD: YEAR() disables the index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: range query uses the index
SELECT order_id, total FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

-- ANTI-PATTERN 3: N+1 queries
-- BAD: one query for customers, then one per customer for orders
-- SELECT * FROM customers;  -- returns 1000 customers
-- then for each customer:
-- SELECT * FROM orders WHERE customer_id = :id;  -- 1000 separate queries!

-- GOOD: single JOIN fetches everything in one query
SELECT
    c.customer_id, c.name,
    o.order_id, o.total, o.created_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 2, 3, ..., 1000);  -- or use a subquery

-- ANTI-PATTERN 4: OFFSET pagination
-- BAD: OFFSET 50000 scans and discards 50000 rows
SELECT order_id, total FROM orders ORDER BY order_id LIMIT 20 OFFSET 50000;

-- GOOD: keyset pagination -- O(1) regardless of position
SELECT order_id, total FROM orders
WHERE order_id < :last_seen_id  -- last ID from previous page
ORDER BY order_id DESC
LIMIT 20;

Indexing Strategy for Query Optimisation

Most query optimisation problems are indexing problems. Before rewriting any query, verify that the right indexes exist and are being used.

The process: identify the slow query, run EXPLAIN ANALYZE, look for Seq Scan on the WHERE columns, create the appropriate index, run EXPLAIN ANALYZE again to verify it is used.

Partial indexes are underused but extremely effective: CREATE INDEX idx_pending_orders ON orders(created_at) WHERE status = 'pending'. This index only contains rows where status is 'pending' — it is much smaller than a full index on created_at, fits in cache more easily, and is highly selective for the specific query.

Composite index column order determines usability: put equality conditions first, range conditions last. An index on (status, created_at) supports WHERE status = 'pending' AND created_at > '2024-01-01' using both columns. An index on (created_at, status) would only use the created_at portion for a range query on created_at.

-- DIAGNOSIS: find tables with the most sequential scans
SELECT
    relname         AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_scan - idx_scan AS scan_gap
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

-- Standard composite index: equality first, range last
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at DESC);

-- Partial index: only the rows you query (much smaller)
CREATE INDEX idx_active_sessions
    ON sessions(user_id, last_active)
    WHERE is_active = true;  -- only active sessions indexed

-- Covering index: all SELECT columns in the index
CREATE INDEX idx_order_summary
    ON orders(customer_id, status)
    INCLUDE (order_id, total, created_at);  -- SELECT columns in leaf nodes

-- Index for sort: ORDER BY without a filesort
CREATE INDEX idx_orders_created_desc
    ON orders(created_at DESC);
-- Now: SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 20
-- uses index scan in sorted order -- no Sort node in EXPLAIN

-- Verify index usage after creation
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;