Towergit Commit Pagination: Offset vs. Keyset – Decision Guide

When Towergit scales to millions of commits, the pagination strategy for its commit‑list API becomes critical. This guide compares offset‑based and keyset pagination, explains trade‑offs, and shows how to implement and validate keyset pagination in a Java/Hibernate backend.

Problem Statement

Towergit’s public API returns commit lists for repositories. As the number of commits grows into the millions, naïvely paging with LIMIT … OFFSET … becomes a performance bottleneck and can produce inconsistent results when new commits are added during a user’s navigation. The engineering team must decide on a pagination strategy that balances performance, correctness, and usability.

Decision & Constraints

Decision: Choose between offset‑based pagination and keyset (seek‑method) pagination for the commit‑list endpoint.
Constraints:
Java backend using Hibernate ORM.
Commit table contains >1 M rows for large repos.
Clients expect fast response times (<200 ms) even on deep pages.
API must support continuous scrolling (next page) but not arbitrary page jumps.
Data consistency: new commits should not push existing commits to a different page.

Supported Options

Strategy	Implementation	Pros	Cons
Offset‑based	`session.createQuery("FROM Commit c ORDER BY c.id DESC") .setFirstResult(pageNumber * pageSize) .setMaxResults(pageSize) .list();`	Simple to implement; supports arbitrary page numbers.	Performance degrades linearly; new commits shift existing records.
Keyset (seek)	`session.createQuery("FROM Commit c WHERE c.id < :lastId ORDER BY c.id DESC") .setParameter("lastId", lastSeenId) .setMaxResults(pageSize) .list();`	Constant‑time per page; no drift; efficient on large datasets.	Cannot jump to arbitrary page; requires unique, non‑null sort key.

Trade‑Off Analysis

Performance: Offset scans all preceding rows; keyset uses an indexed lookup. On a 1 M row table, OFFSET 10000 may trigger a full table scan, while WHERE id > lastId uses the primary key index.
Consistency: Keyset preserves ordering even when new commits are inserted; offset can cause duplicates or gaps.
Usability: Offset allows “page 500” navigation; keyset requires sequential scrolling.
Complexity: Offset is trivial; keyset needs careful handling of edge cases (e.g., duplicate timestamps).

Concrete Implementation – Keyset Pagination

Assumptions: Commit entity has a primary key id (auto‑increment) and a timestamp field. We will paginate by id descending.

Database Index – Ensure a B‑tree index on id exists (usually implicit for PK).

API Endpoint – Accept lastId and pageSize query params. lastId is the smallest id seen on the previous page; for the first page, omit lastId.

@GetMapping("/repos/{repoId}/commits")
public List<CommitDTO> listCommits(@PathVariable Long repoId,
                                   @RequestParam(required = false) Long lastId,
                                   @RequestParam(defaultValue = "20") int pageSize) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Commit> cq = cb.createQuery(Commit.class);
    Root<Commit> root = cq.from(Commit.class);
    cq.select(root).where(cb.equal(root.get("repoId"), repoId));
    if (lastId != null) {
        cq.where(cb.lessThan(root.get("id"), lastId));
    }
    cq.orderBy(cb.desc(root.get("id")));
    return entityManager.createQuery(cq)
            .setMaxResults(pageSize)
            .getResultList();
}

Client Usage – Store the id of the last commit returned; send it as lastId for the next request.
Verification – Performance Test – Run the following commands on a test database with >1 M commits:
```
# Offset query
EXPLAIN ANALYZE SELECT * FROM commits WHERE repo_id = 1 ORDER BY id DESC LIMIT 20 OFFSET 10000;

# Keyset query
EXPLAIN ANALYZE SELECT * FROM commits WHERE repo_id = 1 AND id < 500000 ORDER BY id DESC LIMIT 20;
```
Check that the offset query shows an Index Scan followed by a Seq Scan or Full Table Scan, while the keyset query shows a simple Index Scan.
Verification – Consistency Test – Insert a new commit into the first page and reload the first two pages. The first page should contain the new commit; the second page should still contain the same commits that were originally on page two, with no duplicates or gaps.
Limitations – Because keyset pagination cannot jump to an arbitrary page number, UI must provide “next”/“previous” controls only. If a user needs to jump to page 500, they must sequentially scroll or the API must expose a hybrid approach (e.g., offset for the first few pages, then keyset).
Edge Cases – If the id column is not strictly monotonically increasing (e.g., due to replication lag), consider using a composite key of timestamp and id to guarantee order.

Rollback Considerations

Keyset pagination changes the API contract: clients must now track lastId. If the existing API is already in production, a graceful migration path is to expose both modes behind a feature flag, deprecate offset after a release cycle, and document the new parameter.

Conclusion

For Towergit’s commit‑list API, keyset pagination offers superior performance and data consistency for large repositories, at the cost of non‑random page access. The trade‑offs align with the constraints: fast, stable paging and a simple implementation that leverages Hibernate’s query API. Implementing the keyset strategy as shown, and validating with the provided queries, will ensure that Towergit scales without compromising user experience.

Choosing Pagination for Towergit Commit APIs: Offset vs. Keyset