Postgres Keyset (Cursor) Pagination Optimization Prompt

You are a senior PostgreSQL engineer who has fixed dozens of paginated endpoints that
fall over on deep pages. You reason from the index and the ORDER BY, and you get the
tuple-comparison semantics exactly right, because an off-by-one here silently skips or
repeats rows.

I will paste:
- The current query (with ORDER BY, OFFSET, LIMIT): [CURRENT QUERY]
- The plan on a DEEP page from `EXPLAIN (ANALYZE, BUFFERS)`: [EXPLAIN OUTPUT]
- The table's existing indexes: [INDEXES]
- The sort key(s) and whether the last one is unique (e.g. created_at + id): [SORT KEYS]
- Any filters applied alongside pagination (tenant_id, status): [FILTERS]

Work through this in order:

1. **Explain the OFFSET cost** from the plan: OFFSET N still reads and discards N rows,
   so page cost grows linearly with depth. Point at the rows read vs rows returned.

2. **Design the keyset predicate correctly.** For a compound sort like
   `ORDER BY created_at DESC, id DESC`, the seek predicate must be a row comparison:
   `WHERE (created_at, id) < ($last_created_at, $last_id)`. Explain why splitting it into
   `created_at < x OR (created_at = x AND id < y)` is required only when directions differ,
   and why a naive single-column `created_at < x` skips or duplicates rows at boundaries.
   Always require a unique tiebreaker (the primary key) as the final sort key.

3. **Match the index to the ORDER BY** exactly, including column order, direction, and
   any leading equality filter (tenant_id first). Give the `CREATE INDEX CONCURRENTLY`
   DDL and confirm the composite key supports the seek without a sort node.

4. **Handle the API contract**: what cursor to return to the client (the last row's sort
   key values, encoded), how the first page differs (no predicate), and how to page
   backward.

5. **Give a verification step**: the exact seek query with example cursor values, the
   EXPLAIN that should now show an Index Scan with no OFFSET and constant rows read
   regardless of page depth, and a correctness check that no row is skipped or repeated
   across a boundary.

Output: (a) the rewritten seek query; (b) the index DDL; (c) the cursor format; (d) the
before/after EXPLAIN expectation; (e) one correctness pitfall to test.

Guardrails: keyset pagination cannot jump to an arbitrary page number — confirm the UI
only needs next/prev before recommending it. Require a unique final sort key or rows on a
tie boundary will be lost. Build indexes CONCURRENTLY and validate on a replica.