ORDER BY, SKIP/OFFSET, LIMIT, Deterministic Pagination, and Why Deep Pagination Can Be Expensive

Learning outcomes

AtlasMart’s order endpoint must return pages predictably while new orders continue to arrive. LIMIT alone does not define which rows are “first,” and offset pagination can force the database to process/sort rows that are ultimately skipped. Stable ordering is therefore a correctness requirement before it is a tuning question.

1. ORDER BY defines the sequence

CYPHER 25CREATE CONSTRAINT customer_id IF NOT EXISTS FOR (c:Customer) REQUIRE c.customerId IS UNIQUE;CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.productId IS UNIQUE;CREATE CONSTRAINT order_id IF NOT EXISTS FOR (o:Order) REQUIRE o.orderId IS UNIQUE;CREATE CONSTRAINT category_id IF NOT EXISTS FOR (c:Category) REQUIRE c.categoryId IS UNIQUE;MERGE (c1:Customer {customerId:'C-1001'}) SET c1.name='Ava Chen', c1.tier='gold', c1.region='eu'MERGE (c2:Customer {customerId:'C-1002'}) SET c2.name='Noah Smith', c2.tier='silver', c2.region='us'MERGE (c3:Customer {customerId:'C-1003'}) SET c3.name='Mina Rahimi', c3.tier='gold', c3.region='me'MERGE (c4:Customer {customerId:'C-1004'}) SET c4.name='Leo Martin', c4.region='eu'MERGE (cat1:Category {categoryId:'CAT-CAMERAS'}) SET cat1.name='Cameras'MERGE (cat2:Category {categoryId:'CAT-AUDIO'}) SET cat2.name='Audio'MERGE (p1:Product {productId:'P-1001'}) SET p1.name='Trail Camera', p1.price=129.90, p1.rating=4.7MERGE (p2:Product {productId:'P-1002'}) SET p2.name='Studio Headphones', p2.price=89.00, p2.rating=4.7MERGE (p3:Product {productId:'P-1003'}) SET p3.name='Action Camera', p3.price=219.00, p3.rating=4.5MERGE (p4:Product {productId:'P-1004'}) SET p4.name='USB Microphone', p4.price=75.00MERGE (p1)-[:IN_CATEGORY]->(cat1)MERGE (p3)-[:IN_CATEGORY]->(cat1)MERGE (p2)-[:IN_CATEGORY]->(cat2)MERGE (p4)-[:IN_CATEGORY]->(cat2)MERGE (o1:Order {orderId:'O-2001'}) SET o1.placedAt=datetime('2026-08-01T09:00:00Z'), o1.status='paid', o1.total=218.90MERGE (o2:Order {orderId:'O-2002'}) SET o2.placedAt=datetime('2026-08-02T10:30:00Z'), o2.status='paid', o2.total=219.00MERGE (o3:Order {orderId:'O-2003'}) SET o3.placedAt=datetime('2026-08-03T12:00:00Z'), o3.status='shipped', o3.total=129.90MERGE (o4:Order {orderId:'O-2004'}) SET o4.placedAt=datetime('2026-08-04T14:15:00Z'), o4.status='paid', o4.total=164.00MERGE (o5:Order {orderId:'O-2005'}) SET o5.placedAt=datetime('2026-08-04T14:15:00Z'), o5.status='paid', o5.total=75.00MERGE (c1)-[:PLACED]->(o1)MERGE (c2)-[:PLACED]->(o2)MERGE (c1)-[:PLACED]->(o3)MERGE (c3)-[:PLACED]->(o4)MERGE (c4)-[:PLACED]->(o5)MERGE (o1)-[:CONTAINS {quantity:1}]->(p1)MERGE (o1)-[:CONTAINS {quantity:1}]->(p2)MERGE (o2)-[:CONTAINS {quantity:1}]->(p3)MERGE (o3)-[:CONTAINS {quantity:1}]->(p1)MERGE (o4)-[:CONTAINS {quantity:1}]->(p2)MERGE (o4)-[:CONTAINS {quantity:1}]->(p4)MERGE (o5)-[:CONTAINS {quantity:1}]->(p4);

CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAtLIMIT 3;CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAtORDER BY o.placedAt DESC, o.orderId DESCLIMIT 3;

The first query may appear stable in a tiny lab, but Neo4j does not guarantee row order without ORDER BY. The second creates an explicit total ordering by adding orderId as a tie-breaker for the two orders with identical placedAt.

2. SKIP and OFFSET trim an ordered stream

CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAt, o.totalORDER BY o.placedAt DESC, o.orderId DESCSKIP $offsetLIMIT $pageSize;

Current Cypher treats OFFSET as a synonym for SKIP. The course uses SKIP in compatibility-oriented examples and explicitly states the current synonym rather than relying on readers to infer it. Both still require ORDER BY for deterministic pagination.

3. Keyset pagination resumes after the last returned sort key

CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAt, o.totalORDER BY o.placedAt DESC, o.orderId DESCLIMIT $pageSize;CYPHER 25MATCH (o:Order)WHERE o.placedAt < $lastPlacedAt   OR (o.placedAt = $lastPlacedAt AND o.orderId < $lastOrderId)RETURN o.orderId, o.placedAt, o.totalORDER BY o.placedAt DESC, o.orderId DESCLIMIT $pageSize;

The cursor contains the exact keys that define the order. If the API sorts by a non-unique key but stores only that key in the cursor, equal-key rows can be skipped or repeated. If the sort key can change after publication, cursor semantics also need a policy.

4. Wrong approach: infer speed from a five-row fixture

On five Orders, offset and keyset queries both look instant. That proves nothing about a million-row production endpoint. Deep offset may still require finding/order-processing rows before discarding the first N. Index-backed ordering can change the plan, but availability depends on schema, predicate, index, runtime and statistics. Measure with a representative dataset and PROFILE in a safe performance environment; use EXPLAIN here to inspect shape without executing.

CYPHER 25 EXPLAINMATCH (o:Order)RETURN o.orderId, o.placedAtORDER BY o.placedAt DESC, o.orderId DESCSKIP $offset LIMIT $pageSize;CYPHER 25 EXPLAINMATCH (o:Order)WHERE o.placedAt < $lastPlacedAt   OR (o.placedAt = $lastPlacedAt AND o.orderId < $lastOrderId)RETURN o.orderId, o.placedAtORDER BY o.placedAt DESC, o.orderId DESCLIMIT $pageSize;

Lab: prove page boundaries with tied timestamps

CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAtORDER BY o.placedAt DESC, o.orderId DESCSKIP 0 LIMIT 2;CYPHER 25MATCH (o:Order)RETURN o.orderId, o.placedAtORDER BY o.placedAt DESC, o.orderId DESCSKIP 2 LIMIT 2;

Because O-2004 and O-2005 share a timestamp, omitting orderId from ORDER BY leaves their relative order unspecified. The tie-breaker makes page membership deterministic. Record page 1’s final pair and use it as the keyset cursor; verify no overlap with page 2 and no missing fixture IDs.

Production judgment

Pagination is a consistency and cost contract. Decide whether clients need snapshot-like stability, how inserts/updates should affect later pages, and whether cursors may expire. Stable total order is non-negotiable; deep offset is acceptable only when measured workload and UX justify it. Next, Chapter 03 closes with a scripted read-query suite that treats expected rows as test fixtures.

Summary and next step

ORDER BY, SKIP/OFFSET, LIMIT, Deterministic Pagination, and Why Deep Pagination Can Be Expensive is useful only when its assumptions and observed evidence stay attached to the decision. The examples above establish a reproducible mechanism and boundary; they do not turn one lab result into a universal production rule.

Next, continue to Build Parameterized Read Queries in Cypher Shell and Verify Results Against Known Graph Fixtures. Carry forward the verified assumptions, fixture state, version/edition boundaries, and measurements from this lesson instead of treating the next topic as an isolated recipe.

Authoritative references

• Current Neo4j versions — Official current-release and 5.26 LTS patch snapshot.
• Cypher Manual introduction — Current Cypher 25 baseline and Cypher 5 compatibility framing.
• MATCH — Pattern matching and variable-binding semantics.
• Variables — Variable naming and scope across query parts.
• WHERE — Pattern constraints and post-WITH filtering semantics.
• Predicates — Boolean/comparison/string/list/type predicates and three-valued results.
• Working with null — Null propagation and missing-property semantics.
• RETURN — Projection, aliases, expressions and DISTINCT semantics.
• List expressions and pattern comprehension — Current fixed/variable pattern-comprehension syntax and behavior.
• ORDER BY — Only ORDER BY guarantees result ordering; sort semantics and index-backed ordering.
• SKIP / OFFSET — Offset semantics and OFFSET synonym.
• LIMIT — Row limiting semantics and the absence of ordering guarantees without ORDER BY.
• Cypher Shell — Bolt CLI, parameter support, script/file execution and output modes.
• Query tuning and plans — EXPLAIN/PROFILE and execution-plan evidence.