Persistence with PostgreSQL

Pagination at the Database Layer

ReadingPreview

আপনি একটি free preview lesson দেখছেন।

আমরা API design-এর সময় pagination contract already define করেছি:

GET /api/v1/products?page=0&size=20

GET /api/v1/orders?page=0&size=20

Current v1 rules:

page
→ zero-based
→ default 0
→ minimum 0

size
→ default 20
→ minimum 1
→ maximum 100

Response shape:

{
  "items": [],
  "page": 0,
  "size": 20,
  "totalItems": 0,
  "totalPages": 0
}

কিন্তু HTTP layer-এ pagination parameters accept করলেই application actually paginated হয়ে যায় না।

Bad implementation:

load 1,000,000 rows from PostgreSQL
    ↓
Java List
    ↓
skip 20
    ↓
take 20

Technically client 20 records পায়।

Operationally এটি pagination নয়।

এই lesson-এর core principle:

Pagination is only real when PostgreSQL itself limits the amount of data returned to the application.

এই lesson-এ আমরা দেখব API-এর page এবং size কীভাবে database query-তে translate হয়, কেন stable ordering mandatory, COUNT query কোথা থেকে আসে, Spring Data pagination কীভাবে persistence boundary-এর মধ্যে থাকবে, এবং offset pagination-এর trade-offs কী।

Why Pagination Belongs Close to the Data

Suppose database contains:

5,000,000 Orders

Client requests:

GET /api/v1/orders?page=0&size=20

The application only needs:

20 Orders

If Repository executes:

SELECT *
FROM orders;

then loads 5 million rows into application memory and slices 20 rows, several things go wrong:

database transfers unnecessary rows

network/database I/O increases

Java memory usage increases

entity mapping work increases

response latency increases

GC pressure increases

multiple concurrent requests become dangerous

Pagination must therefore happen before rows leave PostgreSQL.

SQL Pagination Basics

Relationally, page-based pagination commonly becomes:

LIMIT
OFFSET

Example:

page = 0
size = 20

maps to:

LIMIT 20
OFFSET 0

Page 1:

page = 1
size = 20

maps to:

LIMIT 20
OFFSET 20

Page 2:

page = 2
size = 20

maps to:

LIMIT 20
OFFSET 40

General formula:

offset = page × size

Zero-Based Pagination

Our API intentionally uses:

page = 0

for the first page.

This aligns naturally with Spring Data's page numbering and keeps conversion simple.

Examples:

page 0
size 20
→ rows 1–20 conceptually

page 1
size 20
→ rows 21–40

page 2
size 20
→ rows 41–60

The public contract must stay consistent.

Do not let one endpoint use:

page=0

while another interprets:

page=1

as the first page.

Pagination Without Ordering Is Broken

Consider:

SELECT
    id,
    status
FROM orders
LIMIT 20
OFFSET 20;

What does:

"the second 20 rows"

mean?

Without an explicit:

ORDER BY

there is no reliable application-level ordering contract.

A relational table is not inherently:

ordered by insertion time

or:

ordered by primary key

unless the query explicitly says so.

Therefore:

Every paginated query needs deterministic ordering.

Our Order History Ordering

We already chose:

created_at DESC

id DESC

for customer Order history.

Query:

SELECT
    id,
    customer_id,
    status,
    created_at
FROM orders
WHERE customer_id = ?
ORDER BY
    created_at DESC,
    id DESC
LIMIT ?
OFFSET ?;

This gives us:

newest Orders first

and a deterministic tie-breaker.

Why We Need the ID Tie-Breaker

Suppose:

Order 1001
created_at = 18:00:00.123

and:

Order 1002
created_at = 18:00:00.123

If query only says:

ORDER BY created_at DESC

the relative ordering of those two rows is not part of the contract.

Across queries, database plans, or changes, they may appear in different order.

Then one row can theoretically:

appear on page 0 once

appear on page 1 later

or be skipped around a page boundary.

Adding:

id DESC

creates a unique secondary ordering.

Stable Ordering Rule

For pagination, aim for an ordering where the full ordering key uniquely positions rows.

For Order history:

(created_at, id)

works because:

id

is unique.

This is much stronger than:

ORDER BY status

where thousands of rows may share the same value.

Pagination and Our Index

Previous lesson introduced:

CREATE INDEX orders_customer_history_idx
    ON orders (
        customer_id,
        created_at DESC,
        id DESC
    );

Notice how well this matches:

WHERE customer_id = ?
ORDER BY created_at DESC, id DESC

Pagination contract, query design, and index design now align.

This is what good persistence design looks like:

API requirement
    ↓
Repository query
    ↓
database ordering
    ↓
index

Calculating Offset

Conceptually:

int page = 2;
int size = 20;

long offset =
        (long) page * size;

Why cast/use a wider type?

Because multiplying two integers can overflow if unchecked extreme values somehow reach internal code.

Our HTTP validation already restricts:

size <= 100

and page >= 0, but handling pagination values through well-defined types remains good defensive engineering.

With Spring Data, we normally let:

PageRequest

perform the pagination translation rather than manually building the offset.

Spring Data `Pageable`

Inside persistence adapter:

Pageable pageable =
        PageRequest.of(
                pageQuery.page(),
                pageQuery.size(),
                Sort.by(
                        Sort.Order.desc(
                                "createdAt"
                        ),
                        Sort.Order.desc(
                                "id"
                        )
                )
        );

This is persistence infrastructure.

UseCase receives:

PageQuery

not:

Pageable

Application Boundary

Application-owned model:

public record PageQuery(
        int page,
        int size
) {
}

UseCase:

PageResult<OrderSummary> result =
        orderRepository.findByCustomer(
                customerId,
                pageQuery
        );

Repository adapter converts:

PageQuery
→ Pageable

This keeps Spring Data outside the application layer.

Spring Data Query

Infrastructure repository might expose:

Page<OrderSummaryRow>
findByCustomerId(
        String customerId,
        Pageable pageable
);

Spring Data translates:

Pageable

into database-level:

LIMIT

OFFSET

ORDER BY

or equivalent dialect-specific query behaviour.

The important thing is:

PostgreSQL receives a bounded query

rather than Java slicing a full result set.

Mapping Back to Application Pagination

Spring returns conceptually:

Page<OrderSummaryRow>

Our adapter maps it to:

public record PageResult<T>(
        List<T> items,
        int page,
        int size,
        long totalItems,
        int totalPages
) {
}

Then Handler maps:

PageResult<OrderSummary>

to our public:

PageResponse<OrderSummaryResponse>

Architecture:

HTTP
    ↓
page / size
    ↓
PageQuery
    ↓
Repository
    ↓
Pageable
    ↓
PostgreSQL LIMIT/OFFSET
    ↓
Spring Page
    ↓
PageResult
    ↓
PageResponse

Each representation belongs to its own boundary.

Why Not Return Spring `Page` Directly?

Because Spring's Page is a framework abstraction.

If Handler directly returns it, our public API may suddenly expose framework-specific fields such as:

pageable

sort

first

last

numberOfElements

depending on serialization.

But our API contract explicitly says:

{
  "items": [],
  "page": 0,
  "size": 20,
  "totalItems": 0,
  "totalPages": 0
}

Public API design should remain deliberate.

The Count Query

Where do:

totalItems

totalPages

come from?

Suppose current page query is:

SELECT ...
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 40;

This query only tells us:

rows on this page

It does not tell us:

how many matching Orders exist in total

So a paginated Page generally needs another query conceptually like:

SELECT COUNT(*)
FROM orders
WHERE customer_id = ?;

Then:

totalItems = count

and:

totalPages =
ceil(totalItems / size)

Example

Suppose:

totalItems = 95

size = 20

Then:

page 0 → 20
page 1 → 20
page 2 → 20
page 3 → 20
page 4 → 15

So:

totalPages = 5

Why We Chose Totals

Our API already committed to:

totalItems

totalPages

because they are useful for the v1 client experience and we have not proven the count query is problematic.

This is an intentional trade-off.

We should not remove totals because:

COUNT might theoretically be expensive someday

without evidence.

Count Queries Are Not Free

However, a backend engineer should understand the cost.

For a large filtered dataset, PostgreSQL may need real work to determine:

COUNT(*)

Even if requested page contains only:

20 rows

the total-count query may inspect significantly more data.

So one HTTP request may involve:

1 query
→ requested page

1 query
→ total count

This is normal for our current Page contract.

`Page` vs `Slice`

If our API only needed:

items

hasNext

we could potentially avoid exact total counts through a slice-style query that retrieves enough data to determine whether another page exists.

But our current public contract explicitly includes:

totalItems

totalPages

Therefore:

Page

semantics fit better.

Do not change the transport contract just because another framework abstraction exists.

Empty Collections

Suppose customer has no Orders.

Request:

GET /api/v1/orders?page=0&size=20

Response:

{
  "items": [],
  "page": 0,
  "size": 20,
  "totalItems": 0,
  "totalPages": 0
}

HTTP status:

Not:

The collection resource exists.

It is simply empty.

Out-of-Range Pages

Suppose:

totalItems = 30

size = 20

Valid data exists on:

page 0

page 1

Client asks:

GET /api/v1/orders?page=50&size=20

Our contract says:

return 200

items = []

retain requested page metadata

return actual totalItems / totalPages

Example:

{
  "items": [],
  "page": 50,
  "size": 20,
  "totalItems": 30,
  "totalPages": 2
}

This is not a missing resource.

It is an empty position in a valid collection query.

Invalid Pagination Is Different

These are invalid:

page = -1

size = 0

size = -5

size = 101

They should fail before Repository execution with:

400

VALIDATION_ERROR

Example:

{
  "type": "https://api.liveklass.io/problems/validation-error",
  "title": "Validation failed",
  "status": 400,
  "detail": "One or more fields are invalid.",
  "instance": "/api/v1/orders",
  "code": "VALIDATION_ERROR",
  "errors": [
    {
      "field": "size",
      "code": "INVALID_VALUE",
      "message": "Size must be between 1 and 100."
    }
  ]
}

Do not silently clamp:

size=10000
→ 100

because then client does not know its request violated the contract.

Validation vs Persistence Responsibility

Handler:

parse page

parse size

validate API bounds

UseCase:

apply authenticated/customer scope

coordinate query intent

Repository:

translate to database pagination

apply ordering

execute count/page queries

PostgreSQL:

filter

order

limit

offset

This keeps responsibility clear.

Product Pagination

Product browse conceptually requires:

SELECT
    p.id,
    p.name,
    p.price
FROM products p
JOIN inventory i
    ON i.product_id = p.id
WHERE p.active = TRUE
  AND i.available_quantity > 0
ORDER BY ...
LIMIT ?
OFFSET ?;

The important ordering principle still applies.

Before Product pagination becomes complete production code, its deterministic sort must be explicitly chosen.

We should not rely on:

whatever row order PostgreSQL returns

Filter Before Pagination

This is critical.

Correct:

all Products
    ↓
active filter
    ↓
Inventory > 0 filter
    ↓
sort
    ↓
LIMIT / OFFSET

Bad:

all Products
    ↓
LIMIT / OFFSET
    ↓
Java checks active + Inventory
    ↓
returns whatever remains

Why Page-Then-Filter Is Wrong

Suppose first 20 database Products contain:

15 inactive

3 out of stock

2 orderable

If we paginate first and filter later, page 0 returns only:

2 Products

even though there may be hundreds of orderable Products immediately after those 20 rows.

Now pagination metadata and user experience are wrong.

Business visibility filtering must happen before database page boundaries.

Ownership Before Pagination

Same rule applies to Order history.

Wrong:

SELECT first 20 Orders from entire database
    ↓
Java filters customer ownership

Customer may get:

0–2 Orders

even though they have many Orders elsewhere.

Correct:

WHERE customer_id = ?
ORDER BY ...
LIMIT ...
OFFSET ...

Authorization/business scope must be applied before pagination.

Filtering Must Also Affect the Count

Suppose:

10,000 Orders total

but customer owns:

37 Orders

Page query uses:

WHERE customer_id = ?

Count query must use the same scope:

SELECT COUNT(*)
FROM orders
WHERE customer_id = ?;

Then:

totalItems = 37

Not:

10,000

Otherwise response leaks information and produces incorrect metadata.

Count Query Must Match the Data Query

This is a general rule.

If page query applies:

customer scope

status filter

visibility filter

count query must represent the same logical dataset.

Otherwise:

items

and:

totalItems

describe two different collections.

Joins Can Make Count Queries Tricky

Suppose Product browse joins:

products

inventory

There is exactly one Inventory row per Product in our schema, so the join does not multiply Product rows.

That keeps counting straightforward.

But in other relational models, joining a one-to-many table may duplicate parent rows.

Then a naive:

COUNT(*)

could overcount.

Backend engineers need to understand the relational shape behind ORM-generated count queries.

Example of Row Multiplication

Imagine:

Order
→ many OrderItems

Query:

SELECT o.*
FROM orders o
JOIN order_items oi
    ON oi.order_id = o.id;

One Order with five items creates:

5 joined rows

If pagination target is:

Orders

rather than:

OrderItems

naive pagination/counting over that join can produce incorrect behaviour.

This is one reason paginating parent entities with fetched collections requires care.

Avoid Collection Fetch Join Pagination Blindly

Suppose we try:

page 20 Orders

and fetch all OrderItems

through one giant collection join.

Because each Order can expand into multiple SQL rows, database-level pagination may interact poorly with parent-level page semantics depending on query strategy.

A safer design is often:

page Order IDs / summaries first

then:

load detail/items only when the operation actually needs them

For Order history, we already prefer a summary projection.

Order History Should Not Fetch Items

Our collection endpoint:

GET /api/v1/orders

should not reconstruct every complete Order aggregate unless the response genuinely requires it.

Use a bounded summary query.

Then:

GET /api/v1/orders/{orderId}

can load the full Order with items.

This avoids pagination complexity and unnecessary data loading.

Product Projection

Similarly Product collection may use:

ProductSummaryRow

containing:

id

name

price

rather than loading persistence graphs that the endpoint does not need.

Again:

Read the amount of data the API operation actually needs.

Offset Pagination Under Concurrent Writes

Offset pagination has an important limitation.

Suppose current ordering:

newest first

Customer requests page 0:

A
B
C
D
E

Before they request page 1, a new Order:

is inserted at the beginning.

Now database order is:

X
A
B
C
D
E
...

Page 1's offset shifts.

The client may see:

again or miss another row depending on timing/page size.

This Is Expected v1 Behaviour

Our v1 page-based API does not promise a frozen collection snapshot across independent requests.

Under concurrent inserts/deletes:

duplicates

skips

across page boundaries can occur.

This is a known offset-pagination trade-off.

Don't Pretend Pagination Is a Transaction Across Requests

Page 0 and page 1 are:

two independent HTTP requests

We do not hold:

one database transaction

open while the user browses pages.

That would be operationally inappropriate.

So each page sees database state at the time that request executes.

When Cursor Pagination Helps

Cursor/keyset pagination can be better for:

high-write datasets

very deep browsing

stable continuation semantics

large offsets

For example, instead of:

page=100000

a cursor could conceptually say:

give me rows after
(createdAt=X, id=Y)

This can align strongly with the same ordered index.

But our current API deliberately chose simpler:

page / size

pagination.

We don't redesign v1 prematurely.

Deep Offset Cost

Suppose:

page = 100000

size = 20

Offset:

2,000,000

Even with a useful index, PostgreSQL may need to walk/skip many matching index entries before returning 20 rows.

So cost generally grows with deep offsets.

For typical user-facing history where users browse first few pages, this may be perfectly acceptable.

Maximum Size Protects the Backend

Our:

size <= 100

rule prevents clients from asking:

size = 1,000,000

and turning a paginated endpoint back into an effectively unbounded read.

Pagination bounds are therefore both:

API usability decision

and:

resource protection

But Maximum Page Is Not Fixed

We have not defined:

page <= 1000

or another arbitrary page cap.

A large page is syntactically valid under the current API contract.

It may simply become increasingly expensive because of offset cost.

If production evidence later requires:

maximum browsable depth

or a cursor-based replacement, that should be an intentional API change.

Integer Overflow and Page Offset

Even with:

size <= 100

an absurdly large page could theoretically create offset arithmetic issues in custom code.

Spring Data's pagination abstractions provide their own offset representation, but validation/application code should still avoid unsafe assumptions.

If we manually compute:

long offset =
        Math.multiplyExact(
                (long) page,
                (long) size
        );

we can detect impossible overflow rather than silently wrap.

Normally Spring Data handles this translation, so we don't need manual offset math in our Repository adapter.

Do Not Trust Client Sort Yet

Our API currently exposes:

page

size

but not unrestricted:

sort=anything

That's intentional.

If clients can specify arbitrary database property names:

?sort=customerId

we introduce:

contract complexity

data exposure questions

index/performance unpredictability

Supported sorting should be explicit and whitelisted if/when product requirements need it.

Repository Method

Application:

public interface OrderRepository {

    PageResult<OrderSummary>
    findByCustomer(
            CustomerId customerId,
            PageQuery pageQuery
    );
}

Persistence:

@Override
public PageResult<OrderSummary>
findByCustomer(
        CustomerId customerId,
        PageQuery pageQuery
) {
    Pageable pageable =
            PageRequest.of(
                    pageQuery.page(),
                    pageQuery.size(),
                    Sort.by(
                            Sort.Order.desc(
                                    "createdAt"
                            ),
                            Sort.Order.desc(
                                    "id"
                            )
                    )
            );

    Page<OrderSummaryRow> page =
            repository.findByCustomerId(
                    customerId.value(),
                    pageable
            );

    return toPageResult(page);
}

Spring-specific pagination stays entirely inside persistence.

Handler Mapping

HTTP:

GET /api/v1/orders?page=1&size=20

Handler conceptually:

public PageResponse<OrderSummaryResponse>
getOrders(
        int page,
        int size
) {
    PageQuery pageQuery =
            new PageQuery(
                    page,
                    size
            );

    PageResult<OrderSummary> result =
            useCase.execute(
                    pageQuery
            );

    return toResponse(result);
}

Handler does not call:

PageRequest

JpaRepository

directly.

UseCase

Conceptually:

public PageResult<OrderSummary> execute(
        PageQuery pageQuery
) {
    CustomerId customerId =
            currentCustomer.id();

    return orderRepository.findByCustomer(
            customerId,
            pageQuery
    );
}

UseCase provides the ownership scope.

Repository provides efficient persistence execution.

What About Admin Order History?

Admin may eventually:

view all Orders

That is a different query scope.

Potential application operation:

findAllForAdmin(pageQuery)

or another explicit query contract.

Do not implement admin behaviour by passing:

customerId = null

to mean:

all customers

unless that API is deliberately designed.

Explicit semantics are clearer.

Database Pagination and Transactions

A simple read page query does not require a long-running transaction spanning multiple pages.

Each request runs its own database operation.

Within one request, page query and count query may observe data according to the transaction/isolation context being used.

Under concurrent writes, exact totals can theoretically shift between independent operations depending on timing/isolation.

For normal v1 browsing this is acceptable.

We do not require snapshot-perfect pagination metadata across rapidly changing datasets.

Total Count Can Change Immediately

Suppose response:

{
  "totalItems": 100
}

is generated.

One millisecond later another Order is created.

Now database has:

This does not make the earlier response wrong.

It described the collection as observed for that request.

Clients should not treat pagination totals as permanent business facts.

Pagination Metadata Is Query Metadata

Fields such as:

totalItems

totalPages

describe:

the query result

not durable domain state.

Therefore:

PageResponse

is transport/query representation.

It is not persisted.

Do Not Store Page Numbers in Orders

Avoid schema ideas such as:

orders.page_number

Pagination is determined at query time from:

filter

sort

page

size

Rows do not inherently belong to a permanent page.

As new rows arrive, their page position changes.

Pagination Testing

We should test important boundary cases.

Default page

Request:

GET /api/v1/orders

should behave as:

page = 0

size = 20

First Page

Given 35 Orders:

page 0
size 20

returns:

20 items

totalItems = 35

totalPages = 2

Last Partial Page

page 1
size 20

returns:

15 items

Out-of-Range Page

page 5
size 20

returns:

0 items

200 status

totalItems still 35

totalPages still 2

Stable Ordering

Create several Orders with deterministic timestamps/ties in integration setup.

Verify:

createdAt DESC

id DESC

ordering.

This catches pagination bugs that ordinary "contains these rows" assertions can miss.

Ownership Scope

Create:

Customer A → 30 Orders

Customer B → 50 Orders

Query as Customer A.

Expected:

totalItems = 30

not:

and every returned item belongs to Customer A's authorized dataset.

Size Boundaries

Test:

size = 1

valid.

size = 100

valid.

size = 0

invalid.

size = 101

invalid.

These are HTTP boundary tests.

Database-Level Bounding Test

Integration test can create:

100 Orders

request:

size = 20

and verify Repository result contains exactly:

items.

We don't usually need to assert raw SQL LIMIT 20 text.

The behaviour plus query inspection where needed is enough.

Count Query Performance

At small scale, count queries are trivial.

At large scale, if:

COUNT

becomes a real bottleneck, possible strategies include:

different pagination contract

approximate totals

cached totals

Slice/hasNext semantics

But these are trade-offs with API consequences.

We do not introduce them before measurement.

Don't Cache Pagination Prematurely

A common instinct:

Counts may be expensive, let's put Redis in front of it.

Our architecture does not include Redis.

Before adding another datastore, determine:

Is count actually expensive?

How stale may total be?

How will invalidation work?

PostgreSQL is enough for our current requirements.

Pagination and JPA N+1

Even if parent query uses proper LIMIT 20, this can still be bad:

load 20 Orders
    ↓
lazy-load items once per Order

Now one paginated request performs:

21 queries

Pagination bounds rows but does not automatically solve relationship-fetching problems.

Order history should use a projection to avoid this.

Pagination and Memory

Correct database pagination also protects Java memory.

With:

size <= 100

Repository can normally materialize a bounded number of summary rows per request.

This gives us much more predictable resource usage under concurrency than unbounded collection loading.

Predictability Matters More Than Tiny Benchmark Wins

A backend serving many concurrent requests benefits from knowing:

one request cannot load millions of entities

even if a single unbounded query appears fine during local testing.

Pagination is partly about:

resource predictability

not only frontend navigation.

Pagination at Different Layers

Let's make responsibility explicit.

HTTP Handler

Owns:

page query parameter

size query parameter

defaults

bounds

API error response

UseCase

Owns:

who is querying

which collection semantics apply

business visibility scope

Examples:

authenticated customer's Orders

orderable Products

Repository

Owns:

translate application query to persistence query

database filtering

stable ordering

LIMIT/OFFSET through persistence framework

count query/result mapping

PostgreSQL

Executes:

WHERE

JOIN

ORDER BY

LIMIT

OFFSET

COUNT

against indexed relational data.

Domain

Does not own:

page

size

offset

totalPages

These are collection/query concerns, not Product/Order invariants.

Common Mistake 1 — Load Everything Then `subList()`

This is not real pagination.

Common Mistake 2 — `LIMIT` Without `ORDER BY`

Pages have no stable application meaning.

Common Mistake 3 — Sort After Pagination

Database page boundaries were created using the wrong ordering.

Sorting must happen before LIMIT/OFFSET.

Common Mistake 4 — Filter After Pagination

Pages become sparse or incorrect, and items can be skipped entirely.

Common Mistake 5 — Authorization Filter After Pagination

This can create both security and pagination correctness problems.

Ownership scope belongs in the database query.

Common Mistake 6 — Count a Different Dataset

totalItems must use the same logical filters/scope as the page query.

Common Mistake 7 — Return Spring `Page` Directly From the API

Framework metadata becomes public contract accidentally.

Common Mistake 8 — Paginate a Collection Fetch Join Without Understanding Row Multiplication

One parent with many children can expand into multiple SQL rows and distort parent pagination.

Common Mistake 9 — Assume Offset Pagination Is Snapshot-Stable

Concurrent inserts/deletes can cause cross-page duplicates or skips.

Our v1 contract accepts this trade-off.

Common Mistake 10 — Introduce Cursor Pagination Before It Is Needed

Page-based pagination remains a valid pragmatic choice for our current requirements.

Database Pagination Checklist

For every collection query, ask:

Is filtering happening in PostgreSQL?

Is authorization scope included before pagination?

Is ordering explicit?

Is ordering deterministic?

Does a unique tie-breaker exist?

Are LIMIT/OFFSET applied in the database?

Does the count query use the same filters?

Are we loading unnecessary relationships?

Could a join multiply parent rows?

Does the query align with an appropriate index?

Is the result bounded by our API size limit?

Our Order History Query

Conceptually, the core query is now:

SELECT
    id,
    status,
    created_at
FROM orders
WHERE customer_id = ?
ORDER BY
    created_at DESC,
    id DESC
LIMIT ?
OFFSET ?;

and count:

SELECT COUNT(*)
FROM orders
WHERE customer_id = ?;

Supported by:

orders_customer_history_idx
(
    customer_id,
    created_at DESC,
    id DESC
)

This is a coherent end-to-end pagination design.

Our Product Browse Direction

Conceptually:

SELECT
    p.id,
    p.name,
    p.price
FROM products p
JOIN inventory i
    ON i.product_id = p.id
WHERE p.active = TRUE
  AND i.available_quantity > 0
ORDER BY <deterministic-product-order>
LIMIT ?
OFFSET ?;

Before implementation is considered final, the Product collection ordering must be made explicit.

We do not invent it inside the Repository unnoticed.

Engineering Principle

The core principle:

Pagination belongs in the data query. Loading the full dataset and slicing it in Java is not pagination—it is an unbounded query with a smaller response.

Another:

A page is only meaningful when filtering, authorization scope, and deterministic ordering are applied before the database establishes page boundaries.

And:

Page-based pagination is a deliberate v1 trade-off: simple and predictable for normal browsing, while accepting count-query cost, deep-offset cost, and possible cross-page movement during concurrent writes.

Summary

In this lesson, we learned that:

Our v1 pagination uses zero-based page and bounded size.
page=0,size=20 maps conceptually to LIMIT 20 OFFSET 0.
Offset is derived from page × size.
Real pagination must happen in PostgreSQL before rows are loaded into Java.
Every paginated query needs explicit deterministic ordering.
Customer Order history uses created_at DESC, id DESC.
The unique Order ID acts as a tie-breaker when timestamps are equal.
Our orders_customer_history_idx(customer_id, created_at DESC, id DESC) aligns with filtering and page ordering.
Spring Data Pageable belongs inside the persistence adapter rather than the UseCase.
Application code uses its own PageQuery and PageResult.
Public HTTP responses use our deliberate PageResponse rather than serializing Spring's Page.
totalItems and totalPages generally require a count query in addition to the page-data query.
Our v1 contract intentionally keeps exact totals until real performance evidence justifies a different design.
Empty collections and out-of-range pages return 200 with empty items.
Invalid page or size values return our canonical VALIDATION_ERROR.
Business filters must be applied before pagination.
Product active and Inventory availability filtering must happen before page boundaries.
Customer ownership scope must be applied in the database query before pagination.
Count queries must use the same logical filters and authorization scope as the page-data query.
One-to-many joins can multiply relational rows, so parent pagination must be designed carefully.
Order history should use a summary projection rather than loading complete Order aggregates and OrderItems.
Offset pagination can become expensive for very deep pages.
Concurrent inserts or deletes can cause rows to shift between page requests; our v1 API does not promise a frozen snapshot.
Cursor/keyset pagination may become appropriate later if actual scale and usage justify it.
The size <= 100 contract provides predictable resource bounds for API requests.
Pagination does not solve N+1 queries by itself; relationship-fetching strategy still matters.
Handler, UseCase, Repository, PostgreSQL, and Domain each have distinct pagination-related responsibilities.

Next lesson:

Avoiding Common JPA Problems

There we will bring together everything from this module and examine the failures that make JPA applications slow or fragile in production—N+1 queries, lazy-loading leaks, accidental eager graphs, unsafe entity equality, unexpected dirty checking, transaction-boundary mistakes, unbounded repository calls, and letting Hibernate hide SQL cost.

Why Pagination Belongs Close to the Data

SQL Pagination Basics

Zero-Based Pagination

Pagination Without Ordering Is Broken

Our Order History Ordering

Why We Need the ID Tie-Breaker

Stable Ordering Rule

Pagination and Our Index

Calculating Offset

Spring Data Pageable

Application Boundary

Spring Data Query

Mapping Back to Application Pagination

Why Not Return Spring Page Directly?

The Count Query

Example

Why We Chose Totals

Count Queries Are Not Free

Page vs Slice

Empty Collections

Out-of-Range Pages

Invalid Pagination Is Different

Validation vs Persistence Responsibility

Product Pagination

Filter Before Pagination

Why Page-Then-Filter Is Wrong

Ownership Before Pagination

Filtering Must Also Affect the Count

Count Query Must Match the Data Query

Joins Can Make Count Queries Tricky

Example of Row Multiplication

Avoid Collection Fetch Join Pagination Blindly

Order History Should Not Fetch Items

Product Projection

Offset Pagination Under Concurrent Writes

This Is Expected v1 Behaviour

Don't Pretend Pagination Is a Transaction Across Requests

When Cursor Pagination Helps

Deep Offset Cost

Maximum Size Protects the Backend

But Maximum Page Is Not Fixed

Integer Overflow and Page Offset

Do Not Trust Client Sort Yet

Repository Method

Handler Mapping

UseCase

What About Admin Order History?

Database Pagination and Transactions

Total Count Can Change Immediately

Pagination Metadata Is Query Metadata

Do Not Store Page Numbers in Orders

Pagination Testing

Default page

First Page

Last Partial Page

Out-of-Range Page

Stable Ordering

Ownership Scope

Size Boundaries

Database-Level Bounding Test

Count Query Performance

Don't Cache Pagination Prematurely

Pagination and JPA N+1

Pagination and Memory

Predictability Matters More Than Tiny Benchmark Wins

Pagination at Different Layers

HTTP Handler

UseCase

Repository

PostgreSQL

Domain

Common Mistake 1 — Load Everything Then subList()

Common Mistake 2 — LIMIT Without ORDER BY

Common Mistake 3 — Sort After Pagination

Common Mistake 4 — Filter After Pagination

Common Mistake 5 — Authorization Filter After Pagination

Common Mistake 6 — Count a Different Dataset

Common Mistake 7 — Return Spring Page Directly From the API

Common Mistake 8 — Paginate a Collection Fetch Join Without Understanding Row Multiplication

Common Mistake 9 — Assume Offset Pagination Is Snapshot-Stable

Spring Data `Pageable`

Why Not Return Spring `Page` Directly?

`Page` vs `Slice`

Common Mistake 1 — Load Everything Then `subList()`

Common Mistake 2 — `LIMIT` Without `ORDER BY`

Common Mistake 7 — Return Spring `Page` Directly From the API