LEARN. PRACTICE. GROW.Retain the same view
One session begins REPEATABLE READ READ ONLY and reads page one. The second session deletes id 1, changes id 4 units and inserts id 7 before the boundary. The first session reads remaining pages within the same transaction and still observes original ids and values. After COMMIT, a new query sees the changes. The script confirms these observations without exporting snapshots between sessions. The tested requirement is stability of that local view during reading, not a business-consistency guarantee across systems. Define when the view starts, when it ends and what happens if export stops before delivery. A long read can retain old versions that remain visible; define duration limits and resource release. This experiment measures neither bloat nor vacuum cost.
Avoid the illusion across requests
Another group ends the transaction after page one. The second session inserts the earlier row and the next page starts a new transaction, also REPEATABLE READ. Its response becomes [2,3] again. Using the same isolation name in separate requests does not preserve the first request view. A web application opening and closing one transaction per page needs to state this boundary. It may choose a live experience, generate an export artifact or adopt another snapshot mechanism with a defined lifecycle. The laboratory implements none of those HTTP alternatives and does not demonstrate their recovery after failure.
Measure work in a controlled plan
The script creates 20000 rows for alpha and another 20000 for beta, with an index on (tenant,rank_key,id). After VACUUM ANALYZE, it disables sequential scan only in the experiment session to control the comparison. EXPLAIN ANALYZE executes two queries returning the same ten ids. The child Index Only Scan produces 15010 rows for OFFSET 15000 and ten for the boundary (15000,15000); each scan has one loop. The LIMIT node returns ten in both. The report retains counts and identifies the planner intervention. It measures no SLA, does not recommend disabling sequential scan in production and does not turn the row-count ratio into a latency ratio.
Relate the index to the access pattern
In the plan dataset, the tenant filter fixes the first index component and the query traverses rank_key and id in matching order. That design makes the comparison readable but does not establish that any index containing those fields serves every query. Changed filters, ordering direction, distribution or returned columns require another plan. A review should retain SQL, relevant parameters, statistics and version alongside observed results. In production, compare work and time under representative conditions and include index maintenance cost on writes. The laboratory measures a deliberately controlled read access; it does not exercise index maintenance under concurrent load.
Preserve scope on every page
The tenant=alpha query returns ids 15001 and 15002 from that tenant. Removing the filter allows the same boundary to retrieve alpha 15001 and beta 15001. Additional tenant ordering makes this group deterministic; no authentication exists. The test demonstrates query-scope leakage, not a security audit of an API. A cursor replaces neither the mandatory filter nor current user authorization. When designing the contract, include filters, ordering version and binding to authorized scope. If cursor signing, validity or tamper protection is provided, those controls require their own tests; this script does not implement them.
Close the export decision
In a fictional project, business requires a reconcilable report while support needs an always-current list. Record both requirements and avoid imposing one contract on both. For export, define population, view, order, resume behavior, artifact retention and total reconciliation. For the live list, explain possible changes across pages and how the interface handles them. Provide insertion, deletion and update examples that reproduce each decision. The fourteen local groups help discuss mechanisms and limitations. Acceptance with the real application, representative volume, authorization and human review remains necessary before declaring the operational commitment fulfilled.
python3 content/labs/design-pagination/run.py --postgres-prefix /path/to/postgresql-18.6 --output /tmp/dr-pages-new.json
# Choose a new output path. The script creates and stops its own private cluster.In the controlled plan, OFFSET consumes 15010 index-scan rows to return ten; keyset consumes ten and returns the same ids.
Common pitfalls
Treating a new transaction as the previous snapshot, counts as milliseconds, disabling scans as universal optimization or a cursor as authorization.
Related topics: Execution plans · Snapshots and isolation
The contract needs a view, an order, a scope and operational limits. A plan helps measure work but does not replace target acceptance.
Reference: Using EXPLAIN · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30