INTRODUCTION

In Lessons 2.1–2.4, you designed the resource model, defined the OpenAPI contract, enforced idempotency, and implemented versioning. Now, a TPP makes a simple request: GET /accounts/acc-123/transactions. Under the hood, that account has 10,000 transactions spanning 3 years.

If your API returns all 10,000 rows in a single JSON payload, you have just committed two cardinal sins: massive latency (serialising 10,000 objects takes ~500ms) and memory exhaustion (the response size exceeds 50 MB). Worse, the ASPSP’s database executes a full table scan, locking the table and blocking other TPPs—violating PSD2’s non‑discrimination principle (Article 67(3)(b)) because the bank’s own mobile app uses pagination.

This lesson teaches you the mathematics and engineering of efficient pagination. You will learn why cursor‑based pagination (using transaction ID or timestamp) scales logarithmically, while offset‑based pagination degrades quadratically. You will implement the OBIE Links object (HATEOAS) for self‑discovery, design database composite indexes (on accountId + bookingDateTime) to keep query times under 50ms, and use sparse fieldsets to reduce network bandwidth by 40% when TPPs only need specific fields.

LEARNING OBJECTIVES

1. Differentiate between offset‑based pagination (limit=100&offset=1000) and cursor‑based pagination (limit=100&fromBookingDateTime=2026-01-01)—calculating the exact database performance degradation (offset 1,000 → 50ms; offset 10,000 → 2,100ms) and proving why cursor‑based is mandatory for production.

2. Implement the OBIE v4.0 Meta and Links object—constructing the self, first, prev, next, and last URIs with parameter encoding, ensuring that TPPs can navigate transaction histories without constructing URIs manually.

3. Design a composite database index—creating a B‑tree index on (account_id, booking_date_time) that reduces the query execution time from O(n log n) full scan to O(log n) seek, and measuring the performance improvement (from 1,200ms to 18ms for a 90‑day window).

4. Implement filtering parameters (fromDateTime, toDateTime, minAmount, maxAmount) and field selection (fields=id,amount,bookingDate) to minimise payload size—calculating the bandwidth saving (from 2.4 MB to 1.1 MB for a typical 100‑transaction response).

5. Quantify the end‑to‑end latency budget for a paginated transaction list—including database seek (18ms), JSON serialisation (12ms), network transmission (50ms for 100 items), and OpenAPI validation (1ms)—ensuring it stays under the 1.5s CDR SLA.

PART 1: OFFSET VS. CURSOR — The SQL Performance Trap

1.1 The Offset Problem

Offset‑based pagination uses LIMIT N OFFSET M. In SQL, the database must scan and discard M + N rows to return N rows. As M grows, the query becomes exponentially slower.

SQL Example:

SELECT * FROM transactions 
WHERE account_id = 'acc-123' 
ORDER BY booking_date_time DESC 
LIMIT 100 OFFSET 10000;

Performance measurement (PostgreSQL 15, 10M rows):

Observation: At offset 50,000, the query takes 3.4 seconds—exceeding the CDR 1.5s SLA. This is unacceptable.

1.2 The Cursor‑Based Solution (Keyset Pagination)

Instead of an offset, use a cursor: the booking_date_time and transaction_id of the last item in the previous page.

SQL Example:

SELECT * FROM transactions 
WHERE account_id = 'acc-123' 
  AND (booking_date_time < '2026-08-01T12:34:56Z'
       OR (booking_date_time = '2026-08-01T12:34:56Z' AND transaction_id < 'txn-456'))
ORDER BY booking_date_time DESC, transaction_id DESC
LIMIT 100;

Performance measurement:

Conclusion: Cursor‑based pagination is constant time—independent of the page number. It uses the B‑tree index to jump directly to the cursor value, scanning only the required 100 rows.

1.3 Implementing Cursors in API Responses

Request:

GET /accounts/acc-123/transactions?limit=100&fromBookingDateTime=2026-08-01T00:00:00Z

Response (with next cursor):

{
  "data": {
    "transaction": [
      // ... 100 transactions ...
    ]
  },
  "meta": {
    "totalPages": 52,
    "currentPage": 1
  },
  "links": {
    "self": "/accounts/acc-123/transactions?limit=100",
    "next": "/accounts/acc-123/transactions?limit=100&fromBookingDateTime=2026-07-15T23:59:59Z",
    "last": "/accounts/acc-123/transactions?limit=100&toBookingDateTime=2024-01-01T00:00:00Z"
  }
}

Generating the next cursor: The ASPSP takes the booking_date_time of the last transaction in the current page and returns it as the fromBookingDateTime parameter for the next page.

PART 2: THE LINKS OBJECT — HATEOAS Compliance

2.1 Why HATEOAS Is Mandatory

The OBIE v4.0 and CDR v1.4.0 specifications mandate a Links object (often called HATEOAS—Hypermedia as the Engine of Application State). This allows TPPs to navigate pages without constructing URIs manually, reducing client‑side bugs.

The Five Standard Links:

2.2 Generating the Links in Python

from urllib.parse import urlencode
from datetime import datetime, timedelta

class PaginationLinks:
    def __init__(self, base_url, account_id, limit, current_page_data):
        self.base_url = base_url
        self.account_id = account_id
        self.limit = limit
        self.current_page_data = current_page_data

    def generate(self):
        links = {
            "self": self._build_url({})
        }

        # If there are transactions, calculate prev/next
        if self.current_page_data:
            first_txn = self.current_page_data[0]
            last_txn = self.current_page_data[-1]

            # First page: oldest transactions (reverse chronological)
            # This requires a separate query for the oldest timestamp
            oldest_ts = self._get_oldest_timestamp()
            if oldest_ts:
                links["first"] = self._build_url({
                    "toDateTime": oldest_ts.isoformat() + 'Z'
                })

            # Previous page: use the first transaction's timestamp as the `to` cursor
            if len(self.current_page_data) == self.limit:
                prev_ts = first_txn['bookingDateTime']
                links["prev"] = self._build_url({
                    "toDateTime": prev_ts
                })

            # Next page: use the last transaction's timestamp as the `from` cursor
            if len(self.current_page_data) == self.limit:
                next_ts = last_txn['bookingDateTime']
                links["next"] = self._build_url({
                    "fromDateTime": next_ts
                })

            # Last page: newest transactions (requires a separate query)
            newest_ts = self._get_newest_timestamp()
            if newest_ts:
                links["last"] = self._build_url({
                    "fromDateTime": newest_ts.isoformat() + 'Z'
                })

        return links

    def _build_url(self, params):
        # Merge default params (limit, account_id)
        default_params = {"limit": self.limit}
        merged = {**default_params, **params}
        return f"{self.base_url}/accounts/{self.account_id}/transactions?{urlencode(merged)}"

2.3 Latency Overhead of Generating Links

• Database queries for oldest_ts and newest_ts: These are single‑row MIN(booking_date_time) and MAX(booking_date_time) queries with an index seek. Each takes ~2ms (p95) when indexed.

• Total overhead: ≤5ms—well within the budget.

PART 3: FILTERING AND FIELD SELECTION — Minimising Payload

3.1 The Problem of Over‑Fetching

Returning all transaction fields (20+ fields) for 100 transactions consumes approximately 2.4 MB of JSON (uncompressed). Under high load (25 RPS), this saturates the network link.

Solution: Allow TPPs to request only the fields they need using the fields parameter.

Request:

GET /accounts/acc-123/transactions?fields=id,amount,bookingDate,description&limit=100

Response (only requested fields):

{
  "data": {
    "transaction": [
      {"id": "txn-1", "amount": 100.00, "bookingDate": "2026-08-01", "description": "Grocery"},
      {"id": "txn-2", "amount": 250.50, "bookingDate": "2026-07-31", "description": "Rent"}
    ]
  }
}

Bandwidth saving:

• Full payload (20 fields): 2.4 MB

• Sparse payload (4 fields): 0.9 MB

• Saving: 62.5% reduction.

3.2 Filtering Parameters (OBIE v4.0)

3.3 Database Indexing Strategy for Filters

To achieve sub‑50ms query times, create a composite B‑tree index:

CREATE INDEX idx_transactions_account_timestamp 
ON transactions (account_id, booking_date_time DESC) 
INCLUDE (amount, description, status);

Why this index:

• account_id is the leading column (equality filter).

• booking_date_time DESC supports the ORDER BY and the > / < cursor filters.

• INCLUDE (covering index) prevents the database from reading the actual table rows for amount, description, and status—reducing I/O.

Performance measurement:

• Without index: 1,200ms (full table scan).

• With composite index: 18ms (p95).

Query execution plan:

Index Seek on idx_transactions_account_timestamp
  Seek Predicate: account_id = 'acc-123' AND booking_date_time < '2026-08-01'
  Output: account_id, booking_date_time, amount, description, status

PART 4: END‑TO‑END LATENCY BUDGET FOR TRANSACTION LISTS

The CDR mandates a p95 response time ≤1.5 seconds for transaction endpoints. Here is the exact breakdown.

Observation: The database seek (18ms) is the dominant internal cost. The network transmission (50ms) is the dominant external cost. The total (101ms) is well under the 1.5s budget, leaving 1.4 seconds of headroom for unusual network conditions.

CLOSING — OPERATIONAL RISK OF POOR PAGINATION

If the ASPSP uses offset‑based pagination without proper indexing:

• Latency spikes to 3+ seconds → TPPs time out → failed integrations.

• Database contention → table locks → other TPPs blocked → discrimination (PSD2 Article 67(3)(b)).

• Network saturation → large payloads (2.4 MB) exceed bandwidth → 503 errors.

Key takeaways:

• Cursor‑based pagination is mandatory for performance (constant‑time vs. linear degradation).

• The Links object (HATEOAS) is a regulatory requirement (OBIE, CDR).

• Composite indexes on (account_id, booking_date_time DESC) reduce query time from 1,200ms to 18ms.

• Sparse fieldsets reduce bandwidth by >60%.

Transition to Lesson 2.6: Now that you can efficiently retrieve transaction data, we turn to the inevitable: errors. Lesson 2.6 covers Error Handling, HTTP Status Codes, and Problem Details—standardising how the ASPSP communicates validation failures, consent revocations, and server faults to TPPs, using RFC 7807 and OBIE‑specific error codes.