Handling Cursor Drift When Geometry Changes

← Back to Spatial Pagination & Cursor Strategies

This page covers what happens to a paginated spatial result when the underlying features move, and how to make the failure visible instead of silent.

# Context & When to Use

Pagination assumes a stable ordering. That assumption is safe for a table of invoices and unsafe for a table of vehicles: if the API sorts by distance from a point, every position update reorders the result set under the client’s feet. A vehicle that approaches crosses the cursor backwards and is returned twice; one that recedes is pushed past the cursor and is never returned at all.

The damage is proportional to how fast the data moves and how slowly the client pages. A fleet updating every three seconds, paged at 200 features a request over a 4 000-feature result, will produce a list with several percent duplicates and a similar number of silent omissions. No error is raised at any point, and the client’s totals will simply be wrong.

Two fixes stack. Sorting on an immutable key removes reordering entirely, which is the bulk of the problem and costs nothing — the keyset technique in Implementing Cursor-Based Pagination for Spatial Queries. Reporting drift handles what remains: features entering or leaving the filter mid-walk, which no sort key can prevent.

# Runnable Implementation

import base64
import json
from datetime import datetime, timezone
from typing import Annotated, Any

import asyncpg
from fastapi import APIRouter, Depends, HTTPException, Query

router = APIRouter(prefix="/v1/features", tags=["features"])

PAGE_SQL = """
SELECT f.id,
       ST_AsGeoJSON(f.geom, 6)::json AS geometry,
       f.updated_at,
       -- How many rows in this window changed since the walk began?
       count(*) FILTER (WHERE f.updated_at > $6) OVER () AS changed_since_start
FROM   features f
WHERE  f.geom && ST_MakeEnvelope($1, $2, $3, $4, 4326)
  AND  ($5::bigint IS NULL OR f.id > $5)     -- keyset on an IMMUTABLE key
ORDER  BY f.id                                -- never ORDER BY distance
LIMIT  $7
"""


def encode_cursor(last_id: int, started_at: datetime) -> str:
    payload = {"id": last_id, "t": started_at.isoformat()}
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()


def decode_cursor(token: str) -> tuple[int, datetime]:
    try:
        payload = json.loads(base64.urlsafe_b64decode(token.encode()))
        return int(payload["id"]), datetime.fromisoformat(payload["t"])
    except Exception:
        raise HTTPException(422, detail={"error": "malformed_cursor"})


@router.get("")
async def list_features(
    bbox: Annotated[str, Query()],
    cursor: Annotated[str | None, Query()] = None,
    limit: Annotated[int, Query(ge=1, le=1000)] = 200,
    pool: asyncpg.Pool = Depends(get_pool),
) -> dict[str, Any]:
    minx, miny, maxx, maxy = (float(v) for v in bbox.split(","))
    last_id, started_at = decode_cursor(cursor) if cursor else (None, datetime.now(timezone.utc))

    async with pool.acquire() as conn:
        rows = await conn.fetch(PAGE_SQL, minx, miny, maxx, maxy,
                                last_id, started_at, limit)

    drifted = rows[0]["changed_since_start"] if rows else 0
    next_cursor = encode_cursor(rows[-1]["id"], started_at) if len(rows) == limit else None

    return {
        "features": [
            {"type": "Feature", "id": r["id"], "geometry": r["geometry"]} for r in rows
        ],
        "next_cursor": next_cursor,
        # Honesty: the client is told the ground moved, and by how much
        "drift": {
            "since": started_at.isoformat(),
            "rows_changed_in_window": drifted,
            "advice": "restart pagination" if drifted > limit // 4 else "continue",
        },
    }

The window function counting changed_since_start costs one extra pass over the page’s rows, not over the table, so the drift signal is effectively free.

# Key Parameters & Options

# What each strategy actually guarantees

# How much drift to expect

Whether drift matters at all is arithmetic: it is roughly the update rate multiplied by the time the client spends walking. Working that out for your own data usually settles the argument about whether any of this is worth building.

# Gotchas & Failure Modes

• A cursor that encodes the distance. Any cursor containing a computed value inherits the instability of that value. Encode identifiers only.
• ORDER BY geom <-> point with a LIMIT and a cursor. The KNN operator is the reason people reach for distance ordering; it is also precisely what makes the ordering unstable. Use it for “nearest ten”, never for a paginated walk — see Optimizing KNN Queries with the PostGIS Distance Operator.
• Drift reported but never acted on. A field nobody reads is decoration. Document the threshold and, for first-party clients, restart automatically above it.
• Timestamps from the client. The walk start time must come from the server; a client clock skewed by minutes makes the drift count nonsense.
• updated_at not maintained. The drift count depends on the column being touched by every write. Enforce with a trigger rather than by convention.
• Cursors that outlive their usefulness. A cursor resumed a day later walks a result set that no longer resembles the original. Embed the start time and reject cursors older than a documented window.

# Telling the client what to do about it

A drift field is only useful if the contract says what the numbers mean. Three behaviours cover almost every consumer, and stating them in the API documentation saves every integrator from inventing their own.

A client building a live map should ignore drift entirely and simply keep paging: it is going to refresh anyway, and a duplicate feature is harmless when the result is keyed by id. A client computing a total — how many assets are in this region — must restart when drift exceeds its tolerance, because a count assembled from a shifting set is not a count of anything. A client performing a one-off export should not be paginating at all; give it the streamed export path instead, where a single query sees a single snapshot.

Encode that guidance in the response rather than only in prose. The advice field in the implementation above is deliberately a string the client can branch on, and adding a machine-readable drift.ratio alongside it lets a consumer set its own threshold without parsing English. What matters is that the server has the information and passes it on; a silently inconsistent list is the only genuinely unacceptable outcome.

# Verification Snippet

import pytest


@pytest.mark.asyncio
async def test_moving_features_do_not_duplicate(client, db_conn):
    page1 = (await client.get("/v1/features",
                              params={"bbox": "-1,50,1,52", "limit": 50})).json()
    # Move a feature from page 1 much closer to the query point
    await db_conn.execute(
        "UPDATE features SET geom = ST_SetSRID(ST_MakePoint(0.0, 51.0), 4326),"
        " updated_at = now() WHERE id = $1", page1["features"][10]["id"])

    page2 = (await client.get("/v1/features",
                              params={"bbox": "-1,50,1,52", "limit": 50,
                                      "cursor": page1["next_cursor"]})).json()

    ids1 = {f["id"] for f in page1["features"]}
    ids2 = {f["id"] for f in page2["features"]}
    assert not (ids1 & ids2), "a feature was returned on both pages"
    assert page2["drift"]["rows_changed_in_window"] >= 1

# Walk every page and assert the id set is exactly the table's
python scripts/walk_pages.py --bbox -1,50,1,52 --limit 200 --assert-complete
# 4212 features over 22 pages · 0 duplicates · 0 missing · drift reported on 3 pages

# Related

• Spatial Pagination & Cursor Strategies — the cursor design this hardens
• Implementing Cursor-Based Pagination for Spatial Queries — the keyset mechanics
• K-Nearest Neighbor Routing Algorithms — where distance ordering does belong

← Back to Spatial Pagination & Cursor Strategies