ORM Pagination and Batch Writes #

Two ORM conveniences dominate the plans of data-heavy applications: paginating a list, and writing many rows at once. Both are one line of application code, and both have a plan whose cost grows in ways the calling code does not suggest — page 500 costing 200 times page 1, or a “bulk” insert that turns out to be 10,000 separate statements.

This page covers what the common helpers generate, how those statements are planned, and the shapes that keep cost proportional to the work actually requested. It sits alongside the loading-strategy material in ORM translation pitfalls, and uses the plan concepts from top-N heapsort and LIMIT plans and INSERT ON CONFLICT and MERGE plans.

When the Optimizer Sees These Shapes #

Pagination. Every ORM paginator produces one of two statement shapes:

Paginators also usually issue a count query for the total page count. SELECT count(*) with the same filters reads every matching row — often far more expensive than the page itself.

Batch writes. The helpers differ sharply:

The planner treats all of these as ordinary statements; the differences are in how many statements there are and how much each one reads.

Four shapes and how they scale A table. Offset pagination costs grow with page depth. Keyset pagination costs stay constant per page. A multi-row insert is one statement per batch. Per-row inserts are one statement per row, dominated by round trips. statements cost growth OFFSET pagination 1 per page grows with depth keyset pagination 1 per page constant multi-row INSERT 1 per batch linear in rows per-row INSERT 1 per row round trips dominate the ORM call looks the same; the plan does not

Annotated EXPLAIN Node Breakdown #

Offset pagination, page 500 of 20:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at FROM articles
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 9980;
Limit  (actual time=41.2..41.3 rows=20 loops=1)
  ->  Index Scan Backward using articles_status_created_idx on articles
        (actual time=0.04..40.8 rows=10000 loops=1)
        Index Cond: (status = 'published'::text)
        Buffers: shared hit=9204
Execution Time: 41.4 ms
-- 10,000 rows produced to return 20; buffers grow with page number

Keyset pagination for the same position:

Limit  (actual time=0.03..0.04 rows=20 loops=1)
  ->  Index Scan Backward using articles_status_created_idx on articles
        (actual rows=20 loops=1)
        Index Cond: ((status = 'published'::text) AND (ROW(created_at, id) < ROW('2026-08-02 …'::timestamptz, 41204)))
        Buffers: shared hit=6
Execution Time: 0.05 ms

A bulk insert of 10,000 rows, per-row:

-- 10,000 statements
INSERT INTO events (account_id, kind, payload) VALUES ($1, $2, $3)
-- pg_stat_statements: calls 10,000  mean 0.08 ms  total 800 ms
-- wall clock at 0.25 ms network latency: ≈ 3.3 s

The same rows in batches of 1,000:

INSERT INTO events (account_id, kind, payload) VALUES ($1,$2,$3), ($4,$5,$6), … ($2998,$2999,$3000)
-- pg_stat_statements: calls 10  mean 38 ms  total 380 ms
-- wall clock: ≈ 0.4 s

Breakdown:

Cost by page number With offset pagination, page 1 reads 6 buffers, page 100 reads 1,840, page 500 reads 9,204 and page 5,000 reads 92,010. With keyset pagination every page reads 6 buffers. offset, page 1 6 buffers offset, page 100 1,840 buffers offset, page 500 9,204 buffers keyset, any page 6 buffers offset cost is proportional to the rows skipped

Internals and Trade-offs #

Why offset cannot be optimised away. The server has no way to jump to the m-th row of a result without producing the preceding rows: ordering is a property of the result, not an index position it can seek to. Even with a perfect index, the scan must walk past the skipped entries. The only structural fix is to express the position as a value, which is what keyset pagination does.

Stability. Offset pagination over changing data silently repeats or skips rows: an insert before the current page shifts everything. Keyset pagination is stable with respect to inserts, because the position is a value rather than a count.

The count query. count(*) with the page’s filters is frequently more expensive than the page. Options: omit the total (infinite scroll), show an approximate count from pg_class.reltuples scaled by selectivity, cache the count per filter combination, or compute it only on the first page. For filters that match a large share of a big table, an exact count is inherently a full scan of the matching rows.

Batch size for writes. Larger batches amortise round trips but hold locks longer, produce larger WAL records, and increase the memory a single statement needs. Batches of 500 to 5,000 rows suit most workloads; the right number is where added latency per batch stops falling.

COPY versus multi-row INSERT. COPY avoids per-row SQL parsing and is typically several times faster for large loads, at the cost of not supporting ON CONFLICT. A common pattern is COPY into a staging table followed by one INSERT … SELECT … ON CONFLICT or MERGE, which keeps both speed and conflict handling — the shapes compared in ORM bulk insert and upsert plans.

The fast bulk-load shape Four stages. Rows are streamed with COPY into an unlogged staging table. The staging table is analyzed so the planner knows its size. One INSERT SELECT with ON CONFLICT, or a MERGE, applies the rows to the target. The staging table is truncated for the next batch. COPY to staging no per-row parsing ANALYZE staging real row estimates INSERT … ON CONFLICT one set-based statement TRUNCATE staging ready for the next batch keeps COPY's speed and ON CONFLICT's semantics

The count query deserves its own decision #

Almost every paginator asks for a total, and that request is usually the most expensive part of a page load. SELECT count(*) FROM articles WHERE status = 'published' must visit every matching row — through an index-only scan if one covers the filter, otherwise through the heap — so its cost scales with the size of the filtered set rather than with the page size. On a table where the filter matches ten million rows, the count reads ten million index entries to produce a number the user glances at.

Four alternatives, in increasing order of effort:

The decision belongs in the API design, not in the query layer, because it changes what the endpoint promises. Once an interface commits to exact totals on every page, the database has no way to make that cheap.

Transactions, locks and batch size #

Batch writes have a second dimension that plans do not show: how long each statement holds its locks. A single UPDATE touching two million rows keeps row locks for its entire duration, blocks conflicting writers, and produces one large transaction for replicas and logical decoding to apply. Splitting the same work into two thousand batches of a thousand rows, each in its own transaction, produces the same total work with locks held for milliseconds at a time.

The cost of splitting is overhead per batch — transaction start and commit, statement planning, and the risk of leaving the job half-applied if it fails midway. The first two are small; the third is a design requirement: batch jobs need to be idempotent and restartable, usually by recording progress in a table and using predicates that skip already-processed rows, as described in TID range scans for batched processing.

A practical target is a batch that completes in a few hundred milliseconds. That is short enough that lock waits stay invisible to interactive traffic, long enough that per-batch overhead is negligible, and small enough that a failure re-runs cheaply. Measuring one batch at a few sizes gives the row count that lands in that window, and the number is usually stable as long as the row width and index count stay the same.

Step-by-Step Tuning Workflow #

  1. Classify each list endpoint’s pagination. Offset or keyset? Does it issue a count query?

  2. Add the index that serves the ordering and filters — equality columns, then sort columns with directions, ending in a unique tiebreaker.

  3. Convert deep pagination to keyset. Most ORMs support it: Django with filters on a tuple comparison, ActiveRecord with a where clause, Prisma with cursor, SQLAlchemy with a row comparison.

  4. Decide about the count deliberately: omit, approximate, cache or compute once.

  5. For writes, count the statements. Log SQL for one batch job and check whether the ORM issued one statement or thousands.

  6. Choose the write shape: multi-row INSERT for moderate volumes, COPY into staging plus a set-based apply for large loads.

  7. Size the batch by measuring: increase until per-row time stops improving, then stop — larger batches only add lock duration and WAL bursts.

  8. Re-measure end to end, including application time, since round trips often dominate.

What to measure, and where #

Both halves of this topic have the property that the database’s own numbers understate the problem. For pagination, server-side execution time for page 500 might be 320 ms while the user waits longer, because the endpoint also ran a count query and serialised a page of wide rows. For batch writes, the server may report 36 seconds of execution across 400,000 statements inside an eleven-minute job, because the other ten minutes were round trips.

A useful instrumentation set covers three layers. From the database, pg_stat_statements gives calls, total time and rows per statement, which identifies both deep-offset scans (high rows per call relative to the page size) and per-row write loops (call counts matching row counts). From the driver or ORM, a per-request statement count and the elapsed time inside the database tell you how much of the request is waiting on queries at all. From the application, end-to-end latency per endpoint completes the picture.

When those three disagree — fast statements, many of them, slow endpoint — the fix is almost always structural rather than a matter of tuning any individual plan: fewer statements through batching, or a different pagination contract. When they agree that one statement is slow, the ordinary plan-reading workflow applies, starting from the node that dominates its execution time.

Common Pitfalls #

Deep offset pagination in APIs. Clients iterate to page thousands. Diagnostic signal: buffers growing linearly with page number. Fix: cursor pagination in the API contract.

Count queries on every page. Often costlier than the page. Diagnostic signal: two statements per request, the count slower. Fix: approximate, cache, or drop the total.

Non-unique ordering. Rows repeat or vanish across pages. Diagnostic signal: duplicated items in client-side lists. Fix: add a unique tiebreaker to the order and the keyset condition.

ORM “bulk” helpers that loop. Callbacks or per-row returning values force one statement per row. Diagnostic signal: calls equal to the row count in pg_stat_statements. Fix: use the ORM’s true bulk API, or raw multi-row statements.

Huge single batches. One statement holding locks for minutes. Diagnostic signal: replication lag spikes and blocked writers. Fix: moderate batch sizes with commits between them.

Unanalyzed staging tables. The apply step plans for a tiny table. Diagnostic signal: nested loops over millions of staged rows. Fix: ANALYZE after loading, as in stale statistics after bulk loads.

Frequently Asked Questions #

Why does page 500 take so much longer than page 1? #

Offset pagination produces and discards every row before the requested page, so the work grows with page depth. Keyset pagination expresses the position as a value the index can seek to, making every page cost the same.

Is the count query for pagination worth it? #

Often not. Counting rows matching the filters can cost more than fetching the page, and most interfaces do not need an exact total. Approximate counts, cached counts or infinite scrolling avoid it.

How large should a bulk insert batch be? #

Large enough that round trips stop dominating — usually 500 to 5,000 rows — and small enough that one statement does not hold locks or generate WAL bursts for a long time. Measure and stop when per-row time plateaus.

Is COPY always faster than multi-row INSERT? #

For large loads, yes, because it avoids per-row SQL parsing. It does not support ON CONFLICT, so the usual pattern is COPY into a staging table followed by one set-based statement that applies the rows with conflict handling.

Up: ORM Translation Pitfalls