Server-Side Pagination on Neon Postgres: OFFSET Costs Money (2026)

Your admin table hits 500,000 rows. Page 1 of LIMIT 20 OFFSET 0 returns in milliseconds. Then a user clicks through to page 1000 and the same twenty rows take seconds — and on Neon Postgres, those seconds are not annoying, they are billable. Neon bills compute in CU-hours, metered by the second, and the Free plan caps you at 100 CU-hours per project per month. A pagination pattern that degrades linearly is a quiet tax on that budget: every deep page makes the compute work harder, stay awake longer, and scale up more often.

This post covers the two pagination patterns, why OFFSET degrades, what COUNT(*) really costs, and how to wire keyset pagination in Drizzle ORM against a Neon serverless driver.

TL;DR

• LIMIT/OFFSET cost grows linearly with the offset: Postgres walks and discards all offset rows. Page 500 on a large table is effectively a near-full index scan, every click.
• Keyset (cursor) pagination filters with WHERE (created_at, id) < (last page's values) + a composite index. Cost per page is roughly constant — cost is proportional to page size, not table depth.
• COUNT(*) on a large table is its own full scan. For "page X of Y" UI, use the planner's estimate from pg_class (reltuples) or drop exact totals and show "Load more".
• Infinite scroll + "Load more" pairs naturally with keyset cursors; numbered pages need random access that OFFSET provides but keyset does not. Pick per endpoint.
• On Neon, shallow pagination a few users do rarely costs almost nothing. Deep pagination — background exports, crawlers, users scrolling far into a feed — is where CU-hours leak. One composite index and one cursor endpoint can eliminate most of that compute.
• OFFSET is still fine for small admin tables (a few thousand rows) where every page is cheap and you genuinely want page numbers.

Why OFFSET degrades linearly

The naive pattern everyone writes on day one:

-- Page 1: fast
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 0;

-- Page 1,000: the same 20 rows, a lot more work
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 19980;

The mental model is "skip 19,980 rows, give me 20". The database's actual job is different: a B-tree index can find a value quickly, but it cannot jump to a position. Nothing in the index records "the 19,981st entry". So Postgres walks the index in sort order, reads every entry up to your offset, counts them off, throws them away, and finally returns your 20 rows.

That means the work is O(offset + page size). Page 1 reads 20 rows. Page 1,000 reads 20,000 rows to return 20. Page 25,000 reads half a million. Performance is not degrading because your database got sick — the query is doing proportionally more scanning with every click.

Run EXPLAIN ANALYZE on a deep OFFSET page and you'll see the shape: an index scan whose rows removed by filter or internal discard count dwarfs the 20 rows you asked for. There is no planner trick that fixes this, because the database genuinely has to know where row 19,981 sits, and hierarchical position is not something an index stores.

Two secondary problems make it worse:

1. Instability. OFFSET is positional. If a row is inserted or deleted between two requests, every subsequent row shifts. Users see duplicates or silently miss rows. On a table with any write traffic while someone is paging, OFFSET is not just slow — it's incorrect.
2. Repeated scanning. A user paging to page 30 issues 30 sequential queries, each re-scanning from the top. A background job walking a 500k-row table in OFFSET chunks is O(n²) in total index-traversal work spread across many queries.

On a fixed-cost instance, this is just latency. On Neon it's compute: longer-running, more CPU-bound queries keep the compute active longer, and heavy scans can push autoscaling to larger CU sizes, which bill at more CU-hours. Neon's Free plan includes 100 CU-hours per project per month — enough for a 0.25 CU compute to run roughly 400 hours — so the quota is generous for small workloads, but a table-crawling job that holds the compute awake for hours eats it directly.

Keyset pagination: seek, don't skip

Keyset pagination replaces "skip N rows" with "start after the last row I showed you". You remember the sort key of the final row from the previous page and filter on it:

-- First page: no cursor yet
SELECT id, title, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: cursor = (created_at, id) of the last row above
SELECT id, title, created_at
FROM events
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row-value comparison (a, b) < (x, y) is valid Postgres and is exactly equivalent to the expanded form (a < x) OR (a = x AND b < y) — but it's one condition, so the planner maps it cleanly onto the index. This composite index makes the WHERE clause a direct seek:

CREATE INDEX events_created_id_idx
ON events (created_at DESC, id DESC);

The (created_at, id) pair is the standard combo because created_at alone is not unique — timestamps collide constantly under batch inserts, and a non-unique sort key makes pagination unstable. Adding the primary key as a tiebreaker makes the sort total order, so every page boundary is unambiguous. If your table sorts by something else (say, status, priority), apply the same rule: sort columns + unique tiebreaker.

With this index, Postgres seeks straight to the cursor position and reads only the rows it will return. The cost of any page — page 1 or page 24,000 — is roughly proportional to the page size, not the offset depth. That's the entire trick.

Two operational notes:

• Direction. "Previous page" needs the mirrored predicate: WHERE (created_at, id) > (cursor) ORDER BY created_at ASC, id ASC, then reverse the result in application code if you want display order.
• No random access. A user cannot type "jump to page 37". If you need that UX, keyset alone doesn't give it to you — see the UX section below.

What COUNT(*) actually costs

Almost every OFFSET-based UI ships with a sidebar query: SELECT COUNT(*) FROM events so the UI can render "Page 3 of 4,120". On a large table this is its own performance problem, and it's the part people forget when they switch the page query to keyset but keep the counters.

COUNT(*) is not cached anywhere in Postgres. Every call scans the table or an index and counts rows. MVCC makes it worse in principle: Postgres must check each row's visibility, so a plain index-only count can be thwarted if the visibility map isn't up to date. On a million-row table this is a real multi-second query in its own right — and pairs badly with Neon's metered compute, because every render of every list page now re-runs the scan.

Your options, cheapest first:

-- 1. Table-wide estimate from planner statistics (nearly free)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'events'::regclass;

reltuples is maintained by VACUUM/ANALYZE (and autovacuum runs this for you on active tables). PostgreSQL's own documentation on row estimation notes these statistics drive the planner's row-count guesses; treat them as estimates within a factor of a few for simple predicates. For a UI hint like "~4,100 events" this is usually fine — just never format it as an exact number.

-- 2. Estimate for a filtered subset: EXPLAIN's plan row count, not executed
--    (quality varies by predicate selectivity)

For filtered counts, some apps run EXPLAIN (not EXPLAIN ANALYZE — explain alone doesn't execute) and parse the estimated row count out of the plan. This works but is fragile across plan shapes; treat it as [unverified] territory — test it against your own views/CTEs before trusting it, since subqueries and volatile predicates distort planner estimates.

-- 3. HyperLogLog-style approximations via extensions — needs CREATE EXTENSION
--    and app-side aggregation; usually overkill for a pagination UI

The pragmatic answer, and what most modern APIs do: drop the exact total entirely. Fetch pageSize + 1 rows; if you get back more than pageSize, there is a next page. That "has more" boolean costs one extra indexed row read. Combine it with a cheap estimate from pg_class if you want to display something like "1,203 items (approx.)" in the header.

Infinite scroll vs numbered pages

This decision is a UX decision as much as a performance one:

	OFFSET + COUNT(*)	Keyset + cursor
Page numbers ("37 of 412")	trivial	needs an estimate
"Load more" / infinite scroll	works	works, and stays fast
Correctness under concurrent writes	rows duplicate/skip	stable positions
Arbitrary jump-to-page	yes	no
Client-visible state	?page=37	opaque cursor token
DB cost model	grows with depth	~flat per page

Infinite scroll and "Load more" buttons are naturally sequential — the user got the last row of the previous batch and wants the next ones. That's exactly the shape of keyset pagination, which is why feeds, activity logs, exports, and admin dashboards built this way should be cursors end to end. Numbered pages with jump-to-page are the one genuine OFFSET use case — keep it for small, bounded, read-mostly tables where linear cost doesn't matter, or accept that deep numbered navigation costs what it costs.

A hybrid pattern exists if you must support page numbers on a large table: emit a first-page cursor for each page, so page links are cursors keyed by (offset-derived) boundary keys — but this reintroduces offset-style instability, and most teams just accept "next/prev" for big feeds.

The Drizzle version

Drizzle's official cursor-pagination guide shows the offset-free pattern directly. With Neon's serverless driver, it looks like:

import { and, asc, desc, eq, gt, lt, or } from "drizzle-orm";
import { index, pgTable, bigserial, text, timestamp } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/neon-http";

export const events = pgTable(
  "events",
  {
    id: bigserial("id", { mode: "number" }).primaryKey(),
    title: text("title").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index("events_created_id_idx").on(t.createdAt.desc(), t.id.desc())],
);

type Cursor = { createdAt: Date; id: number };

export async function nextEventPage(cursor?: Cursor, pageSize = 20) {
  return db
    .select({ id: events.id, title: events.title, createdAt: events.createdAt })
    .from(events)
    .where(
      cursor
        ? or(
            gt(events.createdAt, cursor.createdAt),
            and(eq(events.createdAt, cursor.createdAt), gt(events.id, cursor.id)),
          )
        : undefined,
    )
    .orderBy(desc(events.createdAt), desc(events.id))
    .limit(pageSize);
}

Notes on the shape:

• The Drizzle guide's or(gt(colA, x), and(eq(colA, x), gt(colB, y))) is the expanded form of the row-value comparison — same predicate, same plan, just written out so Drizzle operators can express it.
• hasMore falls out of the query: request pageSize + 1, and if the result length exceeds pageSize, drop the trailing row and set hasMore = true. The cursor to emit is the (createdAt, id) of the last row you keep — base64-encode it if you don't want clients poking at raw timestamps, though the Drizzle guide keeps it as plain values.
• Cold starts add to this: Neon suspends a compute after 5 minutes of inactivity (fixed on the Free plan) and resumes it on the next query "typically within a few hundred milliseconds" per Neon's scale-to-zero documentation. A cheap seek query doesn't change that; a 3-second deep-OFFSET query makes the wake-up more noticeable per request. Keep list queries small and the compute mostly either asleep or doing proportionally little work.
• On Neon, use the pooled connection string for serverless functions so short-lived connections don't exhaust the per-CU connection limit — the driver setup itself follows Neon's drizzle-orm/neon-http (or neon-serverless for WebSocket-based) drivers as documented on drizzle-team docs [unverified detail: exact sub-driver choice depends on your Neon endpoint type; check the neon and drizzle-orm connection docs for your connection string].

Wrap-up

• OFFSET's cost is O(offset); keyset's cost is O(page size). On a 500k-row table that difference is milliseconds vs seconds per deep page — and on Neon, seconds of compute are CU-hours you pay for (or burn from your Free plan's 100-hour budget).
• Kill the page-total query unless someone truly needs it: pg_class.reltuples gives a nearly-free estimate, and pageSize + 1 gives a free "has more" flag.
• One composite index (created_at DESC, id DESC), one row-value WHERE, one opaque cursor in the API response — that's the whole migration. Keep OFFSET for the small admin tables that genuinely want page numbers.

Sources

• PostgreSQL docs — LIMIT/OFFSET (Pagination)
• PostgreSQL docs — Indexes and ORDER BY
• PostgreSQL docs — Row and Array Comparisons (row-value comparisons)
• PostgreSQL docs — pg_class (reltuples)
• PostgreSQL docs — Planner Statistics and Row Estimation
• Drizzle ORM — SQL Cursor-based pagination guide
• Drizzle ORM — SQL Limit/Offset pagination guide
• Neon — Scale to zero (cold starts, idle suspension)
• Neon — Plans (Free plan limits, CU-hour pricing)
• Neon — Drizzle integration guide