The slowest query I ever found in a MySQL fleet was not a bad join or a missing index. It was page 4,000 of an order-export endpoint: LIMIT 50 OFFSET 200000, called by a partner integration that walked the entire dataset nightly. The same pattern later greeted me on PostgreSQL, in a different company, with almost identical symptoms. OFFSET pagination is one of the few performance problems that is perfectly portable between engines.
If you are migrating from MySQL to PostgreSQL, this is good news in disguise: the fix, keyset pagination, is also portable, and PostgreSQL makes the ergonomics slightly nicer. Here is how the problem and the fix look on each side.
Why OFFSET gets slower with every page
LIMIT 50 OFFSET 200000 does not mean "jump to row 200,001." Neither engine has an index structure that can seek to the Nth visible row directly, because visibility depends on your transaction snapshot. Both engines execute the query by producing 200,050 rows in order and throwing away the first 200,000. The work grows linearly with page depth: page one is fast, page ten is fine, page four thousand is a scan.
There is a second, sneakier problem: consistency. Rows inserted or deleted between page requests shift every subsequent offset, so users see duplicated or skipped rows as they page. OFFSET pagination is not just slow at depth; it is incorrect under concurrent writes, which is most production systems.
In InnoDB, deep offsets also do their scanning through the buffer pool, evicting hot pages to count rows nobody will see. In PostgreSQL the same applies to shared buffers, and the discarded rows still go through visibility checks. On both engines the query looks innocent in code review and only reveals itself under data volume, which is why it so often shows up first in slow query monitoring rather than in testing.
The keyset idea: remember where you stopped
Keyset pagination, also called seek pagination or cursor pagination, replaces "skip N rows" with "continue after the last row I saw." Instead of an offset, the client passes back the ordering values of the last row from the previous page, and the query uses an indexed comparison to seek straight there:
-- Page 1
SELECT id, created_at, customer_id, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Page N+1: pass back the last row's (created_at, id)
SELECT id, created_at, customer_id, total
FROM orders
WHERE (created_at, id) < ('2026-07-01 14:03:22', 918273)
ORDER BY created_at DESC, id DESC
LIMIT 50;With an index on (created_at, id), the engine descends the b-tree to the cursor position and reads 50 entries. Page 4,000 costs the same as page one. Concurrent inserts no longer shift your pages, because the cursor is anchored to values, not positions.
Two design rules make this correct. First, the sort key must be deterministic and total, which in practice means appending a unique column, almost always the primary key, as a tiebreaker. Sorting by created_at alone breaks the moment two rows share a timestamp. Second, the cursor must contain every ORDER BY column, in order. A composite cursor of (created_at, id) belongs to ORDER BY created_at, id and nothing else.
PostgreSQL: row-value comparisons do the right thing
The query above uses a row-value (tuple) comparison: WHERE (created_at, id) < (:ts, :id). PostgreSQL handles this exactly the way you want: the comparison has proper lexicographic semantics, and the planner can use it as a single range condition on a matching multicolumn b-tree index. This is the cleanest keyset implementation of any mainstream engine, and it extends naturally to three-column cursors for grouped orderings.
Mixed sort directions, like ORDER BY created_at DESC, id ASC, break the single tuple comparison trick. The fixes are either to build the index with matching per-column directions so one direction works for both, or to fall back to the expanded OR form described in the MySQL section, which PostgreSQL also executes fine. My advice is to avoid mixed directions in paginated APIs unless a product requirement forces them; they complicate every layer for marginal benefit.
One more PostgreSQL refinement: covering indexes with INCLUDE. If the page query selects only a few columns, adding them as INCLUDE columns lets the whole page come from an index-only scan, subject to visibility-map freshness:
CREATE INDEX idx_orders_keyset
ON orders (created_at DESC, id DESC)
INCLUDE (customer_id, total);
-- EXPLAIN should now show: Index Only Scan using idx_orders_keysetWhether a covering index earns its write cost depends on the table's update rate, which is the standard index advisor trade-off: read savings versus index maintenance and bloat on a table that may be update-heavy.
MySQL: same idea, more careful SQL
MySQL 8.x parses row-value comparisons, but the optimizer historically does not turn a tuple inequality into an efficient index range seek the way PostgreSQL does. The reliable pattern on MySQL is the expanded boolean form, which the optimizer handles well with a composite index:
SELECT id, created_at, customer_id, total
FROM orders
WHERE created_at < '2026-07-01 14:03:22'
OR (created_at = '2026-07-01 14:03:22' AND id < 918273)
ORDER BY created_at DESC, id DESC
LIMIT 50;Always verify with EXPLAIN that you get a range access on the composite index rather than a full scan with filesort; the expanded form is sensitive to how it is written, and wrapping the columns in functions or mismatching collations will defeat it.
InnoDB gives you one structural gift here: every secondary index implicitly contains the primary key, since secondary index entries point to rows by PK. An index on (created_at) is effectively (created_at, id) for ordering purposes when id is the primary key, so your keyset index is often already narrower than you expected. InnoDB also enables the deferred-join trick for cases where you are stuck with OFFSET for compatibility: paginate a subquery that selects only primary keys through a compact index, then join back to fetch the full rows, so the skipped rows were never wide.
Cursors at the API layer
Whichever engine executes the query, do not expose raw column values as your public cursor. The convention that has aged best in my experience: serialize the ordering values into an opaque token, base64 of a small JSON payload or a signed string, and document only "pass the cursor you were given." This keeps clients from constructing offsets manually, lets you change the sort key later, and avoids leaking timestamps and IDs into URLs as an implicit contract.
Accept that keyset pagination cannot jump to an arbitrary page number. Products that genuinely need "go to page 57" need either OFFSET with capped depth, or a precomputed page index refreshed asynchronously. Most products, once asked, discover that users only ever click next, and infinite scroll never needed page numbers at all. Dropping the total-count query at the same time removes the other expensive half of classic pagination.
Migration notes from MySQL to PostgreSQL
If your MySQL codebase already uses the expanded OR form, it will run unchanged on PostgreSQL, and you can simplify it to tuple comparisons opportunistically. Recheck every pagination index after migration: index direction syntax, collation-dependent orderings of text keys, and NULL ordering differ between the engines. NULLs sort last in descending order by default in PostgreSQL and first in ascending, and any nullable column in a sort key needs an explicit NULLS FIRST or NULLS LAST decision plus a matching index. Better yet, keep nullable columns out of pagination keys entirely.
Plan-verify each paginated endpoint on realistic data volume, not an empty staging database; the failure mode of a wrong keyset index is a silent filesort that only appears at scale. The broader PostgreSQL overview covers the planner behaviors worth learning during this kind of migration.
How MonPG helps once you are on PostgreSQL
MonPG monitors PostgreSQL only, and pagination regressions are one of its most common catches: a deploy changes an ORDER BY, the keyset index stops matching, and a query family that averaged two milliseconds starts running sequential scans. MonPG keeps pg_stat_statements history so the regression is visible as a before-and-after shape, ties it to the plan context, and shows whether the covering index you built is actually being used or just accumulating write overhead. If pagination is a hot path in your product, put its query families on a watchlist from day one. The PostgreSQL monitoring guide shows how query history, index usage, and plan evidence fit into one workflow.